If you鈥檝e ever wished Excel could just “do the work for you,” then Microsoft Copilot might be exactly what you need. This AI-powered assistant can generate formulas, analyze data, apply formatting, and even explain complex calculations鈥攁ll with simple, natural-language commands.
Want to access Copilot?Check out this Microsoft Office 365 Bundle which includes Copilot, Excel, Word and PowerPoint. It is a one year subscription for $99.99. Use our Amazon affiliate link to ensure you grab the correct license and to help support Excel University:
Buy on Amazon: Office 365 with Copilot Video Step-by-step GuideIn this post, we鈥檒l explore how to use Copilot for three essential Excel tasks:
Creating calculated columns Understanding data through analysis and visualizations Editing and applying formatting with easeLet鈥檚 dive in and see how Copilot can transform the way we work in Excel!
1. Creating Calculated Columns with CopilotOne of the most powerful features of Copilot is its ability to generate calculated columns based on a simple prompt. Instead of manually writing formulas, you can just describe what you need, and Copilot does the rest.
Example 1: Calculating Variance in Budget vs. ActualsLet鈥檚 say we he a table with Actual and Budget values, and we want to calculate the variance.
Here鈥檚 how we can do it:
Click Home > Copilot
Enable AutoSe (required for Copilot)
Type the prompt:
“Add a variance column.”
Copilot will suggest a formula (e.g., =Actual - Budget) and provide a preview.
Click Insert Column to apply it.
Since this is a formula-based column, it will automatically update when new data is added!
Example 2: Calculating Variance PercentageNow, let鈥檚 add a variance percentage column:
Use the prompt: “Add a variance percent column.” Copilot suggests a formula like: =(Actual - Budget) / Budget Click Insert Column, and the formula will be applied throughout the table. Example 3: Adding Sales CalculationsFor a sales dataset with Quantity and Price, we can create an Extended Price column.
Type:
“Add an extended price column.”
Copilot suggests multiplying the quantity by the price
Click Insert Column to apply it.
We can also add tax and shipping calculations:
“Add a tax column at 5%.” “Add a shipping column: $10 if quantity is 5 or more, otherwise free.”Copilot understands the logic and applies the correct formulas.
Example 4: Extracting Data from TextIf we he a column containing full names and addresses, we can extract specific values.
Tell Copilot to “Extract the first name” and then Insert Column:
Copilot creates formula-based solutions, meaning new entries will automatically be processed.
2. Using Copilot to Understand DataCopilot isn’t just for calculations鈥攊t can also summarize, visualize, and analyze your data.
Example 1: Total Revenue by RegionLet鈥檚 say we he a table with Transaction IDs, Regions, and Sales Amounts.
Prompt:
“What is the total revenue for each region?”Copilot generates:
A preview and summary
Click Add to New Sheet to se it.
This is especially useful for users unfamiliar with PivotTables or charting features in Excel!
Example 2: Employee Count by DepartmentWith an employee dataset containing IDs, Names, and Departments, we can count employees per department.
Prompt:
“How many employees are in each department?”
Copilot creates a new sheet showing a distinct count for each department:
Example 3: Finding the Top-Selling Items
With a dataset of items, quantities, and prices, we can identify the top 3 best-selling products.
Prompt:
“What are the top three items in terms of sales quantity?”
Copilot generates:
Insert to new sheet:
We can continue dialoging with Copilot if we want to refine the analysis. For example, we can ask:
“Add a margin column.” “Now, show the top three items by total margin.”This allows for quick financial insights without manually setting up complex calculations!
3. Editing and Formatting with CopilotBesides calculations and analysis, Copilot can also help with formatting and sorting.
Example 1: Highlighting the Top 3 ScoresGiven a dataset of names and test scores, we can apply conditional formatting.
Prompt:
“Highlight the top three scores.”
Copilot applies Conditional Formatting, dynamically updating when scores change.
Note: we could customize the formatting if desired:
Open Manage Rules to adjust colors, thresholds, etc. Example 2: Sorting and FilteringCopilot can help filter data based on conditions.
“Filter the table to show only scores greater than 50.”
Results:
While experienced users may prefer manual sorting and filtering, Copilot provides a great shortcut for those unfamiliar with Excel’s filter tools.
Example 3: Explaining FormulasIf you come across a complex formula you don鈥檛 understand, Copilot can break it down:
Copy the formula (Ctrl + C) Prompt: “Explain this formula” and then paste in the formula Copilot provides a step-by-step breakdown of each function and argument.
This is invaluable when working with formulas created by someone else!
Final ThoughtsMicrosoft Copilot is a game-changer for Excel users of all skill levels. Whether you need help writing formulas, analyzing data, or applying formatting, Copilot can handle it with simple, natural-language commands.
The best part? It ses time and reduces manual effort, making Excel more accessible than ever.
He you tried Copilot in Excel yet? Let me know in the comments how it鈥檚 working for you!
Want to see more Copilot tips? Let me know what you’d like to learn next!
Sample File CopilotCompilation_downloadDownload FAQ 1. Is Copilot ailable in all versions of Excel?No, Copilot is a licensed feature and is not ailable in all Excel versions. Check if your version supports it under the Home tab.
2. Do I need AutoSe enabled to use Copilot?Yes, AutoSe must be turned on for Copilot to function properly.
3. Can I undo actions performed by Copilot?Yes! If you don鈥檛 like the result, you can undo any Copilot-generated action using the undo button.
4. Does Copilot replace PivotTables and formulas?No, it simply makes them easier to create. You can always modify the output as needed.
5. Can Copilot create charts and graphs?Yes! When analyzing data, Copilot often generates PivotTables and charts automatically.
6. What if Copilot gives me an incorrect formula?You can either modify the formula manually or ask Copilot to refine it with a follow-up prompt.
7. Does Copilot work offline?No, Copilot requires an internet connection since it uses cloud-based AI.
8. Can Copilot help with VBA macros?Currently, Copilot focuses on formulas and standard Excel functions, but it may assist with basic macro-related tasks in future updates.