Excel Agility: Office 365

Microsoft 365 (formerly Office 365) delivers a continuous stream of new Excel functions unavailable in perpetually licensed versions like Excel 2019 or 2016. The most transformative additions are the dynamic array functions: FILTER (returns rows matching criteria), UNIQUE (removes duplicates), SORT and SORTBY (sort arrays), and SEQUENCE (generates number series). Lookup functions were enhanced with XLOOKUP, which replaces VLOOKUP and HLOOKUP with a more flexible, left-looking syntax and XMATCH as a modern replacement for MATCH. Formula language expanded with LET (named variables within formulas) and LAMBDA (custom reusable functions). Text functions like TEXTJOIN, CONCAT, and TEXTBEFORE/TEXTAFTER simplify string manipulation. Statistical functions like MAXIFS and MINIFS were added. For professionals evaluating whether to upgrade from a perpetually licensed Excel version, these functions—particularly XLOOKUP and the dynamic array suite—represent a substantial productivity gain for anyone working with data analysis, financial modeling, or reporting. Aurora Training Advantage covers all major Microsoft 365 Excel features in its Excel Agility training series.

Co-authoring in Excel for Microsoft 365 allows multiple users to edit the same workbook simultaneously in real time—similar to Google Sheets collaboration. Files must be stored in OneDrive, OneDrive for Business, or SharePoint Online for co-authoring to work. Each collaborator's cursor position and recent edits are highlighted in different colors, and changes sync automatically across all connected devices within seconds. AutoSave is enabled by default for cloud-stored files, ensuring all edits are preserved continuously. Co-authoring works in the Excel desktop app, Excel for the web, and Excel on mobile. Some features are temporarily unavailable during co-authoring sessions, including VBA-based operations and certain PivotTable interactions. For teams working on shared budget templates, sales trackers, or project plans, co-authoring eliminates the version control problems of emailing files back and forth. Understanding the interaction between AutoSave, OneDrive version history, and co-authoring is important for teams implementing collaborative Excel workflows in a Microsoft 365 environment.

Linked data types in Excel 365 connect cells to external live data sources, turning typed text into rich, structured data objects. The two most prominent built-in data types are Stocks (pulls live financial data like price, market cap, and P/E ratio) and Geography (pulls data like population, area, capital city, and time zone for countries or regions). To use them, type a company name or country name in a cell, select it, and click Data > Stocks or Geography. Excel attempts to match the text to a known entity and converts it to a linked data type, indicated by a small icon. You can then extract specific fields using the dot notation in formulas: =A1.Price or =A1.Population, or click the cell and select from a field extraction card. Microsoft has also made the data type framework extensible, allowing organizations to connect internal data sources as custom data types. For finance and data-driven business professionals, linked data types enable dynamic, self-refreshing reference data directly in Excel worksheets without manual data entry or maintenance.

Excel 365 is the subscription-based version of Excel included with Microsoft 365, receiving new features through continuous monthly or semi-annual updates. Perpetually licensed versions—Excel 2021, 2019, 2016—are purchased once and receive security fixes but no new feature additions after release. The functional gap between these versions widens over time: Excel 365 currently includes dynamic array functions (FILTER, UNIQUE, SORT, SEQUENCE), XLOOKUP, LET, LAMBDA, linked data types, and co-authoring capabilities that are not available in older perpetual versions or are available only in limited form. Formulas built in Excel 365 using these features will display as errors when opened in Excel 2019 or earlier. For organizations on mixed Excel versions, this compatibility consideration is important when distributing shared workbooks. Professionals deciding between subscription and perpetual licensing should weigh the ongoing feature access of 365 against the one-time cost and predictability of perpetual licensing for their specific workflow and collaboration needs.

Excel for the web, accessible at office.com, provides a browser-based version of Excel that works on any device without installing software. It supports core spreadsheet operations—entering data, writing formulas, formatting cells, creating charts, and building PivotTables—and is sufficient for everyday editing tasks. Files are stored in OneDrive and open directly from web links, making sharing and real-time collaboration seamless. However, the web version has notable limitations compared to the desktop app: VBA macros do not run in the browser, Power Query and Power Pivot are unavailable, some advanced chart types and data analysis tools are absent, and certain formatting options are limited. Excel for the web is continuously improving as Microsoft adds features, and it supports many of the newer dynamic array functions like FILTER and XLOOKUP. For organizations deploying Microsoft 365, understanding the capability boundaries of Excel for the web versus the desktop app is important for determining which work can be done browser-side and which requires the full installed application.