The beauty of copy and paste...

The beauty of copy and paste

Copying and pasting has saved me so much time on so many occasions.  Assuming you’ve already done something once, why repeat it again?  The more efficiently you can get something done, the more your time frees up for other tasks.  Most people are probably familiar with copying cells of data.  I will try to expand on that same concept by taking it to another level.

Copying entire rows and columns

Suppose you already have a complicated spreadsheet with working formulas, cell references, and various lookups.  See below.  Bill rates, hours, and cost for each contractor are broken up quarterly.  Quarterly Cost of contractor = quarterly bill rate * quarterly hours.  Then suppose you need to expand on that spreadsheet to accommodate quarter 3. 

Instead of inserting blank columns and rewriting the formulas, just copy and paste columns E, H, and J, one at a time.  To copy, highlight the entire column, copy, then paste as a new column. (CTRL + Space, CTRL + C, CTRL + SHIFT + “+”).  Then rename the newly created columns to reflect Q3 and update the data for Q3 Bill Rate and Q3 Hours.  The formulas for Q1 Cost and Q2 Cost should not be impacted by the copy and paste.  The formulas for Q3 Cost should already reflect Q3 Bill Rate * Q3 Hours.  When you added the new columns, since no cells were locked, Excel knows to shift over the formula for Q3 Cost.

Now suppose you want to add a new contractor to the list.  No need to start off with a blank row and rewrite all the formulas.  Just simply highlight one of the rows and re-paste it (SHIFT + Space, CTRL + C, CTRL + SHIFT + ”+”).  Now you should have a duplicate row.  Just replace the non-formulaic data in that duplicate row.  The costs should automatically update for that new person since all the formulas were kept intact.

Although this is a very simple example, it shows how much time you can save by just copying and pasting entire rows and columns, especially if you have a spreadsheet saturated with very similar formulas.

Copying Formatting

Sometimes, you may spend a lot of time formatting an exhibit to get it to look just right.  Then you realize that you have to do the same formatting to 10 other exhibits for consistency.  In that case, you can simply copy the formatting from the original exhibit over to the other exhibits.  If the exhibits are exactly the same size, then you can actually copy the entire exhibit, and paste formatting.  However, if the exhibits are different sizes, you may have to copy the formatting in several phases.

Suppose this is your original exhibit, and you want to transfer this formatting over to other exhibits.  Note that this exhibit contains conditional formatting to set the alternate row shading.

Suppose this is your unformatted exhibit.

What you can do is copy the formatting for the title, headings, and data.  First, select the row that you want to copy the formatting from then copy (CTRL + C).  Next, highlight the row where you want to transfer the formatting to.  Then paste special > formats (ALT, E, S, T, ENTER).  Using paste special > formats, you can quickly copy the look and feel of one exhibit to another exhibit.

Copying entire sheets

Suppose in the previous example, you needed the “Contractor Information” exhibit 12 times, but with a slight variation each time.  For example, maybe you need that same exhibit, but on a monthly basis from January to December.  And suppose you also need it on separate tabs so that other users can easily identify what exhibit they are looking at.  Just simply copy and paste the tab 10 times by right clicking on the tab, then “Move or Copy”, check “Create a Copy”, then OK.  In each copy, just make the tweaks necessary so that it reflects a specific month’s data.  The formatting should already be done.

Click here to download a spreadsheet with the above examples.