Exploring AI’s Role in Building BusinessLevel Spreadsheets: What All Excel Users Should Know.
We built ‘ realworld’ Spreadsheets using AI prompts and here are the Results.
Article Structure :
 Introduction: Can AI help build ‘real life’ business Spreadsheets and boost productivity.
 List of the practical spreadsheets solutions we will build .
 Master ChatGPT prompting, especially for Excel users.
 ChatGPT Prompt Template for building Excel spreadsheets with AI.
 Checklist for using AI with Spreadsheet.
 Building the Spreadsheets using ChatGPT prompts.
 Videos for each of the Spreadsheet Solution.
 Quick 5 minute Test to see if it’s safe for you or your team members to use Excel with AI.
 Conclusion & Summary Benefits & Its limitations in detail.
Please note we will be using ChatGPT version GPT4 free version (late 2024 )to test and build these spreadsheet models.
In this article, we explore how AI, especially ChatGPT, is transforming the way we use Microsoft Excel . In particular, its ability to create practical, ‘realworld’ solutions for everyday workflows .
We have felt the need to update our Online Excel Courses.
In response to the evolving landscape of artificial intelligence, we have updated both our Online Excel Courses and instructorled Excel courses. The new curriculum includes a new level on ‘Using Excel with AI” but the existing courses have changed to emphasizes a deeper comprehension of Excel’s fundamental concepts, logic, syntax and mechanics especially in relation to :
 Relative/absolute references.
 Table structures.
 Linking sheets and workbooks.
 Formatting : Percentages, Dates, Times
 Building formulas with math, logical operators , order of precedence
 The Big Workhorse functions
 Pivot tables structures.
By mastering these core mechanics and syntax and integrating ChatGPT prompting with Excel in mind, Excel users can significantly boost productivity and efficiency while remaining equipped to identify errors and understand the limitations of AIdriven assistance.
If you have ever completed an Excel course, you will probably know this feeling ?
If you’ve completed an Excel course, you will likely understand the difference between knowing functions/ formulas and between trying to combine the functions, concepts and logics together to create complete working spreadsheet solutions.
This is where things get tricky, and it’s often where users struggle the most.
Excel’s true power lies in integrating functions, techniques, to create real life useful spreadsheets . ChatGPT, within its limits, can step in here, offering significant support in building these solutions. It can also review existing formulas, suggest more efficient workflows, and flag potential errors.
We will try to build ‘ realworld’ everyday Business Spreadsheets using AI prompts.
In everyday office scenarios, spreadsheets are not just used for basic calculations; they’re used to automate workflows, track performance, and make informed decisions. Here are a few examples of common, ‘real life’ business spreadsheets challenges:
 Tracking Partial Payments with Conditions and Highlighting Completed Records.
You’re managing customer payments. You need to track partial payments and the payment needs to be marked as “Completed ” in another column. Your solution needs to combine SUMIFS ,conditional logic and formatting formulas, data validation and understanding relative and absolute references.
–  Search & Compare Different lists , Process them and Highlight Certain Records .
Building this practical spreadsheet could involve lookups , linking between worksheets, maybe INDEX & MATCH Functions ,logic operators, references and conditional formatting.
–  Marking Task Completed Based on Multiple Criteria before an action(s) happen.
You need to build a data tracking spreadsheet . A task should only be marked as “Completed” if certain conditions are met: full payments made, the deadline has passed, all subtasks are finished. This spreadsheet involves using IF statements, XLOOKUPS, logical operators like AND, relative and absolute references, checking multiple columns and pulling data from other sheets, even other workbooks.
–  Cross Footing to Check for Calculation Errors and Give Warning.
We have contractors working in two offices office A and office B and on different projects. I want to total up the number of hours worked by each office , each contractor, each project and each location . But I also need checks in place to warn of possible errors.
Can ChatGPT Can Help ?
This is where ChatGPT can step in. Instead of explaining individual formulas, ChatGPT can help you combine functions and techniques to build complete solutions. We will use the examples above and see if AI might guide you in solving them quickly and effectively.
But first you need to learn how to write effective ChatGPT prompts and specifically how to use and adapt them when using Microsoft Excel.
A Quick Course on Build ChatGPT Prompts with Emphasis on Excel
Context is King.
For AI, context is everything and without it Chat GPT won’t be of much help.
Effective prompts always have the same basic structure: First, the prompt tell the AI what role it should play. Then the prompt tells the AI what data the user has provided, followed by what the AI is supposed to do with it, including directions . This is the essence of every good prompt.
So two key principles for successful prompting are
 Writing clear and specific instructions.
 Guide the language models towards sequential reasoning.
For instance, if you’re asking ChatGPT to help with an Excel solution, it’s beneficial to provide details such as the type of data you’re working with, the desired calculation and the structure of your spreadsheet. Copy and paste the header and the first few rows from the spreadsheet into your prompt. This will allow ChatGPT to get a sense of what each column is and what kind of data it holds.
Assign a Persona.
It can also be advantageous to assign the model a specific role, tailored to the task at hand. For example, you can start your prompt with the following role assignments:
 You are an experienced Excel trainer who simplifies complex concepts into easily understandable content.
 You are a seasoned Excel user with 15 year’s experience in building complex Microsoft Excel formulas and functions.
Building up a Basic Excel Prompt.
Now to get a basic understanding of how prompt engineering works in the round , how can we can improve a vague request, such as “How do I use Excel to build a pivot table ‘ into a more specific and useful prompt.
 Clarify the Focus of Your Request.
Be specific about the task you need help in Excel with. Example: “How do I calculate total revenues for each product using a pivot table?
 Role: You are a Pivot table expert.
 Define the Format of the Response.
Decide whether you need detailed steps, a formula explanation, or just a quick summary.
Example: “You are a Pivot table expert. Give me a stepbystep guide to calculate total revenues for each product using a pivot table ”  Set the Tone and Style
Tailor the response to your level of understanding. Do you need a beginnerfriendly guide or a more technical explanation?
Example:“You are a Pivot Table expert. Give me a stepbystep guide to calculate total revenues for each product using a pivot table , using simple language for someone new to Excel.”
 Describe your data in detail with sample data ,include Excel Version. Example:
“You are a Pivot Table expert. Give me a stepbystep guide to calculate total revenues for each product using a pivot table, using simple language for someone new to Excel. Please find a copy of my data’s
headers and the first few rows below. I am using Excel 365.“””
paste data
“””
By crafting your prompts this way, you’re response will be more accurate.
Ask for Structured Output.
You can specify how the output should be structured to suit different formats like Excel tables, CSV, or bullet points, making the data easy to work with.
Table Format: Example: “Generate a table with columns for Product ID, Product Name, Sales, and Revenue.”
CSV (CommaSeparated Values): Example: “Generate a list of products in CSV format with columns for ID, Name, Sales, and Price.”
TSV (TabSeparated Values): Example: “Provide sales data in TSV format with columns for ID, Product, and Sales.”
These output formats ensure data is ready to be used or imported into Excel for further analysis.
Guide LLM Towards Sequential Reasoning.
Now the second main principle in crating effective prompts is to give ChatGPT “ time to reason”. Let’s explore some techniques to guide ChatGPT towards stepbystep sequential thinking and problem solving, particularly when creating Excel solutions.
 Few Shot’ Prompting.
By providing a number of examples before asking the model to perform, you can guide the model toward your desired outcome.
 Chain of though Prompting.
Tell the prompt to think and solve it step by step by adding this text to the prompt – ‘Let’s think step by step on this.’
 Subtask Prompting:
For complex assignments that can be broken down into several smaller tasks, specifying these smaller tasks in the prompt can enhance the reliability of the response.
 Directional Stimulus Prompting.
This is where we ‘nudge’ ChatGPT in the direction we want it to go or do. We have found this to be particularly important when creating prompts for Microsoft Excel.
 Tree of Thought Prompting.
We can build on assigning a specific persona to ChatGPT, by assigning a team of experts to solve your problem. As a first step, ChatGPT can help you create a list of the experts you require. It can then run a simulation in which each of the fictional expert give their solution.
Prompt Template For Spreadsheets.
Below is the outline of our Prompt Template For Excel . You can download the full version at https://onlineexceltraining.com/prompt_template/ Feel free to pass it on to your team members, and friends. Also to learn more about the Prompt Template for Spreadsheets, watch the video here.
General Problem Overview
1.Task Description:
Clearly describe the overall task or problem you’re encountering in Excel. Be specific about the action or result you’re trying to achieve.
2.Dataset Context and Structure
3.Dataset Description:
Offer a detailed description of your dataset, including column headers, number of rows, data types, and any relevant cell ranges.
4.Conditions and Constraints:
Mention any specific rules or conditions that apply to your data, such as specific
5.[ Advanced Prompting Techniques: FewShot Examples for Guidance]
[ChainofThought (CoT) Reasoning]
[TreeofThought (ToT) Exploration]
Specific Excel Tools, Functions, or Features
6.Expected Outcome (Precision and Directional Prompting)
7.Sample Data and Error Details
8. Version and Platform Information
CheckList for Writing Spreadsheet Prompts.
Now from our experiments creating prompts to build Microsoft Excel spreadsheets , here is a brief checklist for you. The full checklist is available at https://onlineexceltraining.com/checklistprompts/


Supply very detailed context of both the data and the problem. Present information clearly , logically in a step by step manner. In particular, supply a clear description of any datasets: Also specifying the structure of the data (e.g., number formats, types of data like text, numbers, dates)

Breakdown of Requirements: Instead of a block of text, list the requirements step by step for better clarity. Prioritize the order of operations if the task has multiple steps.

Use directional nudging. ChatGPT often needs to be nudged in the correct direction, often many times.
 Paste in a copy of your headers and at least 3 rows of your data at end of prompt with delimiters like “”” data “”

We have found that ChatGPT often ignores relative and absolute referencing and does not always recognize Excel tables .So we always include, when it required in the solution , the text: ‘ Please allow for proper application of relative and absolute references when copying formulas’. And if using tables , ‘I want a solution that leverages Excel structured references since these are Excel Tables’

If you know which functions should to use , let the prompt know. The more directions , it is given , the better. This helps it overcome one of its biggest weaknesses, not supplying the most efficient optimal solution.

If possible ,give the prompt an example or examples to follow as in few shot prompting , which is very effective for excel type prompts.

Emphasize and repeat the verbal action you want and we have found it best to start the sentence with the verb. Compare the lists, Count the items, Extract, Calculate etc. They not only sets direction but avoids ambiguity.

Don’t be surprised that you get slightly different answers from using the same prompt. Remember ChatGPT is large language model. This means, basically , it is just matching patterns. It does not understand the meaning of what you are doing.

Also remember AI models like ChatGPT are not search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt. Evaluate the clarity of your instructions. Check it against our template and this checklist. Has the model had enough time to “reason ”. Try to identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task.Ask for Testing Instructions

Once a solution is provided, ask for instructions on how to test or verify its effectiveness. For example: “How can I test if this SUMIFS formula works correctly with different inputs?”

If the task involves ambiguous data (e.g., inconsistent text, missing values), ask ChatGPT to account for this. Example: “Please account for inconsistent text in the data before applying formulas.”

It’s important to prompt ChatGPT to consider edge cases like empty cells, zero values, or invalid data entries. For instance: “Include a solution that handles empty cells or invalid inputs gracefully without causing errors in the spreadsheet.

Then Check for these Common Pitfalls:
 Vague Problem Descriptions: Be specific about what you’re trying to achieve.
 Lack of Data Context: Without understanding the structure of your data, solutions may not fit.
 Assuming a Specific Solution: Don’t assume a specific formula or method will solve your problem (e.g., insisting on using VLOOKUP when XLOOKUP or another method might be better).
 Leaving out Key Details: Include error messages, platform versions, and expected results.
 Oversimplification or Overcomplication: Avoid leaving out key data structure details, but also don’t overwhelm with irrelevant details.
Building Practical Spreadsheets using our ChatGPT Prompt Template.
Watch the Spreadsheet and Prompt been built or read the text version below.
First Spreadsheet Problem:
Lets start off with a more straightforward example . We have a list of artists played on a radio station. Each artist is entitled to a royalty of €2 per minute each time their song is played. Our task is to calculate how much each artist is owed.
The main challenges we face are:
 Extracting a unique list of artists from this large dataset, which contains many duplicate entries.

Summing the total minutes each artist was played, then calculating the royalties by multiplying the time by the €2 rate.
Can ChatGPT help me create a solution to calculate the total royalties for each artist?
The Process.
First, let’s look at some possible solutions. Then we will write our prompt and compare ChatGPT solutions to ours.
In relation to getting a unique list of artists there are a number of ways to do this in excel. We could use the ‘remove duplicates tool in the data ribbon .We could use a pivot table or as we are using excel 365 we can use the UNIQUE function.
5To add up the total minutes played for each artist , the SUMIF OR SUMIFS Function can be used for this.
What is key in this solution is getting the relative and absolute references right. We will be looking very carefully at ChatGPT solution especially in relation to the relative and absolute references.
So let’s build our prompt. It’s based, of course on our Prompts Template for Excel Solutions.
Prompt:
“You are an Excel expert. I have a dataset in the range B3:E954
, which includes Song Number, Singer, Date, and Minutes played. In cell H1, the royalty fee per minute is set at €2.
Each artist is paid €2 for every minute their songs are played. The ‘Singer’ column which is column C contains duplicate entries as some artists have multiple songs. I need you to create a formula that:
 Extracts a unique list of artists (Singers) starting in cell G3.

