Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting

Today, I will explore Microsoft 365 Copilot in Excel with Python. This helps Unformatted Data to XLOOKUP, SUMIF, and Restricted Formatting. Microsoft is introducing the next episode of Microsoft 365 Copilot, which combines Web, Work, and Pages to launch a new design system for knowledge work.

Microsoft has declared the latest update to Copilot, revealing that it is now generally available and has been developed to aid data-driven experts worldwide. Additionally, it has introduced the public preview of Copilot in Excel with Python, accepting advanced and healthy assessments.

You can now access unformatted data in Excel via new elements like XLOOKUP, SUMIF, and restricted formatting. Copilot now supports text and numerical data for visualisations like charts and PivotTables.

Microsoft is taking the next step to present Copilot in Excel with Python. Currently, in public preview, this feature allows users to execute enhanced data analyses using natural language without coding, like having a skilled data analyst on the team.

Patch My PC

Copilot is the Best Excel Expert

For those of you who use Excel frequently, you should spend many hours every day formatting your data sheets to view and use your data more essentially. As your Excel expert, let Copilot assist you with these everyday tasks. Copilot can now analyze structured data, not just tables, and perform functions like adding filters or separating text.

Furthermore, Copilot can help you highlight important information through formula-based accepted formatting. For instance, Copilot makes straightforward highlighting of crucial details, such as identifying expenditure that exceeds revenue in a sales sheet.

Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting- Fig.1 (Image credited to Microsoft)
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting – Fig.1 (Image credited to Microsoft)

Excel offers various formulas for data organisation, visualisation, and analysis. While we often use the same formulas daily, we aim to broaden our use of formulas and have convenient access to the ones we use frequently. Now, we can request assistance from Copilot directly within the spreadsheet. Microsoft recently added support for complex formulas like XLOOKUP and SUMIF.

Microsoft Excel Formulas
XLOOKUP
SUMIF
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting – Table 1
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting - Fig.2 (Image credited to Microsoft)
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting – Fig.2 (Image credited to Microsoft)

Copilot not only implements formulas and data formatting but also gives step-by-step explanations. When you ask a question, it replies with suggestions and explanations for each step. You can screen the suggested action and select to apply it. When it can’t directly change your data, it guides you through the steps to make the change yourself, saving you from outside research. By exposing these elements, Copilot helps you take full advantage of Excel’s capabilities, making tasks more effective.

Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting - Fig.3 (Image credited to Microsoft)
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting – Fig.3 (Image credited to Microsoft)

Acquire Valuable Knowledge using a Common Language

Data-driven management is necessary for business success. Copilot can help you get the results by requesting them using a common language.

Copilot’s study now suggests the best visual formats, such as bar charts, line graphs, or Pivot Tables. It also formulates the proper fields, layouts, and filters for you, creating specific charts or Pivot Tables so you can access Excel’s powerful capabilities without being a professional.

Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting

With Copilot in Excel integrated with Python, you can now conduct advanced analyses that were before impossible. Describe the query in simple language, and Copilot will generate, explain, and insert the necessary Python code directly into your Excel spreadsheet.

For example, if we want to know the forecast revenue, the user can ask the Copilot Excel with Python to predict it. Copilot then automatically generates and inserts Python code to create a revenue forecast and a corresponding chart for visualization.

Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting - Fig.4 (Image credited to Microsoft)
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting- Fig.4 (Image credited to Microsoft)

Even if you’re not an expert in Python, you can use Python to make visualisations, analyse data, and implement machine learning and predictive analytics. Excel’s Copilot with Python can also help you create exciting, previously tricky visuals. These visuals, such as pairplots, multiples, violin plots, and heatmaps, can help you understand and explain your analysis.

Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting - Fig.5 (Image credited to Microsoft)
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting – Fig.5 (Image credited to Microsoft)

Copilot in Excel with Python analyzes and visualises data and explains the code it’s using so that you can understand how it works. It establishes a separate analysis sheet with a sandbox for collaboration while protecting the original data. The analysis is refreshable, so you can also get an updated analysis when the source data is updated.

The Copilot feature integrates Python with Excel and is rolled out to Windows for Insiders. To avail of this feature, a Microsoft 365 Copilot license is needed. Once enabled, users can click on the Advanced Analysis prompt suggestion or create a customised prompt to direct Copilot to conduct a study using Python. Python functionality is now generally available in Excel for Windows users.

Convert Text to Actionable Insights 

Copilot can now analyze text, converting raw textual data into actionable insights. This improvement confirms that Copilot can handle complex numerical or textual datasets with equal accuracy and efficiency, ultimately improving business outcomes.

For example, a marketing manager can get a summary of product reviews to understand opportunities and challenges better. Analysing text with Copilot saves the marketing manager valuable time and energy. A summary can help him make informed decisions or act much more efficiently. 

Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting- Fig.6 (Image credited to Microsoft)
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting – Fig.6 (Image credited to Microsoft)

Revolutionise Your Approach to Working with Data

Excel has always been an essential tool for data analysis. The introduction of Copilot has visibly enhanced the productivity and accuracy of data transformation. Copilot in Excel with Python makes analysts closer to using conversational AI to work with complex data.

Join the LinkedIn Page and Telegram group to get the latest step-by-step guides and news updates. Join our Meetup Page to participate in User group meetings. Also, Join the WhatsApp Community to get the latest news on Microsoft Technologies. We are there on Reddit as well.

Author

Anoop C Nair has been Microsoft MVP for 10 consecutive years from 2015 onwards. He is a Workplace Solution Architect with more than 22+ years of experience in Workplace technologies. He is a Blogger, Speaker, and Local User Group Community leader. His primary focus is on Device Management technologies like SCCM and Intune. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security, Career, etc.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.