Bar Chart Excel Template

$10

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 template is to reduce the time it takes from raw data to meaningful business insights.

Bar chart in Excel
Bar chart in Excel

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 Bar Chart 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

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
Paste Configure Get Insights
Paste Configure Get Insights

 

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.

Comparison with threshold or target or goal
Comparison with threshold or target or goal

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.

Grouping categories into Others
Grouping categories into Others

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.

Pareto analysis in bar chart
Pareto analysis in bar chart

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.

Difference from Mean analysis
Difference from Mean analysis

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.

Stacked Bar Chart in Excel
Stacked Bar Chart in Excel

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.

100% Stacked Bar chart in Excel
100% Stacked Bar chart in Excel

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 Minimum and Maximum
Highlighting Minimum and Maximum

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.

Custom Highlighting in bar chart
Custom Highlighting in bar chart

 

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)

Dynamic flexible slicers filters
Dynamic flexible slicers filters

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.

Custom Calculations - Count and Average
Custom Calculations – Count and Average

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.

Percentage calculations automated in bar chart
Percentage calculations automated in bar chart

The 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’.

Pareto analysis in bar chart
Pareto analysis in bar chart

You can choose either % of absolute value.

Pareto analysis in bar chart with cumulative values
Pareto analysis in bar chart with cumulative values

Customize to your needs

Any currency

Though the default is $, you can customize it to any currency by just typing in any symbol.

Change Currency
Change Currency

Here is an example of a different currency £.

Changed currency
Changed 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.

Change Number format
Change Number format

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.

Customize Dimension, Measure, Date and Date filters
Customize Dimension, Measure, Date and Date filters

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’.

Grouping into others - customize number of bars
Grouping into others – customize number of bars

Threshold or Target or Goal line

You can enter any threshold amount to draw the line.

Customizing threshold or target or goal
Customizing threshold or target or goal

 

Customizing Pareto %

Customizing Pareto
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 %.

Formatting - Value
Formatting – Value

 

Formatting - percentage
Formatting – percentage

 

 

Requirements

Microsoft 365 Subscription

Limits

  • 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

Video

FAQ

Visit our Support Portal for more

For Product specific FAQs, visit Product Support

Reviews

1 2 3 4 5