Your Excel charts should always show a variance. Maybe a variance from plan or a variance from last year. A new Excel (and PowerPoint) tool from Zebra BI makes this very easy.
Links from the video:
IBCS Standards: IBCS Standards 1.2 • IBCS - International Business Communication Standards
About Zebra BI: Excel Add-in - Zebra BI
The free templates from Zebra BI: Report Template Gallery (Excel & Power BI) | Zebra BI
Table of Contents
(0:00) Life-changing
(0:29) Excel Chart
(0:58) ICB Standards
(1:29) Some ICBS rules
(2:18) Zebra BI for Office
(3:14) Creating chart in Zebra BI
(3:52) Interpreting Chart
(5:14) Second chart
(6:06) Formatting
(7:01) Free templates
(7:15) Wrap-up
Links from the video:
IBCS Standards: IBCS Standards 1.2 • IBCS - International Business Communication Standards
About Zebra BI: Excel Add-in - Zebra BI
The free templates from Zebra BI: Report Template Gallery (Excel & Power BI) | Zebra BI
Table of Contents
(0:00) Life-changing
(0:29) Excel Chart
(0:58) ICB Standards
(1:29) Some ICBS rules
(2:18) Zebra BI for Office
(3:14) Creating chart in Zebra BI
(3:52) Interpreting Chart
(5:14) Second chart
(6:06) Formatting
(7:01) Free templates
(7:15) Wrap-up
Transcript of the video:
For a lot of us, what I want to show you in this video can be life-changing.
Now, I've had people come up to me and say, "Hey, I had this one job.
I went to your bootcamp with Mike Alexander, and I got a better job because of it".
I'm really, really impressed with what I'm going to show you today.
Episode 2525 - Easy Variance Charts with Zebra BI for Office.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
We've been stuck with the same charts in Excel forever. Here's a 12-month sales report insert.
What are we going to do? Probably a column chart like that.
And maybe I'll do a little bit of customizing here.
Right Click, Format data series. Change the gap width.
Yeah, I don't know, I mean, that's kind of about it, right?
Is this good? How do we know?
I want to talk to you about something called the International Business Communications Standards Board. Go check out their website.
I'll put the link down in the YouTube description below.
When you get to the About, here's the important thing for me.
This professor worked with Gene Zelazny, who was the guru of visualizations at McKinsey & Company.
I had a chance, McKinsey came into a company that I worked for one time, and great visualization tools. I've written to Gene before.
So he instantly has cred because of that.
And among the rules for charts, they say always display variances.
These are sales numbers, but where were they compared to plan or where were they compared to last year?
And then I absolutely love one. This used to be my go-to signature move.
Never create a chart without putting some sort of a comment on the chart.
Not a title - for me, it was a 32nd elevator pitch.
That way, when that chart made it to my manager's manager, the vice president of sales.
He at least had something that he could take away from the chart.
And then later on when he saw the president of the company, he was like, "Oh yeah, we had blah, blah, blah happen".
All right, so always add comments to explain. And labels always horizontal.
I haven't heard of this organization before.
There's more rules than this, but they resonate with me. It makes a lot of sense.
It's very timely because just last week on October 18th, a brand new add-in was released called Zebra BI Charts for Office and Zebra BI Tables for Office.
It's amazing.
This thing came out right out of the box, works in Windows, works in Mac, works in iOS, works in Android, works in Excel Online, even works in PowerPoint, right? They've got all the products covered.
And Zebra BI are big proponents of IBCS. So they're IBCS compliant.
And if you're a Power BI developer, there's a good chance you've already heard of Zebra BI, because a lot of people are using their custom visualizations in Power BI.
So it's very easy to get this if you have Microsoft 365.
Insert, get add-ins, search for Zebra, and you're going to see both of these and install them both.
There's a free version. You can do a lot with the free version.
They're hoping they eventually will switch over to the Pro version.
But everything I'm going to show you today is in the Free version.
It helps to get kind of used to their lingo there. CY is current year, PY, prior year, PL, plan.
If you happen to use those labels, all the better because then you won't have to define anything.
So here's my 12 months in the data that I had from the current year in that first chart.
I went back and got another 12 months, all right. And I added a comment column.
Watch how easy this is to create four amazing charts that run circles around anything that we could do in Excel.
Open my add-ins, go to Zebra BI Charts for Office. Wow, there we are.
Okay, this is a great chart. Last year, 3.9 million.
This year, 5.2 million. How do we get from 3.9 to 5.2?
We were up every month, month after month after month, except for this month right here.
We weren't up. What the heck is going on?
Look at that number one right there. That number one is this comment.
That comment is the comment that I typed in there.
I actually had to go take a look at this and figure out, well, what the heck? Why were we down?
And it turns out that we weren't really down.
It's that something weird happened the year before.
The best we can figure out is the vendor accidentally gave us an $80,000 order that should have gone to our competitor.
They've never mentioned it. We never mentioned it.
What a great month, but it's not going to happen again.
All right, so in October, we're up 103%, up 62%. I mean, this actually looks really good.
These amazing numbers here, how far up we were.
And then we just kind of settled in to this, about 30%. And then that one month, that looked really bad.
And then since then, we're not up as much as we were, but we're still up.
This tells me so much more than the first chart. I mean, it's a great visualization.
And there's four visualizations here.
See when I hover that little arrow, but I'm a huge fan of this.
So right here, the dark column is where we are this year. The green is the increase from the prior year.
So I can see the same information that we had in that first chart.
But I can see that in 11 of the 12 months we were up from last year, just this one month we're down.
And again, that same explanation that we think it was an $80,000 order that wasn't meant for us.
But they sent it, we filled it, whatever. I love this chart and I love the first chart.
We get those four charts automatically.
Now, there are a lot of things in this tool that I still have not yet discovered.
There's options up here in the Zebra BI settings - these feel a lot like Power BI.
I'm going to switch over to a completely different workbook. There's Options here.
So if I right click on Greece.
There's the chance that if something in the income statement needs to be inverted, I can invert it. Change the positives to a negatives.
I can change the formatting. I can add a formula.
I wish that I could do any of this in regular Excel charts.
I watched their demo last week and their customers fall into two classes.
The people who have been using them in Power BI would be able to run circles around me because they're very familiar with this product.
But the Excel people, I think we're going to be blown away by these new charts that we can create and how much we can customize right in the chart.
And it's based on really good standards from the International Business Communications Standards.
Check it out right now.
Go to Get Add-ins, click on Zebra. Super easy to get it.
Download their 20 free templates.
I learned so much just from studying how they did this.
You might remember that video of the very small slicer - that was Tine. Tine is a developer at Zebra BI.
Just build your data either in a pivot table or a table, or just a regular Excel range here.
Something down the side. Use these little abbreviations.
CY for current year, or AC for actuals, PY for prior year, PL for plan.
If you use some other fields, you can just drag them around here, but they'll automatically figure those out. It looks amazing to me.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Now, I've had people come up to me and say, "Hey, I had this one job.
I went to your bootcamp with Mike Alexander, and I got a better job because of it".
I'm really, really impressed with what I'm going to show you today.
Episode 2525 - Easy Variance Charts with Zebra BI for Office.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
We've been stuck with the same charts in Excel forever. Here's a 12-month sales report insert.
What are we going to do? Probably a column chart like that.
And maybe I'll do a little bit of customizing here.
Right Click, Format data series. Change the gap width.
Yeah, I don't know, I mean, that's kind of about it, right?
Is this good? How do we know?
I want to talk to you about something called the International Business Communications Standards Board. Go check out their website.
I'll put the link down in the YouTube description below.
When you get to the About, here's the important thing for me.
This professor worked with Gene Zelazny, who was the guru of visualizations at McKinsey & Company.
I had a chance, McKinsey came into a company that I worked for one time, and great visualization tools. I've written to Gene before.
So he instantly has cred because of that.
And among the rules for charts, they say always display variances.
These are sales numbers, but where were they compared to plan or where were they compared to last year?
And then I absolutely love one. This used to be my go-to signature move.
Never create a chart without putting some sort of a comment on the chart.
Not a title - for me, it was a 32nd elevator pitch.
That way, when that chart made it to my manager's manager, the vice president of sales.
He at least had something that he could take away from the chart.
And then later on when he saw the president of the company, he was like, "Oh yeah, we had blah, blah, blah happen".
All right, so always add comments to explain. And labels always horizontal.
I haven't heard of this organization before.
There's more rules than this, but they resonate with me. It makes a lot of sense.
It's very timely because just last week on October 18th, a brand new add-in was released called Zebra BI Charts for Office and Zebra BI Tables for Office.
It's amazing.
This thing came out right out of the box, works in Windows, works in Mac, works in iOS, works in Android, works in Excel Online, even works in PowerPoint, right? They've got all the products covered.
And Zebra BI are big proponents of IBCS. So they're IBCS compliant.
And if you're a Power BI developer, there's a good chance you've already heard of Zebra BI, because a lot of people are using their custom visualizations in Power BI.
So it's very easy to get this if you have Microsoft 365.
Insert, get add-ins, search for Zebra, and you're going to see both of these and install them both.
There's a free version. You can do a lot with the free version.
They're hoping they eventually will switch over to the Pro version.
But everything I'm going to show you today is in the Free version.
It helps to get kind of used to their lingo there. CY is current year, PY, prior year, PL, plan.
If you happen to use those labels, all the better because then you won't have to define anything.
So here's my 12 months in the data that I had from the current year in that first chart.
I went back and got another 12 months, all right. And I added a comment column.
Watch how easy this is to create four amazing charts that run circles around anything that we could do in Excel.
Open my add-ins, go to Zebra BI Charts for Office. Wow, there we are.
Okay, this is a great chart. Last year, 3.9 million.
This year, 5.2 million. How do we get from 3.9 to 5.2?
We were up every month, month after month after month, except for this month right here.
We weren't up. What the heck is going on?
Look at that number one right there. That number one is this comment.
That comment is the comment that I typed in there.
I actually had to go take a look at this and figure out, well, what the heck? Why were we down?
And it turns out that we weren't really down.
It's that something weird happened the year before.
The best we can figure out is the vendor accidentally gave us an $80,000 order that should have gone to our competitor.
They've never mentioned it. We never mentioned it.
What a great month, but it's not going to happen again.
All right, so in October, we're up 103%, up 62%. I mean, this actually looks really good.
These amazing numbers here, how far up we were.
And then we just kind of settled in to this, about 30%. And then that one month, that looked really bad.
And then since then, we're not up as much as we were, but we're still up.
This tells me so much more than the first chart. I mean, it's a great visualization.
And there's four visualizations here.
See when I hover that little arrow, but I'm a huge fan of this.
So right here, the dark column is where we are this year. The green is the increase from the prior year.
So I can see the same information that we had in that first chart.
But I can see that in 11 of the 12 months we were up from last year, just this one month we're down.
And again, that same explanation that we think it was an $80,000 order that wasn't meant for us.
But they sent it, we filled it, whatever. I love this chart and I love the first chart.
We get those four charts automatically.
Now, there are a lot of things in this tool that I still have not yet discovered.
There's options up here in the Zebra BI settings - these feel a lot like Power BI.
I'm going to switch over to a completely different workbook. There's Options here.
So if I right click on Greece.
There's the chance that if something in the income statement needs to be inverted, I can invert it. Change the positives to a negatives.
I can change the formatting. I can add a formula.
I wish that I could do any of this in regular Excel charts.
I watched their demo last week and their customers fall into two classes.
The people who have been using them in Power BI would be able to run circles around me because they're very familiar with this product.
But the Excel people, I think we're going to be blown away by these new charts that we can create and how much we can customize right in the chart.
And it's based on really good standards from the International Business Communications Standards.
Check it out right now.
Go to Get Add-ins, click on Zebra. Super easy to get it.
Download their 20 free templates.
I learned so much just from studying how they did this.
You might remember that video of the very small slicer - that was Tine. Tine is a developer at Zebra BI.
Just build your data either in a pivot table or a table, or just a regular Excel range here.
Something down the side. Use these little abbreviations.
CY for current year, or AC for actuals, PY for prior year, PL for plan.
If you use some other fields, you can just drag them around here, but they'll automatically figure those out. It looks amazing to me.
All right, well, hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
