Be obsessive - IT people attend to details (ATD)
because in IT every little thing matters.
In this lab exercise, we'll review basic Excel spreadsheets and
then work with Excel's charting/graphing capabilities.
Formatting a Worksheet
For this lab exercise¹, download this example spreadsheet: lab4-checkbook.xls.
We'll try to make it look as follows:
In order to do this, do the following things:
- Add Checkbook Entries - Add at least 8 entries to the
checkbook in the appropriate places. You don't need to use
the entries given here, most any entries will work so long as
you can categorize them easily.
- Format the Header - Select all the cells in the
header, choose "Format"-"Cells" and then set the "alignment",
"font" and "patterns" as you see fit.
- Format the Dates/Values - Select all the date cells,
choose "Format"-"Cells", and format them as you see in the
sample. Then do the same with the currency values, selecting
the "currency" category on the "Number" tab.
- Write a Balance Formula - Write a balance formula for
cell F3 that takes the opening balance from F2
and subtracts the withdrawals and adds the deposits. When
you've got the formula, then copy it down the column.
- 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.
- Create an Expense Type Table - Create a new expense
type distribution table as shown on the bottom of the sample.
It should have a row for each expense type and a formula that
computes the sum of the expenses of that type.
- Write the SUMIF Formula - Enter in the expense-type
category names by hand and then write a formula to compute
the sum of the withdrawals for each type. Use the
function wizard to do this by highlighting the expense type
cell (C17 in the sample), choosing
"Insert"-"Function", searching for the function SUMIF,
clicking "OK", and then letting the function wizard walk you
through the process. Your formula should look something like
this:
SUMIF(checkbook type names ($G$2:$G$13 in the example),
type name condition (B17 in the example),
checkbook values to be summed ($D$2:$D$13 in the example))
When your function is working, copy it down the column for
each type category. Remember to put commas between your 3
arguments and to use absolute and relative references
appropriately.
Creating a Simple Pie-Chart
Now, create a pie chart that shows the distribution of your
expenses as shown here:
Create this pie chart as follows:
- Select the cell range to chart - Select the cell
range of the chart, B16:C21 in this example.
- Create the chart - Choose "Insert"-"Chart", and
select the "pie" chart type.
- Specify the chart settings - Work through the chart
wizard trying to specify settings that will make the chart
look as much like the sample as possible. You may want to try
it several times to experiment with different setting
values.
Choosing an Appropriate Chart
It is important to know how to choose an appropriate chart (or
charts) for expressing the message you want to deliver. As
discussed in class, Excel provides a number of chart/graph types,
including pie charts, bar/column charts, line graphs and scatter
plots. Always choose the best one for your data and message.
For this lab exercise, consider the data shown in lab4-costs.xls
and answer the following questions:
- You want to help prospective students decide whether they should
live on or off campus by showing them the relative costs of
the three options shown in the example table. What chart
would you use to do this? You don't have to build the chart,
just say which type you'd use.
- You want to show current students how their total tuition
cost is divided among the various expense categories shown in
the example table. What chart type would you use for
this?
- As an extra credit challenge, build these two charts and
submit them with your lab work. As an additional challenge,
try to figure out a way to present both of these perspectives
in a single chart.
Save your written answers and type them into the "Comments"
field of your KV lab submission. If you do the extra credit,
remember to clearly label your charts and add a note to the
"Comments" field telling the grader what you've done.
A Challenge - Creating a Line Chart
For additional extra credit, you can go back to your
checkbook spreadsheet and create a line chart that shows the
balance of your account over time, as shown here:
Create this chart as follows:
- Select the Data to Plot - Select the cells containing
the data and the dates you want to plot. Because these cell
ranges are on different parts of the chart, you must first
select the range of cells with the dates (B3:B13
in the example), and then press and hold the CTRL
button to select the range of cells with the balance value
(F3:F13 in the example). This will result is more
complex cell range specification (B3:B13,F3:F13 in
the example).
- Create the chart - Choose "Insert"-"Chart", choose a
"line" chart, and then follow the wizard through the process of
creating the chart (as you did with the pie chart above).
Getting Further Help
You can get more help on Excel charts by going to Microsoft's
on-line charting tutorial: Create a Chart or by by choosing
"Help"-"Microsoft Word Help" and searching for "create a
chart".
Submitting your Work
Submit your budget spreadsheet as an attachment to your Lab #4
KV submission. Type your answers to the charting questions into
the "Comments" field. If you did the extra credit, attach your
college costs spreadsheet as well.
¹ This exercise is based on an exercise written by Jeff Nyhoff.
Last modified: Fri Jan 20 10:22:43 Eastern Standard Time 2006
©
2006
Calvin College