Data Training Courses

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

Use AI tools to help with Excel challenges

People who use Excel regularly appreciate that there are often challenges building Excel spreadsheets.We can use generative AI tools to help us with Excel challenges, for example

• explain a formula (that someone else has written).
• explain why we have an error and suggest how to fix it.
• provide step by step instructions on an Excel task. We can describe a problem in English to the AI tool and it will write the formulas required and explain how to apply (and possibly build an example spreadsheet).
• provide teaching example of how to use an Excel function.
• understand the formulas and functions Excel spreadsheet (one we may have inherited for example); how to use an Excel function (for example, choose good values for the arguments) or choose the appropriate function to use: e.g., is VLOOKUP or XLOOKUP better in a particular case?
• diagnose why Excel cell contains an error value e.g., #N/A, #NAME?, #NULL!, and suggest how to fix the formula or problem that caused it.
• describe a spreadsheet and comment on the design given an image. (only for the more advanced models currently
• and more generally, analyse data in an Excel spreadsheet (with a custom AI such as ChatGPT’s Data Analyser GPT)

Example: Use an AI to help with general Excel questions

Many of these example are based on a spreadsheet named Store Data.xlsx available for download here.

Initial Prompt: Act as a helpful Excel expert. Keep your answers brief unless I ask for more detail.

Write an Excel formula to

• Write a formula to add up values in cells M2 to M100 of the SampleData sheet.
• Write a formula to add up values in cells M2 to M100 of the SampleData sheet when the corresponding value in the B column is Canada
• Write an Excel formula to lookup the price of a banana in an Excel table named ShoppingList with columns Item and Price
• Write an Excel formula to extract all the text before the space character in a cell value

The next questions refer to an Excel table, Store, with columns, Country, Product, Sales and Profit.

• write a formula to find the sum of Sales
• write a formula to find the total sales where the country is Canada
• write a formula to find the total sales for Canada
• write a formula to give a list of the products in the Store table

Explain an Excel formula

Explain what this formula does
=XLOOKUP($G26, ItemsFinal[Fruit], ItemsFinal[Price])

Improve an Excel formula

Explain what this formula does.
=IF([@Quantity]<500, “Small”, IF([@Quantity]<1000,”Medium”, IF([@Quantity]<3000,”Large”, “Huge”)))

Improve the formula so that it is more readable.

Improve the formula so that it is more readable. Use the IFS() function.

Is it possible to achieve the same result using XLOOKUP?

What is wrong with this Excel formula?

=XLOOKUP($G28, ItemsFinal[Fruit], ItemsFinal[Price]) is a #N/A error. Please explain why this error occurred and how to fix it.

Teach me about some aspect of Excel

Explain the Excel XLOOKUP function with a few examples.

A follow-up question could be: Should I use the XLOOKUP or VLOOKUP function in my Excel spreadsheet?

Exercise 1: Use an AI to build a matrix of BMI values in Excel

In Excel, I want to create a matrix of values of body mass index (BMI) for a set of weights (60, 70, 80 100 Kg) on the row headers and heights (1.6,1.7, 1.8, 2.0 metres) on the column headers. The BMI formula is weight / (height * height). How do I do this?

Exercise 2: Use an AI to stack data in Excel

My spreadsheet has three tables named Jan, Feb and Mar. All three tables have the same structure as in the example below.

Date	Category	Amount
13/01/2024	Alpha	10
26/01/2024	Bravo	20

Write an Excel formula to stack the data in these three tables into a combined table.

Exercise 3: Use an AI to help split a long text into parts in Excel

In Excel I have a column of text containing the full name of several people, in the format
last name, title. othernames
for example:

Braund, Mr. Owen Harris
Cumings, Mrs. John Bradley (Florence Briggs Thayer)
Heikkinen, Miss. Laina

I want to split these names up into three columns: lastname, title and other names. This is the result I want based on the examples

last name	title	other names
Braund	Mr	Owen Harris
Cumings	Mrs	John Bradley (Florence Briggs Thayer)
Heikkinen	Miss	Laina

Exercise 4: Improve an Excel formula

Explain what this formula does
=TAKE(SORT(CHOOSECOLS(Store, XMATCH(B5:B8, Store[#Headers])), 4, -1), 10)

Improve the formula so that it is more readable.

Improve the formula so that it is more readable. Use the LET() function.

Exercise 5: Use an AI to help analyse and summarise data in an Excel sheet

The PatientStay spreadsheet contains fictitious data about 44 patient hospital stays.

Start with this initial prompt:
Act as an Excel expert. Be brief with your responses.

If your AI Tool can import a data file, use this prompt.
The attached spreadsheet has data on patients stays in hospital. Import the file and describe the data.

If your AI Tool can’t import a data file, use this prompt.
An Excel spreadsheet contains data about patient stays in hospital. The columns are PatientId, AdmittedDate, DischargeDate, Hospital, Ward, Tariff and Ethnicity.

Here are some suggested prompts to start your analysis.

• Write formulas to show how many patients are admitted to each hospital.
• Write a formula to calculate the length of stay for each patient.
• Export this spreadsheet with these formulas

Exercise 6: Analyse Pizza Ingredients Excel Spreadsheet

Download and inspect the Pizza Ingedients Excel spreadsheet This is based on a beginner level Excel lesson to teach formulas and relative and fixed references.

Here are some suggested prompts to start your analysis.

• Import the attached spreadsheet then describe it.
• The data is in an Excel table named Items. List the columns of this table.
• What formula should we enter for the CostExVAT column?
• Can you restate this formula using the fact that these columns are in an Excel table.
• And for the VAT column?
• And for the CostIncVAT column?
• Consider that VAT can change from time to time and note that cell C2 contains the prevailing VAT rate. Change your formula suggestions to take account of this.
• Note that cell C2 is a named cell vat_rate. Revise the formulas to take this into account.
• Add these formulas to the Excel file and export it.