Creating Charts That Show Trends - pearsoncmg.com

1 IN THIS CHAPTERC reating Charts that ShowTrends3 choosing a chart TypeYou have two excellent choices when Creating chartsthat show the progress of some value over Western cultures are used to seeing timeprogress from left to right, you are likely to choosea chart where the axis moves from left to right whether it s a column chart , line chart , or you have only a few data points, you can use acolumn chart . Column Charts work easily for 4quarters or 12 months. Within the column chartcategory, you can choose between 2-D and 3-Dstyles. If you want to highlight one component of asales trend, you can use a stacked column chart . choosing a chart Type ..81 Understanding a Date-Based Axis Versus aCategory-Based Axis ..84 Using a chart to Communicate Effectively ..104 Adding an Automatic Trendline to a chart ..113 Showing a Trend of Monthly Sales and Year-to-Date Sales ..115 Understanding the Shortcomings of StackedColumn Charts ..116 Shortcomings of Showing Many Trends on aSingle chart .

2 118 Using a Scatter Plot to show a Trend ..119 Next Steps ..120 This book recommends not using pyramid Charts or cone chartsbecause they distort your message. For an example, see Lying withShrinking Charts in Chapter you get beyond 12 data points, you shouldstrongly consider switching to a line chart . A linechart can easily show Trends for hundreds of peri-ods. Line Charts can be designed to show only thedata points as markers or to connect the data pointswith a straight or smoothed shows a chart of 9 data points. This isfew enough data points that a column chart ismeaningful. Figure shows a chart of 100+ datapoints. With this detail, you should switch to a linechart in order to show the 3/27/07 12:57 PM Page 81An area chart is a line chart where the area under the line is filled with a shading or can be appropriate if you want to highlight a particular portion of the time series. Ifyou have fewer data points, adding drop lines can help the reader determine the actualvalue for each time you are plotting stock market data, you can use stock Charts to show the trend of stockdata over time.

3 You can also use high-low-close Charts to show the trend of data that mightoccur in a range (for example, if you have to track a range of quality rankings for each day).You might think that a bar chart could be used to show time Trends . However, that wouldconfuse your readers because they expect time to be represented from left to right. In veryrare cases, you might use a bar chart to show a time trend for example, if you had 40 or50 points, all with very long category labels, and you needed a printed chart to legibly showdetail for each point. As an example, Figure shows sales for 45 daily dates. The chartwould not work as a PowerPoint slide, but if it were printed as a full page on a letter-sizepiece of paper, the reader could analyze sales by weekday. Note that in the chart in , weekend days are plotted in a different color than Charts are great for comparisons. If you are thinking about using a series of pie Charts toshow changes over time, however, you should instead use a 100% stacked column the Charts in Figure It is difficult for the reader s eye to compare the piewedges from year to year.

4 Did market share increase in 2005?3 Chapter 3 Creating Charts that show Trends82 Figure 12 or fewerdata points, columncharts are viable you gobeyond 12 datapoints, it is best toswitch to a line chartwithout individualdata points. The bottom chart in thisfigure shows thesame data set as aline 3/27/07 12:57 PM Page 8283 choosing a chart Type3 Figure time seriestypically should runacross the horizontalaxis, this chart allows 45points to be is difficult to compareone pie to the Figure , the same data is plotted as a 100% stacked bar chart . Series lines guide thereader s eye from the market share from each year to the next year. The stacked bar chart isa much easier chart to read than the series of pie a 100% stackedbar chart , the samedata from Figure easier to 3/27/07 12:57 PM Page 83 Understanding a Date-Based Axis Versus a Category-Based AxisExcel offers two types of horizontal axes in a trend chart . Having the proper setting canensure that your message is the spacing of events along the time axis is uniform, it does not matter whether youchoose a date-based axis or a text-based axis.

5 The results will be the same. In this case, it isfine to allow Excel to automatically choose the type of , if the spacing of events along the time axis is haphazard, you definitely want tomake sure that Excel is using a date-based 3 Creating Charts that show Trends84 CASESTUDYA ccurately Representing Data Using a Time-Based AxisFigure shows the spot price for a certain component used in your manufacturing plant. To find this data, you down-loaded past purchase orders for that product. Your company doesn t purchase the component on the same day everymonth; therefore, you have an incomplete dataset. In the middle of the dataset, a strike closed one of the vendors, spik-ing the prices from the other vendors. Your purchasing department had stocked up before the strike and was able to dra-matically slow its purchasing during the top chart uses atext-based horizon-tal axis: Every eventis plotted an equaldistance from thenext event. Thisleads to the shadedperiod being the top chart in Figure , the horizontal axis is set to a text-based axis, and every data point is plotted an equal dis-tance apart.

