Forum Discussion

DCasey400's avatar
DCasey400
Copper Contributor
Jan 03, 2024

chart percentages disappear when i hide rows, and the show data in hidden rows..unavailable

Hi all,

 

I am working with multiple charts on one sheet; and trying to hide my data tables.  all was well until I added percentage values to the end of a bar chart, and each time I hide the data, the values disappear.  I tried clicking on the chart, going to select date, clicking on the Hidden and Empty Cells button, and then the Show data in hidden rows and columns option is unavailable, ie it is 'greyed out', so I cannot select that option.

 

Any insight would be most welcome, thank you all so much for your time...

 

Deborah

Charting
excel

3 Replies

Sort By
  • bluenautica's avatar
    bluenautica
    Copper Contributor
    Mar 11, 2024

    DCasey400 I have the easiest solution. All you need to do is right-click the graph and select (Select Data). At the bottom left click the button that says "Hidden and Empty Cells". In the popup make sure "Show data in hidden rows and columns" is checked. That is all you need to do, now you can hide the data in your rows and columns without it affecting your chart. 

    • Lenny_Raikov's avatar
      Lenny_Raikov
      Copper Contributor
      May 14, 2024

      bluenautica: Unfortunately, it's not that simple. In my case graphs only become visible when I tick the "Display hidden data" - no matter whether it was on or off! Weird.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 04, 2024

    When you hide rows or columns that contain data used in a chart, Excel might automatically hide the corresponding data in the chart as well. This behavior is due to Excel's default settings to hide data in hidden rows or columns.

    To address this issue and make the percentage values visible even when the rows are hidden, you can try to use one of the following methods:

    Method 1: Change Axis Format

    1. Right-click on the axis that contains the percentage values.
    2. Select "Format Axis" from the context menu.
    3. In the Axis Options pane, under the "Number" category, choose a format that does not rely on the actual data (e.g., Percentage with no decimal places).

    This method will display the percentage values even if the corresponding rows are hidden.

    Method 2: Use a Dummy Series

    1. Add a dummy series to your chart that contains the percentage values.
    2. This series can be based on a hidden set of cells where you manually enter the percentage values.
    3. Ensure that this dummy series is visible even when you hide other rows or columns.

    Method 3: Use a Data Label Custom Formula

    1. Click on one of the data labels on the chart.
    2. In the formula bar, enter a formula that references the cell containing the percentage value, even if it's in a hidden row. For example:

    =Sheet1!$B$2

    Replace Sheet1 and $B$2 with your actual sheet and cell reference.

     

    These methods should help you maintain visibility of the percentage values on your chart even when rows are hidden. Choose the method that best fits your workflow and chart structure. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Resources