- Choosing a Chart Type
- Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts
- Communicate Effectively with Charts
- Adding an Automatic Trendline to a Chart
- Showing a Trend of Monthly Sales and Year-to-Date Sales
- Understanding the Shortcomings of Stacked Column Charts
- Shortcomings of Showing Many Trends on a Single Chart
- Next Steps
This chapter is from the book
This chapter is from the book
This chapter is from the book
Understanding Date-Based Axis Versus Category-Based Axis in Trend Charts
Excel offers two types of horizontal axes in a trend chart. Having the proper setting can ensure that your message is accurate.
If the spacing of events along the time axis is uniform, it does not matter whether you choose a date-based axis or a text-based axis because the results will be the same. When this occurs, it is fine to allow Excel to choose the type of axis automatically.
However, if the spacing of events along the time axis is haphazard, you definitely want to make sure that Excel uses a date-based axis.
Accurately Representing Data Using a Time-Based Axis
Figure 3.6 shows the spot price for a certain component used in your manufacturing plant. To find this data, you downloaded past purchase orders for that product. Your company doesn’t purchase the component on the same day every month; therefore, you have an incomplete data set. In the middle of the data set, a strike closed one of the vendors, spiking the prices from the other vendors. Your purchasing department had stocked up before the strike, which allowed your company to slow its purchasing dramatically during the strike.
)
Figure 3.6. The top chart uses a text-based horizontal axis: Every event is plotted an equal distance from the next event. This leads to the shaded period being underreported.
In the top chart in Figure 3.6, the horizontal axis is set to a text-based axis, and every data point is plotted an equal distance apart. Because your purchasing department made only two purchases during the strike, it appears the time affected by the strike is very narrow. The bottom chart uses a date-based axis. In this axis, you can see that the strike actually lasted for half of 2013.
Usually, if your data contains dates, Excel defaults to a date-based axis. However, you should always check to make sure Excel is using the correct type of axis. A number of potential problems force Excel to choose a text-based axis instead of a date-based axis. For example, Excel chooses a text-based axis when dates are stored as text in a spreadsheet and when dates are represented by numeric years. The list following Figure 3.7 summarizes other potential problems.
)
Figure 3.7. You can explicitly choose an axis type rather than letting Excel choose the default.
To explicitly choose an axis type, follow these steps:
- Right-click the horizontal axis and select Format Axis.
- In the Format Axis task pane that appears, select the Axis Options at the top, then the chart icon, and then expand the Axis Options category.
- As appropriate, choose either Text Axis or Date Axis from the Axis Type section (see Figure 3.7).
A number of complications that require special handling can occur with date fields. The following are some of the problems you might encounter:
- Dates stored as text—If dates are stored as text dates instead of real dates, a date-based axis will never work. You have to use date functions to convert the text dates to real dates.
- Dates represented by numeric years—Trend charts can have category values of 2008, 2009, 2010, and so on. Excel does not naturally recognize these as dates, but you can trick it into doing so. Read “Plotting Data by Numeric Year” near Figure 3.15 in this chapter.
- Dates before 1900—If your company is old enough to chart historical trends before January 1, 1900, you will have a problem. In Excel’s world, there are no dates before 1900. For a workaround, read “Using Dates Before 1900” near Figure 3.16.
- Dates that are really time—It is not difficult to imagine charts in which the horizontal axis contains periodic times throughout a day. For example, you might use a chart like this to show the number of people entering a bank. For such a chart, you need a time-based axis, but Excel will group all the times from a single day into a single point. See “Using a Workaround to Display a Time-Scale Axis” near Figure 3.19 for the rather complex steps needed to plot data by periods smaller than a day.
Each of these problem situations is discussed in the following sections.
Converting Text Dates to Dates
If your cells contain text that looks like dates, the date-based axis does not work. The data in Figure 3.8 came from a legacy computer system. Each date was imported as text instead of as dates.
){kind=link}
This is a frustrating problem because text dates look exactly like real dates. You may not notice that they are text dates until you see that changing the axis to a date-based axis has no effect on the axis spacing.
If you select a cell that looks like a date cell, look in the formula bar to see whether there is an apostrophe before the date. If so, you know you have text dates (see Figure 3.8). This is Excel’s arcane code to indicate that a date or number should be stored as text instead of a number. Or, if the number format drop-down on the Home tab indicates that the cell is formatted as text, then you might have text dates.
Figure 3.8. These dates are really text, as indicated by the apostrophe before the date in the formula bar.
)
Figure 3.9. Many groups on the ribbon have this tiny Dialog Launcher icon in the lower-right corner. Clicking this icon leads to the legacy dialog box.
Excel provides a complete complement of functions to deal with dates, including functions that convert data from text to dates and back. Excel stores times as decimal fractions of days. For example, you can enter noon today as =TODAY()+0.5 and 9:00 a.m. as =TODAY()+0.375. Again, the number format handles converting the decimals to the appropriate display.
Converting Text Dates to Real Dates
The DATEVALUE function converts text that looks like a date into the equivalent serial number. You can then use the Format Cells dialog to display the number as a date.
The text version of a date can take a number of different formats. For example, your international date settings might call for a month/day/year arrangement of the dates. Figure 3.10 shows a number of valid text formats that can be converted with the DATEVALUE function.
)
Figure 3.10. The DATEVALUE function can handle any of the date formats in column A.
Figure 3.11 shows a column of text dates. Follow these steps to convert the text dates to real dates:
){kind=link}
- Insert a blank column B by selecting cell B1. Select Home, Insert, Insert Sheet Columns. Alternatively, you can use the legacy shortcut Alt+I+C.
- In cell B2, enter the formula =DATEVALUE(A2). Excel displays a number in the 40,000 range in cell B2. You are halfway to the result. You still have to format the result as a date.
- Double-click the fill handle in the lower-right corner of cell B2. The fill handle is the square dot in the lower-right corner of the active cell indicator. Excel copies the formula from cell B2 down to your range of dates.
- Select Column B2. On the Home tab, select the drop-down at the top of the Number group and choose either Short Date or Long Date. Excel displays the numbers in Column B as a date (see Figure 3.12). Alternatively, you can press Ctrl+1 and select any date format from the Number tab. If some of the dates appear as #######, you need to make the column wider. To do so, double-click the border between the column B and column C headings.
- To convert the live formulas in column B to static values, while the range of dates in Column B is selected, press Ctrl+C to copy. Press Ctrl+V to paste. Press Ctrl to open the Paste Options dialog. Press V to paste as values.
- Delete the original column A.
Figure 3.11. The result of the DATEVALUE function is a serial number.
Figure 3.12. Choose a date format from the Number drop-down on the Home tab.
There are other methods for converting the data shown in Figure 3.11 to dates. Here are two methods:
){kind=link}
- Method 1—Select any empty cell. Press Ctrl+C to copy. Select your dates. On the Home tab, select Paste, Paste Special. In the Paste Special dialog, choose Values in the Paste section and Add in the Operation section. Click OK. The text dates convert to dates.
- Method 2—Select the text dates, select Text to Columns on the Data tab, and then click Finish.
After converting the text dates to real dates, insert a line chart with markers. Excel automatically formats the chart with a date-based axis. In Figure 3.13, the top chart reflects cells that contain text dates. The bottom chart uses cells in which the text dates have been converted to numeric dates.
)
Figure 3.13. When your original data contains real dates, Excel automatically chooses a more accurate date-based axis. The bottom chart reflects a date-based axis.
Converting Bizarre Text Dates to Real Dates
When you rely on others for source data, you are likely to encounter dates in all sorts of bizarre formats. For example, while gathering data for this book, I found a data set where each date was listed as a range of dates. Each date was in the format 2/4-6/15. I had to check with the author of the data to find out if that meant February 4th through 6th of 2015 or February 4th through June 15th. It was the former.
Used in combination, the functions in the following list can be useful when you are converting strange text dates to real dates:
- =DATE(2015,12,31)—Returns the serial number for December 31, 2015.
- =LEFT(A1,2)—Returns the two leftmost characters from cell A1.
- =RIGHT(A1,2)—Returns the two rightmost characters from cell A1.
- =MID(A1,3,2)—Returns the third and fourth characters from cell A2. You read the function as “return the middle characters from A1, starting at character position 3, for a length of 2.”
- =FIND("/",A1)—Finds the position number of the first slash within A1.
Follow these steps to convert the text date ranges shown in Figure 3.14 to real dates:
)
Figure 3.14. A mix of LEFT, RIGHT, MID, and FIND functions parse this text to be used in the DATE function.
- Because the year is always the two rightmost characters in column A, enter the formula =RIGHT(A2,2)+2000 in cell B2.
- Because the month is the leftmost one or two characters in column A, ask Excel to find the first slash and then return the characters to the left of the slash. Enter =FIND("/",A2) to indicate that the slash is in second character position. Use =LEFT(A2,FIND("/",A2)-1) to get the proper month number.
- For the day, either choose to extract the first or last date of the range. To extract the first date, ask for the middle characters, starting one position after the slash. The logic to figure out whether you need one or two characters is a bit more complicated. Find the position of the dash, subtract the position of the slash, and then subtract 1. To see the formula in action, select the cell and choose Formulas, Evaluate Formula, and click Evaluate 10 times. Use this formula in cell D2:
=MID(A2,FIND("/",A2)+1,FIND("-",A2)-FIND("/",A2)-1) - Use the DATE function as follows in cell E2 to produce an actual date:
=DATE(B2,C2,D2)
Plotting Data by Numeric Year
If you are plotting data where the only identifier is a numeric year, Excel does not automatically recognize this field as a date field.
For example, in Figure 3.15 data are plotted once a decade for the past 50 years and then yearly for the past decade. Column A contains four-digit years, such as 1960, 1970, and so on. The default chart shown in the top of the figure does not create a date-based axis. You know this to be true because the distance from 1960 to 1970 is the same as the distance from 2000 to 2001.
)
Figure 3.15. Excel does not recognize years as dates.
Listed here are two solutions to this problem:
- Convert the years in column A to dates by using =DATE (A2,12,31). Format the resulting value with a yyyy custom number format. Excel displays 2015 but actually stores the serial number for December 31, 2015.
- Convert the horizontal axis to a date-based axis. Excel thinks your chart is plotting daily dates from May 13, 1905, through July 2, 1905. Because no date format has been applied to the cells, they show up as the serial numbers 1955 through 2005. Excel displays the chart properly, even though the settings show that the base units are days.
Using Dates Before 1900
In Excel 2013, dates from January 1, 1900 through December 31, 9999 are recognized as valid dates. However, if your company was founded more than a demisesquicentennial before Microsoft was founded, you will potentially have company history going back before 1900.
Figure 3.16 shows a data set stretching from 1787 through 1959. The accompanying chart would lead the reader to believe that the number of states in the United States grew at a constant rate. This inaccurate statement would cause Mr. Kessel, my eighth-grade geography teacher, to give me an F for this book.
)
Figure 3.16. Dates from before 1900 are not valid Excel dates. A date-based axis is not possible in this case.
As mentioned previously, formatting the chart to have a date-based axis will not work because Excel does not recognize dates before 1900 as valid dates. Possible workarounds are discussed in the next two subsections.
Using Date-Based Axis with Dates Before 1900
In Figure 3.17, the dates in Column A are text dates from the 1800s. Excel cannot automatically deal with dates from the 1800s, but it can deal with dates from the 1900s.
)
Figure 3.17. Transforming the 1800s dates to 1900s dates and clever formatting allows Excel to plot this data with a date axis.
One solution is to transform the dates to dates in the valid range of dates that Excel can recognize. You can use a date format with two years and a good title on the chart to explain that the dates are from the 1800s. However, keep in mind that this solution fails when you are trying to display more than 100 years of data points.
To create the chart in Figure 3.17, follow these steps:
- Insert a blank column B to hold the transformed dates.
- Enter the formula =DATE(100+RIGHT(A4,4),LEFT(A4,2),MID(A4,4,2)) in cell B4. This formula converts the 1836 date to a 1936 date.
- Select cell B4. Press Ctrl+1 to open the Format Cells dialog. Select the date format 3/14/01 from the Date category on the Number tab. This formats the 1936 date as 6/15/36. Later, you will add a title to indicate that the dates in this column are from the 1800s.
- Double-click the fill handle in cell B4 to copy the formula down to all cells.
- Select the range B3:C17.
- From the Insert tab, open the Line Chart drop-down and choose the first line chart icon.
- Double-click the vertical axis along the left side of the chart to display the Format Axis task pane.
- In the task pane, click the Chart icon.
- For the Minimum value, type a value of 20.
- Click the dates in the horizontal axis in the chart. Excel automatically switches to formatting the horizontal axis, and the settings in the Format task pane redraw to show the settings for the horizontal axis. In the Axis Type section, select Date Axis.
- From the Plus icon to the right of the chart, hover over Chart Title and then click the arrowhead that appears. Select Centered Overlay Title.
- Click the State Count title. Type the new title Westward Expansion<enter>During 1845-1875 Added 13<enter>New States to the Union. Click outside the title to exit Text Edit mode.
- Click the title once. You should have a solid selection rectangle around the title. On the Home tab, click the Decrease Font Size button. Click the Left Align button.
- Carefully click the border of the title. Drag it so the title appears in the top-left corner of the chart.
- Select the dates in B4:B17. Press Ctrl+1 to access the Format Cells dialog. On the Number tab, click the Custom category. Type the custom number format 'yy. This changes the values shown along the horizontal axis from m/d/yy format to show a two-digit year preceded by an apostrophe.
The result is the chart shown in Figure 3.17. The reader might believe that the chart is showing dates in the 1800s, but Excel is actually showing dates in the 1900s.
Rolling Daily Dates to Months Using a Pivot Chart
Suppose that you have daily data that you would like to summarize by month. You might think that you can use the Units drop-downs in the Format Axis task pane, under the Chart icon, Axis Options to solve the problem. Here you’ll see three settings:
- Major Units: N Days/Months/Years
- Minor Units: N Days/Months/Years
- Base: Days/Months/Years
In the first two bullet points, N is the number entered in the text box.
The Major Units setting is useful for controlling the labels along the date axis. The top-left chart in Figure 3.18 is plotting one point per day, but the labels are readable because the Major unit is set to 1 month instead of 1 day.
)
Figure 3.18. The settings in the Format Axis task pane control the axis labels, but cannot summarize by months.
The lower-left chart is also using a Base of Days, but the Major Unit is 2 Months. Using the Number category at the bottom of the task pane, adjust the date format to mmm and you get month abbreviations instead of individual dates.
If you attempt to change the Base field from Days to Months, the result is not a summary by month, as you might have expected. The-top right chart is a Line with Markers chart where the Line is set to No Line so you can see the points. By rolling the Base field to Months, Excel plots all the points that occurred in January in a single column. This might be interesting for showing the range of daily sales, even perhaps the distribution of daily sales. But it is not a method for summarizing the daily dates.
Excel can roll the daily dates up and summarize by month, but you need to start with a pivot chart instead of a regular chart. Pivot charts are covered in detail in Chapter 8, “Creating PivotCharts and Power View Dashboards.” Here are the steps to create the bottom-right chart in Figure 3.18.
- Select the range of sales by date.
- Choose Insert, PivotChart.
- The Create PivotChart dialog will offer to create the chart on a New Worksheet. Because a pivot chart includes an associated summary pivot table, it is best to build the chart on a new worksheet and then move the chart when it is done. Accept the default settings in the Create PivotChart dialog and click OK.
- A new worksheet is inserted to the left of the old worksheet. A PivotTable Fields task pane appears with a list of field. Check both the Date and Sales field. You end with a table of daily dates, identical to what you started with.
- In the Excel worksheet, choose a cell in the new table that contains a date. The ribbon changes to show a tab called PivotTable Tools Analyze. The third group in this ribbon tab is called Group. Choices there include Group Selection and Group Field. Be sure that you have a date cell selected and choose Group Field. Excel displays the Grouping dialog box. You can see this dialog in the inset of the bottom-right chart of Figure 3.18.
- Select Months (or Quarters) from the Grouping dialog. If your data spans more than one year, be sure to include years. To group by Week, choose only Days and then use the Number of Days spin button to go to seven-day periods. Click OK.
The pivot table and pivot chart changes to show one column per month instead of daily dates. You can use normal chart formatting to customize the chart. After the chart is formatted, right-click the chart and choose Move Chart to move to the desired worksheet.
Using a Workaround to Display a Time-Scale Axis
The developers who create Microsoft Excel are careful in the Format Axis dialog box to call the option a date axis. However, the technical writers who write Excel Help refer to a time-scale axis. The developers get a point here for accuracy, because Excel absolutely cannot natively handle an axis that is based on time.
A worksheet in the download files is used to analyze queuing times. In the first column, it logs the time that customers entered a busy bank. Times range from when the bank opened at 10:00 a.m. until the bank closed at 4:00 p.m.
Based on the staffing level and the number of customers in the bank, further calculations determine when the customer will move from the queue to an open teller window and when the customer will leave the window, based on an average of three minutes per transaction.
A summary range records the number of people in the bank every time someone enters or leaves. This data is definitely not spaced equally. Only a few customers arrive in the 10:00 hour, whereas many customers enter the bank during the lunch hour.
The top chart in Figure 3.19 plots the number of customers on a text-based axis. Because each customer arrival or departure merits a new point, the one hour from noon until 1:00 p.m. takes up 41 percent of the horizontal width of the chart. In reality, this one-hour period merits only 16% of the chart. This sounds like a perfect use for a time-series axis, right? Read on for the answer.
)
Figure 3.19. Excel cannot show a time-series axis that contains times.
The bottom chart is an identical chart where the axis is converted to show the data on a date-based axis. This is a complete disaster. In a date-based axis, all time information is discarded. The entire set of 300 points is plotted in a single vertical line.
The solution to this problem involves converting the hours to a different time scale (similar to the 1800s date example in the preceding section). For example, perhaps each hour could be represented by a single year. Using numbers from a 24-hour clock, the 10:00 hour could be represented by 2010 and the 3:00 hour could be represented by 2015.
In this example, you manipulate the labels along the vertical axis using a clever custom number format. A few new settings on the Format Axis dialog ensure that an axis label appears every hour.
Follow these steps to create a chart that appears to have a time-based axis:
- In cell L2, enter the following formula to translate the time to a date:
=ROUND(DATE(HOUR(I2)+2000,1,1)+MINUTE(I2)/60*365,0)
Because each hour represents a single year, the years argument of the DATE function is =HOUR(I2)+2000. This returns values from 2010 through 2015. The month and day arguments in the date function are 1 and 1 to return January 1 of the year. Outside the date function, the minute of the time cell is scaled up to show a value from 1 to 365, using MINUTE(I2)/60*365. The entire formula is rounded to the nearest integer because Excel would normally ignore any time values.
- With cell L2 selected, press Ctrl+1 to display the Format Cells dialog. On the Number tab, choose the Custom category. In the Type box, enter yy“:00”. This format displays the year of the date with two digits. Hence, all dates in 2010 display as “10”. Excel appends whatever is in quotes after the year. Because you’ve put :00 after the year, a date in 2010 displays as 10:00. A date in 2015 displays as 15:00. It is up to you to convince your manager that you have to use the 24-hour clock.
- Select cell L2. Double-click the fill handle to copy this formula down to all the data points. The result of this formula ranges from January 1, 2010, which represents the customer who walked in at 10:00 a.m., to 12/25/2015, which represents the customer who walked in at 3:57 p.m. However, thanks to the formatting in step 2, you have a series of cells that all display 10:00, then more cells that display 11:00, and so on.
- Select cells L1:M303.
- From the Insert tab, select Recommended Charts, OK. The resulting chart is almost perfect. The only glitch: Each “hour” along the bottom axis appears three times.
- Double-click the labels along the horizontal axis to display the Format Axis task pane. Choose the Chart icon and then expand the Axis Options category. You see the Major Unit is set to 4 Months. Change the Months to Years. Change the 4 to 1. The chart now displays one label per year, which on the chart looks like one label per hour.
As you see in Figure 3.20, the chart now allocates one-sixth of the horizontal axis to each hour. This is an improvement in accuracy over either of the charts in Figure 3.19. The additional chart in Figure 3.20 uses a similar methodology to show the wait time for each customer who enters the bank. If my bank offered 12-minute wait times, I would be finding a new bank.
)
Figure 3.20. These charts show the number of customers in the bank and their expected wait times.