This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

A different owl's take on the five biggest ooh! moment tips in Excel
This month it's Shaun's turn to nominate the five best short-cut tips in Excel.

Posted by Andy Brown on 17 June 2019

In this blog

1 - Selecting a block of data
2 - Hide cell contents using custom number formats
3 - Very hidden worksheets
4 - A quick way to save a workbook
5 - Trying to enter numbers into a spreadsheet as text

Shaun gives his 5 biggest OOH! tips from Excel

I inadvertently ruffled a few feathers last month with my claim to have published the 5 best Excel ooh! moment tips (the ones which make a training course collectively gasp in amazement, although I may be slightly over-egging the pudding here, not to mention overdoing the old-fashioned idioms).

Other owls were immediately up in arms, so in the interests of fairness I'll give people a chance to reply (Shaun this month; Jenny the next; subsequent months by arrangement).

Here then are Shaun's Excel tips!

1 - Selecting a block of data

You can press Ctrl + A to select a rectangular block of data:

If you have the cell shown selected above (D5), pressing Ctrl + A will select all of the inputs.

Here's what you'd get for the above example:

The cells selected when you press CTRL + A.

If you press Ctrl + A twice in a row, the second keystroke will select every single cell in your worksheet. Try it!

2 - Hide cell contents using custom number formats

Often you'll want to keep the content of cells, but now show them:

Here we want to hide the range names, but not delete them.

Every cell has a number format which divides into up to four parts, separated by semi-colons:

A number format can have components for how to display positive numbers, negative numbers, zeros and text.

If you omit all four parts and just set the number format ;;; it will hide the cell, since there's no format to apply regardless of the type of data it contains:

Choose to change the cells' format, click on the Number tab of the dialog box which appears, go to the Custom option on the left and type in the number format code shown. Your cell contents will vanish!

3 - Very hidden worksheets

How many worksheets are there in this workbook?

2 visible sheets plus 0 to unhide ... making 2 in total?

The correct answer is that you don't know, as some worksheets may be very hidden (so hidden that you don't even know they're there). To hide a worksheet like this you have to go into the VBA code editor (usually used for writing VBA macros). Press Alt + F11 to do this:

Check that you have both the Project Explorer and Properties windows visible (without worrying too much about what these are or do).

You can now select each sheet that you want to conceal (here not surprisingly it's the one called Evil hidden sheet) and change its Visible property:

Set the Visible property of the selected sheet to 2 - xlSheetVeryHidden. The only way to make this visible again will be to return to this VBA window and change this property.

4 - A quick way to save a workbook

Want to save a workbook?

You may have thought that pressing Ctrl + S was the quickest way to save a workbook, but there's an even faster way.

Simply press F12!

I admit that I didn't actually know this. I've just tried it out, and it works in Outlook and Word too, so presumably it's common to all of the main Microsoft applications!

5 - Trying to enter numbers into a spreadsheet as text

Often you'll want to enter numbers or formulae into a spreadsheet, but you don't want Excel to treat them as such. To do this, prefix entries with a ' key: