BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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:
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:
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:
You can use an apostrophe prefix for any text beginning with a number or punctuation character.
Hope you learnt something!