Prompt Advance logo

6 ChatGPT Prompts for Excel You Need To Try in 2024

6 ChatGPT Prompts for Excel You Need To Try in 2024

Excel is a powerful tool. But let's be honest, it's intimidating as hell. Especially when you're faced with complex tasks and don't know where to start.

But what if I told you there's a smarter way to work with Excel? A way that involves a bit of AI magic?

In this post, I'll show you a series of ChatGPT prompts specifically designed for Excel. Whether you're a beginner struggling with basic functions or an advanced user looking to streamline your workflow, these prompts will help you learn Excel like never before.

These are the best ChatGPT Prompts for Excel:

  1. Explain how Excel function works
  2. Explain how to use Excel functions
  3. Write Excel commands
  4. Write macros
  5. Perform complex tasks
  6. Turn ChatGPT into a useful Excel assistant

In the next section, we'll take a closer look at each aspect of using ChatGPT for Excel (along with the prompt examples). And if you read till the end, you'll also find a little tip to use these prompts more effectively.

Want to Master AI in 5 Days?

ChatGPT Prompts for Excel

This section is for all Excel enthusiasts out there - beginners, intermediates, and experts alike. It has the most useful ChatGPT prompts to improve your Excel skills.

1. To Explain How Excel Function Works

It turns out ChatGPT is good at explaining all kinds of stuff. So why not use it to answer your Excel questions? You can use the following 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.

I used the prompt template and asked ChatGPT to explain the Excel SUM function.

Act as a proficient Excel user and explain how SUM 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.

