Excel Tips: 10 new and revamped features

For users with the subscription version of Excel, there are regular updates that include new features and functions. 

There have been many important updates to Excel in the past three years. Here are 10 of the most important feature updates. 

For recent function updates, click here.

Some updates may not be available in your version. To check the installed version, click the File tab and click Account (bottom left). Users need Microsoft 365 and, in the About section, version 2307 or later. 

Click the Update Options drop down and try Update Now to check if there is an update ready to load.  

1. Excel web platform

Excel now has two platforms. The first platform and most popular is the desktop version of Excel, which is the standard spreadsheet used by most businesses. 

The second platform is Excel for the Web, a newer, browser-based version of the software that is used online. Excel for the Web has limited functionality, but it is being regularly updated to mimic the desktop user experience more closely.

A Google Sheets competitor, Excel for the Web is compatible with multiple platforms because it runs in a browser. 

2. In-cell dropdown filter

Microsoft has been experimenting with rolling out new Excel features in the web version before releasing them for desktop. An example of this is the new in-cell dropdown list filter feature.

Over the years, a popular request for the desktop Excel has been the ability to type a letter into an in-cell dropdown cell and have the drop-down list filter by the letter typed in. 

This functionality is now available in the web platform – see Figure 1. Hopefully, this option is not too far away from rollout to desktop.

Another feature recently added to Excel for the Web is the filter change for blank cells.

In Excel, the Blanks option has always been listed last when filtering a list. This means that, to exclude blank cells when filtering, the user had to scroll to the bottom of the filter list to untick it. 

Excel for the Web now shows the Blanks option first – see Figure 2. 

4. Power Query

Power Query is Excel’s data importation and cleansing feature. Recent updates to Power Query have given users the ability to import from PDF, dynamic arrays, Power BI models and image files. 

5. Ctrl + mouse key range selection

Few people realise that it is possible to select more than one range at once in Excel. 

To do this, select the first range, as per normal, using the mouse. Then, hold down the Ctrl key and use the mouse to select other ranges. 

Users can select as many separate ranges as they like, which is useful for formatting, copying and deleting. 

A recent Excel update gave users the ability to change the ranges selected using this technique.

Before, if a mistake was made in selecting the ranges, users had to start over from scratch. 

With this update, users can now hold the Ctrl key down and re-select the unwanted range to remove it from selection, then select the correct range to add it in.

6. Auto display values in the Formula Bar

When reviewing a formula, it can be useful to see the result of a function or what the value is in a particular cell. 

There is now an auto display in the Formula Bar when part of a formula is selected. Excel will display the value or the result above the part that has been selected. 

Note that the formula part selected must be able to be calculated separately – see Figure 3. The highlighted XLOOKUP function is returning the #N/A error – shown above the Formula Bar.

7. Office Scripts

Excel has released a new way to automate processes. In the past, only macros could be used to automate processes, and macros do not work in the web version of Excel. 

Microsoft has released a new scripting language, Typescript, that will work in both the desktop and web versions. From this comes a new feature – office scripts. They are in the Automate ribbon tab – see Figure 4. 

Office scripts are written in Typescript and can be recorded the same way that macros are recorded. Users can rerun them at the simple click of a button.

The Typescript language is a superset of Javascript, while Excel macros are written in Visual Basic for Applications (VBA), a subset of Visual Basic.

Presently, the processes that Typescript can record are limited. However, as a new feature, it will continue to evolve and improve. The main advantage of using Typescript is that the scripts can be executed in both the desktop and web versions of Excel.

This features the use of OneDrive for business and internet access.

High on the wish-list for many users is the ability to display sheet tabs vertically, rather than horizontally at the bottom of the program. 

The new Navigation Pane provides a vertical list of sheet tabs. The Navigation icon is in the View tab, in the Show section – see Figure 5.

The Navigation Pane opens on the right-hand side of the screen. You can click the sheet name to navigate to the sheet – see Figure 6.

The pane has a few other useful options. It can show formatted tables, e.g. tblSales in the Data sheet, as shown in Figure 6. Ranges used on each sheet can be seen, and users can navigate to a range by clicking on it. This information can help you gain an understanding of the structure of your workbook.

9. Comments vs Notes

Excel’s old Comments feature has been updated and rebadged as Notes. 

The old Comments feature added notes that looked much like post-it notes on cells. Mousing over a cell made the note pop up. The Comments feature was used for notes, instructions and explanations. Each cell with a comment had a small red triangle in the top right corner – see Figure 7. 

A new comment feature uses the name Comments now, and it provides a threaded comment system. This allows multiple users to create separate comments associated with a single cell. The comments can be viewed together in timeline order, with date and time stamps – see Figure 8.

This feature is particularly useful when a team is developing or using a file, the idea being that multiple people can have a conversation in the comment thread. Users can mark a comment as resolved to close the thread and prevent adding new comments. Comments can be edited or reset if needed. 

Note that a single cell can contain either a comment or a note, but not both.

Users who save their files on OneDrive, Microsoft’s cloud file storage platform, can use the new Show Changes feature. The icon is on the Review tab – see Figure 9.

This opens a task pane on the right of screen. It allows users to see who made what changes, and when – see Figure 10.

This new feature only works for files saved to OneDrive.

Future Excel updates

Keep tabs on the latest updates to Excel via Microsoft’s Excel blog.