How to Use ChatGPT for Excel, Excel (lent) Prompts for Excel

Change the way you interact with your spreadsheets by using ChatGPT for Excel. So far, on Tilburg.ai we have discussed how to use ChatGPT to improve your coding skills, use it directly in Word, and build your own customized AI tool with R or Python. In this article, we’ll look at how to master Microsoft Excel with ChatGPT. Being proficient in Excel remains a valuable skill, especially in the workplace. With the rise of ChatGPT, everyone can now use Excel in a better and more efficient way.

Although ChatGPT does not add extra features to Excel, its main advantage lies in the fact that you do not have to master Excel’s terminology. Now, you can communicate your intentions in Excel in your own language through a single entry point. This can save time and make using Excel easier, especially if you are not an expert. Therefore, you have a ‘reliable wingman‘ by your side, helping you explore all the functionalities of Excel, complete with explanations, step-by-step instructions, and use cases!

This article introduces some Excel(lent) ChatGPT prompts designed to work on your proficiency and efficiency in Excel and assist with your projects. Furthermore, It explains how ChatGPT can be used to add value to your Excel workbooks.

ChatGPT Prompts For Excel

In this section, we will walk you through several prompts, each showcasing a specific aspect of improving your Excel skills. Writing clear prompts, with a precise description of the Excel worksheet and intentions, makes sure that ChatGPT accurately answers your Excel-related tasks. Please watch our article about “How to write good prompts” if you want to learn more about how to make good prompts.

Learning Excel functions

If you’re struggling to understand an Excel function or need a detailed explanation of an argument, ChatGPT can be of perfect help. Here are some example cases of how you can frame your prompts to get the most out of ChatGPT’s replies.

Short Prompt Format

For an explanation of an Excel function, this prompt offers a direct, step-by-step explanation with a practical example:

• Simple Prompt Example:
  • Function: SUM
  • Prompt Template: Explain how Excel [function name] works. Give a step-by-step answer and provide an example.
  • Prompt to ChatGPT: Explain how the Excel SUM function works. Give a step-by-step answer and provide an example.

Long Prompt Format

When you want to have more control over ChatGPT’s answer. Seeking for a more precise answer, or a more zoomed-in function scenario, then the long prompt format is a better approach.

• Long Prompt Example:
  • Function: The combination of INDEX and MATCH.
  • Long prompt Template: Act as a proficient Excel user and explain how the [name of Excel function] works in a clear and concise manner. Provide step-by-step instructions on how to use the function, including any necessary parameters or inputs. Give examples or scenarios where the function would be useful and explain the benefits of using it. Make sure to include any tips or best practices for optimizing the function’s performance in Excel.
  • Prompt to ChatGPT: Act as a proficient Excel user and explain how the INDEX MATCH function combo works in a clear and concise manner. Provide step-by-step instructions on how to use these functions together, including necessary parameters or inputs. Give examples or scenarios where these functions would be useful, explain the benefits of using them over VLOOKUP, and include any tips for optimizing their performance in Excel.

Curious about the response, check it out for yourself using this link! 

Applying Excel Functions

Having learned new Excel functions, the next step is to understand how to apply them to solve your Excel problems. Whether you need a brief guide or an in-depth tutorial, specifying the right prompt in ChatGPT can help you through this process.

Short Prompt Format

For a quick understanding of the inner workings of a function, use the short prompt template:

• Short Prompt Example:
  • Function & Task: Use the IF function to categorize data based on a condition.
  • Short Prompt: How to use Excel [name of the function] to [what you want to do]? Give step-by-step guidance but skip the basic steps.
  • Prompt to ChatGPT: How to use Excel IF function to assign ‘High’ if values in column A are above 50 and ‘Low’ otherwise? Give step-by-step guidance but skip the basic steps.

Long Prompt Approach

For a more in-depth understanding of an Excel function, especially when it comes to more complex problems or a combination of functions, the long prompt format is suited.

• Long Prompt Example:
  • Function & Task: Use the VLOOKUP function to match employee names with their IDs.
  • Long Prompt: Act as a proficient Excel user and create a step-by-step guide on how to use the [Excel function] to perform a specific [action]. Break down the process into easy-to-follow steps, including examples. Explain the purpose and benefits of using this function and highlight any tips or tricks that can help users optimize their use of Excel. Provide clear and concise explanations to help users understand and successfully execute the task.
  • Prompt to ChatGPT: Act as a proficient Excel user and create a step-by-step guide on how to use the VLOOKUP function to find employee IDs based on their names from a table on another sheet. Break down the process into easy-to-follow steps, including examples. Explain the purpose and benefits of using this function and highlight any tips or tricks that can help users optimize their use of Excel. Provide clear and concise explanations to help users understand and successfully execute the task.

Check out ChatGPT's reply by using this link. 

Here is ChatGPT's Response.

Finding the Right Excel Commands

When you are unsure which Excel function or combinations of functions best fit your specific queries, ask ChatGPT to assist you in identifying the right Excel formulas.

Short Prompt Format

