Dynamic Charts in Excel 2016 for Mac

A reader emailed to ask whether you could make a dynamic chart using OFFSET-function-based Names in Excel 2016 for Mac. Good question, and I wondered if he’d encountered some unexpected problem, perhaps a bug, in Mac Excel. So I dusted off my MacBook Pro and tried it out.

Bottom line: There are several ways to make dynamic charts in Excel, and there seems to be no difference other than cosmetic in how they work between different versions of Excel, and between operating system. The protocols are the same for Mac Excel and Windows Excel, and perhaps it’s time for a quick review. This exercise was done completely in Mac Excel 2016, and other than not knowing a few of the shortcuts I use everyday, it was not very different from working in Windows Excel 2016.

Dynamic Charts in Excel

It’s pretty easy to set up data and create a chart in Excel. But once you’ve created a chart, it keeps plotting data from the same cells. If the data in the cells changes, so does the chart, but if the data extends to more cells (or shrinks to fewer cells), the chart doesn’t seem to notice.

Static Excel Chart

There are a couple ways to create charts that will grow with your data. The easiest way is to use Tables as the chart source data. A bit more complicated is to use Excel’s Names to define the series data for your chart. Using Names can lead to more flexibility in defining the data in your charts. I’ll describe how to make dynamic charts using Tables, using Names, and using Names in a more flexible way.

Dynamic Charts Using Tables

The easiest way to make a chart’s contents reflect the size of a range of data is to put the data into a Table.

Tables made their appearance in Excel 2003, and were called “Lists”. These lists were a more structured container for your data, with a database structure of fields (columns) and records (rows), field headers (column headers) and filtering tools. You could sort and filter your data range easily, and any formula that used a whole column of your List updated to automatically keep using that whole column of the list. Lists became the favored source data for charts and also for pivot tables.

In Excel 2007, Lists became known as “Tables”, and their capabilities have been expanded in every version since.

The screenshot below shows the same data and chart as above, but the data is now in a Table.

To get your data into a table, you select it (or select one cell and let Excel figure out how far it stretches), and on the Insert tab of the ribbon, click Table. Excel asks if your table has headers, then it applies a Table style (the yellow style is shown below), it adds AutoFilter dropdown arrows to the field headers, and it puts a small backwards “L” bracket at the bottom right corner of the table.

Excel Chart Based On Table

You can change the size of the Table by clicking and dragging the bracket at the bottom left corner of the Table. If you type or paste data directly below the Table, the Table will automatically expand to include this new data. And a chart that uses all rows of the existing Table will expand accordingly.

Excel Chart Expands to Include Added Table Rows

If you type or paste data directly to the right of the Table, the Table will also automatically expand to include this new data. A chart that uses all of the existing Table will expand accordingly.

Excel Chart Expands to Include Added Table Columns

This little trick of adding a new series if the data expands accordingly is nice, but it requires that the chart already contain all of the Table’s data.

Names (a/k/a “Defined Names”, “Named Ranges”, etc.)

A Name is what Excel calls a variable that resides in a worksheet or a workbook.

Names are often assigned to cells or ranges; for example, you might place a sales tax rate into a cell and name the cell SalesTax, and subsequently use the cell’s name rather than its address in a formula. Because of this Names have been nicknamed “Named Ranges”.

However, the definition of the name includes a formula. If my sales tax rate was stored in cell A1, then my Name SalesTax would have a definition of “=A1”. Because of this, John Walkenbach proposed that Names should be called “Named Formulas”, but he’s smarter than the rest of us, so his suggestion didn’t stick.

We can use Names in our charts, but we need a distinct name for each dynamic range that the chart will need. We’ll need one Name for the X values if the series use the same X values range, and we’ll need one Name for the Y values of each series. In our sample, we will need three Names. I’ll call them XValues, Y1Values, and Y2Values, and I will define them as follows:

XValues
=OFFSET(Names!$A$1,1,0,COUNTA(Names!$A:$A)-1,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by one row and right by zero rows, then makes it as many rows tall as the number of alphanumeric cells in column A minus one (we don’t want to include the “Category” label), and one column wide.

So starting with cell A1, our range begins in cell B1, and is 6 rows tall and one column wide; our final range is A2:A7. It’s easy to see that adding another value into cell A8 will expand this range to A2:A8. However, if we add a value in cell A57, it will also expand our range to A2:A8, so we need to make sure the rows below our data are kept blank.

The other two definitions are easier:

Y1Values
=OFFSET(Names!XValues,0,1)
Y2Values
=OFFSET(Names!XValues,0,2)

We’ve already figured out how large each range needs to be, since the X and Y values have the same number of cells, so both of these OFFSET formulas start with the first name XValues as an anchor, and offset no rows down but one or two columns to the right. If we don’t specify sizes, then the new Names will define ranges the same size as the anchor.

Okay, that’s how to build a formula definition for a Name. Let’s actually create a Name.

On the Formulas tab of the ribbon, click the Define Name dropdown, and select Define Name… This pops up the Define Name dialog, shown below for the Mac. The Windows dialog is a bit more extensive, and Windows Excel has a much better Names Manager (this dialog happens to serve as the Mac’s Names Manager). For a truly powerful Name Manager, you should try out the free Name Manager add-in at the website of my colleague, Excel MVP Jan Karel Pieterse.

Static Excel Chart

If you’ve selected data before opening the dialog, Excel tries to guess how you want to name data based on labels in the top row and left column of the selection. But I’ve cleared all of this so we’re starting fresh.

Here I’ve typed the name of the Name. Note that I’ve included the worksheet name and exclamation point, which means the Name will be “in scope” (i.e., available) for the worksheet “Names”. Otherwise it would be “in scope” for the entire workbook.

Static Excel Chart

Then enter the formula where it says “Select the range of cells”. You can enter any formula that refers to cells, or a formula that calculates a value, or a constant value. I don’t capitalize my function names when I enter them; that way, if there’s an error, Excel won’t capitalize a bad function name. A misspelled keyword is easier to recognize if it is not capitalized (“offfset” vs. “OFFFSET”).

Static Excel Chart

Click the OK button to add the Name and exit the dialog, or click the Plus icon to add the name and keep the dialog open.

The name is listed in the box at the left; the worksheet name is listed as well to remind us that the scope of the Name is limited to that worksheet. The sheet name is removed from the name in the top right box.

Static Excel Chart

You can make sure the name refers to the intended range if you click in the box with the formula defining the Name. With the cursor in the formula, the range A2:A7 is highlighted in the worksheet. Perfect.

Static Excel Chart

Now enter the name and formula for the next Name, and don’t forget to include the worksheet name.

Static Excel Chart

Click the plus icon to add the name, and click in the formula to make sure that the Name references the desired range, B2:B7. Check.

Static Excel Chart

Enter the name and formula for the last Name, remembering to include the worksheet name. Click the plus icon, and check that the formula refers to C2:C7.

Static Excel Chart

Whew! Now we’re finally ready to make our dynamic chart.

Dynamic Charts Using Names

Every chart series has a formula that defines the data in the chart. The blue series in the static chart below is

=SERIES(Names!$B$1,Names!$A$2:$A$7,Names!$B$2:$B$7,1)

This means it uses cell B1 of the sheet Names for the series name (“Alpha”), A2:A7 for the X values, B2:B7 for the Y values, and it’s the first series in the chart. The formula for the orange series is

=SERIES(Names!$C$1,Names!$A$2:$A$7,Names!$C$2:$C$7,2)

We can use the Select Data dialog to modify these, but it’s easiest to simply edit the formula directly.

Select the blue series of the dynamic chart, and observe the formula in the Formula Bar. It probably looks like the first SERIES formula above (I invariably start with a static chart of the data I want to plot dynamically). Edit the formula to read as follows, and press Enter.

=SERIES(Names!$B$1,Names!XValues,Names!Y1Values,1)

If Excel doesn’t like the new formula, make sure you’ve spelled the Names correctly.

Similarly edit the formula for the orange series to read

=SERIES(Names!$B$1,Names!XValues,Names!Y2Values,2)

At first the two charts look the same.

Static Excel Chart

When we select the static chart, we can see the chart’s source data highlighted in the worksheet.

Static Excel Chart

We see the same data highlighted when the dynamic chart is selected. It’s convenient that Excel is smart enough to highlight the chart data even if it is defined by dynamic Names. I’ll select the dynamic charts in the rest of this tutorial to show the range included in these charts.

Static Excel Chart

Now let’s extend the data by a couple of rows. The static chart isn’t clever enough to notice, but the dynamic chart keeps up nicely, illustrated by the highlighted data in the worksheet.

Static Excel Chart

If we extend the data by a new column, the static chart doesn’t change, and the dynamic chart doesn’t add a series to represent the new data.

Static Excel Chart

I’ve added a third chart which shows the new data. I had to add an extra Name to the worksheet:

Y3Values
=OFFSET(Names!XValues,0,3)

and then I had to add a third series to the chart with the following SERIES formula:

=SERIES(Names!$B$1,Names!XValues,Names!Y3Values,3)

My new chart contains all the data, though I had to include it in the chart manually.

Static Excel Chart

Before Excel 2003, the only way to get a dynamic chart was by using Names. It’s a lot of work, and hardly seems worthwhile if using Tables is so easy. But if we know how to make a chart using Names, we can make a dynamic chart that’s more complicated than just expanding to add a row.

Dynamic Charts Using Complicated Names

How about a chart that doesn’t show all the data, but only the last several points. This might be useful if you want to show the last six months of sales, or high temperature for the previous week.

We’ll make a dynamic chart that plots the last five values. The setup is almost identical to the previous dynamic chart, but our definition of XValues will change.

XValues
=OFFSET(Names!$A$1,COUNTA(Names!$A:$A)-1,0,-5,1)

This OFFSET formula uses cell A1 of worksheet Names as a starting point, offsets the range down by the number of alphanumeric cells in column A minus one, and right by zero rows. This means now we’re starting at cell A7 instead of A2. Then we make the range -5 rows high, meaning we count upward, and one column wide. Our new X values range should be A3:A7. After entering the new name as before, check to make sure the correct range is highlighted.

Edit the SERIES formulas of your dynamic chart as we did above. We see that our static chart shows all six points of the data, but the dynamic chart shows only the last five points, categories B through F.

Static Excel Chart

Let’s add a couple rows of data. The static chart is, well, static, but our dynamic chart shows the new last five points, categories D through H.

Static Excel Chart

Summary

Dynamic charts can easily be created in Excel using data ranges from Tables.

With a bit more work dynamic charts can be created using skillfully defined Names. These charts can be more flexible than Table-based dynamic charts, depending on the formula skills of the Excel user. There are a few gotchas that I didn’t mention: some Name definitions seem like they should work, for example, but Excel charts won’t recognize certain functions. Also, some Name names may cause problems, especially those beginning with the letter “c”; you can’t enter them into the SERIES formula, but you can use them in the Select Data dialog.

 

Peltier Tech Charts for Excel

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

Peltier Tech Advanced Chart Utilities for Mac

The Peltier Tech Advanced Chart Utilities for Mac is here.

Over the past month a month, I’ve converted the Advanced Edition of the Peltier Tech Chart Utility so that it works on a Mac as well as in Windows. I’m pleased that every feature from the Windows version has made it into the Mac version, though some options are not available. This is due mostly to the inconsistencies between Office on the Mac and Office in Windows, such as the unavailability of modeless forms and the reduced selection of Paste Special options in MacOffice. A few differences relate to incompatibilities between the two platforms themselves, such as the strange special keys and special key behavior on the Mac (somehow the Mac’s Cmd-Ctrl-Opt-Shift keys provide less functionality than Windows’ Ctrl-Alt-Shift). The lack of a customizable ribbon in Mac Office means the user experience is less fulfilling than in Windows.

 The Charts

The Advanced Edition for the Mac has all the custom charts as the Standard Version for the Mac and for Windows: Waterfall, Box Plot, Marimekko, Cluster-Stack, etc., and all the new charts introduced last month in the Advanced Edition for Windows: More Waterfalls, Diverging Stacked Bar, Paretos and Histograms, and more.

In addition to the charts shown here, any new custom charts developed by Peltier Tech will become part of the Advanced Edition. If you hear from the Peltier Tech twitter feed, the Peltier Tech Facebook page, or the Peltier Tech Blog that a new chart has been added (or a bug has been fixed), email for a new download link so you can keep your license up-to-date.

Custom Chart Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Features

In addition to the helpful features of the Standard Edition, such as Edit Series Formulas, Copy and Paste Series Formats, Label Last Point, and Export Chart as Image File, the Advanced Edition adds such items as Export Range as Image File, Export Charts to PowerPoint and Word, Switch X and Y, and a simpler Error Bar Manager.

In addition to the features shown below, any new functionality developed by Peltier Tech will be added to the Advanced Edition. If you hear via the Peltier Tech twitter feed or the Peltier Tech Blog that a new feature has been added (or a bug has been fixed), email for a new download link so you can keep your utility current.

Feature Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Details

The Advanced Edition for the Mac runs on the latest updates of Excel 2011 for the Mac, and the relevant versions of the Mac OS that supports Excel 2011.

Detail Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Deals

The Advanced Edition retails for $99, just slightly more than the $79 for the Standard Edition. But there are some great introductory deals:

  • If you purchase a license for the Advanced Edition between July 8 and July 15, you get it for the same $79 price as the Standard Edition.
  • If you already have a license for the Standard Edition, send me an email and I will send you a coupon code for a free upgrade to the Advanced Edition.
    • In the future, you can upgrade from the Standard to Advanced Edition for only the $20 difference in price.
  • If you have licensed one of the earlier Waterfall or Cluster-Stack Utilities for the Mac, email me and I’ll send a coupon for half off the $79 introductory price.
    • In the future, if you have licensed any of the older utilities, email me for a half-price coupon.
  • When Microsoft releases the long-awaited Excel 2014 for Mac, the Peltier Tech Utility for Mac will be updated, and current licensees will be able to upgrade for no charge.
  • When Microsoft releases Excel 16 for Windows (probably a/k/a Excel 2016 or 2017), the Peltier Tech Chart Utility will be upgraded, and current licensees will as always be able to license the upgrade for half price.

Visit the Peltier Tech Chart Utilities page to take advantage of these special offers.

 

Peltier Tech Charts for Excel

Peltier Tech Chart Utilities for Mac Excel – Advanced Edition

It’s nearly finished!

In only a month, I’ve converted the Advanced Edition of the Peltier Tech Chart Utility so that it works on a Mac as well as in Windows. I’m pleased that every feature from the Windows version has made it into the Mac version, though some options are not available. This is due mostly to the inconsistencies between Office on the Mac and Office in Windows, such as the unavailability of modeless forms and the reduced selection of Paste Special options in MacOffice. A small number of differences relate to incompatibilities between the two platforms themselves, such as the strange special keys and special key behavior on the Mac (somehow the Mac’s Cmd-Ctrl-Opt-Shift keys provide less functionality than Windows’ Ctrl-Alt-Shift). And the lack of a customizable ribbon in Mac Office means the user experience is less fulfilling than in Windows.

 The Charts

The Advanced Edition for the Mac has all the custom charts as the Standard Version for the Mac and for Windows: Waterfall, Box Plot, Marimekko, Cluster-Stack, etc., and all the new charts introduced last month in the Advanced Edition for Windows: More Waterfalls, Diverging Stacked Bar, Paretos and Histograms, and more.

In addition to the charts shown here, any new custom charts developed by Peltier Tech will become part of the Advanced Edition. If you hear from the Peltier Tech twitter feed or the Peltier Tech Blog that a new chart has been added (or a bug has been fixed), email for a new download link so you can keep your license up-to-date.

Custom Chart Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Features

In addition to the helpful features of the Standard Edition, such as Edit Series Formulas, Copy and Paste Series Formats, Label Last Point, and Export Chart as Image File, the Advanced Edition adds such items as Export Range as Image File, Export Charts to PowerPoint and Word, Switch X and Y, and a simpler Error Bar Manager.

In addition to the features shown below, any new functionality developed by Peltier Tech will be added tothe Advanced Edition. If you hear via the Peltier Tech twitter feed or the Peltier Tech Blog that a new feature has been added (or a bug has been fixed), email for a new download link so you can keep your utility current.

Feature Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Details

The Advanced Edition for the Mac runs on the latest updates of Excel 2011 for the Mac, and whatever relevant versions of the Mac OS that supports Excel 2011. Mavericks, Mountain Lion, Cougar, even Water Buffalo, for all I know, which I find more confusing than Microsoft’s imaginative names, like Windows 7, 8, 3.14, etc. Joking aside, if you keep your Mac software up-to-date, you should have no problems.

Detail Comparison of All Versions and Editions of the Peltier Tech Chart Utility

The Deals

The Advanced Edition of the Peltier Tech Chart Utilities for the Macintosh will be available on Tuesday, July 8, 2104.

The Advanced Edition retails for $99, just slightly more than the $79 for the Standard Edition. But there are some great introductory deals:

  • If you purchase a license for the Advanced Edition between July 8 and July 15, you get it for the same $79 price as the Standard Edition.
  • If you already have a license for the Standard Edition, send me an email and I will send you a coupon code for a free upgrade to the Advanced Edition.
    • In the future, you can upgrade from the Standard to Advanced Edition for only the $20 difference in price.
  • If you have licensed one of the earlier Waterfall or Cluster-Stack Utilities for the Mac, email me and I’ll send a coupon for half off the $79 introductory price.
    • In the future, if you have licensed any of the older utilities, email me for a half-price coupon.
  • When Microsoft releases the long-awaited Excel 2014 for Mac, the Peltier Tech Utility for Mac will be updated, and current licensees will be able to upgrade for no charge.
  • When Microsoft releases Excel 16 for Windows (probably a/k/a Excel 2016 or 2017), the Peltier Tech Chart Utility will be upgraded, and current licensees will as always be able to license the upgrade for half price.

Mark your calendars so you don’t miss the special offers.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0