One that I spotted recently has a nice mix of tips, so I thought I’d share it (see link at the bottom). It’s a free electronic book called “95 Excel Tips & Tricks” by Purna Duggirala.

Some of the tips that I liked were:

• Debugging formulae – number 16 on page 11Select the portion of formula and press F9 to see the result of that portion. Really useful in complicated formulae.
• Use Choose() instead of lengthy nested if() statements – number 6 on page 13Very useful when you have more than two outcomes for a given condition.
• Saving data filters and other settings – Number 8 on page 7. The Custom View feature that is outlined here allows you to set up a worksheet as you like (hide columns, adjust the row height, zoom into a section, etc), save it as a Custom View and then go back to that view whenever you like. One use is if you have to prepare a particular sheet for printing, but you will always want to hide some columns in the printed version – hide the columns and save it as a Custom View. You can unhide the columns and work away on the spreadsheet, but always have a quick way back to the view you want to print.Another use is with filters – if you have one filtered view that you use a lot (where you have applied different filters to several columns). It’s great to have all those filters available at the click of a button rather than having to try to remember and then apply them all.Note that if one or more sheets in the workbook contains an Excel List, the Custom View button is disabled for the entire workbook (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command).
• What is the date after 30 working days from now? – number 14 on page 16. Can be useful if you are printing invoices or if you are creating project plans.
• Filter unique items on a list – number 2 on page 18Lots of uses for this one. Good if you have a list of names, addresses and actions, but you want to get a list of unique names and addresses for a mail merge.
• Excel Keyboard Shortcuts on Page 6. There are only a few in the book, but I liked the way they were presented. There are other good sites out there with shortcuts such as David McRitchie’s comprehensive list. There’s also Microsoft’s online help.Two shortcuts from the list that I use a lot are the Ctrl+$ which currency formats the selected cells and Alt+= which auto sums the selected cells and places the formula in the cell beneath.
• Selecting all formulae – number 9 on page 7Sometimes you would like to know where the formulae are in a sheet. This is one method – there are a few more.
• 6 Steps for better chart formatting – page 9The default chart format in Excel doesn’t lead to great looking charts. You can mess around with them for hours trying to get them looking ok. It’s good to have a short checklist of steps that work reasonably well – it should save you wasting time every time you create a chart. Use this as a basis.
• Use Convert() to find out how many lbs in a kg – number 11 on page 15. Useful for merging spreadsheets where different measurements have been used for example. The Convert() function “can convert many things to so many other things”.
• Change the colour of various sheet name tabs – number 14 on page 23. Great for big spreadsheets that you use a lot.

You can sign up for the book here.

Cathal