Microsoft Excel Charting Survey

Microsoft is conducting a survey to help plan new charting features for future versions of Excel. This is your chance to provide your thoughts and feedback. Here is the announcement from the Excel Team:

This survey is being conducted by the Microsoft Excel team. In the future, new charting features will be added to Excel. We want to better understand how you expect charts, that have new features, to be displayed and behave in older versions of Excel that don’t have these new features.

This survey will take approximately 10 minutes. Feedback from this survey will be used to improve the user experience in Excel. Responses to this survey will not be associated with any personal information. Please see the Microsoft Privacy Statement for further privacy details on all Microsoft products.

Thank you for participating,
Excel Team

Click for the Microsoft Excel Chart Survey

[NOTE: The survey has been closed]

 

Peltier Tech Chart Utility

VB Password Prompt when Closing Excel

The VB Password Prompt Problem

The VB Password Prompt is a (usually) rare problem that occurs when you are using a workbook or add-in with a password protected Visual Basic project. When you quit Excel, you are asked for a password to open the VB project.

VB Password Prompt

You may not know the password, so you click Cancel once, twice, seventeen times before the prompt is dismissed.

Note: Peltier Tech software does have such a password, but users do not need it to run the software.

This may affect Peltier Tech add-ins, or some other add-in. I’ve written about the VB Password Prompt as part of my documentation for Peltier Tech Charts for Excel.

It is not the fault of the add-in, but of another third-party software application that apparently does not properly release resources when it finishes with them. Windows or Excel or VBA gets confused, decides a given add-in is at fault, and asks for the password so it can open the code and clean up the mess. If you do know the password and enter it at the prompt, your computer checks the project, decides the problem is elsewhere, and proceeds to close Excel.

Debugging the problem is difficult, because it is not consistent across even nominally identical computers. You have to disable any software that could possibly have an effect, and then turn them on one by one until you find the one that causes the problem. Normally the “solution” is either to disable the culprit, or live with the problem.

Implicated Software

The kinds of software that may cause a spurious VB password prompt include applications that control printing, either to a physical printer or to pdf files, enterprise document management programs, database applications, and others. Adobe Acrobat has been implicated, as has Acrobat PDFMaker, though this was reported fixed in December 2013. Other third-party programs that have been blamed include ProjectWise, Palo, Hyperion SmartView, Tabs for Excel, Microstrategy, OmniJoin, CapitalIQ, and ManicTime.

Et tu, Dropbox?

A recent culprit in the VB Password Prompt bug is Dropbox. This is disturbing to me because I like Dropbox; among the various file syncing programs, I feel that Dropbox runs most smoothly and reliably. I use Google Drive more extensively than Dropbox, and it seems to work pretty well, with occasional hangs and closes. I used to have problems with Microsoft’s OneDrive, but I’ve heard they’ve made some reliability improvements, so I’ll be trying them out again. Nice to have options.

First reported in September 2015, the problem affects VB workbooks and add-ins that contain at least one UserForm. If the UserForm is displayed during operation of the workbook or add-in, then all open workbooks are closed, and then Excel is closed, the insidious password prompt appears. In some cases, clicking Cancel a number of times lets Excel close properly, but sometimes….

Microsoft Excel has stopped working

That’s no fun.

The Dropbox Solution

If you use Dropbox, there is a workaround. Dropbox includes a feature called Dropbox Badge, which is closely integrated into Microsoft Office, and allows advanced interaction among users working on the same Office files. Sounds good, except this feature seems to be causing the password prompt to appear. It’s easy enough to turn off Dropbox Badge:

  • Click on the Dropbox icon in the Taskbar
  • Click on the settings gear icon in the top right of the dialog
  • Choose Preferences
  • Under Dropbox Badge, choose ‘Never show’
  • Click OK

Now you can continue using all your favorite Excel workbooks and icons, and let Dropbox sync your files.

 

Peltier Tech Chart Utility

Multiple Time Series in an Excel Chart

I recently showed several ways to display Multiple Series in One Excel Chart. The current article describes a special case of this, in which the X values are dates. 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.

