Help with interactive charting (percentage and general format of data or numbers)

[dobetter]

Hi Gurus,

I am new here.

I recently created an interactive chart for my class.

In the chart (found in dashboard tab sheet), there is a controller for when a user selects different KPIs.

These KPIs should be in different format, one KPI in numbers, one KPI in percentage format.

Here are the KPIs and there "should be" formats:
Average CTR = percent
New Opt-ins per month = number
Opt-in List size = number

My problem here is that when I include data labels on my clustered column chart,
if I set the data label to be percentage say for Average CTR there is no issue, the issue would be
when I change the KPI to say New Opt-ins per month, instead of a whole number format, it also formatted as percentage.

How can I make it that the percentage data labels should only be the one displaying "percentage" format, and the other 2 KPI should
maintain the whole number format?

I will attach my file.


Thank you for helping me.

[Andy Pope]

The data labels will reflect the number format of the range. How you have it set up currently there is only 1 number format in use.

Start by adding 2 rows of formula to the calc sheet so you have a set of data with a row for each KPI.
Format each row of data with correct number format.

Use the name manager to Define that data
ALL_DATASETS: =calc!$B$14:$M$16

Define another range to reference the currently selected KPI

USE_DATASET: =INDEX(ALL_DATASETS,MATCH(calc!$A$13,REF!$H$2:$H$4,0),0)

Now on the chart checked the number format of the data labels so it is linked to the cells.
Edit the series formula

=SERIES(,calc!$B$13:$M$13,'1371411.xlsx'!USE_DATASET,1)