Excel Unplugged

Boost your Excel efficiency with one key – F5 (Go To and Go To Special)

100614_0931_BoostyourEx19.png

Very often you will find yourself in a position where you wish to either move efficiently through the workbook or you wish to select only cells containing certain kind of data (text, numbers, errors, objects, formulas…). The Go To… command which you get by either pressing F5, Ctrl + G or HOME/Find & Select/Go To… does it both. Let’s take a look at each functionality separately.

F5 Go To and Go To Special in Excel

Moving through the Workbook

You probably know that you can move through and even select a range of cells in a workbook by using the Name Box, but there is a catch. If you make a mistake and write something that isn’t a valid cell address, Excel will (without warning) give the cell (or range) that is selected a name. Now this can be very useful but also a source of confusion. Here’s an example.

F5 Go To and Go To Special in Excel

I’m in cell A1 and wish to go to E4. I will write _E4 in the Name Box by mistake and press enter.

F5 Go To and Go To Special in Excel

Although Excel gives no warning, cell A1 was renamed to _E4.

Now I will attempt the same with the Go To… command. So I press F5 and in the “Reference:” field I write _E4.

F5 Go To and Go To Special in Excel

Press enter and something great happens, instead of renaming the active cell Excel warns me that the reference is not valid!

F5 Go To and Go To Special in Excel

So a far more bulletproof method than the Name Box although it takes an extra key to get there :).

Defining or refining your selection (Go To Special)

Now this is pure magic in Excel. I will give four samples that will give you an idea of the usefulness here, but do feel obliged to explore this command further.

Example 1 (select only text):

Let’s say a very “evil” person did this:

F5 Go To and Go To Special in Excel

In a few cells a plain number 12 would be inserted, but in a few cells ’12 would be inserted. So a number 12 but as text.

Now at this point you can easily differentiate between the numbers and text but then the “evil” part 🙂

F5 Go To and Go To Special in Excel

First we get rid of the error signs (green triangle indicators in the left top corner of the cell) by going to FILE/Options/Formulas and clear the check box next to Numbers formatted as text or preceded by an apostrophe.

F5 Go To and Go To Special in Excel

You can still see where the numbers are because of Excels default alignment but now that “evil” person gives all the cells Center alignment.

F5 Go To and Go To Special in Excel

Now this could be a problem. We know some cells contain text, but it’s impossible to tell which ones. This is where Go To special comes in.

You can start by selecting all the data or by just standing in one cell. Then press F5 and in the Go To window select the “Special” button at the bottom left corner.

F5 Go To and Go To Special in Excel

And here it is, one of the greatest Excel windows. As we wish to select the cells containing text, we will choose Constants (since all the data was inserted as souch, but we could also select formulas if we had calculations in those cells) and then only leave the check box next to text selected.

F5 Go To and Go To Special in Excel

And voila, eternal happiness 🙂

 

F5 Go To and Go To Special in Excel

You can chose only cells with Errors in the same manner or only Numbers… Definitely worth exploring.

Example 2 (select visible cells only):

I used the same data as before but have hidden some rows (all that were in the way of true Fibonacci fans 🙂 ).

F5 Go To and Go To Special in Excel

Now I wish to select the visible cells in column A to copy them to another sheet. So I select those cells by dragging from A1 to A13 and press Ctrl + C and then Ctrl + V n another Sheet.

F5 Go To and Go To Special in Excel

Oops?!? Weel I guess it can happen to anybody but how can we copy only visible cells? Since it’s impossible to guess I’m gonna tell you. It’s F5! So after you select the initial cells, you press F5 and press the Special button and select Visible Cells Only.

F5 Go To and Go To Special in Excel

This gives you something comepletely different which you can see right away, but even better after you press Ctrl + C and paste the data.

F5 Go To and Go To Special in Excel

The difference (like the Chinese wall) can be seen from the moon 🙂

Example 3 (select objects):

Ever tried to copy a web page content to Excel? Let’s demonstrate with my daily stats of visits by country. After selecting all this data, I copy it to Excel.

F5 Go To and Go To Special in Excel

This is what I get in Excel.

F5 Go To and Go To Special in Excel

The data is OK, but I also got a number of flags that are only covering up the country names. So what I would like is to delete all the flags but to do that, I must first find the best way to select them all. You guessed it, it’s F5. So either while selecting the range of cells where the flags are or by just standing in one cell, we press F5, select Special and then Objects.

F5 Go To and Go To Special in Excel

And we get

F5 Go To and Go To Special in Excel

And just by pressing Delete, all the flags are gone.

F5 Go To and Go To Special in Excel

As simple as that!

Example 4 (select blank cells):

F5 Go To and Go To Special in Excel

This is the only example where you will want to start with the selection and then pressing F5 and choosing Special.

F5 Go To and Go To Special in Excel

You select Blanks, press OK and you get

This combined with the Ctrl + Enter is truly a powerful weapon in Excel. Here is a sample of this and Ctrl + Enter in Action.

Now if you’ve read this article from start to finish I’m sure you got some great ideas where to use this new gained knowledge, and I assure you it will make you far more effective in Excel!

Comments 8

  1. MF says:

    Can’t agree more that Go To Special is an “under-used” but really powerful function in Excel.

  2. andrew chowa says:

    you are great leaving a trail of excitement in exploring excel great man

  3. Gulia says:

    Thanks for tips. However, at the very end of this trick, i cannot manipulate with all the cells appeared as “selected”. i could just only see them and any further click (“Copy-paste” or any other) lead to dissapear of so-called “selected” (tons of in my own spreadsheet) cells. Do you have further instructions on how to manipulate further with cells rather than only enjoy while looking at them 🙂 ?
    Thanks!

    1. Hi,

      if your selection does not contain any renegade single cells, then you’re OK. As long as it is in the same dimensions, you’re OK. Always imagine a Rectangle around those cells and it’s sides must contain every cell selection. Check this image

  4. jack says:

    You wrote “First we get rid of the error signs (green triangle indicators in the left top corner of the cell) by going to FILE/Options/Formulas and clear the check box next to Numbers formatted as text or preceded by an apostrophe.”
    I ask why ditch the easy method to fix the error? Simply select the affected cells, click on the down arrow by the exclamation mark and click on “Convert to Number”.

    1. Sure thing Jack,as a one time thing it’s much faster, but it’s something you repeat over and over whereas the setting will hold from that point on and that was the purpose of the command.

  5. Jesse N says:

    OK Gasper! Have a question! 🙂

    Is there any way to use the Go To command to select all cells less than zero?

    I can conditionally format them, but to select them all to modify them or remove them, I have to CTRL + CLICK each one… I offset the time by using a OR function and filtering… but I feel like I’m just missing something! shouldn’t I be able to do this somehow?

    Thanks!

    1. Hi,
      the conditional formatting can’t help you here. The GoTo Special command has a Conditional formatting option but it will not select the cells where the condition for the formatting is met but rather all cells where the conditional formatting is applied even if the condition itself is not met. VBA is the best approach for the desired selection.

Leave a Reply

Your email address will not be published. Required fields are marked *

"
%d bloggers like this: