Excel XP: Planning your Excel XP Spreadsheet

Lesson 22: Planning your Excel XP Spreadsheet


Many different applications can be created in Microsoft Excel. You may want to create a budget spreadsheet or a newsletter mailing list. Perhaps you want to create an inventory of items in your home and business. Maybe you want to create a spreadsheet that contains financial data or baseball statistics. Whatever you may want to do in Excel, the first step of any successful spreadsheet is to plan what you want to create.

Let’s say, for example, that you want to create a checkbook register. The first step is to identify the different types of information that must be added to the checkbook spreadsheet. A good start is to look at the transaction register that comes with the box of checks from the bank. Notice the fields that are used in the register: Number or Code, Date, Transaction Description, Payment/Fee/Withdrawal, Deposit/Credit, and Balance.

Checkbook Transaction Register

The fields that appear as column headings in your transaction register should also appear as column headings in the spreadsheet.

Checkbook Register Blank

Each row in the checkbook spreadsheet will be represented by a specific check made payable to someone on a particular date for a specific amount and with a unique check number.

Checkbook Register showing data

Each column in the spreadsheet will be formatted differently. The Date column, for example, will be formatted as a Number. The Number column will be formatted as a General Number. The Transaction column will be formatted as text. The Debit and Credit columns will be formatted as Currency. Each formula in the Balance column will subtract the credit and add the debit to the previous balance to display the new balance.

That’s really all there is to planning how to create a spreadsheet. You are simply going to re-create a process that you already do manually. Errors will be minimal because the balance is calculated automatically for you. Typing will be minimal because Microsoft Excel’s AutoComplete feature will save you time from having to retype information, like Telephone in the picture above, as long as it has already been typed into the spreadsheet.

Now that you know what it is you want to create and how to create it, it’s time to open Microsoft Excel and get to work.