Join 400,000+ professionals in our courses here https://link.xelplus.com/yt-d-all-courses

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

⬇️ DOWNLOAD the workbook here: https://pages.xelplus.com/interactive-chart-file

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

LINKS to Excel Dynamic Arrays Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyPFY31LldHWcJdLzGUPTSP
Excel Dashboards & Reports Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKyz_4TwSfxzN_gsRtg3ODzyx

️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join

Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/

Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

RESOURCES I recommend: https://www.xelplus.com/resources/

Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
LinkedIn: https://www.linkedin.com/company/xelplus

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