OpenOffice.Org 1.0 Resource Kit

Using spreadsheets wisely has a lot to do with making spreadsheets as automated as possible, which ultimately makes it easier for you to maintain them. The following considerations explain this further.

Put Every Piece of Data in a Separate Cell

Put every piece of data in a separate cell as much possible. For example, if you want to divide a dollar amount by 12 to get a monthly dollar amount, consider putting 12 in a separate cell; so that instead of 30000/12, you get 30000/B5. That way, you can change 12 to 4 to show a quarterly amount, or to 6 to show a semi-annual amount.

When you put everything in cells , all your formulas can contain cell references, making them more dynamic (they update automatically when cell contents change). Automation also means you spend less time tinkering with numbers that are manually entered in formulas.

Break It Up

In the same way that it's good practice to put values in cells and reference them in formulas (rather than entering values in the formulas themselves ), it's also good practice to divide your spreadsheets into logical parts , with each part on a separate sheet. For example, if you're maintaining income and expense information to create a balance sheet, a single sheet containing all that data could get pretty crowded. A better way would be to track income on one sheet, expenses on another sheet, and build the balance sheet report on a third sheet.

Putting different types of information on different sheets is essential to managing complexity and minimizes the amount of tinkering you have to do, such as hiding rows and columns .

For example, separate sheets are good for reusing data that you need on many sheets, such as interest rates, budget limits, dates, recurring dollar amounts (like mortgage payments), and so on. You can set up such information on a single sheet and reference it on all your other sheets.

At the very least, even if you're going to keep all your data on a single sheet, keep common data in a separate area for easy reference.