Top Tips

Other websites offering Excel tips

Top Tips

Introduction

Most websites offering tips in the use of Excel offer help with the functionality of Excel or suggest new functions to try. The tips below are more concerned with the structure and content of spreadsheets, which we think is far more important to the end-result.

Knowing fifty ways to do a particular thing may be OK in itself but if the spreadsheet itself is poorly constructed, it's really rather valueless and as far as I have been able to ascertain, no other site gives the kind of advice offered below.

Email your question to us at help@bossml.com

1. Do use Excel.

Time and again I receive from other people lists or tables that have been assembled using the ‘Table’ function of Microsoft Word. Don’t do it. Word is a great product for word processing but the ‘Table’ function is an abomination. Always use Excel for tabular data and if you need it to be part of a Word document, import the spreadsheet. If you don’t know how to do it, find out. Always use Excel for lists or tables and stay away from Word’s ‘Table’ function.

2. Avoid splitting your data

A data ‘set’ is a collection of records that are similar. They may be your contact list, some accounting information, or a record of meetings, etc. But if they’re all the same, put them in one worksheet.

Too often, people will break data up into a number of worksheets which might be, for example, the months of the year with a sheet for January, one for February, etc. (See right) It might be that a contact list is broken up into different worksheets by region or city. If you pre-select data by one value (i.e. month or region), it becomes near impossible to do any other form of selection or summarising. This is usually done because it makes summarising the data (i.e. monthly totals) easier.

Multiple worksheets makes maintenance of the workbook difficult. Any change to one worksheet has to be replicated accurately across the whole set.

Multiple workbooks of course just make matters worse. It is common for inexperienced users to create one workbook per 'situation', e.g. client. This just adds to the complication of maintenance. In one situation I audited the users worked regularly with about 250 workbooks with one master into which they copied the most important 'bits' so they could monitor the whole thing. Most of their time was spent checking and cross-checking.

Always put one dataset in one workbook/sheet. If you don’t know how to achieve this or summarise/obtain selective totals, find out *.

3. Avoid fixed cell addresses

Hand in hand with the issue above is that of using fixed cell addresses for totals, etc, particularly across worksheets. Excel is pretty good at keeping tabs on changing cell addresses, but if an address gets lost, it can render the whole spreadsheet unusable with the dreaded #REF! appearing where your cell reference used to be.

This is quite different to storing constants (See 12 below) where fixed cell addresses are perfectly OK.

4. Use Columns instead of Headings

People often create worksheets that are sub-divided into groups with, at the top of each group, a row containing a Heading or Description. A sales forecast list, for example, might have a few rows under a Heading “Immediate”, a few more under another heading “Prospects” and a third group headed “Suspects”. I’ve even seen worksheets sub-divided in this way by date or month or using people's names

The problem with such headings is that they render the worksheet virtually unusable – for example, it can’t be sorted and a spreadsheet that can’t be sorted is not actually a spreadsheet. It might as well be produced using Word – it could probably be made to look prettier.

Add another column instead of having such Headings so that each row now has ‘Immediate’, ‘Prospect’, or ‘Suspect’ as appropriate. With no gaps or blank rows, the worksheet can now be used properly.

5. Consolidate data

Let’s say a spreadsheet contains a name and phone number. At a later stage, the need arises to keep the individual’s email address as well. All too often, instead of just adding the email address as a new column on the original spreadsheet, a completely new spreadsheet is created with the name and email addresses. Other data may start to be kept on these people and, typically, a new spreadsheet is created for each. There is now a multiplicity of spreadsheets all containing bits of data relating to one person, and probably all maintained by different users. Before long, it can become near impossible to cross-track any of the data because names will be spelled or formatted differently, etc.

Always try to add new data to existing workbooks rather than create new ones.

6. Don’t replicate data

In the same way as data can be replicated across workbooks, it can also be repeated across worksheets so that more than one worksheet in the same workbook has a column ‘Name’ and as often as not they will not be the same making any form of consolidation difficult if not impossible.

If it is necessary to show, say, a name on more than 1 worksheet, cross reference rather than repeat data.

7. One data Set per worksheet

