Lesson 9: Raising the curtain on F5




So, after having talked about "The Prestige" in your previous lesson featuring the F9 button, are you now referring to the Muppet Show?

Eh? I don't follow.

Well, I'm sure you can sing along to the theme song of the Muppets:

      It's time to put on makeup
      It's time to dress up right
      It's time to raise the curtain on the Muppet Show tonight

Haha! No, today there'll be no link between the course and any movies or TV shows.

There goes the only interesting topic in what you were teaching...

Nobody obliges you to stay.


F5: The basics - Excel Navigation

It's a little exaggerated, but F5 will tremendously increase your efficiency when using Excel. In a nutshell, the F5 key lets you jump to specific cells or named ranges.

That's it? I really am glad I stayed...

No, that isn't all it can do. Let me explain. Let's press the F5 key in any worksheet. The "Go To" dialog box appears:


If you type a cell reference like "H22" or the name of a range in the reference field of the "Go To" dialog box, the F5 key will take you just there. In this silly example below, I just named cell A6 "Testing_F5". When typing this name in the reference field, the F5 function takes me to cell A6.


To be honest, I've never seen anyone using F5 for that specific purpose. Luckily, there're more interesting tricks in the big F5 book.

F5: The specials - Data selection

Let's have a look at the table below. In this spreadsheet, some values are hardcoded while some others are calculated. You would like for instance to highlight all cells containing a formula. F5 to the rescue!

Hold on, what is the link with a "Go To" functionality and F5 taking me somewhere in a workbook as you explained earlier?

Good point. This is a special F5 functionality. See it as Excel "bringing" you or "jumping" to all cells containing formulas. And as it is a special function, this time you need to press the "Special" button in the F5 dialog box. Life can be easy isn't it?


In the new dialog box that appears, select formulas:


And, Sim Sala Bim, F5 selected all fields with formulas for you:


You could then for instance decide to give these fields a special highlight or color, or even lock them, whatever:


The added value of the above is already more important. Being for instance a senior accountant, you can protect your financial models from being updated by someone with whom you share your data. And this without bearing the risk of forgetting some cells that might have escaped your attention.

But that isn't all it can do.

I must admit, I wasn't enthusiast at all but I'm slowly becoming a fan.

F5: Advanced data selection

Let's dig a little deeper and have a look at the below dataset.


Nice. What do these figures tell us?

It doesn't really matter. Suppose they represent monthly sales figures. Now, your evil data engineer that provided you with the data - an amateur - didn't get rid of annoying empty rows. You, of course, want them out of the way.

I would select them one by one and delete them.

Perfect solution in this case. But what if you have 1.000 empty rows? That would be one hell of a job. Luckily the F5 function can help you here as well. Choose the "Go To Special" option again and highlight the "Blanks" option:


Abracadabra! All blank cells have been selected and I can just right click to delete them:


Let's go one step further. Suppose the below table is your dataset:


For some reason, you would like to copy all rows where your Sales guys and girls did not sell anything in June. Well, call F5 for help again. Simply select your data table, filter the June column on "0", press F5 and select "Visible cells only" in the Go To Specials options.

Hocus Pocus, only the visible cells are selected and are ready to be copied and pasted:


Coming back to the Muppets:

      It's time to put on makeup
      It's time to dress up right
      It's time to be efficient with the F5 key tonight


Good to know, and to remember...

  • Instead of pressing F5 you might prefer using CTRL+G;

  • Explore all "Go To Special" options. There are parameters to select cells based on difference in values, conditional formatting etc;

  • Limit the F5 scope by first selecting the range you want the "Go To Special" function to be applied to;

  • Extra trick: Suppose you want all empty cells in a particular range to be filled with the same value. How would you proceed? Well, as you learned in this lesson, the F5 key will help you selecting all empty cells. Next, and without clicking as clicking will make you lose the whole selection, type in the standard value you would like to be assigned to each of the empty cells. Now press Ctrl+Enter. Bibbidi-Bobbidi-Boo that value now resides in all selected cells. Hey, this even works with formulas!

We appreciate your feedback!


About Cintellis

Let us surprise you with how much more you can achieve by using Excel and VBA based products.

Are you looking for an on the level, reliable and trustworthy company that will surprise you with how much more you can achieve by using Excel and VBA based products? Learn more about our philosophy, products, services or contact us for more info.



You are not established in Belgium? That's not an issue: did you know that Cintellis provides remote services to customers located all over the world?



About Numis

Flexible, cheap and very short implementation time, discover Numis, our Excel based Cash Management and Management Accounting Software and experience a totally new way to analyse and report on your cash positions.

At Numis, we help you to get more out of your financial data than you thought possible. From enhanced reporting functionalities and detailed analysis to providing enterprise budgeting and forecasting solutions, we will help you to achieve your business objectives. When using Numis, you can share your analysis and strategy with your business partners, suppliers or employees as needed.

Numis is a product from Cintellis bvba & FS&P bvba.

CONTACT Us

You can contact us by phone, mail or by filling out the form below.

Corporate address:
Alsembergsesteenweg 588
1653 Dworp
Belgium

Phone Numbers:
+32 (0)471 65 19 66

E-Mail: info@cintellis.com