Excel Announcements @ Ignite 2019
Published Nov 04 2019 06:00 AM 49K Views
Former Employee

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.

 

171120_FY20Q2_M365-Apps_Excel-Natural-Language-Query_Gif.gif

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.

 

158453_EXCEL_XLOOKUP_GIF_V04.gif

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.

 

171609_FY20Q2_M365_APPS_Excel_Sheet_View_GIF_v05.gif

 

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.

 

arrays-device.gif

 

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

 

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.

 

1.gif

 

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.

 

Autoclassification (003).gif

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

 

19 Comments
Silver Contributor
Hi Will SheetView also become available in the desktop app?
Copper Contributor
I don't see "Ask a Question" in Ideas. I tried on Excel on the web and with my Office 365 insider account. When will this feature be available?
Copper Contributor

"We’re happy to announce that dynamic arrays will be generally available starting this week on Excel for the web." 

 

Great news.  Will this apply to the Monthly channel and, if not, any ETA?

Looking forward to the availability of XLOOKUP to Office 365 users

Iron Contributor

@Jee Soo Han, these are great enhancements for Excel. They need to be available in all versions: desktop, online and mobile.

 

Specifically, when will the Sheet View be available in desktop?

Copper Contributor
"We’re happy to announce that dynamic arrays will be generally available starting this week on Excel for the web." "For the web" ... are you kidding me. I guess I can stop my 365 subscription and just use the web version.
Microsoft

Hey folks, really like the way you are taking excel to next level. I sincerely recommend to have a glance at following video which would recommend additional features beyond query I.e functions as algorithms and more.

 

https://m.youtube.com/watch?v=4pXQRmliTcM

 

 

 

Copper Contributor

Hi, What’s is the file size limit on a shared work sheet.

Brass Contributor

What about Simplified Ribbon? When will I receive this feature?

Copper Contributor

Where I can sign up for the annoucement that the script is avaiable? Is there a preview I can get to test it? @Marcin Rachwalski 

Microsoft

@chrismenard7 you should now be able to see the "Ask a question" functionality as a part of Excel Ideas. Please let us know if you are still not able to see it.

Copper Contributor
@Sharon910 I do have "Ask a Question" now. That is an awesome feature! I liked it so much I made a YT video on it. https://youtu.be/M2Qez_VkCz8
Microsoft

@chrismenard7 we're excited to hear that you like the feature, and thank you for sharing your video! If you have any feedback on your experience, we would love to hear from you via the Excel Feedback page (File -> Feedback).

Copper Contributor

Hi, 

Can i get same visualization on MS Excell like power BI ? if yes, please advise. 

@chakrabartyjoydeb , nope. For some cases you may have similar, but not the same.

Copper Contributor

I still do not have ask question box. When will that be available? I have version 2004 (Build 12730.20250) Monthly Channel

Microsoft

@tky6716 Hello! Natural language processing is being made available to customers on a gradual basis. You should see it soon! 

Copper Contributor

When is this Xlookup coming to all excel users???

@StevenBehr95 , my guess is that to all users - never. It never will be on Excel 2016 and perhaps 2019, only on Excel 2022 or so. Thus fall most probably all Office365 subscribers will have it  (shall be added to semi-annual channel). Something like this, but again, that's only the guess.

Version history
Last update:
‎Nov 26 2019 12:22 PM
Updated by: