Adding Dynamic Annotations for your Charts

Here’s a quick idea for anyone who needs to show annotations or comments on their charts. Tie your annotations to a check box so that users can interactively decide to show them or not.

The annotations themselves are simply chart data labels that are showing Category names instead of values. These labels are tied to an invisible data series in the chart. This means that as your chart data changes, the annotations move with the other chart elements. The check box is a Form control that drives a simple IF formula. IF the check box is TRUE then annotation series gets plotted on the chart, else the annotations series is ignored.

This neat trick involves no VBA. It uses a few simple modeling techniques that have been around for years. In fact, this trick uses the same steps described in this post I pushed out back in 2009. Follow that post to see how it’s done.

Also, feel free to download the sample workbook to see it in action.

Lock Slicers Before Sending Out your Dashboards

Last week, I hosted my Power BI Boot Camp here in Dallas. The folks that attended were seriously one of the best crowds I’ve met. Lots of great discussion about the new era of Power BI and data science. I even learned a few new tips from some of the attendees.

Here’s a quick tip I didn’t know.

A young fella named King taught us how to lock the positioning of slicers so users can’t accidently move them around. If you right-click your slicer and select Slicer Size and Properties, you’ll see the Position and Layout section. Expanding this section will reveal a checkbox called ‘Disable resizing and moving’.

Placing a check here essentially locks your slicer into place.

Users can select items in the slicer, but won’t be able to move or resize it.

How have I never seen this setting?

Excellent tip King – Thanks!

VBA to JavaScript Translator

It’s been a while since my last post. I’ve been enthralled with updating a few of my books for Office 2016 and learning about the inner workings of Web Apps for Office.

For those of you who don’t know, the future of office programmability will be creating applications that work on all devices (desktops, phones, tablets, etc.). Microsoft is currently developing a host of web APIs that will essentially make up a new kind of web-based Object model in which developers can leverage to create web driven applications.

These “apps for Office” are NOT a replacement for VBA. So don’t go looney on me. VBA still has a home on client-side development. The new Web Apps paradigm is a way for us to be able enter a realm of development we’ve been missing out on (web development).

This will require us to jump into scary waters. We’ll have to get comfortable with JavaScript, XML, HTML and some of the other web languages that we just don’t deal with now.

This journey into web development will be a slow one for many of us. It will require think-time, practice, and training. It will be an uncomfortable, but worthwhile transition.

Don’t worry…it’s not something we need to deal with all at once. I’ll eventually do a series of posts on developing these Web Apps for Office. For now, I want to share a pet project that I’ve been toying with.


I’ve create a VBA to JavaScript translator; partly to help me get my mind around JavaScript, and partly as an educational tool for anyone interested in moving from VBA to JavaScript.

You can find this tool here

This tool is designed to translate the most common constructs in VBA to JavaScript.

The idea is to enter some familiar VBA code to see how you would write the same syntax in JavaScript.


I figure I’m about a year too early with this kind of tool, but who knows. Some of you may be looking to dip into JavaScript now.

By the way…on the tool’s web page, you will find a link to a Github page where all the source code is available. I’m hoping a few intrepid souls are willing to help enhance the tool to make it more robust.

I’ll be back for regular blogging later this week.

10 Little Known Facts about Excel’s Humble Beginnings

Excel turns 30 today!

Yes, the program that has served as the foundation for my paycheck for over 15 years was saved to a Master Disk and rushed to market on this day in 1985. Given the impact Excel has had on the way the modern world works with data, it should be a global holiday. But I suspect this day will go unnoticed by almost all of Excel’s 1.2 billion users.


For my part, Read more

Contest to Win a Free Seat at my Next Power BI Boot Camp

I recently appeared on ExcelTV for a riveting one-hour interview.

Some of you saw it live.

Others of you were too busy doing things like having a love-life, engaging in real friendships, and doing something interesting.


For those of you that did catch the interview live, you’ll know that I announced a contest for a free seat at my Excel Power BI Boot Camp in October.

That’s right! If you win this contest, you get to sit in on 3-days of training without paying the registration fee. All you have to do is get yourself to Read more