New Features for Excel Late October 2022
8 November 2022
The latest updates see the Navigation pane is now Generally Available for Excel for Windows, and for Windows and Mac Insiders, Automate Tasks with Office Scripts enables you to automate repetitive tasks in your Excel work. There are other additions / improvements too, with the full list as follows:
Excel for the web
- Quickly ‘Find the Command’ you need
Excel for Windows
- Automate Tasks with Office Scripts (Insiders Beta)
- Navigation pane
- Quickly ‘Find the Command’ you need
- New DAX Functions in Excel Data Models and Power Pivot (Insiders Beta)
- New Paste Options when using keyboard shortcuts (Insiders Beta)
Excel for Mac
- Automate Tasks with Office Scripts (Insiders Beta).
Let’s plough through.
Quickly ‘Find the Command’ you need
In Excel for the web and Excel for Windows (on an Insiders Preview basis), it has just become easier to find that feature or function (i.e. “command”) you need using the shortcut menu. This should save you time for other stuff and allow you to focus on the key problems at hand.
It is ever so simple to use:
- Right-click an area or object in your document, worksheet or presentation (alternatively, you may use the menu key on your keyboard or press SHIFT + F10 to display the shortcut menu too)
- Type the first few letters of the command you are looking for in the Search box at the top of the shortcut menu
- Click the command you want, or use the arrow keys to select it, and then press ENTER.
Erm, that’s it. This feature is currently available on the following platforms and in the following applications:
- Web: Excel, PowerPoint and Word
- Windows: Excel, PowerPoint and Word for Office Insiders running Beta Channel and Current Channel (Preview) Version 2206 (Build 15331.20010) or later.
It may take a short while to roll out so don’t panic if you don’t see it immediately.
Automate Tasks with Office Scripts (Insiders Preview)
Office Scripts enables you to automate repetitive tasks in your spreadsheet workflows and automate repetitive tasks in your spreadsheet workflow and automate repetitive tasks in your spreadsheet workflow…
These scripts help you save time by increasing efficiency and reducing errors in your workflow, and you can even schedule your scripts so that your automations can run even while you’re away.
Up until now, you could only create these helpful scripts in Excel on the web. With this update, you can now create and modify scripts in Excel for Windows and Excel for Mac (in Insiders Beta) using the Code Editor, as well as view and run scripts in the ‘All Scripts’ task pane. This work represents the first stage of bringing Office Scripts across all Excel platforms, but we don’t imagine VBA will be quaking in its boots just yet.
To view and run scripts:
- open any workbook in Excel for Windows or for Mac and select the Automate tab
- to run an existing script, select one in the gallery or in the ‘All Scripts’ task pane
- in the ‘All Scripts’ task pane, select any script in the gallery or click the ‘All Scripts’ button to view more detail
- to run the script you’ve selected, click the Run button on the script’s detail page.
To create and modify scripts:
- open any workbook in Excel for Windows or for Mac and navigate to the Automate tab.You should note that all the scripts in your workbook are available as well as various Microsoft samples
- you may start making your own scripts by selecting the ‘New Script’ button
- to modify an existing script, select Edit on the script’s details page or select the pencil icon by hovering over any script in the ‘All Scripts’ task pane.
As this is new, there are some known issues / restrictions:
- the ‘Action Recorder’, which records the actions you take on a worksheet and saves them into a script for you, remains a web-only feature at this time (that is a shame unfortunately)
- Office Scripts in Excel for Mac does not support scripts saved to SharePoint at this time
- the Ribbon buttons do not work when logged in with a non-work / school account, or when completely logged out. To fix this issue, switch to a work or school account as your primary identity (there is no need to restart Excel).
To use Office Scripts in Excel for Windows or Excel for Mac, you must have the following:
- a commercial (E3 / E5) licence for Microsoft 365
- for Windows users only: Microsoft Edge WebView2 installed.
This feature is available to Office Insiders running:
- Windows: version 2209 (Build 15726.20000) or later
- Mac: version 16.66 (Build 22092601) or later.
The Navigation pane in Excel is an easy way to understand a workbook's layout, see what elements exist within the workbook and navigate directly to those elements. Whether you're a new user getting familiar with Excel or an experienced user trying to navigate a large workbook, the Navigation pane may prove useful.
With this pane, you can find and access elements such as tables, charts, PivotTables and images within your workbook. Navigation will no longer be such a “pane”. Once you've opened the Navigation pane, it displays on the right side of the Excel window. The Navigation pane also makes it easier for those with visual impairments to access all parts of the workbook. It can improve how tools such as screen readers interpret your workbook.
Simply click on the ‘Navigation’ button in the ‘Show’ grouping on the View tab of the Ribbon, viz.
You can then navigate through the workbook with ease:
This new feature is part of Microsoft’s grand scheme to make their products inclusive and accessible to everyone, including people with disabilities. It’s clear that the Navigation pane will assist in:
- ensuring Excel works seamlessly with assistive technology
- building inclusive experiences for people with disabilities
- supporting authors in creating accessible content.
New DAX Functions in Excel Data Models and Power Pivot (Insiders Beta)
Excel’s Data Model
and Power Pivot are tools that help you analyse data across multiple tables and
support a powerful expression language called Data Analysis eXpressions (DAX). Using the features of the Data Model, you can
quickly relate various data sets to create PivotTables and Pivot Charts, using
a similar technology to that employed by Power BI.
As of these updates, you may now use 92 new DAX functions when creating measures in Power Pivot. This provides you with new capabilities to analyse, visualise and report on your data. At the time of writing, there are presently 353 DAX functions available, but a significant number were not recognised in Power Pivot. Adding these functions into the Excel realm brings Power Pivot closer to parity with its counterpart features in Power BI.
The new functions are as follows:
Presently, this feature is available to Beta Channel users running Version 2208 (Build 15504.10000 ) or later. It should be noted that Data Models may only be created in Excel for Windows and Power Pivot is a COM add-in and therefore must be installed.
New Paste Options when using keyboard shortcuts (Insiders Beta)
It’s common to copy text from a web page, an email or another document and then paste it into Excel. Often, your goal is to match the format that’s already in place in the cell or range where you’re pasting. Now you can simply copy the content that you want to paste into an existing worksheet and press CTRL + SHIFT + V.
A lot of the keyboard shortcut fraternity have become very excited by this one (I must confess this has passed me by a little). It’s not to say you couldn’t do this previously, it’s just it took more steps, e.g.
- Paste the copied content by pressing CTRL + V, and then select ‘Paste Values’ or ‘Match Destination Formatting’ in the ‘Paste Options’ dialog box
- Select ‘Paste Values’ or ‘Match Destination Formatting’ in the Paste drop-down menu in the Ribbon (press ALT + H + V to open the menu with the keyboard)
- Press CTRL + ALT + V to open the Paste Special dialog, choose ‘Values’ or ‘Text’, and then press ENTER.
Goodness me, those long winter nights will just fly by now.
The updated version of the grid with all the new features is fast becoming too complicated to show here. Nonetheless, you can find the interactive links at aka.ms/ExcelFeaturesFlyer.
More next month, we’re sure.