1

Is it possible to hide a series in a chart automatically depending on another cell's value.

Edit: I am building a dashboard for social media analytics with charts for different metrics (impressions, engagements, etc.). I would like to allow the chart to be formatted (show different values, i.e. hide Twitter) based on the value in a drop down menu in the top column that will allow you to select and view metrics for either Twitter, LinkedIn, or both at the same time.

Example showing Impressions chart for Twitter & LinkedIn when dropdown selected should only be showing Twitter data in Q2:

Example showing Impressions chart for Twitter in Q2]

Right now, my back end has the static data for each platform for the year, and then smaller 3x3 tables with data from only the months that are in the quarter selected. The charts are built using this dynamic table.

I can hide a chart using macros, but the problem is that I'll have to create and include 3 separate charts (Twitter, LinkedIn, Both) instead of using just the one in that image that shows both, and hiding the series that is not selected.

To make this worse, I also have a feature that compares to the previous quarter results, so that would mean 3 more charts, for a total of 6, which will just weigh down the spreadsheet and create more work to build and format.

Open to any suggestions on how to have 1 chart that will dynamically hide either Twitter or LinkedIn when the dropdown menu is changed.

My closest solution is dynamically changing LinkedIn values to 0 when Twitter is selected, but this causes the bar graph to be off center from the label - it's minor, but still looking for a better solution.

Improve this question
1
  • If you add auto-filter, then a dropdown is added at the top of the list and controls the visibility of rows in the table
    – cybernetic.nomad
    Commented Jul 19, 2019 at 17:34

1 Answer 1

Reset to default
0

Assuming you chart is something like this:

enter image description here

  1. Select cell A1

  2. Click to Data -> Sort & Filter -> Filter your chart should now look like this:

enter image description here

  1. Use the dropdowns at the top to filter out whatever series you want.

enter image description here

The chart should automatically stop displaying the hidden series

enter image description here

If it still displays the series, right click on the chart an choose "Select Data" from the dropdown menu, then click on "Hidden and Empty Cells in the resulting dialog box and make sure "Show data in hidden rows and columns is unchecked:

enter image description here

Improve this answer
3
  • Okay, thank you for this. Part 2, then, would be: Do you have a formula that can automatically filter a table based on the value of a cell. I.e. =IF(A1="Facebook", HIDE TABLE COLUMN 1), "")....
    – Sam
    Commented Jul 19, 2019 at 19:48
  • Formulas only put values in the cell that contains them and cannot affect other cells. I am starting to think we are having an XY problem. It may be useful for you to explain exactly what you have, and what you are trying to accomplish, along with providing sample data
    – cybernetic.nomad
    Commented Jul 19, 2019 at 19:54
  • Hi @cybernetic.nomad, thank for you pointing that out. It's true I may have been having that problem. I updated the description with more detail. I would appreciate if you could have another look and let me know if it's not clear. Thank you!
    – Sam
    Commented Jul 22, 2019 at 12:28

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .