10 worst Microsoft Excel practices

I have met a lot of people who know they ‘know’ Excel, even sometimes stating they are experts of some sort. Well, they were not, and I could usually spot it because they make one of the following ‘mistakes’. Of course they get the job done, but in the long run they usually got themselves into trouble with it. I used to be such a person myself, until I became a full time Excel and VBA trainer.

So here it goes, starting with the worst:

1. Using tables

Tables (like years on the left column months on the top row) are nice for a visual report, but the data cannot be used in any other way. If you’ve read my recent article about the TableToListConverter, you know why. Excel is an excellent analyzer of data, as long as this data is organized in rows (lists), much like a database.

2. Using cell references

Don’t you hate those formulas like =C1*B6-H3+F5?. They’re horrible to work with. Wouldn’t it be easier to see =Rate*Hours-Discount+ExtraCost?
Well, that’s possible. Just click on the C1 in the top left corner and type the name ‘Rate’, then press Enter. From then on, C1 has an alias of ‘Rate’, and you can use it in formulas.

3. Using too many worksheets

Again too many people use too many worksheet. As above in #1, they create Worksheets named ‘jan’, ‘feb’, ‘mar’, ‘apr’ and so on. Later on it will become very difficult to get totals or filter this data. Forget about multiple worksheets and put everything in one sheet, as long as the data belongs together of course.

4. Using color to indicate meta data

Using a color to indicate something is great if you are the only person using the Excel file. That is hardly ever the case. You would have to explain: ‘well, red means this row is to be deleted, yellow means it’s not checked, and green means it is checked’. That’s nice, thank you, but the sheet looks butt ugly, and if I print this on black and white all that information is suddenly lost.

The correct way to indicate this information is to add another column or more columns next to your data. Give them descriptive heading values, like ‘deleted, checked’. Notice yellow and green can simply be combined by putting yes or no in the ‘checked’ column.

And did you know about the conditional formatting feature? Based on a value or even a formula you can color or format a cell. But, make sure to only do that for formatting that doesn’t relate to valuable information you should have visible in your sheet. It’s a good feature though for e.g. zebra-stripes (odd rows colored different from even rows).

5. Copy & Paste