6 Because your purchasing department made only two purchases during the strike, it appears as if the timeaffected by the strike is very narrow. The bottom chart uses a date-based axis. In this axis, you can see that the strikeactually lasted for half of 3/27/07 12:57 PM Page 8485 Understanding a Date-Based Axis Versus a Category-Based AxisUsually, if your data contains dates, Excel defaults to a date-based axis. However, youshould explicitly check to make sure that Excel is using the correct type of axis. A numberof potential problems force Excel to choose a text-based axis instead of a date-based axis,such as dates that are stored as text in a spreadsheet and dates represented by numericyears. (See the list following Figure for other potential problems.)To explicitly choose an axis type, you follow these the horizontal axis and choose Format the Format Axis dialog box that appears, choose the Axis Options either Text Axis or Date Axis, as appropriate, from the Axis Type section (seeFigure ).

7 3To learn how to highlight a portion of a chart as shown in Figure , see Highlighting a Section ofChart by Adding a Second Series, later in this Type SettingsFigure can explicitly choosean axis type rather thanletting Excel choose number of complications that require special handling can occur with your date following are some of the problems you might encounter: Dates stored as text If your dates are stored as text dates instead of real dates, adate-based axis will never work. You have to use date functions to convert the textdates to real dates. Dates represented by numeric years All your trend Charts may have category val-ues of 2005, 2006, 2007, and so on. Excel doesn t naturally recognize these as dates,but you can trick it into doing 3/27/07 12:57 PM Page 85 Dates before 1900 If your company has been around long enough that you arecharting historical Trends before January 1, 1900, you are sunk. In Excel s world, thereare no dates before this time period. Dates that are really time It is not difficult to imagine Charts in which the horizon-tal axis contains periodic times throughout a day.

8 You might want to use such a chart toshow the number of people entering a bank. For such a chart , you need a time-basedaxis, but Excel will group all of the times from a single day into a single point. See Using a Workaround to Display a Time-Scale Axis for the rather complex stepsneeded to plot data by periods smaller than a day. Dates that you need to appear as text in order to draw in a decorative element The case study, Using a Decorative Element in a chart , later in this chapter, shows achart by designer Kyle Fletcher in which the dates are forced to be of these situations is discussed in the following Text Dates to DatesIf your cells contain text that looks like dates, the date-based axis will not work. In , the data came from a legacy computer system. Each date was imported as text insteadof as 3 Creating Charts that show Trends86 Figure dates arereally text, as indi-cated by the apos-trophe before thedate in the is a frustrating problem because text dates look exactly like real dates.

9 You may notnotice that they are text dates until you see that changing the axis to a date-based axis hasno effect on the axis you select a cell that looks like a date cell, look in the formula bar, and see an apostrophebefore the date, you know you have text dates (refer to Figure ). This is Excel s arcanecode to indicate that a date or number should be stored as text instead of a a new format from the Format Cells dialog does not fix this problem, but it may preventyou from fixing the problem! If you import your data from a .txtfile and choose to format that col-umn as text, Excel changes the numeric format for the range to be text. After a range is formatted astext, you can never enter a formula, a number, or a date in the range. People try to select the range,change the format from text to numeric or date, and hope that this will fix the problem, but 3/27/07 12:57 PM Page 8687 Understanding a Date-Based Axis Versus a Category-Based AxisUnderstanding How Excel Stores Dates and TimeOn a Windows PC, Excel stores dates as the number of days since January 1, 1900.

10 For adate such as 9/15/2007, Excel actually stores the value 39,340, but it formats the date toshow you a value such as 09/15 a Mac running Mac OS, Excel stores the dates as the number of days since January 1,1904. The original designers of the Mac OS were trying to squeeze the OS into 64K ofROM. Since every byte mattered, it seemed unnecessary to add a couple lines of code tohandle the fact that 1900 is not a leap year. Excel for the Mac adopted the 1904 conven-tion. Excel for Windows, which needed to be compatible with Lotus 1-2-3, adopted the1900 convention. As you will read in the next case study, the 1900 convention incorrectlymade 1900 a leap t. After you change the format, you still have to use a method described in the section Converting Text Dates to Real Dates, later in this chapter, to convert the text dates to numeric , it is still worth changing the format from a text format to anything else. If you do notchange the format, and you then insert a new column to the right of the bad dates, the new columninherits the text setting from the date column.