Excel Time-Saving Tips #2

Excel is everywhere!

 

From students to professionals, anywhere from your local Café to NASA and CERN, Excel is used to crunch numbers, plan events and organise businesses. Excel has a wide and varied audience – but what is certain, is that most users are not taking advantage of some amazing time saving tricks right in front of them!

Follow our series on Excel tips and save yourself some serious time with your spreadsheets, and get those brownie points for being the office’s Excel guru.

 

1.   CTRL+Y to Repeat Inserts

 

Inserting new cells into existing tables, or adding rows and columns in the middle of existing data is a very common task in Excel. Normally, you might find yourself right clicking the range and Insert->Above/Below. This can mean 4 or 5 whole clicks each time you insert! Avoid subsequent menu boxes and clicking by following the easy tip below.

First, perform an insert on a cell, row or column – then select the next place you want to perform this same operation and press Ctrl + Y on your keyboard. This will do the same action again in the new place you selected.

 

 

2.    Freeze Important Rows and Columns

 

When navigating larger, data heavy spreadsheets, it’s easy to get lost the further down or accross you scroll. Suddenly your nicely formatted table has become a mess of numbers with no headings and you can’t read this at a glance.

Excel can freeze in place, rows or columns which give your data meaning, so that when you scroll in the depths of your data, you can still tell what value is which.

 

 

3.    Pasting Data Without Formatting, Hyperlinks or Formulas

 

When bringing in data to your worksheet from external programs or websites, often if you copy and paste this data, you’ll get hyperlinks to the website, odd colours, column widths and fonts you didn’t want. This also applies to copying formula-based cells from other worksheets, which will copy the formula, not the data itself.

To work around this, Excel offers multiple pasting options including “Match Destination Formatting” which will try and blend in to your existing table, or “Paste Values” which pastes only the value of the cell itself.

For example if you had a cell with the formula “=B2” in it, which displayed the value of the adjacent cell as “500“, when copying this to a new table, it will copy “=B2“, which might now refer to an empty cell and you’ll get a blank value or the dreaded “#REF!“. Pasting this using “Paste Values” will retain the value “500” when pasting into the new cell.

Have a look at the example below which illustrates these features:

 

 

 

You can also use these pasting options to “transpose” your data, meaning you can change a column to a row and vice versa with ease.

 

 

Enjoyed this article? Get in touch and let us know!