More 'Excel Shortcuts for Accountants' Mini Episode 3: Ctrl Key

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, it's Neale Blackwood here again to share some more Excel tips with you. This one includes a few visual elements, so bear with me as we work through it in this audio version. So, what I'd like to cover today is combining the mouse and the keyboard. Most people tend to be either keyboard focused or mouse focused. The Control key, which has lots of shortcuts on the keyboard, also works with the mouse. Here's the deal. When you click hold and drag something, so you can do that with a cell, a range, or even a sheet like a tab down the bottom, you are basically cutting and pasting. So, you're moving it from one place to another. If you hold the Control key down while you do that, you'll actually copy it. So for example, a chart. If you click and hold a chart and drag it and move it with the mouse, then if you do exactly the same thing but you hold the Control key down while you do it, you'll make a copy of the chart. And then for example, you could change the range that the chart is reporting on.

Neale Blackwood CPA:
And so, it's a really quick and easy way to make a copy of a chart and maybe just change the data that it's reporting. Now, that works with pretty much everything. So, it's a really handy shortcut. Now, this works in Word and PowerPoint as well. So for example, if you've got images or shapes that you need to copy, all you need to do is to drag that shape and then hold the Control key down and then release the mouse. So, timing is reasonably important. With the chart, actually, it's a little bit quirky because when you drag the chart you don't hold the Control key down initially. So, you basically drag the chart, start dragging it and don't release the mouse, and then hold the Control key, and then release the mouse, and that will make the copy. I don't know what changed. In the old days, you could hold the Control key down all the way through. I think it was Excel 2013 that things changed a bit.

Neale Blackwood CPA:
Now, that works with ranges, so you can select a range. Point to the border. Now, around the border of a range is an arrow symbol. Now, that's the symbol where you can click hold and drag and move it. Now, be very careful moving ranges and cells around by dragging them like that because that can create what are called #REF errors in Excel, because that's a cut and a paste. But if you hold the Control key down while you do it, you're actually copying it, which doesn't really have as many problems associated with it. When you do hold the Control key down, you will see in some cases that the icon will change slightly and you'll see a little plus sign display. So, that's Excel basically telling you it's going to do something different, so where you would normally be cutting and pasting, you'll be copying and pasting. So, keep an eye out for that little plus sign because as I said, it does show you that you are going to do something different.

Neale Blackwood CPA: In terms of copying sheets, this is the quickest way to make a copy of a sheet. Click and hold on the sheet. Now, I'm just using the normal left mouse button here. So, I have spoken previously about the right mouse button but this is just the normal left mouse button. So, if you click and hold on a sheet and drag it to the left or the right, you are moving the sheet in terms of where it is associated with the other sheets. But if you hold the Control key down, you will in fact make a copy of that sheet. So, that's the quickest way to make a copy of that sheet. Now again, that can be really handy if you've got a pivot table report in the sheet and you want to tweak the report. Very common to have pivot table reports that are just differently laid out.

Neale Blackwood CPA:
So, they might have the same data in them but you might just split them up a little bit differently. So, you can have two reports that the total of the report might be exactly the same but you might be splitting things out; you might be splitting up by state and by product and all that sort of stuff, and you want to have both types of reports available. That's especially important for subtotals, for example, because the hierarchy you use in a pivot table will adjust the subtotals that are in there. And maybe you want a subtotal by product and subtotal by state; that's not always possible in a single pivot table, but you can just make a copy of the sheet and then modify the report, so you've got both reports giving you both subtotals, for example.

Neale Blackwood CPA:
So, the examples so far have focused on converting a move that you would normally do into a copy and a paste instead of a cut and a paste. But there is another technique that the Control key is associated with. What I want to discuss now is what you can do with the fill handle. Now, the fill handle is that, a little plus sign that's at the bottom right-hand corner of a cell or a range when you point to it. So, it's a selected cell or range. It will have this little plus sign there. And so, it's the bottom right-hand corner. Now, when you click, hold and drag that you are typically either copying something or incrementing something. So if you drag a date, for example, you will increment the date.

Neale Blackwood CPA:
If you want to do the opposite of that, all you do is hold the Control key down. So, if you drag the 1st of the first, 2018, normally you'll get the 2nd of the first, and the 3rd, as you drag it down. But if you hold the Control key down whilst you do it, you'll copy it. So, Excel will either copy or increment. So if you wanted sequential numbers, for example, if you type "1" in a cell and you use the fill handle to drag the cell, if you hold the Control key down at the same time then you'll actually get a list of sequential numbers. So, the Control key basically just reverses what the default operation is. So, Excel figures out what it's going to do based on what's in the cell. So for example, it will increment dates, but it will normally copy numbers. So if you want to do the opposite, just hold the Control key whilst you do it. I hope you found that helpful. Thanks for listening.

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.