11 ChatGPT and Excel Tips to SKYROCKET Your Workflow

In this blog post, I will give you 11 insanely effective tips to use ChatGPT and Excel together to skyrocket your productivity.

If you’re looking for a way to spice up your spreadsheet skills, look no further than ChatGPT. ChatGPT is such a versatile tool that no field of computers and IT seems to be untouched by it.

Microsoft Excel is no exception. ChatGPT can help you with various tasks in MS Excel, from generating content to analyzing data, from writing formulas to troubleshooting them.

Let’s get started.

11 ChatGPT and Excel tricks to supercharge your game

Before I start giving you some tips to use ChatGPT with Excel, we need to have some sample data to work with.

Well, good news because ChatGPT can help.

1. ChatGPT can generate random Excel data for practice

You can ask ChatGPT to generate some sample data in CSV format.

CSV stands for comma-separated values. It is a way of storing data in a text file where each value is separated by a comma and each row is on a new line.

The biggest advantage of generating sample data from ChatGPT over downloading one from the Internet is that you can customize the data. You can decide what columns should be there, what should be the format of columns, or any other particular requirements.

In the example below, I ask ChatGPT to give me data for a Japanese company —

Generate me sample data in CSV format for a Japanese company. The columns of this data are “Name”, “Date of birth”, “Gender”, “Department”, “Annual Salary ($)”, “Email”. I want 20 rows of this data. Go.

The best part is that Microsoft Excel recognizes this data and displays it in table format. I copied it into a text file, named it as “sample-data.csv”, opened it in MS Excel, and voila!

However, keep in mind that ChatGPT outputs only a limited number of words at a time (~ 600 words), so it won’t pump out 100 rows in one output even if you ask it to. You will need to run the command 3 or 4 times to get such large data.

2. ChatGPT can act as a text-based Excel

Did you know the ChatGPT itself can behave like a basic Excel program, albeit only text-based?

You can feed it some data in Comma-separated values (CSV) format, it can crunch some numbers, do some basic manipulation, and a few other cool things.

Using ChatGPT as a text-based Excel is a totally viable option if you are completely unfamiliar with Excel and need to do some basic things only. However, remember that ChatGPT does not work well with super-long inputs and cannot output more than ~600 words at a time.

To give you a demo, I will use the sample data that ChatGPT came up with in the previous point.

Let us break the name column in our data into two columns – First name and Last name. Here’s my command —

I am giving you a csv data with several columns. You have to split the name column into two columns – “First name” and “Last name”. Also, delete the original “name” column from the data. Give me final output in CSV format. Here’s the data in CSV format –

Name,Date of birth,Gender,Department,Annual Salary ($),Email
Yuto Suzuki,01/02/1985,Male,Marketing,75000,yuto.suzuki@example.com
….

and here’s ChatGPT’s response —

You can do a few other nifty things too such as:

Transform data into a pivot table

In the CSV data above, you have to create a pivot table around the column “Department”. The columns in the final output will be “Department”, “Total Salary”, “Total number of Employees”, “Average Salary per Employee (rounded up to 100)”, and “Names of Employees”. Go.

Convert a date into a custom format

In the CSV data that I gave you above, I want you to convert the date of birth in the format “8th September, 19XX”. All other columns remain unchanged. Give me final output in CSV format only. Go.

3. ChatGPT can create custom Excel formulas

ChatGPT shines as a text-based Excel but it is still limited in its capabilities. You cannot manipulate huge data. You will have to use the Excel program itself.

ChatGPT works wonders when it comes to helping you with your Excel troubles. You can use it to create custom formulas for your specific use case —

I have a column named financial year which has entries like “2019-20”. The data in this column starts from cell C5. I want these financial years to be shifted ahead by one year. For example, “2019-20” should become “2020-21”. Write me a formula which will accomplish this.

I have tried this prompt and it works perfectly, but I didn’t stumble upon it by accident. In fact, It took me 3 or 4 attempts to get it working.

In my tweaking and testing with the prompt, I learned a few things that work best with ChatGPT, especially for Excel formulas —

Break down the process for ChatGPT

ChatGPT is good at leaping from A to Z, but it’s great at taking small steps. It works much better when it goes from A to B, B to C, and so on.

Rather than asking ChatGPT to do something as a whole, you should, instead, break down the task and give it as much information as possible.

Instead of writing this —

Write a VLOOKUP formula which will take employee id from one table, and look it up in another table. If it matches a row, give me the corresponding value in the 2nd column of the second table.

Write this —

I have two tables in my Excel sheet: Table 1 and Table 2. Table 1 contains two columns: Employee ID and Name, and Table 2 also contains two columns: Employee Id and Salary. Table 1 ranges from cell “F4” to cell “G16” whereas Table 2 ranges from cell “L12” to cell “M24”.

I want you to write a VLOOKUP formula which will take Employee ID from column “F” and then look it up in column L. If it finds a match, then return the corresponding cell in the column M. For example, if a match is found in cell “L9”, then return the value in the cell “M9”.

I have personally tested this prompt. ChatGPT wrote the formula that worked in the first attempt.

Give an example to ChatGPT

An example given to ChatGPT is better than four lines of prompts. ChatGPT is smart enough to gauge the pattern from your example.

Suppose, you want to change the format of dates in your data, then you have to specify these two things —

• what you have (17-03-2023), and
• what you want it to become (17th March 2023)

ChatGPT will get your point.

By giving ChatGPT one or two examples, you can achieve some crazy results.

Here’s another example of a prompt that primes ChatGPT by giving it an example —

I have a column “Full name” in my Excel sheet. I want this column to be split into three columns, “First name”, “Middle name”, and “Last name”.

The “first name” and “last name” column can contain only one word whereas the “middle name” column can have any number of words (including 0). For example, if a person’s name is “Mohandas Karam Chand Gandhi”, then first name is Mohandas, middle name is Karam Chand and last name is Gandhi.

Write me an Excel formula which will split the full name column in these three columns as per my requirement.

Specify the cell number of your data

While getting ChatGPT to write you an Excel formula, it is a good idea to specify the exact cell from which your data begins.

In absence of this information, ChatGPT assumes a cell and writes a formula based on that assumption. You will then need to modify the formula to fit your specific needs.

See what happens when I don’t specify a cell —

Write me an excel formula to split a “Full name” into “First name” and “Last name”

It gave me a formula assuming that my data starts from cell ‘A1’. Now, you will have to change the cell number at five places to use it in your Excel sheet. Changing the cell number at so many places is a time-consuming and of course, error-prone task.

Now see what happens when I specify a cell

I have a column named “Full name” in my excel sheet. The data in it starts from cell C3. Write me an excel formula to split this full name column into “First name” and “Last name”.

ChatGPT gave me a formula that I could grab and go. I don’t have to change anything, so I can just copy and paste it into my Excel sheet.

When your formula uses data from more than one column at once, specifying cells will save you even more time.

4. Troubleshoot an error in Excel

You can use ChatGPT to troubleshoot any error that you may be getting.

I was working with an Excel sheet that had a salary column. But when I tried to get the sum of the salary column, it didn’t work. Excel told me that there was a “Number stored as text” error in this column

I quickly turned to ChatGPT and asked for a solution —

Within 2 minutes, it gave me a solution to my problem.

You can use ChatGPT to troubleshoot any error or problem you are encountering in Excel. It is much better than using Google to search for a solution.

5. Troubleshoot a formula

If you are having trouble with a formula you wrote, you can always ask ChatGPT to identify the error and its remedy.

6. Learn Excel shortcuts

You can ask ChatGPT for keyboard shortcuts which will help you do everything on Excel faster.

7. Write VBA scripts

VBA (Visual Basic for Applications) is a programming language that is used to write scripts or customize the behavior of Microsoft Office applications. You can write scripts to automate repetitive tasks in Excel, write custom formulas, or unlock some advanced functionalities.

We can use ChatGPT to write a custom formula for our specific use case.