It is often tempting to put two bits of information on one worksheet, for example, a bit of summary or generic data about something at the top of a page and then an analysis or breakdown of the data (e.g. membership of a club or scheme) further down.

They are different data sets and should be on different worksheets. the two data sets mean the worksheet can't be sorted and if it can't be sorted, it's not a spreadsheet.

Only put one data set on one worksheet.

8. Validate data

As you’re now only going to have each item of data in your workbook once, it might as well be correct, so as far as possible, validate your data on the way in. It’s much easier than correcting it or handling errors later. Again if you don’t know how, find out *

9. Use Dates

If your spreadsheet needs a ‘Date’ column, make sure it is a date. All date formats are not the same. 1 Jan 2010 and 1/1/10 are valid date formats but 1^st Jan 2010 or 01.01.10 are just pieces of text. You can work with proper dates (e.g, calculation of age), but can do nothing with text other than just print it.

Try to understand how Excel stores dates and times. This helps a lot in using them. Essentially, a date is stored as a whole number and a time is the decimal fraction value of the time of day so that 12:00 noon is 0.5 and 6:00pm is 0.75. Once you get used to this it makes working with dates and times so much easier.

If you subtract 1 date from another you get the number of days between them. However you need to be sure your date does not incorporate a time as this will distort your result.

Make sure your dates are what they appear!

10. Write generic formulas

Formulas (although the plural of 'formula' is properly 'formulae', 'formulas' is used here to fit in with Excel) should be written in such a way that they can be dragged to adjacent cells and still work properly. The formula '=C3' dragged to the right would become '=D3' and dragged down would become '=C4' but '=$C3' dragged to the right would remain unchanged as would '=D$3' dragged down.

The '$' sign fixes the row or column according to where used. Fixing the row and column as in '=$C$3' creates a constant. One often comes across formulas with an embedded constant as in '=C3/1.175'. If that constant changes for any reason it can require amendment of lots of cells. Whereas if the formula were written '=C3/$C$2' where C2=1.175, merely changing the value of C2 would amend every cell with a formula that referred to it.

Ideally formulas should be written in this way. I usually refer to dragging formulas like this as 'painting' the workbook. It saves a lot of time and heartache whenever a formula has to be changed.

11. Use Excel's intelligence

Learn how to let Excel make decisions with the 'IF(' function.

A friend had a spreadsheet with some names and dates on. The dates were renewal dates for insurance policies and she told me that each day she would scan the list for any dates that were the current date and then she had a procedure to follow for each such situation.

I showed her that by using the 'IF(' function, Excel could tell her which of the dates matched the current date.

Let's say the list holds birthdays. Column A holds names and column B the birthdays. In column C put the function 'IF($Br=TODAY(),"Happy Birthday","") where 'r' is the current row number. This should display 'Happy Birthday' for anyone whose birthday falls on the current date with the others blank.

12. Give each row a unique value.

Much of the intelligence of Excel relies on being able to find a specific row using search criteria. Often a row will contain a unique value like a reference number, but in many other cases, a unique value has to be created.

This can be done using CONCATENATE() or & which allow values from multiple cells to be strung together as in '=CONCATENATE(C3, D3)' or '=C3 & D3'. you might, for example, combine a date with something else to create a unique value.

If you are using a date value, the best form to use for it is its raw integer form, which can be obtained using TEXT() as in '=TEXT(C3,"00000") where C3 contains the date.

13. Don't embed constants

If you have a value that you use repeatedly in calculations (known as a constant), don't hard-code it into formulas. Instead put it in a cell of its own somewhere in the workbook and refer to that cell.

For example to net the VAT off a price, it is common to use the formula '=A2/1.175' where A2 contains the price and the VAT rate is 17.5%. Often one comes across spreadsheets with such values coded into literally dozens of cells. for years this presented no problem because the VAT rate never changed. We now know better.

To avoid having to go through countless cells manually changing the tax rate, put it in a cell of its own and refer to it. Thus if the VAT Rate is in cell D1 make the formula '=A2/(1+$D$1)' so that when the VAT rate changes, there is only one cell to change.

However, store your constant in a cell that won't change (i.e. in the first row of the worksheet that refers to it, or in a different worksheet (or even workbook) altogether. If you don't know how to do that, find out *

* You could try me!