Indzara

Create a Dynamic Column Chart Limited by a Measure Value in Excel

In this topic, we’ll learn to create a column chart limited by a measure value as given by a user.  

Before we begin, we highly recommend reading our previous articles on dynamic chart creation, and a dynamic chart sorted by measure before you get into this post.

Consider the following chart with department names on X-Axis and the count of employees in Y-Axis.

Dynamic Column Chart Limited by a Measure

We can see that the department names are not clearly visible.

In some cases, we might have departments that have very less employees in them. Similarly, in terms of sales data, you might encounter a set of products with very less sales. Instead of having each of those columns, we can limit the number of columns to be displayed for a more visually concise and clear column chart.

That is exactly what we will create in this session! Read along.

Consider a sample data where we have a list of employees’ ID, their Names, and Departments.

Dynamic Column Chart Limited by a Measure sample data

What do we aim to accomplish?

This session aims to create a chart that limits the number of columns to be displayed based on an arbitrary number chosen by the user.  A chart that is dynamic and looks something like this:

Dynamic Column Chart Limited by a Measure limit the columns

As a preparatory step, always convert your data into an Excel Table (we’ll call this Data), this increases formula readability and scalability when the data expands.

Dynamic Column Chart Limited by a Measure convert data to tables

Let’s get into chart creation now!

First, let us extract the count of all distinct departments from our “Data” table, we use the COUNTA and UNIQUE functions for this in cell C4:

Dynamic Column Chart Limited by a Measure use COUNTA function

That is, if we create a default column chart for all departments, we’ll get 9 columns. Let us look at how to limit this.

Step 01:

Find out the short list of departments that satisfy the threshold.

Consider the input threshold in cell I4.

Dynamic Column Chart Limited by a Measure consider input threshold

We’ll use the LET function in cell C7 to get the shortlist. We have explained in detail, the use of the LET function in a previous article on dynamic column charts sorted by measure, please read the same.

Dynamic Column Chart Limited by a Measure use LET function

This function allows us to assign names to parts of your formula.

Let us understand the formula above,

  1. First, we need a distinct list of departments in our “Data” table using UNIQUE, and let us call them Departments
  2. Next, we need the count of the number of employees where we use the COUNTIF function to count the departments in our “Data” table and we check this against the distinct list of departments, which we named in step a. We’ll name this as Counts.
  3. After defining the two components we needed, the result we need is a FILTER of the departments only when the Counts are greater than or equal to our user-given limit measure in cell I4.
Dynamic Column Chart with dynamic number of categories Limited by a Measure

Step 02:

Now, we have to group all the columns (departments) which are not in our short list as “Others”. For this let us create a new column “New Department” in our raw data table “Data”.

a. First, we’ll use a MATCH function to look up the department from the table, and look up in the shortlist and we’ll look for an exact match.

This returns the position of the department in the corresponding short list, if there is no match this returns a #N/A error. 

Dynamic Column Chart Limited by a Measure use MATCH function

b. Now, we can wrap this formula within an ISERROR function which returns a Boolean whenever it encounters an error.

Dynamic Column Chart limited by a Measure use ISERROR function

c. We use an IF function to name the departments that have a TRUE from the previous step as “Others” since these are NOT present in our shortlist.

Dynamic Column Chart Limited by a Measure  use IF function

Step 03:

Now, we’ll focus on getting the list of departments for our column chart. We’ll tweak the LET function from our Short List but, instead of the Department, we’ll use the New Department from our Data table.

And use SORTBY to sort our Departments based on the Counts.

Step 04:

The count of Employees is a straightforward step, with COUNTIF as shown:

Now the data we need for the chart is dynamic based on the limit of measure value:

Dynamic Column Chart Limited by a Measure use COUNTIF function

Note: The Others column need not always be the lowest in the sort order, this is purely based on the limit measure given by the user:

Dynamic Column Chart Limited by a Measure with dynamic data

Let us build our column chart limited by measure,  

Since this has been extensively covered in our previous articles, I recommend you give it a reading to understand the detailed step-by-step process of creating a dynamic chart.

The overview of the steps are:

1. Select the data and create a clustered column chart

2. Define names for Department from D7 as Dept and make the range dynamic by using a #;

Dynamic Column Chart Limited by a Measure  Define series name

3. Similarly, for Employees from E7 as Emp and  

Dynamic Column Chart Limited by a Measure  Define series name

4. All that’s needed now is to point to our column chart to refer to these ranges:  right-click on the chart select Data, and click on the series to edit the range:

Dynamic Column Chart Limited by a Measure  edit series range

Similarly for the horizontal axis series,

Dynamic Column Chart Limited by a Measure edit axis label range

Now, we have a dynamic chart that limits columns based on the measure values as chosen by the user.  

Dynamic Column Chart Limited by a Measure

Ready to save time on your next presentation? Explore our Data Visualization Toolkit featuring multiple pre-designed charts, ready to use in Excel. Buy now, create charts instantly, and save time! Click here to visit the product page.

We have a dedicated YouTube video explaining the steps to create a column chart limited by measure:

If you have any feedback or suggestions, please post them in the comments below.

Leave a Reply

Your email address will not be published. Required fields are marked *