Master 10 new and revamped Excel features

Garreth Hanley:
This is Excel Tips, a podcast for accounting and finance professionals, brought to you by CPA Australia.

Neale Blackwood CPA:
Welcome To Excel Tips, my name is Neale Blackwood, and in this episode we're going to look at some new features that have come into Excel recently.

1. Online version of Excel: The first thing I wanted to cover isn't really a feature, but it's something you should be aware of, is that there is another type of Excel, it's another version if you like. It's the web-based version of Excel. You can access it via your Microsoft 365 login on the internet, and it runs Excel in a browser. Now the advantage with that is that you can pretty much run Excel from anywhere, so you can run it from a phone or a tablet.

It gets around any platform issues, so you can run it from the browser. It's sort of a competitor for Google Sheets if you like. The downside is obviously you have to be on the internet, and the other aspect is that the browser-based version does not have all of the functionality that the desktop version has. In the companion video, if you want to check that out on the INTHEBLACK website, you'll see that you can have the browser open and you can have a file open on desktop, and you can be interacting with that single file through two separate interfaces.

To be able to do that, you've got to save the file to OneDrive. So OneDrive is Microsoft's online file-sharing facility, so it's like Dropbox. And if you save your files to OneDrive, you have the ability to share between desktop devices so someone else can be working on it, and you'll see an example of that in the companion video. One thing about the web-based version is that Microsoft have been using it to experiment a little bit with some of the new features coming to Excel. So some of the new features actually start out in the browser version, and there's two examples of that.

The first is the in-cell drop-down list. Now that's been in Excel for a long time, but something that's been requested for a long time is the ability to type in a letter in that dropdown and just see the entries that start with that letter. Now that hasn't been possible before, but now in the web-based version you can see that has been implemented. Having said that, when I actually recorded the video, I found out that even the desktop version has a version of that drop-down. It's not entirely the same, but it still works the same in that you can type in a letter and be able to just see the options that start with that letter. So that's brand new in the desktop version as well.

2. Position of the blanks option in filter dropdown: Another feature that's been tested in the web-based version, and it's not in the desktop version as at the time of recording anyway, is the position of the blanks option in the filter drop-down for Excel. So Excel allows you to filter columns, and if there are blank cells in that column, there is a blanks option usually at the bottom of the list. Now that's really frustrating, because oftentimes you want to turn off that blanks option or you want to use that blanks option, which means you've got to scroll down to the bottom of the list to be able to select it or deselect it.

In Power Query there is a blank option that's called null, but that's at the top of the list. And so that's really easy to access, and that's what people have been requesting, for the blanks option in the filter interface in Excel. And on the web-based version, it's there, it's there right at the top of the list. So be patient, hopefully that's eventually going to work its way into the desktop version of Excel as well.

3. Power Query import updates: I mentioned Power Queries, Power Query's got some updates to it as well. So Power Query has now the ability to import data from a picture. So, for example, you could take a photo of a table of values and then be able to import that table of values. So it's also able to import from PDF files, dynamic arrays, which are a new formula type in Excel, as well as Power BI models. So you can have a fairly complex Power BI model outside of Excel and you can import the data via Power Query.

4. CTRL key and mouse updates: So something that I demonstrated in the companion video is the ability to use the control key and the mouse. Now this ability has been there for a long time in Excel, but they've changed how it works a little bit. So you are able to select ranges that are not in the same range if you like. So they're called non-contiguous ranges, so they don't sort of touch, so they're separate ranges in separate parts of the sheet. And the idea is you select the first range, then you hold down the control key, and then you can use the mouse to select another range and another range.

The problem was that if you made a mistake in one of those selections, you had to go back to the start and start from scratch. Now in the latest versions of Excel, if you've made a mistake, then you can just keep holding the control key down, and reselect the bit that you want to change, and that'll take it off, and then you can use it again to select the range to add it back on. So it's a lot easier to modify one of those non-contiguous ranges, that's a new feature as well.

5. Formula bar value display: Something else that's changed is the ability to see what a value is in the formula bar. So in the formula bar, in Excel, if you're referring to another cell, that cell could be in a whole 'nother sheet. What you can do is highlight the reference in the formula bar, and Excel will display above it what the value is, and that applies to a function as well. And in the past what you could do is you could highlight a part of a formula and press the F9 key, and I use that a lot in training because that replaces the formula with the value. So from wherever the values come from, the formula is updated with that value.

The problem with that is you needed to remember to hit the escape key, otherwise the value stayed in the formula. So this new technique of just highlighting in the formula bar and it displays it above the formula bar is a lot easier. You don't have to worry about pressing any keys, and can help you understand your formulas, and what the values are, even if you can't see where the formulas are coming from.

