If you’ve ever used Excel in a professional setting, you’ve almost certainly experienced the crunch of trying to obtain the right numbers or charts quickly.
Does this sound familiar? You’re in the middle of a meeting and are asked a specific question. Or, you’re about to head home, and your manager requests one last thing. It’s the end of the day, you’re tired, and it’s hard to recall how to use Excel to answer that question. The last thing you want is to keep adding, deleting and modifying worksheets just for quick answers.
Let’s explore how Copilot in Excel can streamline this process. Copilot enables us to generate basic charts and tables by simply querying the data in natural language. Like with most forms of communication, the more specific we are, the more likely we are to get exactly what we need from Copilot.
This tool is invaluable for performing basic data summarization and analysis. For those interested in following along, please refer to the exercise file provided below.
Imagine you’re a sales analyst in a company, participating in a planning meeting. You’re bombarded with questions about trends and figures, and keeping up manually with Excel proves to be quite the challenge. This is when you decide to leverage Copilot to streamline the data summarization process.
To use Copilot, first make sure that your data has been properly prepared and is compatible with the tool.
Upon accessing Copilot’s prompt guide, choose the last option: “Analyze this data.” This selection signals to Copilot that your goal is to extract meaningful insights from your data, steering it toward the specific analysis you wish to undertake.
Copilot is now primed to explore the table for insights. For instance, when I request a visualization of total sales by the month of the order date, I receive a beautifully formatted line chart, specifically a PivotChart, as this data required aggregation before plotting. I can then insert this chart into my workbook or prompt Copilot for additional analyses.
Regarding data summaries and visualizations, Copilot typically opts for basic formats like line charts, bar charts, and scatter charts, yet it consistently makes sound analytical decisions.
Let’s assume that we’re not particularly keen on visualizing this data and would prefer to see the results in a table format. We can communicate this preference to Copilot. We have the option to integrate it into the workbook or proceed with our analysis without it.
One aspect of Copilot I greatly appreciate is its ability to swiftly query data and provide answers without cluttering up the workspace with numerous intermediary worksheets.
Unfortunately, I’m not really fond of the layout or structure of this PivotTable, but Copilot doesn’t allow for customization. I’m optimistic that, over time, there will be more opportunities to modify the formatting and fine-tune the results within Copilot. For the time being, however, we’ll proceed as is.
Suppose I’m keen on analyzing sales for a specific month. I aim to summarize the total sales by product category for June 2016, based on the order date. Although the Copilot’s output appears to be a PivotTable, inserting the data into my worksheet reveals that the information is presented as a static table. This implies that, should the source data change, I would need to consult Copilot again to obtain the refreshed results.
Up to this point, we’ve examined total sales, focusing on both general trends and specific categories. Now, let’s integrate these perspectives by exploring sales data by month and by category. You’re likely familiar with the process by now: if you prefer the data to be presented as a chart or a table, please mention that in your prompt.
I hope you see that Copilot can significantly save you time when summarizing your data. Although it’s not the most detailed tool for formatting results or delving into greater depth, it provides an excellent starting point and can be effectively used in an ad hoc setting when you need to quickly extract data. However, it’s crucial to be precise when asking Copilot for what you need; otherwise, you may receive misleading or incorrect responses to your prompts.
Do you have any questions about using conditional formatting with Copilot in Excel? Or is there something else you’d like to explore with Copilot in Excel? Please let me know in the comments.
Leave a Reply