Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

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

Copper Contributor

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

1 Reply

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.