Home | Excel | Power BI | Copilot | Visualising Data
Here is the company policy on the use of Copilot at SEGRO.
People who use Excel regularly appreciate that there are often challenges building Excel spreadsheets. Copilot can help us become more productive with Excel in several ways. These include:
Several sample spreadsheets are used in the tutorials and exercises. These are:
It’s worth having a quick look to get familiar with the contents and sample data within these spreadsheets before we start the exercises.
A suggested prompt appears like this.
Write a formula to find the sum of Sales.
You can copy / paste the prompts below to avoid typing them - but also feel free to improve and experiment with prompts.
When you see several prompts, like this:
Initial prompt example
Follow on prompt 1
Follow on prompt 2
do not copy and paste them all at once. Enter each as an individual prompt - firstly the initial prompt (and wait for then read Copilot’s response) then the first follow on prompt and so on. Follow the to-and-fro pattern as if in a conversation.
In a few of the exercises, we import spreadsheet data into an Copilot. In real life, we would not want to do this unless our company policy states that this is allowed and we are using a version of the Copilot from a firm that guarantees that they will absolutely keep our data private: they will not share any data or use it to to train models.
An alternative to providing actual data is to describe the structure of the data e.g. the names and data types of the columns. This is usually much safer but we must consider whether this metadata may also need to be kept private.
A final alternative is to build a small fictitious dataset that resemble the actual data enough to reproduce the Excel challenges and import that fictional dataset with our prompt.
A word about Excel tables: Copilot, and Copilot, are better at understanding Excel data if it is within an Excel table. Much of the data in the sample spreadsheets is formatted as an Excel table.
An Excel table is a structured range of data within a worksheet that is formatted with features like column headers, filters, and consistent formatting for easy analysis and organization. It allows users to sort, filter, and summarize data dynamically, and also expands when new data, either rows or columns, is added to the table. Tables have many advantages for Excel users, not just that Copilot can understand them better.
Initial Prompt:
Act as a helpful Excel expert. Keep your answers brief unless I ask for more detail.
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?
The next prompts relate to the ‘Store Data’ spreadsheet.
Write an Excel formula to…
Write a formula to add up values in cells M2 to M100 of the ‘Sample Data’ sheet.
Write a formula to add up values in cells M2 to M100 of the ‘Sample Data’ sheet when the corresponding value in the B column is Canada.
This workbook contains to an Excel table, Store. The Store table has several columns including Country, Product, Sales and Profit. Write a formula for total sales.
Write a formula to find the total sales for Canada.
Write a formula to give a list of the products in the Store table.
The next prompts relate to the ‘Copilot Sample Data’ spreadsheet.
There is a ShoppingList table with columns Item and Price
Write an Excel formula to lookup the price of a banana in an Excel table named ShoppingList with columns Item and Price
Explain what this formula does. =XLOOKUP(A1, ShoppingList[Item], ShoppingList[Price])
This formula, =XLOOKUP(A1, ShoppingList[Item], ShoppingList[Price]), results in a #N/A error. Please explain why this error occurred and how to fix it.
The formula in the prompt below appears in the Shipments table.
Explain what this formula does. =IF([@Delay] >= 10, “Red”, IF([@Delay] >= 5, “Amber”, IF([@Delay] >= 1, “Green”, “Blue”)))
Follow on with some further prompts:
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?
Here is an example prompt. How well does Copilot perform?
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?
This relates to the three tables Jan, Feb and Mar, in the Sales worksheet.
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.
This prompts relates to data in the ‘Titanic Passenger’ worksheet
In Excel I have a column of text containing the full name of several people, in the format: last name, title. other names 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 |
The horrible formula in the prompt below appears in the Analysis worksheet of the ‘Store Data’ spreadsheet
Explain what this formula does: =TAKE(SORT(CHOOSECOLS(Store, XMATCH(B5:B8, Store[#Headers])), 4, -1), 10) Cells B5 to B8 contains the values: Country, Product, Sales, Profit
Improve the formula so that it is more readable.
Improve the formula so that it is more readable. Use the LET() function.
This is an extended exercise based on the PatientStay spreadsheet
Start with this initial prompt:
Act as an Excel expert. Be brief with your responses.
If your version of Copilot 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 Copilot 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.
The data is in an Excel table, PatientData. Can you simply any of the previously provided formulas.
Export this spreadsheet with these formulas.
This is an extended exercise based on the ‘Pizza Ingredients’ spreadsheet.
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.