Excel is a powerhouse tool for data analysis and management used daily by millions users in any sphere. However, getting the most of the app potential might be confusing or take too much time. At this point we recommend trying Microsoft 365 Copilot, intuitive way to enhance your Excel experience. Using this AI Assistant every user, even a non-tech savvy one will be able to facilitate the workload and still remain efficient.
In this blog post, we'll explore 20 top prompts you can use with Microsoft 365 Copilot to maximize your productivity.
1. Set up conditional formatting
Bold the top 10 values in [Annual sales]
To be clear
This sets up conditional formatting on a column so if the top 10 values change, the bold-faced ones change automatically too.
2. Compare columns
Add a column to compare [FY24 sales] and [FY23 sales]
To be clear
This prompt creates a column that compares the two named columns.
3. Extract text
Add columns to extract [the city, state, and zipcode from each address]
To be clear
This uses a table with data like mailing labels and breaks it down into separate columns for city, state, and zipcode
4. Spot trends
Are there any trends in my data?
Take it to the next level
You can specify what you'd like the result to look like. For example, "Show it as a chart" or "Show me trends over time".
5. Calculate based on other table
Create a column that calculates [the total sales for each employee]
To be clear
This prompt looks up sales and calculates totals for each employee
6. Split text
Add two columns that split [employees' combined name into separate first and last names]
To be clear
Using a table with a column of data, such as first and last names together, you're splitting them into separate columns
7. Lookup data
Return the [price of each product in a list based on the Inventory table]
To be clear
Look up the price of matching products based on another table.
8. Compare columns in different tables
Add a column to compare [product IDs in the Inventory table and the Sales table, returning "Available" for matches and "Not Available" if there are no matches.]#
To be clear
Use to compare and lookup things like the product ID on the sales table, comparing it to IDs in the Inventory table and reporting if it's available .
Take it to the next level
Add a column to compare product ids in the inventory table and the sales table and using sales quantity to return "In stock" or "Out of stock" based inventory quantities
9. Understand my data
Which items have the most [remaining inventory]?
More to try
You can use some visual cues to understand your data as well. For example you can ask Copilot to "Highlight the item with the most inventory in green and the item with the least inventory in red."
10. Extract the month
Add a column to extract the month from the date
To be clear
This prompt works best when the column that contains the dates is formatted as a date number format. For more information on formatting your dates in Excel see Format a date the way you want.
11. Combine columns
Add a column that combines [the customer's first and last name]
Take it to the next level
You can tell Copilot more about how you'd like the data formatted. For example, add "...as 'Last name, First name'."
Experiment
You can combine almost any kind of columns. For example, Date and Time or City, State, and Zip Code.
12. Highlight data
Highlight the [lowest 5 budgets]
To be clear
This highlighting is conditional formatting which will automatically update if your data does.
Take it to the next level
You can specify what color you'd like the highlight to be. “Highlight the lowest 5 budgets in blue.”
13. Get a count
How many different [teams are in this table?]
Take it to the next level
You can give Copilot additional criteria for it to count, such as "How many different teams with at least 8 members are in this table?"
14. Get column ideas
Suggest a column
To be clear
Copilot will analyze your data and suggest a formula for a column you might want to add.
More to try
If you like what Copilot suggests click the "Insert column" button to have it added to your table automatically.
15. See your data
Visualize this data
To be clear
This prompt will likely suggest a PivotChart that you can add to a new sheet.
Take it to the next level
If you want to have Copilot try to create a specific kind of chart you can give it that guidance. For example add "...as a pie chart."
16. Visualize relative values
What is the percentage of [total revenue from each region?]
To be clear
This will create a PivotChart that compares the percentages.
Experiment
Try using different calculations instead of percentage such as "What is the average of [total revenue from each region?]"
More to try
You can choose to have that PivotChart added to a new sheet.
17. Compare dates
Add a column to compare the current date with the [Due date]
More to try
You can add more visual impact to this new column by telling it to make "Past" one color and "Future" a different color.
To be clear
Replace "Due date" with the name of any date column in your table.
18. Check for outliers
Are there any outliers in my data?
Experiment
You can also ask to highlight the outliers in blue.
19. Find insights
Show data insights
Experiment
You can also try "Analyze this data" or "What are the insights".
More to try
Add all insights to a new sheet.
20. Apply color scale
Apply a red-yellow-green color scale to the [Total column]
To be clear
This works best on numerical columns.
Experiment
You can try different sets of colors.
Conclusion
Summing all above, a right Microsoft 365 Copilot prompt can boost your user experience with Excel. Working with complex tables, enabling tricky functionalities, managing large data bases is a breeze with the help of a smart AI assistant offered by Microsoft Copilot. Excel mastery is just a few prompts away!
You can find more useful tips and tricks for Microsoft 365 Copilot here https://copilot.cloud.microsoft/en-us/prompts/all
