Right-click (control-click on a Mac) and drag a date field from the Data window to the Filters shelf, and then click Relative Date in the Filter Field dialog box. Except for axis tick labels, text elements in an Excel chart can be linked to worksheet cells. The screen shot below shows a chart, the chart source data range .. that a graph update can require–relative to using an intermediary cell, .. Broken Y Axis in an Excel Chart · Grouping by Date in a Pivot Table. In the old days, like around Excel 4, Excel only had sheets, not workbooks, A chart had to fully reference its data source by worksheet name, .. year to date to avoid the plotted line suddenly going from a value down to zero.
- Understanding a Date-Based Axis Versus a Category-Based Axis
- Excel Formula Training
- Primary Sidebar
Make a Copied Chart Link to New Data
Link Chart Text to a Cell
The additional chart in Figure 3. The text version of a date can take a number of different formats. Below we see two windows each for two workbooks.
Excel chart source data relative dating - Reader Interactions
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 be 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.
To create the chart in Figure 3. Insert a blank column B to hold the transformed dates. This formula converts the date to a date. You will later add a title to indicate that the dates are in the s. Double-click the fill handle in cell B4 to copy the formula down to all cells. Select the range B3: From the Layout ribbon, choose Legend, No Legend.
Right-click the vertical axis along the left side of the chart and choose Format Axis from the context menu. In the Format Axis dialog that appears, on the Axis Options page, choose the Fixed option button next to Minimum and enter a fixed value of Without closing the Format Axis dialog, click the dates in the horizontal axis in the chart.
Excel automatically switches to formatting the horizontal axis, and the settings in the Format Axis dialog redraw to show the settings for the horizontal axis.
In the Axis Type section, choose Date Axis. Click Close to close the dialog box. Click the State Count title. 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 ribbon, click the Decrease Font Size button.
Click the Left Align button. Carefully click the border of the title. Drag it so it appears in the top-left corner of the chart. Select the dates in B4: On the Number tab, click the Custom category. Type the custom number format 'yy.
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. This method fails when you are trying to display more than years of data points.
Using a Date-Based Axis with Dates Before Spanning More Than Years If you attempt to use the technique described in the preceding section on a chart that contains more than years' worth of dates, the technique will fail. While 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. To do so, you must transform your date axis into a scale that shows months, hide the axis, and add your own axis, using text boxes.
These steps are not for the faint of heart. You first need to transform the dates from the s to the s. You then 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 six years. You then need to use care to completely hide the labels along the horizontal axis and to replace them with text boxes showing the centuries. You then add a new data series to draw vertical lines at the change of each century.
Insert new columns B and C. This transforms the dates from to a valid Excel date in Format this cell with a short date format. Format this cell as a short date. This formula now reduces years into x12 into 2, days, where each day represents 1 month of real time. C4 and double-click the fill handle to copy the formula down to your range of data. The dates in column B span to The dates in column D span to Although the relative position of the data points is correct, you have to hide the axis labels that Excel draws in for the horizontal axis.
It would therefore be helpful to draw in vertical lines to show where the axis switches from the s to the s and another line to show where the axis switches from the s to the s.
Insert a new column E to hold the data for the second series. This series contains just two nonzero points: Enter the heading Divide Line in cell E3. Look through the dates in column A. Insert a new row before the first date in the s. Copy the formulas in columns B and C. In column D, copy the point from the row above.
In column E, enter the value This draws a single vertical bar from the horizontal axis up to a height of Repeat step 6 to add a new data point for January 1, , and January 1, From the Insert ribbon, choose Charts, Line, Line. On the Layout ribbon, choose Legend, None.
Right-click the numbers along the vertical axis and choose Format Axis. Change the Maximum option button to Fixed and enter the value This changes the vertical axis to show from 0 to On the Layout ribbon, use the Current Selection drop-down to select Series.
There are now only two data points selected in the chart. On the Design ribbon, choose Change Chart Type. Select the first icon in the column section—for a clustered column chart.
This draws narrow columns—actually lines—at and on the chart. Note that the chart type change affects only the second series because you selected the Divide Line series in step Click the labels along the horizontal axis.
On the Home ribbon, from the Font Color drop-down choose a white font. This causes the axis labels to disappear. On the Insert ribbon, click the Text Box icon. On the chart, draw a text box from the line to the line, just below the horizontal axis.
The mouse pointer changes into a crosshairs as you draw. You can make sure the vertical line in the crosshairs corresponds to the vertical dividing lines. After you create the text box, a flashing cursor appears inside the text box. Click the edge of the text box to change it from a dashed line to a solid line.
While the text box is selected, choose Center Align from the Home ribbon. Choose Vertical Center Align. Choose Increase Font Size from the Home ribbon. While the text box is still selected, choose Format, Shape Outline, Black on the Layout ribbon in order to outline the text box. Click the text box and start to drag to the right.
After you start to drag, hold down the Shift key to constrain the movement to the right. Hold down the Ctrl key to make an identical copy of the text box. When the left edge of the new text box is aligned with the vertical line at , release the mouse button. Click in the text box and change the text from s to s.
The title Chart Title appears, and it is selected. Click inside the Chart Title text area to enter Text Entry mode.
Overwrite the default text in the title by typing Growth of USA, pressing Enter, typing by of States, pressing Enter, and typing Click on the border of the chart title to exit Text Entry mode. Drag the chart title to a new location in the lower-right corner of the chart. The result is a chart that appears to show a line chart that spans years. The line is appropriately scaled, using a date-based axis. 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. The data in Figure 3. 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, 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 The top chart in Figure 3. Because each customer arrival or departure merits a new point, the one hour from noon until 1 p.
It sounds like a perfect use for a time-series axis, right? 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. Perhaps each hour could be represented by a single year. 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. You follow these steps to create a chart that appears to have a time-based axis: NOTE 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. In cell L2, enter the following formula to translate the time to a date: This returns values from through The other arguments in the date function are 1 and 1 to return January 1 of the year. The entire formula is rounded to the nearest integer because Excel would normally ignore any time values. Double-click the fill handle to copy this formula down to all the data points.
The results of this formula ranges from January 1, representing the customer who walked in at 10 a. I hope that after studying Software Quality Metrics SQM data for Excel , Microsoft finally realizes that million people instantly turn off the legend in every chart that has a single data series. Right-click the labels along the horizontal axis and choose Format Axis to display the Format Axis dialog box, where you make the following selections: For Major Unit, choose Fixed, 1 Years.
For Minor Unit, choose Fixed, 1 Days. For Base Unit, choose Fixed, Days. Click Close to close the Format Axis dialog. Return to the transformed dates in column L. On the Number tab, choose the Custom category. A custom number format of yy would display 10 for and 15 for Instead, use a custom number format of yy": This causes Excel to display This is fairly sneaky, eh?
As you can see in Figure 3. This is an improvement in accuracy over either of the charts in Figure 3. The additional chart in Figure 3. If my bank offered minute wait times, I would be finding a new bank. Converting Dates to Text to Add a Decorative Chart Element There are times when you want to force the category axis to be a text-based axis so that you have a bit more control.
In the following case study, professional designer Kyle Fletcher used Adobe Illustrator to produce a chart for a music industry publication. Kyle needed to separate sales before and after a specific event.
Using Illustrator, Kyle drew his chart and created a gap between the pre-event and post-event columns. In order to replicate this effect in Excel, you need to use a text-based axis. Designing Charts Like The Pros: Using a Decorative Element in a Chart When commissioned to create a chart for a record industry publication, designer Kyle Fletcher of www.
This chart is mostly devoid of chartjunk; it has no gridlines and no axis lines. The only extra element is a separator line used to break the chart into two sections. This separator line takes up the width of an entire column and serves to break the single chart into two charts.
You follow these steps to create a chart like this one: Insert a blank column to the right of the year-ending dates. Converting from dates to numbers ensures that Excel chooses a text-based axis. While B2 is selected, choose the number format from the drop-down in the Number group on the Home ribbon. Click the Decrease Decimal icon twice to remove the decimals. Insert a blank row between and For the year, enter a space. For the sales column, leave the column blank. Change the Sales heading in C1 to John Denver.
This will be the start of the title. The initial chart appears as shown in Figure 3. Left-click the numbers in the vertical axis. This removes the vertical line next to the numbers. With the axis still selected, use the Font drop-down on the Home ribbon to choose the Rockwell font. Change the font color to orange. Right-click a number along the vertical axis and choose Format Axis.
In the Format Axis dialog that appears, specify the following: For Minimum, select Fixed, 0. For Maximum, select Fixed, For Major Unit, select Fixed, Click the Close button. Excel now displays only 0, ,, and , along the vertical axis. Left-click one of the columns to select the data series. If the cell contents are changed, the chart title will change accordingly. Use the same protocol twice more to customize the X and Y axis titles. Link Data Labels If you are going to link one or two labels, use the protocol shown here.
If you are going to link whole series at a time, this protocol becomes tedious very quickly. Fortunately this can be automated through VBA, and there are several utilities around the internet that do this for you.
They install quickly and interface smoothly with Excel. Add a data label to the first point in the series. Select the series with one click, then select the point with another click. Select any of the label options, such as Series Name.
The label has been moved from its default Right position to Above. Select the series of labels with one click, then select the specific label with another click.
Press Enter and the data label updates. Add a data label to the last point in the series, and link it to a cell using this process. Link Shapes Linking a shape to a cell is a bit trickier. Start by adding a shape to the chart here the shape is an oval. Instead of the selected shape picking up the link, Excel adds a new textbox with the link.
A link can be added to the shape using VBA, so I wrote a short procedure to make it happen.