Calculates the total royalty due to each artist, based on the sum of minutes played for all their songs, and displays the corresponding total royalty in cell H3 and be copied down.
Please provide the formula(s) necessary for this. No need for explanations. The full list range is C3:C954. Also Please allow for proper application of relative and absolute references when copying formulas down. Note: I am using Excel 365.”
“””
Song Number  Singer  Date  Minutes 
1  Eminem  21/05/2024  4 
2  Eminem  15/04/2024  2 
3  Cher  28/01/2023  2 
“””
Let’s have a look at this prompt.
Firstly, we are giving the prompt its role as an Excel Expert. We are then describing our dataset in detail, both its location and also the headings. We have also attached a sample at the end of the prompt. We also describe exactly where the royalty fee is located.
Next , we are describing in detail the task we want the prompt to do . Its written in a logical , clear , step by step approach . Again, we are reminding it of the column names and where they are located column wise.
Finally at the end , we are summarizing what we want it to do. Also important to note, we’re reminding and emphasizing to the prompt to allow for full relative and absolute referencing. This is very important . Remember ChatGPT does not understand the meaning of what we’re asking it. It is simply matching patterns.
Also, I only want the formulas, hence I have written ‘No need for explanations’. Leave this out , if you want full explanations for each formula. Finally we note the version of excel.
ChatGPT Response
It does very well even though, it is a quite straightforward problem.
=UNIQUE(C4:C954)
=SUMIF($C$4:$C$954,G3,$E$4:$E$954)*H$1
It’s giving the best solution for the version we told it we are using , namely Excel 365. It uses the UNIQUE and the SUMIF functionS and most importantly it gets the absolute and relative references totally correct ,which is the key to this particular problem.
So as off late 20204, for accurate responses from ChatGPT , it’s important to specify the context in detail and clearly , to use directional nudging and remind it to apply relative and absolute referencing/ table structure.
Always, always double check the results of the suggested formulas. To do this, you can incorporate it into your spreadsheet and make sure the results are what you are looking for. If they aren’t, feeding Chat GPT the expected result or even your workings, might be all it needs to fix the formula. Remember it’s built for dialogue.
Second Spreadsheet Problem & Solution:
Full Data Tracking System for Partial Payments.
Watch the Spreadsheet and Prompt been built.
General:
As per image , I need to calculate the total payments made by customers in various amounts, and I want Excel to highlight records when certain conditions are met.
Specific:
Clients are paying for their holiday trip in instalments, and we track each payment in a table called payments. In column E, I need to display the total payment for each client. Also In the “Complete” column (H), we need the spreadsheet to display a “C” if the customer has paid in total, signed their itinerary form and paid their insurance fee.
Next, we want to highlight every record when those same conditions have been meet. Can ChatGPT provide a complete solution for this?
The Process:
Let’s look at some possible solutions then we will write our prompt and compare ChatGPT solutions to ours.
So to calculate ‘Total Fees, SUMIF will do.
=SUMIF(Payments[Attending],[@Accepted],Payments[Payment])
It will use the Attending column in the payments table , the criteria will be the name in B4, Alo and it will add up all payments from Alo in the payments table. ChatGPT might also use the SUMIFS Function.
In the “Complete” column (H), a formula like this could be used
=IF(AND(COUNTA(holidays[@[Travel Itinerary]:[Insurance ]])=2,[@[Total Fees Paid]]=[@FEE]),”C”,””)
The COUNTA function will count the entries in cells F9 and G9 to see if both contain an “x” (indicating completion), and then verify that the total fee matches the required amount. If all conditions are satisfied, the formula will place a “C” in the cell.
For the conditional formatting formula:
=AND($D4=$E4, $F4=”x”, $G4=”x”, $H4=”C”)
will work. ChatGPT hopefully, if we write our prompt correctly, will use standard direct references and also get the relative and absolute referencing totally right.
So let’s build our prompt . Its based , of course, on our ChatGPT Prompts Template for building spreadsheet . So from using this template, here is our prompt.
The Prompt:
“I have two Excel tables:
 holidays table

Contains seven columns. The fourth column (E3) is labelled ‘Total Fees Paid’. Client names are in the first column (B3) and is labelled “Accepted”.

 Payments table

Contains two columns: ‘Attending’ (K5) and ‘Payment’ (L5). The ‘Attending’ column has client names, and ‘Payment’ records the payment amounts.

Task 1:
I need a formula for the ‘Total Fees Paid’ column (in the holidays table) that sums the payments from the payments table for each client (matching by client name). Use Excel structured references since these are Excel tables.
Task 2:
In the “Complete” column (H), we need the spreadsheet to display a “C” if the customer has met all the following conditions:
 The total fee has been paid.
 The travel itinerary has been signed. (there is an ‘x’ in the cell)
 The insurance payment is complete. (there is an ‘x’ in the cell)
Can you create a formula to do this.
Task 3:
For each record in the holidays table (starting from row 4):
 Check if the values in the ‘Total Fees Paid’ column (E) and the ‘Fee’ column (D) are equal.
 Check if there is an “x” in the ‘Travel Itinerary’ column (F) and the ‘Insurance’ column (G).
 Check if there is a “C” in the ‘COMPLETE’ column (H).
If all conditions are met, apply conditional formatting to the row. Please provide a formula using standard cell references (not table references) and ensure proper use of relative and absolute references. No need for explanations. The headers are in row 3, and the first record starts in row 4.” I am using Excel 365.
“””
Accepted  City  FEE  Total Fees Paid  Travel Itinerary  Insurance  COMPLETE 
Alo  Dublin  1500  x  x  
Ashleigh  Dublin  1500  
Beverly  London  1300 
Attending  Payment 
Alo  1500 
Lorraine  1000 
Lorraine  300 
“””
How the Prompt was built?
Let’s have a look at this prompt and see how it is constructed. We describe the dataset in detail, both its location and its headings. We also attached a sample at the end of the prompt.
Next, we describe in detail the tasks we want the prompt to do .The three tasks are written in a logical , clear , step by step manner . Again, we are reminding the prompt of the column names and where they are located column wise .We let it know we are dealing with tables and use the term ‘Use Excel structured references since these are Excel tables”.
The conditions in Task 2 and 3 are laid out clearly. Again, note the important nudging techniques term ‘Please provide a formula using standard cell references (not table references’. We don’t want ChatGPT to use tables structure in the conditional format formula .Excel’s conditional formatting doesn’t handle structured references the same way as normal formulas. And to make ensure correct references are applied , we added “ensure proper use of relative and absolute references.”
Remember ChatGPT does not understand the meaning of what we’re asking it to do . It is simply matching patterns.
Now I only want the prompt to return the formulas, hence I have written ‘No need for explanations’. Leave this out , if you want full explanations for each formula. Its ends up with the Excel Version we are using. Again, very important when building spreadsheet prompts. A sample of the data is then added to the end of the prompt with delimiters.
ChatGPT Response:
=SUMIFS(payments[Payment], payments[Attending], [@Accepted])
While we used the SUMIF function, it used the SUMIFS function , which is fine and also correct. It uses the correct excel table syntax between both the holiday and payments tables.
=IF(AND([@Total Fees Paid]=[@FEE], [@Travel Itinerary]=”x”, [@Insurance]=”x”), “C”, “”)
Now for the H column, the “complete” column, it uses a slightly different formula than us. We used the COUNTA function , but it has used a more direct approach and probably a better approach and it work fine.
=AND($E4=$D4, $F4=”x”, $G4=”x”, $H4=”C”)
For the conditional format formula, again, it is correct . It is using standard cell referencing, the correct cells references and most importantly, the relative and absolute referencing are applied properly.
It should be noted that when we left out ‘and I want a solution that leverages Excel structured references since these are Excel Tables“, the majority of the responses from ChatGPT did not recognize that we were using Excel Tables.
Third Spreadsheet Problem & Solution:
Comparing Two Lists & Processing the Data.
Video of this Spreadsheet and Prompt been built.
The Problem:
We run a club and we have 2 lists called paid members and one called overdue members.
The paid members list is the latest updated list showing us all the members that have paid their current membership fees. And list 2, Overdue Members , are the members who have overdue payments.
We want to: Compare both lists to identify:
 Which overdue members have now paid their fees i.e., members who appear in both lists .
 Also I want to identify which members are still overdue , appear in the overdue list but not in the paid list.
 Finally I want to identify members who paid their fees on time , that is they appear only in the paid list, not in the overdue list.
I also now want , after getting this updated payments list , to calculate the total fees paid by members who were overdue but have now paid. I will put this formula in this cell here.
And finally, as you can see , I want to apply conditional formatting to visually highlight the various payment positions:
 We will color Green members who have paid their fees and were overdue that is they appear in both lists.
 We will color Red Members who are still overdue that is they are only in the overdue list.
 And finally we will color in Blue Members who paid on time that is they appear only in the paid list.
Possible Solutions:
Now, we will look at possible solutions , which we will then compare with the solutions offered by ChatGPT. The main task could be completed by using a VLOOKUP Functions like this one here. But a MATCH function could also be used.
=IF(NOT(ISERROR(VLOOKUP(A4, $E$4:$E$8, 1, FALSE))), “Paid Overdue”, “Paid On Time”)
Now to Calculate the Total Fees Paid by Overdue Members , we could use a SUMIFS or a SUMIF function like this one here.
=SUMIFS($B$4:$B$13, $C$4:$C$13,”Paid Overdue”)
Finally, let us look at possible formulas we can use to apply the conditional formats .
‘=$C4=”Paid Overdue”
=$C4=”Paid On Time”
=ISNA(VLOOKUP(E4, $A$4:$A$13, 1, FALSE))
CheckList for Excel Prompts.
Now ,before we build the prompt for this example , certain prompting techniques stand out , especially when building prompts for Excel , as of late 2024.

Supply very detailed context in terms of both the data and the problem. Present information clearly and logically. In particular, supply a Clear Description of any datasets, both column headings and range descriptions. Paste samples at end with delimiters.
 Breakdown of Requirements: Instead of a block of text, list the requirements step by step for better clarity.
 Use directional nudging. ChatGPT often needs to be nudged in the correct direction . For example, copy the formula down the range A2:A10.

We have found that ChatGPT often ignores relative and absolute referencing and does not always recognize Excel tables . So we always include, when it is required in the solution , the text:
‘ Please allow for proper application of relative and absolute references when copying formulas’.
And if using tables , ‘I want a solution that leverages Excel structured references since these are Excel Tables’ 
If you want the prompt to use certain functions , let the prompt know. The more directions it gets, the better. This helps it overcome one of its biggest weaknesses, not supplying he most efficient optimal solution.

Emphasize and repeat the verbal action you want and we have found it best to start the sentence with the verb . “Compare the lists”, “Count the items” etc

Don’t be surprised that you get slightly different answers from using the same prompt. Remember ChatGPT is large language model, it is just matching patterns, it does not understand the meaning of what you are doing.

Remember AI models like ChatGPT are not search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt. Evaluate the clarity of your instructions. Check it against our template and checklist.
Has the model had enough time to “reason ” and identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task
Our Prompt
“I have two lists of club members in Excel:
 A current list of paid members in the range A4 to B8
 A list of overdue members in the range E3 to E8
I need to generate formulas that:
 Directly compare both lists (using VLOOKUP, MATCH or similar) to identify which overdue members have now paid their fees. If a member is found on both lists (i.e., they were previously overdue but have now paid), label them ‘Paid Overdue’. If a member is only found in the paid list (i.e., they paid on time and were never overdue), label them ‘Paid On Time’. The comparison result should go into cell C4 and be copied down.

Calculate the total subscription fees paid by members who were previously overdue but have now paid.
 Apply conditional formatting with the following rules:
Green: members who were overdue but have now paid (appear in both lists).
Blue: members who paid on time (only appear in the paid list)
Red: members who are still overdue (only appear in the overdue list, but not the paid list).
Please provide the exact formulas. Use the comparison result in column C directly for conditional formatting . Use Direct cell references as much as possible. Ensue proper use of relative and absolute references when copying.”. Please supply the required formulas, including the formula to COMPARE the two lists , in tabular form. There is no need for explanations. I am using Excel 2019.
ChatGPT Response.
Let’s input the prompt into ChatGPT and let’s see what it comes up with.
And here are its solutions.
=IF(ISNUMBER(MATCH(A4,$E$3:$E$13,0)),”Paid Overdue”,”Paid On Time”)
Now it is using a Match function .We used the VLOOKUP function but both are suitable for this task and work correctly.
=SUMIFS($B$4:$B$13, $C$4:$C$13,”Paid Overdue”)
We can see that it used the SUMIFS Function which of course is fine. And the formulas for conditional formatting are exactly the same as the ones that we had created ourselves , except , again, is used the MATCH function , which is fine.
So the response from ChatGPT has been very good but the importance of accurate prompting should be noted in getting it to work accurately. But even if it does not work properly the first time, remember prompt Writing is an Iterative Process.
AI models like ChatGPT are not search engines – they’re designed for dialogue. If an initial prompt doesn’t yield the expected result, refine the prompt.
Evaluate the clarity of your instructions. Check it against our template and checklist. Has the model had enough time to “reason ” and identify any potentially misleading elements in the prompt. The key to success is to iteratively refine your prompt, improving its effectiveness with each iteration to best suit your task.
Fourth Spreadsheet Problem & Solution.
Reconciling & Highlighting Payments System.
The Problem.
General:
Summing Values Based on Conditions and Highlighting them. This type of conditional summing is crucial in tasks like reconciling payments.
Specific:
We need to sum payments over $500, but only if the payment status in another column is marked as ‘Approved’ and the date in another column is greater than 31/12/2023. Also I want all records that match these three criteria highlighted.
The Prompt:
“You are an Excel expert. I have a data range from A1:D18 , with the headers: ‘Payment Id’, ‘Payment’, ‘Status’, and ‘Payment Date’. I need a formula in Excel 365 that sums the values in the ‘Payment’ column (B) only when:
 The ‘Payment’ is greater than $500,
 The ‘Status’ is marked as ‘Approved’ in column C, and
 The ‘Payment Date’ in column D is after December 31, 2023.
Additionally, I want to apply conditional formatting to highlight records that meet all these criteria. Can you write the formula I need for this conditional formatting. What potential issues could prevent this formula from working correctly, and how can I address them?
ChatGPT Response:
=SUMIFS(B2:B18, B2:B18, “>500”, C2:C18, “Approved”, D2:D18, “>31/12/2023”)
=AND($B2>500, $C2=”Approved”, $D2>DATE(2023,12,31))
Both formula when pasted onto our spreadsheet , work effectively. A very good response from AI.
In terms of potential Issues and solutions, ChatGPT lists – Data type mismatches, date formatting, case sensitivity in Status . See full response at https://chatgpt.com/share/66fdb955ecdc800f94b78bf5a5560622
Fifth Spreadsheet Problem & Solution.
Searching Datasets from Right to Left.
The Problem:
I need to identify the largest invoice issued today, along with the name of the customer.
To do this, I must find the value of the largest invoice and then search my list of invoices from right to left. So I cannot use VLOOKUP and as I’m using Excel 2016 I don’t have access to XLOOKUP. Can ChatGPT help me with this?
Prompt:
You are an expert Excel coder. I have a list of data in cells B5:D406 , with the headers: “ID”, “CusName”, and “InvoiceAmt.” I need a formula to:
 Identify the largest invoice value from the “InvoiceAmt” column (column D).
 Find this value in column D and return the corresponding customer name from column C.
The formula must be robust enough to continue working if rows or columns are added in the future. Please ensure it covers all possible scenarios, and explain your approach. Note: I am using Excel 2016.
“””
ID  CusName  InvoiceAmt 
195  A.J. Hinch  $ 260,000 
227  Lorraine Byrne  $ 210,000 
290  Aaron Sele  $ 7,500,000 
“””
ChatGPT Response:
It returns this solution:
=INDEX(C5:C406, MATCH(MAX(D5:D406), D5:D406, 0))
which is good solution and precise usage of the INDEX and MATCH Functions.
In relation to inserting rows, it rightly suggests using structured table , or =INDEX(C:C, MATCH(MAX(D:D), D:D, 0)). In relation to inserting columns, it suggests using relative column references (e.g., C:C, D:D) to ensure the formula still works if columns are inserted or shifted. But it does not mention what would happen if we had duplicate invoice amounts. Also note , this detailed list of weakness and solutions only happened after using the nudging technique text –“ Please ensure it covers all possible scenarios, and explain your approach.”
Sixth Spreadsheet Problem & Solution.
Cross Footing my Spreadsheets.
Problem.
I want to check that our calculations are correct. Can ChatGPT create formulas to total up hours worked in the various tables and then create a cross footing formula to warm of errors.
Specifically:
We have contractors working in two offices office A and office B and on different projects. I want to total up the number of hours worked by each office , by each contractor, on each project and in each location . Finally check they all equal each other.
Prompt:
“
You are an Excel Expert. We have 2 tables , both have the same column headings: “Contractor” , “Tasks”, “Time(Hours).
The first table called officeA starts in cell B3 and the second table called office starts in G3.
They are keeping tract of the hours worked on various projects by each contractor in 2 different offices, office a and office b.
Now I have 3 others tables :
“Tasks” table which has 2 headings and starts in cell L3 . Tasks , Total Hours. It is summing the hours spent on each task.
contractors table which has 2 headings and starts in cell L10: Contractors , Total Hours. It is summing the hours worked by each contractor.
“Locations” table which has 2 headings and starts in cell O3: location , Total Hours. It is summing the hours worked at each office location .
Can you create formulas which will sum up the total hours in these tables for each item : tasks, contractors and locations. ‘I want a solution that leverages Excel structured references since these are Excel Tables’
Finally , can you create a cross footing formula to make sure the total Hours of these 3 tables : tasks, contractors and locations are equal to each other . If not, I want a message saying “ warning, possible error in calculations “. I am using Excel 365″
“””
officeA table sample
Contractor  Tasks  Time (Hours) 
Andrea  AQ526  6 
Andrea  AQ526  9 
Office B Sample
Contractor  Tasks  Time (Hours) 
Aline  LQ539  4 
Aline  LQ539  9 
Aline  LQ539  0.75 
tasks sampe
Tasks  Total Hours 
AQ204  
RM309 
contractors sample
Contractors  Total Hours 
Aline  
Andrea  
Arjun 
locations table
Locations  Total Hours 
officeA  
officeB 
ChatGPT Response :
See full response here https://chatgpt.com/share/6701ba85015c800f9ad84bd735a0d520
For tasks, after plugging in the formula
=SUMIF(officeA[Tasks], L4, officeA[Time (Hours)]) + SUMIF(officeB[Tasks], L4, officeB[Time (Hours)])
it works perfectly.
For contractors , it gives
=SUMIF(officeA[Contractor], L11, officeA[Time (Hours)]) + SUMIF(officeB[Contractor], L11, officeB[Time (Hours)])
which again is correct.
For locations,
=SUM(officeA[Time (Hours)]) and =SUM(officeB[Time (Hours)]) which are also correct.
And the cross foot formula , it suggestion is
=IF(SUM(tasks[Total Hours]) = SUM(contractors[Total Hours]) = SUM(locations[Total Hours]), “Totals Match”, “Warning, possible error in calculations”)
This , unfortunately, its not a usable formula .The problem is you can’t use = to compare multiple values in a single operation. Instead, you need to break it down into two comparisons and use the logical AND function to combine them.
Lets go back and see if ChatGPT realise its mistake and can it fix it .
It comes back with
=IF(AND(SUM(tasks[Total Hours]) = SUM(contractors[Total Hours]), SUM(contractors[Total Hours]) = SUM(locations[Total Hours])), “Totals match”, “Warning, possible error in calculations”)
This is correct and ChatGPT explains its reasoning and the error it made.
Again, it shows the importance of checking these suggested formulas in your workbook and the need to have a strong knowledge of Excel to notice and understand these errors.
Here’s a prompt you can use to ask ChatGPT to check its work on an Excel formula:
“Could you please verify the Excel formula you previously provided? The formula is [insert formula]. I’d like you to check if it will achieve [describe your intended result]. If necessary, please make any corrections or suggest improvements to ensure it works as expected.”
[Improvement with Nudging ]
Finally can you see any weaknesses in the formula . If so, can you create a more effective formula which would allow for eventualities and explain your reasoning .
Version: [ I am using Excel ]
ChatGPT can Amplify our Abilities using Microsoft Excel but not Replace It or Us !
With the arrival of ChatGPT, anyone can now use Microsoft Excel more efficiently. For general intermediatelevel solutions, ChatGPT can be highly effective, provided you have a solid understanding of Excel, carefully craft your prompts, and thoroughly test the suggested solutions in your spreadsheet.”
“As of late 2024, ChatGPT’s suggested solutions for more complex business process problems demand significantly greater caution and closer attention to potential errors, as well as ensuring the solutions provided are optimized.”
Minimum Excel Knowledge requirements to use Excel Safely with AI – Quick 5 Minutes Safety Test.
To use it safely, we suggest users have an intermediate level of Excel knowledge but at a minimum, to understand the mechanics & Excel syntax in these areas.
 Relative and absolute references. Do you know the difference between $A$1 and A1. If not , do not use Excel with ChatGPT.
–  Linking between worksheets /workbooks and its syntax. Is this formula correct : Sheet3!G2+Sheet3H3+Sheet4!$B$2. If not sure , do not use Excel with ChatGPT.
–  Building formulas with mathematical ,logical operators and understand order of precedence. How would Excel calculate this formula . = (B2+F5)*G4 Not sure, do not use Excel with ChatGPT.
–  Data Formatting: Currency, percentages, text, and dates.
–  Understanding table structures and its syntax. =SUM(TABLE1[Revenues]) If you don’t understand this syntax, , do not use Excel with ChatGPT.
–  Understanding the main workhorse Functions like IF, AND, Lookups, SUMIFS, COUNTIFS, SUBTOTAL etc. Do you understand this formula and why would it not work. =IF( B10>= 1000, Give Bonus, “”).. If not , do not use Excel with ChatGPT.
–  Conditional Formatting: Formatting based on cell values.
–  Understand the Mechanics of Pivot Tables – filter and math machine.
Test solutions in your workbook properly and always plugin the suggested formulas/solutions into your spreadsheet and test them extensively. And understand that ChatGTPT and other AI models do not always come back with the most efficient optimum solutions hence of knowledge of Excel is so very necessary.
So the Benefits of ChatGPT for Excel Users.
 Increased Efficiency:
ChatGPT helps users save time by guiding them through multi layered tasks that involve combining multiple functions & techniques.
 Reduced Errors:
By reviewing formulas and highlighting potential mistakes, ChatGPT can significantly reduce the risk of errors in your spreadsheets.
 Optimized Workflows:
Whether you’re trying to simplify your formulas or enhance their performance, ChatGPT provides tips and suggestions to make your spreadsheets more efficient.
Conclusion: ChatGPT as Your Excel Assistant
For general Excel users, ChatGPT offers a powerful way to tackle intermediate level problems by guiding you through multifunction formulas, checking for errors, and suggesting optimizations. Whether you’re summing values based on conditions, comparing data across sheets, or ensuring accuracy in your reports, ChatGPT can help you find the right solutions—and even improve your existing ones.
So AI is a tool that not only provides realtime assistance but also helps you refine and enhance your work, leading to better productivity and more reliable results.
But the limitations must always be in Mind.
While AI, like ChatGPT, can significantly boost productivity in Excel, there are several limitations to keep in mind:
 Complex Context Understanding:
AI can struggle with highly specific or complex business contexts, so it may provide incomplete or inaccurate suggestions.
–
 Advanced Formulas and Macros:
AI is great for common functions and basic to intermediate formulas, but it can have trouble with more advanced Excel tools, such as nested or complex formulas, and VBA (Visual Basic for Applications) scripts for automating tasks.
–
 Your Company’s Data Security and Privacy:
AI tools, especially those like ChatGPT which operate in the cloud, could pose a risk when dealing with sensitive or proprietary information. Users need to ensure they are not inadvertently sharing confidential data with AI models unless using securely managed or locally hosted versions of the AI.
–
 Understanding User Intent:
Sometimes, ChatGPT might misinterpret the task you’re trying to achieve. For instance, if you’re working with ambiguous spreadsheet structures, AI may not accurately guess your end goal, leading to suggestions that need manual adjustments.
The Future of Microsoft Excel.
Despite all the things AI can do, Is it still just a pattern matching machine without cognitive abilities. But who knows what the future holds.
So, it is unlikely to fully replace Excel. Human expertise, critical thinking, and creativity are still essential for handling complex problems and designing tailored solutions.
AI tools like ChatGPT will remain powerful assistants but not complete replacements for skilled Excel users, probably !!!
Resources & Research
 Stanford University’s HumanCentered Artificial Intelligence (HAI) – You can explore their research and applications in AI:
 MIT AI Lab – For insights on AI and data manipulation tools, visit:
Vanderbilt University’s Generative AI – Prompt Patterns:
Georgetown University’s CNDLS – Teaching with AI:
University of Denver’s Office of Teaching & Learning – AI for Teaching:
University of Liverpool. Using AI to create spreading in an educational context.