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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

Selecting rectangle 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:

Results of CTRL + A

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:

Hiding cells

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:

Number formats

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:

Custom format

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?

Unhiding worksheets

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:

Showing Properties window

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:

Hiding a sheet

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?

Saving a file

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:

Entering labels

The ' apostrophe will vanish when you enter the text into the cell!

 

Here are some example of labels that you can enter in this way:

Example labels

You can use an apostrophe prefix for any text beginning with a number or punctuation character.

 

 

Hope you learnt something! 

This blog has 0 threads Add post