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.
The bottom chart uses cells in which the text dates have been converted to numeric dates. The bottom chart reflects a date-based axis. There are other methods for converting the data shown in Figure 3.
Subscribe to RSS
Here are two methods:. Method 1: Select any empty cell. Select your dates. On the Home tab, select Paste, Paste Special. Click OK. The text dates will convert to dates.
Barnard Library and Academic Information Services (BLAIS)
Method 2: Select the text dates and then, on the Data tab, select Text to Columns, and then click Finish. 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 dataset where each date was listed as a range of dates. I had to check with the author of the data to find out if they meant February 4th through 6th of or if they meant February 4th through June 11th.
They meant the former. Used in combination, the functions listed below can be useful when you are converting strange text dates to real dates:. Follow these steps to convert the text date ranges shown in Figure 3. 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. Column A contains four-digit years such as , , 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 to is the same as the distance from to In Excel , dates from January 1, through December 31, 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 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. 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 as valid dates. Possible workarounds are discussed in the next two subsections. Excel cannot automatically deal with dates from the s, but it can deal with dates from the s. 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 s. However, keep in mind that this solution fails when you are trying to display more than years of data points. To create the chart in Figure 3. The result is the chart shown in Figure 3. The reader may believe that the chart is showing dates in the s, but Excel is actually showing dates in the s.
Although I managed to create a date-based axis covering years with 10 data points, a dataset covering years and 40 points cannot display a date-based axis. However, as Figure 3. To do so, you must transform the date axis into a scale that shows months, hide the axis, and then add your own axis using text boxes. These steps are not for the faint of heart. First, you need to transform the dates from the s to the s. Next, you will transform the dates spanning years into a range where each month in real time is represented by a single day.
This results in a time span of 6 years. You then need to use care to completely hide the labels along the horizontal axis and replace them with text boxes showing the centuries. Lastly, you add a new data series to draw vertical lines at the change of each century. Microsoft interprets Ctrl-click as the shortcut to select an object's container.
The result is a chart that appears to show a line chart that spans years. The line is scaled appropriately using a date-based 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 Column A, it logs the time that customers entered a busy bank. Times range from when the bank opened at 10 a. After you enter planned staffing levels in Column C, the model calculates when the customer will move from the queue to an open teller window and when he or she will leave the window based on an average of three minutes per transaction. Data in Columns I:M record 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 hour, while many customers enter the bank during the lunch hour. The top chart in Figure 3. Because each customer arrival or departure merits a new point, the one hour from noon until 1 p.
In reality, this 1-hour period merits only 16 percent of the chart.
This sounds like a perfect use for a time-series axis, right? Read on for the answer. 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 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 s date example in the preceding section.
For example, perhaps each hour could be represented by a single year. Using numbers from a hour clock, the hour could be represented by and the hour could be represented by In this example, you manipulate the labels along the vertical axis using a clever custom number format.
How does one add an axis label in Microsoft Office Excel ? - Super User
A few new settings on the Format Axis dialog ensure that an axis label appears every hour. In the original chart, a time appeared in Column I, and a formula in Column L simply copied this time so that it would be adjacent to the customer count in Column M. In step 1, the transformation formula is applied to Column L. These options include those that fix the maximum and minimum amount for the first and last tick mark on the axis, display the values in reverse order highest to lowest , apply a logarithmic scale, display units on the axis hundreds, thousands, and millions, and so forth and divide the values by those units, reposition the tick marks on the axis, and modify the value at which the other axis crosses.
For example, to select the Number format with the comma as the thousands separator and no decimal places, you select Number in the Category list box; then leave the Use Separator , check box selected and enter 0 in the Decimal Places text box. Indicate the new orientation by clicking the desired vertical alignment in the Vertical Alignment drop-down list box and desired text direction in the Text Direction drop-down list.
As you choose new options for the selected axis, Excel shows you the change in the chart. These changes are, however, set in the chart only after you click Close in the Format Axis dialog box. Customizing the Type and Style of an Excel Chart.