Why Excel can be a university student’s secret weapon

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

Neale Blackwood:
Welcome to the Excel Tips podcast. My name is Neil Blackwood, and in this episode we're going to look at some handy Excel tips for university students and recent graduates. Now, one of the first things you'll notice when you get a job and start working is that Excel is an important part of most positions, especially in accounting and finance roles. Now, the beauty of that is that if you get good Excel skills, that makes you more employable. Because you'll find that organisations might use different accounting packages, but they all tend to use Excel fairly heavily.

So Excel skills are transferable between jobs. I thought I'd start off with some areas that you need to focus on if you want to learn Excel, because if you can have Excel skills before you get to the job, then you can hit the ground running and be more productive earlier. I'd start with structure. When you open up a blank Excel file, you might have one or two tabs down the bottom and a blank spreadsheet. You have to add the structure to it if you are building it. So one of the ways you can do that is to break your Excel file down into different tabs or sheets.

The sheet is where you do all your work and the idea is that you have separate sheets for separate things in Excel. So for example, you might have a list of assumptions that are the basis for the file that you're working on. You might have a sheet for a dashboard, so you might have charts and things like that. You might have separate tabs or... So tabs and sheets are pretty much the same thing. So you might have a separate tab for data. And it's a good idea to separate your data from your reports. So you might have multiple data tabs and you might have multiple report tabs.

So if you are creating financial models or budgets, it's a good idea to separate the inputs. So you might have sheets that are dedicated just to the inputs for the budget or financial model. Always a good idea to have instructions. Put an instruction sheet in there. I use a separate tab for my lists and my tables that I use throughout the model. You might create separate sheets for workings and also validations. One of the problems that you face when you're working with Excel is that there can be errors built into your model, so you need to identify those. So a validation sheet is quite important, to make sure things like the balance sheet balance and also to make sure that everything that should be adding up is adding up. So a validation sheet is an important part of it. If you check out the companion video on INTHEBLACK website, then I do give you a brief overview of a validation sheet. Okay. Now in terms of the basic building blocks of Excel, it's working with cells and ranges. So a cell is where you enter your data, your labels, your formulas, which are important, and when you are building formulas, you refer to cells.

So A1 is how you refer to the very top left cell and you use the column letter followed by the row number. So A1, B2, things like that. When you are referring to a range, so you often work with cells, but you more often work with ranges. So you might want to add up a range. So to do that, you refer to the top left cell, followed by the colon, followed by the bottom right cell. So it might be something like A1:C100, as a range reference. Now, you use range references with functions typically, and the main function in Excel is the SUM function. So that adds up ranges. So there's actually a built-in keyboard shortcut to insert the SUM function. It's alt, so you hold the alt key down and press the equal sign and that will insert SUM function. There's also a built-in icon, I think it's the Greek letter sigma, that is also called auto sum, which you can find in the top right-hand corner of the home ribbon. In Excel, you can put that on what's called the quick access toolbar.

So if you right click that icon, the auto sum icon, there is an option to add to quick access toolbar. So that appears on the toolbar to make it really easy to use basically from anywhere. So the SUM function is the most popular, but there are lots of functions available in Excel. What I wanted to focus on first was the keyboard. Now in Excel, you do a lot of work with the plus sign and multiplication and things like that, so it's a good idea to use the numeric keypad. Now unfortunately, a lot of laptop keyboards do not have the numeric keypad on the right-hand side. So if you are using a laptop, I recommend using a laptop with a new numeric keypad or just a separate keyboard that has that numeric keypad on there. The reason is, all of the numbers are on there and they're in the calculator layout rather than the phone layout. And there's a large plus sign and a large enter sign on there as well, plus all of the operators that you need like, plus, minus, divide.

