How to Simplify Data Management with Copilot in Microsoft Excel
Effectively managing and visualizing your data is evolving with Copilot in Microsoft Excel.
Copilot's AI-driven features help you easily create more intuitive spreadsheets to streamline workflows, enhance predictive analytics, and improve data modeling, empowering you to make smarter decisions.
Before diving into Copilot’s powerful features in this Microsoft 365 tool, ensure it's properly integrated into your Excel environment. Setting it up correctly will unlock its full potential and give you a more seamless experience.
Getting Started with Copilot in Excel
To begin using Copilot in Excel, verify that your account and settings are properly configured. Follow these key steps to get started:
- Ensure Proper Login: Sign in to your Microsoft account with the correct permissions to access Copilot. Check with your IT admin to confirm access.
- Enable AutoSave: Copilot requires AutoSave to be turned on, which means your Excel file must be saved in OneDrive or SharePoint. If the Copilot button is grayed out, check that your file is stored in a supported location.
- Check Data Permissions: If your data has restrictions or isn't fully accessible, some Copilot features may not work as expected. Ensure your file permissions allow for AI-driven enhancements.
Once you complete these steps, you should see the Copilot button activate. This means it's ready to assist you with streamlining data, performing predictive analytics, and enhancing your spreadsheets.
With Copilot set up, it’s time to explore how it enhances your Excel experience. One of its most powerful features is the sidecar interface, which intuitively interacts with your data using AI-driven automation. Let's explore some key features:
Navigating the Copilot Sidecar and Prompt Guide
When you open Copilot in Excel, the sidecar panel appears, offering a range of AI-powered tools to streamline your workflow. Copilot provides quick access to key actions that make data analysis more efficient, such as formatting column updates, and outliers.
A useful feature is the prompt guide, which helps you discover and refine the types of actions Copilot can perform. By clicking "View More Prompts", you can explore a categorized list of available prompts, making it easier to edit, analyze, or generate insights from your data. These prompts evolve, so regularly checking for updates ensures you’re making the most of Copilot’s latest capabilities.
Creating Columns with Copilot in Excel
Let’s start with a simple yet practical example: creating a new column using formulas. While experienced Excel users might do this manually, Copilot makes it easier for those unfamiliar with formulas—helping bridge the gap for all skill levels.
By simply describing what you need, Copilot interprets your request, identifies the relevant data, and applies the appropriate formula. This speeds up the process, reducing errors and making data manipulation more efficient.
In the example below, we wanted to create a Cost column by taking the Amount Including Tax, and reducing it by 20%. Instead of manually entering a formula, we can ask Copilot:
"Create a new column called 'Cost' that takes the Amount Including Tax and reduces it by 20%."
Copilot processes this request recognizes the relevant data, and generates the correct formula: multiplying the Amount Including Tax by 0.8 to reflect a 20% reduction. It then applies the formula and inserts the new column instantly.
The real power of Copilot comes from its ability to build on previous actions. For example, once you’ve added an initial column, you can continue refining your data by generating additional fields without manually adjusting formulas. If you've already created a cost column, you can ask Copilot to create a margin column that subtracts cost from the amount including tax. Copilot recognizes the existing data, applies the calculation, and inserts the new column, enriching your dataset with minimal effort.
Copilot will also act on prompts you give through voice commands. While some are more comfortable typing, this feature makes interactions even faster whether you are working on a desktop, or mobile device, or within Copilot's business chat tools.
This seamless integration shows how Copilot isn’t just about automation. These capabilities help streamline workflows and make data analysis more accessible to all users—regardless of their Excel expertise.
Next, let’s explore recommended actions within Copilot and use it to create conditional formatting.
Using Recommended Actions and Conditional Formatting
Copilot doesn’t just wait for instructions—it actively suggests ways to enhance and analyze your data. As you interact with it, you’ll see recommended actions based on the structure and content of your spreadsheet. These suggestions can help you quickly format, analyze, and refine your data without manually exploring Excel’s features.
One powerful recommendation is conditional formatting, which allows you to highlight specific data points based on predefined criteria. Instead of setting up conditional rules manually, you can let Copilot suggest options, such as identifying outliers, highlighting top-performing values, or flagging critical thresholds.
A good example of this is when you are working with a margin column. Copilot will suggest highlighting values below a certain amount, marking the top-performing entries, or applying a color scale to visualize differences between values. You can accept these recommendations as they are or modify them with custom colors or threshold values.
Beyond formatting, Copilot can also sort and organize data in meaningful ways. You can instruct it to arrange values from highest to lowest or group similar data points together, making trends easier to spot. These actions, when combined, provide a more dynamic and insightful way to engage with your data, allowing you to focus on decision-making rather than manual adjustments.
Next, let’s explore another simple, but useful way to use Copilot in Excel.
Filtering Data with Copilot in Microsoft Excel
Filtering is a simple yet powerful way to focus on the most relevant data in your spreadsheet. Instead of manually setting up filters, Copilot can apply them instantly based on your instructions. This lets you quickly refine large datasets and extract meaningful insights without navigating multiple menus.
For example, you might want to filter out orders with a margin below $1,000 to focus only on higher-value transactions. Asking Copilot to apply this filter will cause it to analyze your data, identify the relevant column, and execute the action. Once applied, only the rows meeting the criteria remain visible, streamlining your view for better decision-making.
This capability extends beyond basic tables—Copilot can now work with freeform data ranges, making it even more versatile. Whether you’re analyzing financial performance, sales trends, or operational metrics, filtering with Copilot makes the process faster and more intuitive.
Next, we’ll explore more advanced ways to leverage Copilot for deeper data insights, beyond calculations.
Asking Questions About Your Data
Copilot in Excel isn’t just for calculations—it can also analyze data and provide quick insights. For example, if you want to know how many unique customers are in a sales order file, you can ask Copilot. It scans the data, identifies unique values, and returns the answer instantly. Copilot even generates the underlying formula, allowing you to add the result to a new sheet for further analysis.
Creating and Modifying Charts
Visualizing data is another powerful way Copilot enhances Excel. You can request a chart to display specific values, such as margin by customer, and Copilot will generate it instantly. Need a different format? Ask for a change—like switching to a pie chart—and Copilot will update it accordingly. Once created, these charts can be moved to a new sheet for organization and further refinement.
Identifying Data Outliers
Copilot can also help surface key insights by identifying outliers in your data. Analyzing trends highlights values that stand out—such as an unusually high transaction on a specific date. This can be useful for spotting anomalies in revenue, costs, or other key metrics. These insights can then be added to a grid for deeper analysis, helping you make informed business decisions quickly.
Final Thoughts: How Copilot in Microsoft Word Is Transforming Data Management
Microsoft Copilot in Excel makes complex tasks faster, easier, and more intuitive. From generating formulas to visualizing trends and identifying outliers, Copilot streamlines workflows and enhances decision-making. As its capabilities continue to evolve, with wave one coming out this year, leveraging AI-driven tools like Copilot will become essential for maximizing efficiency and gaining deeper insights from your data.
Get In Touch With Stoneridge Today to Start Using Copilot in Microsoft Excel
If you have questions or want to explore how Microsoft Copilot can help build faster, more efficient systems within your organization, Stoneridge Software is here to help. Our team can help you get the most out of Microsoft Copilot.
Reach out to Stoneridge to learn more!
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.