Certainly! Excel's SUM function is used to add up a range of numbers within a selected set of cells. It's a fundamental mathematical function that helps you calculate the total of multiple values. Here's a step-by-step explanation of how the SUM function works in Excel:

  1. Select a Cell: Choose a cell in which you want the sum to appear. This is typically where you'll see the final sum of the numbers.
  2. Enter the SUM Function: In the selected cell, type =SUM(. This tells Excel that you want to use the SUM function to calculate a total.
  3. Select Range: Now you need to select the range of cells that you want to add up. You can do this by clicking and dragging the mouse over the cells you want to include in the sum, or you can manually enter cell references. For example, if you want to sum the numbers in cells A1 to A5, you can enter A1:A5.
  4. Close Parenthesis: After you've selected the range, type ) to close the parenthesis. Your formula should now look something like =SUM(A1:A5).
  5. Press Enter: Press the "Enter" key on your keyboard. Excel will immediately calculate the sum of the numbers within the selected range and display the result in the cell where you entered the formula.
  6. View Result: The cell will now show the calculated sum of the numbers in the selected range. For instance, if the values in cells A1 to A5 were 10, 15, 20, 5, and 30, the result would be 80.

Keep in mind that the SUM function can handle a range of different data types, not just numbers. If you include cells with text, logical values (TRUE/FALSE), or empty cells, Excel will attempt to sum them according to their relevant values or characteristics. However, the function will ignore cells that contain text or non-numeric data when calculating the sum.

Additionally, you can use the SUM function with multiple ranges or even individual cell references. For instance, you could use =SUM(A1:A5, B1:B5) to sum values from two different ranges. The SUM function can also be used in formulas with conditions, where you sum only the values that meet specific criteria.

In summary, Excel's SUM function is a powerful tool for quickly calculating totals of numeric values within specified ranges or cells. It streamlines the process of adding multiple values together, saving you time and reducing the risk of manual errors.

2. To Explain How to Use Excel Functions

Excel functions can be confusing. But, with the help of the next prompt, you can understand and use these functions more effectively. The following prompt will guide you through the process.

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 instructions, including screenshots or examples where necessary. Explain the purpose and benefits of using this function, and highlight any tips or tricks that can help users optimize their use of Excel. Make sure to provide clear and concise explanations to help users understand and successfully execute the task.

In the following example, I wanted to know how to get an average value from cells.

How to use Excel AVG to get the average value from all the fields in column C? Explain step-by-step, but leave out obvious instructions.

Act as a proficient Excel user and create a step-by-step guide on how to use AVG to get the average value from all the fields in column C. Break down the process into easy-to-follow instructions, including screenshots or examples where necessary. Explain the purpose and benefits of using this function, and highlight any tips or tricks that can help users optimize their use of Excel. Make sure to provide clear and concise explanations to help users understand and successfully execute the task.

Certainly, here's a step-by-step guide on how to use the AVG function in Excel to calculate the average value from all the cells in column C:

  1. Open Excel: Launch Microsoft Excel and open the workbook containing the data.
  2. Navigate to the Cell: Click on an empty cell where you want the average value to appear. This cell will be your output cell.
  3. Enter the Formula: Type the following formula into the selected cell.
=AVERAGE(C:C)

Please note that the formula assumes that all the values in column C are numeric. If there are any non-numeric values, they will be ignored by the AVERAGE function.

Remember that Excel functions may vary slightly depending on the version you're using, but the steps provided should be generally applicable across different versions.

3. To Write Excel Commands

If you're an Excel beginner like me, there's a good chance you'll need help with writing Excel commands. As it turns out, ChatGPT can help here as well. Just use the prompt below.

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.

Using this prompt, you need to explain what kind of action you're trying to perform in Excel. And ChatGPT will do the rest.

Act as a proficient Excel user. Write an Excel command to use the SUM function to add up a range of cells. 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.

Sure, here's the Excel formula using the SUM function to add up a range of cells:

=SUM(A1:A10)

Replace A1:A10 with the actual range of cells you want to sum.

4. To Write Macros

If you're a fan of automation with Excel, you probably heard of macros. But did you know you don't need to write macros yourself? ChatGPT can help you in this process.

A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. (Source: Microsoft)

Below is a prompt template to help you achieve this.

Act as a proficient Excel user with knowledge of VBA. Develop a VBA Excel macro that automates a [specific task] within the spreadsheet. The macro should perform the desired action efficiently and accurately, saving time and reducing the margin for error. Ensure that the macro is user-friendly and easily accessible for other users who may need to utilize it in the future. Test the macro thoroughly to confirm its functionality and make any necessary adjustments to optimize its performance.

I tried to use it to get a simple macro that reads the value from a cell and opens a worksheet based on that value.

Act as a proficient Excel user with knowledge of VBA. Develop a VBA Excel macro that takes the value in cell C2 and finds the worksheet with the exact name. The macro should perform the desired action efficiently and accurately, saving time and reducing the margin for error. Ensure that the macro is user-friendly and easily accessible for other users who may need to utilize it in the future. Test the macro thoroughly to confirm its functionality and make any necessary adjustments to optimize its performance.

Sure, here it is:

Sub FindWorksheetByName()
    Dim targetName As String
    Dim ws As Worksheet
    
    targetName = ThisWorkbook.Sheets("Sheet1").Range("C2").Value
    
    On Error Resume Next
    Set ws = ThisWorkbook.Sheets(targetName)
    On Error GoTo 0
    
    If Not ws Is Nothing Then
        ws.Activate
    Else
        MsgBox "Worksheet '" & targetName & "' not found."
    End If
End Sub

5. To Perform Complex Tasks

With the help of the next prompt, you can execute complicated tasks without breaking a sweat. The following prompt will guide you on how to do just that.

Act as a proficient Excel user. Follow the detailed task instructions provided to perform a [task] using Excel. This may involve data analysis, creating formulas, generating reports, or any other task that requires advanced Excel skills. Ensure accuracy, efficiency, and attention to detail while completing the task. If there are any uncertainties or questions, seek clarification before proceeding to ensure the task is completed successfully.

As you can see, I received the instructions, also with a brief explanation of what each function does.

Act as a proficient Excel user. Follow the detailed task instructions provided to extract the fourth word in cell A1, but only when cell B1 is smaller than 10. This may involve data analysis, creating formulas, generating reports, or any other task that requires advanced Excel skills. Ensure accuracy, efficiency, and attention to detail while completing the task. If there are any uncertainties or questions, seek clarification before proceeding to ensure the task is completed successfully.

It sounds like you're referring to a scenario in a spreadsheet, possibly using Microsoft Excel or Google Sheets. If you want to extract the fourth word in cell A1, but only when the value in cell B1 is smaller than 10, you can use a combination of functions like IF, SPLIT, and INDEX (or SUBSTITUTE and MID). Here's how you might do it:

Assuming you're using Microsoft Excel:

In a different cell (let's say C1), you can use the following formula:

=IF(B1<10, INDEX(SPLIT(A1, " "), 4), "")

Here's how this formula works:

  1. SPLIT(A1, " ") - This function splits the text in cell A1 into an array of words, using space as the delimiter.
  2. INDEX(..., 4) - This function returns the fourth element from the array created by the SPLIT function.
  3. IF(B1<10, ..., "") - This checks if the value in cell B1 is smaller than 10. If true, it returns the result of the INDEX function; otherwise, it returns an empty string.

Make sure you adjust the cell references (A1 and B1) according to your actual data placement.

6. Turn ChatGPT Into a Useful Excel Assistant

It's useful to have a specific prompt for each Excel task. But in reality, it's hard to manage a lot of prompts. The better way is to have one prompt that turns ChatGPT into an Excel assistant that can help you with any task.

Here's the prompt that will do that.

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.

Help me with the following task: [describe your task]

The best part about this prompt? You can use it for any Excel-related task. And you'll always receive clear step-by-step instructions.

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.

Help me with the following task: I need to hide all the cells where the value is smaller than 10.

Of course, I'd be happy to help you with that Excel task! To hide all the cells where the value is smaller than 10, you can follow these step-by-step instructions:

Step 1: Open the Excel spreadsheet containing the data you want to work with.

Step 2: Select the range of cells that you want to hide based on the condition (values smaller than 10). You can do this by clicking and dragging to select the range, or you can click on the first cell and then hold the Shift key while clicking on the last cell in the range.

Step 3: Once you've selected the range, right-click on any of the selected cells. This will open a context menu.

Step 4: In the context menu, hover over the "Format Cells" option and then click on "Format Cells."

Step 5: In the "Format Cells" dialog box that opens, go to the "Number" tab.

Step 6: In the "Category" list on the left side, select "Custom."

Step 7: In the "Type" field on the right side, enter three semicolons (;;;) without the quotation marks. This is a custom number format that will display the cells as blank while retaining their actual values.

Step 8: Click the "OK" button to apply the custom number format.

Step 9: After applying the custom number format, the cells with values smaller than 10 will appear blank, effectively hidden from view.

Step 10: If you want to revert the cells back to their original appearance, you can select the range again, right-click, choose "Format Cells," go to the "Number" tab, and select a different number format (such as "General") from the "Category" list.

How to Use These Prompts Effectively

The prompts I mentioned today are also available in our FREE prompt directory. You can check them out here: ChatGPT prompts.

ChatGPT prompts
Free ChatGPT Prompt Directory

How To Generate Custom Prompts

Didn't find the prompt you need? Try our FREE ChatGPT Prompt Generator to generate one for you!

ChatGPT prompt generator
ChatGPT Prompt Generator

Final thoughts

ChatGPT prompts shared in this post are powerful tools that can transform how you work with Excel. They can help automate tasks, simplify processes, and improve your productivity.

Remember, Excel is more than just a spreadsheet. It's a tool for success in business, education, or personal management. And by using these prompts, you have a better chance to succeed in all these areas.

Want to Master AI in 5 Days?

FAQ

Let's also address some of the common questions about using ChatGPT for Excel.

What are Excel ChatGPT prompts?

Excel Chat GPT prompts are instructions being used with ChatGPT to provide information on how to accomplish Excel-related tasks.

What Excel tasks can ChatGPT help with?

ChatGPT can be used to handle a wide range of complex tasks. Whether it's data analysis, visualizations, advanced calculations, or writing complex macros, ChatGPT can help you accomplish any of these tasks.