Top Excel tips for your first 100 days in a new job

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 I wanted to share some Excel tips that might help you in your first 100 days in a new job. Now, starting a new job is both exciting and stressful, and hopefully some of these tips can take some of the stress out of a new position. Most accounting and finance positions involve using Excel files, and there's a few things you can do when you take over a job to make using the Excel files a little bit easier.

Starting off with a word of warning. When you take over a job, if you get a good handover, that can make it a lot easier, but that doesn't always happen in the real world. So you might be taking over an Excel spreadsheet and you haven't had a handover, so you're going to have to find your way around. You might also find that the person who created the Excel file may no longer be in the organisation, so this can mean that it's going to take a little longer for you to figure out the file, especially if it doesn't have instructions or documentation associated with the file.

Interface setup:
So the first thing you can do when you start a new position in Excel is to get the Excel interface working. So there's a toolbar in the Excel interface called the Quick Access Toolbar. So when you first install Excel, the Quick Access Toolbar sits above the ribbon and it'll only have a couple of icons on there. Now, if you right-click it, you can show it below the ribbon, which is where I tend to have it, and any icon on the ribbon, you can add to that Quick Access Toolbar by right-clicking it.

So this means you can build up a list of all of your commonly used icons to allow you to work a little bit quicker. It also means that as you're learning a job, you'll find you'll be using different icons and you can just keep adding to the Quick Access Toolbar. You can also right-click on the Quick Access Toolbar and customise it. This means you can move the icons around on it, so then you can put icons that are associated with certain tasks together. For example, you might want to put all your printing icons together, all your formatting icons together and maybe all your data related icons together, just makes them easier to find.

Pin files and folders and quick access:
In terms of working with files in Excel, if you go to the file ribbon tab, you've got the open option there, and when you're working with files, there is a little icon, it's the pin icon, so you can pin files to your recently open list, and they all appear at the top of the list. So this makes it easier to find files that you work with a lot. You can also do the same pinning with folders. So there's a folders section of open as well. So if you are using folders regularly, you can pin them to the top of the list.

This also works in Word and PowerPoint as well. So these type of shortcuts apply across the Office Suite. You also have the ability to, when you're opening a folder if you right-click the folder, there's an option to add it to Quick Access. Now, this is a listing of folders on the left-hand side of the screen, and this is throughout Windows basically, so that makes it easier to access folders wherever you are in Windows. Okay, so that's the interface.

Using checklists:
Now, something I recommend you use, and this applies whether you're starting out at a job or you've been in it for a few years, is using checklists. So checklists allow you to identify the things that you need to do. Maybe they're in a sequence, maybe they're not, but they're things you need to do to perform a task. Now, that task can be finance related or something, or basically for anything. Pilots use checklists, doctors use checklists. So checklists are an easy way to make sure you don't miss out on anything. I use checklists for a lot of tasks. I also use some codes. So typically, if I've done something, I'll use the X. If I've started something but I haven't finished it, I'll put an S and if I'm working through it and I'm maybe waiting on something, I'll put a W. So that way I can judge where I am for each of the tasks.

In Excel, it's really easy to have a separate tab for each checklist. Some checklists I print out. For example, when I run webinars, I have a checklist for my webinars. So I actually physically print that out and I tick it off as I go. That just makes sure that I've got everything, turn my phone off, all of that sort of stuff so that everything runs smoothly.

Instruction sheets:
Now, when you're working with Excel files, hopefully the Excel files that you're using have instruction sheets. Now, if they don't, that's where you're going to have to maybe build one because instruction sheets can make it a lot easier to work with the file. Those instructions can include things like hyperlinks, so you can actually go directly to wherever you need to go within the file. So I'll cover hyperlinks a little bit later. An instruction sheet can make it easier to understand what you're doing. Also, it might have checklists built into it as well, so you can work your way through those.

Sometimes the instructions might be hidden, so if you right-click any of the tabs at the bottom of the screen, there is an option to unhide. So if the unhide option is there, it means there are hidden sheets and instructions may be hidden simply because the person might've been doing the job for a while, and so they don't need the instruction sheet anymore. So they may have hidden it. Also, when they're distributing it, they may hide the instruction sheet. It's always a good idea to right-click the tabs, check out the hidden sheets and see if there's any instructions or documentations there that may help you.

Notes and comments:
When you do start a job, you do get the opportunity to ask lots of questions. So when you get the responses, make sure you note them down, especially if they're relating to the Excel file that you're working with. There's a few ways to do that. So you can type the responses into a cell. You can also use a note. So if you right-click a cell, you can enter something called a note, and that note is like a Post-it note on the cell.

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:
There's also an option called comments. So if you right-click a cell, you can insert a new comment, and that's like a threaded comment, which I've mentioned in a previous podcast in terms of some of the new and changes to Excel. So either use a note or a comment, and that allows you to put a note onto a cell. So again, that can help explain possibly where the input for a cell comes from. You can put a note in there about that.

Setting up hyperlinks:
Now, back to hyperlinks. A hyperlink will take you directly to a place in the file. You can also hyperlink outside of the file to another file or to the internet. In terms of instructions, you typically hyperlink within the file and it takes you to the sheet that you need to do something in. That might be inputs, that might be dropping some new data in, something like that. The shortcut to create a hyperlink is control and the letter K. So hold the control key, press the K. On the left-hand side of the dialogue, click on, "Place in this document," and that'll give you a list of all of the sheets. It also lists range names as well, so you can link to a range name if you use range names in Excel.

Now, when you follow a hyperlink, to return to where you followed it from, you can use the key combination. You press the F5 function key and then press, "Enter." Just a heads-up, on the laptops, sometimes you need to use an extra function key to access the function keys, so you've got to hold down the Fn key and then hit the function key on the laptop keyboards. That's one reason why I always recommend using a separate keyboard if you are using a laptop, makes it so much easier to hit the function keys.

Using colours in Excel:
Okay, colours in Excel. You can use colours to help you figure out where, for example, you need to input. So I recommend having a standard input colour within a file, and that way you know what cells to input. So you can use a different fill colour. I tend to use a light yellow for my input cells. You can also use the colours on the tabs down the bottom. I try and limit where I make inputs in my file, so I'll have specific input tabs, and typically I'll change them to yellow when I'm starting a process. And then as I work through, so if I've started but haven't finished, I'll change the tab to orange, and when I've finished it, I'll change it to green. So I know that that tab is ready to go. To change the tab colour, simply right-click it, and there's a tab colour option, and you can select a colour.

Centralise inputs:
Now, speaking of inputs, I recommend that you try and centralise your inputs as much as possible, as well as using that colour coding. Instead of having to go to all these separate sheets, it's much easier to capture your inputs in one place and then link to them, especially if you're entering the same thing multiple times, just enter it once and then link to that, and that just saves a lot of work in your files.

Validations:
Validations are an important part of Excel files, and what I do is I recommend that you centralise the validations. So you'll probably find that you have a lot of validations within each of the sheets that you are working with, and that's great, but it's a good idea to centralise those. So link them all back to a single sheet, and that way you can monitor all of the validations in one spot, and that makes it so much easier to figure out if something's gone out of balance, where it is and that sort of thing.

If you need to follow a link, so either you're following a link to a validation or to a value that's in an Excel file, there is a keyboard shortcut that lets you follow that link, so it's control, so hold the control key down and press the left square bracket. Now, that's next to the letter P on your keyboard. So control and the left square bracket, that will follow a link, so it will take you to where the value is coming from, and again, that other keyboard shortcut that I mentioned, which was the F5 function key, and then enter. So you press F5, then press enter. That will take you back as well. So you can use these keyboard shortcuts in combination to find where values are coming from and then go back to the original place and then find where the other ones are coming from.

Save-as and copy-sheets for monthly reporting:
Now, two typical structures that you'll find you'll be working with is using one file per month for reporting, and the other structure is one sheet per month. Now, when you're using the file technique, the F12 function key is really useful because that's, "Save As". So, basically you can take a file, save as, maybe change the file name and include the new month, and then save it to possibly to a different folder as well. So F12 is the, "Save as," shortcut, and again, that works throughout Office. And so when you are working with a different tab for each month, then what you can do to copy the tab, so if you click and hold the sheet tab with the mouse and move it to the right and then hold the control key and then release the mouse, you'll find you'll make a copy of the sheet. It's an exact copy with everything in it, and then you can rename it and work with it. In the companion video, I show you how you can do that and how you can also automate the reporting process by using the sheet name to generate the report, so you can check out the companion video for that.

Tips recap:
Okay, so hopefully there's a few tips and tricks that can make your first 100 days a little bit easier. Ask lots of questions, make sure you capture the answers, whether that's in Excel or a Word document. Remember, if you can't find the instruction sheet, then you probably need to develop one so that it's easier for the next person. It's also going to be easier for you the next time you do it. Ultimately, you probably won't need the instruction sheet because you'll know what you need to do, but initially it saves you having to remember everything and it captures things in one place. So highly recommend having instruction sheets. Highly recommend having centralised inputs and also centralised validations so it can make your job a lot easier. And that also applies to a job that maybe you've been in for a while, so there can be some straightforward changes that make using the file a lot easier. I hope you found some of these tips and tricks 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.