Axis Labels on Small Charts

My colleague Patrica McCarthy, the Excel Diva, wrote in 12 Months Data – 13 Months Data in a Chart last week about a problem with Excel charts. If you make a 12-month chart, as you shrink the chart, for example to fit it into a dashboard, Excel drops off the last month’s axis label. She mentions that plotting 13 months of data will avoid this problem.

That doesn’t make sense, I thought. Excel doesn’t drop off the last category axis label on a chart. But then I thought about it. When you shrink the chart, and the labels get close together, Excel does little things to prevent axis labels from overlapping.

I’ll illustrate by showing larger charts and a really simple data set.

The default chart in US versions of Excel is 5 inches wide and 3 inches tall. The figure below shows three charts, each 1.5 inches tall, at widths of 5, 4, and 3 inches. The month labels all appear in these charts. It helps that I’ve used the three-letter abbreviations for the month names, and I’ve shrunk the axis label font size from 9 to 8 points (and I’ve also shrunk the chart title from a ridiculous default of 14 points down to 9 points).

Larger Excel Charts with Normal Monthly Category Axes

When I shrink the chart a bit further to 2.5 inches in width, Excel realizes that the axis labels won’t all fit, and it uses one of its favorite tricks: Excel has rotated the axis labels 90° (see chart below). They fit fine, but they are harder to read than horizontal labels.

Smaller Excel Charts with Potentially Overlapping Vertical Labels

You can format the labels so they are horizontal, but Excel uses another of its favorite tricks: it only shows alternating labels, starting with the first (left chart below). This is what Patricia was talking about, but when she said Excel hides the last label, I didn’t realize she meant Excel was hiding alternating axis labels. You can format the axis so that it shows every label (in other words, it uses a label interval unit of 1), but now we see that the labels are beginning to overlap (right chart below).

Smaller Excel Charts with Potentially Overlapping Horizontal Labels

How about a 45° degree tilt to the labels? Starting with the first chart, if you change to 45° labels, Excel still decides to show only every second label (left chart below). If you show all of the axis labels, they really don’t overlap (right chart below). This isn’t terrible, but I find partially rotated text as distracting as fully rotated text, and hardly easier to read.

Smaller Excel Charts with Potentially Overlapping Inclined Labels

Patricia mentioned in her article that you can show 13 months of data to make the last month’s label visible. I’ve added the prior December’s data below, and now that we start the alternating axis labels with last December, this December also appears. We still miss every second label, but seeing the last one anchors the visual more effectively.

Plot 13 Months to Show Last Month's Label

Showing both December’s gives insight into a year-over-year comparison, at least for December.

In addition to using Patricia’s workaround above, and using smaller font sizes and shorter axis labels as I’ve done from the start, there are a few other tricks you can try.

Here I’ve used one-letter abbreviations for the month names. Sure, there are 3 J’s, 2 M’s and 2 A’s, but within the context of the entire year, there’s little confusion about this, and it is done frequently.

Shorten Labels to Make Them All Fit

Quick Number Format Tip #1: If you have a date in a cell, you can display just the three-letter abbreviation if you use a custom number format of MMM, and you can show the one-letter abbreviation if you use a custom number format of MMMMM. (A single M shows a one- or two-digit numerical month, 1 for January and 12 for December; a double MM shows a two-digit numerical month, 01 for January and 12 for December; and a quadruple MMMM spells out the entire name of the month.)

You can also show alternating month labels, ending with December, as follows. Make your chart as before with the full set of month abbreviations, set it to show every horizontal label, then delete every other month starting with January. Now at least the last month’s label is visible, even if you’re only showing half of the axis labels.

Hide Alternate Labels in Worksheet to Show the Labels You Want to Show

Finally, you can show all axis labels, but prevent overlapping them, if you stagger them, showing some higher and others lower. To do this, put the cursor at the beginning of some of the cells, and hold Alt while pressing the Enter key. This inserts a carriage return (or line feed, whichever) at that point in the cell’s text. I’ve set the cell’s Word Wrap to true, so you can see the taller two-line cells in every second row below, and these labels appear one line lower along the axis. This needs more space to display, so Excel shrinks the plot area slightly, but you get the benefit of seeing all of the axis labels.

Stagger Labels with Creative Use of Carriage Returns

Quick Number Format Tip #2: To get the carriage return in a cell containing a date, use a custom number format of MMM, but precede the MMM with Ctrl+J. This undocumented feature inserts a carriage return right in the number format. This undocumented feature also only works in the number format dialog for worksheet cells, not for chart elements, which is a total bummer, because they’re at least as useful in charts as in the worksheet.

Quick Formula Tip #1: If the labels are in a column of cells, say A2:A13, you can easily get a carriage return into alternate cells. Enter the formula =A2 in cell B2, enter =CHAR(10)&A3 in cell B3, then select B2:B3, and drag the square on the bottom right of the selection down to B13 to fill the range with these alternating formulas.

Quick Formula Tip #2: If you have dates in A2:A13 instead of labels, enter =TEXT(A2,"MMM") into B2, enter =CHAR(10)&TEXT(A3,"MMM") into B3, select B2:B3, and fill down as above to B13.


Peltier Tech Charts for Excel

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 Charts for Excel

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 Charts for Excel

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 Charts for Excel

Peltier Tech Charts for Excel 3.0