Excel Magic

• Develop problem-solving and prompting skills in using generative AI to automate repetitive Excel tasks. The goal is to prompt the AI to produce precise Excel functions or formulas that can be copied and pasted directly into Excel without manual adjustments.  

What you need

Setting it up

• A computer with access to spreadsheet applications such as Microsoft Excel or Google Sheet;

• Access to a generative AI tool (e.g., ChatGPT, Copilot);

• The raw data file; 

• The final results file (to illustrate goal).  

How long does it take?

• Approximately 1-2 hours, depending on users ability to breakdown steps for generative AI. 

How it works

1. Take the raw data (a list of tags for 507 unique items), ask generative AI provide steps/formulas to separate the tags, categorise them, put them into columns for their own category, and count the number of tags in each column. As demonstrated in this plain text version of the final table.

2. For example, if an item is tagged 'Edinburgh', the word 'Edinburgh' should appear in the column ''Edinburgh' for its row.  Note, an item may have multiple tags.     

3. Hint: You can arrive at the final table shown above in 5 operations. (An operation is an action that manipulates the data. For example, 'Separate tags into columns' count as one operation even though to apply it to the whole table involves multiple clicks and copying the formula.)

4. Aim to get generative AI to provide formulas that you can copy and paste directly into the spreadsheet application. You can start with a general question such as, 'Can Excel [do X] for me?'

Suggested follow-up

• Reflect on the experience: Was generative AI useful? In what way? If not, why not. 

• Experiment with other Excel tasks using generative AI assistance.

• Share insights and tips with others.

Where it works well

• Generative AI can perform Excel magic for casual spreadsheet users--no more figuring out where the quotation mark should go in, if you should use double quote or single quote. You may be able to find ways to automate data manipulation and repetitive tasks even if you don't know how to write macros.

• Reducing manual effort and minimizing errors in using spreadsheets.

• Enhancing understanding of spreadsheet functions and formulas. Both ChatGPT and Copilot will explain how the formulas and Macros they provide. Understanding will help you implement and adjust for future use. 

• If participants don't know how to run macros, ask and generative AI will walk them through step-by-step. But they need to realise they can ask. 

What to watch out for

• There are many ways to skin a cat - many ways to achieve the same results even if you start from the same data set and end in the same place. In one iteration of the game, participants came up with 4 different solutions that arrived at the same results. 

• Iterate your prompts to be as specific as you can. For example, define the sheet name, row and column ranges where data are located, specify actions like 'remove heading and trailing spaces that may or may not be in the data'.

• If you are specific enough in your instructions, you should be able to simply cut-and-paste and get the desired results.

• If the solution offered by generative AI doesn't work the first time–which happens frequently–ask GenAI to troubleshoot. Tell it what the problem is so that it can refine or provide alternative methods. This requires patience on participants’ part.  

Authorship