Top 5 Ways I Use ChatGPT to be an Excel Ninja
No matter how experienced you are at Excel, ChatGPT is your secret superpower.
As a Business Systems Analyst, I regularly use ChatGPT to improve my Excel skills, especially when cleaning messy data and analyzing large datasets. It has been a game-changer for my work productivity!
Read on for the top 5 ways I use ChatGPT to master Excel.
1. Ask ChatGPT what an Excel error means, and how to fix it.
When I was editing an Excel spreadsheet, I got the error “#SPILL!”
I used ChatGPT to help me solve this problem in real-time by asking: “What does the error message #SPILL mean in Excel, and how can I fix it?”
I learned that the “#SPILL!” error occurs when a formula designed to return multiple values doesn’t have enough space to spill its results on the worksheet. ChatGPT offered me multiple fixes for this problem — I decided to move the formula to a new location where there was room for the results to spill over.
ChatGPT is also excellent for troubleshooting error messages in other systems, including SQL and Python.
2. Ask ChatGPT to write Excel formulas for you. Be as specific in your prompt as possible.
ChatGPT is a great resource for writing fast Excel formulas, but you need to be as clear and specific as possible, including details like table names, variable names, data types, and the correct cell ranges. Also, provide additional context and background information whenever possible, such as the objective of your data analysis and the type of information in the spreadsheet.
Below, I asked ChatGPT to write an X-LOOKUP formula that brings column values from one worksheet to another.
I was very specific in this prompt:
- I named the worksheets and described the information on each one (Student profile information and outcome information.)
- I cited the cell range that I want the X-LOOKUP information to pull from.
- I explained the end goal (that the “Program” column values are pulled into the Outcomes tab.)
ChatGPT produced an X-LOOKUP formula within minutes that worked perfectly!
3. Ask ChatGPT to explain the results from your Excel analysis.
Even if you know the correct Excel calculation (for example, to calculate the 90th percentile of salaries), you may still have trouble explaining that result to your coworkers. Luckily, you can ask ChatGPT to give you a simplified explanation of your results.
In this case, I asked ChatGPT: “What does it mean if a salary is in the 90th percentile?”
ChatGPT explained that “it indicates that the individual’s salary is higher than the salaries of 90% of the people in the reference group,” and then offered helpful examples.
Now when you are presenting this data to coworkers, you can give them a clear and concise explanation of what the 90th percentile means.
4. Ask ChatGPT to explain an Excel formula that is already in the spreadsheet.
Maybe you started a new job and they’ve asked you to edit an Excel spreadsheet from your predecessor. Or maybe you created a spreadsheet, but haven’t looked at it in months and can’t remember why you put in that confusing nested formula. In either case, you can ask ChatGPT to explain the formula to you.
Explain the basics of the spreadsheet, including the information inside and the structure (number and name of worksheets, etc.)
Then, you can ask a more detailed question like: “Can you breakdown this formula for me and explain what it does? =XLOOKUP(B2, ‘Students’!B:B, ‘Students’!L:T, “Not Found”)”
ChatGPT will explain the components of the formula and how the formula works. This way, you can figure out the purpose of the formula and see if you need to make any changes.
5. Ask ChatGPT to provide step-by-step guides for Excel administrative tasks, such as password-protecting your workbook.
ChatGPT also provides easy-to-follow instructions for Excel administration tasks. Although I’ve done it multiple times, I forgot how to password-protect my notebook, so I asked ChatGPT: “How do I change my Excel document to be password-protected?”
ChatGPT provided two sets of step-by-step instructions, one for Microsoft Excel 2016 and later (which applied to me), and instructions for older versions of Microsoft Excel. The steps worked perfectly for me.
ChatGPT can also provide step-by-step instructions for other platforms, including Microsoft Power BI. For certain systems, ChatGPT may have a disclaimer like: “Note: The availability of features may differ depending on the version of Power BI you are using, but generally, the steps should be similar.”
Final Thoughts
ChatGPT is a crucial tool to have in your Excel arsenal. Even the free version can help you:
- Troubleshoot Excel error messages.
- Explain Excel formulas in your spreadsheet.
- Generate Excel formulas to perform any number of tasks (X-LOOKUP, V-LOOKUP, extracting information, etc.)
- Explain the results of your Excel analysis in easy-to-understand language.
- Create step-by-step instructions on how to perform Excel administrative tasks.
To make the most of ChatGPT, you should include the following information in your prompt:
- Components of spreadsheet: table names, variable names, data types, cell ranges, names of worksheets
- Background information: the purpose of your request, the desired outcome, the type of information in the spreadsheet, whether there is incorrect or missing data in the spreadsheet (depending on the situation)
As always, when using ChatGPT, remember that you probably won’t nail the perfect prompt right away. Refine your prompt and chat back and forth with ChatGPT to get your desired outcome.
To learn more, I highly recommend the Maven Analytics self-paced course: ChatGPT for Data Analytics.
The contents of external submissions are not necessarily reflective of the opinions or work of Maven Analytics or any of its team members.
We believe in fostering lifelong learning and our intent is to provide a platform for the data community to share their work and seek feedback from the Maven Analytics data fam.
Happy learning!
-Team Maven
