ChatGPT, far from replacing white-collar workers, has become an indispensable tool–in fact, more than a mere tool but a co-worker. ChatGPT has made the lives of white-collar workers easier and more efficient, thus freeing up time and energy for more strategic and creative tasks.
From writing personalised emails to creating and improving blogs, from coding to web design, ChatGPT has found its use.
In this article, we’ll learn yet another profitable use of this versatile tool: How to use ChatGPT to master Excel and Google Sheets. Whether you are a beginner or an advanced user, you can benefit from ChatGPT’s expertise and knowledge of Excel and Google Sheets.
Ways to use ChatGPT to master Excel and Google Sheets
There are numerous ways we can use ChatGPT for Excel and Google Sheets. By asking specific questions and getting detailed explanations and tips, we can improve our skills and become more proficient in using Excel or Google Sheets. Our imagination is often the limit.
So, here is a list of things that we can use ChatGPT for Excel and Google Sheets. These are not exhaustive nor comprehensive nor is there any claim to these being the most important. They, however, it is hoped, will serve as a good starting point for using this wonderful tool to excel in Excel and Google Sheets.
- Ask ChatGPT questions about specific functions
- Get help with formulas
- Solve common formula parse errors
- Learn shortcuts
- Get help formatting data
- Get tips on data analysis
- Create macros and app scripts
Let us learn each of these in more detail.
Learn about specific functions and formulas
ChatGPT can provide us with detailed explanations of specific Excel and Google Sheets functions, such as VLOOKUP, SUMIF, COUNTIF, INDEX, MATCH, and so on–in one word, just-about-anything.
Let us ask ChatGPT a simple question about an Excel function.
What is the VLOOKUP function?
Not only does it give us the definition of the function but also the syntax and its parameters along with an example. Pretty cool! However, this is not what most people will be using it for.
A more practical use of ChatGPT is for getting help with Excel formulas, whether it is getting a formula for a calculation or fixing formula issues.
How do I use the VLOOKUP function with the IF function?
Both of the above examples assume that we don’t yet know what a specific function or combination of functions does. Most of the time, though, we do know what each function does (if we know they exist) but don’t quite know what function to use for a particular calculation or set of problems.
We can use ChatGPT to get formulas in Excel or Google Sheets for specific calculations and tasks and it will give us the approximate answer, if not the exact answer.
Get help with Excel and Google Sheets formulas
Excel and Google Sheets have a vast range of formulas that can help us perform calculations and analyse data. We can ask ChatGPT to explain the syntax of specific Excel formulas and how to use them.
Or, we can ask ChatGPT for answers to specific problems as we would a human assistant by describing what we want.
What is the Excel formula for finding the price of coffee from a table?
ChatGPT can also help us with more complex Excel and Google Sheets formulas. Explain in detail (the more detailed, the better) the Excel problem and what you want to achieve, and ChatGPT will provide you with answers and formulas.
How do I use the query formula in Google Sheets to get all entries greater than 100 from a table and sort the data by descending order of column B?
If we want to import data from another spreadsheet with certain conditions but don’t know how, we can ask ChatGPT.
How can I import all rows with the label “female” from another spreadsheet and sort them in alphabetical order?
We can also ask ChatGPT to explain Excel and Google Sheets formulas to us in plain language.
Can you explain what this formula does?
[paste the formula here]
There are no shortage of ways to use ChatGPT to help us with Excel and Google Sheets problems but we need to move on. There are several other interesting ways we can use ChatGPT to master Excel and Google Sheets.
Solve common Excel problems and formula parse errors in Google Sheets
ChatGPT can help us diagnose common problems and formula errors in Google Sheets and Excel. We can ask ChatGPT to explain why we are seeing an error message or how to fix a formatting issue.
We can describe the error to ChatGPT and what we are trying to achieve. We can either describe the problem in one prompt or in a conversational manner by elaborating the issues prompt by prompt.
Learn Excel and Google Sheets shortcuts with ChatGPT
Shortcuts are small things but they aid greatly in helping us work efficiently and increasing productivity. They are important ingredients in Excel and Google Sheets proficiency.
What is the Excel shortcut to add cell borders?
How do I automatically apply formulas to all cells in Google Sheets?
Some other shortcut prompts you can ask:
What is the shortcut in Google Sheets to move to the end of the sheet?
How is the shortcut to switch tabs in Google Sheets?
What is the shortcut to insert a column to the right?
For your curiosity, the answers respectively are Ctrl + End, Alt + Down Arrow, Alt + I, then O.
Get help with formatting data
Excel and Google Sheets have many formatting options that can help us make our spreadsheets look clean and easy to read. We can ask ChatGPT for Excel tips on how to use cell formatting, conditional formatting, and other formatting options.
ChatGPT can help us with formatting numbers, setting up conditional formatting rules, and creating custom cell formats among other things.
How do I format numbers with two decimal places in the international system in Google Sheets?
We can also ask it to help us stylise numbers so that they look more visually appealing and easier to read and understand.
I have a column with values in percentage in Google Sheets. I want the percentages to be displayed in green with up arrow if positive and red with down arrow if negative. How can I do that?
We can also use ChatGPT to master Excel and Google Sheets for various conditional formatting rules.
How do I set up conditional formatting in Google Sheets to highlight cells with values less than 50 in red?
We can also use ChatGPT to help us create custom cell formats in Excel or Google Sheets.
How can I change phone numbers to this format in Excel: (+1) 1234-567-890?
Use ChatGPT for data analysis in Excel and Google Sheets
Excel and Google Sheets are excellent tools for data analysis. ChatGPT can help us improve our skills in this area. For example, we can ask ChatGPT for Excel tips on how to use pivot tables to represent data and create charts and graphs to visualise data.
It can also be used in various statistical analyses in Excel and Google Sheets, such as finding standard deviation, calculating mean, median and mode, and finding correlations among others.
Create a pivot table in Google Sheets that shows the total sales for each product, broken down by month and sorted in descending order.
Google Sheets and Excel are good at visualising data which makes data analysis easier. We can ask ChatGPT to help us visualise the data.
How do I create the sparkline function in Google Sheets to create progress bars?
We can give specific details and create charts with the conditions and values provided.
Can you modify the bar colours in the chart to green if progress is more than 80 percent, red if less than 50 percent and yellow if between 50 and 80?
We need to keep in mind that the formulas may not always work out of the box. We need to tweak them to make them work, especially if we do not specify the cells and number format in the prompt.
In the example below, the progress is in percentage and not in decimal, so the values in the formula are changed accordingly.
Create Excel macros and Google Sheets App Script with ChatGPT
ChatGPT can also be used for advanced Excel and Google Sheets features such as macros and VBA programming in Excel and writing Apps Script in Google Sheets.
Macros in Excel can be used for various things such as automating repetitive tasks.
Suppose you have a worksheet with a large data set that includes a column of product names and a column of sales figures for each product. You want to create a separate worksheet for each product that includes only the sales figures for that product. You could manually create each worksheet and copy and paste the data for each product, but this would be time-consuming and error-prone.
Let us ask ChatGPT to write a VBA code for this macro in Excel.
The code may not work out of the box and we often need to perform multiple iterations to get a workable code. However, it’s a lot easier than starting from scratch.
We can similarly use ChatGPT to create Apps scripts for Google Sheets.
Create an app script for Google Sheets that highlights duplicate values by changing the background colour of the cell to light red.
ChatGPT is a fantastic tool for learning and mastering Excel and Google Sheets. Its language capabilities make it suitable for asking questions in a conversational tone–from the most basic queries to the sophisticated ones. It is a teacher and an assistant that novices and pros alike can benefit from.
Some similar articles
Use ChatGPT as a Writing Assistant to Write Faster and Better