1



1.4 Charts and GraphsFrequently we need to present our data in a more “user friendly” format than a table. Often a simple graphical representation of our results greatly enhances the impact of the message. The Excel charting tools can represent information stored in a workbook in graphical format such as pie charts, line plots, and bar/column graphs. This chapter will focus on which type of chart best represents the information we wish to convey. This chapter does not focus on the mechanical details of using the charting tools; creating and modifying charts can be learned from the course textbook or any other step-by-step instruction manuals available on this topic.There are several basic types of charts that will be discussed in this section:Line ChartsXY Scatter Plots Bar & Column ChartsPie ChartsLine Chart:A Line Chart plots a series of data on the X and Y-axes. Each value is plotted in equal intervals along the X-axis. This type of chart is an excellent method of illustrating trends. It should not be used to show the relationship between two variables. For example, look at (fictitious) population data for the United States in Figure 1. Notice the points are evenly spaced on the horizontal axis of the chart. However, these “even spaces” represent different time intervals. The same eighth of an inch represents time spans ranging from as little as 20 years to 1000 years.Figure 11905085725The chart clearly illustrates how this population has grown. The trend line drawn between data points shows that the population grew slowly and then took a small dip before rapidly increasing during the 1800-1950 period. In recent years the chart shows another small population decrease.XY Scatter Chart:An XY Scatter Chart is used to plot the relationship between two variables on an XY plot. Most students learn a simple form of this type of chart in algebra class – the general formula for a straight line in the XY plot is y=mx+b. The XY Scatter chart options allow plotting of the data points with no connecting lines, creation of a straight line between points, or creation of a curved line between points.What if the data in Figure 1, time vs. population, was plotted on an XY Scatter chart rather than on a line chart? Many of the points would be clustered around the year 2000. This would not be a very effective method of showing a trend. In addition, time is not really a “variable” in the sense that a specific year is not by itself a predictor of the population. That is, population does not depend on time – it depends on variables like infant mortality rate, immigration, average expected life spans, etc.To illustrate the use of an XY Scatter chart, look at a plot of immigration and population growth as seen in Figure 2. This chart shows the relationship between the two variables, in this case population growth over a twenty year time span versus the average yearly immigration rate over this same twenty year time span. Population growth is shown as a function of immigration. The line connecting these points allows the user to extrapolate population growth for a specific immigration rate. Notice that the points are not plotted in chronological order, time is not explicitly shown. The resulting graph shows a positive sloping line, indicating that as immigration increases so will population growth.Figure 2257175308610Bar & Column Charts:Bar charts are graphs that use horizontal bars to represent a quantity for a specific item. Similarly, Column charts are graphs that use vertical bars to represent a quantity for a specific item. Both of these types of charts are well suited for comparing quantities of discrete items.Philadelphia?Figure 33019425795655In the line chart plotted in Figure 1, population is plotted along a timeline. Population on the timeline can be extrapolated by selecting the point on the line corresponding to a specific time period. While this value may not be exactly accurate as there is no functional dependency, it at least gives a reasonable estimate. Now consider another set of data, the population in the year 2007 for 5 major US cities: Boston, Hartford, New York, Washington D.C. and Miami. Figure 3 shows this data plotted on a line chart. Could we estimate the population of Philadelphia (the triangle point drawn on the line) as between that of New York and Washington since it geographically lies between the two? Of course not, each city is a discrete entity and relevant data cannot be extrapolated on a graph joining the population data points. This type of discrete information is best displayed in a Bar or Column chart as seen in Figure 4. Figure 4676275950595Population data In millionsFigure 4 shows two charts representing the same data. The left hand chart is a Column chart and the right hand chart is Bar chart. Each is equally good at displaying this type of discreet data. Which one to choose is a matter of individual preference and limitations such as width and length of the page. -95251165225There are several useful options in Excel for enhancing Column and Bar charts. Each bar/column can be individually shaded, labels may be placed on each bar, a Legend may be displayed, the background can be modified, and the text fonts for the titles and axes may be specified. These charts also have options for displaying groups of values using a “clustered” bar/column charts or a “stacked” bar/column charts.Clusters: Cluster Bar and Column charts allow for the use of multiple data sets, such as populations for each city for multiple years. Data for each city is then plotted as a series of bars (representing years) clustered together. An example of a clustered column chart is illustrated in Figure 5.Figure 5-974090425450Figure 6Stacked: Stacked Bar and Column charts make it possible to take a group of data and subdivide it. This data can then be illustrated as a series of bars, where each bar contains multiple sub-sections. For example, populations in each city for two different age groups can be “stacked,” showing each component and the total population for that city. An example of a stacked bar chart is illustrated in Figure 6. Pie Charts:Figure 74286251883410A pie chart is basically a circle subdivided to represent “parts of a whole”. A pie chart is excellent for representing percentage comparisons between categories. In the population example, a pie chart can be used to illustrate the relative sizes of each of the 5 cities. The illustration shown in Figure 7 is a pie chart comparing populations in each of these cities as a percentage of the total population for all 5 cities. The pie chart does not readily illustrate that there are 12 million people in NY versus 12,000 or 1200. However, it can be easily extrapolated that NY has the largest population of these five cities, while Hartford has the smallest population. Good Practices for Charts:It’s always easy when working with a tool such as charting to either create charts that are either poorly documented or overly busy or messy. Here are some good rules of thumb for creating effective, readable charts – whether they be in Excel or anywhere else:Choose the correct chart type to convey the points you wish to make.Charts should appear neat and clean and be done as simply as possible to convey the most important message. Well chosen colors and patterns clearly illustrate similarities and differences.Charts should have titles that clearly indicate the subject matter.The axes scales should be clearly titled and delineated with well scaled and well marked intervals. Unit values should reflect the needed “significance.” e.g., values in the millions need not show decimal places.Data should be clearly marked with labels or on an accompanying legend/key.If a chart is rotated it should be readable either from the bottom or the right hand side of the page.A good overall chart should be easy to read and tell a complete story which can stand alone even without the text. When displaying data, it really is true that a picture is worth a thousand words.Solving Problems using Charts:Figure 81171575836930Below is a spreadsheet listing John’s expenditures over a three-month period from January through March. We will use this data in Figure 8 to help create charts to answer the questions below.2409825533400Question #1: Graphically illustrate how the costs have changed over the three month period. Detail the change for each cost category.Solution: The chart needs to show a trend over time. A good choice might be to use a Line Chart, as illustrated in Figure 9.Figure 9-28575074930Question #2: Graphically represent the relative total expenditures in each category over the three month period.Solution: Relative expenditures can be illustrated by using percent of total. To display percentage comparisons, a pie chart is usually a good choice, as seen in Figure 10.Figure 10Question #3: Graphically represent each month’s expenditures including category breakdowns.238125816610Solution: Each month’s total expenditures is a discrete item. Thus, a Bar or Column chart would be appropriate. With the use of stacking, each component can be visually displayed. The solution is plotted in Figure 11.Figure 11Exercise 1.4-1 Chapter Review – Creating ChartsWhat type of chart would best display the current values of each of the stocks in your portfolio?If you were to configure the chart you’ve selected in question one, what range would you use on your chart such that it would include the stock name and value?If you were to configure the chart you’ve selected in question one, which would you use for the x-axis values – values along rows or columns?What type of chart would best display the relative proportions of each of these stocks in your portfolio based on their current values?You have downloaded the historical prices (date and price) of Microsoft stock over the past five years onto a separate worksheet. What type of chart would best display the trend of price fluctuations for this data?Your buddy (Mr. Get Rich Quick) swears that there is a relationship between the daily temperature and whether or not Microsoft’s stock price goes up or down. So to prove him wrong you have input the temperature in New York for each of the dates corresponding to the prices you downloaded. What type of chart would best show this functional dependency between temperature and price? ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download