4 Ways to Elevate Your Spreadsheet Game with Chat GPT (The Construction Special)

How do you solve complex problems in Excel? 

If you’re like me, you play around in the spreadsheet - fail heroically - and head over to Google. Finally, you land on Youtube and find the solution 11 videos later. 

Save yourself time and frustration with Chat GPT.

Simply type out your Excel problem and asks the bot how to solve it. You get the solution, often a copy-and-paste formula, and a breakdown of how to do it yourself.

Let’s look at four examples of Chat GPT doing this for me. Remember, even if you know how to do the below, you can apply the process to any problem. 

Full disclosure, I rarely use spreadsheets anymore and forgot more than I learned. So, I asked Chat GPT how to solve these problems using the spreadsheets in the images, just like you would.

Note: I’m using Excel to reference Google Sheets too.

Set up your Chat GPT account

It’s easy to set up your Chat GPT account.

Step 1: Open your browser and type: chat.openai.com.

Step 2: Click sign up and create an account

Step 3: Follow the steps, like verifying your account

Step 4: Login and you’re good to go.

Get around the “Chat GPT is at capacity right now” issue

Sometimes, Chat GPT will refuse entry because it’s at capacity. You can give your email address in exchange for notifying you when it’s back up. The bot throws out some novel content to soften the blow.

I overcome this by handing over the email address and then hitting return on the webpage a few times. I’ve never got an email but I get access quickly every time.

4 ways Chat GPT improves your Excel game

Let’s start with a basic example and get more complex as we go.

1. Checking for errors with IF Statements

Click the buttons in the image above to see the process in action

If you’re in construction and using spreadsheets, you’re probably dealing with financials.

I know from experience that errors are easy to come by. Long hours, tired eyes and boredom will do that. But tiny errors can cost you big. I’ve been guilty of a misplaced digit costing thousands.

Won us the contract though - silver linings.

IF statements in Excel can prevent these mistakes by highlighting errors. This function lets you logically compare a value with what you expect. So, an IF statement gives two results. The first result is if your comparison is true. The second result is when it’s false.

If the statement is false, you flag this by asking Excel to say “Error” and to highlight the cell with a colour.

Let’s ask Chat GPT to create this formula for us.

Chat GPT returns the formula and tells you what it is and why it works. Now, when the figures do not match, the error pops up and you can fix it.

Now, when the result of the formula is wrong “error” will appear and the cell turns red.

Note: We’re keeping it relatively simple here with just one IF statement. But on complex spreadsheets, you can have multiple IF statements using the “nest IF” formula. You’ll have to ask Chat GPT about that one.

2. Pulling unique names and counts from lists with the UNIQUE function

Click the buttons in the image above to see the process in action

Finding the unique names and counts of something in Excel is typically a challenge. It’s painful to scroll through hundreds of cells to pull out individual objects. 

Let’s say you have a monster spreadsheet that lists dozens of products. You need to pull out the names of unique products to understand what you’re site teams are buying. 

Think of the time wasted scrolling and recording single product names until you get them all. How do you keep track? It’s a manual, sad and long task.

To overcome this, you use the UNIQUE function. This will list every product appearing in your lists and remove duplicates - leaving you with an accurate list if unique products.

Let’s ask Chat GPT how to do this:

Chat GPT gives us the formula and what it does with some history too, telling us the UNIQUE function was introduced to Google Sheets in 2021 (make sure you’re tool is up-to-date).

Next, take the formula and paste it into your spreadsheet. The full list of products will appear instantly. 

3. Splitting names from email addresses with the LEFT & FIND function

Click the buttons in the image above to see the process in action

Let’s say you have a list of professional email addresses on a spreadsheet. You need to split the first names and the last names from the email addresses to load the contacts into your customer relationship management (CRM) system.

Doing this manually eats up lots of your time. You can solve this by using Flash Fill from the data tab.

You establish a pattern by typing the first name in the column next to the email address. Press Ctrl+E and Excel will sense the pattern and fill the cells below with all the first names relative to the email address in that row.

Do the same for the second name in the next column to get the needed data.

But, if you didn’t know that and didn’t want to hunt online for the answer, you could ask Chat GPT to give you the answer.

In this example, the list only has first names before the @ (for simplicity) but you can adapt to whatever you have. 

It gives you the formula you need which is a mix of the LEFT and FIND functions. Paste it into the spreadsheet and press Ctrl+Enter. This will spread the function into all the cells.

4. Sourcing prices from a master price tab with VLOOKUP function

Price changes. They happen daily in construction and managing them on spreadsheets is risky and necessary.

Let’s say you have a spreadsheet with a huge list of materials. It’s a mix of repeating and unique products because it’s everything one project manager needs to buy on their projects. 

The last thing you want to do is run through and change each price individually. You could Ctrl+F and Replace with the price, but what about manual errors?

That’s why VLOOKUP is the best function here.

VLOOKUP lets you pull prices from an updated Master Price List on a separate tab based on the product name (or anything you choose). No playing around with individual cells.

If you’re a seasoned Excel pro, you’ll use these but I personally find them complex. That’s where Chat GPT helps.

First, Chat GPT explains how to use the Vlookups function without its help, which is handy because you want to learn.

The bot then explains the answer, giving you the context to understand how it works. For example, you know FALSE means you’re looking for an exact match. 

What do you think about Chat GPT?

The jury is out on Chat GPT. I sit firmly in the “this tool is fantastic and can help me and the construction industry” camp.

The four tips in this article solve basic to relatively complex problems in Excel and Google Sheets. But it’s not limited to these. You don’t need to know the function you’re after; you just need to express the problem you’re having clearly.

Look on Youtube and see the countless ways Chat GPT can help you with spreadsheet problems. It even guides you on how to automate repetitive actions using  Macros.

As much as I love a good spreadsheet, they’ve caused me lots of anguish over the years, especially when pricing work as an estimator. It doesn’t need to be that way.

Archdesk takes away the spreadsheet pain and most of the other issues you get through them. It’s the reason I joined the company - to find a better way for others like me.

Why not reach out to us and see what I mean?