Indzara

How to Create an Interactive Line Chart with Dynamic X-Axis?

Consider the chart of sales trends by month as shown below where the X-axis can either denote Quarterly or Monthly data.

Interactive Line Chart with Dynamic X-Axis sample screenshot
Interactive Line Chart with Dynamic X-Axis

This can be built in Excel with simple steps, let’s get started!

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 created a YouTube video explaining these charts in detail, check it out!

The following are the steps we’d follow in creating this chart:

  • Understand the data
  • Include “Labels” column to chart
  • Assign named ranges
  • Insert a line with markers chart
  • Format the chart

Step 01: Understand the data

Consider a sample sales data by month as shown:

Interactive Line Chart with Dynamic X-Axis  sample data
Sample data

Since our chart’s horizontal axis will be based on a user selection, include a drop-down to choose either Quarterly or Monthly in cell E3 as shown:

Interactive Line Chart with Dynamic X-Axis create dropdowns
The drop-down

To insert a drop-down, go to the Data tab, choose Data Validation, and insert a list of the values needed.

Step 02: Include “Labels” column to chart

Let us include an additional column (Labels) to have labels which will be our horizontal axis labels based on the drop-down selection.

Our formula to obtain these labels will be:

What does this formula do?

Note: Cell B5 onwards has the input month data

* Check if the input in cell E3 (the drop-down) is Quarterly or Monthly using an IF function.

* First, if it’s Quarterly we’ll require the label to have the format

“Q”-” the quarter number” – “year”

This is obtained using the ROUNDUP and MONTH functions:

“Q” & ROUNDUP(MONTH(B5)/3,0)& ” – “&YEAR(B5)

where the MONTH is used to return the month as a number from 1 to 12 (Jan through Dec), we’ll divide by 3 to get quarters, and ROUNDUP is used to round the decimals based on the second argument (in this case 0).

* If the selection is Monthly, then we’ll use a TEXT function, which returns numbers or dates in the required format based on the second argument, to get only the year:

TEXT(B5,”MMM-YY”)

With this, our new column, that will dynamically update based on selection will be:

Interactive Line Chart with Dynamic X-Axis dynamically updated column
Updated sample data

With this data and the control from the drop-down, we have to arrive at the X-axis which will be done using a formula. Based on this, the Y-axis values can also be arrived at using some simple Excel functions.

For the X-axis, use UNIQUE to get either the month or quarter as shown:

// cells D5 to D28 contain the labels we created.

For the Y-axis, which has to change according to the X-axis, we’ll use the SUMIF function to return sales data based on the Quarter or Month:

The syntax for SUMIF is:

Interactive Line Chart with Dynamic X-Axis use SUMIF function

Here, our range is the labels column, the criteria will be the X-axis we just created (F5) and the sum range will be the Sales column.

Note: To make our formula dynamic based on the input range, include a “#” after the X-axis cell range, hence the second argument in the formula is F5#.

Step 03: Assign named ranges

Our data for chart creation is ready, as shown:

Interactive Line Chart with Dynamic X-Axis data for chart creation

Please check our previous article on creating and formatting line charts where the steps to come here are explained in detail.

Since our data is dynamic, we have to assign names to these before building our chart.

Select the cell with our X-axis data ie. F5 and go to Formulas and Define Name and 

Interactive Line Chart with Dynamic X-Axis Define name

assign a name, say, AXISX:

Interactive Line Chart with Dynamic X-Axis assign name AXISX
Assign dynamic X axis values name

Similarly, let’s call the Y-axis as AXISY:

Interactive Line Chart with Dynamic X-Axis  assign name AXISY
Assign dynamic Y axis values name

Step 04: Insert a line with markers chart

With these named ranges, we’ll create our line chart. Click anywhere outside the data and insert a 2D line chart with markers.

 Interactive Line Chart with Dynamic X-Axis insert line chart with marker
Insert a line with markers chart

This creates an empty chart, right-click anywhere inside the chart and click on Select Data to add series and the horizontal axis as shown:

Interactive Line Chart with Dynamic X-Axis add series

Ensure to modify the series values to include the named ranges we’ve created for each axis.

Interactive Line Chart with Dynamic X-Axis  add axis labels

With this our dynamic, interactive line chart gets created:

Interactive Line Chart with Dynamic X-
Line chart

Change the drop-down input to see that the X-axis will automatically change based on the input chosen.

Step 05: Format the chart

Let’s do some formatting on our chart, say since this is a dynamic chart let us modify the X-axis name based on the drop-down cell.

Modify the X-axis header cell (cell F4) like this:

 Interactive Line Chart with Dynamic X-Axis modify X axis header cell

Now, include axis titles as shown:

Interactive Line Chart with Dynamic X-Axis add axis titles
Add axis title

For the Y-axis change the title to SALES and the X-axis can be dynamic by pointing it to refer to the column header from our data = F4.

We can include data labels, modify the gridlines, and much more with the formatting options available in Excel, check our article on creating line charts with multiple series for the same.

Our final chart is ready for analysis!

An Interactive Line Chart with Dynamic X-Axis
Interactive Line Chart with Dynamic X-Axis

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 *