'Excel Dynamic Chart Title (Top n)
Is it possible to create a dynamic Excel Pivot Chart title that includes "(Top n)" where n = the value filter row limit selected by the user? I know the chart title can be set to the contents of a cell but am unsure how to go about determining the Top n limit selected by the user or how to add that in the cell formula. If this is possible, any assistance would be greatly appreciated. Thanks!
Solution 1:[1]
Max didn't work as I need the row count (n) after the user applies a value filter of Top n. However, your response steered me in the right direction and I was able to accomplish what I was looking for with ="(Top "&SUBTOTAL(103, A2:A140) &")" . Supposedly, SUBTOTAL with the first parameter set to 103 will perform a COUNTA function but ignore hidden rows. Reference: https://exceljet.net/excel-functions/excel-subtotal-function
Solution 2:[2]
So use max() on the range of cells and concatenate - I like the & (less typing):
=(Top &MAX(A21:A24)&")"
Just for those who don't know how top get the cell result into the title:
Select the chart title box (so it has blue corners
In the formula bar type "=" and select the cell with the contents and enter.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Solar Mike |