The New Waterfall Chart in Excel 2016

This article about Microsoft’s new Waterfall Chart in Excel 2016 was written by Kasper Langmann, co-founder with Mikkel Sciegienny of Spreadsheeto, a relative newcomer to the Excel blogosphere. Kasper and Mikkel are very enthusiastic about Excel, they have written a number of tutorials on the Spreadsheeto Blog, and they offer a comprehensive seven-part free training course on Excel. Mikkel approached me to write about the new charts that Microsoft introduced in Excel 2016, and he agreed that we should write some posts together.

In the first half of this post, Spreadsheeto will specifically talk about Microsoft’s new Waterfall Chart. I will follow up with a discussion of the extensive and flexible waterfall charts in Peltier Tech’s software.

The Waterfall Chart in Excel 2016

In 2015, Microsoft released six new charts in an update for Excel 2016. This release was a direct response to user feedback and one of those new charts was the waterfall chart. The waterfall chart is a bar chart in which the bars are placed along the vertical axis at different levels according to whether they are an increase or decrease.

Then totals are shown as bars of height from zero as they are affected by the increases and decreases.

This is often useful for visualizing things like financial data where revenue can be shown as bars that shift upward vertically and expenses can be shown as bars that shift downward by contrast.

As we dive into the details of how to create a waterfall chart, note that we will work with an example scenario in Excel 2016 for Windows.

Getting Started with a Waterfall Chart – Get the Data Right

The first and foremost objective when setting out to create a waterfall chart is to make sure our data is in the correct format. Let’s take a look at a simple example of income statement data.

Waterfall Chart Data

It is important that our data is in this form where increases (income) and decreases (expenses) will be shown in the waterfall chart from left to right according to the data points from top to bottom in our table. Also, any totals (Total Revenue, Operating Income, and Net Income) will be shown in the chart as they appear in sequence in our data table.

Note that Total Revenue, Operating Income, and Net Income are subtotals along the way that will be shown as cumulative totals in our waterfall chart. This is set up to be a very intuitive progression as the chart presents revenue and expenses in the same logical fashion as our data table.

This is an instrumental point for setting up our data. You get out of the waterfall chart what you put into it and this means knowing how to set up our data before creating the chart.

Inserting the Waterfall Chart

Once our data table fits this layout, simply highlight the entire table (or one cell within the table), click on the ‘Waterfall and Stock Charts’ dropdown button…

Waterfall Chart on the Excel Ribbon

Then select ‘Waterfall’ in the menu.

Waterfall Chart on the Excel Ribbon

Alternatively, click on the ‘See All Charts’ button at the bottom right of the Insert > Charts group…

Find All Charts on the Ribbon

Then find ‘Waterfall’ in the list of available charts and click OK.

Waterfall Chart Excel's All Charts Dialog

Identifying the Totals

Initially, our new waterfall chart will be a bit unorganized and will need some specific tweaks from before it will begin to take shape and be a better visual representation of our data. Note in the legend that there are three classifications of the data bars: ‘Increase’, ‘Decrease’, and ‘Total’.

Waterfall Chart, Not Ready Yet
Notice how ‘Increase’ bars rise in cascading fashion and ‘Decrease’ bars fall in the same way along the vertical axis according to the cumulative effect each has on the running total. Right now our Total Revenue, Operating Income, and Net Income totals are each shown as an ‘Increase’. We need to change them to each to a ‘Total’.

To do this, let’s first click on the Total Revenue bar and then right click.

Waterfall Chart, Setting Subtotals

Select ‘Set as Total’ and two things will happen. First, the color of the bar will change to match the legend as a ‘Total’ and second, the bottom of the bar will be anchored at zero. That way, the bar will be a visualization of the cumulative total income after increases due to Sales and Service income. Notice now how the top of the ‘Total Revenue’ bar is the exact same height as both the ‘Sales’ and ‘Service’ bars put together.

Waterfall Chart, Setting Subtotals

Now we just need to repeat this step for ‘Operating Income’ and ‘Net Income’. This results in the chart we ultimately want. Increases (income) are show as rising bars in blue while decreases (expenses) are shown as falling orange bars.  Note the cascading visual effect of the rising and falling bars that give the waterfall chart its name.

Waterfall Chart, Setting Subtotals

Now we can make any formatting changes we want just as we would with any other chart in Excel. Then it’s ready to disseminate across your organization, to your stakeholders, or for a presentation.

Summary

The waterfall chart is a really nice addition to the family of charts offered in Excel 2016. As we have seen here, it provides a cascading visualization of data that includes increases and decreases while allowing us to see the cumulative effects on the running total in a very intuitive fashion. For the right kind of data, the waterfall chart can really bring things to life by providing an at-a-glance representation that anyone can appreciate.

The new charts in Excel 2016 can be used in a variety of scenarios. To learn more about all of them check out our guide here.

Before you begin your charting adventure, you should definitely read this comprehensive piece on what to do and what not to do when creating charts.

This article is written by Kasper Langmann from Spreadsheeto. If you like it, you should check out his free Excel training.

Peltier Tech Charts for Excel Waterfall Charts

Many years ago, Peltier Tech introduced a tutorial that showed you how to make your own waterfall charts in Excel, the slow hard way. You can even read the latest edition of the Excel Waterfall Charts (Bridge Charts), which is only a few years old.

