Skip to content
Trending
Simplify Windows Devices to Run Only the Required Applications using Intune Enhance macOS Compliance with Custom Security and Compliance Checks to Improve Device Security using Microsoft Intune Manage Samsung Galaxy Firmware Versions to Improve Security and Compliance using Microsoft Intune MS Intune Adds Windows Registry Data Collection to Device Inventory for Single Values All Key Values and Subkeys Updated Windows Device Sync Experience Enhances Per-Device Troubleshooting Configure Windows Sandbox vGPU Policy using Intune for Better Graphics Performance or Reduced Attack Surface Simplify Windows Devices to Run Only the Required Applications using Intune Enhance macOS Compliance with Custom Security and Compliance Checks to Improve Device Security using Microsoft Intune Manage Samsung Galaxy Firmware Versions to Improve Security and Compliance using Microsoft Intune MS Intune Adds Windows Registry Data Collection to Device Inventory for Single Values All Key Values and Subkeys Updated Windows Device Sync Experience Enhances Per-Device Troubleshooting Configure Windows Sandbox vGPU Policy using Intune for Better Graphics Performance or Reduced Attack Surface
Microsoft 365 Copilot in Excel with Python | Unformatted Data XLOOKUP SUMIF and Restricted Formatting

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

Written By Anoop C Nair
Last Updated September 20, 2024
Posted In AI
SHARE

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.

Written by

Anoop C Nair is Workplace Technology solution architect with 25+ years of experience in global enterprise organizations such as JP Morgan, Capgemini, etc. Microsoft Certified Trainer. Microsoft MVP from 2015 onwards for consecutive 11+ years! He also conducts Intune and modern workplace tech training for enterprise organizations. He is Blogger, Speaker, and Founder of HTMD Community and HTMD Conference. His main focus is on Device Management technologies like Intune, Windows, Cloud PC. He writes about technologies like Intune, SCCM, Windows, Cloud PC, Windows, Entra, Microsoft Security.

Discussion

Join the discussion

Your email address will not be published. Required fields are marked *

Related guides

AI

How to Configure Google Gemini Integration Settings using Microsoft Intune

Key Takeaways How to Configure Google Gemini Integration Settings using Microsoft Intune! Google Chrome’s Generative AI settings in Intune allow administrators to centrally manage how Chrome’s AI-powered features function across enterprise devices. These settings are especially important in today’s AI-driven era. Once devices are properly enrolled in Microsoft Intune, administrators can seamlessly enforce these policies, […]

SN Sujin Nelladath 6 min read
AI

16 New Windows Vulnerabilities Discovered by MDASH AI-Powered Agentic Security System

Key Takeaways In this post we are discussing 16 New Windows Vulnerabilities Discovered by MDASH AI-Powered Agentic Security System. Recently, Microsoft announced a new AI-powered security system called MDASH (Microsoft Security multi-model agentic scanning harness) that can automatically find serious software vulnerabilities in Windows. Microsoft explains that the platform helped researchers discover 16 new security flaws, including several critical […]

AC Anoop C Nair 4 min read
AI

3 Core Areas to Improve Security Reduce Risk and Build Resilience Against AI-Driven Threats

Key Takeaways In this post, we are discussing3 Core Areas to Improve Security Reduce Risk and Build Resilience Against AI-Driven Threats. Microsoft has introduced a set of new security capabilities focused on AI threats more effectively. This includes updated guidance to help organisations assess their exposure, prioritise critical risks, and take faster action to fix […]

AC Anoop C Nair 4 min read
AI

Intune Policy Now Allows Automatic Removal of Unused Copilot App

Key Takeaways In this Post, we are discussing Intune Policy Now Allows Automatic Removal of Unused Copilot App. Microsoft has introduced a new policy setting that allows IT administrators to uninstall the Microsoft Copilot app from managed devices under specific conditions. The feature is designed to help organizations better control app deployments and reduce unused […]

AC Anoop C Nair 7 min read