Plot Multiple Time Series in an Excel Chart

This discussion mostly concerns Excel Line Charts with Date Axis formatting. 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. Any of the formatting described here applies to all of these chart types.

XY Scatter charts are different: X axes behave like Y axes. I could write a book just on this subject.

Displaying Multiple Time Series in An Excel Chart

The usual problem here is that data comes from different places. While the data may span a similar range of dates, the different data sets may have varying intervals between recorded values. Perhaps you have daily temperature readings you want to plot against historic monthly temperatures.

I’ve generated the following arbitrary monthly and weekly data for this exercise.

Monthly and Weekly Data to Plot Together

When plotted in separate Excel line charts, this is how it looks.

Monthly and Weekly Data Plotted Separately

Displaying Multiple Time Series in A Line Chart

Line Chart 1 – Plot by Month

Start by selecting the monthly data set, and inserting a line chart. Excel has detected the dates and applied a Date Scale, with a spacing of 1 month and base units of 1 month (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).

Plot monthly series by month, copy and paste special weekly series

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:

Copy Paste Special Dialog

Excel’s line charts use the same data for all series in the chart, or more precisely, for all series on a particular axis. So let’s assign the weekly data to the secondary axis (below left). Excel only gives us the secondary vertical axis, and we really needed the secondary horizontal axis. Using the “+” skittle floating beside the chart (Excel 2013 and later) or the Axis controls on the ribbon, add the secondary horizontal axis (below right).

Assign weekly data to secondary axis, add secondary horizontal axis

Finally format the secondary date axis with the same settings as the primary date axis: Minimum: 1/1/2016, Maximum: 4/1/2016, Major Units: 1 Month, Base Units: Months.

Make secondary horizontal axis just like primary

And that looks horrible. 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. So let’s look at these base units.

Date Axis Base Units

Among the options for formatting a Date Axis are the units. These include Major Units (major tick mark spacing), Minor Units (minor tick mark spacing), and Base Unit. Base units are the categories that  Excel uses to handle the dates in the data. 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.

Date Axis Formatting: Base Units

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. 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 there is no data point for a given slot, the line connecting points would have extended across the unpopulated slot. If there are multiple data points for a given slot, such as the four weekly points in each month in our first attempt above, these points will all be aligned with the center of the slot.

The chart below right uses Days for its base unit. Labels appear every month, so they are centered on the slots for the first point of each month. Data points also appear on the first of each month, and the slots for all the other days of the month lie empty, with the series line connecting points across the empty slots.

Date Axis Base Units: Months or Days

The points in the first chart are equally spaced horizontally, since the slots for each month are the same width. The points in the second chart are not equally spaced, since the slots for each day are equally spaced (given rounding errors across pixels) and the months have different numbers of slots (days).

You could count minor tick marks to verify this, but I’ve made the following illustration with two copies of this chart. They are identical: I’ve lightened the horizontal gridlines but added vertical gridlines, hidden the plotted data and chart titles, and colored one red and the other blue. I’ve offset the blue chart laterally so its 1/1/2016 gridline is aligned with the red chart’s 2/1/2016 gridline. Note that the next red gridline comes before the next blue gridline: that’s obvious once we note that February’s 29 days make up a shorter month than March’s 31 days. However, the red (4/1/2106) and blue (3/1/2016) gridlines after that line up, since February + March have 29 + 31 = 60 days on the red chart, and January + February have 31 + 29 = 60 days on the blue chart.

Base Units Days: Month Length Proportional to Days in Month

What’s really useful is that a 1-month spacing of labels on the line chart lets you put labels on the first of each month, even with months of unequal length.

Line Chart 2 – Plot by Day

Let’s start again by plotting the monthly data in a line chart. But let’s set our base unit to Days (below left). As before, copy the weekly data, and use Paste Special to add it as a new series to the char. Only the first four weekly points show up, aligned with the monthly point, because the monthly series only has four points (below right).

Plot monthly series by day, copy and paste special weekly series

To allow display of all points in the weekly series, format it so it appears on the secondary axis. Excel at first only draws the secondary vertical axis (below left). 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).

Assign weekly data to secondary axis, add secondary horizontal axis

Rescale the secondary horizontal axis so it matches the primary: make sure the minimum and maximum units are the same (below left). Finally, you can do a little clean-up. Delete the secondary vertical axis, and all data will be plotted on the primary scale (which was the same anyway). Hide the secondary horizontal axis by formatting it to use no line and no labels (below right).

Make secondary horizontal axis just like primary

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.

Displaying Multiple Time Series in An XY Scatter Chart

 

XY Scatter charts have X axes which are much more flexible, so let’s try one with our data.

Select the monthly data, and insert an XY Scatter chart.

XY Scatter Chart of Monthly Data with Date-Formatted Labels

That X axis is cluttered, and what’s up with those axis limits? 12/22/2015 to 4/20/2016? Those aren’t nice round numbers.

Let’s take another look at the data. 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. Those nice dates that go from 1/1/2016 to 4/1/2016 are actually values that go from 42370 to 42461.

Data Showing Dates Formatted As General Numbers

If we reformat our chart’s axis to show General numbers, the scale of 42360 to 42480 looks reasonable (see below). So an XY Scatter chart isn’t as smart as a line chart when picking dates. No matter, will fix it in post.

XY Scatter Chart of Monthly Data with General-Formatted Labels

So let’s revert to dates on the axis.

Copy the weekly data, and use the by now ultrafamiliar Paste Special to add it to the chart (below left). Reformat the horizontal axis so it scales from 1/1 to 4/1/2016, and pick a reasonable major unit (below right).

Copy Paste Special Weekly Data into Chart, Fix X Axis

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. If I used 31 days instead, I’d get 1/1, 2/1, 3/3, and 4/3. 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. 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.

Data Including Dummy Axis Values

Hide the axis labels by using a custom number format of ” ” (a space surrounded by quotes). 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).

Hide Axis Labels, Add Dummy Series for Axis

Add data labels below this new series (below left), and format the labels to show X Value, not Y Value (below right).

Add and Format Data Labels

Format the Axis series so it uses no line and a gray cross marker (below left). Finally do some clean-up. Delete the Axis legend entry (click once to select the legend, again to select the Axis entry, then click Delete). Make the plot area a bit narrower so the date labels are centered under the markers. I deleted the vertical gridlines, because they did not line up with the axis markers (below right).

Format Axis Series, Clean Up the Chart

You could simulate vertical gridlines by adding plus error bars to the Axis series, but we’ve already spent way too much time on this stupid chart.

When To Use XY Charts for Timeline Data

If you don’t need monthly increments along the X axis, then is makes sense to use an XY Scatter chart for your time series.

XY Scatter Chart with Weekly (7-Daily) Increments

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. The data below shows four unevenly-spaced points per day over a two day span. The Line chart with a base unit of Days plots all of the points for each day at one horizontal position, while the XY Scatter chart plots the points horizontally according to the time of day.

Line and Scatter Charts for Data Spaced by Hours

This was the same problem our first line chart attempt ran into while trying to plot multiple days within a month, when the base unit was Months.

Make secondary horizontal axis just like primary

Displaying Multiple Time Series in A Line-XY Combo Chart

Now for a short trip down Memory Lane.

In Excel 2003 and earlier, you could plot an XY series along a Line chart axis, and it worked really well. The line chart axis gave you the nice axis, and the XY data provided multiple time series without any gyrations.

So the process was, make a line chart from the monthly data (below left). Copy the weekly data and use Paste Special to add it to the chart. Only four points were visible, but be patient (below right).

Plot monthly series by day, copy and paste special weekly series

Change the weekly series to XY Scatter type (below left). Finally, assign the weekly XY series to the primary axis (below right).

Change weekly series to XY, assign weekly XY to primary axis