8. Learn multiple ways to do something in Excel

You can ask ChatGPT to suggest multiple methods to do anything in Excel. You can then pick one which best suits your use case.

9. Ask ChatGPT to explain an Excel function

If you want to learn the working of an Excel function, you can ask ChatGPT and it will explain that to you with examples.

I want to learn the “XLOOKUP” function in Excel. Explain what it does and what arguments does it take. Use an example to teach me better.

10. Learn Excel

You can use ChatGPT to learn Excel. Start with this prompt —

I want to learn Excel from basic to advanced. First, write a curriculum and then start from the beginning. I want to learn the practical aspects of Excel so that I can work with it right from the beginning. Avoid theory. Keep your teaching style practical-focused. Use lots of examples.

You can then ask ChatGPT to expand upon the sub-headings. Try to take it one by one. Ask for example if you need more detailed explanations.

Although you can learn Excel with ChatGPT, it will not be an exciting or rewarding experience because you will have to read and type a lot.

ChatGPT can work better to teach you a small subset of a topic, such as “how to create a VBA script”, “how to convert a US date format to UK format”, or “how to export an Excel sheet as an HTML table”.

11. Use ChatGPT as an Excel interviewer

If you want to test your Excel skills, you can ask ChatGPT to be your Excel interviewer. Use this prompt —

I want you act like Excel interviewer. Ask me questions one by one. Do not ask them all at once. You will ask me a question and I will answer. Do not correct my answers. Do not provide explanations to your questions. Just ask me questions and wait for me to answer. You don’t have say anything else other than asking questions. Vary your questions from level 0 to level 10 gradually.

Go. Ask me the first question.

Here’s how it went —

ChatGPT and Excel: FAQs

Can ChatGPT work with Excel?

Yes, ChatGPT can act like a text-based Excel or it can also be a great instructor. It can help you craft Excel formulas, find errors in formulas, explain Excel functions, and even teach you some Excel tips.

Can we integrate ChatGPT with Excel?

There is currently no way to integrate ChatGPT. However, Microsoft has announced that they will integrate Bing chat in their office applications with a feature termed Copilot.

Can ChatBot do Excel?

Yes, AI chatbots like ChatGPT and Bing AI act like text-based Excels. You can perform some basic tasks like crunching some numbers, manipulating a few fields, or adding or deleting a column with them. However, do note that they accept input in CSV format only and can accept very limited input due to the character limits on their input and output.

Can ChatGPT write Excel macros?

Yes, ChatGPT can write Excel macros if you tell it exactly what you want it to do. I already showed you how to get ChatGPT to write VBA scripts for you in point number 7 above.

Can ChatGPT analyze Excel?

The answer is a resounding yes! Excel can not only analyze Excel sheets but can also help you create graphs, charts, and pivot tables for your data. However, remember that ChatGPT is still text-based, so it can only provide you with step-by-step instructions to perform these tasks.

In some cases, if you provide your data in CSV format, it can analyze that data and tell you some general trends or patterns.

ChatGPT and Excel: Conclusion

In this blog post, I gave you a few pointers on how you can use ChatGPT and Excel together to take your productivity to the next level.

ChatGPT is a powerful tool that can help you get more out of Excel in many ways. It can produce random Excel data for practice, work as a text-based Excel and even craft Excel formulas for your custom use cases. It can also help you fix errors in your formulas and even write VBA scripts and Macros.

ChatGPT can also be a helpful way to learn for people who want to improve their Excel skills. If you need help understanding an Excel function or want to learn new features and techniques, ChatGPT can give you clear and concise explanations to help you learn. And since ChatGPT can be used as an Excel interviewer, you can use it to practice your skills and test your knowledge in a friendly and interactive setting.

In general, combining ChatGPT with Excel is a fresh and new way to use this powerful tool. ChatGPT’s many features and functions can help you get the most out of Excel, and make you more productive and efficient at work.

So, whether you are new to Excel or an expert, you should definitely look into the many benefits of using ChatGPT with Excel.