Master the latest Excel changes: your ultimate guide

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

Neale Blackwood CPA:
Welcome to the Excel Tips podcast. My name is Neale Blackwood, and in this episode, we're going to look at changes to how Excel opens CSV files, as well as three new functions. You will need the subscription version of Excel to see these new features.

Importing and using CSV files
Okay, CSV files. They're the workhorse of the data industry. Basically, every system will drop data into a CSV file. Now, CSV stands for comma separated values. It's a very efficient way to hold data. Some people call them a flat file because they're basically two dimensions. When you open a CSV file, it just has one sheet in it, so it's just a single data set.

In the past when you've opened a CSV file in Excel, Excel makes some assumptions about the data, and unfortunately, for accounting and finance people, some of those assumptions don't work that well. The main one that affects data that comes in for accounting and finance reasons is leading zeros. So leading zeros are often on account numbers and they can be on mobile phone numbers. So when Excel opens a CSV file and it sees numbers and it sees leading zeros on the numbers, it removes any leading zeros, which can be a problem. One way around that is to use Power Query because Power Query can treat the column as text, and so you have more control over it. I highly recommend using Power Query. There's lots of Power Query resources on the INTHEBLACK website as well with lots of examples.

Without Power Query, you can now open up a CSV file, and when you do, it gives you the option to ignore any conversions. So there's a button you can click to basically don't do any conversions so that it imports the data as it is, which that's really good. Now, there are some settings that you can play with. So if you go file and then options, then there's a data section under options, and at the bottom of that dialogue are some new CSV file settings. So you can set some default settings. You can turn them all off, or you can turn certain ones on, so you can have a play around with that. So that's under file, options and then the data settings, and it's at the bottom of that screen.

So whilst that has improved how you can import CSV files, which is a good thing, I do recommend you use Power Query to import CSV files because you can do a lot of data cleansing. So fixing things like dates, missing data, things like that. Power Query is really good for that. And as I mentioned, there's lots of resources on the In The Black website for Power Query.

MAP, SCAN and REDUCE function
Okay, there's three new functions that are in the latest versions of Excel. They're called map, scan and reduce, so we'll work through them. One thing they all have in common is they all use the lambda function. So lambda, L-A-M-B-D-A, is a special function in Excel. It's been around for a couple of years. It takes as its first arguments variables, so you can import data into the lambda via the variables. Once you have them in the lambda, you can then perform calculations using Excel's functions and other calculation options. So all three of these files need the lambda to actually work.

Using the MAP function with ranges and LAMBDA
Okay, we'll start with the map function. So dynamic arrays are a brand new calculation feature in Excel, and they changed the way you create formulas. One way to think about dynamic arrays is that where you used to work with a single cell and compare that to maybe another cell, you can now refer to a range and compare that range to a single cell. And that's the example I've used in the companion article and the companion video. Initially, we're looking at comparing a quantity with a level, and anything above that level we're going to give free freight for. So quantities above 20 get free freight. In the past, you created a single formula that you copied down a range to figure out whether freight should be free, but now you can create a single formula that will populate a range in this case down the sheet to match the range that you selected.

So in this case, our example was A2 down to A10. So the formula spills from row two all the way down to row 10, but the formula is only in, in this case C2. So all of the other formulas in that range will have a display of value, but the formula in the formula bar will actually be greyed out. So you make all your changes in the top left cell when you're using a dynamic array and it's spilling. By the way, the terminology for populating the range is called spilling. So it creates a spill range that can go down the page, across the page, and spill ranges can even create two-dimensional ranges.

Now, one of the problems you face when you're trying to create a spill range is that some functions won't spill. Typically, they're functions that are actually expecting a range. So when you use the range within the function, it just stays in the single cell and it doesn't spill down. Two functions that do that are the OR function and the AND function. Both of those functions are used for logic calculations. So they're typically used with the if function. You can use them by themselves as well, which I've done in the example, but in this case, they do not spill down when you try to use them, and that's where the map function comes in. So the map function allows you to define a range, and it could be a spill range, and what it does is it extracts every single cell from that spill range, and it then gives those individual cells to a lambda function. And then the lambda function has a variable that takes those cells and then it can perform a calculation on that.

