Label the columns
To create a new workbook, start Excel. For tracking receipts, I begin by typing in column headers, like this:
1. In cell A1, type All Costs.
2. Press Enter twice (cell A3 should now be selected).
3. Type Date of Purchase.
4. Press the right arrow on your keyboard to move to cell B3.
5. Type Job.
6. Row 3 in this worksheet will be the “header row,” so each column must have a unique name.
7. Press the right arrow to move to cell C3. In cell C3, type Item.
8. Continue alternately pressing the right arrow and typing until your worksheet looks like the one shown above.
9. Name and save the file.
Enter data and apply markup
Next, enter the data as shown in the sample below.
1. Type the date 7/1 in cell A4 and press the right arrow. Excel will automatically format 7/1 as 1-Jul.
2. Continue entering the sample receipt data in cells B4 through F4.
3. Click cell G4.
4. Type =f4*1.3
The * symbol in the formula indicates multiplication; multiplying the receipt amount in cell F4 by 1.3 gives the cost of the lumber plus a 30 percent markup. You can adjust this number to reflect your own practice.
5. Press Enter.
Subtract your cost from what you charge the customer to find your profit.
1. Click cell H4.
2. Type =g4-f4
3. Press Enter.
Use a shortcut to copy formulas
The copy handle makes copying a formula to a group of cells easy. It appears as cross hairs when you place your mouse pointer in the lower right corner of any cell. As you copy a formula down a column, Excel automatically changes the cell addresses in the formulas to reflect the new row numbers.
1. First enter additional rows of data, as shown below.
2. Select cells G4 and H4.
3. Place your mouse pointer in the lower right corner of cell H4.
4. Click on the copy handle and drag it down the
column to copy the Customer Price and Profit
formulas to the new rows.
Delete unwanted formulas
After copying a formula to a whole group of cells, you may find you need to remove the formula from a few selected cells. In the example below, the cost of a permit is not marked up, so I removed the markup formulas from cells G6 and G9.
1. Click on cell G6.
2. Press Delete on the keyboard and replace the formula with the actual cost of the permit.
3. Repeat for cell G9.
Set up the AutoFilter
The power of the AutoFilter feature is best appreciated when used with a long, complicated list, so I’ve added more receipt data to the example (below). This tool “filters out” only the data you wish to see at a given time. For example, you could filter out the details of the Alexander job. Or, you could filter out your lumber purchases. I’ll do both. (If you already know how to create a totals row, don’t do it just yet. Wait until after you apply a filter. )
1. Add the data in rows 10 through 20.
2. The cell pointer must be placed somewhere within the list, so click cell B4.
3. On the Home ribbon, click the Sort & Filter tool, then select Filter. (If you are using an earlier version of Excel, you can turn on the AutoFilter by clicking Data on the toolbar, selecting Filter, and clicking on AutoFilter.)
4. Notice the down arrows that appear in every cell of the header row (row 3).
View one job at a time
Finding out how well the Alexander job is going is now a simple matter.
1. Click the down arrow to the right of the “Job” column heading in cell B3.
2. Click Select All (to turn the filter off). (In Excel 2003, click (All).)
3. Click Alexander (to turn the filter on). The result is a view of only the Alexander job, as shown at right. Excel is hiding all rows that do not have “Alexander” in the Job column in the spreadsheet.
Create a “totals row”
Now it’s time to create a totals row.
1. Drag to select cells F22 through H22.
2. Click once on the SUM tool (?) on the Home ribbon (or the toolbar, if you’re using an earlier version of Excel).
3. The profit on the Alexander job is $4,260.00. 4. Notice the formula that appears in the formula bar.
Clear the filter
When a column has been filtered, the down arrow in the header row changes to a filter icon (in Excel 2003, the arrow changes color) to remind you that the list is filtered. You will typically clear this filter before filtering again.
1. Click the filter icon in cell B3.
2. Choose Clear Filter from “Job.” (In Excel 2003, click the colored down arrow and choose (All).) The unfiltered list appears. The subtotal formula in row 22 should now reveal a total profit to date of $34,777.50 (see spreadsheet at the bottom of page 58).
Filter out lumber purchases
Let’s ask another question: “How much has been spent on lumber so far?”
1. Click the down arrow in the “Item” header (cell C3).
2. Click Select All (to turn the filter off). (In Excel 2003, click (All).)
3. Click Lumber (to turn the filter on).
4. The subtotal in cell H22 indicates a profit of $15,916.50 on lumber sales for the season.
Add more rows
As the season progresses, you can add more rows in which to enter receipts. An easy way to do this while keeping the totals row intact is:
1. Click anywhere in row 21 (the last empty row in the list).
2. On the Home ribbon, click the down arrow next to the Insert tool, then choose Insert Sheet Rows. (In Excel 2003, select Insert from the toolbar, then click Rows.)
3. If you make a mistake, immediately either press Ctrl Z on your keyboard or click on the undo icon to “undo” your last action. Then try again.
Odds are your computer came loaded with Microsoft Excel, a spreadsheet program that has a variety of tools you can use to track or analyze information about your business. One of these is the powerful, yet easy-to-use, AutoFilter. With it, you can check the overall status of the year’s finances or zoom in on one particular aspect. In this article, I’ll show you how to use AutoFilter to manage a list of purchase receipts and customer charges.
If you’ve never used Excel, here’s the gist: Starting Excel opens what’s called a “worksheet,” which is a huge grid of rows and columns. Each row is numbered and each column has a letter assigned to it. The intersection of a row and a column is called a “cell.” A single worksheet contains millions of cells, so you’re not likely to run out of space.
Worksheets are customizable. As you build a worksheet, you put a piece of information — typically a number or a note — in each cell. You can also set up a worksheet so that Excel performs mathematical operations like addition, subtraction, multiplication, or division on selected cells, rows, or columns. The goal is to organize all the pieces of information and let Excel perform the calculations. Then you can easily see how well (or not) your business is doing.
Please note that the Excel screens shown in this article depict the most recent version of the program, Excel 2007, but if you have Excel 2003, you don’t need to upgrade. The main difference — in the context of this article — is that the “toolbar” of earlier versions of Excel has been replaced by what’s called a “home ribbon” in Excel 2007. It doesn’t change the way that the worksheet functions.
As always, be sure to back up your file after working with it. The easiest way is to copy it to a USB flash drive, which you can get at any office supply store for about $20.
Kathy Slivka is a freelance software trainer and the Academic Technology Coordinator for Kent School in Kent, Conn.
Rows and columns intersect at cells; the cell selected in this worksheet, at the intersection of column E and row 4, is called E4. Excel can be set to perform mathematical operations to selected cells, rows, or columns, providing a variety of ways to look at data.