In this blog, I share how to use simple data, and create some meaningful charts, even with animation and custom graphics. Along the way, I’ll demonstrate techniques to rank and spark some ideas on PowerPoint<->Excel interaction.
Let’s start with the dataset. Following are the responses from a survey of 1,002 consumers from a couple of years back on Cyber 5 shopping behavior. Cyber 5 as you already may know is the collection of: Thanksgiving Day, Black Friday, Cyber Saturday, Cyber Sunday, Cyber Monday.
The question was: Which websites did you shop on over Cyber 5?
(Source: Internet Retailer and Toluna)
Ok, I’d like to take this simple data, and rank them. While in this case, it’s a very short dataset and we could do this manually, the technique I’m sharing will be useful for all sizes of datasets!
I’d like my raw dataset to be arranged as below, where the data is sorted by % column and then ranked (1 to n), and I want to find the max (we’ll use this information soon).
The formula to rank is below. Notice that the data starts at row 7 and ends in row 16, hence the range and is anchored.
Then copying the G7’s formula down to the end of the dataset (no matter how many rows you need to go), gives the rank for the entire dataset very nicely.
To calculate the max value of the dataset, we’ll use MAX() with nested IFs…then copy it down so that only the top-most will have the value shown. We’re saying if this retailer has the max %, then show it, otherwise, show zero.
I also want a bar chart and one that shows the max (or maybe top 3!) in a different graphics than the rest of the bars! Normally, you’ll just get the bars with same design, just different heights based on value. In this case, I want ONLY specific bar(s) to not only be taller but stand out by their looks based on value(s).
This is what I want…
I want to actually pick the TOP 3 and show them with this custom image, while the others will be of their own less-conspicuous design (gray bars). Using the same idea, I use the custom image and apply to 3 separate datapoints that are top 3 (we already ranked them, remember?).
And that’s why we found the max value, specifically, we created a whole column with zeros and >=1 max values! Because now, we’ll add that column as a new series “max” ON TOP of a normal bar chart. Then if I set the series overlap property to 100%, it’ll render only the max values (everything else is zero, remember?) and will be perfectly covering the monotonous bar only for the max value!
Where did that green arrow come from??? It’s nothing more than a good quality image…you could use anything here and that’s the beauty. Be sure to pick a good quality that can resize according to data value for the bar it’s on. I used the image as below (sans the gridlines that’s showing through in Excel):
Then by selecting this custom image for that datapoint, and with the previous steps, Excel will automatically scale it to appropriate height.
But I’m not finished! I want to animate it!
This is where PowerPoint and Excel need to work together. This is one of the least discovered/utilized power of presentation when it comes to charts and Infographics in my opinion. At least by most Excel users. Many of the fancy presentations and Infographics are often farmed out to the artists when a little deeper knowledge of Excel’s feature would accomplish most of the needs without having to spend extra resources ($$ and time).
By creating a new slide in PowerPoint, and then embedding the chart from Excel into it, I can now manipulate the chart as an element of both PowerPoint and Excel. Keeping the data integrity of Excel, I can apply PowerPoint’s animation feature to animate in many ways I like.
And below is my final output:
Hope this gives you some creative ideas and also experiment with and explore new tricks on your own.
This is not so much a tutorial (sorry, I don’t have much time to run tutorials outside of work, but there are tons of good information on the internet as well as books), but more of a show-case for my own curiosity. However, I will explain with time on hand enough for someone with beginner to intermediate knowledge in Spreadsheet applications to implement their own solutions based on this.
Be sure to look up additional tips and tricks by searching the tag cloud for “STEM” or “coding” or “Excel” (or anything you fancy) from my blog’s home page flyingslamon.net/blog
