Peltier Tech

Peltier Technical Services - Excel Charts and Programming

Peltier Tech Excel Charts and Programming Blog

Plot Values Along the Axis of an Excel Chart

by 6 Comments

Thursday, August 13, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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

Posted: Thursday, August 13th, 2015 under Chart Types.
Tags: , , .
Comments: 6

Peltier Tech Charts for Excel

Microsoft MVP Logo

Simple XY Quad Chart Using Axes as Quadrant Boundaries

by 4 Comments

Wednesday, July 29, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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.

Posted: Wednesday, July 29th, 2015 under Chart Axes.
Tags: , , .
Comments: 4

Peltier Tech Charts for Excel

Microsoft MVP Logo

Salary Chart: Plot Markers on Floating Bars

by 38 Comments

Tuesday, July 21, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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

Select the XY scatter series, and press Ctrl+1 (numeral one) to open the Format Series dialog or task pane. Under Series Options, select Primary Axis, then 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.

More Combination Chart Articles on the Peltier Tech Blog

Posted: Tuesday, July 21st, 2015 under Combination Charts.
Tags: .
Comments: 38

Peltier Tech Charts for Excel

Microsoft MVP Logo

Click a Worksheet Cell to Change a Chart

by 6 Comments

Wednesday, July 15, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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

 

Posted: Wednesday, July 15th, 2015 under VBA.
Tags: .
Comments: 6

Peltier Tech Charts for Excel

Microsoft MVP Logo

Office 2016 For Mac Is Here (well…)

by 22 Comments

Monday, July 13, 2015 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

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

Posted: Monday, July 13th, 2015 under Excel 2016.
Tags: , , .
Comments: 22

Peltier Tech Charts for Excel

Microsoft MVP Logo

Peltier Tech Charts for Excel