asked on

Dynamic Bar chart in Excel

Can someone please show me how to create dynamic chart for the data attached here? In the attached example, mint does not have any sales. How can i build a dynamic chart that hides mint from the chart and only show mint sales on the chart when mint is not blank.
Book12.xlsx

Patrick Matthews

Recast your source data to look like this:

Category      Product      Result
Percent Sales      cookie      42.64%
Percent Sales      sweets      84.75%
Percent Sales      dough      22.64%
Percent Sales      oil      20.00%
Percent Sales      biscuit      52.16%
Percent Rewards      cookie      48.10%
Percent Rewards      sweets      70.29%
Percent Rewards      dough      38.10%
Percent Rewards      biscuit      92.78%

Then create a PivotChart on that data, with Product as the Axis field, Category as the Legend field, and Sum of Result as the Value field

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fb1990

ASKER

Thanks Patrick and Neeraj. Neeraj solution worked for me. Is there a way to hide #N/A? I want add data label and don't want that to show

Subodh Tiwari (Neeraj)

You're welcome.
#N/A is necessary to hide the series with no data in this case Mint sales.

fb1990

ASKER

Thank you. I added iferror to mask it and it changed the #N/A to 0%. I want it to go away but i can live with it. Thanks a a bunch as usual