Excel How To Understand a Formula with ChatGPT

Have you ever tried to reverse engineer or understand a complex Excel formula? Perhaps you inherited a workbook, and you encounter functions that you haven’t used before. Or, there are multiple functions in a single formula. It would really be nice if the person who created the formula could walk you through it. When this is not possible, we can use a free tool called ChatGPT to break down and explain Microsoft Excel formulas.

Video

Step-by-step guide

Chat GPT is a great tool to help you understand the purpose of formulas and how they work, so let’s dive right in and see how it works.

Example 1

First, let’s take a look at a workbook with an example formula.

The formula in cell B2 is:

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

It appears as if the formula in B2 is trying to extract the first name from the full name in B1. But when we examine the formula, it’s not immediately clear what each function is doing and how the arguments work. No worries … we can use ChatGPT to explain the formula to us.

We get started by opening a new chat at chat.openai.com.

We type “Please explain the following Excel formula: =RIGHT(B1,LEN(B1)-FIND(“, “,B1,1)-1)” and then hit Enter. ChatGPT provides the following response:

Certainly! The Excel formula you provided, =RIGHT(B1,LEN(B1)-FIND(“, “,B1,1)-1), is used to extract a specific portion of text from a cell. Let’s break it down step by step:

1. FIND(“, “,B1,1) – This part of the formula searches for the position of the first occurrence of the string “, ” (comma followed by a space) within the contents of cell B1. The FIND function returns the starting position of the found string.
2. LEN(B1)-FIND(“, “,B1,1)-1 – Here, the LEN function is used to determine the length of the entire text in cell B1. We subtract the position found in step 1 (including the space and comma) from the length to calculate the number of characters remaining after the string “, “.
3. RIGHT(B1,LEN(B1)-FIND(“, “,B1,1)-1) – The RIGHT function extracts a specified number of characters from the right side of a string. In this case, it takes the contents of cell B1 and extracts the number of characters calculated in step 2, starting from the rightmost character. This gives us the desired portion of text.

As you can see, ChatGPT explains what the formula is accomplishing, and proceeds to provide a breakdown of the key functions and arguments.

Example 2

Now, let’s take a look at a different example. In this workbook, the formula in C4 is using the INDEX and MATCH functions to return a value from the table based on the specified criteria in C2 and C3.

The formula in cell C4 is:

=INDEX(Table1,MATCH(C2,Table1[Product],0),MATCH(C3,Table1[#Headers],0))

We can use ChatGPT to understand the formula in the same way.

We write a new ChatGPT prompt that starts with “explain the following Excel formula: ” and paste in the formula. We hit Enter, and bam. ChatGPT responds with:

Certainly! Here’s the summary of the Excel formula:

In essence, this formula looks for a value in Table1 based on the values in cells C2 and C3, and returns the corresponding value at the intersection of the matching row and column within Table1.

Let’s break it down step by step:

1. INDEX(Table1, row_num, column_num): The INDEX function is used to return the value at a specific row and column within a given range or table. In this case, the range being referred to is Table1.
2. MATCH(C2, Table1[Product], 0): The MATCH function is used to find the position of a value within a specified range or column. Here, it is used to find the position of the value in cell C2 within the Table1[Product] column. The 0 at the end indicates an exact match.
   • C2 refers to the cell containing the value to be looked up within the Table1[Product] column.
   • Table1[Product] refers to the entire column named “Product” within the Table1 table.
3. MATCH(C3, Table1[#Headers], 0): Similar to the previous MATCH function, this one is used to find the position of the value in cell C3 within the table headers of Table1. The #Headers notation refers to the row containing the column headers of the Table1 table.
   • C3 refers to the cell containing the value to be looked up within the table headers.
   • Table1[#Headers] refers to the row containing the column headers within the Table1 table.

As you can see, it explains that the formula is using the INDEX and MATCH functions to return a value from a table based on the specified criteria. It also talks about the table range, lookup value, column, and header row.

Conclusion

As you can see, ChatGPT is a great tool for breaking down and understanding complex formulas in Microsoft Excel.

If I can provide any additional information, or if you have any suggestions or recommendations for others, please share by posting a comment below … thanks!

Sample file