562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
|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.|
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!
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!
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!
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.
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!
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!
Some other pages relevant to the above blog include:
25 Aytoun Street