Excel Announcements @ Ignite 2019

11/26 Editor's Note: Updated links to Help articles and visuals showcasing sensitivity labeling functionality.

Microsoft Ignite 2019 has begun and there are lots of announcements coming your way throughout the week. Here is some of the Excel news that will be presented at Ignite this year:  

Easily turn data into insights with natural language query

Rolling out today to Office Insiders, natural language query is a feature in Ideas in Excel that allows users to ask a question of their data to glean insights without having to write complicated formulas. Using the intelligence that powers Ideas in Excel, natural language query will quickly answer users’ data questions with formulas, charts, or pivot tables. The feature will be available on Windows, Mac, and Excel for the web in English language only at this time, with plans to expand to other languages in the future.

To use the feature, you simply need to open the Ideas pane in Excel, enter a question in the query box at the top of the pane, and press enter. Ideas will provide answers supported by formulas, charts or pivot tables that can be inserted into the workbook.

With natural language query, we’re continuing the journey to make data insights and visualization more approachable and accessible to users of all levels of Excel experience. It’s for the novice users who may not know how to write the right formulas to gain useful insights from their data, the power users who can save time by simply asking the right questions and quickly adding charts and tables, and everyone in between. Natural language query can help users get the insights they need for better and faster decisions. Read this article to learn more about natural language query and how to use it.

Find what you need faster with XLOOKUP

Previously, we released XLOOKUP to Office Insiders and have gotten a ton of great feedback from our users. As successor to the iconic VLOOKUP function, XLOOKUP helps users find values better and more efficiently by unblocking previous limitations that existed in VLOOKUP.

As of October 29, we’ve added an [if_not_found] argument to the feature based on strong feedback from the Office Insider community. [if_not_found] was rolled out as the 6th argument to XLOOKUP, but will be promoted to the 4th argument as it will see more usage than [match_mode] and [search_mode]. This change will alter the behavior of any pre-existing XLOOKUPs that use four or more arguments. If you have any XLOOKUPs that do, you will need to revise them once you get the update to ensure they continue to calculate as you intended.

XLOOKUP will be generally available in the coming months. Learn more about XLOOKUP by reading this article and try it out. We look forward to hearing more of your feedback!

Collaborate without disrupting a shared workbook with Sheet View

Have you ever opened a shared workbook and panicked because some of the values in the table were missing? Often, this panic sets in when a user adds filters to or sorts a column in a shared document. We love to collaborate in real-time, but that means what one user changes in the document will instantly be visible to others. While this is great functionality, it often works disrupts collaboration in Excel when one or more users need to manipulate a table to view certain values and changes the table for the entire group.

With Sheet View on Excel for the web, this problem is no longer a stumbling block for collaboration. Sheet View allows users to sort and filter data, and then select an option to make those changes just for themselves or to everyone in the document. Once you choose the option to make the changes just for yourself, you can also save the results to view again as a separate view. All this can be done without disrupting others’ view of the data on a shared file. Views can be saved to be accessed by other users in the document when collaborating in real time, avoiding disruptions in workflows across teams. All they need to do is click on the View tab and find the Sheet View they need in the drop down list on the left side of the ribbon.

Sheet View will be available by the end of this calendar year on Excel for the web. To learn more about Sheet View, visit the Help article here.

Write fewer formulas for more values with dynamic arrays

We’re happy to announce that dynamic arrays will be generally available starting this week on Excel for the web. We will be rolling out to subsets of our users to continue to gather feedback and monitor quality as we strive to provide a great experience for our customers. With dynamic arrays, you can write one formula such as FILTER, SORT, or UNIQUE (among others) to get an array of values returned, rather than having to write one formula that returns only one value at a time.  Values from dynamic arrays will automatically “spill” or be placed in neighboring blank cells so that you get all the values you need from one formula.

To learn more about dynamic arrays, read our previous announcement post and the help articles here:

• Spilled range operator and how to use it
• Various dynamic array behaviors 
• How dynamic arrays compare to legacy array formulas
• New #SPILL error and scenarios that can trigger it
• Implicit intersection and dynamic arrays
• Dynamic arrays in prior versions of Excel

Use Office Scripts to automate tasks easily

Office Scripts is a feature that enables users from those of novice Excel experience to those with advanced coding expertise to record scripts and automate their tasks. The feature will be available as public preview on Excel on the web by the end of 2019.  Once Office Scripts is available, users can get started by clicking the Record button in the Automate tab, recording the actions desired, stopping the recorder, and then saving the script.  The script is saved to OneDrive for Business, which allows them to be easily reused across workbooks. After recording a script or choosing one that was already created, a user can run the script in one of three ways: manually starting the script from within Excel on the web, automatically in a flow on a schedule, or conditionally in a flow based on a trigger.

For users who do have coding experience, Office Scripts also allows you to take recorded scripts and extend them using the same Excel JavaScript APIs that are available in Office Add-ins.

Office Scripts will be available by the end of 2019 in public preview for Excel on the web, check back soon for more information on how to enable and use it.

Classify documents and protect access to sensitive data

Knowing what data you have, understanding the level of sensitivity of that data, and protecting your documents accordingly are key steps to securing your organization’s most sensitive information. Sensitivity labels can help prevent accidental access to a document by people outside the originally intended audience. With Office 365, users can maintain control of access and sensitivity of their documents by manually applying a label or using the automatically recommended labels from Microsoft.

Already in production on Mac and mobile, manual sensitivity labeling is currently rolling out on Excel on Windows and the web for Office 365 subscribers.  A shared workbook can be accessed if a user has access rights provided by any encryption associated with the sensitivity label (labels can be configured to have encryption).  For example, if a workbook is labeled Internal, it restricts read/write access to employees of a company, which means employees will be able to open, view and modify content in the document while access is restricted to external users. 

With automatically recommended labels, Excel will notify you of potential sensitive content in the workbook and recommend a label per your administrator’s security settings.  This happens automatically when you open the workbook and continues to work in the background as you update the file.  In addition, if your administrator has configured automated labeling, a label may be automatically applied to the workbook.  The label on a workbook is visible in the Status Bar, and adjustable through the Home >Sensitivity button.  

Automatic sensitivity labeling is currently rolling out to Insider Fast for Excel on Windows and private preview for Office on the web. To learn more about applying sensitivity labels to files, read the Help article here.

To read more on the latest Microsoft Information Protection news, read the blog post here.

We hope you enjoy all the latest Microsoft news this week from Ignite! Let us know your thoughts in the comments below and, of course, via UserVoice. To stay connected to Excel news and community, read the Excel blog posts and follow Excel on Facebook and Twitter.