Five Tips for Saving Time and Pain with Excel
At the Digita User Conference in Stratford, England on 27-28 April, Simon Hurst from The Knowledge Base presented an hour-long seminar entitled "Everything about Excel". The session included as many tips as he could squeeze in from his latest guide, '100 Ways to Save Time with Microsoft Office'. Here is a short selection of edited highlights.
What is the first thing you should do before using Excel?
OK, once you've turned on the computer, sat down comfortably and equipped yourself with some coffee, the first thing you should do is get hold of a large piece of paper and a nice pencil.
"Some of the spreadsheets I come across tend to lack a bit of design. Generally people approach spreadsheets and start typing all over the place," said Hurst. "Often you get to the right answer, but it's very difficult to design a spreadsheet like this that will look elegant."
If you think about what you want the spreadsheet, it will generally look and work better than one you construct through trial and error.
What does it take to make a really awful spreadsheet?
One conference delegate hated it when people typed in numbers into a spreadsheet rather than using formulae. Hurst confirmed this as a real problem by mentioning that he spotted one recalcitrant partner using a spreadsheet, but he would then add up the figures in a row with a calculator and type the result into the final column.
But what really gets his goat are spreadsheets that look incredibly clever, but are appalling in reality.
"One of my pet hates is when people use spreadsheets when they should be using databases. If you're storing lots of data or doing data input with lots of transactions, use Microsoft Access instead," he said.
Accountants tend to overuse Excel because they're familiar with it, and aren't familiar with Access. "But the person who creates an elaborate spreadsheet database could probably become a world expert in Access in the time it took them to do it in Excel."
Fun with fill handles
Get to know the small, square black blob that appears at the bottom right hand corner of an Excel cell when you select it, Says Hurst. Left-clicking over the blob (which turns into a cross when you're in the right place) allows you to pull commonly occurring lists across a set of rows, for example consecutive days at the top of a column. It works down as well.
You can also use the right mouse button to drag it. Doing so will present you with a menu of options which can sometimes be very useful - for example by month or year, which can be handy if you're working with period ends and want to show the specific dates. NB: if you are using Excel XP, you can drag with the left mouse button as normal, and then use the Smart Tag that appears when you let go to choose the sort of fill you wish to carry out.
There is a list of commonly occurring fill options in the Custom Lists dialogue box (Choose Tools-Options, and then the Custom Lists tab). It's a simple matter to add your own lists to Excel to extend this functionality. Just type them into the appropriate box, or even quicker, click the Import list from cells option and select the range of cells that contains the list you want to turn into a custom list.
Crediting David Carter and other AccountingWEB UK members for the next tip, Simon demonstrated the miracle of the double-click fill.
"When you drag something off the bottom of the screen, you can sometimes inadvertently find yourself down at row 2497. Then you try to go back up to find the end of your data range… It can be jolly irritating," said Hurst.
"If you double click the fill handle, it will automatically fill down to the bottom of your data."
As he demonstrated the trick, there were audible gasps from the audience.
"Good lord!" said one participant. "What are we going to do with all the time we save?"
Take a closer look at Paste Special
Paste special (under the Edit menu) is an unassuming little command that has a multitude of uses. For example, if you've got a sheet with monthly totals calculated by formulae and you want to "fix" them - for example at a period end. You can convert an area from formulas to values by copying it and then pasting it as ‘Values’ to the same location.
Other useful Paste Special options include pasting formats, column widths and even the data validation settings you have included on a spreadsheet.
One useful option that has featured in Any Answers is Transpose, which can take the cells selected from a column, and paste them across the row you select.
Speeding through pivot tables
Using the Northwind.mdb sample data that comes with Access, Hurst took the audience on a break-neck tour of pivot tables that kept the audience on the edge of their seats (with yet more "oohs" and "ahs").
"If you use pivot tables, its worth spending a little time paying attention to the cosmetics," he said, racing around his monthly sales analysis chart and opening up the Advanced Field Settings tab in the Pivot Table Toolbar. Suddenly, it all seemed to go awry as he specified an esoteric sorting option along the lines of "descending order of the sum of the extended sale price".
A stern dialogue box appeared and the spreadsheet pinged in alarm. Hurst shrugged.
"Computer presentations are a bit like Formula One races," he said. "People only come along to see the crashes."
To order a copy of '100 Ways to Save Time with Microsoft Office', visit The Knowledge Base The Knowledge Base. AccountingWEB members can also access his back catalogue and online email support via the Office ProductivITy service.
This article has been reprinted from our sister site AccountingWEB UK.