This was really nice, because you could get your nice axis even using a dummy/hidden line chart series. Then you could add as many time series as you wanted, with whatever arbitrary and different date values they contained, all using just the primary axis, so you didn’t have to change the scale on one to keep up with another, etc.

This behavior has been broken since 2007, and I suspect it’s gone for good. In fact, this is one reason I was so slow to adopt Excel 2007.

Try the same process in Excel 2007 or later (this is Excel 2016). Chart the monthly data (below left) and add the weekly data (below right).

Plot monthly series by day, copy and paste special weekly series

Convert the weekly data to an XY type (below left), then move it to the primary axis (below right).

Change weekly series to XY, assign weekly XY to primary axis

Everything was cool until the real maintenance saving step of using the primary axis for everything. But on the primary axis you can only plot as many XY points as there are points in the original line chart series. I guess you could plot a whole blank column as your line chart, and now that I’ve thought of it, maybe that’s what I’ll start doing.

Displaying Multiple Time Series in A Line Chart – A Better Way

In fact, there is a pretty reasonable and not too convoluted way to get multiple timelines with different date sequences on the same chart. It requires laying out your data differently, and tweaking the chart in the most minor but slightly obscure way.

Start with your monthly data in A1:B5. Add the weekly dates below the monthly dates (A6:A18). Add the weekly values below the monthly values, and one column to the right (C6:C18), with the weekly header in C1. (You can repeat this using more rows and columns for many more series as well.)

Select the data and insert your line chart.

Rearranged Data and Resulting Chart

Looks pretty good, except for the gaps. Why are there gaps, anyway? Excel puts a gap between points that have blank cells, but my data had no such gaps.

Well, in a line chart with a date axis, Excel sorts the data behind the scenes before plotting it. So if I sort my data, I can see the gaps in the worksheet range that correspond to gaps in the chart.

Rearranged and Sorted Data and Resulting Chart

This is easy to fix. Select the chart, choose Select Data from the ribbon (or right click the chart and choose Select Data), and click the Hidden and Empty Cells button at the bottom left of the Select Data Source dialog. In the mini dialog that pops up, select the Connect Data Points With Line option for Show Empty Cells. The result is the nice multiple time series chart below right, without any gaps, all on one set of axes, with almost no messing around.

Interpolate Across Gaps

 

 

Peltier Tech Chart Utility

Multiple Series in One Excel Chart

A common question in online forums is “How can I show multiple series in one Excel chart?” It’s really not too hard to do, but for someone unfamiliar with charts in Excel, it isn’t totally obvious. I’m going to show a couple ways to handle this. I’ll show how to add series to XY scatter charts first, then how to add data to line and other chart types; the process is similar but the effects are different.

Displaying Multiple Series in One Excel Chart

Displaying Multiple Series in an XY Scatter Chart

Single Block of Data

This is a trivial case, and probably not what people are asking about. But I’ll cover it just for completeness.

If I have a single block of data, I can select the block of data, or just a single cell within it, and Excel will build a chart using all of the data. The first column (if series are plotted by column) is used for X values, the rest of the columns become the Y values, and the first row is used for series names.

Multiple Series from One Data Block - XY Scatter Chart

Select Series Data: If I somehow have a chart that uses only part of the data, I can right click on the chart and choose Select Data, or I can click Select Data on the ribbon, and the Select Data Source dialog pops up. I can then edit the Chart Data Range, either by manually editing the address, or by selecting a different range, to update the chart.

Select Data Dialog for One Data Block - XY Scatter Chart

Highlighted Chart Data: But it’s even easier to do without the dialog. If I select the chart, I can see the chart’s data highlighted in the worksheet.

XY Scatter Chart with One Series

I can click on any of the handles on the corners of the highlighted ranges to stretch the amount of data used in the chart.

XY Scatter Chart with Three Series

Easy peasy, right? I’ve written about this simple yet powerful technique for controlling chart data in Chart Source Data HighlightingChart Series Data Highlighting, and Highlighted Chart Source Data.

Multiple Blocks of Data

It’s not as easy to manipulate your chart’s data when the data resides in separate blocks of data, such as this:

Multiple Data Blocks - XY Scatter Chart

You have to start by selecting one of the blocks of data and creating the chart.

XY Scatter Chart with 1 Series

Select Series Data: Right click the chart and choose Select Data, or click on Select Data in the ribbon, to bring up the Select Data Source dialog. You can’t edit the Chart Data Range to include multiple blocks of data. However, you can add data by clicking the Add button above the list of series (which includes just the first series).

Select Data Source Dialog

The Select Data Source dialog disappears, while a smaller Edit Series dialog pops up, with spaces for series name, X values, and Y values.

Edit Series Dialog

Select ranges for each of these…

Edit Series Dialog

… then click OK and the new data appears as a new series in the list.

Select Data Source Dialog

The chart now has two series. Note that in an XY Scatter chart, each series can have its own X values, independent of the other series in the chart.

XY Scatter Chart with 2 Series

Repeat as needed to fully populate the chart.

Not too bad, but I’m not a huge fan of the Select Data Source dialog. It just seems like too much work. And like the expansion of data within a single range that I started this article with, there’s a faster and easier way to add data to a chart from different ranges.

Copy – Paste Special: Select and copy the data you want to add to the chart, then select the chart, and from the Home tab of the ribbon, click the Paste dropdown, and select Paste Special. You will be greeted with the Paste Special dialog.

Paste Special - Don't Replace Existing Categories

Make sure that the settings in the dialog are correct: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column.

The Replace Existing Categories setting would replace existing X values with those being pasted, which makes little sense for an XY chart that already has X values defined. We’ll talk about this setting when we discuss Line charts. For XY Scatter charts, I never ever check this box.

Click OK and the chart has a new series.

XY Scatter Chart with 2 Series

Copy the next range, select the chart, Paste Special.

XY Scatter Chart with 3 Series

Again, in an XY Scatter chart, each series can have its own X values, plotted along the same X axis scale, independent of the other series in the chart.

This is pretty easy. It’s even easier to use Paste instead of Paste Special, but sometimes Excel guesses incorrectly on those row/column, first row, first column settings, and you’ll have to undo the Paste and do Paste Special.

Displaying Multiple Series in a Line (Column/Area/Bar) Chart

I’m using Line charts here, but the behavior of the X axis is the same in Column and Area charts, and in Bar charts, but you have to remember that the Bar chart’s X axis is the vertical axis, and it starts at the bottom and extends upwards.

Single Block of Data

When your data is in a single block, a Line chart works just like the XY scatter chart. The first column (if the series data is plotted in columns) is used as X values, or more accurately, X labels; the rest of the columns are used as Y values. The first row is used for series names.

Multiple Series from One Data Block - Line Chart

Multiple Blocks of Data

When there are multiple blocks of data, Line charts still work mostly the same as XY Scatter charts. Let’s look at this simple data.

Separate Data Blocks - Line Chart

Start by creating a Line chart from the first block of data.

Line Chart with 1 Series

Select Series Data: Right click the chart and choose Select Data from the pop-up menu, or click Select Data on the ribbon.

Select Data Dialog

As before, click Add, and the Edit Series dialog pops up. There are spaces for series name and Y values.

Edit Series Dialog

Fill in entries for series name and Y values, and the chart shows two series. The original X labels remain on the chart.

Line Chart with 2 Series and Original Categories

This dialog differs from the one seen when adding data to an XY Scatter chart, because there is no place for X values (or X labels). To change the X labels, click the Edit button above the list of X labels in the chart. The Axis Labels dialog appears.

Edit Series Dialog

The reason for this is that Line charts (plus Column, Area, and Bar charts) treat X values differently than XY Scatter charts. XY Scatter charts treat X values as numerical values, and each series can have its own independent X values. Line charts and their ilk treat X values as non-numeric labels, and all series in the chart use the same X labels.

Change the range in the Axis Labels dialog, and all series in the chart now use the new X labels.

Line Chart with 2 Series and New Categories