The multiplication symbol is the asterisks in Excel or the star, and that's also on that numeric keypad. So that just simplifies creating formulas. And one tip for creating formulas, you need to start all your formulas with the equal sign, but you'll find that you can start formulas with the plus sign and when you hit enter, Excel will put the equal sign in there for you. Now that's good because on that numeric keypad, you've got this large plus sign, so it's really easy to hit that. The equal sign is tucked away in the middle of the keyboard and it's harder to get to, whereas that plus sign is really easy to hit. So you can start all your formulas with that plus sign, so that can save you a little bit of time. Now when you work with cell references and range references, you'll quickly find that if you want to copy a formula across or down, you're going to need to learn about the dollar sign and how it affects references. An A1 reference is what's called a relative reference.

So if you've got that in a formula and copy it down, it's going to change to A2, A3, A4. If you copy a formula with A1 across, it's going to change to B1, C1, things like that. So that's a relative reference. You can use what are called fixed or absolute references and mixed references. Now you need to use these to be able to copy across and down in most cases. So the most commonly used one is the fixed or absolute reference. And what that does, is it has a dollar sign in front of the A and a dollar sign in front of the one. So a dollar sign in front of the column letter and a dollar sign in front of the row number. And what that does is that fixes the reference. So as you copy the formula down and across, that reference won't change. So in the video example on INTHEBLACK website, I did a calculation on GST and I had the 10% in a separate cell and I wanted to refer to that cell. So when I copied the formula down, I didn't want that cell reference which was J1 to change.

So that's where it's important to use the dollar signs in your references to make sure that you can create a formula that you create once and then just copy down and/or across. Using the dollar signs is a lot easier if you use the F4 function key. When you press F4, it adds both dollar signs to the reference it's next to. Now that's really easy to enter. It's easy to remember that F4 because the number four has that dollar sign above it. So the F four function key and just a heads-up on some laptops, you might need to press another key to use your function keys because they tend to keep a reduced set of keys on a laptop keyboard. But you press the F4 function key, it adds the dollar signs to the column letter and the row number. You can press it more than once. So each time you press the F4 key, the dollar signs move around. I'll use A1 as the example. So it starts off with A1, you press the F4 key ones and it goes to dollar A$1. Press it again and it goes to A$1.

Press it again and it goes to $A1, and press it again and it goes back to A1. So that's the sequence that it always follows. So you get to know how many times you've got to press the F4 key to get the reference you need. And remember, whatever the dollar sign is in front of, that is the reference that doesn't change as you copy it across or down. So the dollar A, if you just had $A and then A1, the A would never change. If you had A$1, the one would never change. You get used to it. It does take a bit of trial and error, so don't be worry if you don't get it first go. It's really easy to change. So you just use that F4 function key. You can type the dollar signs in yourself as well. I tend to try and reduce the amount of typing I do, so I prefer the F4 key to insert the dollar signs.

Now the first rule of Excel formulas, if a value can change, then don't enter it into the formula. And again, I use the example of the GST with 10%. You could, if you wanted to work out GST, just put the 0.1 for 10% into the formula. But the problem with that is, if you've done that throughout your file and then the government decides to go and increase the GST, then you've got to go through and find out all of those or track down all of those references that have used that 0.1. Now just remember you could be using 0.1 for other things as well. So you can't necessarily use find and replace to fix it. But the idea is that rather than put the value 0.1 into a formula, you put it into a cell with a label, and that's an input cell, and then you use that cell reference in your formulas.

That way, you only have to change one cell to affect all of the other formulas. So it sort of centralising the input and it just reduces the need to go and try and modify formulas to track down where the 0.1 is for a GST calculation. Okay. It's now functions. So you can build normal formulas like five plus six or something, but you can also work with cells and ranges to do calculations and you can use what are called functions. Now I mentioned the SUM function. It's the most popular and commonly used function in Excel. It adds up ranges. So there are a lot of other functions that I've listed in the article in a table. So there's about 20 there I think, and if you focus on those, you'll actually get quite good at Excel. Now some of them work in pretty much the same way as sum. So there's average, count, min, max. So those functions work in exactly the same way as the SUM function. So you have the equal sum, open bracket, then you refer to a range, and then you close the bracket. And that adds up whatever is in that range that you've inserted. And remember, you start with the top left cell, and then the colon and then the bottom right cell. Now, you can type your cell or range references in, but it's much easier to use the mouse to select a range and Excel will type in the reference for you.

So there's an example of that in the companion video that's on INTHEBLACK website. I mentioned the auto sum icon, which is on the home ribbon on the top right corner. If you click the dropdown of that, it's easy to enter functions like average, min, max and count. So they're in that dropdown. So you can automatically enter them. Much like the SUM function and it will automatically insert a range for you. You do need to check that range just to make sure it's correct. Providing you've got no blanks in the range that you're looking at or empty cells, then the range is typically correct. But always check it.

Jackie 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:
Now another important part of Excel is formatting. So when you create a sheet from start, there is no format applied. So it uses what's called the general format, which is pretty much no format. The only time you might see a format applied is if you enter a date into a cell and Excel will automatically format that date for you. But everything else is pretty much left to the way you entered it. So if you enter 0.1 into a cell, it's just going to be 0.1, it's not going to be 10%. You need to format it. And most of the formatting entries are on the home ribbon, under font alignment and number. So there's also a dropdown in the number section that has a lot of built-in formats that you can use as well. Formats are really good because they can help you lead and direct the user to use your file correctly. So I highly recommend that you use a consistent input cell colour for cells that you're expecting to use it to change. So use that throughout the file.

There is an easy way to do that, it's called styles. On the home ribbon, there's a style section. And what you can do is you can use the built-in styles and you can also create your own styles. The beauty with styles is that, if you are using orange as an example for the input cells and then your boss comes in and says, "No, I think I'd rather yellow," then you can just change the style. And if you've used that style to apply the format, then that automatically changes all of the formats throughout the file. So again, it just simplifies getting a consistent format throughout your file without a whole lot of work. So styles are a really easy way to get consistent formatting. Couple of tips on formats. Don't go overboard with colour. You can use so many different colours in Excel. Use it sparingly and for effect. Also be aware of colorblindness. Now, colorblindness affects about one in 12 men. I think it's about one in 100 women, so it is a male thing.

The most common form of colorblindness is red green. So in the typical traffic light report, where you might have red is bad, green is good. That's not necessarily going to help people with colorblindness. So you might want to consider using ticks and crosses, which I'm going to talk about in a minute, which is part of conditional formatting. Just be aware. That said, the red and green, typical traffic light report is not helpful for people with colorblindness. Maybe use pastel colours as well, not necessarily the bright colours. When you are showing large numbers, always use the comma format. So that has the comma separator for the thousands. Just use the dollar signs sparingly. You could put the dollar sign format on all of your numbers if they're dollar values. If you overuse them, it does just add extra characters to your report that doesn't make it easier to read. Rather than put the dollar sign on all the numbers, just put the dollar sign as part of the heading and then just leave the dollar sign out, and just use the comma separator. Okay. As I mentioned, conditional formatting can add ticks and crosses to your reports.

Now, this is typically used in things like dashboards, where you want to put, let's say we're looking at a variance, and you want to show whether it's a good variance or a bad variance. So favourable or unfavourable variants, you can use a tick and a cross as a symbol, as an icon, and you can colourize them. But the fact that their icons gets around the issue with the colorblindness. So you are using an icon that is showing that it's a tick. It can be a green tick, but the fact that it's a tick gets around the problem of the colour coding, if you like. One thing I forgot to mention earlier was the colour coding you can use on sheets. So when you are creating the sheets that I mentioned earlier and separating the file into different sheets, which is different sections, you can use colour coding on the sheet. So you can right click a sheet and just select the tab colour. So for example, you might have multiple reports, so you could maybe make all of those sheet tabs the same colour, and same with data. And just using some colour coding can help you and others understand the file a little bit better. Now, something else that's fairly visual is charts.

