Now that the formulas and values are in place, we can set up our conditional formats. Cell P4 returns the row number for the second-ranked result, and so on.įinally, you can set up the formulas that return the labels and values:Ĭopy this pair of formulas down the column as needed. Cell P3 returns the row number for the highest-ranked result. Copy cell O5 down the column as needed to generate these values.įormulas in column P automatically sort the results. The formulas in column N rank the results found in column M.Ĭolumn O returns 1,1, 2, 2, and so on. (Here, we’re adding the row number multiplied by.
To make sure that no two values are duplicates, we add a unique-but-inconsequential value to each value we rank. However, the RANK function returns duplicate results whenever it ranks duplicate values. We’re going to use the RANK function to rank these results from 1 to 5. Unless I say otherwise, copy the last formula in a column down its column as needed.Ĭolumn L just contains the numbers 1 through 5.Ĭolumn M returns data for the current month from the data worksheet…with one addition. I discuss the key values and formulas below. To create the chart, let’s start with the control area. That is, I named the range of products Product, and the range of current values Current. Here, of course, the categories are product lines: It’s just a display that shows 13 months for five different categories.
This method calculates quickly and produces great results. The other way is to use Conditional Formatting. Therefore, it’s always a good idea to limit your use of Camera objects to only when you really need them. The problem is that using more than a few Camera objects slows calculation time considerably. If it weren’t for one problem, I probably would have used this approach. I can think of two ways to set up a legend like this. But in Excel, we need dynamic chart legends! What’s A Dynamic Chart Legend?ĭynamic chart legends change as your data changes.įor example, the legend in each of the two charts below automatically adjusts so that the information about the top line also is at the top of the legend bar, and the information about the bottom line is at the bottom of the bar, and so on.Īlso, this legend includes the current value for each data series. You can add any other data you want, of course.īecause this chart shows random data, the legends automatically adjust each time I recalculate my workbook. When I saw a chart designed like the ones below in the Wall Street Journal, I knew I had to create one just like it.