How to Create an Excel Interactive Chart with Dynamic Arrays

Discover how to dynamically hide or show data series in Excel charts based on cell values. Learn to sort series automatically for impactful presentations!

Quickly create an automatically sorted Excel bar chart that ALSO lets you hide and show categories on the chart based on a flag in the cell. It's super easy with Excel dynamic arrays - specifically with the new Excel FILTER and SORTBY functions.

📌 What You'll Learn:
▪️ Dynamic Series Visibility: Effortlessly hide/show chart series based on specific cell values, avoiding manual filtering or row hiding.
▪️ Automatic Sorting: See how to sort chart series automatically, enhancing your data visualization.
▪️ Using Excel’s Dynamic Array Functions: Leverage new functions in Excel for Office 365 to create responsive and interactive charts.

This is especially useful for Excel dashboards to include interactive charts depending on a selection.
The solution will be fully dynamic, i.e. we will not use manual filter or manual hiding. We will even take it another step further and automatically sort the Excel chart based on the visible sales data.

To summarize what you learn:
1. How to use Excel Dynamic Array referencing in charts
2. How to use Excel FILTER & SORTBY functions
3. How to Sort categories based on their values

Step 1: Data Preparation Table
Whenever the chart has a different setup than the source data, a data preparation table is needed in between. We will use the new Excel FILTER function for that. In order to get the chart properly sorted we will apply the new Excel SORTBY function. This function is used when the sorting criteria is not in the result. The tricky part is to ensure that the array for the sorting (by_array1) has the same size and order as the array for the visible sales data. We will solve this by nesting another FILTER function in the formula. To get the respective sales data we'll use a simple VLOOKUP.

Step 2: Excel Chart
For the visualization we'll use a column chart that's connected to the data preparation table. However, we want the Excel chart area to update automatically based on the number of data points in the data preparation table. In other words, we want the chart to be dynamic and to include any new selections from the data preparation table and exclude any blank areas.
Excel Chart references can either take direct references or they can take names. Therefore, to keep the chart dynamic we'll use names in the Name Manager and we'll use the "#" for the reference to spill. Then we'll update the Series Values and the Axis Labels of the chart with the names we created.

Step 3: Dynamic Dashboard
This technique can easily be used to create dynamic dashboards. Based on a dropdown selection the chart is automatically updated. I used the Excel IF and SUMPRODUCT functions to help with that. In order to make sure that new data is added to the chart I converted the data into an official Excel table.

00:00 Intro
01:26 Prepare Data Table
06:30 Create Excel Chart
09:38 Setup Dynamic Excel Dashboard

🚩Let’s connect on social:

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

[chh]

My Thursday morning routine … a cup of coffee w/ Leila … she is always interesting … she never disappoints.

[mozgusify]

Leila, you're amazing. Everyone who is afraid of Excel should watch your channel. Excel with you is fun!

[duncanwil]

I didn't know this, obviously and had looked at other Excel trainers and practitioners for help. This is the only one to demonstrated exactly this ... which is what I needed! Excellent!

[catbou]

Fantastic. You remain my favorite educator on the internet.

[sasavienne]

Just *amazing as usual*
Thanks Leila. You make everything easy to understand and easy to keep in mind. 🌟 🌟 🌟 🌟 🌟

[arielspalter]

You're a life saver. This video has pointed straight to what I needed.

[nixoniraj]

This is a super informative YouTube channel for excel users and finance professionals. I recommend others to subscribe the channel. I have learned a lot from her videos. Thanks Ms. Leila :) Much appreciation.

[brighnquisitive]

Awesone as always... How can one person be so consistently amazing?

[ahmadgarbaauwal]

I really appreciate your efforts, thank you so much and congratulations!

[Antagraber]

I felt confident enough with Excel until I saw one of your videos, and I started to realize about the high level of this YT channel.
I really appreciate not just this video (which is going to help me a lot), but also I am going to subscribe and thoroughly study all of the others, as well as the offering you are promoting.
I found a huge source of knowledge and information on Excel that will help me a lot to know better this amazing product.
Thank you so much, Leila !! It has been a really enriching experience.

[Michael_Alaska]

Thanks for the great walk-through. Playing around I created four 4 prep ranges so I could use one each for all, red, green, and yellow; then using an earlier lesson on overlapping chart data series made the chart bars change color to match the group. Good fun.

[alexkim]

Thank you so much for making it easy to understand, Leila. I used to FEAR charting in Excel (that's why I went to Power BI). Your video has made everything really posh and easy to digest.

[taimoor]

I can't believe what I learned today. Leila you are just amazing. Thank you for this dynamic lesson.

[arumoydas]

Just Stupendous working with excel, looks so easy, thanks to the nicely explained tutorial by Ms Leila.

[alexeyagaev]

Leila, you’re terrific! Thank you very much for your content! It saves a lot of time while using excel and helps to create wonderful excel dashboards!

[aviewfromthesaddle]

Genuinely the first time I've learnt something in Excel for a very long time, thanks. I've recently upgraded to 365 and will definitely make use of this.

[ryanmiguelsantiago]

Thank you, Leila! Very helpful and useful as always

[bahatidramou]

You are amazing @Leila!!
I have been following your channel for awhile now. THANK YOU,

[DougHExcel]

Thanks for the video, can't wait for dynamic arrays to become more widely available!

[johnborg]

Thanks Leila. Filter part was Great!!!