Underrated Excel Functionality Which We May Not Use Daily

Excel is incredibly adaptable and these prompt it to the most well-known software available in the market today. This inescapable use is all the more frequently than not prone to prompt circumstances where you need to discover some of the underrated functions used in Excel. In this article, we walk you through the top 5 underrated excel functionality which we may not use daily. Here are the top 5 underrated excel functionality

1. Paste Special

Grabbing (i.e 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 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

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

Paste Values and Number Formats is the same as Paste Values but this time it brings over the format of 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 to format, 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 color 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.

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.