Right, this article is split into two parts. The first part is written by me and ChatGPT. The second is by just me, which I wrote after experimenting with the AI tool after it was released.
I decided to update the article because of its popularity. I didn't think my first crack did you, the reader, justice - though being a helpful article in its own right.
So, the new article is first and you can find the original by scrolling down about two-thirds.
ChatGPT, an advanced AI tool, can enhance your spreadsheet usage and streamline various aspects of construction management.
In this comprehensive guide, we will explore 14 actionable tips, complete with step-by-step guides and creative suggestions, on how construction professionals can elevate their Excel and Google Sheets game with ChatGPT.
Cost estimation is a vital aspect of construction management, impacting budgeting, resource allocation, and project feasibility. ChatGPT can help you achieve accurate cost estimates with minimal effort.
Example: A road construction project requires cost estimation for various activities, including excavation, asphalt laying, and drainage installation. ChatGPT can analyse the required materials, labour, and equipment rates, and calculate accurate costs for each activity, streamlining the estimation process.
Quantity surveying is crucial for construction projects, as it determines the required materials and labour.
ChatGPT's natural language processing capabilities can help you extract data from construction documents and automate calculations, saving you time and effort.
Example: For a commercial building project, ChatGPT can analyse the architectural drawings and specifications to accurately calculate the amount of concrete, steel reinforcement, and formwork needed for the foundation and structural elements, saving time and reducing potential errors.
Effective project scheduling and resource allocation are vital for successful construction management.
ChatGPT can help create visual tools like Gantt charts and calendars while providing suggestions for optimised resource allocation.
Example: A residential housing development project can benefit from ChatGPT's ability to analyse the sequence of tasks, resource availability, and potential bottlenecks.
ChatGPT can create an optimised construction schedule that minimises delays and maximises resource utilisation.
Identifying and managing potential risks are essential aspects of construction management.
ChatGPT can help analyse project data to identify risks and develop effective risk mitigation strategies.
Example: ChatGPT can evaluate a bridge construction project and identify risks, such as potential delays due to inclement weather, material shortages, or labour disputes.
Based on these identified risks, ChatGPT can suggest mitigation strategies, like procuring materials early or creating backup labour plans.
Clear and concise communication is vital for successful construction management.
ChatGPT can help generate comprehensive progress reports and facilitate team collaboration on shared documents.
Example: A construction project manager overseeing a shopping mall development can utilise ChatGPT to create detailed progress reports.
By providing the AI with organised project data, such as completed tasks, milestones, resource allocation, and budget, ChatGPT can generate a comprehensive report.
It will effectively communicate the project's status to stakeholders, ensuring everyone stays informed and aligned.
Accurate financial management is crucial for construction project success. ChatGPT can help create and manage budgets, as well as analyse financial performance.
Example: For a hospital construction project, ChatGPT can help monitor expenses in real-time.
It compares actual costs against budgeted costs for various categories, such as labour, materials, and equipment.
This allows project managers to identify potential cost overruns and adjust spending as needed.
Efficient inventory management is essential for reducing waste and ensuring timely availability of materials.
ChatGPT can help you automate inventory tracking and optimise procurement processes.
Example: A construction company working on a large infrastructure project can use ChatGPT to create an automated inventory tracking system.
This ensures materials like steel beams, concrete, and pipes are reordered when stock levels reach a predetermined threshold, preventing shortages and delays.
Maintaining high-quality standards and adhering to industry regulations are essential for successful construction projects.
ChatGPT can help you develop quality control checklists and monitor compliance with relevant standards.
Example: A construction company working on a LEED-certified green building can use ChatGPT to develop a quality control checklist.
It can ensure all materials and construction methods adhere to the required environmental standards.
ChatGPT can also help monitor compliance with these standards throughout the project.
Continuous training and skill development are crucial for construction professionals.
ChatGPT can help you create training materials and facilitate knowledge sharing among team members.
Example: A construction firm wanting to train its workforce on lean construction principles can use ChatGPT to create training materials and resources.
Additionally, ChatGPT can help facilitate knowledge sharing among team members, allowing them to learn from each other's experiences and implement best practices.
Construction safety management is crucial for preventing accidents and ensuring a safe working environment. ChatGPT can help you develop comprehensive safety plans and analyse incident reports.
Example: A construction company working on a high-rise building project can use ChatGPT to develop a comprehensive safety plan, addressing hazards such as working at heights, crane operations, and electrical safety.
ChatGPT can also analyse incident reports to identify trends and recommend corrective measures.
A well-planned construction site layout can improve efficiency and safety. ChatGPT can help you analyse site constraints and suggest optimal site layout configurations.
Example: A construction project in a congested urban area can benefit from ChatGPT's ability to analyse site constraints and suggest optimal site layout configurations.
ChatGPT may recommend positioning material storage areas and equipment in locations that maximise space utilisation and minimise safety risks.
Managing change orders efficiently is crucial for minimising delays and cost overruns.
ChatGPT can help you automate change order tracking and evaluate the impact of changes on project timelines and budgets.
Example: For a hotel construction project, a client requests several changes, such as upgrading finishes and modifying room layouts.
ChatGPT can help track these change orders and evaluate their impact on project timelines and budgets, allowing the project manager to make informed decisions and minimise potential delays and cost overruns.
Sustainable construction practices can reduce environmental impacts and improve long-term project outcomes.
ChatGPT can help you identify and implement sustainable practices tailored to your project's specific needs.
Example: A construction company working on an eco-friendly residential complex can use ChatGPT to identify sustainable construction practices, such as using solar panels, green roofs, and energy-efficient windows.
ChatGPT can also help monitor and evaluate the effectiveness of these practices, ensuring optimal environmental performance.
Effective marketing and client relations are vital for attracting new business and maintaining long-term relationships.
ChatGPT can help you create marketing materials and manage client communication.
Example: A construction firm specialising in energy-efficient commercial buildings can use ChatGPT to create marketing materials that highlight their expertise and unique selling points.
ChatGPT can also help manage client communication, drafting and reviewing emails and proposals, ensuring professionalism and clarity, and fostering strong client relationships.
By integrating ChatGPT into your construction management processes, you can unlock numerous benefits, such as streamlining cost estimation, enhancing quantity surveying, improving risk assessment, and optimising site layouts.
Embrace ChatGPT's advanced AI capabilities to elevate your spreadsheet game and achieve more efficient, accurate, and sustainable construction projects.
Here is the article I first produced, written purely by me after experimenting with ChatGPT.
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 ChatGPT.
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 ChatGPT doing this for me. Remember, even if you know how to do the below, you can apply the process to any construction problem you need help with.
Full disclosure, I rarely use spreadsheets anymore and forgot more than I learned. So, I asked ChatGPT 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.
It’s easy to set up your ChatGPT 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.
Sometimes, ChatGPT 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.
Let’s start with a basic example and get more complex as we go.
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 ChatGPT to create this formula for us.
Question: In Excel, if C10 matches C22, say "OK", if not say "ERROR"
Formula: =IF (C10=C22, “OK”, “Error”)
How to highlight cell: Home > Conditional Formatting > Highlight Cell Rules > Equal To. In the "Value1" field, enter "ERROR" and choose the red fill colour.
ChatGPT 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 ChatGPT about that one.
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 ChatGPT how to do this:
Question: On google sheets, give me a formula that lists all the unique values that appear in one column - cells D4-D700
ChatGPT Formula: UNIQUE(D4:D700)
ChatGPT 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 your tool is up-to-date).
Next, take the formula and paste it into your spreadsheet. The full list of products will appear instantly.
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 ChatGPT 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.
Question: Write an excel formula to extract all the text before the @ character in cell B3.
Chat GPT Formula: =LEFT(B3,FIND(“@”,B3)-1)
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.
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 ChatGPT helps.
Question: Write an excel VLOOKUP formula to find 100mm Rockwool insulation on the “Master Price” tab and return the price.
ChatGPT Formula: =VLOOKUP("Rockwool RWA45 100mm", 'Master Price'!A:B, 2, FALSE)
First, ChatGPT 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.
The jury is out on ChatGPT. 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 ChatGPT 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?
July 3, 2023 • 6 min read
June 14, 2023 • 6 min read
June 14, 2023 • 4 min read
June 6, 2023 • 5 min read