Column Chart Excel Template

$10

Description

If you have been given a raw dataset and you are looking to gain business insights, you need an effective tool that makes the data instantly useful. You need to 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 a presentable form.

The goal of this template is to reduce the time it takes from raw data to meaningful business insights.

 

Column Chart Excel Template
Column Chart Excel Template

About Column Charts

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 Column Chart will be good method to determine and present the information.

  • Ranking
    • What are the top 3 products with Sales?
    • What are the bottom 3 products with Sales?
    • Which product has the highest sales?
    • Which product has the lowest 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 Column 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
  • 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 columns to display (Grouping into Others)
    • Threshold line
    • Customize 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.

Comparing against Threshold or Target or Goal
Comparing against 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 automatically
Grouping categories automatically

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 columns 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 Chart
Pareto Analysis 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 Chart
Difference from Mean Chart

Stacked Column 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 or column chart which uses two dimensions and a measure.

Stacked Bar or Stacked Column Chart
Stacked Bar or Stacked Column Chart

In the above screenshot, we are measuring sales by Category. Within each Category, we can see the sales broken down by Sub-Category.

100% Stacked Column 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 Column Chart
100% Stacked Column Chart

In the above screenshot, we can see that 35% of sales in West region is driven by Office Supplies. 38% of the sales in South region is driven by Office Supplies. This takes out the fact that Sales in West and South 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.

Minimum and Maximum highlighted
Minimum and Maximum highlighted

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 - Customize
Custom Highlighting – Customize

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

 

Percentage calculation automated

By default the column chart shows the value as it is. However, we may want to represent the % of total as the value of each bar.

 

Percentage calculation Automated
Percentage calculation Automated

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

Cumulative % calculated
Cumulative % calculated

You can choose either % of absolute value.

Cumulative Values calculated
Cumulative Values calculated

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

 

Customize Currency
Customize 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 format
Change 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 columns to display (Grouping into others)

You can control the number of columns to display from 5 to 25. The last column will automatically group the rest and label it as ‘Others’.

Customizing max number of columns
Customizing max number of columns

Threshold line

You can enter any threshold amount to draw the line.

Customize threshold
Customize threshold

Labels on/off

Labels on the columns 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

 

Customizing Pareto %

Customize Pareto %
Customize Pareto %

Limits

  • Dataset size (max 25 columns in dataset, max 25,000 rows by default)
  • 25 columns (distinct values) in column chart
  • Max number of unique values in a dimension: 100

Video

FAQ

Visit our Support Portal

For Product specific FAQs, Product Support

Reviews

1 2 3 4 5