Excel

1
Lab 2: Creating Charts in Excel
In this lab, we are going to create a pie chart, a bar graph, and a line graph using
criminal justice expenditure data for a ten-year period at varying levels of
governance.
Data Entry
Enter the information from Table 1 into Excel, using the skills you acquired in the
first lab. Be sure to place a title on it and label the data. Let cell C2 be your title,
and begin the data entry with cell B4 (being the label Year). This means F15 will
be your end cell for now (the value for municipalities in 2019). Be sure to label
your columns, and use text-wrapping so the labels fit in each cell. Again, if the
values in a cell reads as ####, the data you entered is there, but the column
width needs to be widened to display it. You can do this by simply placing the
cursor over the vertical line that divides the columns, left-click, hold down the left
mouse button and drag the line to the right, thereby widening the column.
Table 1: Criminal Justice Expenditures (in dollars)
for 2009-2019
Year Federal State County Municipality
2009 12,798 43,559 19,644 24,244
2010 15,231 47,075 21,913 25,599
2011 17,423 50,115 23,820 26,771
2012 18,591 52,562 24,625 28,321
2013 19,084 55,517 26,071 29,909
2014 22,651 58,933 27,917 31,581
2015 23,344 62,970 29,805 33,782
2016 27,065 67,083 31,778 35,869
2017 22,834 70,831 33,916 37,586
2018 27,392 74,830 35,547 39,995
2019 26,465 76,346 35,983 41,937
Analysis
Once the data are entered, we will need to generate some numbers for our
graphs. Knowing CJ expenditures over time suits the purpose of a line graph,
but we also wish to construct a pie chart and a bar graph. Our first question is:
what percentage does each level of government pay of the total CJ expenditures
during this period? Using a formula, we are going to calculate the percentage
each government agency spends on CJ for the entire period from 2009-2019. To
do this, sum each for the four government columns over the years (hint: use the
auto-sum function you learned in Lab 1) for cells C16-F16. Next, create a new
column title in cell G4 titled “Yearly Totals”. Now, in G5-G15, sum each year’s
worth of expenditures across all levels of government using the auto-sum
function (be sure not to include the year in the total!). In cell G16, sum all of the
2
expenditures of G5-G15. Finally, you are going to create another row of
information (C17-F17) that will calculate and show the percentage of total of CJ
expenditures over the years for each governmental agency. The formula will be:
= (total expenses by government agency/total expenses by all the agencies*100)
Use cell referencing in constructing the formula (example: =C16/G16*100) for
each percentage you calculate. You will not be able to cut and paste this formula
across the rows, since G16 will be your denominator in each one. Round off the
answer so you have two decimal places. Once you have the spreadsheet ready,
save your work.
When you are finished, you should have something that looks more-or-less like
this:
ALT = This is a screen shot of the spreadsheet with all your data entered and
calculated. You should have the following sums: Federal ($232,878), State
($659,821), County ($311,019), Municipality ($355,594), and Yearly Total
($1,559,312). For the percentages, you should have the following values:
Federal (14.93%), State (42.31%), County (19.95%) and Municipality (22.80%).
This shows that state expenditures in CJ are the highest, while federal
expenditures are the lowest.
3
Creating Charts
Now that our data are ready, we can create some charts. This lab will show you
how to construct a pie chart, a bar graph, and a line graph, as well as show you
some of the display options available in Excel.
To answer our first question, let us consider our options. The question deals with
percentages and compares parts of a whole (the percentage each level of
government pays for CJ administration). Thus, a pie chart OR a bar graph would
be appropriate. Let us do both!
The Pie Chart
To begin, we need to tell Excel what values we are interested in charting. So,
select the first percentage cell (C17) by left-clicking it, and drag the pointer
across the row to the last percentage cell (F17). Next, click the Insert Tab on
the Ribbon, then select the Pie Chart option in the Charts group. A sub-menu
will appear offering different pie chart types. Select the first option in the 2D
menu and a pie chart will appear. This chart has four slices, but we do not know
what they mean. Let us add labels to the chart along with percentages!
Notice two new tabs now appear in the Ribbon: Chart Design and Format. The
first thing we wish to do is modify the layout of the chart. Under the Chart
Design tab, in the Chart Styles group, you will see a down arrow with a
horizontal line above it. Click on it so you can see all the layout options. Select
one of the layouts that adds percentages to the chart.
Now let us add a title to the chart. Click the Chart Title, and a box will appear
around it indicating this part of the chart has been selected. Right-click on the
box and select Edit Text, and then delete the current text and type “Figure 1:
Criminal Justice Expenditures from 2009-2019”. (You can also highlight the text
by placing the cursor at either the beginning or end of the text, and then highlight
it by holding down the left button on the mouse and selecting the text).
Finally, we want to add labels to our Legend. Right now, numbers appear in the
Legend box, which does not tell a reader what they correspond to. Click on the
Legend and a box will appear around it indicating this part of the chart has been
selected. Right-click the Legend and then choose Select Data from the menu.
A new Select Data Source menu will appear. Click on the Edit button from the
Horizontal (Category) Axis Labels window in this menu. A new Axis Labels menu
will appear, and you should select the appropriate labels for the Legend. Leftclick and hold on the Federal cell and drag the mouse across to the Municipality
cell so all four levels of government are selected. Hit Enter, followed by OK
when returned to the Select Data Source menu, and now you can see that the
Legend is labeled correctly.
4
The chart will be fairly small, but you can adjust its size on the screen by clicking
and dragging one of the resizing handles along the chart frame. Try doing this
now.
Before printing, it is good to preview your work. Select the File tab, and then
select Print. The Print Preview will appear on the right side of the screen. Exit
this view if it looks okay. Just for your knowledge, if you were to print this and
you wanted it in black and white to conserve colored ink, you could select the
black and white pie chart in the Chart Styles group under the Design tab. Also
under this section are several other color options for you to choose from.
Go ahead and save your worksheet. **When you save an Excel file, every page
you create, and every chart you make gets saved in the one workbook file**
If you did this all correctly, your chart should look something like this:
ALT = Figure 1 is a pie chart representing the data you entered into your
spreadsheet. This figure shows the percentages for the four variables of interest.
Do not delete this chart! Simply move it to the side of your workbook before
moving along to the next chart.
15%
42%
20%
23%
Figure 1: Criminal Justice Expenditures from 2009-2019
Federal State County Municipality
5
The Bar Graph
We can easily construct a bar graph to display the data as well. If you have not
done so, move the pie chart off to the side by clicking on the pie chart and then
dragging it either to the right or bottom of your data.
Now, select the range of values to be charted (the percentages, again), and then
select the Insert Tab from the Ribbon. Under the Charts group, select the
Column/Bar button, and then select the first 2D Column/Bar chart option. A chart
will appear.
Now we want to select a different type of Column chart. Under the Chart Styles
group, which is found under the Chart Design tab, select an option that provides
you with values on the X- (horizontal) and Y-axis (vertical). Make sure you
choose an option that gives you room to add a chart title, as well as axis labels.
Click on the chart title so a box appears around it, right-click the title and select
Edit Text. Delete the Chart Title and write “Figure 2: Criminal Justice
Expenditures from 2009-2019”.
Now let us label the X-axis, which are the government agencies and the Y-axis,
which is the percentage of total expenditures. If the layout you selected does not
have a label already on your graph, under the Chart Design tab and the Charts
Layout section, click on the Add Chart Element button. Select the Axis Titles
option and you can insert a title for either axis. If you selected a layout with your
axes already labeled (or just one axis labeled), simply click on the label and a
box will appear around it, then right-click the label and select Edit Text. Now
label the X-axis “Government Agency” and the Y-axis “Percentage of Total
Expenditures”.
The next step to take is to add labels to the bars themselves. Right now, all that
appears are numbers, so the reader does not know what bar refers to which
government agency.
Click on the number 1 below the first bar. This will place a box around all of the
bar labels (1-4). Right-click the box and then choose Select Data… from the
menu. A new Select Data Source window will appear. Click on the Edit button
from the Horizontal (Category) Axis Labels. A new Axis Labels menu will
appear which wants you to select the appropriate labels for the bars. Now leftclick and hold on the Federal cell in the worksheet, and drag the mouse across to
the Municipality cell so all four levels of government are selected. Hit Enter and
select OK when returned to the Select Data Source menu. Now the bars are
labeled correctly.
Finally, we do not need a Legend, since there is only one series of data (the
chart title labels the series). If you selected a chart layout with a legend, click on
6
the Legend, and then Delete it. (Use either the DELETE key on the keyboard,
or right click and select Delete).
If you did this correctly, your graph should look something like this:
ALT = Figure 2 is a bar graph of the same data. Again, it is graphing the
percentage of criminal justice expenditures. This provides you with an alternative
way of presenting the data.
Do not delete this chart! Simply move it to the side of your workbook before
moving along to the next chart.
The Line Graph
Let us say that instead of wanting to know the percentage each government
agency spends of the total CJ budget for the decade, we wanted to track each
agency’s spending over time during that period. Here, we will need to construct
a line graph. Many of the steps to do so are like constructing the pie chart or bar
graph.
If you have not done so already, move the bar graph off to the side/bottom of
your data. Now we need to select the data we want to chart, which are all the
expenditures for each agency. Select these (range: C5:F15) and be sure not to
select the years or yearly totals, since we are not interested in these columns of
data. Also, be sure not to select the row with the agency totals either. Then,
0.00
5.00
10.00
15.00
20.00
25.00
30.00
35.00
40.00
45.00
Federal State County Municipality
Percentage of Total Expenditures
Government Agency
Figure 2: Criminal Justice Expenditures from 2009-2019
7
under the Insert Tab, select Line from the Charts group, and choose the first 2D
option. A line chart will appear.
Note: if your line chart looks flat with a big spike at the end, you mistakenly
selected the row with the agency totals. If you did this, start over, and do not
select them!
Now under the Chart Design tab, in the Chart Styles group, select a layout
option that gives you space to add a label to the X- and Y-axis and add a chart
title. Using the same procedures from the bar graph, add a figure number (i.e.,
Figure 3), title (same as the last two graphs), and label the X-axis “Years” and
the Y-axis “Dollars”. (Do not worry about the X-axis values, we will discuss those
in a second!)
Once the labels have been added, it is time to fix the Legend. Click on the
Legend so a box appears around it. Now right-click on the box and choose
Select Data…. The Select Data Source menu will appear. In this menu, we
need to label the Series 1 through Series 4 data. In the Legend Entries (Series)
window, select Series 1 so it is highlighted, and then click the Edit button in the
same window. Now Excel wishes to know where the label for the first series of
data is. Click on the cell with the Federal label (C4), and then hit Enter. This will
bring you back to the Select Data Source menu. Now select Series 2 so it is
highlighted, and then click the Edit button in the same window. Now click the cell
with the State label (D4) and then hit Enter. This will bring you back to the
Select Data Source menu. Label Series 3 “County” and Series 4 “Municipality”
using this same procedure, but do not exit the Select Data Source menu.
Last but not least, we have to add the years to the X-axis. To do this, select Edit
from the Horizontal (Category) Axis Labels window. Now Excel wants the
labels for the X-axis, which are the years. Click and drag across all the years
(2000-2009), but do not select the Years label from the worksheet. Hit Enter,
and now the years will appear along the X-axis in the line graph. Click OK.
If you did the last graph correctly, it should look something like this:
8
ALT = Figure 3 is a line graph of the data. This time you should be graphing the
dollar amount spent by these groups from 2009-2019.
Great! Now you know how to construct three different chart/graph types in Excel.
(*Note: If your labels disappear (i.e., x/y-axis, legend, title, etc.), under the Chart
Layout section of the Chart Design tab, the Add Chart Element button allows you
to insert them again.)
Lab 2 Assignment: Please submit your final Excel spreadsheet with the
data you have just entered and the three charts/graphs you have created.
Let us briefly review what you learned in this lab:
▪ How to construct a pie chart, a bar graph, and line graph
▪ How to place a title on these charts
▪ How to label the X- and Y-axis on these charts where appropriate
▪ How to label different series of data on a line graph
▪ How to label the legend on a pie chart and line graph
10,000
20,000
30,000
40,000
50,000
60,000
70,000
80,000
90,000
2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019
DOLLARS
YEARS
FIGURE 3: CRIMINAL JUSTICE EXPENDITURES
FROM 2009-2019
Federal State County Municipality