Axis Labels

LAMBDA Function to Build Three-Tier Year-Quarter-Month Category Axis Labels

Tuesday, November 29, 2022 by Jon Peltier Leave a Comment

Tuesday, November 29, 2022 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

A certain data layout can produce a chart axis which divides and subdivides the categories into logical subcategories, such as years, quarters, and months in the following chart.

Excel Line Chart with Three-Tiered Year-Quarter-Month Category Axis Labels

Generally, this data layout must be produced by hand, because it relies on an arrangement of filled and blank cells to help Excel parse the data into subcategories. In this post I’ll show how a LAMBDA formula can build the range for you.

Bulgaria Excel Days 2022

Earlier this month I had the honor and pleasure of participating in Excel Days 2022, held in Sofia, Bulgaria. On one day I held my Advanced Excel Charting Masterclass for a group of about 25; on another I presented a conference session entitled “The Best Excel Charting Tips and Tricks” to a crowd of 300 or more. That’s me standing in front of the largest LED screen I’ve ever used.

Jon Peltier presenting at Excel Days 2022 in Sofia, Bulgaria.

Chart Tip/Trick: Top-Left Cell

One of the tips was that using a blank top-left cell helps Excel parse the X values, Y values, and series names (highlighted purple, blue, and red respectively in the screenshot below), especially if the X values and series names consist of numbers (years, for example). The top-left cell, or TLC, is filled with light gold.

A blank top-left cell helps Excel parse chart source data

Many people know about this TLC trick.

Chart Tip/Trick: Category Axis Grouping

What I call TLC+ extends this approach to identify multiple columns to use as X values, using blank cells to identify grouping of categories and subcategories. This example groups months into quarters. Again, blank cells are filled with light gold, and the axis categories and subcategories are highlighted with the same color.

Grouped chart axis labels created with a partially populated column

Many people don’t know about grouping axis categories in this way, although they’ve likely seen it in a pivot chart that has multiple fields in the rows area of its pivot table. So this trick was new to much of my audience.

TLC+ doesn’t stop there. The next example shows months grouped into quarters, and quarters grouped into years. I’ve applied the same highlighting as above.

Multiple chart axis grouping levels are possible

This grouping can be extended much farther; the limit is not with Excel’s technical capability but more with the legibility of the output. Once I built a chart with 9 or 10 levels of grouping in its category axis. By this point, the chart consisted of a very wide category axis and very little room for data in the plot area.

Not-Just-For-Charts Tip/Trick: Custom Number Formats for Months

Speaking of legibility, those month abbreviations are difficult to read, since they have been rotated upward. But you can use a one-letter abbreviation for each month, by entering an actual date in the cells, and applying the custom number format below. One “m” in the custom format string results in a one- or two-digit month number; two results in a two-digit month number with a leading zero if necessary. Use three m’s for the three-letter month abbreviation, and four m’s for the full month name. Finally, five m’s will give you just the first initial of each month. This may be confusing if only one or two months are abbreviated this way, since there are multiple months for J, M, and A. But when a whole year of months is abbreviated like this, people recognize the months.

This “mmmmm” trick amazed one audience member so much that she tested whether “ddddd” would produce the one-letter abbreviations of the days of the week. She reported that, sadly, it does not.

This is the same data as above, with one-letter month abbreviations that improve legibility of the axis labels.

Cleaned up month labels in grouped category axis

Most Important Chart Tip/Trick?

Those are three great tips: top-left cell, category label grouping, and the custom number format for one-letter month abbreviations. But what is the most important charting trick?

As I am energetically explaining below, the most important charting trick is to get the data right before you make your chart.

Jon Peltier explaining the need for good chart data at Excel Days 2022 in Sofia, Bulgaria

Dude, Where’s My LAMBDA?

I promised a formula to construct a grouped data range for a chart’s category axis. Here is how I built my LAMBDA.

TEXTSPLIT

My initial approach was to build a string of years, quarters, and months delimited by commas and semicolons, then use the recently introduced TEXTSPLIT function to break that into the required grid.

I typed this long string into cell B2 (note the semicolons before the first quarter and before the first month)

2021,,,,,,,,,,,,2022,,,,,,,,,,,;Q1,,,Q2,,,Q3,,,Q4,,,Q1,,,Q2,,,Q3,,,Q4,,;J,F,M,A,M,J,J,A,S,O,N,D,J,F,M,A,M,J,J,A,S,O,N,D

and this formula in cell B5 produced the grouped dates in B5:D28 (with a blue border intended to resemble the Dynamic Array formula highlighting).

=TRANSPOSE(TEXTSPLIT(B2,",",";",FALSE))

Some people like to use spaces to make formulas easier to read; Excel ignores the spaces.

=TRANSPOSE(TEXTSPLIT(B2 , "," , ";" , FALSE))

Some will even write the formula on multiple lines for clarity, with spaces to provide indentation; Excel ignores the line feeds as well as the spaces. You can press Alt+Enter to insert a line feed within a formula. I’m not sure extra spaces and line feeds are necessary for this relatively simple formula, but they will help in a little while.

=TRANSPOSE(
  TEXTSPLIT(
    B2 ,
    "," ,
    ";" ,
    FALSE
  )
)

The chart was a test that the output would work for my axis groupings.

Grouped labels can be created using TEXTSPLIT

My initial thought was to input an initial year and number of years into my LAMBDA, construct the long, delimited string, then TEXTSPLIT it. But I did a little experimenting first.

Build the Pieces

I used the DATE function to produce a list of months. DATE(year,month,day) produces a numerical date for month/day/year (or day/month/year outside the US). To get sequential months, I used the SEQUENCE function in the month argument of the DATE function.

All I needed was the initial month, as shown below. Using year 2021 and months 1 through 12, I get January through December of 2021. Then month 13 spills into the next year, giving me January of 2022. This makes it easier than having to calculate when the year turned over.

The DATE(year,month,day) function for 24 months

The screenshot below shows my approach. I listed the months in column B. In column D, I listed the year, but only if the month was January. In column E I listed the quarter, but only for the first month of each quarter. In column F I listed the first initial of each month using the “mmmmm” custom number format.

Column G holds some dummy data, so I could test out a chart, and it worked nicely.

Building the chart axis grouping formula

Thanks to Leonid who pointed out a typo in one of these formulas in the original figure.

Put the Pieces Together

I needed to condense all of this into one formula, so I started with the following LET function. I input the first year and number of years, then calculated my list of dates. Then I did a bit of arithmetic to calculate quarter number from the month: January is quarter number 1, February is 1-1/3, March is 1-2/3, April is 2, etc.

The CHOOSE function’s syntax the way I used it is something like this:

CHOOSE({column 1, column 2, column 3},
  formula for column 1,
  formula for column 2,
  formula for column 3)

The CHOOSE function is a great way to build up a multiple-column Dynamic Array.

=LET(
  firstyear,2021,
  numyears,2,
  dates,DATE(firstyear,SEQUENCE(12*numyears),1),
  qtr,(MONTH(dates)+2)/3,
  CHOOSE(
    {1,2,3},
    IF(MONTH(dates)=1,TEXT(dates,"yyyy"),""),
    IF(qtr=INT(qtr),"Q"&qtr,""),
    TEXT(dates,"mmmmm")
  )
)

This milti-line, indented formula is much easier to read than the no-white-spaces version.

This formula produces the desired output.

Output of the chart axis grouping formula

There are a few caveats for my non-US readers. First, the format strings for year (“Y”) and month (“M”) may be different in your regional version of Excel. Second, the argument separator in Excel formulas is a comma (,) for the US, but a semicolon (;) in many non-US editions, and I’ve heard of other characters being used. Third, array structures like {1,2,3} use a comma (,) and semicolon (;) respectively as column and row separators in the US, but in other regions, these may be reversed or even replaced by other characters.

Finally the LAMBDA

I rearranged my LET to produce the following LAMBDA function. When used in a cell like this, the inputs to the LAMBDA are enclosed in parentheses after the LAMBDA’s closing parenthesis. The LAMBDA produces output identical to the LET output above.

=LAMBDA(
  firstyear,numyears,
  LET(
    dates,DATE(firstyear,SEQUENCE(12*numyears),1),
    qtr,(MONTH(dates)+2)/3,
    CHOOSE(
      {1,2,3},
      IF(MONTH(dates)=1,TEXT(dates,"yyyy"),""),
      IF(qtr=INT(qtr),"Q"&qtr,""),
      TEXT(dates,"mmmmm")
    )
  )
)
(2021,2)

Here is how to convert the LAMBDA into a reusable user-defined function. Click Define Name on the Formulas tab to open the New Name dialog. Give the function a descriptive name, document it with a comment, then enter the LAMBDA formula (without the trailing inputs) into the Refers To box, and click Enter.

To simplify creating, editing, and deploying LAMBDA functions, Microsoft has introduced the Advanced Formula Environment. You can get it from Insert > Get Add-Ins. I have not used it here.

When you start typing the LAMBDA name into a cell, Excel’s IntelliSense suggests the full name of the function, and shows the comment, just like any built-in function.

Click the tab key and Excel fills in the function name with the opening parenthesis, and shows what arguments are expected.

I’ve used my LAMBDA function in cell B3 below, and the output works just fine in the chart. I’ve hard-coded 2020 and 3 as the starting year and number of years, but I could have linked to cells with these values.

TLC Reprise

Here I am showing how a chart looks when the top-left cell is not blank.

Jon Peltier at Excel Days 2022 in Sofia, Bulgaria

Power Query Alternative

My colleague Mark Proctor likes this axis technique, and built a query in Power Query that will take monthly date and generate the Year-Quarter-Month data structure to produce the tiered axis in his charts. Read about it in his Excel Off The Grid blog in How to create chart data with Power Query.

More LAMBDA Functions

Stagger Axis Labels to Prevent Overlapping

Thursday, September 19, 2019 by Jon Peltier 5 Comments

Thursday, September 19, 2019 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

When you have a chart that is too narrow, and has too many axis labels, or the labels it has are too long, Excel tries very hard to prevent the labels from overlapping. Usually Excel will incline the labels so they don’t overlap. Excel may also decided to only show some labels. But you can stagger axis labels to keep them horizontal and not overlapping.

Excel “Helpfully” Formats the Axis Labels

Here is a chart with inclined labels. They’re harder to read than horizontal labels, but easier than overlapping labels.

Excel inclines labels to prevent overlapping

You can overrule the labels’ orientation: on the Home tab of the ribbon, on the alignment tab, click the dropdown to select any alignment, then click it again to unselect the selected alignment. Alternatively, in the Format Axis task pane, select Text Options, then click on the Textbox icon, then where the Custom Angle box is blank, enter any nonzero value, then enter zero. I don’t know why you need to do either thing twice, but Excel is like that sometimes.

Alternate ways to adjust the alignment of the axis labels

Now the labels are horizontal. And to prevent overlapping, Excel has decided to hide alternate labels. Unfortunately, this hides information from us.

Labels are horizontal, but some are hidden

To get the labels back, go to the Format Axis task pane, and under Labels, Interval between Labels, select Specify Interval Unit, and enter 1.

Now all of the labels are horizontal and visible, but they overlap. So maybe Excel wasn’t so dumb after all, but it can’t do what we need without a little help.

All labels are horizontal and visible, but they overlap

How to Overcome Excel’s Labeling Issues

How do we fix the labels? We can’t select individual axis labels and move them or change their contents.

But we can change the data in the worksheet. The trick is to insert a line break in front of alternating labels. The line break will force these labels down line, so they won’t block the adjacent labels.

Starting from scratch, we’ve inserted a line feed in the cells containing the second and fourth labels. And as a reminder, you can insert a line break in a cell by holding the Alt key while pressing the Enter key. Here’s the range and the chart, with its inclined labels. Apparently Excel only shows the first line of an inclined label, so the second and fourth labels are not shown.

Alt+Enter inserts a line break at the beginning of alternate cells containing axis labels

Go through the complicated gyrations to make the labels horizontal (below left), then repeat the axis label interval gymnastics to make every label appear (below right). We have successfully staggered the labels.

There are other ways to avoid overlapping labels. First, you could use shorter labels or acronyms.

