I could barely sleep last night. Even though I came home, my heart was in that hospital room only. I got up at 5:30, and came to see my wife and kids by 7:30. Now that they all are sleeping, I can quickly whip an excel post, to help you make twins, clones and duplicates.
- To repeat last action: Press F4. Apparently this is the first thing the new cell did. Lets say you inserted a new row. To add one more row, you just need to press F4. Excel will repeat the last action, thus making one more row. (PS: in Excel 2007, F4 will yeild different and limited outcomes compared to excel 2003)
- To clone a chart, drawing or other objects: Hold down CTRL and click-drag the chart (or object). You will get an identical twin of it in no time. (BONUS: Holding down ALT as well will align the chart to the grid.)
- To clone value from above cell: press ctrl+’ This will make a twin from the above cell. (99 more tips)
- To make a twin from a range of cells: Select the range. Hold down CTRL and click-drag on the selection border. Drag it wherever you want.
- To Duplicate formats from one range to another: Some times the data may be different, but the formats should be same. Like my kids, even though they have different genders, they look pretty much same now. To do this, you can use the format painter tool in excel. See the demo:
- To make a live clone of a range of cells or chart: use excel camera tool. Camera will create a live preview of one range of cells as an image. So whenever the range’s contents change, the image is updated too.
Gotta go, the sister is trying to outwit brother with tongue gestures. I must see who wins this round…
PS: share your twin, duplicate and clone shortcuts and power-tips using comments.
6 Responses to “Twins, Clones and Duplicates – 6 Excel Shortcuts from an Excited Dad”
Congratulations to the new parents 🙂
All the best
Bernard
Chandoo - Can you please advise how you make the animated gifs that frequently accompany your hints? I find them to be extremely useful and would like to employ them with my own files (to enhance instructions).
SUPER job on this site - this is one of my daily stops - and I always learn something new. I love the fact that you (and your followers) make Excel FUN to use!
Many thanks!
mahqooi
Congrats on the new kids. I (well, my wife) only ever had one at a time.
Â
Caution:
"To clone value from above cell"
Ctrl+' (single quote) copies the contents, not value, of the cell above. If the upper cell has the formula =A1+B1, the lower has the formula =A1+B1. This is often not what you want or expect.
Ctrl+D copies the contents, not value, of the above cell, and it updates relative references. =A1+B1 is converted to =A2+D2.
Â
"To make a twin from a range of cells"
I prefer right click and drag, because when I release the mouse I get a drop down menu asking how I want to handle the content: move here, copy here, copy here as values or formats, etc.
Another really good "Twinning" Tool is:
Copy > Paste Special > Transpose.
Turns a vertical list into a horizontal list and visa versa.
Oh and I should add something about Transpose....
Let's say for example that you wanted to produce a cross rate chart that could be read either horizontally or or vertically, but your data only presents itself one way.
If you use:
Copy > Paste > Transpose > Skip Blanks
and then paste your data over itself, the data can now be read both ways.
@Jon... Despite your in-experience with twins, your excel cloning tips are way better than mine. Thanks for the useful pointers...
@Eric.. that is cool. You have mixed transpose with skip blanks to do something fun..
@Mahqooi..Thanks for the compliments. I use camtasia studio to make the animations. You can get a trail version from the techsmith website, I suggest you to use it if you think it makes a difference to your learning material.
@Bernard.. thank you 🙂