Excel in Microsoft 365 has undergone significant enhancements and new features to improve productivity and drive better insights from data. Some of the biggest new additions and improvements include:
Microsoft introduced XLOOKUP, a new lookup and reference function that makes it easier to look up values and return matches from a table or range. XLOOKUP allows lookups from left to right or top to bottom. It also supports approximate matching, which returns the closest match if an exact match is not found. This is a powerful function that simplifies tasks that previously required more complex INDEX/MATCH formulas.
Pivotal tableau capabilities were added to Excel to make it easier for users to analyze and visualize their data. Tableaus let users interactively sort, filter, and analyze data in a pivot table style user interface directly from the Excel sheets. Users can now gain valuable insights through visualized pivot views of their data without leaving Excel.
Excel added dynamic arrays that allow for new in-memory calculations across entire ranges and tables of data at once, without the limitations of copying down formulas. Functions like SEQUENCE, GROWTH, FIND, etc. now return full column or row arrays instead of single values. This enables auto-filling of patterns and series as well as more powerful what-if analysis through scenarios.
Conditional formatting rules were updated to support dynamic arrays. Users can now apply conditional formats to entire tables and ranges based on array formulas, instead of having to copy down formats for each cell. This streamlines tasks like highlighting outliers, thresholds, and trends across large datasets.
To simplify working with external data, Query options were added to directly import data from the web without needing to write Data queries or depend on Power Query. queries can import live web pages as well as static data from URLs. Users can also refresh imported data on a schedule if needed.
A Data Navigator view was introduced to conveniently browse and manage imported Excel data. Users can see a visual representation of their imported data along with related sheets, views, and queries in one centralized window. This interface makes managing multiple imports, refreshes, and queries much more accessible.
Excel automatically created charts from imported data to give instant visual summaries. Users can interactively modify these charts directly to gain insights without needing to build visualizations from scratch each time. With dynamic data linked to the original queries, charts always reflect the latest data.
Excel’s formatting capabilities were expanded with new features like Text Adjust and Optical Character Recognition. Text Adjust automatically sizes and positions text to fill available space, while OCR copies scanned images or PDF text into editable cells for further analysis and manipulation as standard Excel data types.
Excel templates gained support for multiple pages per template file for things like invoices and reports that need sequenced, structured layouts. Page setup options were enhanced to control formatting across pages using sections, watermarks, headers/footers. Along with conditional formatting, this improves templating of multi-section documents within Excel.
To support building robust models and distributed workbooks, Excel added offline capabilities that allow syncing of shared workbooks even when a user is working offline or on a plane with no connectivity. Updates are securely synced when the device is back online to share the latest changes.
Machine learning capabilities with automation were introduced through features like Custom Functions, which allow developers to code own Excel functions that tap into powerful ML algorithms for predictive insights. Integrated text and sentiment analysis functions provide AI-driven analysis of narrative data within worksheets.
Collaboration tools were enhanced to streamline working together on spreadsheets in real-time. Chat-enabled coediting allows simultaneous updates from multiple editors. Activity feed tracks changes across versions with comments. Excel can also integrate with Teams and SharePoint for seamless sharing and discussion of live Excel documents within Office 365 work streams.
This covers many of the key areas where Excel for Microsoft 365 has evolved with powerful new tools for productivity, automation, analysis, visualization, collaboration and management of data. These intelligent features enable knowledge workers to identify deeper patterns, have more meaningful conversations through visualized insights directly from within Excel.