Mastering Microsoft 365 Excel: A Deep Dive into Its Most Powerful New Features
While operating systems like Windows often capture the spotlight, the true engine of productivity for many lies within the applications running on them. For anyone working with data, that frequently means Microsoft Excel. As a cornerstone of the Microsoft 365 suite, Excel is not a static program; it's a dynamic tool that receives continuous updates, bringing a wealth of new features and refinements to its subscribers.
This constant evolution is a key differentiator between the subscription-based Microsoft 365 and the one-time purchase, or “perpetual,” versions like Office 2021 or Office 2024. Perpetual licenses offer a fixed set of features at the time of purchase, never receiving major new capabilities. Microsoft 365, however, ensures that users always have access to the latest innovations as they roll out. Understanding these new features is crucial for leveraging Excel to its fullest potential in today's fast-paced work environment.
This guide serves as a comprehensive overview of some of the most impactful features introduced or significantly changed in Excel for Microsoft 365 on Windows over the past few years. We'll explore how these tools can transform your workflow, from simplifying complex calculations to enabling seamless collaboration and powerful data visualization.
Navigating the Interface: The Enduring Ribbon and Enhanced Search
The familiar Ribbon interface remains the primary way to access Excel's vast array of commands. Organized into tabs like Home, Insert, Data, and View, the Ribbon groups related functions logically. While its appearance has undergone cosmetic changes over time, its core functionality persists: click a tab to reveal its associated tools.
Through the years, Excel’s Ribbon has gotten a variety of cosmetic changes, but it still works largely the way it always has.
Preston Gralla / Foundry
For those who prefer a less cluttered view, the Ribbon can be minimized by pressing Ctrl-F1 or clicking the active tab name. The tabs remain visible, allowing you to temporarily reveal commands by clicking a tab again or pressing Ctrl-F1.
More granular control over the Ribbon's display is available via the “Ribbon display options” icon (a down arrow) at the far right, just below the Share button. Options include:
- Full-screen mode: Maximizes the Excel window and hides the Ribbon entirely. Exit by clicking the three-dot icon.
- Show tabs only: Displays only the Ribbon tabs, hiding the commands. This is equivalent to Ctrl-F1.
- Always show Ribbon: The default view, displaying both tabs and commands.
- Show/Hide Quick Access toolbar: Toggles the visibility of the Quick Access toolbar, a customizable area for frequently used commands. You can add commands by clicking the down arrow on the toolbar and selecting 'More Commands'. The toolbar can be positioned above or below the Ribbon.
While a simplified Ribbon exists in the Excel web app, it has not yet made its way to the desktop client.
A useful enhancement in the “backstage area” (accessed via the File tab) is the clear display of connected cloud services like SharePoint and OneDrive, showing the associated email address for each. This is particularly helpful for users managing multiple accounts on the same service, providing an at-a-glance view of which account is linked to which location when opening or saving files.
Finding Your Way: The Power of Search
Excel's extensive feature set can sometimes feel overwhelming. The Search box, located above the Ribbon in the title bar (or accessed via Alt-Q), acts as a powerful guide. Simply type the task you want to perform, and Excel suggests relevant commands and actions.
The search box makes it easy to perform just about any task in Excel.
Preston Gralla / Foundry
For instance, typing “summarize with pivot table” directly offers the option to create a PivotTable, bypassing the need to navigate the Insert tab. The search also provides links to relevant Help topics for more information. A particularly useful aspect is its memory; it remembers your previous searches, making frequently used commands instantly accessible. For users of enterprise and education editions, the Search box integrates with Microsoft Search, allowing discovery of people, SharePoint resources, and other organizational data directly within Excel. For more details on its capabilities, refer to the Microsoft Search support page.
Visualizing Data: Exploring Advanced Chart Types
Charts are indispensable for transforming raw data into understandable visuals, aiding both presentation and analysis. Microsoft 365 Excel has introduced several sophisticated chart types to provide deeper insights, available exclusively for files saved in the modern .xlsx format.
Excel includes several advanced chart types, including waterfall.
Preston Gralla / Foundry
To access most of these, select your data, go to Insert > Recommended Charts, and click the All Charts tab. Some, like the Pareto chart, are found under Insert > Insert Statistic Chart.
Key advanced chart types include:
- Treemap: Visualizes hierarchical data as nested rectangles. The size of each rectangle represents its value, making it easy to compare categories and subcategories within a single view.
- Sunburst: Also for hierarchical data, but uses a multi-level pie chart structure. Concentric rings represent hierarchy levels, showing relationships between categories and subcategories.
- Waterfall: Ideal for financial data, showing a running total as positive and negative values are added or subtracted, culminating in a net total.
- Histogram: Displays the frequency distribution of data, grouping values into bins to show how often values fall within specific ranges.
- Pareto: A sorted histogram combined with a line graph. Bars show values in descending order, while a line shows the cumulative percentage. Useful for identifying the most significant factors in a dataset, following the Pareto principle (80/20 rule).
- Box & Whisker: Provides a detailed view of data distribution, showing quartiles, median, mean, range, and outliers. Excellent for comparing distributions across different categories, such as price ranges for various book genres. For a deeper understanding of quartiles, you can consult the Wikipedia page on Quartiles.
- Funnel: Illustrates values at different stages of a process, typically showing decreasing values. Commonly used for sales pipelines, showing prospects moving from initial contact to closed deals.
- Map: Visualizes data across geographical regions like countries, states, or cities. Excel automatically recognizes locations and creates a shaded map based on your data values.
A treemap chart lets you easily compare top-level categories and subcategories in a single view.
Preston Gralla / Foundry
A sunburst chart shows hierarchical data such as book categories and subcategories as a multi-level pie chart.
Preston Gralla / Foundry
A waterfall chart shows a running total of positive and negative contributions, such as revenue and expenses, toward a final net value.
Preston Gralla / Foundry
Histograms are good for showing frequencies, such as number of books sold at various price points.
Preston Gralla / Foundry
In a Pareto chart, or sorted histogram, a rising line represents the cumulative total percentage of the items being measured. In this example, it’s easy to see that more than 80% of a bookstore’s returns are attributable to three problems.
Preston Gralla / Foundry
Box & Whisker charts can show details about data ranges such as the first to third quartile in the “boxes,” median and mean inside the boxes, upper and lower range with the “whiskers,” and outliers with dots.
Preston Gralla / Foundry
Funnel charts let you display values at multiple stages in a process.
Preston Gralla / Foundry
You can compare data across different locations with a map chart.
Preston Gralla / Foundry
These charts provide powerful new ways to interpret and present complex datasets, moving beyond traditional bar and pie charts.
Working Together: Real-Time Collaboration (Co-authoring)
For teams, one of the most significant benefits of Microsoft 365 Excel is real-time collaboration, or “co-authoring.” This feature allows multiple users to work on the same spreadsheet simultaneously from different locations, seeing each other's changes as they happen. To utilize co-authoring, the spreadsheet must be stored on a cloud service like OneDrive, OneDrive for Business, or SharePoint Online, and all collaborators must be logged into their Microsoft 365 accounts.
Crucially, co-authoring requires AutoSave to be enabled. You can toggle AutoSave using the slider at the top left of the Excel window.
Sharing a spreadsheet for collaboration is straightforward. Click the Share button in the upper-right corner. The “Send link” pane appears, allowing you to email a link to collaborators. You can enter individual email addresses or choose to share with anyone who has the link. Granular permissions can be set to allow editing or view-only access. For business, enterprise, or education users, additional options might be available based on organizational settings, including sharing within the organization or setting expiration dates and passwords via the “Link settings” gear icon.
Use the “Send link” pane to share a document and the “Link settings” pane to fine-tune its access permissions.
Preston Gralla / Foundry
When collaborators open the shared file (initially in the web version, with an option to open in the desktop app), their presence is indicated by colored cursors. Each collaborator has a unique color, and hovering over a colored cell reveals their name. Changes made by anyone are visible to all in real time, fostering a truly collaborative environment.
You can easily see where collaborators are working in a shared worksheet.
Preston Gralla / Foundry
Comments are also integrated into the collaboration workflow. Right-clicking a cell and selecting “New Comment” allows users to add notes without altering cell content. A small colored notch indicates a commented cell. Hovering over it reveals the comment and the author, along with a reply option. The Comments pane, accessible via the button in the upper right, provides a centralized view of all comments, allowing easy navigation and conversation threads.
You can make see comments that other people make, and make comments yourself.
Preston Gralla / Foundry
Bringing in External Data: Linked Data Types
Excel for Microsoft 365 introduces “linked data types,” a feature that connects cells to online data sources, automatically updating the information. Powered by Bing, these data types go beyond simple text or numbers, transforming cell content into rich, interactive data cards. Currently, there are around 100 linked data types available, covering diverse topics from stocks and geography to chemistry, anatomy, and food.
To use this feature, type the items you want to track into cells in a single column (e.g., stock ticker symbols or country names). Select these cells, then go to the Data tab on the Ribbon and select the appropriate data type (e.g., Stocks or Geography) from the Data Types section. Excel attempts to convert the text into the corresponding linked data type, indicated by a small icon next to the cell.
Linked data types let you insert information, such as a company’s high and low stock prices, that is continually updated.
Preston Gralla / Foundry
Clicking the icon reveals a data card with extensive information about the item. For a stock, this includes price, daily highs/lows, industry, and employee count. For a location, it might show population, capital, or GDP. You can then easily add this information to your spreadsheet by selecting the cells and clicking the “Insert Data” button, choosing the specific fields you want to add as new columns. These linked data points stay updated automatically, providing dynamic, real-time information within your spreadsheet.
Managing Views: Custom Sheet Views
When collaborating on a large spreadsheet, different team members may need to filter or sort the data in different ways without affecting how others see it. Sheet Views address this by allowing users to create personalized, filtered, or sorted views of a single worksheet. These views are saved with the workbook and are accessible to anyone with whom the spreadsheet is shared (provided it's stored on OneDrive).
For optimal use, it's recommended to format your data as an Excel Table (Insert > Table). To create a new sheet view, go to the View tab and click New in the Sheet View area. The row numbers and column letters turn black to indicate you are in a temporary view. Apply your desired filters or sorting. Click Keep to save the view, giving it a descriptive name instead of the default “Temporary View.”
Here’s a sheet view with data sorted from highest to lowest costs.
Preston Gralla / Foundry
Switching between views is easy: click the down arrow next to the current view name (e.g., “Default”) in the Sheet View area on the View tab and select the desired view from the list. This feature significantly enhances collaborative data analysis by allowing personalized perspectives without disrupting others.
Automating Calculations: Dynamic Arrays and Charts
Dynamic arrays represent a fundamental shift in how Excel handles formulas. Instead of a formula returning a single value, dynamic array formulas can return multiple values that “spill” into adjacent cells. When the source data changes, the dynamic array automatically updates and resizes itself.
This feature is powered by new functions like FILTER, SORT, and UNIQUE. For example, the formula =FILTER(A2:B9, B2:B9 < 2000) would create a dynamic array showing only rows where the value in column B is less than 2000. As data is added or changed in the source range (A2:B9), the filtered list automatically updates.
The FILTER function created a data array showing only the items with costs below $2,000.
Preston Gralla / Foundry
Dynamic charts can be created from dynamic arrays. Since the array automatically updates with source data changes, the chart based on it also updates automatically, providing a constantly current visualization. You can learn more about the FILTER, SORT, and UNIQUE functions on Microsoft's support site.
Protecting Your Work: AutoSave and Version History
Losing work due to unexpected crashes or power outages is a common fear. Microsoft 365 Excel alleviates this with AutoSave, a feature that automatically saves your changes in real time. This works seamlessly for files stored on OneDrive, OneDrive for Business, or SharePoint Online, providing a robust safety net compared to the older, less reliable AutoRecover feature.
AutoSave is enabled by default for compatible files. You can toggle it off or on using the slider at the top left. If you prefer AutoSave to be off by default for all cloud files, you can change this in File > Options > Save.
It's important to adjust your workflow when using AutoSave. If you typically open an existing file, make changes, and use “Save As” to create a new version, AutoSave will save your changes to the original file before you can use “Save a Copy” (which replaces “Save As” when AutoSave is on). Microsoft recommends using “File > Save a Copy” immediately after opening the original file if your intention is to create a new version.
Even with AutoSave, mistakes happen. The Version History feature is a powerful tool for reviewing and restoring previous states of your spreadsheet. Click the file name in the title bar and select “Version History,” or go to File > Info > Version History. A pane on the right lists previous versions saved automatically by AutoSave, complete with timestamps.
Use Version History to see all previous versions of a spreadsheet, copy and paste from an older file to your existing one, or restore an entire old version.
Preston Gralla / Foundry
You can open any older version in a read-only window to review its contents and copy data to paste into your current version. If needed, you can restore an entire previous version, overwriting the current one. This provides a robust safety net for managing changes and recovering from errors.
Exploring AI Assistance: Microsoft 365 Copilot
Microsoft 365 Copilot, available to business users with an additional subscription fee (and some features bundled for Personal/Family subscribers), is Microsoft's generative AI add-in for its productivity suite. In Excel, Copilot aims to assist with tasks like suggesting and creating charts, generating formulas, and identifying data insights. You can interact with Copilot via a panel on the right side of the screen, accessed from the Home tab.
Microsoft 365 Copilot can help you in multiple ways in Excel, including creating formulas and charts, mining spreadsheets for insights, and more.
Preston Gralla / Foundry
However, based on testing, Copilot's utility in Excel can be inconsistent. It may offer generic suggestions that aren't always applicable or struggle with complex tasks, sometimes providing confusing instructions or failing to perform actions directly. For example, attempts to have it summarize data with PivotTables or charts encountered errors or required manual reformatting with functions like Transform(). While it might suggest data to highlight for conditional formatting, it may not explain the significance or perform the formatting itself. An attempt at advanced analysis using Python resulted in potentially incorrect data overwriting the original spreadsheet.
In my testing, I found Copilot not at all helpful, although spreadsheet jockeys may be able to make some sense of what it does.
Preston Gralla / Foundry
Furthermore, Microsoft's focus on Copilot has led to the deprecation of previously useful features like Smart Lookup, which allowed targeted web searches from within Excel. Now, web search functionality is primarily routed through Copilot, which lacks some of Smart Lookup's convenience, such as automatically searching highlighted text. This shift, coupled with Copilot's additional cost for business users, suggests that while AI assistance is a future direction, its current implementation in Excel may not yet meet the needs of all users.
For a broader understanding of Copilot's role in Microsoft 365, you can read more about Microsoft's generative AI tool explained and how Copilot AI is bundled with Microsoft 365 subscriptions.
Beyond the Basics: Other Notable Features
Microsoft 365 Excel includes several other features designed to enhance data handling, analysis, and visualization:
Instant Data Analysis with Quick Analysis
The Quick Analysis tool provides rapid insights into selected data. Highlight a range of cells, and a small icon appears in the lower-right corner. Clicking it reveals options for instant analysis, such as applying conditional formatting, calculating averages, or quickly creating charts based on the selection.
The Quick Analysis feature gives you a variety of tools for analyzing your data instantly.
Preston Gralla / Foundry
Translate Text Directly
For multilingual work, Excel allows you to translate text within cells. Select the cell, go to Review > Translate. The Translator pane opens, detecting the source language and allowing you to choose the target language for translation.
Finding Shared Worksheets
Keeping track of files shared with you is simplified. Navigate to File > Open > Shared with Me to see a list of all workbooks shared with your Microsoft or work/school account via OneDrive or SharePoint Online.
Predicting Trends with Forecast Sheet
Based on historical time-series data, the Forecast Sheet feature can generate future predictions. With your cursor in a data cell, go to the Data tab and select Forecast Sheet. You can customize options like the forecast end date and chart type (line or bar) before creating a new sheet with the historical data, forecast values, and a visual chart.
The Forecast Sheet feature can predict future results based on historical data.
Preston Gralla / Foundry
Data Management with Get & Transform
Formerly known as Power Query, Get & Transform is a powerful business intelligence tool integrated into Excel. Found on the Data tab, it allows you to connect to, combine, and shape data from a wide array of sources, including local files, databases, cloud services, and even public sources like Wikipedia. This feature is invaluable for preparing data for analysis and reporting. Microsoft provides documentation on Getting Started with Get & Transform in Excel.
Get & Transform helps you pull in and shape data from a wide variety of sources.
Preston Gralla / Foundry
Creating Interactive 3D Maps
3D Maps (formerly Power Map) allows you to visualize geospatial data on a 3D globe or map. This feature is built into Microsoft 365 Excel and is excellent for plotting geographic data to identify trends or patterns across locations. While preparing data for 3D Maps requires specific formatting, Microsoft offers guides on how to get and prepare data and how to get started with 3D Maps. Sample datasets are also available to explore the feature firsthand.
With 3D Maps you can plot geospatial data in an interactive 3D map.
Preston Gralla / Foundry
Automating Tasks with Office Scripts
For commercial or educational Microsoft 365 license holders using OneDrive for Business, the Automate tab provides access to Office Scripts. This feature allows users to record actions or write code in a Code Editor to automate repetitive tasks. Scripts can be run with a single button click and shared with colleagues, streamlining workflows. Microsoft's documentation on Office Scripts in Excel provides detailed guidance.
Inserting Data from Pictures
Excel can now convert data found within images into editable spreadsheet data. Using the “From Picture” dropdown in the Get & Transform Data group on the Data tab, you can select “Picture From File” or “Picture from Clipboard.” Excel uses optical character recognition (OCR) to extract the data, which you can then review and insert into your worksheet. More information is available on the Microsoft support page for inserting data from picture.
Boosting Efficiency: Keyboard Shortcuts
While new features add powerful capabilities, sometimes the simplest tools offer the biggest productivity boost. Mastering keyboard shortcuts for common actions can save significant time compared to navigating menus. Incorporating a handful of essential shortcuts into your workflow can dramatically increase efficiency. For a list of useful shortcuts, refer to guides like “Handy Excel keyboard shortcuts for Windows and Mac.”
Conclusion
Microsoft 365 Excel is a continuously evolving platform, regularly updated with features designed to make data analysis, visualization, and collaboration more powerful and intuitive. From dynamic arrays that automate calculations and charts to real-time co-authoring that transforms teamwork, these additions significantly enhance the capabilities available to subscribers. While some features like AI-driven assistance are still maturing, the overall trajectory points towards a more connected, intelligent, and efficient spreadsheet experience. By exploring and integrating these new tools into your daily tasks, you can unlock new levels of productivity and gain deeper insights from your data.
For those interested in further enhancing their Excel skills, Computerworld offers a range of tutorials covering essential topics such as getting started with tables, charts and sparklines, PivotTables and PivotCharts, using slicers, formulas and functions, conditional formatting, and automating work with macros.