In this exercise, we'll review basic Excel spreadsheets. Part B will extend this work to add charts.

Begin by downloading this example spreadsheet: checkbook.xlsx. We'll try to make it look like this sample spreadsheet:

  1. Format the Header. Select all the cells in the header, choose "Format"-"Cells" and then set the "alignment" to "center", and add a background color.
  2. Format the Values. Format all the cells that display currencies by select the cell (or cell ranges) and selecting the "currency" category on the "Number" tab.
  3. Write a Balance Formula. Write a balance formula for cell F3 that takes the opening balance from F2 and subtracts the current withdrawal (the withdrawal value that may or not be on that line) and then adds the current deposit (which may or may not exist on that line). Note that when you use a cell in a formula and that cell's value is empty, the formula will interpret as being the number 0. When you've got the formula, then copy it down the column.
  4. Add an Expense Type Column. Add a "Type" column to the table, format its header as you did the other headers, and then enter appropriate values for each checkbook entry (e.g. Food, School, Entertainment, etc.). Be sure that at least two of your types have multiple entries.
  5. Create an Expense Type Table. Create a new expense type distribution table in the rows under your checkbook data:
    1. Add a "Type" column and list each type of expense that you used in your checkbook data.
    2. Add a "Total" column and use a SUMIF formula to compute the values. Use the function wizard to do this by highlighting the expense type cell (C20 in the sample), choosing the Formula tab, and then "Insert Function", searching for the function SUMIF, clicking "OK", and then letting the function wizard walk you through the process. After creating your formula with the wizard, your formula should look something like this:
      SUMIF(a range indicating the checkbook type names, 
            a cell indicating the type value to match,
            a range indicating the checkbook values to be summed)
      (Hint: the two ranges from the checkbook should start and end on the same rows.) When your function is working, copy it down the column for each type category using the fill handle. Remember to put commas between your 3 arguments and to use absolute and relative references appropriately.

This completes Part A of the check book exercise. DO NOT SUBMIT until you have also completed Part B. (You will be given time after Quiz 2 to work on Part B in class.)

Grading Criteria

We will grade this portion of the assignment according to the following criteria: