Top 5 Under-Appreciated Excel Functions

They say you never forget your first time. It’s true. My first experience of Microsoft Excel is burned forever into my mind. I was in my first graduate job, fresh from a humanities degree. I’d written plenty of essays, but few of these required any data analysis.

 

Then my boss sent me a gigantic spreadsheet, covered with indecipherable numbers, and asked me to pull some useful information from it. I didn’t know what to do or where to start. Blind panic!

 

I felt awkward asking for help from colleagues whom I barely knew, especially for something so basic to them. In my desperation I went on Amazon and bought a DVD (remember those?) on learning Excel.

 

Fast forward a few years and I’m now MD in an Excel training company that has developed a viral list of the Definitive 100 Most Useful Excel Tips. We pooled our collective Excel knowledge, data from our learners, and knowledge from 30+ leading Excel blogs to put together the ultimate 100.

 

But 100 is a lot. This got me thinking – of the 100 tips, which 5 do I use the most? Not flashy things like PivotTables or VLOOKUP, but things that make my life in Excel easier and help me to be more productive.

 

My top 5 are:

 

  1. Paste Special
  2. Go To Special
  3. Flash Fill
  4. Use of Apostrophe
  5. F2

 

1. Paste Special

 

Grabbing (ie copying) data from one cell and pasting it into another is one of the most common actions in Excel. But there’s a lot that gets copied in a regular grab (formatting, value, formula, comments, etc) and sometimes you don’t want to copy all of it. That’s where Paste Special comes in.

 

To access Paste Special, go to the toolbar (top left on the Home tab under Clipboard), right click or (my favourite), use the shortcut Ctrl-Alt-V.

 

Once the Paste Special box is open you have loads of options to choose from. Here are my top three:

  1. Paste Values
  2. Paste Values and Number Formats
  3. Transpose

Pasting never looked so good

 

Paste Values pastes the value of the cell you have copied instead of copying the formula. By default Excel copies over a formula, but sometimes you just want the end result. Paste Values does that for you.

The shortcut for Paste Values is Ctrl-Alt-V-V (then hit enter).

 

Paste Values and Number Formats is the same as Paste Values but this time it brings over the format of the the original cell. This is particularly useful when you are copying over dates or a currency but you don’t want to copy the formula from the original cell.

The shortcut for Paste Values and Number Formats is Ctrl-Alt-V-U (then hit enter).

 

Transpose allows you to flip rows and columns around in seconds. Turn a row of numbers vertical or vice-versa by simply copying and then using Paste Special – Transpose.

The shortcut for Transpose is Ctrl-Alt-V-E (then hit enter).

 

2. Go To Special

 

Like Paste Special, this hidden gem opens up a treasure trove of Excel goodies. Go To Special is located on the Home ribbon in the Editing section under “Find & Select” or you can use the shortcut – Ctrl-G followed by Alt-S.

 

Go To Special allows you to select types of cell en-mass. You can use this function to select cells that have comments, formulas, numbers, text, errors and many more. Once selected you can then apply formatting, use it to find errors or highlight certain things.

 

Your options on Go To Special

 

To give a practical example, we’ll focus on Go To Special, Blanks.

 

Go To Special Blanks allows you to select all the blank cells in a selected area. Say you have a huge column of data with intermittent blank cells that you want to format a different colour or delete. You’d select that column, Go To Special, select Blanks and all the empty cells would be highlighted. Right click on one and select “Delete” and the blank cells will disappear.

The shortcut for Go To Special, Blanks is Ctrl-G followed by Alt-S-K (then just hit enter).

 

3. Flash Fill

 

Excel developed a mind of its own in 2013. Say you have two columns of names (first and last) and you want to put them together. Type it in manually for the first row and Excel will work out what you mean and do it for the rest. That’s Flash Fill, or Excel black magic as some people say…

Flash Fill in action

 

Please be aware that this functionality was only introduced in Excel 2013, so is included in Excel 2013 and 2016. If it’s not working for you in those versions then make sure it is switched on in Advanced Options (File > Options > Advanced). Or you can activate it manually from the Data tab or by using Ctrl-E.

 

4. Use of Apostrophe

 

Sometimes you type what looks to Excel like the start of a formula. If the first character is +, -, = etc, Excel treats the cell differently and starts looking for cells to refer to. This can be disorienting. Alternatively you might be listing numbers that start with zero, like a stock keeping unit – 0000345 – etc. In this case Excel will remove all the leading zeros giving you 345. How annoying.

 

That’s where our next Excel hidden gem comes in. The apostrophe (‘) is Excel’s solution to displaying data that Excel would otherwise treat differently.

 

With and without an apostrophe – see the difference?

 

One word of warning when using the apostrophe in this way: if you are entering a number, Excel will store that number as text so you won’t be able to run sums etc. on it.

 

5. F2

 

The last hidden gem is so simple you’ll be kicking yourself if you didn’t know it. F2 activates the cell you are in for editing. So no more double clicking then setting the cursor to the end of the text. F2 takes you straight there.

 

Author – Adam Lacey, Managing Director – Consumer at Excel with Business.

 

If you are interested in more Excel hacks and hidden gems, why not download our completely free Definitive 100 Most Useful Excel Tips.