6. Office Scripts: Another new feature, this is a brand-new feature, it's in the Automate ribbon tab in Excel, and it's called Office Scripts. Now this is a replacement for Excel's macro language of VBA, Visual Basic for Applications. One of the problems with macros is you can't run them on that web-based version of Excel that I mentioned. The online version of Excel cannot run the VBA macros.

So what Microsoft have decided to do is to go with another language if you like. It's called TypeScript, and it's going to run the Office Scripts. So TypeScript is a subset of JavaScript, which is like a huge language that runs the internet.

Jacqueline Blondell:
If you're enjoying this podcast, you should check out our in-depth business and finance show, INTHEBLACK. Search for INTHEBLACK on your favourite podcast app today.

Neale Blackwood CPA:
What they're going to be doing is using TypeScript to create the Office Scripts, which sort of replace macros, and they can automate processes. You can still record an action, and then the Office Script will write the code for you, but the code, I think, in the long run, you're still going to need to be able to edit that code. The code is not as readable as VBA. VBA is a very English language, you can look at it and get a real feel for what the programming language is doing. Office scripts not so much. It's a little bit more complex, I think, than VBA. That's my first reading of it anyway. They will work on the desktop, so this is Office Scripts, they work on the desktop, and on the online, web browser based version of Excel as well. So they both work in both places, but you do need to be online to access Office Scripts. But it is a new feature, so it is going to develop, so just keep an eye out on that one.

7. Vertical sheet tabs: Something else that's been requested in Excel is the ability to see the sheet tabs vertically. Obviously we've got them horizontally across the bottom of the screen, but if you go to the View tab, there is a navigation button in the middle, if you click that, what happens is on the right-hand side of the screen a task pane opens up and it lists all of the sheets in the file. If you open the sheet up there, it shows you all of the used ranges within that particular sheet, so it's really useful to get an idea of the structure of that sheet. If you're using range names, then they're listed there as well, and also if you're using formatted tables, they're also listed. So it's a new way to navigate around. It might be easier, because if you click on the range in the task pane on the right-hand side, it'll actually take you to that particular range in the sheet involved.

8 & 9. Notes and threaded comments: Now something unusual happened with a new feature in Excel. Normally when Microsoft creates a new feature it gives it a new name. Well, they've changed that. We had a feature in Excel called Comments and it was like a pop-up note or a Post-it Note on a cell. So you could right click a cell, add a comment, you could type something in, whenever anyone pointed to the cell, the comment would pop up, and so they would see it. So people use this for things like instructions or just notes about a particular cell. Now that particular functionality has been changed and it's now called a note, okay? Now the comment has been taken over and it's been updated, and it's now a threaded comment.

So it looks a bit more like social media. You can post a comment onto the cell, and then someone can reply to it, and then you can reply to them, and so it builds up this threaded comment. So all of the comments are date-stamped. There is the ability to resolve a comment, which sort of closes it off. The idea being that you might have someone maybe helping you develop a spreadsheet, and so you might have a bit of toing and froing creating a formula, for example, and so once it's fixed, then you can just resolve that. And that comment will stay there as documentation if you like, on how the formula was built. But you can un-resolve it. So if you do need to open it back up again, you can. It's a new more modern comment system, and so I said they've taken that name and they've modified it so that it's a threaded comment system. There's two different icons, so there's an icon for the comments, and the old little red triangle that was in the top right-hand corner, that's still there, and that's for the notes. You can only have one of them on a cell, either have a note or a comment, you can't have both.

10. Show changes: And I thought I'd finish off with the last feature that I wanted to cover, it's called Show Changes. So again, if you've saved to Microsoft OneDrive, you have the ability to track changes in the file, and that's changes you make and changes anyone else makes when they're accessing the file. It's under the Review tab, it's called Show Changes, and it opens up a task pane on the right-hand side of the screen. And it lists all of the changes, with the sheet name, the cell reference, and the changes that's been made. So that can be handy for auditing purposes where you might try to figure out what happened on a sheet.

So the Show Changes feature is only available if you've saved the file onto OneDrive. Okay, so it's exciting times with Excel, so there's lots of new features being updated. In the old days it used to take about three years to get some updates to Excel, and now it's happening a lot more regularly. So keep an eye on Excel because it does change, or if you do want to check if you've got an update that's possible, so what you can do is go to your File ribbon, go to the account option on the left-hand side, so there is an update options button, so you can click that and one of the options there is to update now. And so that will update you with any pending updates if you haven't already updated them. As I mentioned, exciting times for Excel with lots of these new features, functions, and things coming through, so I hope you found that useful. Thanks for listening.

Garreth Hanley:
Thanks For listening to Excel Tips, a CPA Australia podcast. If you've enjoyed this episode, help others discover the show by leaving us a review or sharing this episode with colleagues, clients, or anyone else looking for the best Excel tips and tricks. To find out more about our other podcasts and CPA Australia, check out the show notes for this episode, and we hope you can join us next time for another episode of Excel Tips.