Visual analytics is amazing – it helps “data enthusiasts” save time in answering questions using Data. Let’s see one such example. For the purpose of the blog post, I am going to show how to do it in Excel 2010:
Problem:
Here’s the Business Question: What was sales of Tea in North Region in 2012 Q1
Here’s the data:
| SALES DATA(2012 Q1) | East | West | Central | North | South |
| Coffee | $ 7,348.00 | $ 7,238.00 | $ 1,543.00 | $ 9,837.00 | $ 1,823.00 |
| Tea | $ 9,572.00 | $ 8,235.00 | $ 3,057.00 | $ 8,934.00 | $ 13,814.00 |
| Herbal Tea | $ 5,782.00 | $ 8,941.00 | $ 9,235.00 | $ 392.00 | $ 1,268.00 |
| Espresso | $ 9,012.00 | $ 2,590.00 | $ 4,289.00 | $ 7,848.00 | $ 340.00 |
So it’s easy to give out answer using the data: $8934
But let me CHANGE the business question:
WHICH Products in WHAT regions are doing the best?
Now this questions is not as easy as the previous one? WHY? because you’ll have to manually go through each number in a linear fashion to answer the question. Now imagine a bigger data-set. It’ll take even more time.
Solution
What can Excel Power users and Data Enthusiasts do to answer the new business question in an efficient way? Well, let’s see what conditional formatting can do it:
Now with the Data Bars, it’s easier to just glance at the report and see best performing products and regions. For instance, it’s very easy to spot that Tea is performing best in South among all products and region.
So how do you create data bars?
1. Select the data
2. Home > Conditional Formatting > Data Bars
3.Done! you’ll see this:
4. You can play with other options here to see what suits the best for your needs. But I just wanted to point out that there is a way for you to highlight the data in a way that helps you save time in answering business questions using data
Conclusion:
Visual analytics is a great way to quickly analyze data. In most cases, Human brain is much faster at interpreting the visual results as oppose to text/numbers – so why not use it to your advantage. And tools like Excel have inbuilt functionality to help you do that!
Related articles
- Remove Duplicates in Excel Tables using Data Explorer Add-in: (parasdoshi.com)
