31 ChatGPT Prompts for Excel (Automating Routine Tasks)

ChatGPT is transforming the way we use Excel.

From figuring out tricky formulas to creating pivot tables, interpreting data insights, and even enhancing project organization—its potential is genuinely groundbreaking.

But with numerous opportunities, it can be tough to know where to begin.

That's why I've put together this guide.

In this guide, based on actual Excel situations and numerous hours of playing around with ChatGPT, I'll share reliable ChatGPT prompts for Excel users.

Let's get started.

ChatGPT Prompts for Excel

Create and format a new spreadsheet

ChatGPT can help you in creating and formatting a new spreadsheet in Excel.

You can provide it with the details of the data you want to include, and it can create the spreadsheet with the given data.

Additionally, it can add formatting such as bold, italics, conditional formatting, or even create charts based on your needs.

ChatGPT Prompt:

Act as an experienced Excel user to create and format a new spreadsheet.

I want a spreadsheet that tracks the monthly sales data of my business, highlighting the top-selling products in bold.

It should also include a pie-chart to visually represent the sales distribution.

Here is the sales data for the past year:

Import and export data in Excel

Managing data efficiently is a crucial aspect of Excel.

ChatGPT can guide you through the process of importing and exporting data in Excel, whether it's a CSV, text file, or another spreadsheet.

You can ask for step-by-step instructions on how to perform these tasks, or inquire about best practices to avoid errors.

For example, you can request a guide on how to import a CSV file into Excel without losing any data.

ChatGPT Prompt:

Act as an Excel expert and provide step-by-step instructions on how to import a CSV file into Excel.

Also, share some tips on what to be cautious of while performing this task to prevent any loss of data.

Apply basic formulas and functions

When working with Excel, using formulas and functions can greatly streamline your data analysis process.

ChatGPT can help you apply these formulas and functions to your data.

Simply provide the dataset and the operation you want to perform.

For instance, you may want to calculate averages, sums, or identify the maximum or minimum value in a dataset.

ChatGPT Prompt:

Act as an experienced data analyst to help me apply basic Excel functions to this dataset.

Calculate the average, sum, maximum, and minimum values for the given column of data:

Use conditional formatting to highlight data

Conditional formatting is an essential feature in Excel that highlights cells with a certain color, depending on the cell's value.

ChatGPT can guide you through creating rules for conditional formatting to simplify data analysis.

For instance, you can use conditional formatting to highlight cells that contain values above a certain threshold or to distinguish between odd and even numbers.

ChatGPT Prompt:

Act as an Excel expert and guide me through using conditional formatting to highlight cells that contain values greater than 50.

Also, explain how to create a rule to differentiate between odd and even numbers.

Create and edit charts, graphs and pivot tables

ChatGPT can be used as a guide to create and edit charts, graphs, and pivot tables in Excel.

Simply feed it with your data and ask for a step-by-step procedure on how to create a specific chart or graph.

It can also explain the use of pivot tables in data summarization and analysis.

For example, you can ask ChatGPT how to create a pie chart or a pivot table with your given data set.

ChatGPT Prompt:

As an Excel expert, guide me on how to create a pie chart and a pivot table using the following dataset:

Utilize the VLOOKUP and HLOOKUP function

VLOOKUP and HLOOKUP functions are crucial in Excel for searching and referencing data across columns and rows.

Whether it's locating employee data or merging two datasets, these functions can save you a lot of time.

For example, use VLOOKUP to find data in a vertical list, and use HLOOKUP for data in a horizontal list.

ChatGPT Prompt:

Assume you're an expert Excel user.

We have two tables of data: one with employee names and IDs, another with IDs and their corresponding salaries.

Demonstrate how to use VLOOKUP and HLOOKUP functions to associate each employee with their respective salary.

Here are the data tables for your reference:

Implement IF, COUNTIF, and SUMIF functions

Excel has a variety of functions that can help us analyze and manipulate data.

Functions such as IF, COUNTIF, and SUMIF are among the most commonly used.

The IF function performs a logical test and returns one value if the condition is TRUE, and another for FALSE.

COUNTIF counts the number of cells in a range that meet a given condition.

SUMIF adds all the numbers in a range of cells based on one criteria.

ChatGPT Prompt:

Act as an experienced Excel user to demonstrate the usage of IF, COUNTIF, and SUMIF functions.

Here is the data set:

Apply data validation rules

Data validation in Excel helps maintain the accuracy and consistency of the data in your spreadsheet.

Ask ChatGPT to guide you through the process of applying data validation rules, such as allowing only specific data types, number ranges, or predefined options in certain cells.

For example, you can ask ChatGPT to assist in creating a dropdown list for a specific cell range.

ChatGPT Prompt:

As an expert in Excel, guide me through the steps of applying data validation to a range of cells.

I want to create a dropdown list in cells A1:A10 that only allows the options 'Pass', 'Fail', and 'In Progress'.

Sort and filter data in a spreadsheet

Sorting and filtering data in Excel can be a daunting task.

ChatGPT can simplify the process by guiding you step by step.

Whether you need to organize data alphabetically, numerically, or based on specific criteria, ChatGPT can provide a custom-tailored approach.

For example, you can ask ChatGPT to help sort a column of dates in ascending order or filter out specific data based on a condition.

ChatGPT Prompt:

Act as an Excel expert, guide me on how to sort a spreadsheet containing employee data by the 'Hire Date' column in ascending order and filter out the employees who were hired after 2015.

Use Excel for data analysis and reporting

Excel is a powerful tool with numerous functionalities that can assist you in data analysis and reporting.

You can input large datasets, create pivot tables, apply filters, and use formulas to organize, analyze, and interpret data.

ChatGPT can guide you through data processing, data cleaning, and data visualization in Excel.

ChatGPT Prompt:

Act as an Excel expert, guide me in organizing and analyzing this dataset using Excel.

I want to identify trends, patterns and also generate a report from the findings.

Here is the dataset I need to analyze:

Apply Excel for financial modeling and forecasting

Excel is a powerful tool for financial modeling and forecasting.

By inputting data and establishing formulas, you can create models to predict financial outcomes and trends.

ChatGPT can assist you in setting up these models, giving advice on best practices, and providing guidelines on interpreting the results.

For instance, provide ChatGPT with a set of financial data and ask it to guide you through creating a financial model in Excel.

ChatGPT Prompt:

Act as a financial analyst and guide me through creating a financial forecasting model in Excel.

Here is the financial data from the past five years for XYZ company:

Generate macros to automate repetitive tasks

ChatGPT can assist you in generating macros to automate repetitive tasks in Excel.

It can suggest the necessary steps or even write the VBA code snippets for you.

By providing details about the repetitive task, such as the process and desired outcome, ChatGPT can create the corresponding macros.

For example, you can ask ChatGPT to write a macro to format cells, automate data entry, or generate reports.

ChatGPT Prompt:

Act as an experienced Excel user to create a macro that automates the task of highlighting cells with values greater than 100 in a given dataset.

Please write the VBA code for this task.

Utilize the CONCATENATE function for data merging

The CONCATENATE function in Excel is a fantastic tool that you can use to merge data from different cells into a single cell.

For instance, ChatGPT can guide you through using CONCATENATE to combine first and last name columns into a full name column.

Additionally, it can help in creating comprehensive lists, addresses, or other combined data points.

ChatGPT Prompt:

Act as an Excel expert and guide me through the process of using the CONCATENATE function to merge data.

I have two columns, one with first names and the other with last names.

I want to merge them into a full name in a single column.

Please provide step-by-step instructions.

Use the TEXT function for data formatting

The TEXT function in Excel is a powerful tool that allows you to convert a numeric value into text in a specific number format.

You can utilize the TEXT function to format dates, times, percentages, currency, and more.

For instance, you can transform a date like 20220220 into a more readable format like 20-Feb-2022.

ChatGPT Prompt:

Act as an Excel expert and teach me how to use the TEXT function to format numeric values into text.

Here is a numeric value that needs to be converted into a date format: 20220220

Apply the DATE and TIME function

Excel's DATE and TIME functions are used to create date and time values from individual components.

ChatGPT can provide you with step-by-step instructions on how to apply these functions in your spreadsheets effectively.

Whether you're trying to track work hours, event schedules, or just need to organize information by date and time, ChatGPT can help.

ChatGPT Prompt:

Act as an Excel expert and guide me through the process of using the DATE and TIME functions.

I have a dataset where the day, month, year, hours, and minutes are all in separate columns and I want to combine them into a single cell.

Use Excel for project management tracking

Excel is an incredibly useful tool for project management tracking.

You can use it to create schedules, manage budgets, track progress, and much more.

ChatGPT can assist in organizing data in Excel, analyzing it, and even offering tips for more efficient use.

For example, provide ChatGPT with raw project data and ask for insights, or ask it to suggest the best way to track specific project elements within an Excel spreadsheet.

ChatGPT Prompt:

As an expert in Excel and project management, please help me organize this raw project data in a way that allows for easy tracking and analysis.

Also, suggest the best practices for tracking project timelines, deliverables, and resource allocation within an Excel spreadsheet.

Here is the data I have:

Create a dynamic drop-down list

Creating a dynamic drop-down list can be a tricky task for some, but with ChatGPT, you can make it a breeze.

Provide ChatGPT with the instructions or specifications you want to implement, and it will guide you step by step to create a dynamic drop-down list in Excel.

From defining named ranges to incorporating them in the data validation, ChatGPT can help.

ChatGPT Prompt:

Act as an expert in Excel and provide me with a guide on how to create a dynamic drop-down list.

I want the list to be updated as soon as I enter new data.

Protect and secure your Excel data

Managing and protecting your Excel data is crucial for maintaining confidentiality and avoiding unauthorized changes.

ChatGPT can guide you through the process of securing your Excel files, explaining how to use password protection, read-only status, and other protective features.

For instance, you can ask ChatGPT for steps to apply password protection to your Excel file.

ChatGPT Prompt:

As an Excel expert, guide me through the steps to apply password protection to my Excel file to secure the data.

Also, recommend other measures to further improve the security of my Excel data.

Use Excel for inventory management

Excel is an extremely useful tool for inventory management due to its data storage and analysis capabilities.

You can use Excel to keep track of your stock levels, calculate costs, and predict future inventory needs.

ChatGPT can guide you in creating an efficient Excel inventory management system.

ChatGPT Prompt:

Act as an Excel expert assisting in setting up an inventory management system.

I need to track stock levels, calculate costs, and predict future inventory needs for my small retail business.

Guide me through the process of setting this up in Excel.

Create a budget and expense tracker in Excel

You can task ChatGPT to guide you in creating a budget and expense tracker using Excel.

Provide it with information about your income sources, fixed and variable expenses, and any other financial details.

ChatGPT will then outline a structure for your Excel file, suggesting the best way to categorize your data for an efficient and insightful financial analysis.

ChatGPT Prompt:

Act as an experienced financial analyst and guide me in creating a budget and expense tracker in Excel.

Here are my financial details:

- Monthly Income:...

- Fixed Expenses:...

- Variable Expenses:...

Use Excel for data visualization

Excel is a powerful tool that can be used to generate a wide variety of data visualizations.

It's ideal for creating pie charts, bar graphs, scatter plots, and more to help you understand trends and patterns in your data.

Have ChatGPT guide you through the process of creating a specific type of chart in Excel or provide general tips for data visualization using Excel.

ChatGPT Prompt:

Act as an experienced data analyst and guide me in creating a scatter plot in Excel.

I have a dataset with two variables, 'Age' and 'Income'.

Understand and use the power query feature

Power Query is an exceptional tool in Excel for data wrangling and data cleaning.

ChatGPT can help you understand the intricacies of Power Query and demonstrate its use for various data manipulation tasks.

Just provide the specific task you are struggling with, and ChatGPT will guide you through the process using Power Query.

ChatGPT Prompt:

Act as an experienced Excel user explaining how to use Power Query.

I have a large dataset with many redundancies and unnecessary columns.

Show me how to clean this data using Power Query in Excel.

Apply the OFFSET function for dynamic calculations

Using the OFFSET function in Excel can help create dynamic calculations and reference a range of cells.

ChatGPT can guide you through the process of using the OFFSET function in Excel, which can be quite handy for complex calculations involving dynamic ranges.

Just provide ChatGPT with the details of the calculation, and it will help you apply the OFFSET function.

ChatGPT Prompt:

As an Excel expert, guide me on how to use the OFFSET function for dynamic calculations.

I have a dataset in the range A1:E20, and I want to create a sum of the values that are 3 rows down and 2 columns to the right of cell C10.

Use Excel for statistical analysis

Excel is an invaluable tool for statistical analysis.

You can input a dataset, and ask ChatGPT to generate descriptive statistics, conduct a regression analysis, or even create a scatter plot.

ChatGPT can also advise on best practices for data cleaning and organization in Excel to ensure accurate results.

ChatGPT Prompt:

You are an expert data analyst proficient in Excel.

I have a dataset that I would like to analyze for trends and patterns.

Guide me on how to clean and organize the data, and then conduct a comprehensive statistical analysis using Excel.

Create a custom Excel template

Designing a custom Excel template can be a daunting task, but ChatGPT can simplify the process for you.

Provide ChatGPT with details such as the type of data, the kind of analysis you need, and the desired layout.

It can then guide you step by step through setting up the appropriate cells, formulas, and formatting.

ChatGPT Prompt:

Act as an experienced data analyst and guide me in creating a custom Excel template for tracking monthly business expenses.

Describe how to set up the cells, which formulas to use, and suggest a suitable layout for easy comprehension.

Troubleshoot common Excel errors and issues

Excel can sometimes be complex and throw up errors that are hard to decipher.

ChatGPT can help you troubleshoot these common Excel problems like formula errors, file opening issues, or even data import/export errors.

Provide it with details of the error you're encountering, or ask it for general troubleshooting advice.

ChatGPT Prompt:

As an experienced Excel user, I need help troubleshooting an error I'm encountering.

Every time I attempt to use VLOOKUP, I get an #N/A error.

Can you explain why this might be happening and how I can resolve it?

Understand and apply array formulas

Array formulas are powerful tools in Excel that carry out calculations on the arrays of values.

ChatGPT can help you understand how they work, and even create array formulas based on your specific needs.

From summing up rows or columns, to complex calculations, array formulas can significantly reduce the amount of manual work.

ChatGPT Prompt:

Act as an Excel expert and explain how to use array formulas.

I have a set of data in rows 3 to 10 and columns A to F.

I need to calculate the sum of each row's values, where the value is greater than 5.

Import data from web into Excel

Working with Excel and need to import data from the web?

ChatGPT is here to help.

Whether it's a table from a webpage or data from an API, you can ask ChatGPT to guide you through the process.

This can include instructing you how to use Excel's built-in web query tool or how to write macros to fetch and parse data.

ChatGPT Prompt:

As an Excel expert, guide me through the process of importing data from a webpage into an Excel worksheet.

Assume the data is in a table format on the webpage.

Learn advanced features like PowerPivot and PowerView

Understanding advanced Excel features such as PowerPivot and PowerView can significantly enhance your data analysis capabilities.

With ChatGPT, you can get step-by-step guidance on using these features effectively.

For instance, you can have it explain how to create a data model using PowerPivot or how to visualize data using PowerView.

ChatGPT Prompt:

Act as an experienced Excel user and guide me on how to create a data model using PowerPivot and visualize it using PowerView.

Use Excel for payroll and HR metrics

Excel, with its advanced data processing abilities, can be a powerful tool for managing payroll and HR metrics.

You can use Excel for creating payroll templates, calculating salaries, managing time-off records, and keeping track of overtime.

For HR metrics, Excel can aid in tracking performance indicators, analyzing employee turnover rates, and assessing training effectiveness.

ChatGPT Prompt:

Act as an experienced HR professional using Excel to manage payroll and HR metrics.

Help me create a payroll template that includes employee's basic pay, allowances, deductions, and net pay.

Also, guide me on how to track key HR metrics such as employee turnover and training effectiveness.

Implement the INDEX MATCH function for complex lookups.

Excel's INDEX MATCH function can be a powerful tool for complex lookups, especially when the lookup value does not exist in the leftmost column of the lookup table.

You start by using the MATCH function to find the position of a specific value in a row or column.

Then, the INDEX function uses that position to find the corresponding value in a different row or column.

ChatGPT Prompt:

Act as an Excel expert to guide through the process of implementing an INDEX MATCH function.

The task is to find the corresponding value of Product B from column Price in the dataset.

 

Conclusion

Wow! We've journeyed far together.

From ideating Excel functions to refining data calculations, crafting spreadsheet formulas, and scrutinizing data trends, ChatGPT is revolutionizing how we use Excel.

Consider it your helpful aide when you're perplexed, your computation buddy for intricate data, and your thought collaborator for innovative problem-solving.

But bear in mind:

ChatGPT is an instrument, not a substitute for your proficiency. Combine its strengths with your own comprehension to unveil truly significant outcomes.

Now it’s your moment.

Choose a few prompts from this guide and implement them in your upcoming data analysis, financial forecast, or team report. You could be amazed at just how much more efficient—and inventive—you become.

And if you're eager to discover even more potent tools that extend beyond ChatGPT, take a look at Galaxy.ai.

With a comprehensive range of AI tools at your disposal, it’s the supreme efficiency partner for modern Excel users.

Happy Excel-ing! 🚀