UseExcel: Excel 2007 Common Tasks

We are going to build a small budget worksheet. We will sort the budget data, format it, and page preview it.

1. Open a new Excel workbook (below).
   
   
2. Change the Sheet1 name to "Budget" to reflect what we are doing. Double-click on the worksheet tab where it says "Sheet1" and rename it to "Budget". Press the Enter key when you are done renaming it.
   
   
3. In column A create a column header called "Category", and in column B create a header called "Amount". Enter the budget categories and amounts that you see in the image below. Add a "Total" row in cell A11.
   
   
4. Let's format the amounts to make them look like money. Make sure the Home ribbon is selected and look for the Number group. Locate the Accounting Number Format icon within that group. Click the Accounting Number Format icon.
   
   
5. Select cell B10 where the formula for the total Amount is going to go. On the Home ribbon look for the Editing group. Locate the AutoSum icon within that group. Click the AutoSum icon.
   
   
6. The AutoSum function =SUM(B2:B9) appears in the the formula bar and in cell B10. Notice that Excel correctly guesses the range you want in the formula. Press the Enter key to finish adding the Sum formula.
   
   
7. This is what it looks like:
   
   
8. We want to insert a new row above row 1 in order to indicate that column B is for January. We will add more columns for more months. Select row 1. Right click and click the Insert option to insert a new row above row 1. Row 1 becomes row 2. Enter January in cell B1, February in cell C1, Amount in cell C2. Enter some amounts for the categories in cells C3:C10. Autosum the column total in cell C11. Format the cells with the Accounting Number Format icon. This is what it should look like:
   
   
9. We're going to calculate the percent change between Frebruary and January. Enter "% Change" in cell D1. In cell D3 enter the formula for the % change. To do this, step-by-step, start by entering an equals (=) sign. Remember all formulas start with an equals sign. Next, enter a left parenthesis (. With your mouse, select cell C3. Enter a minus sign (-). With your mouse, select cell B3. Enter a right parenthesis, then the divisor sign - the forward slash (/). Finally, with your mouse, select cell B3 and press the Enter key. The formula should look like =(C3-B3)/B3. While you could have entered the formula directly through the keyboard e.g. typing in the cell addresses C3 and B3 instead of selecting celss C3 and B3, I recommend that you select the cells to add the cell addresses to the formula. This technique eliminates the possibility of typing the cell adress incorrectly, and this technique is used in other worksheet functions, where you select a range with the mouse, as part of the formula.
   
   
10. Unfortunately Excel guesses wrongly and formats cell D3 as Accounting Number. Select cell D3. Locate and click the % format icon in the Number group on the Home ribbon. With cell D3 selected still, hover the mouse over the fill handle of cell D3. The fill handle is in the lower right hand corner of teh cell and it is a small black square. Once the mouse is over the fill handle, the cursor changes from the default, wide plus sign to a cross-hair cursor. Left-click, hold, and drag down to cell D11 and let go. This is what it should look like: