Excel and ChatGPT

Have you ever encountered any of these common Excel situations where you need to:

• Understand a complex formula or write a formula?
• Understand a VBA macro that was written by an Excel power user who is no longer available?
• Write a VBA macro?
• Get general Excel help?

When you encounter these types of situations, what do you do? My guess is you probably start with a Google search, then follow several links to various blog posts and videos. Sometimes, they will provide you with the answers you are seeking, but sometimes not.

So, when they don’t, what do you do? You are totally stuck … or are you?

Here’s how a free tool called ChatGPT can help with all of these and much more. Let’s get to it.

ChatGPT Primer

In the simplest terms, ChatGPT is a service that provides text-based responses to your prompts.

So, when you provide a prompt such as:

When was Excel released?

ChatGPT will provide a response like:

Microsoft Excel was first released in September 1985 for Apple Macintosh computers. It was later released for IBM-compatible PCs in November 1987. Since then, Excel has undergone many updates and versions, and it remains one of the most popular spreadsheet software applications in use today.

In this article, I’ll show you how ChatGPT can help with your Excel work. But know that the knowledge base of ChatGPT is unbelievably immense. How does ChatGPT describe itself? To find out, I asked it.

I entered the following prompt:

Can you describe ChatGPT in one sentence?

And got the following response:

I am ChatGPT, a large language model designed to understand natural language input and generate human-like responses using deep learning algorithms.

If you’d like to learn more, head to openai.com.
If you’d like to create an account and use the service for free, visit chat.openai.com.

Understanding a Complex Formula

Let’s say you open a workbook and encounter a formula written by someone else. For example:

=RIGHT(B8,LEN(B8)-FIND(",",B8)-1)

You’d like to understand what it does.

So, you use the following prompt with ChatGPT:

What does this excel formula do: =RIGHT(B8,LEN(B8)-FIND(“,”,B8)-1)

ChatGPT response:

This Excel formula extracts a portion of text from a cell in column B, specifically the text to the right of the comma (",") in the cell Here’s a breakdown of how the formula works:
• The LEN function returns the length of the text string in cell B8.
• The FIND function searches for the position of the comma in the text string of cell B8.
• The difference between the length of the text string and the position of the comma gives the number of characters to the right of the comma.
• The RIGHT function extracts that portion of text from cell B8.
So, the formula is essentially taking the text in cell B8, finding the comma, and returning the text to the right of it. For example, if cell B8 contains "John Smith,123 Main Street", the formula would return "123 Main Street".

How cool is that? It explains the overall goal of the formula, the purpose of each function and concludes with an example. Amazing!

Writing a Formula

Let’s say you want to write a formula, but aren’t sure how. For example, you want to write a formula that adds up a column of values (column B), but you only want to include the Debit rows (as noted in column A).

So, you enter the following prompt:

Write an excel formula that adds the values in column b but only when the value in column a is equal to “Debit”

ChatGPT responds with:

The formula to add values in column B based on a condition in column A can be achieved using the SUMIF function. Here’s the formula you can use:
=SUMIF(A:A,"Debit",B:B)
Explanation: The SUMIF function takes three arguments:
• The range to check for the condition (column A in this case)
• The condition to be met ("Debit" in this case)
• The range to sum (column B in this case)
This formula checks column A for the condition "Debit" and sums the corresponding values in column B.

Notice that the response not only includes the formula that works with your workbook, it also explains the function arguments and uses your specific data ranges (not just a generic example).

Understanding VBA

Let’s say your organization had an Excel power user write a macro. You have questions about the macro, but the original developer is no longer available. You wonder if ChatGPT can explain the vba code.

So, you copy the code and use the following prompt:

Please explain the following vba code:
Sub MyMacro()
  For Each ws In Sheets
    ws.Name = ws.Range(“a1”).Value
  Next ws
End Sub

ChatGPT responds with:

This VBA code defines a macro named "MyMacro". The macro uses a "For Each" loop to loop through all the sheets in the current Excel workbook, assigning each sheet’s name to the value in cell A1 of that sheet. Here is a breakdown of the code:
1. Sub MyMacro() - This line declares the macro and gives it the name "MyMacro".
2. For Each ws In Sheets - This line starts a loop that will go through each sheet in the workbook. The variable "ws" is used to refer to each sheet in the loop.
3. ws.Name = ws.Range("a1").Value - This line sets the sheet’s name to the value in cell A1 of that sheet. The Name property of the worksheet object is used to  change the sheet’s name.
4. Next ws - This line tells the loop to move on to the next sheet in the workbook.
Overall, this macro can be useful for quickly renaming a large number of sheets based on the value in a specific cell on each sheet.

Notice the response includes an overall summary and then explains each line of code. It also provides a short conclusion. Pretty cool.

Writing VBA

Let’s say you want to automate some manual Excel tasks, but aren’t sure how to write a VBA macro. For example, you want a macro that will create a new PDF file for each worksheet in the workbook.

Provide this prompt:

Write vba code that will print each worksheet to a pdf

ChatGPT responds with:

Sure, here’s an example VBA code that will print each worksheet in the active workbook to a separate PDF file:
Sub PrintWorksheetsToPDF()
Dim ws As Worksheet
Dim SaveName As String
For Each ws In ThisWorkbook.Worksheets
SaveName = ThisWorkbook.Path & "\" & ws.Name & ".pdf"
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=SaveName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
Next ws
End Sub
You can run this macro by opening the VBA Editor in Excel (press Alt+F11), creating a new module, pasting the code into the module, and then running the macro from the Excel interface (press Alt+F8 to open the Macro dialog and select the "PrintWorksheetsToPDF" macro).
This code will save each worksheet as a separate PDF file in the same folder as the workbook, with the same name as the worksheet. If you want to change the location where the PDF files are saved, you can modify the “SaveName” variable to specify a different path.

ChatGPT not only provides the code, but it also provides instructions for how to run the code. It concludes with what will happen, and even suggests how to edit the code if you want to change the pdf destination folder. By the way, I ran this macro and it worked!

Excel Help

You can ask ChatGPT for information on pretty much any topic. Here are a few other Excel examples.

Prompt:

Tell me in one sentence which excel feature can help build a report fast

Response:

The PivotTable feature in Excel can help build a report fast by summarizing and analyzing large amounts of data.

Prompt:

Which excel feature can help me get data from a csv and format it for a pivot table?

Response:

You can use Excel’s "Get & Transform Data" feature to import data from a CSV file and transform it for a pivot table. Here are the steps:
1. Open a new Excel workbook and click on the "Data" tab in the ribbon.
2. Click on "Get Data" and choose "From File," then select "From CSV."
3. Browse to the location of the CSV file and select it.
4. In the "Navigator" pane, select the data you want to import and click "Edit."
5. In the "Query Editor," use the tools in the "Transform" tab to format the data as needed for the pivot table. This may include renaming columns, changing data types, filtering rows, and more.
6. Once the data is formatted as needed, click "Close & Load" to import it into a new worksheet.
7. Create a pivot table from the imported data by clicking on the "Insert" tab in the ribbon and choosing "PivotTable." Select the imported data as the source for the pivot table.
8. Use the pivot table tools to analyze the data and create the desired view of the data.

Prompt:

Write a short love poem about excel

Response:

Oh Excel, my love, my sweetest delight,
Your rows and columns, always in sight.
Your functions and formulas, so precise,
In my heart, you hold a special place.

You make my data organized and neat,
With charts and graphs, my insights complete.
I can’t imagine my work without you,
My love for you, forever true.

From PivotTables to VLOOKUP,
You make my tasks easy to look up.
I never knew a software could be,
So beautiful, so dear to me.

Oh Excel, my love, my heart’s desire,
With you by my side, I never tire.
You are my rock, my steady hand,
My love for you, will always stand.

Now you have a few tips on using ChatGPT to supercharge your Excel skills. And remember, Excel rules!