14 Ways to Up Your Spreadsheet Game with ChatGPT (The Construction Special)


Solve problems faster with Excel and Google Sheets


 

Want to see what life with the best construction management software on the market looks like?

Watch Archdesk Demos

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.

1. Streamline Cost Estimation 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.

Use ChatGPT to generate formulas for accurate calculations

  1. Input cost data and specifications into your spreadsheet.
  2. Request ChatGPT to generate appropriate formulas by providing a clear and concise prompt, such as "Generate formulas for calculating total costs based on material, labour, and equipment rates."
  3. Review and apply the suggested formulas to your spreadsheet.

Analyse and compare different scenarios

  1. Provide ChatGPT with different cost scenarios or variations, such as fluctuating material prices or changes in labour rates.
  2. Ask ChatGPT to analyse the impact of these scenarios on your project's overall cost and identify the most cost-effective option.
  3. Use the insights to make informed decisions about resource allocation and project planning.

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.

2. Enhance Quantity Surveying with Natural Language Processing

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.

Extract data from construction documents

  1. Input your construction documents into a text format that ChatGPT can process.
  2. Ask ChatGPT to extract relevant data, such as material specifications, quantities, and labour requirements, by providing a specific prompt like "Extract material and labour data from the construction documents."
  3. Incorporate the extracted data into your spreadsheets for further analysis and calculations.

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.

Automate calculations for materials and labour requirements

  1. Use the data extracted from construction documents in your spreadsheet.
  2. Request ChatGPT to generate formulas to calculate the required materials and labour based on project specifications.
  3. Apply the formulas to your spreadsheet, streamlining your quantity surveying process.

3. Simplify Project Scheduling and Resource Allocation

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.

Create Gantt charts and calendars with ChatGPT

  1. Input your project timeline, milestones, and tasks into your spreadsheet.
  2. Request ChatGPT to generate a Gantt chart or calendar by providing a prompt like "Create a Gantt chart based on the project timeline and tasks."
  3. Review and incorporate the generated visual tools into your project management process to track progress and deadlines efficiently.

Optimise resource allocation with ChatGPT's suggestions

  1. Provide ChatGPT with information on available resources, such as personnel, equipment, and materials.
  2. Ask ChatGPT for suggestions on optimising resource allocation by using a prompt like "Suggest an optimised resource allocation plan for the construction project."
  3. Evaluate the proposed resource allocation plan and implement the changes to improve project efficiency and reduce potential bottlenecks.

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.

4. Improve Risk Assessment and Mitigation Strategies

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.

Identify potential risks using ChatGPT's analysis

  1. Input project data, including schedules, budgets, and resources, into your spreadsheet.
  2. Ask ChatGPT to analyse the data and identify potential risks by providing a prompt like "analyse project data and identify potential risks."
  3. Review the identified risks and incorporate them into your project risk management plan.

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.

Develop risk mitigation strategies with ChatGPT's assistance

  1. Discuss the identified risks with ChatGPT and request suggestions for mitigation strategies using a prompt like "Suggest risk mitigation strategies for the identified risks."
  2. Evaluate the proposed strategies and implement the most suitable measures to minimise potential risks in your construction project.
  3. Continuously update and refine your risk management plan based on ChatGPT's insights.

5. Enhance Project Communication and Collaboration

Clear and concise communication is vital for successful construction management. 

ChatGPT can help generate comprehensive progress reports and facilitate team collaboration on shared documents.

Generate comprehensive progress reports using ChatGPT

  1. Compile project data, including completed tasks, ongoing activities, and upcoming milestones, into your spreadsheet.
  2. Request ChatGPT to create a progress report by providing a prompt like "Generate a progress report based on the project data."
  3. Review and distribute the generated progress report to relevant stakeholders, ensuring effective communication and timely decision-making.

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.

Use ChatGPT to facilitate team collaboration on shared documents

  1. Share your spreadsheet with team members, allowing them to make edits and updates.
  2. Request ChatGPT to monitor and track changes made by collaborators by providing a prompt like "Track changes in the shared spreadsheet and provide a summary of updates."
  3. Use ChatGPT's summary to review changes and maintain clear communication among team members, ensuring efficient collaboration.

