Hence, we need a secondary axis in order to plot the two lines in the same chart. Displaying multiple time series in an Excel chart is not difficult if all the series use the same dates, but it becomes a problem if the dates are different, for example, if the series show monthly and weekly values over the same span of time.The present y-axis line is having much higher values and the percentage line will be having values lesser than 1 i.e. The current article describes a special case of this, in which the X values are dates. I hear youyou might.I recently showed several ways to display Multiple Series in One Excel Chart.Any of the formatting described here applies to all of these chart types.Excel just does not do it. Date Axis formatting is available for the X axis (the independent variable axis) in Excel’s Line, Area, Column, and Bar charts for all of these charts except the Bar chart, the X axis is the horizontal axis, but in Bar charts the X axis is the vertical axis. The steps to add a secondary axis are as follows : 1.This discussion mostly concerns Excel Line Charts with Date Axis formatting.While the data may span a similar range of dates, the different data sets may have varying intervals between recorded values. Displaying Multiple Time Series in An Excel ChartThe usual problem here is that data comes from different places. I could write a book just on this subject. If you are a super advanced excel guru, you can get another set of XY Scatter charts are different: X axes behave like Y axes. With excel you can have either clustered column bar charts, or stacked column bar charts, but not both.
Excel Add Secondary Axis Series In AnExcel only gives us the secondary vertical axis, and we really needed the secondary horizontal axis. So let’s assign the weekly data to the secondary axis (below left). Select and copy the weekly data set, select the chart, and use Paste Special to add the data to the chart (below right).To get to Paste Special, on the Home tab, click on the Paste dropdown, select Paste Special, and make sure you’ve selected the settings below:Excel’s line charts use the same data for all series in the chart, or more precisely, for all series on a particular axis. Excel has detected the dates and applied a Date Scale, with a spacing of 1 month and base units of 1 month (below left). Date Axis Base UnitsAmong the options for formatting a Date Axis are the units. So let’s look at these base units. With Base Unit of Months, Excel plots everything in a month at one horizontal position, so all weekly values in January are plotted with the monthly value for January 1. If there is no data point for a given slot, the line connecting points would have extended across the unpopulated slot. There is one slot for each month, the slot’s label is centered within the slot, and the slot’s data point is also centered. If base unit is Days, then there will be a slot on the axis for each date within the span of the axis if base unit is Months, then there is one slot per month along the axis if base unit is Years, well, you get the picture.I’ve used a darker line for the axes in the two charts below, I’ve formatted the major ticks to cross the axis and the minor ticks to lie outside the axis, and I’ve added faint droplines to the points, all to illustrate this concept.The chart below left uses Months for its base unit. Base units are the categories that Excel uses to handle the dates in the data. As before, copy the weekly data, and use Paste Special to add it as a new series to the char. But let’s set our base unit to Days (below left). Line Chart 2 – Plot by DayLet’s start again by plotting the monthly data in a line chart. Hide the secondary horizontal axis by formatting it to use no line and no labels (below right).This is pretty good, but it’s a bit complicated, and if we have a third data set with different dates, we have no more axes to plot it on. Delete the secondary vertical axis, and all data will be plotted on the primary scale (which was the same anyway). Finally, you can do a little clean-up. Use the “+” icon floating beside the chart (Excel 2013 and later) or the Axis controls on the ribbon toadd the secondary horizontal axis (below right).Rescale the secondary horizontal axis so it matches the primary: make sure the minimum and maximum units are the same (below left). Excel at first only draws the secondary vertical axis (below left). Free program gamehouse games sallys spa crack only ind zipReformat the horizontal axis so it scales from 1/1 to , and pick a reasonable major unit (below right).But look at the tick labels. The reasonable major unit of 30 days gives me funny dates: 1/1, 1/31, 3/1, and 3/31. No matter, will fix it in post.Copy the weekly data, and use the by now ultrafamiliar Paste Special to add it to the chart (below left). So an XY Scatter chart isn’t as smart as a line chart when picking dates. Those nice dates that go from to are actually values that go from 42370 to 42461.If we reformat our chart’s axis to show General numbers, the scale of 42360 to 42480 looks reasonable (see below). I’ve duplicated the data in columns A to E in columns G to K, but I’ve formatted the dates as General numbers in columns G and J. Make the plot area a bit narrower so the date labels are centered under the markers. Delete the Axis legend entry (click once to select the legend, again to select the Axis entry, then click Delete). If we just set the axis to show no labels, the margin below the axis would have collapsed, but using this dummy number format uses a space character for each label, preserving the space for our replacement labels (below left).Use Copy – Paste Special to add the new axis data to the chart as a new series (below right).Add data labels below this new series (below left), and format the labels to show X Value, not Y Value (below right).Format the Axis series so it uses no line and a gray cross marker (below left). Here is the data for our dummy series, with X values for the first of each month and Y values of zero so it rests on the bottom of the chart.Hide the axis labels by using a custom number format of ” ” (a space surrounded by quotes). Again, an XY Scatter chart isn’t so smart with dates, despite its flexibility in other ways.Well, we can hide the axis labels and add a dummy series with data labels that provide the dates we want to see. The data below shows four unevenly-spaced points per day over a two day span. When To Use XY Charts for Timeline DataIf you don’t need monthly increments along the X axis, then is makes sense to use an XY Scatter chart for your time series.A more important case for using an XY Scatter chart for a timeline is when the spacing of points is on the order of hours or less, rather than days.
0 Comments
Leave a Reply. |
AuthorSelwyn ArchivesCategories |