10 worst Microsoft Excel practices

| No TrackBacks

Patrick also pointed me at 10 worst Microsoft Excel practices .from the The Universe Divided blog by Michiel van der Blonk. Michiel identifies problems such as using too many worksheets — one for each month in a financial report, for example — which makes it much harder to analyse the data than if it were all in one sheet.

Michiel's first section warns against using two-dimensional tables, for example years listed in a left-hand column versus month names on a top row, with the data below and to the right. These look nice, but are extremely hard to analyse. In an article linked from this section, Convert Excel Tables To Lists , he recommends converting the table to a sequence of rows, such that the cell with value V under column heading CH and row heading RH becomes a row whose first three columns are RH, CH, and V. One can then analyse these easily with pivot tables. The conversion can be done by looping over the table in VBA, and Michiel explains how.

There are a lot of comments about Michiel's advice following the entry Worst Excel Practices from Dick Kusleika's Daily Dose of Excel blog.

No TrackBacks

TrackBack URL: http://www.j-paine.org/cgi-bin/mt/mt-tb.cgi/91

About this Entry

This page contains a single entry by Jocelyn Ireson-Paine published on June 18, 2010 5:06 AM.

The Devil's guide to spreadsheet creation was the previous entry in this blog.

The Pain of Three-Dimensional Tables is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.