Excel tips: how to improve your formatting

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

Neale Blackwood:
My name is Neale Blackwood and in this episode we are going to look at Styles. Now, styles are on the home ribbon and they allow you to set up specific formats that you can use throughout your file. The biggest advantage with styles is that once you apply a style, which applies a format, you can modify it very easily. That will affect all of the cells that you have used that style on. So, for example, the built-in input style has an orange background. And so you could, you could build a file, use that orange background for your inputs, and basically go to your boss and your boss says, no, no, I don't want to use orange background, I want to use a yellow background. And so all you need to do is modify that style to use a yellow background, and that will change all of the styles right throughout the file.

Neale Blackwood:
So it does save you a lot of effort if you do need to change a format. Now, there is a, a built-in input style, but you can manipulate that one and you can actually copy it and change it to work with other types of cells. The default format for the input style is just the general format. So you may want to change the format for things like percentages dollars and say large numbers where you want, might want to use the comma format. So if you check out the companion video, I show you how to do all of those so you can see how to create these new styles. Once you've created those styles, you have the ability then to create template styles that can allow you to create a new sheet and a new book. Also, on the video I show you how to save those files and the names you need to use, because you need to use specific names, in terms of creating these templates.

Neale Blackwood:
So what you can do is create all of the styles that you want to use in all your files and then create the template so that future files will have those styles. Now the thing with styles is that they are saved with the file. So that is why if you want to use them in the Future files that you create, you do need to create these templates to capture them. There's a little trick in the comma format. So, the comma format is also on the home ribbon and it's in the number section. If you change the comma format in the styles, that affects the little comma icon that is in the number format section. And so the typical thing that you do is remove the decimals because unfortunately the standard comma format includes decimal places where most people don't want to use the decimal places.

Neale Blackwood:
They just want to put the comma format for the separator, the Thousands Separator. I also went through in the companion video a couple of the red brackets format. So, this is a format that used to be in Excel in the old days for negatives. It shows them in brackets and the brackets are red. So, there's a little trick with actually getting the numbers to line up, and that involves using the underscore character. So, check out the companion video to see how you can do that. Couple of suggestions in terms of grid lines. Grid lines are useful when developing a file, but when you are presenting a file or getting it ready for someone else to use, oftentimes turning off the grid lines can make the file a lot cleaner to look at.

Neale Blackwood:
There are grid line options under the page layout section. You can have grid lines for printing as well as grid lines for viewing and they're controlled separately. So that's in the page layout, ribbon. And the other one is in the view ribbon tab. There is the grid lines option there. And both of those options you can add to your quick access toolbar, which is the little toolbar that sometimes will sit above your ribbon, but I usually show it below the ribbon. So the quick access toolbar allows you to capture a lot of your commonly used icons and put them on a dedicated toolbar. The Format Painter, that's really useful. That's on the Home Ribbon, it's in the clipboard section On the far left hand side. The Format painter allows you to have a, a cell selected. You click the Format Painter and then you can apply that format to another cell or range, a little trick, you can double click the format painter, and that allows you to then, apply that format multiple times.

Neale Blackwood:
So, and then when you're finished, just press the escape key to turn it off. And probably the last thing I'll mention in terms of, formatting is the conditional format option, again, on the home ribbon, this allows you to create, formats that vary based on the values in the cells. This can be really useful for things like, variance analysis where you want certain variances to actually stand out. And so you can use conditional formatting to make, certain variances become more prominent, and catch the eye a little bit better than just the standard format. In terms of the templates that I mentioned earlier, you do need to save them in a specific place. So that's the Excel Start folder. And that folder is in various positions in the computer that you are using.

Neale Blackwood:
So, and that can be in various folders depending on your version of Excel. So again, check out the companion video for that. I, show you some of the various, folders that the, Excel start folder can be in. The way that Excel start folder works is any file in that folder will automatically open. So the most commonly used file in that folder is something called the Personal Macro Workbook. It's actually called Personal. Some people think it's got personal data in it, it doesn't, it has your personal macros in it. And that, those macros are available, basically whenever you open up Excel, it's the place to put your general purpose macros. And that's also the place where you actually save the book template and the sheet template when you are creating the templates for use, as, when you open new files and new sheets.

Neale Blackwood:
By the way, you templates can be specific templates. So for example, you might create a template that works with a time sheet. For example, you might have a time sheet template so people can open up the time sheet template and then create their time sheet using that. So, and it can be all set up with everything that you need. So just a heads up, you can override the Excel start folder. that's the system-based folder with your own folder in a certain place that you want to put it. And to do that, you go into file options and the advanced options and scroll right to the bottom and there is the option to change that Excel start folder. Okay. So I hope you found that useful. As I said, if you want to see how to do all of this, these steps, just check out the companion video on the, INTHEBLACK website, and you can see how you can take advantage of formatting and some of the tips and tricks associated with formatting. Hope you found that useful. Thanks for listening.

Neale Blackwood:
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.