Peltier Tech came out with a demo Waterfall Chart utility a decade ago, because even though it’s possible to build your own waterfall chart by hand, it’s much easier to select your data and click a button than to follow a long protocol with many easy-to-omit steps.

Peltier Tech has had a commercial Waterfall Chart utility on the market since 2008. Over the years it has been upgraded to run in newer versions of Excel, and to run in Excel for Mac. It has been continually enhanced, several varieties of waterfall charts have been introduced beside it, and thousands of users now depend on it. The newest edition, released to coincide with Excel 2016 for Windows and Mac, is called Peltier Tech Charts for Excel 3.0.

When Microsoft finally came out with their own Waterfall Chart, Peltier Tech said, “Nice going, that’s a pretty good start.”

Regular Waterfall

The data for the Peltier Tech Waterfall Chart is like that for Microsoft’s version, except the totals are left blank. When the program encounters a blank in the data column, it knows to calculate a total for that category. (If the totals in your input data are wrong, the chart Microsoft creates is also wrong.)

Data for Peltier Tech Waterfall Chart

Select the data, then click the Waterfall dropdown button on the Peltier Tech tab > Custom Charts group of the ribbon, and click Waterfall.

Peltier Tech Waterfall Chart Dropdown

You get a dialog that lets you select from a number of options.

Peltier Tech Waterfall Chart Dialog

When you click OK, the program inserts a worksheet with links to the data as well as columns of formulas that make sure the plot comes out right, plus a chart. Alternatively, you could have the program construct these formulas in the original worksheet. There is a checkbox and list box next to the output range, so that you can modify some of the options selected in the dialog.

Peltier Tech Waterfall Chart Output

 

The waterfall chart looks like on of the charts below. The waterfall treats the first value in the input data as a subtotal, for example, as a starting value that you are tracing through a set of intermediate operations to get a final value.

Peltier Tech Waterfall Chart - First Category is Subtotal

The second waterfall chart treats the first value in the input data as a change in value, in this case, as the first increase in revenue, coming from sales.

Peltier Tech Waterfall Chart - First Category is Change in Value

These charts are regular Excel charts, so you can reformat them, resize them, copy and paste them wherever you want. You need to be careful, however, because if you format away some of the hidden magic that makes them work as waterfall charts, you may break them and have to rebuild them.

The Microsoft waterfall charts are still not 100% user editable, depending on which build of Excel 2016 you use. On my main laptop I have the non-Office-365 version of Excel 2016, so it isn’t updated monthly with new features. I can format the colors of the Microsoft waterfall chart, and I can change the maximum and minimum of the vertical axis, but I am unable to modify the major tick spacing of the vertical axis. I also cannot modify the orientation of the horizontal axis labels. I’m sure that before long Microsoft will enable formatting of all of the elements of its waterfall charts, but for now, as I said, it’s still just a “good start”.

Rotated Waterfall

So what are some of Peltier Tech’s other waterfall charts? One of them is a Rotated Waterfall Chart. It uses the same data as the “Regular” Waterfall Chart, but it uses horizontal bars instead of vertical ones. Most people prefer the regular orientation, but some customers asked for a rotated version, so here it is. Here is the “First Item is a Starting Total” version…

Peltier Tech Rotated Waterfall Chart - First Category is Subtotal

And here is the “First Item is a Change in Value” version.

Peltier Tech Rotated Waterfall Chart - First Category is Change in Value

Stacked Waterfall

There is also a Stacked Waterfall chart. which breaks down each bar into smaller components. For example, you may want to see how several divisions contribute to your company’s performance.

The data is the same as for the “Regular” Waterfall Chart, except that there are two or more columns of values, one for each division.

Data for Peltier Tech Stacked Waterfall Chart

Each division appears in the chart with its own color, so you can track it across the chart.

Peltier Tech Stacked Waterfall Chart

Note that it’s impossible to stack negative and positive values in a stacked bar chart. The Stacked Waterfall handles this situation by replacing a multicolored stacked bar with a single bar that merely shows the net for that category. There is a Split Bar Waterfall Chart in Peltier Tech Charts for Excel (not shown here) that takes this into account, splitting each bar vertically, to stack positive values in the left half of the bar and negative values in the right.

Dual Waterfall

Another type of waterfall is the Dual Waterfall Chart, which allows you to compare two sets of data. The data set below shows the same data for two years.

Data for Peltier Tech Dual Waterfall Chart

The Dual Waterfall shows one set of data as filled bars, the other as transparent bars with a thick outline, so you can compare performance item by item across two sets of data.

Peltier Tech Dual Waterfall Chart

Paired Waterfall Chart

I wrote about the Paired Waterfall Chart recently in the Peltier Tech Blog. This is yet another way to track the cumulative effects of intermediate factors on a value or set of values.

Paired Waterfall Chart

I’m sure there are numerous additional Waterfall Charts that people could show you, but this extensive set is all that I’ve got.

Peltier Tech Charts for Excel 3.0

All of these Waterfall Charts, and several other handy chart types, are included in the Advanced Edition of Peltier Tech Charts for Excel 3.0. The “Regular” and Stacked Waterfall Charts are included in the Standard Edition.

 

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

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