AI AND EXCEL

Artificial Intelligence is currently a hot topic and is used in many fields, such as:

• The creation of images, text, music…
• Voice and image recognition (voice assistants such as Siri, Alexa…)
• Autonomous driving, notably with Tesla
• Medical diagnosis, for example through the automated analysis of X-rays or CT scans…
• And much more besides

But what about using AI with Excel?

Issues : When using ChatGPT and/or Copilot, how can students create an Excel spreadsheet, and how can they analyse and refine it?

First of all, it is important to understand the difference between the two tools:

ChatGPT can offer us two types of response:

• The spreadsheet itself (available to download)
• Detailed, step-by-step instructions to follow in Excel

         As Copilot is integrated directly into Excel, it provides guidance on how to improve and analyse the spreadsheet, but not on how to create it.

Please note that there are various Microsoft licences available, and the most comprehensive ones do allow for direct creation.

Using AI requires you to articulate your needs as clearly as possible; this is known as a prompt. The more thoughtful and precise the prompt, the more relevant the result will be.

 

There are two ways to generate a result:

• Step-by-step approach: we ask for the various required elements one by one, in sequence. This method allows us to arrive at the result gradually.
• Comprehensive prompt: the prompt contains all the required information in one go. This method requires a detailed analysis of the requirements in order to formulate them all.

But in both cases, the requirements will still need to be clearly specified. (For further reading: here)

 

It’s also worth noting that ChatGPT is free and offers unlimited use for generating explanations. However, when it comes to creating files, its use is limited to a few files per day. To get round this limitation, you’ll need to use the paid version.

Copilot is integrated into Microsoft 365 and is therefore a paid service.

 

1 Create the table directly from ChatGPT

1.1 Using a simple prompt (level 1)

Let’s give the pupils a very simple prompt:

Give me an Excel spreadsheet that I can use to keep track of my bank account.

The answer is as follows:

And here is the file that was generated:

It only contains the column headings… In short, we could have done this work without any difficulty, and this table, as it stands, isn’t very useful.

It is therefore necessary to be much more precise in order to achieve a satisfactory result.

1.2 Using a more detailed prompt (level 2)

Let’s now try the following prompt:

Please provide me with an Excel spreadsheet that allows me to track my bank account. It must include the following columns:

DATE

TYPE OF EXPENSE

DEBIT

CREDIT

BALANCE

The columns must be of an appropriate width.

The row containing the column headings must have a yellow fill colour.

The row containing the headings must be on row 3.

The column headings must be in bold.

The BALANCE column must contain a formula that calculates the running total.

The opening balance must be entered in cell A1

The table must have borders.

The table must contain 60 rows.

The running total must be shown in green if it is positive and in red if it is negative.

 

The result matches what was requested (in this screenshot, the values have been entered manually):

The prompt used in this example required a more detailed explanation, forcing us to carry out a more in-depth analysis.

 

1.3 Using an (almost) complete prompt (level 3)

Let us now encourage the pupils to complete the following prompt, which, compared to the previous prompts, introduces the concepts of graphs and monthly sheets, as well as an annual summary:

Please provide me with an Excel spreadsheet that allows me to track my bank account.

It must contain the following columns: DATE, EXPENSE TYPE, DEBIT, CREDIT, BALANCE.

The columns must be of an appropriate width.

The row containing the column headings must have a yellow fill colour.

The row containing the column headings must be on row 3.

Column headings must be in bold.

The BALANCE column must contain a formula that calculates the running total.

The opening balance must be entered in cell A1.

The table must have borders.

The table must contain 60 rows.

The running total must be green if it is positive and red if it is negative.

I want the columns containing euro amounts to be in accounting format.

I want a drop-down menu in the EXPENSE TYPE column offering the following options: Housing, Vehicle, Food, Education, Leisure, Health, Miscellaneous.

I need a chart showing the proportion of each of these expenses per month.

Each sheet in the workbook should correspond to a month of the year.

The final sheet in the workbook should provide an annual summary of expenditure by category, as well as an annual chart.

 

We get an even more effective result:

 

This result appears to be most in line with our initial expectations. Nevertheless, we are dealing with a dataset that could be improved. Despite the length of the prompt, we have not processed banking transactions relating to credit (salary payments, National Health Insurance reimbursements, cheque deposits, etc.). An even longer prompt would need to be drafted to cover these aspects…

 

1.4 Why not use the ‘drawer’ prompt then?

In this specific case, where an Excel file is created automatically, this option is not the most suitable.

In fact, when ChatGPT is asked to improve or modify the table, it does not necessarily take the original requests into account.

For example, at the end of the Level 2 prompt discussed earlier, we could have asked ChatGPT to modify the proposed output by adding the monthly sheets and the annual summary. ChatGPT would indeed have responded to the new requests but would not have taken certain previous requests into account. The output would no longer have been 60 lines long, and the column headers would have disappeared…

We would therefore need to provide it with a comprehensive prompt all at once, which is no easy task.

The best approach is therefore to use a detailed prompt to create the base image. Subsequently, ‘drop-down’ prompts can be used to refine and improve this image.

 

2 Step-by-step guide (drawer prompt) with ChatGPT

This observation leads us to use the prompts provided, which will guide us through each successive step to achieve the desired result.

To do this, ChatGPT will explain, using simple prompts, how to create the table.

Here are just a few examples of prompts; the aim of this guide is not to teach you how to use Excel, but to show you how to use AI to achieve a satisfactory result.

So let’s start by opening a blank Excel file and follow the instructions!

 

2.1 Prompts regarding the table structure

 

Once you have entered the headings into the cells as shown above, ask your pupils to come up with the following prompt:

Explain to me how to change the column widths in Excel.

 

The result is as follows:

Changing column widths in Excel is easy! Here are three ways to do it:

1️ Using the mouse

1. Place your cursor on the line separating two column headers (e.g. between A and B).
2. Click and drag to the right or left to adjust the width.
3. Release the mouse once you have reached the desired width.

2️ By specifying a precise width

1. Select the column(s) you want to change (e.g. click on A to select column A).
2. Go to the Home tab.
3. In the Cells group, click on Format.
4. Select Column width.
5. Enter a numerical value (e.g. 15 for a standard width).
6. Confirm by clicking OK.

3️ Automatic adjustment (AutoFit)

If you want Excel to automatically adjust the width to fit the content:

1. Select the column.
2. Double-click on the line separating two column headers.
3. The column will immediately adjust to fit the largest value it contains.

 

All the student has to do is apply one of these three solutions.

Pupils should therefore ask him in the same way how to add borders, colour, etc. These steps are not described here as the process is the same.

 

2.2 Prompt regarding the cumulative balance formula

Once the structure is in place, let’s look at the formula for the cumulative balance (in the BALANCE column).

Let’s give ChatGPT the following prompt:

Which Excel formula should I use to calculate the cumulative balance in the BALANCE column based on the DEBIT and CREDIT columns?

His answer is very clear:

To calculate the cumulative total in the BALANCE column based on the DEBIT and CREDIT columns, here is the formula to use in Excel:

Basic formula for the opening balance

If the opening balance is stored in A1, then the first cell in the BALANCE column (e.g. E4) must contain:

=A1+D4-C4

This formula takes the opening balance and adds the credits (D4) whilst subtracting the debits (C4).

Formula for the following rows (e.g. E5, E6, etc.)

In E5 and the following rows, you must use the previous balance and update it with the debit and credit amounts for the current row:

=E4+D5-C5

Then, you can drag the fill handle down to apply this formula to all the rows.

Explanation of the calculation

• E4 → Takes the previous balance.
• +D5 → Adds the credited amount (cash inflow).
• -C5 → Subtracts the debited amount (cash outflow).
• The formula is applied row by row to calculate the running balance.

💡 Tip: If you want negative values to be displayed in red and positive values in green, use conditional formatting! 🎨

 

ChatGPT even encourages us to use conditional formatting!!!

All the students have to do is come up with prompts for each of their needs.

 

3 Analysis with Copilot

Please note: With a basic licence, Copilot does not allow you to create a table. It will only allow the student to improve and analyse it!!!

Ask the student to create a fairly simple sales table as follows:

It should be noted here that only the SELLING PRICE column contains a formula (PRICE × QUANTITY).

3.1 Let Copilot create formulas

After clicking on the ‘Create Formulas’ tool, Copilot analyses the data in the table and suggests the following:

 

Here, we’re going with the flow as we don’t have any particular destination in mind, so we accept his suggestion and confirm by saying

We have the following result:

Copilot used the SUMIF function.

 

3.2 Ask Copilot for a specific formula

We now want to find out the total quantity of products sold; let’s use the following prompt:

I need a column that calculates the total quantity of products sold.

Once validated, we have an additional column:

 

3.3 Automation of the pivot table tool

Let’s now use the tool

Copilot gives us the following result:

After confirming (Add to a new sheet), we get the following result: (amazing…)

Of course, this slide deck and this chart can be edited as required.

 

3.4 Apply colour and formatting

When you click on this tool, Copilot prompts you to finish the sentence.

Give the student this prompt (this sentence fragment):

Highlight all the cells containing Marie’s sales figures.

Copilot will give them this response:

Let’s validate this proposal:

Conditional formatting has just been applied.

Let’s try some advanced conditional formatting using the following prompt:

I want all rows where the quantities are less than 50 to be highlighted in red.

Copilot replies as follows:

Once validated, we have this result, which is exactly as we expected:

 

CONCLUSION

First and foremost, it is important to realise that AI can make mistakes, so you should not place absolute trust in it.

This document has highlighted the importance of precise prompts; they must be as specific as possible.

 

ChatGPT and Copilot are different but complementary:

• To create an Excel spreadsheet, ChatGPT is the best option

Once the basic spreadsheet has been created, we can refine it using the ‘nested’ prompt method

• To analyse and automate our spreadsheets, we’ll use Copilot

 

Advanced users may not need AI. Conversely, beginners will find they can create sophisticated spreadsheets by taking the time to formulate their prompts carefully.

We should view AI as a tool for developing our skills, rather than simply for carrying out tasks. With Excel, we’ll spend less time applying our knowledge and more time explaining our needs to the AI.

 

PS :What does ChatGPT think?

Just for fun, ask the student to try this final prompt on ChatGPT:

What is the difference between ChatGPT and Copilot when it comes to using Excel?

Here is the response:

 

ChatGPT and Copilot are two AI-powered assistance tools, but they have different uses and features when it comes to Excel.

📌 1. ChatGPT: General and Explanatory Support

👉 How does it help in Excel?

• Explains Excel concepts and formulas.
• Provides tips and best practices.
• Offers solutions in plain language.
• Provides formulas tailored to your needs (e.g. “How do I calculate a running total?”).
• Can generate VBA code to automate tasks.
• Helps you understand and debug errors.

💡 Example of use:

“How do I extract the text after a specific character in a cell?”

Response using various functions depending on requirements (MID, RIGHT, FIND, etc.).

📌 2. Copilot for Excel: Integrated and Interactive Assistance

👉 How does it help in Excel?

• Suggests and applies formulas directly in Excel.
• Generates dynamic tables, charts and summaries based on the data in the file.
• Performs advanced data analysis (statistics, trends, forecasts).
• Can automatically create VBA macros.
• Runs directly within Excel, with access to cells and data.

💡 Example of use:

“Create a pivot table for me to analyse sales by product.”

Copilot will generate and insert the table into your file.

 

📊 Comparison Table

Feature	ChatGPT	Copilot Excel
Explanation of the formulas	✅ Yes	🔸 Partial (suggests but does not always explain)
Formula generation	✅ Yes	✅ Yes
Direct application in Excel	❌ No (text-only reply)	✅ Yes
Automatic data analysis	❌ No	✅ Yes
Creating dynamic charts	❌ No (possible explanation)	✅ Yes (generated automatically)
Using Excel data context	❌ No	✅ Yes
Help with VBA and macros	✅ Yes	✅ Yes (generated automatically)

🤔 Which tool should I choose?

✔ ChatGPT is ideal for understanding, learning and solving specific problems.

✔ Copilot is better suited to working directly on Excel files and automating data analysis.