Column Chart with Category Axis Labels Between Columns

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.

Desired Column Chart with Labels Between Columns

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.

Data for this exercise

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

Initial 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.

Stacked Column Chart

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.

Stacked Column Chart

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.

Stacked Column Chart

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.

Stacked Column Chart

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.

Stacked Column Chart

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

Stacked Column Chart

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

Stacked Column Chart

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

Peltier Tech Chart Utility

Plot Values Along the Axis of an Excel Chart

A blog reader emailed me with a question:

I want to visualize a series of prices on single axis:

Data to plot along an axis

I want to portray these in correct proportion, horizontally along a single line – much like points on a thermometer.

These prices are going to be the X values. You need some kind of dummy Y values, so Excel knows where to put the points.

In the data range shown below, I’ve put a set of dummy values in the row below the prices, using 0.5 as a dummy value. I picked 0.5 to float the points in a line above the horizontal axis; if you do in fact want the points right on the axis, you could use 0.0 for the dummy Y values.

I also changed the product numbers to alphabetic labels for this illustration. Short names will work best as data labels in the chart.

I made a chart using the prices and the dummy Y values (the shaded cells in the sheet). That’s the big chart below the data.

Expanded data and preliminary chart

First I shrunk the chart and the chart title, and changed the title to something more descriptive.

I removed the gridlines, and set the Y axes to a min of 0.0 and a max of 0.7, which made the labels fit better.

The Y axis scale is something that can be easily adjusted as needed. In fact, I didn’t really set it here, I set it later, after I saw how my chart looked with the labels. If you plot the points along (not above) the X axis, you may want to pick a Y axis minimum which is the negative of the maximum.

Evolution of the chart - step 2

Next I hid the vertical axis by using no line color and no labels.

I also set the X axis scale to a min of 1.5, to spread out the points a bit.

Evolution of the chart - step 3

Next I set the plotted points so the format varied by point; this setting is found where you set the fill color of the markers.

I also added data labels; by default Excel uses the values in the labels, and placed them above the points.

Evolution of the chart - step 4

Finally I set up the custom labels I wanted. I used the X values instead of the default Y values and also used the Value from Cells option to get the product names into the labels, with a new line separating the cell values and the X values. These settings are shown in the Format Labels task pane shown below. Unfortunately if you do not have Excel 2013, you don’t have the Values from Cells option, but you have other ways to Apply Custom Data Labels in Excel Charts.

Evolution of the chart - step 2

I also changed the font color of each label to match the corresponding point, and staggered labels where they were too close by placing a couple below the points.

Finished chart

Peltier Tech Chart Utility

Peltier Tech Utility 3.0 for Office 2016

New Releases Everywhere!

Microsoft Office 2016 for Mac is out already, and Microsoft Office 2016 for Windows will be released sometime later in 2015 (the Preview versions of Office 2016 for Windows can be downloaded and tried out).

Peltier Tech Chart Utility 2.0 (the current version) will not work in the commercial release of Office 2016, though it may load in Previews.

Peltier Technical Services will release an upgraded utility, Peltier Tech Utility 3.0, to correspond with the upgrade to Microsoft Office.

What’s New in Peltier Tech Utility 3.0

Peltier Tech Utility 3.0 will work in Office 2007, 2010, 2013, and 2016 for Windows and Office 2011 and 2016 for Mac. Note that Office 2007 for Windows will no longer be “officially” supported after Microsoft’s Extended Support End Date of 10 October 2017, though the utility should still run fine.

Mac or Windows?

In the past, the Peltier Tech Utility had separate add-ins for Windows and for Mac. Users of both platforms had to purchase both add-ins (at a discount, of course). In contrast, the Peltier Tech Utility 3.0 will have a single add-in that runs equally well in both operating systems.

Excel and…

There will be a PowerPoint edition of Peltier Tech Utility 3.0, running right in PowerPoint. You will be able to insert and modify great charts right in PowerPoint, using your Excel data or entering your own in the PowerPoint chart’s datasheet. There may also be a Word edition of Peltier Tech Utility 3.0. Non-Excel versions of the utility will be available some time after the Excel version, and will be described elsewhere.

New Charts

Peltier Tech Utility 3.0 is not just a new number assigned to the same old program. There will be many new charts and features.

The upgraded utility will add simple Controls Charts. These will not replace the superb Statistical Process Control packages available for Excel, but they will satisfy the needs of users who only want a few simple run charts.

The utility will also introduce Grouped Box Plots, allowing multiple color-coded groups of boxes and whiskers.

Other chart types under consideration for the new utility are:

  • Sensitivity Tornado Plots
  • Floating Pareto Charts
  • Gantt Charts
  • Stacked Histograms and Cumulative Histograms
  • Trellis Charts

Even though Microsoft Office 2016 will offer Waterfalls, Paretos, Histograms, and Box Plots (finally, right?), the Peltier Tech Utility will continue to offer these charts, for consistency and for users who are still using Excel 2013 and 2010.

Did I forget something? Let me know.

New Features

Numerous new features are being evaluated for Peltier Tech Utility 3.0, including:

  • Move or Extend Chart Data for One or More Series
  • Convert Pivot Charts to Regular Charts
  • Extract Chart Data
  • Enhanced Color Chooser
  • Chart Deformatter (Cleaner)
  • Regression with Confidence Intervals
  • Chart Alignment Tools
  • Series Namer
  • Drag a Point to Change its Data
  • Chart Zoomer: Draw a Box to Rescale Axes or Highlight Points
  • Directory Tools
  • Updates: when a new update is available, the utility will notify you, then install the new software

Something missing? Tell me about it.

Editions

There will be Standard and Advanced Editions of Peltier Tech Utility 3.0. The Standard Edition has a lot of great custom charts and features, and the Advanced Edition has twice as much. A few features now found in the Advanced Edition may be migrated to the Standard Edition. New features developed during the Beta program and later will be introduced to the Advanced Edition.

Peltier Tech Utility 3.0 Beta

The Peltier Tech Utility 3.0 is finally ready for beta testing. The beta is available only in the Advanced Edition, and it still does not have many new charts and features. But it includes a lot of behind the scenes improvements, including the ability to run one add-in version in both Windows and Mac operating systems.

Anyone can download and test the Peltier Tech Utility 3.0 Beta. You have to accept any risk of installing and using it, though such risk is minimal, and has been limited to occasional hanging of Excel. It should run in Excel 2007, 2010, 2013, and 2016 for Windows and in Excel 2011 and 2016 for Mac.

The current issue of the beta is PeltierTechUtility30B-beta-20150824.xlam (click on the filename to download). You can install it following the steps below. It will remain operational until 20 August, and newer versions will be issued at intervals of one week or shorter, as features are added and bugs are corrected.

Installing the Peltier Tech Utility 3.0 Beta

Excel 2016 for Mac

1a. Developer tab > Add-Ins

1b. Tools menu > Add-Ins

2. Browse > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. If new buttons don’t appear on a new Add-Ins tab, restart Excel, and they will become available. This glitch will be fixed when Microsoft finishes making the Mac ribbon fully customizable, sometime over the next few weeks or months.

The Peltier Tech Utility 3.0 Add-ins tab in Mac Excel 2016, showing (top to bottom) Main, Chart Data, Chart Format, Export, and Misc toolbars. This arrangement of multiple toolbars was necessitated because Mac Excel 2016 does not yet support a custom ribbon tab, yet it no longer supports custom menus. Click the image above to view full-size in a new browser tab.

Below is the dialog for selecting a specific custom chart type, full size.

Excel 2011 for Mac

1. Tools menu > Add-Ins

2. Select > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. A menu will appear on the main Excel menu bar at the top of the screen, without the need to restart.

The Peltier Tech Utility 3.0 Menus in Mac Excel 2011. Click the image above to view full-size in a new browser tab.

Excel 2010, 2013, 2016 for Windows

1a. Developer tab > Add-Ins

1b. Ribbon > File tab > Options > Add-Ins > select Excel Add-Ins in the dropdown > Go

2. Browse > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. A Peltier Tech tab will appear on the Excel ribbon, without the need to restart.

Excel 2007 for Windows

1. Large round Office button in top left of screen > Excel Options > Add-Ins > select Excel Add-Ins in the dropdown > Go

2. Browse > navigate to folder where the add-in file was downloaded, click OK a couple times to return to Excel

3. A Peltier Tech tab will appear on the Excel ribbon, without the need to restart.

The Peltier Tech Utility 3.0 Ribbon in Windows. Click the image above to view full-size in a new browser tab.We expect that Excel 2016 for Mac will closely resemble this ribbon.

Report a Bug or Suggest a Feature

Find a bug? Think of a feature that is missing? Mention it in the comments below.

Include details like which version of Excel you’re using, what feature you were trying to use, what you expected to happen, and what did happen. Include error messages (the text of the message, not just the number). If I contact you I may ask for a screenshot of the error, and a copy of your data including the problematic output.

Include your email when posting your comment. I’m the only one who can see it, so it will help me follow up but will not let anyone else spam you.

Peltier Tech Utility 3.0

The Peltier Tech Utility 3.0 beta program will end when Microsoft releases Office 2016 to manufacturing. I’ll announce a date when Microsoft announces a date.

Existing users of Peltier Tech software will get a healthy discount for upgrading. There will be an opportunity to score an additional discount for purchasing before the beta program has ended. Details will be posted soon.

Peltier Tech Chart Utility

Simple XY Quad Chart Using Axes as Quadrant Boundaries

This article will show how easy it is to create a scatter chart with its plot area divided horizontally and vertically into four regions. The regions are separated by the chart axes, and these axes can be positioned where needed to demarcate the quadrants.

Quad Chart Using Axes as Quadrant Boundaries

I have written a tutorial showing how to create an Excel Chart With Colored Quadrant Background, which was more complicated, as it used stacked areas and secondary axes to get the colored background. This is much simpler to create and maintain, and serves much the same purpose.

Here is the sample X and Y data, with calculated averages, and the initial XY scatter chart. We will position the axes at the respective averages of the X and Y data, though you can position them wherever it makes sense in your analysis.

Quad Chart by Axes - Data and Chart

We need to reposition the axes of this chart. Double click the horizontal axis, or select the horizontal axis and press Ctrl+1 (numeral one), to open the Format Axis task pane (shown here, Excel 2013) or Format Axis dialog (works much the same in earlier Excel versions). Under Axis Options >Vertical Axis Crosses, select the Axis Value option, and enter the X average into the box, as shown.

Format Axis Task Pane - Axis Crosses At

The result is shown below left. Repeat for the vertical axis, below right.

Quad Chart by Axes - Position Axes

Those axis labels are totally in the way, but it’s easy to move them. Format each axis (open the task pane or dialog as above) and under Labels > Label Position, select Low from the dropdown.

Format Axis Task Pane - Label Position Low

Now those labels are along the edges of the chart, where they do more good than harm (below left). You can do a small amount of formatting to make the quadrants stick out a bit more clearly. In the chart below right, I’ve used a lighter shade of gray for the gridlines, and I’ve used a darker color, in fact, the same color as the markers, for the axis line color.

Quad Chart by Axes - Position Labels and Reformat

It is easy to use VBA to position the axes and axis labels, using a simple routine like that shown below. This routine positions the labels, then uses the averages calculated in the worksheet to position the axis lines.

Sub AxesAsQuadBoundaries1()
  With ActiveChart
    With .Axes(xlCategory)
      .TickLabelPosition = xlTickLabelPositionLow
      .CrossesAt = ActiveSheet.Range("A17").Value2
    End With
    With .Axes(xlValue)
      .TickLabelPosition = xlTickLabelPositionLow
      .CrossesAt = ActiveSheet.Range("B17").Value2
    End With
  End With
End Sub

The next routine skips the worksheet calculations, instead taking the averages of the X and Y values plotted in the chart to position the axis lines.

Sub AxesAsQuadBoundaries2()
  Dim vData As Variant
  With ActiveChart
    vData = .SeriesCollection(1).XValues
    With .Axes(xlCategory)
      .TickLabelPosition = xlTickLabelPositionLow
      .CrossesAt = WorksheetFunction.Average(vData)
    End With
    vData = .SeriesCollection(1).Values
    With .Axes(xlValue)
      .TickLabelPosition = xlTickLabelPositionLow
      .CrossesAt = WorksheetFunction.Average(vData)
    End With
  End With
End Sub

You can even use worksheet events to reposition the chart axes. My X and Y values were calculated using =RANDBETWEEN(2,14), so whenever the worksheet calculates (e.g., by pressing the F9 function key), the values change. I can tap into this calculation event as follows.

  • Right click on the worksheet tab, and select View Code from the popup menu. The VB Editor opens with a code module corresponding to the worksheet.
  • Select Worksheet from the left hand dropdown at the top of the new code module.
  • Select Calculate from the right hand dropdown.
  • Enter the code as shown.

Worksheet_Calculate Event to Keep Axes in Position

Here is the code so you don’t need to type it all yourself. Simply copy and paste into the worksheet’s code module.

Private Sub Worksheet_Calculate()
  Dim vData As Variant
  With Me.ChartObjects(1).Chart
    vData = .SeriesCollection(1).XValues
    With .Axes(xlCategory)
      .TickLabelPosition = xlTickLabelPositionLow
      .CrossesAt = WorksheetFunction.Average(vData)
    End With
    vData = .SeriesCollection(1).Values
    With .Axes(xlValue)
      .TickLabelPosition = xlTickLabelPositionLow
      .CrossesAt = WorksheetFunction.Average(vData)
    End With
  End With
End Sub

Depending on the details of your worksheet model, you could use the Worksheet_Calculate, Worksheet_Change, Worksheet_PivotTableUpdate, or other event procedures to update the chart.

Peltier Tech Chart Utility

Salary Chart: Plot Markers on Floating Bars

In an organization it is often informative to plot individual salaries and grades to see how they fall within salary bands for those grades.

This is easily accomplished by constructing a combination chart using a floating bar chart and overlaying the individual points as an XY scatter plot.

The screenshot below shows salary ranges for six grades of engineers, along with actual salaries and grades for eight engineers, with the desired chart.

Salary Data and Chart

The data needed to construct the floating bar chart is shown below, with Span calculated in the column between Min and Max. The chart is constructed by selecting the orange shaded cells (Grade, Min, and Span) and inserting a stacked column chart (top chart below).

Fix up the chart (bottom chart below) by deleting the legend, formatting Min to use no fill and Span to use a light fill color, and setting a gap width of 50 or 75%. Clean up the vertical axis labels by using a custom number format of

0,"k"

The lone zero means display the value without decimal digits, and the comma after the zero means show thousands, not ones. I’ve deleted the chart title, but you should use a title that describes the data being displayed.

Salary Band Data and Floating Bar Chart

Add the individual salary data as follows. Set up the data as shown, using a MATCH formula to find which bar the engineer’s grade falls within:

=MATCH(cell containing grade label,range containing list of grade labels,0)

This column should be to the left of the salaries, since it will be used as X values for the XY series we will plot. Copy the shaded range (grade index and salary), select the chart, use Paste Special from the Home tab of the ribbon, and select the options shown in the dialog screen shot below:

  • Add Cells as New Series
  • Values (Y) in Columns
  • Series Names in First Row
  • Categories (X Labels) in First Column

Individual Salary Data, Pasting into Chart

The data is added as another stacked column series (below left).

Right click on the added bars, choose Change Series Chart Type from the pop-up menu, and select the XY Scatter option with markers only (below right).

Converting Individual Data to Points

Format the XY scatter series to be plotted on the Primary axis, and choose a marker color and style that shows up clearly against the floating bars (below left).

Finally, you may like to stretch the chart to improve resolution (below right). Add labels to make it easier to track each engineer. In Excel 2013, add labels, then use the Values from Cells option to use the cells containing the labels. In earlier versions, you can manually change the text of each label, or you can use Rob Bovey’s Chart Labeler, a free add-in, to link the data labels to the cells.

Finishing Salary Chart

I’ve described these data labeling options in more detail in my tutorial Apply Custom Data Labels to Charted Points.

This tutorial is a rework of my answer to the October 2014 question need to create salary data with salary bands on Stack Overflow.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

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