Analysis principle: keep your data consistent and together

One of the most important principles for managing data in Excel is this: keep your data consistent and together.

Excel is vast. In the 2007 version, each sheet contains enough cells to store three pieces of information about every person on the planet. So it's tempting to use its vastness to store one thing here, one thing there. Put your risks on one sheet, issues on another; move your closed defects on to a separate sheet to avoid cluttering the list.

A word of advice: don't.

Excel is at its most powerful when your data is consolidated. If your risks and issues are in a single list table, you can analyse them more easily. If your closed bugs are interleaved with your open ones, then it will be much easier to provide bug metrics. Once you start creating multiple lists or tables, you lose all of this power.

If things are sufficiently different so as never to need to be analysed together, then that's fine—keep them apart. But if there's ever a chance that you'll need to analyse them together, then keep the data together.

Many people complain about data being littered: I only want to see my open defects; I only want to see open risks assigned to me. Don't accept this argument.

At the higher level, use Custom Views to show different cuts of data. Custom Views allow you to apply filters and hide columns, and to save this view for future use. So you might have different views for risks, issues, items (everything), open risks, open issues, open items. At a lower level, users are free to use filters to select everything assigned to themselves, for example.

One final piece of advice: if at all possible, avoid having columns headed with the same type of thing. An example might be dates. If you're storing raw data for analysis purposes, try not to have a column representing each of a number of years (column B contains 2011figures, column C contains 2012 figures etc.). Instead, create multiple rows to store the data, and have a column headed "Year".

There will be more tutorials on how to analyse the data at a later date. But first, it's important to embed the principle that enables that analysis.

• Tweet