Copilot in Excel Now Generally Available

Microsoft has recently unveiled the next wave of Copilot including the announcement that Copilot in Excel is now Generally Available.  In addition, the company has also broadcast the public Preview of Copilot in Excel with Python too.

Copilot in Excel is built into your workbook, enabling you to iterate with it quickly and easily.  Copilot in Excel with Python will further help you gain deeper insights without needing to be a Python expert.  That’s good news for me then.

Many of us spend hours every day just formatting your data sheets to more clearly view and use your data.  Some of us have evolved to use Power Query to assist extraction, said transformation and loading of the data into Excel.  Now, Copilot can help you with these frequent tasks too.

Copilot can now reason over structured data, not just tables, and it can complete tasks such as adding filters or splitting text.  Copilot can also help you highlight important information with formula based conditional formatting.  For example, Copilot simplifies the process of highlighting crucial information, such as spending that’s higher than revenue in a sales sheet. 

Copilot in Excel is asked to highlight marketing spend if it is higher than revenue.  Copilot provides a suggested conditional formatting formula.  The suggestion is applied and the highlights appear in the spreadsheet.

Excel has hundreds of different formulas to help you organise, visualise and obtain insights out of your data.  Recently support has been added for formulae such as XLOOKUP and SUMIF as well.  

Here, Copilot in Excel was asked to calculate the profit and profitability.  Copilot provided suggested formulae.  One of the suggestions is previewed, then inserted into the spreadsheet.

As well as constructing formulae and implementing formatting, Copilot also provides steps and explanations along the way.  When you ask Copilot a question, it will now respond with a suggestion and an explanation of all the steps to show its “thinking”.  You can preview the suggested action and then choose what to apply.  When Copilot can’t make a direct change to your data, it will provide you with steps you can take to make the change yourself.

In this example, Copilot in Excel provides steps to manually accomplish a task, creating a specific chart.

Gaining insights is one of the most difficult parts of working with data.  Copilot can help: simply request insights from Copilot using its natural language.

As part of Copilot’s analysis, Microsoft also announced that it now recommends the best visual formats, like Bar charts, Line graphs, or PivotTables and offers what it considers to be the right sets of fields layouts and filters for you, creating a specific chart or PivotTable so you can get all the benefits of Excel’s powerful capabilities without being an expert.

For example, a sales consultant can easily understand the relationship between ad spend and campaign reach, with Copilot generating a PivotTable to aid in planning the next quarter's budget:

Copilot in Excel is asked to group units sold by state.  Copilot provides a suggested PivotTable.  The PivotTable is inserted onto a new spreadsheet.

Copilot in Excel works with Python too.  Analysts often have specific needs to conduct analysis which takes time and expertise.  Now using Copilot in Excel with Python, you can conduct assisted analysis.  You can use natural language to describe the analysis you want to perform, and Copilot will automatically generate, explain, and insert Python code into your Excel spreadsheet.  

Here, Copilot in Excel with Python is prompted to forecast revenue.  Copilot creates and inserts Python code including a chart.

This unlocks powerful analytics via Python for visualisations, cleaning data, machine learning, predictive analytics, and more - without needing to be Python proficient yourself.  Unique visuals like heatmaps, pairplots, multiplots, and violin plots help you understand and communicate your analysis.  That was much more difficult in the past.

Copilot in Excel with Python is asked to visualise data.  Copilot creates and inserts Python code resulting in three [3] chart types.

Python in Excel leverages Anaconda Distribution for Python which includes the most popular Python libraries such as pandas, Matplotlib and scikit-learn.  Now, Copilot in Excel with Python can use these libraries too, unlocking their potential with everyday language.

Copilot in Excel with Python not only analyses and visualises data, it also shares its thinking, showing and explaining the code it’s using so that you understand how it’s working.  It creates a dedicated analysis sheet, with a sandbox for you to collaborate with Copilot, whilst leaving the original source data untouched.  The analysis is refreshable so when source data is updated, you can get an updated analysis as well.  You may even edit the code directly in the spreadsheet giving you full control to adjust as you wish. 

Copilot in Excel with Python is rolling out to Windows for Insiders and requires a Microsoft 365 Copilot licence.  Once enabled you can simply click the ‘Advanced analysis’ prompt suggestion or write a customised prompt asking Copilot to ‘analyze with Python’.

Furthermore, not all analysis is undertaken with numerical data: Copilot can now analyse text, transforming raw textual data into actionable insights.  Copilot can handle reasonably complex datasets, whether they are numerical or textual.

For instance, a marketing manager can obtain a summary of product reviews to better understand opportunities and challenges.  Being able to analyse text with Copilot saves the marketing manager valuable time and energy. 

Copilot in Excel with Python helps you go even further by analysing text-based data too.  This provides even deeper insights such as sentiment analysis and keyword extraction, powerful text analytics libraries such as NLTK, and unique visuals like a word cloud that help communicate text-based trends. 

Copilot is now available in Excel with a Microsoft 365 Copilot licence; join the Microsoft 365 Insider’s program for Copilot in Excel with Python too.

As always, we'll be detailing these updates in our October newsletter.  Please remember we have virtual / online training in Excel which you can find out more about here.  If you are not already a subscriber, why not sign up at the bottom of any SumProduct web page?