The differences between Line and XY Scatter charts can be confusing. What is important is that the data can be formatted the same (markers or no markers, lines or no lines), while the X values are treated differently (numerical values in XY Scatter charts, non-numeric labels in Line charts).

Copy – Paste Special: As in XY Scatter charts, adding data to Line charts can be faster and easier with Copy and Paste Special than with the Select Data dialog.

Paste Special - Don't Replace Existing Categories

Check the settings in the dialo: Values (Y) in rows or columns, series names in first row, categories (X labels) in first column. If Replace Existing Categories is unchecked, the original X labels will remain in the chart. Click OK to update the chart.

Line Chart with 2 Series and Original Categories

Although both series are plotted against the original X labels, if we examine the series formulas, we see that the original series formula contains the original X labels range ($B$3:$B$8), while the new series formula references the new range ($E$3:$E$8).

    =SERIES(Sheet4!$C$2,Sheet4!$B$3:$B$8,Sheet4!$C$3:$C$8,1)
    =SERIES(Sheet4!$F$2,Sheet4!$E$3:$E$8,Sheet4!$F$3:$F$8,2)

The X labels specified in the first series formula is what Excel uses for the chart. If we had selected only the new Y values, ignoring any new X values, and kept Categories in First Column unchecked, both series formulas would reference the same X label range.

Here is what happens when we check Replace Existing Categories.

Paste Special - Replace Existing Categories

When we click OK to update the chart, the new X labels appear along the axis. In addition, both series formulas include the new X label range.

Line Chart with 2 Series and New Categories

This usually isn’t what I want, so I almost never check Replace Existing Categories for any chart type.

The behavior even becomes stranger when we use mismatched data ranges. The second range below has many more rows than the first.

Unmatched Data Blocks

Here is the chart if we paste special with Replace Existing Categories unchecked. Both series use the same X labels, so the axis has enough spaces for the longest series. Since the first labels are being used, these fill the first part of the axis, overlapping excessively, while the rest of the axis remains unlabeled. The first series is pushed to the left of the chart along with the axis labels, since it only uses a fraction of the X axis labels.

Line Chart with 2 Series and Original Categories

Here is the same chart if we paste special with Replace Existing Categories checked. Both series use the new X labels, which fill the entire length of the axis, and they don’t overlap excessively since I wisely used one-character labels. The first series is again pushed to the left of the chart, since it has many fewer points than the second series.

Line Chart with 2 Series and New Categories

You can assign one series to the primary axis and the other to the secondary axis, and each axis will be long enough for its labels. Below, the first series is plotted on the primary axis (bottom and left edges of the chart), while the second series is plotted on the secondary axis (top and right edges).

It is easier to assign the new series to the secondary axis because I kept the Replace Existing Categories unchecked. This kept the new X label range in the series formula even though the series was initially plotted against the original labels. When I switched the series to the secondary axis, it used the new X labels from the series formula. If I had used Replace Existing Categories, the original categories would have been removed from the original series formula, and I would have had to restore them.

I oversimplified when I stated earlier that all series in a Line (Column, Area, Bar) chart use the same X labels. It’s more accurate to say that all primary axis series in a Line chart use the primary axis labels, while all secondary axis series use the secondary axis labels.

Line Chart with 2 Series on 2 Axes

You can try to do a little rescaling of axes to make the chart look better. Here I set the same maximum and minimum values for primary and secondary Y axes. I also formatted the Axis Position to On Tick Marks for both primary and secondary X axes: “One” lines up with “a” and “Six” lines up with “u”, and fortunately there are the right number of categories that each category on the primary scale lines up with a category on the secondary scale (“Two” with “e”, “Three” with “i”, etc.). This alignment was a happy accident.

Line Chart with 2 Series on 2 Semi-Aligned Axes

I hardly ever have a secondary X axis on a line chart, since there is usually no relationship between the two sets of labels, but our eye insists on seeing such a relationship. I’ve written about this confusion caused by Secondary Axes in Charts, even when applied in a well-meaning way.

 

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

Create Excel dashboards quickly with Plug-N-Play reports.