Category:

Course Description:

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. Course Length: 12:32 minutes Source: Leila Gharani

Thousands of courses

DisclaimerMany of the curated courses have been manually added into our Learning Management System, then enhanced with structure, descriptive, activities and consolidation,  referencing who the content creators are (where required). If you like the curated content from any creator and like their materials, please feel free to connect with them directly to explore their full libraries of paid programs. 

We will continue to add more curated quality content as well as increasing partnerships with creators to provide you with an all-in-one skills development platform. 

Book a demo now!

Tell us a bit about you. After review, you will receive an invite calendar where you can select your demo time.