So charts can take a table of numbers and convert them into an easy to read format, that you can see the ups and downs in sales and things like that, and trends, and also relationships. So highly recommend that you check out charts. They're in the insert tab, about halfway across. There is what's called a recommended chart button. And what that does if you select your range first, and then click on that recommended charts, it gives you a list of what it recommends. Now it's using patent matching to identify the charts that could be best, but certainly check those out, especially if you're just starting out using charts. Okay. Printing. Now, in Excel, printing is a lot more problematic than say Word or PowerPoint or even Outlook. Excel tries to print everything that's on the sheet, unless you tell it otherwise. And to do that, you use what's called the print area. When you're working with printing in Excel, the page layout tab has pretty much everything you need, and pretty much everything's in the page setup section.

You can also use the scale to fit section on there, and there's a print area dropdown. So what you do is, you select the range that you want to print, and then click on print area and set the print area. Now, that may mean that you are not going to print things like workings, which you might have around your report, for example, on the one sheet. So you can just define the print area. Either way, it's always a good idea to check your print before you hit the print button. So if you go file, at the top and then hit the print option, it'll show you what it's going to look like and down the bottom, bottom middle of that page, you'll see how many pages they're going to print. So always check that. There's been a lot of empty pages printed over the years, when you just hit print in Excel and Excel tries to print everything on the sheet. Now remember, Excel can have up to a million plus rows and 16,000 plus columns, so that can be a lot of paper. So always check your print before you hit the print button. Okay. I thought I'd throw in some bonus topics. So that's the basics of Excel, that gets you started. There's a couple of other things that if you learn, it will take you to the next level in Excel. And the first is Format as Table. Now, this is a feature that's been in Excel for over 15 years. It came in Excel 2007.

And what it does, it allows you to define a table, as like a little database in Excel. Now, Excel is not a database, but what it does is it treats the table like a database, so that as you add data to it, the table expands for rows and for columns. It's got a lot of advantages. One of them is that the formulas you create in the top of the first cell, for example, in a column, if you create a formula, it will be copied down, automatically. You don't need to do anything. And if you add an extra row, the formula is copied down for the new row. So there's an example of that in the companion video. So highly recommend that you check out the video because it does demonstrate the Format as Table. Following on from Format as Table, there is a feature called PivotTables. Now, PivotTables have a really and it's a weird name, but what they do is they summarise data. So if you've got a data table, you can use a PivotTable to summarise that data table, and maybe break it down by state or by category or by customer type or whatever. However, you've broken down your table, you can summarise by the columns in the table. And you do it without a formula. So it's just a drag and drop functionality there. So it's a really powerful data reporting tool. Now, Power Query. Power Query has been around for about 10 years. It allows you to import data into Excel and make it repeatable. So the idea is you'll go and import data. So let's say from a CSV file, which is a comma-separated values file, which is a very common type of file to move data around with. You can import that. And in the importation process, you can update and fix things that might be wrong in the data. Because when you get out in the real world, you'll find out that the data out there is not in a great shape. So sometimes you do need to fix that data before you can actually use it, in things like a pivot table.

And Power Query automates that. So the Power Query allows you to go through and do all of the importation and let's say correction. The technical term is data cleansing, and it captures that. So you can repeat that whole process by just refreshing. And it can also bring in multiple files. So Power Query, really powerful, and it really does take Excel to another level. So highly recommend you learn that. The last thing I wanted to cover was something called Dynamic Arrays. Now, these are a new form of calculation in Excel, and what they do is they spill. So normally a formula you have to copy down and across, whereas with a dynamic array formula, it will automatically copy across and down, and the formula is only in the top left cell of the range.

Again, I gave a brief example of that on the companion video. Most of the stuff I've covered in this podcast, there is extra information on the INTHEBLACK website. If you go to INTHEBLACK website, and at the top left, click on the menu option, and then click on technical skills, then you'll see a whole lot of articles that are related to Excel and also other technical skills. So you can do a search and track down some of the topics that I've covered today. That's some of the areas that I recommend university students and graduates concentrate on when they start out using Excel. The earlier you start, the earlier you'll start to master some of these things. And if you can have the skills before you go into a job, you'll find that you'll be a lot more productive, a lot quicker if you've got good Excel skills. Now, if you found this podcast useful, please subscribe and also if you could leave a review, that would be appreciated. Thanks very much for listening and hope to catch you on a future podcast. Thanks very much.

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.