6. Boost Financial Management and Budgeting

Accurate financial management is crucial for construction project success. ChatGPT can help create and manage budgets, as well as analyse financial performance.

Automate budget creation and tracking with ChatGPT

  1. Input cost estimation data and financial information into your spreadsheet.
  2. Ask ChatGPT to create a budget by providing a prompt like "Create a project budget based on the provided financial data."
  3. Use ChatGPT's suggested budget to monitor and track project expenses, ensuring accurate financial management throughout the project.

Analyse financial performance with ChatGPT's insights

  1. Regularly update your spreadsheet with actual project expenses and financial data.
  2. Request ChatGPT to analyse the data and provide insights into financial performance by using a prompt like "analyse the project's financial performance and identify areas for improvement." 3. Review ChatGPT's insights and recommendations, and implement necessary changes to optimise your financial management practices.

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.

7. Automate Inventory Management for Construction Projects

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.

Use ChatGPT to create an automated inventory tracking system

  1. Input your current inventory data, including materials, quantities, and reorder levels, into your spreadsheet.
  2. Request ChatGPT to generate an automated inventory tracking system by providing a prompt like "Create an automated inventory tracking system based on the provided data."
  3. Implement the generated system to monitor inventory levels and reorder materials as needed, reducing waste and ensuring timely availability.

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.

optimise procurement processes using ChatGPT's insights

  1. Input data on suppliers, lead times, and pricing into your spreadsheet.
  2. Ask ChatGPT for suggestions to optimise procurement processes by using a prompt like "Suggest ways to optimise procurement processes based on the supplier data."
  3. Evaluate ChatGPT's recommendations and implement changes to streamline procurement and reduce costs.

8. Enhance Quality Control and Compliance Monitoring

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.

Utilise ChatGPT to develop quality control checklists and procedures

  1. Discuss your project's specific quality requirements and standards with ChatGPT.
  2. Request ChatGPT to create a customised quality control checklist by providing a prompt like "Create a quality control checklist tailored to the project's requirements and standards."
  3. Implement the generated checklist to ensure consistent quality control throughout the construction process.

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.

Monitor compliance with industry standards and regulations using ChatGPT

  1. Input data on industry standards and regulations relevant to your construction project into your spreadsheet.
  2. Ask ChatGPT to track and analyse compliance data by providing a prompt like "Monitor and analyse compliance with industry standards and regulations based on the provided data."
  3. Review ChatGPT's analysis and implement necessary changes to maintain regulatory compliance.

9. Leverage ChatGPT for Training and Skill Development

Continuous training and skill development are crucial for construction professionals. 

ChatGPT can help you create training materials and facilitate knowledge sharing among team members.

Develop training materials and resources with ChatGPT's help

  1. Identify the training topics and objectives relevant to your construction team.
  2. Request ChatGPT to create training materials by providing a prompt like "Create training materials on the identified topics for the construction team."
  3. Review and utilise the generated training materials to enhance your team's skill set and improve project outcomes.

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.

Facilitate knowledge sharing and best practices using ChatGPT

  1. Encourage team members to share their experiences and insights from construction projects.
  2. Request ChatGPT to compile and analyse this information by providing a prompt like "analyse team members' experiences and insights and identify best practices for the construction industry."
  3. Share and implement the identified best practices within your team to improve overall performance and project outcomes.

10. Enhance Construction Safety Management

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.

Develop construction safety plans with ChatGPT's assistance

  1. Discuss your project's specific safety requirements and potential hazards with ChatGPT.
  2. Request ChatGPT to create a customised construction safety plan by providing a prompt like "Develop a construction safety plan addressing the project's specific requirements and potential hazards."
  3. Implement the generated safety plan to maintain a safe working environment and minimise accidents.

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.

Analyse incident reports and implement corrective measures with ChatGPT

  1. Input incident report data, including details of accidents and near-misses, into your spreadsheet.
  2. Ask ChatGPT to analyse the data and identify trends or areas of concern by providing a prompt like "analyse incident reports and identify trends or areas requiring corrective measures."
  3. Review ChatGPT's analysis and implement necessary corrective measures to improve safety and prevent future incidents.

11. Optimise Construction Site Layout Planning

A well-planned construction site layout can improve efficiency and safety. ChatGPT can help you analyse site constraints and suggest optimal site layout configurations.

analyse site constraints with ChatGPT

  1. Input data on your construction site's dimensions, terrain, access points, and other relevant factors into your spreadsheet.
  2. Request ChatGPT to analyse site constraints by providing a prompt like "analyse the construction site constraints based on the provided data."
  3. Review the analysis to better understand the site's limitations and opportunities for optimization.

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.

Generate optimal site layout configurations using ChatGPT

  1. Discuss your project's requirements and site constraints with ChatGPT.
  2. Ask ChatGPT to suggest optimal site layout configurations by providing a prompt like "Suggest optimal site layout configurations based on the project requirements and site constraints."
  3. Evaluate the proposed configurations and implement the most suitable layout to enhance construction site efficiency and safety.

12. Streamline Change Order Management

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.

Automate change order tracking with ChatGPT

  1. Input change order data, including descriptions, costs, and impacts on project schedules, into your spreadsheet.
  2. Request ChatGPT to create an automated change order tracking system by providing a prompt like "Develop an automated change order tracking system based on the provided data."
  3. Use the generated system to monitor change orders and their impact on your construction project.

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.

Evaluate the impact of change orders on project timelines and budgets

  1. Update your spreadsheet with new change orders and their associated costs and schedule impacts.
  2. Ask ChatGPT to analyse the impact of change orders on project timelines and budgets by providing a prompt like "analyse the impact of change orders on project timelines and budgets."
  3. Review ChatGPT's analysis and adjust your project plans accordingly to minimise the negative effects of change orders on your construction project.

13. Implement Sustainable Construction Practices with ChatGPT's Guidance

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.

Identify sustainable construction practices with ChatGPT's help

  1. Discuss your project's environmental goals and constraints with ChatGPT.
  2. Request ChatGPT to suggest sustainable construction practices by providing a prompt like "Suggest sustainable construction practices suitable for the project's environmental goals and constraints."
  3. Review and incorporate the suggested practices into your construction project to minimise environmental impacts and promote sustainability.

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.

Monitor and evaluate the effectiveness of sustainable practices

  1. Track the implementation and outcomes of sustainable construction practices in your spreadsheet.
  2. Ask ChatGPT to analyse the data and evaluate the effectiveness of the implemented practices by providing a prompt like "analyse the effectiveness of implemented sustainable construction practices."
  3. Review ChatGPT's analysis and adjust your sustainability strategies as needed to optimise environmental performance.

14. Leverage ChatGPT for Construction Marketing and Client Relations

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.

Create marketing materials with ChatGPT's assistance

  1. Discuss your construction company's unique selling points, services, and target audience with ChatGPT.
  2. Request ChatGPT to create marketing materials, such as brochures or presentations, by providing a prompt like "Design marketing materials showcasing our construction services and unique selling points."
  3. Review and use the generated materials to promote your construction company and attract new clients.

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.

Manage client communication using ChatGPT

  1. Integrate ChatGPT into your email and messaging systems to assist with client communication.
  2. Use ChatGPT to draft and review emails, proposals, and other client communications, ensuring professionalism and clarity.
  3. Leverage ChatGPT's natural language processing capabilities to analyse client feedback and inform your ongoing client relationship management strategies.

Conclusion

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.

Set up your ChatGPT account

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.

Get around the “ChatGPT is at capacity right now” issue

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.

4 ways ChatGPT 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 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.

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 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. 

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 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.

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 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. 

What do you think about ChatGPT?

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?

Headshot of Danny Mitchell, Archdesk's Head of Content Marketing

Danny Mitchell
Head of Content Marketing

From estimating to journalism to helping construction pros make the most of tech.
 
The Construction Digital Playbook We're launching a newsletter next week

Sign up today for tips on using free digital tools like Chat GPT and Canva.

You might also like

Find a solution tailored for your business.

 
 
Get a Demo
2024-07-20 07:33:49