Excel Unplugged

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

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!

Tech Tip : Now get an easy one click access to all your essential Office 365 documents, data and windows applications on remotely accessible citrix xendesktop from CloudDesktopOnline with cheapest xendesktop pricing. To know more about Hosted SharePoint, Exchange and Office 365 visit www.Apps4Rent.com.