Quick Tip - Removing Error Displays

When we are writing formulae we are often interested in ensuring that the result does not end in an error. But sometimes we also want to remove any errors being displayed in the formula result.

We can do this by combining the IF and ISERROR functions.

For example, the following formula will result in a blank if cell B1 has a value of zero in it:

=IF(ISERROR(A1/B1),"",A1/B1)

An alternative to the ISERROR function is the ISERR function.

Similar to the ISERROR function, the ISERR function will remove most error messages (such as #VALUE!, #REF!, #NUM!, #NAME?) but it will still display the #N/A error message.

Quick Tip - Auto Drop-downs

Sometimes we can't escape having to manually populate cells in a particular column.

The text auto-complete function sometimes picks-up on what we want half-way through what we're typing, and at other times it just annoys us!

But next time you're preparing a list in a column with repeat entries above it, try the following combination of keys: Alt + Down Arrow

It will give you a list of relevant items you've already entered above to choose from, and as a bonus it's alphabetically-ordered too:

Quick Tip - Default Worksheets

When we create a new workbook in Excel, it always defaults to offering us 3 blank worksheets. Often one of the first things we then do is go in and delete worksheets 2 & 3 !

We can change the default number of worksheets given to us in a new workbook as follows: select File/Options/General and then amend the setting for sheets in new workbook...

Quick Tip - Skipping through Worksheets

Accountants and analysts are often working on files containing many worksheets. And at times it can become a little tiresome to navigate between individual worksheets using only the mouse or mouse pad.

So try using the following keystroke shortcuts instead: Ctrl + PgUp, Ctrl + PgDn

Say we were initially looking at Sheet 7 in our workbook.

Ctrl + PgUp will move you to the next worksheet immediately to the left of the one you are on...

...whilst Ctrl + PgDn will move you to the immediate right:

Quick Tip - Format Painter

Copying formats with the 'Format Painter' icon is a great time-saver.

It's that little icon way over on the top left of your screen in Excel 2010 on the Home tab, with the picture of a paintbrush next to it:

I even like the paintbrush idea - it's sort of saying "take a bit of whatever paint we've used over here and then dab it over there for the same effect". Brilliant.

But did you know that you can repeat-apply your chosen format to any number of cells or areas in your worksheet (or entire workbook in fact) simply by firstly double-clicking the icon?

Try it for yourself - but remember to click the icon again or hit 'Escape' to stop the effect, or you could find yourself clicking 'Undo' for some time...

Quick Tip - Number Summaries

There are of course many ways of summing numbers and data in Excel - it is a spreadsheet package after all.

But sometimes it's useful to have a way of quickly seeing what the total is for a range of cells with numbers in them, or the average, or maybe how many cells are populated in the range:

If there's nothing else on this worksheet, then we can find all the information we need in just one click. Click on the top left hand area of the grid (between the column A header and row number 1):

Have a look at the Status Bar down the bottom right of the screen - it shows you all the data you were after, and not an =SUM or Autosum or other formula in sight. Simple!

Quick Tip - Hiding & Unhiding Sheets

You can easily hide and unhide any chosen worksheet(s) in a workbook in Excel.

Simply right-click your chosen worksheet tab and select 'Hide':

To unhide, again right-click on any worksheet tab and then select the sheet you want to unhide: