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

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

Peltier Tech Utility 3.0 for Office 2016

New Releases Everywhere!

Microsoft Office 2016 for Mac and Microsoft Office 2016 for Windows have been released over the past couple of months.

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

Peltier Technical Services has released an upgraded utility, Peltier Tech Charts for Excel 3.0, to correspond with the upgrade to Microsoft Office.

What’s New in Peltier Tech Charts for Excel 3.0

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

Mac or Windows?

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

Excel and…

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

New Charts

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

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

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

Other chart types under consideration for the new utility are:

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

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

Did I forget something? Let me know.

New Features

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

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

Something missing? Tell me about it.

Editions

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

Peltier Tech Utility 3.0 Beta

The Peltier Tech Charts for Excel 3.0 Beta program has concluded.

You can read about the new utility and get your license at the following link:
Peltier Tech Charts for Excel 3.0

Installing Peltier Tech Charts for Excel 3.0

Excel 2016 for Mac

1a. Developer tab > Add-Ins

1b. Tools menu > Add-Ins

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

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

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

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

Excel 2011 for Mac

1. Tools menu > Add-Ins

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

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

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

Excel 2010, 2013, 2016 for Windows

1a. Developer tab > Add-Ins

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

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

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

Excel 2007 for Windows

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

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

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

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

Report a Bug or Suggest a Feature

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

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

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

Peltier Tech Charts for Excel 3.0

The Peltier Tech Charts for Excel 3.0 beta program has concluded.

You can read about it and get your license at the following link:
Peltier Tech Charts for Excel 3.0

Existing users of Peltier Tech software will get a healthy discount for upgrading. Send an email to Peltier Tech to request a coupon code for your discount.

 

Peltier Tech Charts for Excel

Peltier Tech Charts for Excel 3.0