This post will describe bad habits in Excel to do with cell formatting and formulas and how to avoid them. If you share spreadsheets in a team, the data will only be as strong as the weakest link in the team.

For the first time ever, a Guest Blogpost on DataScopic.net XLCalibre! XLCalibre is a UK-based site that focuses on Excel use by & for Human Resources professionals. There are excellent tips in this post and it’s great to see them summarized in this way. They’re tips/habits that I’ve developed over the years but haven’t thought much about … until now.

If you’re anything like me, you’ll have been in any number of jobs that require you to do some data entry in Excel from time to time. Whether it’s recording addresses and phone numbers of leads in a sales team, or updating a new starter list in HR, putting stuff into Excel is a very common task. If the data is recorded incorrectly it can cause problems, and it only takes one person to spoil the fun for everyone. So this is a list of the seven things that everyone who touches that spreadsheet should know:

1. No blank cells

If you have a field that you don’t have data for, instead of leaving it blank, put something like “N/A” instead. And put the same thing every time so that if you try to sort or filter later you won’t have loads of different pointless values (like TBC, None, Pending, Unknown, Don’t know, etc etc). Why not just leave them all blank? Several reasons:

i) When you fill down a formula it will stop at the blank row, which may cause errors or at least slow you down

ii) If you navigate around Excel using CTRL and the arrow keys, you will be stopped at each blank

iii) If you apply autofilters when there is a blank row, you will probably cut off the section of your table below the blank row

iv) You know that the cell is an intentional blank, not an accidental deletion

2. Don’t select an entire row or column when you change formatting

If you’re in the habit of formatting the entire row or column in something like bright yellow or bold to make it stand out, you’re jeopardising the speed of your worksheet. Only format the cells that you need to.

It’s worth remembering this even if you don’t do it yourself. If I get a spreadsheet that’s moving at a snail’s pace, this is one of the first things I’ll check. I’ll highlight every cell that’s not being used and Clear All (Alt+HEA in Excel 2007 or 2010).

3. Number formatting

You want your number formatting to be consistent and correct – and notably this includes dates.

Excel stores dates as numbers, but you can set the date format that it is shown in (41275 could be 1 January 2013, or 01/01/2013 or 1/1/13 etc etc). If you haven’t set a specific date format, Excel will try to ‘guess’ what format you want by how you enter it. Apart from the fact that lots of different date formats looks messy, it can cause problems if you want to sort or filter. For example, when I type in “01012013” in general format, it converts it to date format, but shows “17 October 4670”, which is clearly not what I had in mind. In a large table you may not spot this and filter it out unintentionally. So, for columns that will contain dates, format them first (CTRL+1 for the Format Cells dialogue box then >Number>Date). You could even use data validation (more on that later) so that only dates can be entered.

4. Don’t merge cells

There are rare occasions when merging cells are appropriate, and a data table is not one of them. It’s quite common when you export a report to Excel from another system that there are several merged cells at the top or bottom of the spreadsheet. Often when I get one of these, or inherit someone else’s spreadsheet I check for and remove all merged cells (check out number 2 on my top 5 keyboard shortcuts).

Why shouldn’t you merge cells? It plays havoc with sorting and filtering. You can’t drag down formulas through cells that are merged / unmerged differently. It affects keyboard shortcuts to select an entire column/row. They can’t be used in Table functionality introduced in Excel 2007. They’re a pain when dealing with macros. There are other reasons, but let’s focus on a decent alternative – Center Across Selection. Highlight the cells in question, and bring up the Format Cells dialogue box (my preferred way is the keyboard shortcut CTRL+1):

Select the alignment tab, and select from the dropdown as shown above. Hopefully that should have the desired effect, but with the problems.

5. Keep formulas consistent

If you’ve entered a formula in Excel it will show the result. So if you have a column of formulas, you will only see the results of those formulas, and if any of those formulas are different to the others, you wouldn’t necessarily notice it straight away. This means if someone else picks up your spreadsheet, those might not know what you’ve done and take for granted that the formulas are all the same, when actually they are not. This kind of thing can lead to big mistakes.

So how can you tell quickly if the formulas are all the same or not?

i. If a formula is different to those around it, Excel puts a little green triangle in the top left corner of the cell telling you its and Inconsistent Formula:

ii. Another way is Show Formulas, shown on the ribbon here:

My preferred keyboard shortcut is CTRL+` (on the top left of your keyboard, with tilde ~).

iii. One more way is Error Checking, immediately below Show Formulas (or Alt MKK for short). It’ll take you to the inconsistent formulas one at a time, and let you work through them with this dialogue box:

Of course, if you’re making the spreadsheet in the first place, the best way to avoid inconsistent formulas is to not create them in the first place, and either make one formula that fits all possibilities, or have another column to sort your exceptions. To be clear, you should not mix values (i.e. non-formulas) with formulas either.

6. Each piece of data gets its own field

This crosses over a bit with number 5, but that is a technical principle about how you arrive at the data, whereas this is about the meaning, purpose and segmentation of the data. Let’s use a common example – names. You might think that having Name as a single field is fine, let’s say it’s Mr John Smith. Seems all good and simple right? Wrong. What if you’re writing a formal letter and you want to put Dear Mr Smith. Suddenly you’ll need to use formulas or Text to Columns (Alt AE) to separate the data. You might as well start off with separate fields to make it straight forward. Because it can get it a lot more complicated, not everyone is called Mr John Smith. Someone could be called Professor Antonio Rodriguez Dos Cantos Jr, for example. Now when you use formulas to split the names, it’s hard to tell which word(s) are the surname(s), and Text to Columns will only be the first step in wading through data manually to split it correctly. If you have a list with thousands of names this can take a long time.

Of course names are just one example, there many other examples. In salary review spreadsheets I’ve seen column for increase %, but if an employee is getting no increase they’ll put a comment in the same column saying why. But this is completely different information. Now if you try to multiply old salary by this column to show new salary, every line with a comment will be an error (unless you use IFERROR), and you’re starting to rely on the user’s quick understanding of how you’re multi-tasking the column. You should instead have two clearly labelled columns that just do exactly what they say.

7. Use data validation wherever reasonable

Data validation is a way of standardising what kind of data is entered into a cell. Most commonly I use it to create a drop-down list of options, or to ensure that users enter a real date (as mentioned in 3. Number Formatting above).

Data validation is a great tool. It keeps your data neat and tidy and therefore easier to understand, sort and filter, handle in formulas, use for mail merges etc. etc. Let’s use the name example above – Mr John Smith. For his title, someone could enter Mr, mr, Mr., mr., mister, Mister… and who knows what else. We will decide that the standard way of putting this is Mr and make this the only option for this in a list of others like Mrs, Ms, Dr and so on.

The data validation option is on the ribbon under Data (or Alt AVV for short).

And it brings up a dialogue box like this:

Focusing on the drop-down list capability, the above box shows you selecting a list of values (like Mr, Ms, Miss etc) as options in the drop down.

I’ve seen data validation misused before. People have set up a list of options on the same tab as their data just to right and maybe 100 rows down so it’s kind of hidden off in the distance. Then the table will grow so that it is on the same rows as the validation list, and then they will delete a row or two and wonder why the options in the data validation list have disappeared.

I therefore like to put my Source list on a different worksheet. Data validation won’t usually let you do this, so you have to make the source a named range. The quickest way to do this is highlight the list and rename it in the box to the left of the formula bar (where it currently says A1 here):

Now in your data validation options you can just refer to the named range:

Now your validation list is kept separate from your data, and will expand automatically if you add a value. You can manage your named ranges easily by pressing Alt MN.

Good luck! Hopefully this post has stood you in good stead with some basic, but not always common sense, tips for playing well with data. Come visit us at http://xlcalibre.com for more similar tips to advance your Excel learning and get more effective and efficient at using Excel!

Thanks XLCalibre! This opens up new, bright possibilities in the world of Data Management.

Oz@ DataScopic