And so the syntax is fairly straightforward. It's equals map opening bracket. Some people call them parentheses, I prefer bracket, it's easier to say. And then you refer to the range, comma, then you use the lambda. So L-A-M-B-D-A, open bracket. Then you need to give it a variable name. Now, in the example I've just used QTY, short for quantity, comma, and then you can perform a calculation. So in this case it was the or function. So OR, open bracket, QTY greater than F1. So cell F1 had the value that I was comparing to. So you close the first bracket, which is the or function, close the second bracket which is the lambda function, and close the third bracket which is the map function. By the way, when you are creating nested functions like this, keep an eye on the bracket colour because they match. So each function, the opening bracket and the closing bracket are the same colour, and when you're creating nested functions, the last bracket is always black.

Okay. So that's the way it works. And when you hit enter on that formula, it will spill down to match the range. The map function allows you to pretty much access every single cell in a range or a spill range. To refer to a spill range by the way, you just refer to the top left cell and have the hash symbol. So you have the hash symbol on the end, and that refers to the spill range that starts in that cell. Now, in the companion video and the article, I used an example using two ranges because the map function can handle more than one range. You basically separate the ranges with commas, and then for each range you'll need a separate variable name in the Lambda function. So each range has to have a variable name in the Lambda, and just as before, you can perform a calculation using those variable names.

Using the SCAN and REDUCE functions
The next two new functions are related. They have exactly the same syntax, so they're laid out exactly the same. They perform exactly the same calculation, but they return a different result. They are the scan function and the reduce function. So the scan function returns the spill range, the reduce function returns a value in a single cell. So even though it's performing the same calculation, it returns a single cell value. So let's see how they're used. The typical example for a scan function is a running total. The scan function has three arguments. It has an initial value, comma, then it has an array, which is typically a range, comma, and then it uses the lambda function to perform its calculation. Now, the initial value could be zero. If you're doing a running total, then you probably don't want to have an opening balance, but if you did have an opening balance, that's where you'd put it, in this initial value, comma.

And then you can have a list of numbers. You can also use this for text, which I'll explain in a minute. And then the lambda function has a variable for the initial value and then a variable for the array. Now, in terms of the array, it gets every single cell from the range that you've referred to. The first variable, however, is a little bit different. So let's see, I'll try and explain how it works. If you want to check out the video, I go through in a fairly detailed way to explain how this works. But here goes. Let's use the example of X and Y. So X and Y are the two variables. X takes the initial value. Y gets the, let's call it the monthly value, because the example we used gave us a running total over multiple months.

So here we go. The X function initially gets the opening balance, which I used as zero. The Y value gets the first value from the range, which was 17 odd thousand dollars. So those two are then added together, so X plus Y, so that's what was within the lambda, just X plus Y. So on the first cell, it's basically exactly the same. Whatever that value was, that's what the running total was. This is where it gets interesting. On the next row, so the next row, the X variable gets the balance from the previous row, so it gets that 17 odd thousand value as the X value. Then the Y value is whatever was in the second cell in the range, which was also 17,000. The total ended up being about 35,000 for that particular row. So that's how it works. The closing balance, if you like, from one row becomes the opening balance for the next row. So that's the first variable.

So I used X and Y, so X always gets the previous balance, and don't forget, you gave it the initial balance with that very first part of the scan function. I used a zero for that. And so it just works its way down and it shows every single value as it goes down. So you get a running total. So it's a really easy way to create a running total based on a range or based on a spill range. So that's really useful. But the reduce function does exactly the same calculation, so it keeps a track of all the individual rows, but the reduce function only shows you the final balance, which was the total in this case of all of our values that we added up. The reduce function gives you a single value, the last value, whereas the scan function gives you an accumulated running total.

Another example I gave to show you how you can use it is building up a text string. So in the example, the scan function started with... The initial value was actually text, CPA with a space, and then in the range that I refer to, I had all of the letters of Australia. And so when you use the scan function, each individual letter of Australia is added to the CPA followed by the space. So you get the building up, if you like, of the text string. And then the very last entry in the listing was CPA Australia. So the scan function gave you each letter being added from Australia to the text string. So the reduced function just gave you the final entry, which was CPA Australia. In the companion video, I also had a look at another example of using the scan function to create a balance for a loan schedule.

So it's really useful in that context. The scan function simplifies creating that running balance for a loan schedule. It was possible in the past, but you had a fairly complex formula to calculate that, whereas now you can just use the scan function and refer to the monthly principal amounts that you have listed in your loan schedule. So again, check out the companion video to see that. Okay, so lots of things changing in Excel recently. So we've got CSV files. You now have a little bit more control over them. The map function allows you to access every single cell of a range or a spill range, and the scan and reduce allow you to create a fairly easy running total calculation. 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.