Using copy and paste is fine, as long as you don’t copy when you don’t need to. A simple cell reference will point to value without needing a copy. So if you have a price in a C1 in one worksheet, and need it somewhere else, use =C1, or of course =Rate (see #2 in this list). Another way, which even works for many cells, is go to ‘Edit > Paste Special > Paste Link’.

6. Bad formatting

Excel is good at formatting data automatically, if you insert it in the ‘right’ way. E.g. try typing ‘1-1′ and instead of ‘0′ you will get ‘1-jan’ depending on your date format. So the value is automatically converted to a date value. If you would type ‘January 1, 2009′, most likely Excel won’t recognize this and leave it as a text value. You cannot calculate with text values, but you can with Date values. So if A1 has ‘1-jan’, and B1 has =A1+1 then B1 will become ‘2-jan’.

7. Too complex formulas

Of course, some formulas actually are quite complex, there’s no doubt about it. But there’s no need to make them look complex. Split your formulas in multiple parts, and use named ranges. So instead of =Rate * Hours - Disount + ExtraCost you can use =Rate * Hours in the Amount field and =Amount - Discount + ExtraCost in the Total field.

8. Empty rows and columns

Empty rows are inserted to ‘make a sheet look nice’. However, by splitting e.g. January data and February data this way, Excel will assume you are dealing with two lists, not with one. The totals underneath using autosum will therefor work only on one of these sets. If you wish to have a bit more space, just drag the row height handle, or use Format > Row > Height to set a specific height.

9. Formatting for print

If you enlarge a font to make it look bigger on paper, you are making a mistake. Do it using File > Page Setup > Adjust to…

You can adjust to any size without changing the font. The same goes for displaying on the screen in fact. Use View > Zoom, or use the percentage dropdown box in the formatting toolbar.

10.  Not using Pivot Tables.

I intentionally name it this way, since there are so many bad ways of analyzing data, getting totals, count, average and more on a set of data. But there’s only one way to do it extremely efficient, powerful and it brings lots more uses with it. Also, it keeps your data separate from your analysis. It’s called a pivot table. Make sure to have a list of data rows with headings, and then choose Data > Pivot Table. Now just click Finish right away instead of following the wizard. You can now drag in column heading names and be presented with a table with totals.

There’s so much you can do with a pivot table I am going to leave it to Microsoft to explain.

A very practical use of Pivot Tables is e.g. to get only unique values for a certain row. Since these are automatically grouped in a pivot table, you can simply copy and paste the list of unique values from there.

Convert Excel Tables To Lists

In a dark past I was an Excel instructor (among other things). I have trained countless people in the art of Excel number wizardry. I have then become a certified Excel VBA specialist, and I must say, in my years being a professional programmer, this is the skill that has set me apart from all other programmers around me. Sure, people can do Regular Expressions… So can I. Sure, people can do Object Orientation. So can I. But what programmer fancies dumb jumbling of data, and programming a language that has the word ‘basic’ in it? Right. Most programmers I knew were Linux bashers (pun intended) that had no idea something good was hidden up the sleeve of Microsoft. But ok, sometimes I was able to show them some awesome things, and they would instantly recognize that their world view (Excelll is for end-users) was a grave mistake.

What fun it was to teach people Excel, and more so, VBA! To me, it’s the tool of all tools, and it can greatly help anyone who ever works with data (ehm, anyone). So in this first part of a long long series (I hope) I will show you, the humble ignorant user how to convert an Excel table to a list.

Why? Many times I have gone to companies and helped them with some particular problem. Usually it started out with an analyst/marketer/ceo showing me a bunch of data. The data was always presented as a table, with column headings and row headings, with the data in the middle. That seems like a nice way to present data, yes, it is in fact. The first thing I would do is then convert this table to an ugly list.

So why would you convert it to a list?

Because Excel is in love with lists. Excel craves lists, it’s like Access’ little brother, but it can speak five languages and juggle 4 balls. It’s no database tool (maximum of 65535 rows, 1M in Excel 2007)… but it can transform any list into a deep, deep analysis.

The way this analysis is done later on is with Pivot Tables. I wrote those capitals on purpose. Pivot Tables are so powerful that you can basically give it any data list and it can tell you what’s missing, what’s wrong, what’s unique, what’s the total, what’s the average, you name it. But more on that later on.

Let’s convert!

Warning: code ahead…

A table consists of three parts:

1. The row headings (left)
2. The column headings (top)
3. The data (center)

We will loop through the data cell by cell, and create a row in a new list for each. That’s the basic idea (pun intended).

Before we start, we check some preconditions. We have to make sure that we are inside a set of data, formed into a table. All we do is just check if we have at least two rows and two columns (not the ultimate, but it works).

Sub TableToList() 
    If ActiveCell.CurrentRegion.Rows.Count < 2 Then
        Exit Sub
    End If
    If ActiveCell.CurrentRegion.Columns.Count < 2 Then
        Exit Sub
    End If

Then we will need some variables to refer to the various sections of the table

    Dim table As Range
    Dim rngColHead As Range
    Dim rngRowHead As Range
    Dim rngData As Range
    Dim rngCurrCell As Range

Next. we will need some variables for the data itself

    Dim rowVal As Variant
    Dim colVal As Variant
    Dim val As Variant

Now, we will start pointing our variables to the data, row headings and column headings, like so

    Set table = ActiveCell.CurrentRegion
    Set rngColHead = table.Rows(1)
    Set rngRowHead = table.Columns(1)
    Set rngData = table.Offset(1, 1)
    Set rngData = rngData.Resize(rngData.Rows.Count - 1, rngData.Columns.Count - 1)

Note that “currentregion” is a handy tool that expands any cell into a surrounding of non-empty cells. So this way your selected cell could be anywhere inside the table when you run the macro. The data part is a bit harder, line 4 and 5 together shift and resize the original table to form the right bottom part, where all the data resides.

    Dim shList As Worksheet
    Set shList = ThisWorkbook.Worksheets.Add

Next, we create a new sheet in the workbook, to hold the list.

    ActiveCell.Value = "Row#"
    ActiveCell.Offset(0, 1).Value = "RowValue"
    ActiveCell.Offset(0, 2).Value = "ColValue"
    ActiveCell.Offset(0, 3).Value = "Data"
    ActiveCell.Offset(1, 0).Select

In this sheet, we create a first row, “manually”, where we name the column headings for our list. These column headings are very important for sorting, analysis, pivot tables, export and such. The last statement instantly moves the current cell selection one row down. Notice we’re inserting a special column for Row Number. This is not always necessary, but it doesn’t hurt, and it helps you to always be able to restore the original order of the list.

Now it’s time for the actual grunt work, looping through the table

    Dim n As Long
    For Each rngCurrCell In rngData
        colVal = rngColHead.Cells(rngCurrCell.Column - table.Column + 1)
        rowVal = rngRowHead.Cells(rngCurrCell.Row - table.Row + 1)

The “for each rngCurrCell in” is a real beauty in VBA. It just runs through any selection, without worries of overflows, row and column numbers, or calculations. In the loop, we set the value of the current column and row. Note that the rngCurrCell.column and rngCurrCell.row are not relative, it’s the actual number of the column/row. So if the tables starts at C3, the first cel is having column=3 and row=3.

        n = n + 1
        ActiveCell.Value = n

Here, we upped counter ‘n’ and put it in the list.

        ActiveCell.Offset(0, 1).Value = rowVal
        ActiveCell.Offset(0, 2).Value = colVal
        ActiveCell.Offset(0, 3).Value = rngCurrCell.Value
        ActiveCell.Offset(1, 0).Select

We do the same trick again to put a new row in the data list on our new sheet. As you can see this part of code is repeated from the part where we created the header. A small improvement would be to create a function named ‘newRow(n, rv, cv, dv)’ to insert a new row with these values.

    Next
End Sub

Well, that’s it!

When you are done with your code, run it and look at the list. You can try sorting, filtering, analyzing, totalling, and… pivot tables. A pivot table is a dynamically updating table which automatically totals values from a list, and presents them in… a table. Here’s how to re-create the original table from the list:”

1. Choose Data > Pivot Table
2. Choose Finish
3. Drag ColumnValues to the ‘column fields’
4. Drag RowValues to the ‘row fields’
5. Drag Data into ‘Drop data items here’

Voila, the list is back. That is, if it was a list of numbers. Pivot Tables are for numeric operations, if you had text in there, it won’t show anything (anything good).

The file(s)

Download the file here:
Table2List.xla
Note: to install an XLA you need to go to Tools > AddIns and select the file