Excel Shortcuts - Ditto Cell Above
September 04, 2017 - by Bill Jelen
The Ditto Key in Excel. This great shortcut can copy a formula down one cell, whilst keeping the cell references pointing at the original cells. Why is this useful? Read on.
You have to sum in D10 and average in D11. Create the AutoSum in D10. When you press Enter, you are in D11. Press Ctrl + ’ to bring the exact formula down without changing the cell reference. If D10 is =SUM(D2:D9), the formula in D11 will also be =SUM(D2:D9).
From there, you can press F2, Home, Right, AVERAGE, Delete, Delete, Delete, Enter. It sounds crazy, but the engineers at General Electric in Cleveland swear by it.
If you use Ctrl + Shift + " it will bring the value from above into the current cell, eliminating any formula.
Watch Video
Video Transcript
Learn Excel From MrExcel, Podcast Episode 2145: The DITTO Key.
I love this one. Alright, so, right here, we have a formula that is totalling those numbers, and if I would come right below that cell -- this goes back to elementary school, where we would say something like, if we would write a word and then, beneath that, we would press the “ key, right, that “ key was saying that we're just…you know, it's the same word as above, right, at least back when I was in 1st grade. So, it was a great way to not have to write so many letters. You could just use the “.
So, here, we're going to do the same thing. CONTROL+SHIFT “ -- in other words, the quotation mark -- and it will bring that formula down as a value, alright, and so I can lock a value in, kind of CONTROL+C, ALT+E, S, V in a new spot, but the place where this is really handy, and I learned about this at General Electric in Cleveland, I was doing my seminar there, and the engineers would use this one all the time because they would have to total something and average the exact same thing, and so I have a total formula here, =SUM, and if you would just copy this down, right, it's going to change the reference. It’s going to be referencing the wrong thing, alright?
So, that's not effective, but what they would do is CONTROL UNSHIFT the “, so CONTROL ’. It will bring the exact same formula down and leave it in edit mode, and from here they would actually press F2, HOME, RIGHT, SHIFT, RIGHT, RIGHT, RIGHT, and then type AVERAGE and press ENTER, and they would end up with an averaging AVERAGE that was averaging the right reference, alright?
So, CONTROL ’ will bring that formula down and keep the reference exactly the same; a couple of cool, great little tricks.
Well, hey. I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Title Photo: exoteric / Pixabay
