Data Analysis in MS Excel with Copilot — Dawn of a New Era
We now live in a time which is experiencing an explosion — I am not referring to Big Bang or similar explosion. We are surrounded by Data Explosion. There is massive amounts of Data everywhere. This could be attributed to rise of smartphones, apps, sensors, smart vehicles and smart appliances. This explosion brings a challenge with it. We have huge amounts of raw data available to us however without meaningful insights from this raw data, this Data is of very little to no use. Modern businesses have realised the way to deal with this level of data and they are using contemporary methods of Data Analysis which assist them driving their business decisions.
One of the basic yet powerful (and still widely) used tool for Data Analysis is MS Excel. In MS Excel, you can perform data analysis using various features, functions, and tools. For years, we have been inserting formulas and Pivot tables with or without graphs manually to understand insights in the data. That might be about to change.
Enters Copilot — Microsoft’s own version of Artificial Intelligence (AI) Assistant which is similar to popular ChatGPT. As Satya Nadella, CEO of Microsoft puts — “Today marks the next major step in the evolution of how we interact with computing, which will fundamentally change the way we work and unlock a new wave of productivity growth. With our new copilot for work, we’re giving people more agency and making technology more accessible through the most universal interface — natural language.”
What is Copilot in Excel
Copilot in Excel is basically an AI Assistant embedded within MS Excel application and it has been designed to analyse trends and create professional-looking data visualisations in seconds. Like any other AI Assistant you simply type your request (or click on suggested prompts) and within seconds Copilot will do its magic!
What can you do with Copilot in Excel?
While there are numerous ways Copilot can be used in Excel, here are some basic tasks that it can perform:
Data analysis: Copilot reads your data in the worksheet and put focus on important information. Copilot can further be questioned to dig deeper into those focus areas.
Data Modeling: If you are interested in generating a model for your data, you can explore different scenarios by asking “what-if” questions and providing prompts such as “model how a change to Advertising would affect Sales”.
Visualisations: Within seconds, Copilot can create charts and graphs to visualise your data in a clear and understandable way.
Insights: Divulge interesting insights that exists within your data. For e.g. it can create trends, highlight outliers, or unravel correlations between different columns.
Suggestions: Based on your context, you can get intelligent recommendations for formulas, functions, and actions.
Writing formulas: Copilot assists in writing formulas for your calculations, making complex formula creation easier and more accessible.
Organising and Data Cleansing: Gone are the days when we would spend hours behind organising and cleaning the messy data. It can remove extra spaces, merge values from different columns, focus on duplicates by highlighting them, sort, filter, and more.
Automating tasks: Automating common tasks (e.g. creating shortcuts) using VBA Code is a charm through Copilot. You can increase productivity by delegating such tasks to Copilot and apply your precious time to other core activities.
Look at some simple prompts provided by Copilot:
To Create: “Generate a formula column suggestion.”
To Edit: Bold the top 5 values in the Sales column.”
To Understand: “Show the growth of advertising sales from last year and compare with current year.”
How to access Copilot in Excel
To get access to Copilot features in Excel, a subscription of Microsoft 365 and Copilot subscriptions are required.
For more information, please visit Microsoft UK URL for Copilot for Microsoft 365 here.
The Future
With more and more businesses relying on data driven decision making, Copilot in Excel comes very handy for them to gain insights without spending a lot of time and energies behind it. While this is just a starting, with more and more advancements being made in the field of AI, its going to grow better and more accurate.
About the Author:
Ashish Bansal is a Senior Data Consultant at Version 1.