Description
If you have been given a raw dataset and asked to gain business insights, you need an effective tool that makes the data instantly useful. You should be able to ask business questions/hypotheses and get answers immediately. Also, you would need to be able to present those insights with supporting data in an effective format.
The goal of this Data Visualization Bar Chart Excel Template is to reduce the time it takes from raw data to meaningful business insights.
About Bar Charts
Bar Charts, like Column Charts, are effective in analyzing categorical variables in two common ways
1) Ranking: identifying the order or position in which each category contributes compared to the other categories.
2) Part-to-whole: what % of the whole is contributed by each category.
To further elaborate, let’s use a simple example where we are measuring sales by product. Here Sales is the quantitative measure and Product is the categorical variable (or dimension). Here are some questions to which a Bar Chart will be good method to determine and present the information.
- Ranking
- What are the top 3 and bottom 3 products with Sales?
- Which product has the highest sales? lowest sales?
- Which region contributes the most in sales?
- Part-to-Whole
- What % of the sales comes from each product?
- How many and which products contribute to 80% of the Sales?
Features of Data Visualization Bar Chart Excel Template
- Ready-to-use for instant insights
- Can handle any dataset
- 3 simple steps to insights
- Effective analysis techniques
- Pareto, Comparison with thresholds/targets, Min/Max, Stacked Bar, 100% Stacked Bar
- Grouping long tail values automatically
- Sorting by Dimension or Measure – ascending or descending
- Highlighting values with different colors automatically
- Dynamic Slicers
- Comments can be added
- Automated calculations
- Automated Count of rows calculation
- Automated % calculations
- Customized Average calculations
- Cumulative % and values are calculated for Pareto analysis automatically
- Customize to your needs
- Any currency
- Formatting options
- Customize dimension, measure and date fields
- Number of bars to display (Grouping into Others)
- Threshold line
- Pareto %
- Labels on/off
Highlights of Data Visualization Bar Chart Excel Template
Ready to use
The template makes it easy to accommodate any dataset. You can bring any dataset of yours and paste that in the template.
Three simple steps: Paste your data, Configure & Get Insights
- Paste your data: Please paste as values
- Configure: Choose your fields for analysis
- Get Insights: View, Print or Export Column Chart
Effective Analysis techniques
Though Charts in Excel, in default form, are very useful, we end up spending time modifying it to meet our needs often. The template takes those steps out of the way. The template employs effective analysis and presentation techniques that are automatic.
Highlighting categories that exceeded a threshold
A very common analysis technique is to compare the actual results against a target / goal / threshold. In this template, you can type in any threshold and the categories that exceed the threshold are immediately highlighted along with a threshold line.
Grouping category values automatically to improve presentation
Very often I have found that when the category has more than 10 values the chart looks less presentable with too many categories which don’t contribute much to the overall measure. The template allows you to automatically group categories into an ‘Others’ bucket.
In the screenshot above, though there are 17 values in ‘Sub-Category’, the template groups the last 8 into the ‘Others’ category. This charge is a lot more presentable now without 17 bars crammed into the chart area.
Pareto Analysis automated
A common phenomenon noticed is that a large portion (eg. 80%) of a measure is driven by a small portion (eg. 20%) of categories. In order to tell such a story, we employ Pareto analysis. In our template, you can choose Pareto analysis in one click.
In the screenshot above, 9 product sub-categories make up 80% of the Sales.
Difference from Mean
If we need to highlight the categories that are above and those that are below the mean, you can do that instantly. The following chart is created with 1 click.
Stacked Bar Chart
So far, all the above analysis techniques used one measure and one dimension. To get deeper understanding of our data, we can use the Stacked bar chart which uses two dimensions and a measure.
In the above screenshot, we are measuring sales by Region. Within each Region, we can see the sales broken down by product Category.
100% Stacked Bar chart
While the Stacked bar chart allows viewing the actual measures (eg. Sales), in some cases we need to view the normalized values. That is where 100% stacked bar chart comes in.
In the above screenshot, we can see that 40% of sales in West region is driven by Chairs. 52% of the sales in Central region is driven by Chairs. This takes out the fact that Sales in West and Central are not the same. However, what we are interested is the % distribution within each region.
Highlighting minimum and maximum categories
Often, we need to tell a story about the most and the least among the categories. This analysis method immediately highlights the minimum and maximum categories in different colors.
Highlighting a specific category for effective story presentation
If our analysis is focused on a specific category and we need to highlight it to show the position/rank of that category among all categories, we can use the ‘Custom Highlight’ option in the template.
Dynamic Slicers (or Filters)
When doing data analysis, having the ability to filter to segments of the data is essential. The template allows customizable filters (Excel terminology Slicers)
Automated calculations
An area where time is usually spent is to add aggregation calculations on top of the raw data. The template automates some of the commonly needed aggregation calculations.
Automated Count of rows calculation and customized Average calculation
Count of rows is one of the available calculations. For example, if your data set is a set of orders, then Count calculation will provide # of orders.
There is also an Average calculation available. This is an aggregation that calculates (sum of a measure/sum of another measure). For example, if you have Sales in one column, and Quantity in another, you can calculate the average unit price by dividing Sales/Quantity. You can customize this with any two measures.
Percentage calculation automated
By default the bar chart shows the value as it is. However, we may want to represent the % of total as the value of each bar.
The Data Visualization Bar Chart Excel Template allows the option to display value or Percentage. Again, a single-click change.
Cumulative % and values are calculated for Pareto analysis automatically
When doing Pareto analysis, we need to calculate Cumulative or running total. The template will automatically do that when you choose ‘Pareto’.
Customize to your needs
Any currency
Though the default is $, you can customize it to any currency by just typing in any symbol.
Here is an example of a different currency £.
Formatting options for data labels
Labels can be formatted from a variety of choices. You can choose from Currency, Whole number and Number with decimals.
Customize dimension, measure, date and date filters
As we were discussing the different analysis techniques, it involved a measure and one or two dimensions. You can change the measure or dimensions anytime and they are one-click away. You can also choose from different date fields. If you need to filter on dates to narrow down your dataset, just type in the start and end dates.
Number of bars to display (Grouping into others)
You can control the number of bars to display from 5 to 25. The last bar will automatically group the rest and label it as ‘Others’.
Threshold or Target or Goal line
You can enter any threshold amount to draw the line.
Customizing Pareto %
Labels on/off
Labels on the bars in the chart can be turned on or off when needed. Labels can be either the value or the %.
Requirements
Microsoft 365 Subscription
Limits of Data Visualization Bar Chart Excel Template
- Dataset size (max 25 columns/fields in dataset, max 25,000 rows by default)
- 25 Bars (distinct values) in Bar chart
- Max number of unique values in a dimension: 100
Reviews
There are no reviews yet