Click a Worksheet Cell to Change a Chart

We want our chart to be interactive, so that clicking in the data range will cause the chart to plot the row we clicked in.

Here is the data and resulting chart. The series “alpha” is plotted, from row 3 of the data range (row 2 contains the category labels).

442

Excel lets us write VBA code that will respond to user actions. There isn’t a “click” event to respond to, but there is a “selection change” event.

Unlike regular VBA procedures that reside in regular code modules, these event procedures are stored in special class modules that correspond to Excel objects. Each workbook has such an object code module, and so does every sheet in the workbook.

It’s easy to access the worksheet code module. Right click on the sheet tab, and choose View Code.

368

If you’re already in the Visual Basic Editor, find the workbook’s VB Project in the Project Explorer window, expand the Microsoft Excel Objects node, and double click on the item corresponding to the worksheet. In this case, the sheet name is Ranges, shown in parentheses after the sheet’s code name. We won’t talk about code names here.

386

A worksheet code module opens, with the workbook name and sheet code name in the title bar. If you have “Require Variable Declaration” checked in Tools > Options (if you don’t, you should), then the line Option Explicit appears at the top of the module.

649

Click the left hand dropdown at the top of the module, and select Worksheet.

649

This puts an empty procedure stub in the module. The right hand dropdown now contains a list of events you can code against in the worksheet. The default event is SelectionChange, which I have selected in the dropdown, but as the default it is the one that appeared a moment ago.

649

Here is the module with the new procedure stub. It’s a good idea to use the dropdowns to insert these procedures, so you don’t have to remember which parameters are needed. The SelectionChange procedure needs the Target parameter, which was automatically inserted.

649

When the user selects a new range, the Worksheet_SelectionChange procedure runs, and Target is the range selected by the user.

The code that responds to the selection change to update the chart is shown below. We don’t care about the entire Target range, just the active cell. If the active cell intersects with the chart’s Y values or series names (in B3:H6), we will update the chart. For series name, we will use the cell that’s in the row that was clicked (ActiveCell.EntireRow) and in column B. For Y values, we will use the cells in columns C through H that are in the row that was clicked.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(ActiveCell, Me.Range("$B$3:$H$6")) Is Nothing Then
    With Me.ChartObjects(1).Chart.SeriesCollection(1)
      .Name = "=" & Intersect(ActiveCell.EntireRow, Me.Range("$B:$B")).Address(, , , True)
      .Values = Intersect(ActiveCell.EntireRow, Me.Range("$C:$H"))
    End With
  End If
End Sub

Here is the chart after selecting cell B4. Series “beta” from row 4 is now plotted:

463

Here is the chart after selecting C3. Series “alpha” from row 3 is again plotted:

463

After selecting G6, series “delta” in row 6 is plotted:

463

Clicking cell I7 does not change the chart, since I7 lies outside the chart data range.

463

Instead of using the hard-coded ranges in the code, we can use Names that indicate the chart source data. Below two names are defined, one for the series names (column B, shaded red) and one for the Y values (C3:H6, shaded blue).

442

The code is substantially the same, referencing the range names instead of the hard-coded range addresses. This means that a change to the chart source data only requires an update to the name definitions, not to the code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Not Intersect(ActiveCell, Union(Me.Range("SeriesNames"), Me.Range("SeriesYValues"))) Is Nothing Then
    With Me.ChartObjects(1).Chart.SeriesCollection(1)
      .Name = "=" & Intersect(ActiveCell.EntireRow, Me.Range("SeriesNames")).Address(, , , True)
      .Values = Intersect(ActiveCell.EntireRow, Me.Range("SeriesYValues"))
    End With
  End If
End Sub

The approach using Names can readily be extended to the case where multiple data ranges and charts reside on one sheet.

570

The following procedure cycles through all (both) charts on the active sheet, testing whether the active cell intersects with the names associated with either chart, and updating as appropriate.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim iCht As Long
  
  For iCht = 1 To 2
    If Not Intersect(ActiveCell, Union(Me.Range("SeriesNames" & iCht), Me.Range("SeriesYValues" & iCht))) Is Nothing Then
      With Me.ChartObjects("Chart " & iCht).Chart.SeriesCollection(1)
        .Name = "=" & Intersect(ActiveCell.EntireRow, Me.Range("SeriesNames" & iCht)).Address(, , , True)
        .Values = Intersect(ActiveCell.EntireRow, Me.Range("SeriesYValues" & iCht))
      End With
    End If
  Next
End Sub

When cell C4 is selected, the first chart updates (beta is plotted in place of alpha), but the second chart is unchanged.

570

Selecting cell M5 updates the second chart (Charles is now plotted in place of Andy), but the first chart is unchanged.

570

Selecting cell I6 makes no change to either chart, since I6 is not within the source data range of either.

570

You can extend this even further, without names or hard-coded ranges, if you assume each chart’s potential data is in a contiguous range separated from other charts’ data ranges by blank rows or columns. This code finds the region containing data for each chart, and determines whether the active cell is within that region, changing the chart if necessary.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim chtob As ChartObject
  Dim srs As Series
  Dim sFmla As String
  Dim vFmla As Variant
  Dim rngNm As Range, rngY As Range, rngData As Range
  
  For Each chtob In ActiveSheet.ChartObjects
    For Each srs In chtob.Chart.SeriesCollection
      sFmla = srs.Formula
        ' e.g. =SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
      sFmla = Mid$(sFmla, 9, Len(sFmla) - 9)
       ' Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1
      vFmla = Split(sFmla, ",")
      
      On Error Resume Next
      Set rngNm = Range(vFmla(LBound(vFmla)))
      Set rngY = Range(vFmla(LBound(vFmla) + 2))
      Set rngData = rngY.CurrentRegion
      Set rngData = rngData.Offset(1).Resize(rngData.Rows.Count - 1)
      On Error Resume Next
      
      If Not rngY Is Nothing Then
        If Not Intersect(rngData, ActiveCell) Is Nothing Then
          With srs
            .Name = "=" & Intersect(ActiveCell.EntireRow, rngNm.EntireColumn).Address(, , , True)
            .Values = Intersect(ActiveCell.EntireRow, rngY.EntireColumn)
          End With
        End If
      End If
      
      Set rngNm = Nothing
      Set rngY = Nothing
      Set rngData = Nothing
      Erase vFmla
    Next
  Next
End Sub

 

Peltier Tech Chart Utility

Office 2016 For Mac Is Here (well…)

Note: This article was originally published on 13 July. I have made modifications on 17 July.

The big announcement from Microsoft last Thursday was Office 2016 for Mac is here! Ed Bott has a glowing review at New Office 2016 for Mac makes life easier for the cross-platform crowd.

We’ve known for months that Microsoft has been working on Office 2016 for both Windows and Mac. I’ve been playing a bit with the Office 2016 for Windows preview for a while, but I haven’t done much with the Mac preview.

Highlights

Mac Office 2016 User Interface

The Office 2016 for Mac user interface is very much like that for Office for Windows. It no longer feels like a toy program built by an 8th grader in shop class. This is a very good sign. Now Office looks and feels the same across all platforms: Windows, Mac, Android, iOS.

New Charts (Windows only, so far)

Earlier, Microsoft Introduc[ed] new and modern chart types now available in Office 2016 Preview. The latest Office 2016 preview features a few new chart types, including some that were previously only available to users of add-ins like the Peltier Tech Chart Utility. Microsoft has added waterfalls, histograms, paretos, treemaps, and sunbursts.

Lowlights

Office 365

The Windows Office 2016 preview has only been available through Office 365 (the kludgy online subscription service). I never can remember which of my umpteen Microsoft logins is valid for Office 365, and even when I look it up, the correct user ID and password don’t work the first time. Updating Windows Office 2016 preview versions has never gone without several major hitches for me, followed by complete uninstallation then reinstallation from scratch.

So far, Mac Office 2016 is only available via Office 365, which Microsoft treats as a good thing. In Windows, installing from Office 365 wipes out any older versions of Office, so you can’t run Office 2007, 2010, and 2013 side-by-side to support different clients. On the Mac, I was glad to see that I could install Office 2016 without wiping out the previously installed Office 2011. I did have to rebuild the LaunchPad links to Office 2011 and put the icons back onto the Dock, but that only wasted an hour.

Mac Office 2016 VBA

This is the painful one. Mac Office 2016 does still support VBA, of course, and Microsoft has rebuilt the VBA Editor from scratch, which was drastically. Unfortunately the VBA Editor is not really ready to use: you can’t insert new modules or UserForms, and while you can edit existing modules, you can’t even view the UserForms.

Microsoft is making small improvements to the VB editor, and these improvements will be part of the monthly updates. but they are not expecting to make the editor as functional as that in Windows. They encourage developers to build their add-ins in Windows versions of Excel or in Excel 2011, then test in Mac Excel 2016.

Mac Office 2016 Custom User Interface Elements

First, a little background…

In Office 2003 and earlier, developers and users could construct custom menus and toolbars to access built-in and custom functionality.

Starting in Office 2007, Office for Windows has had the ribbon. It could readily be modified for developers willing to delve into RibbonX, a variation of XML used to control and customize the ribbon. Since Office 2010, the ribbon could also be easily modified through the user interface. While custom menus and toolbars were no longer supported in Office 2007, any custom menu items were put into a special Add-Ins ribbon tab. Windows Office add-in developers, myself included, have migrated to custom ribbon interfaces, to great effect.

Mac Office 2011 introduced a low-functioning ribbon, which did not allow any customization programmatically or through the Office interface. However, the custom menus and toolbars that were discontinued in Windows Office are still supported in Mac Office 2011.

Mac Office 2016 has a better ribbon than 2011, but it is still not customizable by the user, nor can it be it controlled using RibbonX. Custom menus and toolbars are no longer supported in Mac Office 2016, but there is an Add-Ins ribbon tab to handle legacy menu items.

Microsoft is working on implementing RibbonX interface controls for Mac Excel, and it will be rolled out as part of a regular update. Until then a Mac Office add-in developer should still build legacy menu-type user interfaces.

Sandbox Mode

One more drawback is that Mac Office 2016 now runs in a sandboxed mode, so certain features are more difficult for developers to implement, features such as accessing folders and opening files. I don’t know the full ramifications of this behavior, since my code isn’t yet running properly in Mac Office 2016.

Bottom Line

Therefore, if you are using any add-ins to extend the features of Mac Office, you should not upgrade until you know whether these add-ins will work properly in Mac Excel 2016.

Peltier Tech Charts

Peltier Tech Chart Utility 2.0

Peltier Tech has taken steps to prepare for Microsoft Office 2016. The Windows version of the current Peltier Tech Chart Utility was designed for Office 2007 through 2013, and it can to run in the preview of Office 2016, but it will not work in the eventual commercial release of Office 2016. As always, there are a few minor changes that need to be made to accommodate new features and syntax of the new Office version.

Peltier Tech Charts for Excel 3.0

In addition, Peltier Tech is working on a major upgrade to the utility, called Peltier Tech Charts for Excel. This edition will work in Windows Office 2016, but also 2013, 2010, and 2007. It will also run in Mac Office 2016, when that has become capable of supporting add-ins, and of course, it will run in Mac Office 2011. The exciting part is that the same add-in file will work in both Windows and Mac, so users stuck between platforms will not need to license two separate add-ins.

The new Peltier Tech Utility will feature all of the old charts, including the ones that Microsoft is introducing in Office 2016, because some Peltier Tech customers will still be using older Office versions. New chart types are being developed, including grouped box plots and sensitivity tornado charts. If you have a favorite chart type that neither Microsoft nor I have made available, mention it in the comments below, and Peltier Tech will add it to the long long list of suggestions.

The new Utility will also offer some new features, including more powerful chart data manipulation tools. Again, if there’s a feature you can’t live without, let us know in the comments.

The new Utility will likely have Standard and Advanced editions like the current Utility, and these editions will likely be priced at the same levels as the current utility. Existing users of the current Utility will be able to upgrade at a 50% discount, and users of the older family of utilities will qualify for a 25% discount. In addition, bulk and academic discounts will be available.

Another exciting development is Peltier Tech Charts for PowerPoint, and in all likelihood Peltier Tech Charts for Word. These are still now in their infancy, but it will bring easier linking of standard PowerPoint and Word charts to Excel data, as well as at least a partial menu of Peltier Tech custom charts.

Peltier Tech Charts for Excel 3.0 Beta

So far the new utility is only in the alpha testing stage, meaning new pieces are being incorporated and tested in-house only.

In about a month a beta version will become available. Current and potential new users will be able to test and comment on it.

During the beta testing period, users of the current Utility will be able to lock in their upgrade at a discount of 60%, users of the older family of utilities at 35%, and new users at a discount of 10%.

More details will become available over the coming weeks.

Peltier Tech Chart Utility

Highlight Min and Max Data Points in an Excel Chart

Here is a simple data set and an XY Scatter chart. We want to highlight the minimum and maximum points in the chart.

We could manually select the high data point and format it, then select the low point and format it. No big deal for a one-off chart. But if the data changes, we may have to manually change formatting again.

Instead we’ll add a couple columns to the data range, and use them to determine where to plot the minimum and maximum values.

The formula in cell C2 (copied and pasted in C3:C10) is

=IF(B2=MAX(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column C only if it’s the maximum value in column B. Otherwise column C contains the #N/A error value, which is not plotted with a marker in an Excel scatter or line chart.

Likewise, the formula in cell D2 (copied and pasted in D3:D10) is

=IF(B2=MIN(B$2:B$10),B2,NA())

which puts the value of column B into the same row of column D only if it’s the minimum value in column B. Otherwise column D contains #N/A.

When we select the chart, we can see the chart data highlighted in the worksheet.

We can drag to extend the blue range to include columns C and D.

And now columns C and D are plotted in the chart. Column C (minimum) is plotted as a blue marker, and column D (maximum) as an orange marker).

Let’s do a little formatting. Right click on the Max point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Max”, and choose the bright blue text color. Format the marker so it’s an 8-point circle with a 1.5-pt matching blue border and no fill.

Right click on the Min point, and choose Data Labels. Select the label and choose the Series Name option, so it shows “Min”, and choose the gold text color. Format the marker so it’s an 8-point circle with a 1.5-pt gold border and no fill.

The nice thing about using this formula-based approach, instead of manually formatting everything, is that if the data changes, the new Min and Max are highlighted. In fact, if multiple points have the same min and max values, all will be highlighted.

This approach, of adding a series for any data you want to highlight, is very flexible. You don’t need to only highlight minimum or maximum values, you can highlight anything that you can write a formula for.

Peltier Tech Chart Utility

VBA: Assign Names to Chart Series

If you select a well-defined worksheet range and insert a chart, Excel parses the range and assigns values (Y values), categories (X values), and series names based on its analysis of the range.

For example, if you select the range C2:F8 shown below, Excel notices that the top left cell C2 is blank, so Row 2 and Column C will be treated differently. Excel also notices that there are more rows than columns, so the data will be plotted by column, with categories in the first column and series names in the first row. When you insert a chart, the worksheet is highlighted showing that Column C (purple highlights) is used as category labels (X values), Row 2 (red highlights) is used as series names, and the rest of the area (blue highlights) is used as Y values.

Nice Data Leads to a Nice Chart

If you always arrange your data nicely like this, you will rarely have to fix up what Excel uses for X values, Y values, and series names.

Assign Names from Cells in Front of Y Values

But sometimes your chart hasn’t been no nicely assigned to the worksheet range. The chart below has series plotted by row, but the first column has not been used for series names. Instead the series have the dreaded “Series1”, “Series2”, etc., as their names.

Chart plotted by row with no series names

Similarly, this chart has series plotted by column, but the first row has not been used for series names. Again, the series are labeled with the dreaded “Series1”, “Series2”, etc.

Chart plotted by column with no series names

You can manually name the series, using the Select Data command from the ribbon or from the right click menu, or editing the series formula. But it’s not too much trouble to write a little code to find the appropriate cells to name the series in a chart.

I’ll start with a routing that works on one chart series. It gets the series formula, parses out the argument that specifies the series Y values, and finds the range containing those values. (If the Y values are not from a range, for example, they are hard-coded as an array right in the formula, the code skips the series.) If the Y values range is a row, the code finds the cell to the left of this range in the same row; if the Y values range is a column, the code finds the cell above this range in the same column. This cell is used to create a link for the series name.

Sub Series_AssignNameToCellBeforeYValues(srs As Series)
  Dim sFmla As String
  Dim vFmla As Variant
  Dim sYVals As String
  Dim rYVals As Range
  Dim rName As Range
  
  sFmla = srs.Formula
    ' e.g. =SERIES("Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1)
  sFmla = Replace(sFmla, "=SERIES(", "")
  sFmla = Left$(sFmla, Len(sFmla) - 1)
    ' e.g. "Name",Sheet1!$A$2:$A$10,Sheet1!$B$2:$B$10,1
  vFmla = Split(sFmla, ",")
  
  If UBound(vFmla) + 1 - LBound(vFmla) = 4 Then
    sYVals = vFmla(LBound(vFmla) + 2)
      ' third element (first + 2)
      ' e.g. Sheet1!$B$2:$B$10
    On Error Resume Next
    Set rYVals = Range(sYVals)
    On Error GoTo 0
    If Not rYVals Is Nothing Then
      If rYVals.Cells.Count > 1 Then
        On Error Resume Next
        If rYVals.Columns.Count > rYVals.Rows.Count Then
          ' by row, take cell to left
          Set rName = rYVals.Resize(1, 1).Offset(, -1)
        Else
          ' by col, take cell above
          Set rName = rYVals.Resize(1, 1).Offset(-1)
        End If
        On Error GoTo 0
        If Not rName Is Nothing Then
          srs.Name = "=" & rName.Address(, , , True)
            ' e.g. "=Sheet1!$B$1"
            ' use formula notation so it links to cell
        End If
      End If
    End If
  End If
End Sub

That’s great for one series. Use the following code to call the above procedure for each series in a given chart:

Sub Chart_AssignNameToCellBeforeYValues(cht As Chart)
  Dim srs As Series
  For Each srs In ActiveChart.SeriesCollection
    Series_AssignNameToCellBeforeYValues srs
  Next
End Sub

Use this to assign names for the active chart:

Sub ActiveChart_AssignNameToCellBeforeYValues()
  If Not ActiveChart Is Nothing Then
    Chart_AssignNameToCellBeforeYValues ActiveChart
  End If
End Sub

Use this to assign names to every chart in the active sheet:

Sub AllCharts_AssignNameToCellBeforeYValues()
  Dim chtob As ChartObject
  For Each chtob In ActiveSheet.ChartObjects
    Chart_AssignNameToCellBeforeYValues chtob.Chart
  Next
End Sub

Here is our first chart, plotted by row, with its series names now defined: note the red highlighted range containing the same labels now displayed in the legend.

Chart plotted by row with assigned series names

Here is the second chart, plotted by column, with names assigned.

Chart plotted by column with assigned series names

In fact, the alignment of all series need not be the same, since the routine works series-by-series. The top chart below uses the first two rows of data from the first chart above and the last two columns from the second chart, with no series names assigned. The bottom chart shows how the code has correctly assigned names.

Charts with mixed rows and columns, without and with series names

Assign Names from an Arbitrary Range

What if the series names are in a range that isn’t connected to the rest of the chart data? Here I want to use series names from the range below the chart. The highlights in the worksheet only show X and Y values for the selected series.

Chart with no series names

Here is a routine that assigns names from a given range to series in a given chart:

Sub Chart_AssignNamesFromRange(cht As Chart, rng As Range)
  Dim iSrs As Long
  For iSrs = 1 To cht.SeriesCollection.Count
    If iSrs > rng.Cells.Count Then Exit For
    cht.SeriesCollection(iSrs).Name = _
        "=" & rng.Cells(iSrs).Address(, , , True)
  Next
End Sub

This routine asks the user to select a range, then calls the routine above to assign names from this range to the active chart:

Sub ActiveChart_AssignNamesFromRange()
  Dim myRange As Range
  On Error Resume Next
  Set myRange = Application.InputBox( _
      "Select a range containing series names for the active chart.", _
      "Select Range", , , , , , 8)
    ' Type 8 to input a range
  If Not myRange Is Nothing Then
    Chart_AssignNamesFromRange ActiveChart, myRange
  End If
End Sub

Here is the dialog, which I’ve already used to select a range containing labels.

Select a range dialog

Here the chart’s series have been labeled. The highlights show X and Y values and a series name for the selected series.

Chart with series names from an arbitrary range

Peltier Tech Chart Utility

Reciprocal Chart Axis Scale

Many physical phenomena exhibit behavior that shows faster rates or greater solubilities at higher temperatures. An Arrhenius equation gives the following relationship between some measure of reaction rate or chemical solubility and temperature:

K = A exp (-Q/RT)

where K is the rate or solubility, A is a constant, Q is an activation energy, R is the gas constant, and T is the absolute temperature. The equation above can be restated to:

log (K) = A' - Q/RT

A chart can be constructed with log(K) on the Y axis and reciprocal temperature (1/T) on the X axis; the slope of this line indicates the activation energy (actually -Q/R) and the intercept is the new constant A’. This page describes the construction of one variation of this type of chart.

Arrhenius Plot

Microsoft Excel does not offer a built in capability to chart reciprocal data, but the technique described here allows you to simulate a reciprocal scale along a chart axis (the horizontal axis in this example). A dummy series is plotted along the X axis, and formatted to look like an axis, with error bars as customized vertical gridlines and data labels as customized axis tick labels.

In addition, we will use a dummy series along the Y axis to display selected minor tick mark labels.

Data

The tables below show the dummy experimental data (shaded blue), along with the data I used to construct the simulated X axis (shaded orange) and Y axis (shaded green) in this example. The only significance of this data is that I chose it to fall roughly on a straight line on the log K vs. 1/T chart. This line has a positive slope because the reciprocal temperature increases from right to left (temperature decreases from left to right).

The temperatures in column F are numbers with a custom number format of “0°C”. The reciprocal temperatures in columns C and G were defined as possible: For a Celsius temperature in cell B4, the formula in cell C4 is:

=1000/(B4+273)

Dummy Horizontal Axis

Reciprocal temperatures (Column G) are used for the X values, but the original Celsius temperatures (Column F) are used for the X axis labels, because they are more meaningful.

Y values (Column H) correspond to the minimum on the Y axis (it will eventually be a logarithmic axis with a min of 1 and max of 100).

Dummy Vertical Axis

X values for the Dummy Vertical Axis correspond to the maximum on the X axis (Column J, X=3.9526), not the minimum, because the values are charted in reverse order.

The Y axis labels (Column K) were chosen to show selected minor tick labels, to supplement the built-in major tick labels (1, 10, and 100).

The Chart

This is an XY Scatter plot of the Rate Y (Column D) as a function of Temperature X (Column B). This is not the chart we will start with.

We will start with this XY Scatter plot of Rate Y (Column D) as a function of Reciprocal Temperature X (Column C).

First I’ll construct the custom X axis labels and vertical gridlines, then I’ll add custom Y axis minor tick labels. I’m using the same light gray color for all axis lines and gridlines in the chart.

Horizontal (Reciprocal) Axis Construction

We want temperature to increase from left to right, so reciprocal temperature should increase from right to left. To accomplish this, format the horizontal axis, and select the Values in Reverse Order option.

We want the vertical axis on the left, so while formatting the horizontal axis, select Axis Crosses at Maximum Axis Value.

We want the horizontal axis to go from -20°C to 240°C (on a reciprocal basis), so set the min to 1.9493 and the max to 3.9526. The major spacing doesn’t matter, because we will not be using the built-in axis labels or vertical gridlines.

Select the default vertical gridlines and press Delete.

Format the horizontal axis, and for Label Position, select the none option.

Copy the dummy horizontal axis data from columns G and H, select the chart, and use Paste Special from the Home tab of the ribbon to add the copied data as a new series. These are the orange circles near the bottom of the chart.

We’ll use error bars for the chart’s vertical gridlines. Select the dummy horizontal axis data points, and add error bars using the plus icon next to the chart (Excel 2013) or the Chart Tools > Layout tab (Excel 2007-2010). Since it’s an XY Scatter chart series, it gets both horizontal and vertical error bars.

Delete the horizontal error bars. Format the vertical error bars using the Plus direction only and without end caps, with a value of 99 (the Y maximum of 100 minus the Y minimum of 1), and using the light gray line color.

Right click the dummy horizontal data series and select Add Data Labels. They were added above the points, and I’ve used the same orange as the font color to clarify this tutorial.

Several techniques for using custom labels as chart series data labels are described in Apply Custom Data Labels to Charted Points elsewhere on this blog. I’ve used Excel 2013’s Value From Cells label option, using the Celsius temperatures in column F.

Change the data label position from above to below the points.

Change the alignment of the data labels to “Rotate Text Up” on the Home tab of the ribbon or “Rotate 270°” in the Format Data Labels dialog.

Finally, resize the plot area to increase the margin between the plot area and the bottom of the chart. This allows the axis title and data labels to appear without overlapping.

The custom horizontal axis is now finished. Hide the last evidence of your trickery by reformatting the dummy axis series to show no markers.

Vertical (Logarithmic) Axis Construction

Start fixing up the vertical axis by formatting it, and selecting Logarithmic Scale, and keep the default base 10. The error bar/gridlines now span the entire height of the chart.

Add minor horizontal gridlines and format them with the same line color as the other gridlines and axes.

Copy the green-shaded data for the dummy vertical axis series from columns J and K, select the chart, and use Paste Special to add the data as a new series.

Add data labels to this new series to the left of the data points, using the default Y value labels. These new labels are shown in green in the chart below. The labeling of the vertical axis is now complete.

Format the vertical dummy axis series to use no markers, hiding any evidence of your axis deception.

Here is the finished chart, with all labels in the same dark gray font color.

Extension to Other Custom Axes

The technique shown here to add gridlines and labels corresponding to a reciprocal temperature scale can be applied to any arbitrary scale type, limited only by your ability to transform the desired scale into a plain vanilla linear axis. You could generate a Probability Scale Axis, or put Category Labels Along a Vertical Axis, or apply more Conventional Scientific Notation Axis labels.

Peltier Tech Chart Utility

Peltier Tech Chart Utility

 

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