Next, you could try to use labels with spaces, where Excel can choose to wrap the text.

Finally, you can change from a vertical column chart to a horizontal line chart, which allows much more room for long, horizontal labels.

Horizontal labels don't overlap in a bar chart

Formulaic Approach

We’ve tricked Excel into staggering our axis labels by inserting a line break in alternate cells. If we could see these line breaks the way we can see paragraph markers in Word, it would look something like this:

If you don’t want to spoil your original data range by inserting line breaks, you can always link to the original labels in another range (even on another sheet), and include a little logic in the link formulas to put line breaks where needed. Here I show the original data on the left and the chart source data on the right.

Source data and linked data with selective line breaks

(Note: the red pilcrow characters are not really there, I’ve added them for illustration purposes.) The formula in cell E14, filled down to E18, is

=IF(MOD(ROW()-ROW($F$13),2)=0,CHAR(10),"")&B3

where CHAR(10) is a linefeed character.

Posted: Thursday, September 19th, 2019 under Chart Axes.
Tags: Axis Labels.
Comments: 5

Extract Labels from Category Axis in an Excel Chart (VBA)

Thursday, February 15, 2018 by Jon Peltier 7 Comments

Thursday, February 15, 2018 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

Question: Extract a Category Label for a Point

I came across a question in the Excel Reddit, Is there a way to select a chart series point and have the label name of that point be copied into a cell?

Answer 1: Chart with Simple Axis

The short answer is the following function: you get .XValues property of the series, which is an array of category labels, and find the element of the array for the particular point:

Function GetCategoryLabel(cht As Chart, iSrsNum As Long, iPtNum As Long) As String
  Dim srs As Series, vCats As Variant
  
  Set srs = cht.SeriesCollection(iSrsNum)
  vCats = srs.XValues
  GetCategoryLabel = vCats(iPtNum)
End Function

You call it like this, passing in the chart, series number, and point number:

Sub TEST_GetCategoryLabel()
  Dim s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  s = GetCategoryLabel(cht, iSrs, iPt)
  s = "Category Label for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  MsgBox s
End Sub

This doesn’t stick the label into a cell, like the question asked, but displays the label with a message box. As shown below, Series 1 – Point 3 has the label “C”:

Chart with Simple Category Axis

Answer 2: Chart with Multi-Tier Category Labels

It turns out that the chart was a pivot chart, based on a pivot table with several fields in the rows area. Each field contributes a tier of labels to the category axis. The screenshot below shows a Table of data, a Pivot Table based on this Table, and above both a Pivot Chart, with a two-tiered category axis.

Table, Pivot Table, and Pivot Chart

If you use the simple approach above, the .XValues don’t distinguish among the tiers, and the labels from different tiers are put together, separated by spaces. In the chart below, Series 1 – Point 3 has the compound label “Alpha C”, according to the simple algorithm.

Pivot Chart with Multiple Tier Category Axis

We need a much more detailed VBA approach. We have to parse the series formula, get the address of the range containing the category labels, then get the corresponding range, figure out how many columns of labels there are, then find the label in each column corresponding to the point. This is complicated by the empty cells in the pivot fields, so we have to keep looking upward until we find a cell with a label.

Here is the more intricate function that extracts an array of category labels:

Function GetCategoryLabels(cht As Chart, iSrsNum As Long, iPtNum As Long) As Variant
  Dim srs As Series
  Dim sFmla As String, vFmla As Variant
  Dim rCats As Range, vCats As Variant, vOutput As Variant
  Dim iRow As Long, iCol As Long, nRows As Long, nCols As Long
  
  Set srs = cht.SeriesCollection(iSrsNum)
  
  sFmla = srs.Formula
  sFmla = Mid$(Left$(sFmla, Len(sFmla) - 1), InStr(sFmla, "(") + 1)
  vFmla = Split(sFmla, ",")
  
  Set rCats = Range(vFmla(LBound(vFmla) + 1))
  vCats = rCats.Value2
  
  ReDim vOutput(1 To UBound(vCats, 2))
  For iCol = 1 To UBound(vCats, 2)
    For iRow = iPtNum To 1 Step -1
      If Len(vCats(iRow, iCol)) > 0 Then
        vOutput(iCol) = vCats(iRow, iCol)
        Exit For
      End If
    Next
  Next
  
  GetCategoryLabels = vOutput
End Function

It’s called in a similar way:

Sub TEST_GetCategoryLabels()
  Dim v As Variant, i As Long, s As String
  Dim cht As Chart, iSrs As Long, iPt As Long
  
  Set cht = ActiveSheet.ChartObjects(1).Chart
  iSrs = 1
  iPt = 3
  
  v = GetCategoryLabels(cht, iSrs, iPt)
  
  s = v(LBound(v))
  For i = LBound(v) + 1 To UBound(v)
    s = s & ", " & v(i)
  Next
  
  s = "Category Labels for Series " & iSrs & " - Point " & iPt & ":" & vbNewLine & s
  
  MsgBox s
End Sub

When we put all the category fields into the rows area of the pivot table, we get three labels for Series 1 – Point 3, “Alpha”, “A”, and “III”:

Pivot Chart with Three-Tiered Category Axis

When we put the fields “Greek” and “Latin” into the rows area of the pivot table and move “Roman” to the columns area, we get two labels for Series 1 – Point 3, “Alpha” and “C”:

Pivot Chart with Two-Tiered Category Axis

When we leave the field “Greek” in the rows area of the pivot table and put “Latin” and “Roman” to the columns area, we get a single label for Series 1 – Point 3, “Gamma”:

Pivot Chart with One-Tiered Category Axis

Posted: Thursday, February 15th, 2018 under VBA.
Tags: Axis Labels, VBA.
Comments: 7

Microsoft MVP Logo

Axis Labels on Small Charts

Monday, October 17, 2016 by Jon Peltier 2 Comments

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.

Posted: Monday, October 17th, 2016 under Chart Axes.
Tags: Axis Labels, number formats.
Comments: 2

Microsoft MVP Logo

Column Chart with Category Axis Labels Between Columns

Friday, August 21, 2015 by Jon Peltier 7 Comments

In Stacked column chart in Excel with the label of x-axis between the bars, a SuperUser user posted this chart, asking how to get the labels between the columns, instead of under the columns, where Excel puts them. This is a great tutorial, so I’m repeating my answer here.

We’re going to use a dummy (hidden) XY series on the chart, whose data labels will become the labels we want our axis to display.

We’ll start with two sets of data. The first (below left) is the data for the stacked columns, using simple counting numbers for the categories. The second (below right) is XY data where X is the list of values where we want our labels, and Y is zero.

Select the first set of data and insert a stacked column chart.

Copy the second range, select the chart, and use Paste Special (Home tab of the ribbon > Paste button dropdown > Paste Special), and select the following options: Add data as series, series in columns, series names in first row, categories in first column.

The new series is added as a third set of stacked bars, which don’t show up because their height is zero.

Select the added series by selecting the green bars and clicking the up arrow key. Click the menu key (between the right Alt and Ctrl buttons on most Windows keyboards) or hold Shift and click the F10 function key to pop up the context menu. Click Change Series Chart Type, and choose XY Scatter. This adds a set of markers along the bottom of the chart (I used blue circles in the chart below) and it adds secondary X and Y axes.

Format the scale of the secondary horizontal axis (top of chart) so it fits the data: min = -115, max = -50. Note that the blue circles are now aligned between the bars, where the labels will go.

Hide the secondary X and Y axes by formatting their label position as No Label, and their line color as No Line. This hides the labels and reduces the margin around the chart that previously held the labels.

Hide the primary horizontal labels by using a custom number format of ” ” (that’s right, a space surrounded by double quotes). This hides the labels but keeps the margin in place for the other labels we’re going to add.

Right-click the series of blue dots, and choose Add Data Labels. Excel adds the default Y values (zeros) to the right of the markers.

Format the labels so they are in the Below position, and so they show the X values instead of the Y values.

Finally format the series of dots so they use no markers. And we’re done.

This technique can be used to customize axis labels and add many other labels to your charts.

Posted: Friday, August 21st, 2015 under Chart Axes.
Tags: Axis Labels, Column Charts, Combination Charts, dummy series.
Comments: 7