More ‘Excel Shortcuts for Accountants’ mini episode 1: Double clicking

Intro:
Hello everyone and welcome to the CPA Australia podcast. Today's mini episode features CPA and Excel expert, Neale Blackwood. Neale has written articles for the CPA Australia publication, INTHEBLACK, since 2002. And is the author of Advanced Excel Reporting for Management Accountants. We hope you enjoy Neale's Excel tips today. Over to you, Neale.

Neale Blackwood CPA:
Hi, my name's Neale Blackwood. I write the Excel Yourself articles for the INTHEBLACK magazine. In this episode, I would like to share some quick Excel tips with you on double clicking. Now, double clicking is a skill worth pursuing because there are a lot of shortcuts you can access in Excel using a double click. The one that I get the most reaction from when I do training is double clicking the fill handle. Now the fill handle is the little plus sign that you see when you point to the bottom right hand corner of a selected cell or range. It's a small plus sign. Usually you click, hold and drag it, but you can double click it. In a list, this will copy whatever you've got in the cell down as far as there's something on the left. It will also look on the right, and it will also look in the column that you're copying down.
Neale Blackwood CPA:
So if you've already got existing entries in that column, it'll overwrite those. It does try and copy down as far as possible. So as I mentioned, it looks to the left and to the right. Something else you can double click on, is the sheet name. So that allows you to change the sheet name or the tab name, which is at the bottom of the screen. One of the common double clicks is double clicking between the columns. So there is a little vertical line between each column letter. If you point to it, you'll see a double-headed arrow. You can double click that double-headed arrow, and that will change the column width to fit whatever's in that column, it's called AutoFit. You can select multiple columns before you do it. You can even select the whole sheet. So if you click on the little icon, that's at the intersection of the column letters and the row numbers.

Neale Blackwood CPA:
So, that's the top left, that selects the whole sheet. And if you double click one of the lines between in the column letters that will change all of the columns so that they fit whatever's in the columns. This applies to row height as well, but that's not as commonly done as the column width. You can also double click the auto sum icon, which is the Greek letter Sigma. If you are trying to create a single sum function, what most people do is they'll click the sum and then they'll hit enter, but you can actually just double click the sum icon, and that will enter the sum function for you. Another worthwhile double click is the format painter. Now the format painter is on the home ribbon in the top left. There's a little... What looks like a paint brush. If you point to it, it'll tell you it's the format painter.

Neale Blackwood CPA:
If you single click it, it allows you to basically copy the cell you are on and be able to paste that format somewhere else. But you can only do that once, but if you double click the format painter, you can paste multiple times. You'll actually see that the icon change on the mouse to look like a paintbrush. And you can, as I mentioned, paste that format multiple times, that also applies to word by the way. To get out of it or to stop it pasting, which you will eventually want to do, just press the escape key and that will do that. Another one that a lot of people are not aware of is, it's a quick way to navigate around a table. If you point to a cell and you point to the border of the cell, you'll see a little arrow icon. So if you, for example, point to the top border of a cell and double click the icon, the arrow icon that you see there, that will take you up as far as that table has entries in it.

Neale Blackwood CPA:
And it will work in all four directions. So double clicking the arrow icon will move you around your sheet, depending on where you are, it might not move at all, but give it a try and you'll see that you can move around quite quickly, just double clicking that. One that I've learned recently is hiding and unhiding the ribbon. Now, I usually use control F1 to do that, but you can actually double click one of the tab names at the top of the ribbon. And if you double click it, it will either hide the ribbon or unhide the ribbon. I tend to work with the ribbon hidden so that I get more screen real estate, but that's a mouse shortcut. So double clicking one of the ribbon tabs will actually hide or unhide the ribbon. And lastly, this one's a little bit different, depends on a setting you've got. There's a setting in the advanced options, which is edit directly in cell.

Neale Blackwood CPA:
When you have that ticked and turned on, you can edit your formulas actually in the cell on the spreadsheet. I don't tend to do that. I do tend to use the formula bar for all my formula editing. If you've got that option ticked, then you can double click a cell to actually edit in the cell. What's unusual is if you untick that option, so if you untick edit directly in cells, then can actually follow links by double clicking. So if you've got a cell that's linked to another cell and you have that option turned off, then double clicking the cell will take you to wherever that's linked to. Then you can press F5, enter to come back. It's an unusual shortcut, that because it's not very obvious, but I do use it when I am sort of navigate around a file. I hope you found those shortcuts useful. Thanks a lot.

Outro:
Thank you so much for sharing your tips with us today, Neale. If you want to find out more about Neale and access further resources, go to www.cpaaustralia.com.au/podcast/58. Make sure you never miss an episode of the CPA Australia podcast by subscribing on iTunes or Stitcher. Until next time, thanks for tuning in.