Make Technical Dot Plots in Excel

What’s a “Technical” Dot Plot?

This chart is crisp and well suited for publication in a technical journal, hence the name I’ve given it, “Technical” Dot Plot. The chart below show results of a hypothetical clinical trial, where the X values (categories) are three different cleansing approaches, and the Y values are the individual responses, where a lower value indicates fewer incidences of infection.

Columns of Dots Plot

This “technical” dot plot chart shows each individual response, to give you an idea of the distribution of results. This is more detailed than a simple average, or even a box plot, which simplifies the data distribution into its min, max, median, and quartiles. If desired, each category could have different marker (dot) shapes, sizes, or colors. However, that isn’t necessary.

Other Kinds of Dot Plot

If you Google “Dot Plot”, or search Wikipedia or any other resource, you’ll learn that the phrase “dot plot” can mean many different things. In addition to what I call the “Technical” Dot Plot shown above, there are also “Cleveland” Dot Plots, “Kindergarten” Dot Plots, and Scatter Plots. Technical Dot Plots, Cleveland Dot Plots, and Scatter Plots are all effective means of displaying data. Unfortunately people who display data effectively do not always come up with effective and unique names for their charts.

“Cleveland” Dot Plot

This type of graphic is named for William Cleveland who described them in a 1984 paper with coauthor Robert McGill. They were presented nicely by Naomi Robbins in Dot Plots: A Useful Alternative to Bar Charts.

Dot Plot per William Cleveland

Peltier Tech Charts for Excel can create this type of chart as easily as any built-in Excel chart.

These are often a better alternative to horizontally-oriented line charts, especially since the category labels can be reasonably long and still remain horizontal for improved readability.

Line Chart

“Kindergarten” Dot Plot

I call this the “Kindergarten” Dot Plot, because it feels more like a fingerpainted art project than a serious means of visualizing data. It is built by adding a dot to the chart every time you encounter the given value in a set of numbers. See another value, dip your finger in paint and put another blot on the paper.

Kindergarten Dot Plot

To me, the histogram below is a better representation of the distribution above. The simple bars show the values without distracting me into counting all those stupid little dots.

Histogram

Peltier Tech Charts for Excel can create histograms easily in Excel. Excel’s old Analysis Toolpak used to make column charts that were passed off as histograms, and Excel 2016 for Windows has finally introduced native histograms.

You can represent a histogram as a line chart, below left, often called a “Probability Polygon”. It’s not a terrible representation, though I prefer the histogram.

Often you’ll see a probability polygon shown with markers and without line segments, and it’s also called a dot plot, below right. I find this inferior, because the dots are unconnected and seem to be strewn across the chart.

Probability Polygon and another Dot Plot

Scatter Plot

A Scatter Plot (a/k/a XY Chart, Scatter Chart, etc.) is often called a Dot Plot because dots (markers) are used to indicate individual data points.

Scatter Plot

Make a Technical Dot Plot

I’ll describe a few different ways to create Technical Dot Plots. The end result is the same, but the protocol differs because you may have different data layouts available for use.

Regardless of data layout, we will make a combination chart, using a column chart type to get the nice horizontal axis labels and XY Scatter types to get the dots.

Data Layout A – Multiple X and Y Series

The first data layout has separate X and Y values for each category in the chart. The X and Y ranges for each set of dots may be next to each other:

or the X and Y ranges for each set of dots may be separated from each other:

You also need a small table with the category names and zero values. Select this small table, and insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Copy and select the orange shaded data range, either B2:C12 in the example with the X and Y ranges together or B2:B12 and E2:E12 in the example with the separated X and Y ranges. To select multiple areas, select the first area, then hold Ctrl while selecting additional areas.

Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below left).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly reduces steps needed for this in a combination chart like this.

Copy and select the gray shaded data range, either D2:E12 in the example with X and Y ranges together or C2:C12 and F2:F12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as new series, values in columns, series name in first row, categories in first column. The result is a set of gray dots added to the chart (below right). Excel remembers that the previous added series was changed to an XY type with markers and no lines on the primary axis, so it uses these settings for the new series. The X values of 2 position the gray dots above the second category along the X axis.

Copy and select the gold shaded data range, either F2:G12 in the example with X and Y ranges together or D2:D12 and G2:G12 in the example with separated X and Y ranges. Select the chart, and use Paste Special to add the data as before. The result is a set of gold dots added to the chart (below left). Excel again applies an XY chart type with markers and no lines and assigns the series to the primary axis. The X values of 3 position the gold dots above the third category along the X axis.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready for publication.

Data Layout B – Single X with Multiple Y Series

The second data layout has a single set of X values with three sets of Y values, as shown below. This is easier than the previous data layout, because it requires only one Copy-Paste-Special cycle. Don’t worry about the blank cells; Excel will ignore them in the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as three new column series (below left). The categories are temporarily messed up: the new series have many more points, forcing the three original categories to the left.

Right click on the first added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Change the chart type of the second and third added series from column to XY Scatter, so that there are three sets of colored dots on the secondary axes (below left).

Select the first series of dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1 align the points above the first category along the horizontal axis (below right).

Format the second and third set of dots so they are also plotted on the primary axis (below left). The X values of 2 and 3 position the dots above the second and third categories on the X axis.

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly streamlines the process to build a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

The technical dot plot is ready to go.

Data Layout C – Single X and Y Series

The third data layout has a single set of X values and a single set of Y values, as shown below. This is the easiest of all, because it requires only one Copy-Paste-Special cycle, and there is only one series to be modified once it’s been added to the chart.

You also need a small table with the category names and zero values. From this small table, you insert a column chart. The chart appears to contain no data, because the zero values produce bars with zero height. Format this chart now (or later) as appropriate.

Select and copy the data range (B2:E32 in the example above). Select the chart, then go to Home tab > Paste dropdown > Paste Special, and choose the options shown in the dialog below, to add the data as new series, values in columns, series name in first row, categories in first column.

This results in the data being added as a new column series (below left). The categories are temporarily messed up: the three original categories are forced to the left because the new series has way more points.

Right click on the added series, and choose Change Series Chart Type from the pop-up menu. Choose the XY Scatter type with markers and no lines. The result is a set of orange dots, plus secondary X and Y axes added to the chart (below right).

Select the  series of orange dots and press Ctrl+1 (numeral one), the shortcut to open the Format Selected Object in Excel. Change the series from secondary to primary axis. Without an explicit X axis for the series, the X values of 1, 2, and 3 align the points above the first, second, and third category along the horizontal axis (below right).

Excel 2013 introduced a new Change Chart Type dialog which allows you to change chart types and axis of multiple series at once; this greatly simplifies formatting of a combination chart like this.

If you don’t need different colors for the different sets of dots, format them all the same (below right).

Your new technical dot plot is good to go.

Peltier Tech Chart Utility

Solve xkcd’s Velociraptor Problem with Excel

Velociraptor

The xkcd Velociraptor Problem #1

In Substitute, we learn of a substitute teacher’s “real-life” math problems, which include the following:

Velociraptor Problem - xkcd Setup

Ha, ha, funny commentary on a frustrated old substitute math teacher. (Disclosure: my wife is an 8th grade math teacher, and frustrated is often descriptive of her job.)

So how far could you get before you become Velociraptor Chow? Rhett Allain, a science writer at Wired Magazine, shows us how to figure this out in Here’s How to Solve the xkcd Velociraptor Problem With Code. Prof. Allain describes a numerical approach to the problem using some physics and a bit of python code. Essentially you have some initial conditions, such as speed and position of predator and prey, and some boundary conditions, such as acceleration and maximum speed of each. You select a suitably short time increment, compute the speed due to acceleration and distance traveled based on speed, and update the conditions. If the dinosaur hasn’t yet caught the primate, you again increment the time, update the conditions, and so on, until the two participants coincide in space and time.

You could use your best 8th grade algebra (or my wife’s) and compute the solution analytically, but that’s more suited to paper and pencil, not to a computer. And the numerical approach can be applied to many phenomena, physical and other (including financial).

An algebraic solution?
An algebraic solution?

We’ll use Excel to carry out these same calculations, both as a set of worksheet formulas and as a VBA routine.

Calvin and Hobbes
In his Calvin and Hobbes comic strip, Bill Watterson has touched on velociraptor-human conflict, not as a physics problem but as a solution to an ecological problem (human overpopulation).

Approach 1. Using Excel Table with Simple Formulas

Using an Excel table allows you to make iterative calculations easily. Once you get the formulas right, you can then extend the calculations by adding rows to the table.

Below is the initial setup of the velociraptor problem. Calculations are compiled in a table in the top left of the worksheet. Parameters for the problem are listed in a range nearby and are used in definitions for Names that make setting up the problem easier (see definitions below). The chart plots positions of the velociraptor and of the human on the Y axis vs. time on the X axis.

Velociraptor Problem - Simple Excel Table - Initial

This screen shot of Excel’s Name Manager dialog shows the Names which were defined to facilitate calculations. Delta_t is the time increment between calculated time points in the table. XstartV and XstartH are the initial positions of the velociraptor and of the human. AccelV and AccelH are the acceleration for the velociraptor and for the human. VmaxV and VmaxH are the maximum running speeds for the velociraptor and for the human.

Velociraptor Problem - Defined Names

The table contains these column formulas:

Time
=IF(ROW()-ROW(Table1[#Headers])=1,
    0,
    OFFSET([@Time],-1,0)+Delta_t)
Vvel (velocity of the velociraptor)
=IF([@Time]=0,
    0,
    IF(OFFSET([@Vvel],-1,0)>=VmaxV,
       VmaxV,
       MIN(OFFSET([@Vvel],-1,0)+AccelV*Delta_t,VmaxV)))
Xvel (position of the velociraptor)
=IF([@Time]=0,
    XstartV,
    OFFSET([@Xvel],-1,0)+[@Vvel]*Delta_t)
Vhum (velocity of the human)
=IF([@Time]=0,
    0,
    IF(OFFSET([@Vhum],-1,0)>=VmaxH,
       VmaxH,
       MIN(OFFSET([@Vhum],-1,0)+AccelH*Delta_t,VmaxH)))
Xhum (position of the human)
=IF([@Time]=0,
    XstartH,
    OFFSET([@Xhum],-1,0)+[@Vhum]*Delta_t)

This screenshot shows the table populated to an elapsed time of 2 seconds. The human has just reached his maximum velocity, but the velociraptor is still accelerating. The dinosaur has gotten slightly closer to the human.

You can extend the chase by extending the table, simply by clicking on the small angle-iron at the bottom right corner of the tab and dragging it down as far as needed.

Velociraptor Problem - Simple Excel Table - In Progress

Below, the table has been filled down to 6.5 seconds (the rows between 1 sec and 5 sec have been hidden). We see from the chart that the velociraptor has overtaken the human, and I’ve indicated with red text the row in the table where the position of the velociraptor has first passed the position of the human (row 62, 5.9 sec).

Velociraptor Problem - Simple Excel Table - End of Chase

This simplistic model doesn’t stop when the velociraptor reaches his dinner. We need to insert some intelligence into the formulas in our table.

Velociraptor

Approach 2. Using Excel Table with More Detailed Formulas

Using a more detailed table allows you to calculate the point of intersection of the paths of the velociraptor and the human, and stop the chase at that time.

Below is the initial setup of the velociraptor problem.

Velociraptor Problem - Detailed Excel Table - Initial

There are a few additional columns:

TTime (adjusted time)
=IF(ROW()-ROW(Table14[#Headers])=1,
    0,
    IF(OFFSET([@TTime],-1,0)<>OFFSET([@Time],-1,0),
       NA(),
       IF([@Xvel]<=[@Xhum],
          [@Time],
          (OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0))/
            (OFFSET([@Xhum],-1,0)-OFFSET([@Xvel],-1,0)
            -[@Xhum]+[@Xvel])*Delta_t+OFFSET([@Time],-1,0))))
XXvel (adjusted position of the velociraptor)
=IF([@Time]=[@TTime],
    [@Xvel],
    OFFSET([@Xvel],-1,0)+[@Vvel]*([@TTime]-OFFSET([@TTime],-1,0)))
XXhum (adjusted position of the human)
=IF([@Time]=[@TTime],
    [@Xhum],
    OFFSET([@Xhum],-1,0)+[@Vhum]*([@TTime]-OFFSET([@TTime],-1,0)))

The formula for TTime does a lot of work. It’s equal to zero in the first data row, it fills with #N/A after the velociraptor reaches the human, and if the velociraptor catches the human during the current time increment, it interpolates to find the precise time that this happens (see the red entries in row 61).

Velociraptor Problem - Detailed Excel Table - End of Chase

The chart shows the paths of the predator and prey only up to the point of capture, at 5.8375 sec, or 29.325 m from the human’s initial position.

Velociraptor Problem - Detailed Excel Table - Chart

Velociraptor

Approach 3. Using Excel VBA

You can use Excel VBA to solve this velociraptor problem, and animate the chart which illustrates the chase. Initial and boundary conditions are found in the small table in columns G and H.

Columns A through E are the calculations, as in the first approach, except the results are not calculated by worksheet formulas, but instead are calculated by VBA and output to the table as values. These values are plotted in the larger chart.

The small table in columns J through L show the initial and current (i.e., in the most recently calculated iteration) positions of human and velociraptor. These values are plotted in the smaller chart.

Velociraptor Problem - Excel VBA - Initial

Clicking the Reset Chase button runs the ResetChase procedure (below), which sets conditions back to the start of the chase, by deleting all table rows after the first data row.

Sub ResetChase()
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1)
    Do Until .ListRows.Count = 1
      .ListRows(.ListRows.Count).Delete
    Loop
  End With
  Application.ScreenUpdating = True
End Sub

Clicking the Start Chase button runs the StartChase procedure (below), which starts the chase and runs it until the end.

Sub StartChase()
  Dim tTime0 As Double, tTime1 As Double
  Dim xXhum0 As Double, xXhum1 As Double
  Dim xXvel0 As Double, xXvel1 As Double
  Dim vVhum0 As Double, vVhum1 As Double
  Dim vVvel0 As Double, vVvel1 As Double
  Dim vVmaxH As Double, AccelH As Double
  Dim vVmaxV As Double, AccelV As Double
  Dim DeltaT As Double, DDelTT As Double
  Dim iDelay As Long, iLooper As Long
  Dim ws As Worksheet
  
  ResetChase
  
  ' initialize
  Set ws = ActiveSheet
  tTime0 = 0
  iLooper = ws.Range("Looper").Value2
  xXhum0 = ws.Range("XstartH").Value2
  xXvel0 = ws.Range("XstartV").Value2
  vVhum0 = 0
  vVvel0 = 0
  vVmaxH = ws.Range("VmaxH").Value2
  vVmaxV = ws.Range("VmaxV").Value2
  AccelH = ws.Range("AccelH").Value2
  AccelV = ws.Range("AccelV").Value2
  DeltaT = ws.Range("Delta_t").Value2
  
  ' loop
  Do
    tTime1 = tTime0 + DeltaT
    
    ' calculate human velocity and position
    If vVhum0 >= vVmaxH Then
      vVhum1 = vVmaxH
    Else
      vVhum1 = vVhum0 + AccelH * DeltaT
      If vVhum1 > vVmaxH Then
        vVhum1 = vVmaxH
      End If
    End If
    xXhum1 = xXhum0 + vVhum1 * DeltaT
    
    ' calculate velociraptor velocity and position
    If vVvel0 >= vVmaxV Then
      vVvel1 = vVmaxV
    Else
      vVvel1 = vVvel0 + AccelV * DeltaT
      If vVvel1 > vVmaxV Then
        vVvel1 = vVmaxV
      End If
    End If
    xXvel1 = xXvel0 + vVvel1 * DeltaT
    
    ' has velociraptor caught human?
    If xXvel1 > xXhum1 Then
      DDelTT = DeltaT * (xXhum0 - xXvel0) / _
          ((xXhum0 - xXvel0) + (xXvel1 - xXhum1))
      tTime1 = tTime0 + DDelTT
      xXhum1 = xXhum0 + vVhum1 * DDelTT
      xXvel1 = xXvel0 + vVvel1 * DDelTT
    End If
    
    ' add new time point data to row below table
    With ws.ListObjects(1)
      .ListRows(.ListRows.Count).Range.Offset(1).Value = _
          Array(tTime1, vVvel1, xXvel1, vVhum1, xXhum1)
    End With
    
    ' exit if we're done
    If xXvel1 >= xXhum1 Then
      Exit Do
    End If
    
    ' build in delay if animation runs too quickly on screen
    For iDelay = 1 To iLooper
      DoEvents
    Next
    DoEvents
    
    ' persist previous loop's data
    tTime0 = tTime1
    vVhum0 = vVhum1
    xXhum0 = xXhum1
    vVvel0 = vVvel1
    xXvel0 = xXvel1
  Loop
  
End Sub

As the VBA code runs and data is added to the table, you can watch the chase progress in the two charts. Here is the chase after two seconds.

Velociraptor Problem - Excel VBA - After 2 sec

Here is the chase after four seconds.

Velociraptor Problem - Excel VBA - After 4 sec

And here is the chase at its conclusion.

Velociraptor Problem - Excel VBA - End of Chase

Same result as the table based approaches, except for the added benefit of watching the animation as the VBA calculations proceed.

Velociraptor

VBA: Accuracy vs. Calculation Load

Obviously the accuracy of our numerical solution depends on the size of the time increment we use in our calculations. If we take smaller time increments, we can reduce the error resulting from treating nonlinear behavior (acceleration) as linear. On the other hand, taking smaller time increments means our program has to make more calculations, and therefore it will run more slowly.

I ran a modified StartChase procedure to determine how time increment size affected elapsed time, distance, number of iteractions, total calculation time, and calculation time per iteration. I turned off screen updating during this procedure and did not output the results of each increment to the worksheet, to ignore the time VBA spends communicating with the Excel worksheet.

Here are the effects of increment time on solution accuracy and calculation time. The blue shaded row shows the solution used in the examples above, an increment of 0.1 sec.

Numberical Analysis - Accuracy and Calculation Time

We learn some interesting things if we plot this data.

If we plot computed elapsed time to capture of the human vs time increment (Delta_T), we see a straight line with an almost perfect correlation (below left). The computed elapsed time gets closer and closer to the Y intercept as the time increment decreases. We could say that this Y-intercept is the actual time of capture, and our solutions get closer and closer to predicting it as the error in the incremental calculations is minimized. In fact, the difference between the time increment used above (0.1 sec) and the Y-intercept is only 0.05 sec. Maybe we can decide that our analytical solution using an increment of 0.1 sec is “accurate enough”.

If we plot distance to capture vs time increment (below right), we see a trend, but not a nice linear trend as with the elapsed time chart. However, we see that for time increments of 0.1 sec or shorter, there is barely any deviation from the 0.1 sec computation of 29.325 m.

Numberical Analysis - Accuracy and Calculation Time

Naturally, a smaller time increment will result in more iterations, in an inverse relationship. The chart below left shows a linear scatter chart, which isn’t very interesting; the data for a large increment (0.5 s) is found out on the X axis. Shortening the increment brings the points back along the X axis until they curve around and head up the Y axis. What is happening is shown better in the log-log plot below right. This is a straight line, and the exponent on X is -0.9995, extremely close to the inverse 1/X relationship we expect.

Numberical Analysis - Accuracy and Calculation Time

We would expect a similar inverse relationship between total calculation time and time increment. The linear chart below left shows the same axis-hugging behavior as above, and the log-log chart below right gives us another straight line. This one isn’t as perfectly straight, but a regression on the points for smaller increments (filled points) has a strong correlation and a nearly 1/X relationship.

For the time increment of 0.1 sec from the examples above, we have a total calculation time of 0.05 sec, which is pretty fast for an “accurate enough” solution. If we really want more accuracy, we could even go to a 1 sec solution based on a o.oo2 time increment.

Keep in mind that these calculation times are for a modified procedure, not the procedure that updates the table and chart after each calculation increment. Our 0.1 sec time increment may give the solution in 0.05 sec, but the time we spend watching each increment update the table and chart is more like 4 or 5 seconds.

Numberical Analysis - Accuracy and Calculation Time

Finally, we see that as the number of iterations increases, the calculation time increases linearly.

Numberical Analysis - Accuracy and Calculation Time

 

Velociraptor - Wyoming Dinosaur Center

Peltier Tech Chart Utility

Peltier Tech Update December 2015

Hello patient followers. I haven’t posted for a while, not because I can’t think of anything to write about, but because I’ve been rather busy. I’ll talk about each of these things separately, but I think I need a quick post to say what’s going on.

Peltier Tech Blog

There are plenty of topics that I want to cover. New chart types in Excel 2016. Charting and programming examples. Dozens and dozens of old articles that were written for Excel 97 and need to be updated.

An article that I started in October and still haven’t gotten around to finishing is an Excel-based solution to xkcd’s velociraptor problem, inspired by an article in Wired magazine. I have a formula-based approach and a VBA approach, plus plenty of cool images from a Google search.

Microsoft Excel 2016 for Windows and Mac

I’ve written about how Microsoft has released Excel 2016 for Mac and Excel 2016 for Windows.

The Mac version finally looks enough like the Windows version that I don’t feel totally hobbled while using it. In fact, the look and feel is very much like the Windows equivalent. Of course, it was released prematurely, and a lot of things were not really working yet, particularly on the VBA side. But each month Microsoft releases an update (the latest is 15.17), which fixes a bunch of stuff, hasn’t yet fixed a bunch of other stuff, and into which a few new bugs have crept in. The latest big improvements are that the ribbon can now be customized by add-ins like my own charting utilities, though there is not yet a capability for users to modify the ribbon themselves.

The Windows version is pretty good; I’m using it most of the time now, except for testing. There are a few things I don’t like, for example the way they handle pinned files and folders in the File-Open and File-Save functions. But all in all, it’s working well. The Windows version has monthly updates too, if you’re using an Office 365 subscription.

The coolest thing Microsoft has done is started up a User Voice section on Excel, which allows us, as regular users, to make suggestions for features that would make Excel even better. If you see an idea you like, you can vote on it, and the items with the most votes get attention from Microsoft. Here are some suggestions I’ve made, voted for, or commented on.

Give us a proper NULL() worksheet function – This would let you use NULL() in a formula, for example, and a chart would treat the formula as if the cell were totally blank, and leave a gap in the line. You know what’s cool? Within a couple weeks of posting this idea, someone from Microsoft called me to discuss this function, and now it’s actually being implemented.

Recent Files pane in Excel 2013 was pretty good, but Excel 2016 broke it – This is about how Excel 2016 messed up how pinned files and folders are displayed, which I complained about above. It’s only got 61 votes, so follow the link and add your vote.

Sensible date formatting on X-axis of XY-scatter charts – This would give you a nicer date format for XY charts, without having to use the less-flexible line chart. Only three votes, so I don’t know about this one.

Chart series formatting – UI overhaul – I think the intent of this one is to provide a single dialog to format all of a chart’s series, the way Excel 2013 introduced one dialog to manipulate chart type and axis for all series. 36 votes, so people, share the love.

Go to the User Voice site, read the ideas people have posted, and vote for your favorites. Microsoft is reading these, and commenting on even the ideas without too many votes.

Peltier Tech Charts for Excel

I announced the release of Peltier Tech Charts for Excel 3.0, the latest major upgrade to my popular and awesome Excel charting utilities. This major upgrade makes it compatible with Excel 2016, such that one add-in works on both Windows and Mac computers; no need to buy two licenses if you can’t decide on an operating system. Because Mac Excel 2016 is evolving monthly, I have been spending a lot of time making sure that my software takes advantage of the fixes Microsoft makes (and removing workarounds). I have also been addressing a lot of dumb little problems, and I’ve started outlining some new features. The documentation is admittedly pretty lame, so I have started outlining that work as well.

If you are a licensee of my earlier utilities, email me and I’ll set you up with a discount coupon so you can upgrade on the cheap.

MVP Summit

In November, Microsoft hosted the annual Microsoft Global MVP Summit. I met up with a few dozen Excel MVP colleagues, and we got caught up, had a beer or three, and talked Excel with the Excel Product Group at Microsoft. These folks are working on some cool things, not just the Windows and Mac stuff, but also Excel on all kinds of platforms: I let them talk me into installing Excel on my Android phone, and I was amazed that I could actually do a little work on it. They’re working on improved simultaneous co-authoring of documents, and on more new chart types, and this new chart engine they’ve been developing will make charting faster and more flexible.

Australia and New Zealand 2016

In March 2016 I and several of my Excel MVP colleagues will be traveling to the Southern Hemisphere to present a series of conferences on Excel. We will have two-day sessions in Auckland, Sydney, and Melbourne. Follow the link to read more about Excel Summit South 2016. There will be presentations by the experts, including MVPs, industry leaders, and Microsoft. We’ll have panel discussions and Q&A sessions, and lots of time for off-line chats.

Amsterdam 2016

On May 26, 2016, MVPs Tony de Jonker and Jan Karel Pieterse will host the third annual Amsterdam Excel Summit. I missed the 2014 session, but I was there last year, presented a couple of sessions, and met a lot of people that I’d only known through email or blogs. I’ll be there again in 2016, so come by and visit; I’m really friendly, not as nerdy as my blog would make you think.

On May 27, 2016, I will lead the Excel Charting And Dashboard Masterclass along with Tony de Jonker and David Hoppe. We’ll be teaching about charting, visualization, and dashboards.

Microsoft is Listening

Microsoft has changed a lot, especially in the past couple of years. They’ve really opened up about what they’re working on, and they’re listening much more closely to what others are telling them. Of course, Microsoft pays a lot of attention to us MVPs, both at the Summit and in the mailing lists they host to discuss things with us. But they also are interested in what their regular users are saying. I mentioned User Voice above, where Microsoft program managers are reading the suggestions that users are posting. They also pay attention to the Send-a-Smile/Frown feedback; I always include my email in the things I send in, and I’ve gotten responses on at least a couple of the items I’ve submitted.

This is not the same Microsoft we’ve known all these years.

 

Peltier Tech Chart Utility

Microsoft Releases Excel 2016 for Windows

Today Microsoft officially releases Office 2016 for Windows. We’ve been able to preview it for some time, of course, and the Mac version has been out for a few weeks. But now it’s been let loose.

I have not personally devoted a lot of time with the Excel 2016 for Windows preview. It looks to me a lot like Excel 2013, and what I did confirmed that it behaved very much like Excel 2013, both in Excel itself and in VBA. I spent more time with Excel 2016 for Mac, which also looks a lot like Excel 2013 for Windows, but the back end (VBA) has a lot of differences and a lot of shortcomings which Microsoft is hustling to address. I’ve had to figure out lots of workarounds to make my programs work the way I want them to, and in fact, some of my programs don’t really work yet.

For Excel 2016, Microsoft has introduced several new chart types, described in Introducing new and modern chart types now available in Office 2016 Preview. Several of them are also included in Peltier Tech Charts for Excel, including waterfalls, histograms, Paretos, and box plots. Should I be worried that I’ll lose business to Microsoft? Well, maybe not too worried. I will keep these charts in my product, because people may prefer my chart styles and defaults, and they may have written their own VBA to use my add-in to create charts. And of course, Peltier Tech Charts for Excel has lots more than these few charts.

I did “get to” foray into Office 365, because that was the only way to get either the Mac or Windows preview of Office 2016. That was a little frustrating, because I felt like I no longer had control over my account (seems I have half a dozen accounts with Microsoft, and I never guessed the right credentials whenever I had to log in), and updates could be an adventure. Recently, though, things seem to work more smoothly.

Here are a handful of links to articles on Microsoft’s Office Blog that talk about Office 2106, including topics related to business analytics and some new charts introduced in Excel 2016.

Here are a few articles from users outside of Microsoft

Microsoft is implementing a new development cycle. They’re going to move away from the 3- to 4-year new version cycle, and roll out more substantial updates monthly or so. This will help eliminate bugs and add features more rapidly. It also means they will make Office on all platforms increasingly compatible.

Microsoft has added a new communication feature. If you have a great idea for a new feature, you can suggest it at the Excel Idea Box at UserVoice.com. Other users can review, comment on, and vote on your ideas. I’ve posted Give us a proper NULL() worksheet function; please go and vote for it. I’ve also supported Bring VBA into the modern worldLink the min and max values of a chart axis to cell valueMake it easier to find external links, and Improve the Concatenate Function, among others, and they could also use your votes.

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

Peltier Technical Services is pleased to announce the newest version of its landmark charting utility.

You can skip this article and go straight to Peltier Tech Charts for Excel 3.0 to license your copy. From now until September 30, the 30th anniversary of Microsoft Excel, you can get $10 off using one of these coupon codes:

  • NPTX30A10 to get $10 off the Standard Edition
  • NPTX30B10 to get $10 off the Advanced Edition

If you have a valid license to any version of the Peltier Tech Chart Utility 2.0 (Advanced or Standard, Windows or Mac), you are eligible for a 50% discount, on top of the $10 discount above. Email Peltier Tech for a discount coupon.

If you have a valid license to any of the older standalone Peltier Tech Whatever Chart Utilities (Waterfall, Cluster-Stack, Box Plot, etc.), you are eligible for a 25% discount, on top of the $10 discount above. Email Peltier Tech for a discount coupon.

If you’ve licensed the Peltier Tech Chart Utility in the past month or two, email Peltier Tech and we’ll work out a fair price for an upgrade.

What’s New in Peltier Tech Charts for Excel 3.0

There are several major changes and a lot of minor ones.

First, the utility has been updated to run in Excel 2016 for Windows (out next week) and for Mac (out last month).

Second, the same add-in can run in both Windows and Mac. In the past I had to support two different files, because of incompatibilities between Windows VBA and Mac VBA. But I dug into all of these incompatibilities and found ways around them. Users can now license the utility once and use it on both platforms.

Third, I’ve added a feature to the advanced version of the utility that summarizes your chart’s data, lets you select some or all series in a chart, then moves or resizes their data ranges by a number of rows or columns or to a different sheet in a single operation.

Modify Chart Series Data Dialog

I built it for myself, then decided people might like it, so I put it into the program.

Fourth, I’ve added one new chart type, Clustered Box Plots. This is a nice enhancement to box plots, allowing categories to be grouped and color coded to show patterns more readily.

What’s Still On Tap for Peltier Tech Charts for Excel 3.0

Although Peltier Tech Charts for Excel has been released, there are more things being done to it.

First, there is still a lot of testing to be done in Excel 2016 for Mac. A lot of bugs and inconsistencies have been addressed, but there are still incompatibilities, and there are features that need more development. Exporting charts as image files, for example, which is a victim of an increasingly harsh lockdown by Apple on its hardware.

Second, there are a lot more features, both chart types and other functions, under development. Tornado sensitivity charts, Gantt charts, panel charts, step charts, and run charts. Features to clean up charts, arrange charts and chart elements, extract chart data, quickly apply favorite series formats.

Third, under development are versions of this software that work directly in other Office applications, linking to your Excel data, but building charts in PowerPoint or Word.

License Peltier Tech Charts for Excel Now

Visit Peltier Tech Charts for Excel 3.0 to license your copy. From now until September 30, the 30th anniversary of Microsoft Excel, you can get $10 off using one of these coupon codes:

  • NPTX30A10 to get $10 off the Standard Edition
  • NPTX30B10 to get $10 off the Advanced Edition

If you have a valid license to any version of the Peltier Tech Chart Utility 2.0 (Advanced or Standard, Windows or Mac), you are eligible for a 50% discount, on top of the $10 discount above.

If you have a valid license to any of the older standalone Peltier Tech Whatever Chart Utilities (Waterfall, Cluster-Stack, Box Plot, etc.), you are eligible for a 25% discount, on top of the $10 discount above.

If you are a user of any previous Peltier Tech product, email Peltier Tech for your discount coupon.

Peltier Tech Chart Utility

Peltier Tech Charts for Excel 3.0

 

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