New Features in Excel for the Web

Microsoft decided to get busy before they broke up for the seasonal year-end break. Excel for the web benefitted immensely with a raft of new features / updates:

• searchable data validation list
• Queries pane
• text in shapes
• paste option floaty
• sheet direction switcher
• open and edit more files.

Searchable Data Validation List

Selecting an item from a drop-down list is now simpler, especially when the list is long. You may search within the data validated list by typing any letter that is part of the item you’re looking for and Excel for the web will help auto-complete the search for you. Blank values are hidden from the drop-down list and duplicate values are removed.

The full Power Query experience is rolling out to Excel for the web. Coming first is the Queries pane, with the familiar user experience of the ‘Queries & Connections’ pane in Excel Desktop. With this Queries pane, you can view all the queries in the workbook in Excel for the web.

Text in Shapes

If you’re using shapes to add design and context to your data and analysis, you should note that you may now add or edit text in shapes. Simply select a shape and start typing to add or edit text in the shape. Alternatively, double-click on the shape or right click and select Edit text.

Furthermore, you may now add a text box by selecting the Insert Ribbon tab -> Shapes -> Text box and start typing to add text.

The text may be formatted too. You can change font style, size and colour using the text formatting options on the Home Ribbon tab or via the quick option floaty when you select and right click on the text.

Paste Option Floaty

You may also now access all the Paste options right where you’re pasting content or after:

Sheet Direction Switcher

For users preferring the right-to-left (RTL) experience, you may now flip a spreadsheet to RTL at the click of a button on the ‘Page Layout’ Ribbon tab. Previously available only in Excel Desktop, switching to RTL – and back – is now also now available in Excel for the web.

Open and Edit More Files

As can be seen from above, Excel for the web continues to add features and improvements to close the gap with Desktop. To this end, you may now open and edit more files on the web and complete your job anywhere, for any given browser.

For instance, you may now open and edit large files from SharePoint up to 100MB. This allows you to work with large numbers of rows of data and compete your work without having to switch to the Desktop.

Furthermore, you may also now edit files containing Legacy Art objects and SmartArt objects in Excel for the Web. These legacy art objects include Form Controls, ActiveX Controls, Camera Tools and OLE objects. This enables you to access and interact with any of such workbooks, but not with the objects themselves. If you are using either a Button form control or an ActiveX control to run a macro, watch out for the upcoming Office Scripts functionality that will allow you to run Office Scripts with a workbook button.

You can also now access your password protected workbooks on Excel for the Web. This allows you to open and interact with your files protected with a password to open without switching back to Desktop. However, it should be noted that co-authoring is not supported with password protected files (which is the same as with Excel Desktop). In this scenario, Microsoft recommends you switch to the new Microsoft Information Protection (MIP) encrypted workbooks that allow you to co-author and still protect sensitive data.

But that’s not all. You may also edit files which are protected for editing using a password to modify, thus allowing you to access / interact with different types of protected workbooks on the browser itself. As noted above, co-authoring is not supported for Password to Modify (again, same as Desktop), therefore, here it’s recommended that you share edit access to specific people that allows you to block editing to others.

More is to come. The following capabilities will soon be coming to Excel for the Web:

• support for large files up to 100 MB from OneDrive (only for M365 subscribers) in Excel for the Web, thus enabling you to work with large datasets in the browser
  
• editing of files with the presence of legacy shared workbook feature, by provisioning you with one-click turn-off of the legacy feature, thus allowing you to interact and collaborate with the workbook
  
• editing of files containing the legacy data wizard connections like Text Queries, Web Queries, Query Table or SharePoint Lists, thus enabling interaction with the workbook. Do note that Microsoft has stated that they do not plan to support the connection itself and recommend you should switch to import data via Power Query to connect and refresh data.