Taming other people's spreadsheets

Spreadsheets are great for managing diverse information. There's nothing as easy and powerful. But, the power comes with a cost.

Other people's spreadsheets can be challenging to understand and manage because the structure of the spreadsheet represents how other's think about the information (or worse, it's been passed around and reflects multiple opinions). In addition, the spreadsheet represents what someone else is tracking/managing which may not be exactly what we care about.

One technique to help place your structure on someone else's spreadsheet is a pivot table.

1. Start by adding a column to the worksheet which represents your opinion/status of the row. For high level reviews, I add simple values in each row such as "correct", "needs review", "needs repair".

2. Create a pivot table from the worksheet.

3. Using the newly added column as a top row summary in the pivot table, we can quickly get a count of how many items need attention and roughly quantify how much work lies ahead.

The more columns you add to the spreadsheet the fancier the reporting. For example, if you add another column which represents "assigned to", you can track who owns what problems and how many are unresolved - instant project management.

The possibilities are as endless as the spreadsheet.