New Features for Excel Late October 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.

Navigation pane

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:

1. ACCRINT
2. ACCRINTM
3. ALLCROSSFILTERED
4. AMORDEGRC
5. AMORLINC
6. BITAND
7. BITLSHIFT
8. BITOR
9. BITRSHIFT
10. BITXOR
11. COALESCE
12. COLUMNSTATISTICS
13. COMBINEVALUES
14. CONTAINSSTRING
15. CONTAINSSTRINGEXACT
16. CONVERT
17. COUPDAYBS
18. COUPDAYS
19. COUPDAYSNC
20. COUPNCD
21. COUPNUM
22. COUPPCD
23. CUMIPMT
24. CUMPRINC
25. DB
26. DDB
27. DISC
28. DISTINCTCOUNTNOBLANK
29. DOLLARDE
30. DOLLARFR
31. DURATION
32. EFFECT
33. ERROR
34. FIRSTNONBLANKVALUE
35. FV
36. GENERATESERIES
37. IF.EAGER
38. INTRATE
39. IPMT
40. ISAFTER
41. ISINSCOPE
42. ISPMT
43. ISSELECTEDMEASURE
44. LASTNONBLANKVALUE
45. MDURATION
46. NAMEOF
47. NOMINAL
48. NONVISUAL
49. NORM.DIST
50. NORM.INV
51. NORM.S.DIST
52. NORM.S.INV
53. NPER
54. ODDFPRICE
55. ODDFYIELD
56. ODDLPRICE
57. ODDLYIELD
58. PDURATION
59. PMT
60. PPMT
61. PRICE
62. PRICEDISC
63. PRICEMAT
64. PV
65. QUARTER
66. RATE
67. RECEIVED
68. REMOVEFILTERS
69. RRI
70. SELECTEDMEASURE
71. SELECTEDMEASUREFORMATSTRING
72. SELECTEDMEASURENAME
73. SELECTEDVALUE
74. SLN
75. SYD
76. T.DIST
77. T.DIST.2T
78. T.DIST.RT
79. T.INV
80. T.INV.2T
81. TBILLEQ
82. TBILLPRICE
83. TBILLYIELD
84. TREATAS
85. UNICHAR
86. USEROBJECTID
87. UTCNOW
88. UTCTODAY
89. VDB
90. YIELD
91. YIELDDISC
92. YIELDMAT.

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.