Charts/Tables Lab
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.xlsx.
We'll try to make it look like this
sample spreadsheet:
- 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" to "center", bold the font, and add
a background color.
- Format the Dates/Values. Select all the date cells,
choose "Format"-"Cells", and format them as MMM-YY. 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 (e.g. Food, School, Entertainment, etc.).
Be sure that at least two of your types have multiple entries.
- Create an Expense Type Table. Create a new expense
type distribution table in the rows under your checkbook data:
- Add a "Type" column and list each type of expense that you used in your checkbook data.
- 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 (C17
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 name condition,
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.
Creating a Simple Pie-Chart
Now, create a pie chart that shows the distribution of your expenses as
shown in this sample pie chart.
To create this pie chart:
- Select the cell range to chart. Select the cell
range of the chart,
B16:C21
in the example.
- Create the chart. Choose the "Insert" tab on the
ribbon and select the "pie" chart type from the options.
- Specify the chart settings. Work through the chart
options in the Office Ribbon (and some clever right-clicks on the chart
itself) to specify settings that will make the
chart look as much like the sample as possible:
- Display the category names around the chart (not in a legend)
- Include the percentage values.
- Include a title.
- Put the chart on an appropriately labeled sheet.
Select the chart, and then cut and paste it to another sheet. Don't forget to
rename the sheet.
Creating a Line Chart
Also create a line chart that shows the balance of your
account over time, as shown in this
sample line chart.
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 in a more
complex cell range specification (B3:B13,F3:F13
in
the example).
- Create the chart. Choose "Insert" tab and choose a
"line" chart, and then follow the wizard through the process of
creating the chart (as you did with the pie chart above).
- As with the pie chart, put this line chart on an appropriately labeled sheet.
Select the chart, and then cut and paste it to another sheet.
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. Do not build any of the charts, just indicate which kind of chart you would build.
- Which Chart #1. 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?
- Which Chart #2. 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?
Jot down your written answers, and type them into the "Notes" field of
your Moodle submission.
Extra Credit
As an extra credit challenge, create a chart that presents both of
the perspectives listed above for the tuition costs in a single chart.
Submitting your Work
Submit your checkbook workbook as your Moodle
submission. Type your answers to the charting questions into the "Notes"
field. If you did the college-costs extra credit, attach your college costs
workbook as well, and make note of it in the "Notes" field.