For plain or straightforward tasks where you need a quick command without detailed explanations, you can use the short prompt format:

• Short Prompt Example:
  • Task Description: Sum the values in a column that meet specific criteria.
  • Prompt Template: Write an Excel command to [describe the task]. Respond with code only.
  • Prompt to ChatGPT: Write an Excel command to sum values in column C where column B equals ‘Approved’. Assume data from rows 1 to 100. Respond with code only.

Long Prompt Approach

When your Excel task is more complex or when you need a command that is not only correct but also well-explained, the long prompt format is more suited:

• Long Prompt Example:
  • Task Description: Extract a list of unique suppliers from a large dataset where the suppliers are listed multiple times.
  • Prompt Template: Act as a proficient Excel user. Write an Excel command to perform a specific [action]. The command should accurately achieve the desired outcome. Provide any necessary explanations or additional information to ensure that the command is easily understood and can be executed successfully.
  • Prompt to ChatGPT: Act as a proficient Excel user. Write an Excel command to extract unique supplier names from column D, which contains duplicates. Explain the steps and provide any necessary additional information to ensure the command is clearly understood and executable.

Here is the reply of your Excel(lent) assistant.

By defining the task you need to complete in Excel, whether it requires a simple command or an explained procedure, ChatGPT provides the proper Excel function.

Those are all the example prompts for this article. Of course, we encourage you to build your own prompts! We hope these templates and examples will help you along the way! If you are unsure of how to build your prompt, check out this article, which discusses all the elements needed for a good prompt.

Create an Excel-lent Assistant 

Having a separate prompt for each task in Excel could give precise answers, yet it is inefficient, and if you are a significant user of ChatGPT, a tedious task. Instead, you can use a single prompt to let ChatGPT be an assistant for every Excel question you have.

This procedure is called prepping, which means we are given the AI model context or background information before posing a question or request. The primary difference between prepping and prompting lies in the approach to starting the conversation. While prompting presents a specific query or instruction to the AI, prepping sets the stage with context or background information, thereby shaping the AI’s understanding and the relevance of its response. If you are curious to learn more about the differences between prepping and prompting we encourage you to read this article.

Step 1: Prepping the AI model

Act as an Excel expert. Provide step-by-step instructions on how to complete a specific task in Excel. This could include tasks such as creating a pivot table, formatting data, using formulas or functions, creating charts or graphs, or any other Excel-related task. Clearly explain each step, specify any required settings, or options, explain what functions to use, and provide any additional tips or recommendations to ensure accurate and efficient completion of the task.

Step 2: Prompt your assistant

After setting the stage with your prepping prompt, you could prompt your assistant. For example use the following prompt: “I need to create a pivot table from a dataset listing monthly sales by product category. Can you guide me through the steps?“

For those who are alert, prepping the AI model means that most of the time the short prompt is sufficient!

Useful Tips

To get the most out of your interactions with ChatGPT, keep the following considerations in mind:

• Prioritize Precision: The explicitness of your prompts directly influences the accuracy of ChatGPT’s responses. Therefore, aim to be as detailed as possible when describing your Excel tasks. This can include specifying the version of Excel you’re using, the nature of your data, and the desired outcome.
  • For instance, rather than saying “Help with sales data,” specify “Guide me on how to calculate the average sales from January to March for product A using the AVERAGE function in Excel.”
• Use Clear Language: Avoid confusion or misinterpretation, by using clear, simple language and straightforward terminology. If you’re dealing with complex Excel functions or tasks, try to explain them in simple terms.
  • Instead of asking, “How do I analyze these numbers?” you can prompt, “Can you show me how to use the COUNTIF function to count how many sales transactions were above $500?”
• Decompose Complex Tasks: If your Excel task is complicated, try breaking it down into smaller, more manageable prompts. This procedure helps ChatGPT to provide more accurate and relevant responses.
  • If creating a financial report, first ask, “How do I sort this year’s revenue data by month in Excel?” before moving on to calculating trends or averages.
• Experiment and Refine: If the initial response does not completely solve your questions or seems off-target, try rephrasing your prompt or adding more context to your subsequent prompts. Adding more context, adjusting the phrasing, or specifying additional parameters can lead to more applicable solutions.
  • Initial Prompt: How do I make a chart in Excel?
  • ChatGPT Response: To create a chart in Excel, first select your data, then go to the ‘Insert’ tab and choose the chart type you want to create.
  • Refined Prompt: I want to create a scatter plot in Excel 2019 using data from columns A and B. Can you guide me through the steps?
• Always Verify: Something we of Tilburg.ai keep on hammering on, always double-check the AI’s advice. If ChatGPT suggests a formula, manually verify a few calculations to confirm the accuracy before applying it broadly in your workbook.

Conclusion

Traditionally, becoming a proficient Excel user requires a good understanding of its formulaic language. However, with upcoming AI technologies, such as ChatGPT, you can conversationally learn Excel. By using the example prompts of the prepping prompt provided, you can have your Excel-lent assistant at your desk, ready to help you wherever you need assistance!