How to hide columns or rows in excel but still keep the data in a chart showing

Question

Hi,

I have set up some charts in excel now I want to hide the rows / columns where the original data sites so that people can just see the charts however whenever I hide these the data in the chart disappears.

I have googled how to do this and it's telling me to select the data, and press the button that says hidden and empty cells. I done this where the cells are not yet hidden and after I have hidden then however this button still stays blocked out and unable to select.

Answer 1

The easiest way to fix that is to create your charts in one sheet based on data in another and then hide that data sheet. The charts will still show.

Answer 2

thanks, any other way as I've already created into one tab and its a live document that over 30 people are using daily so don't want to pause their work to add more tabs.

Answer 3

Can't think of a better way, to be honest.

Answer 4

right-click the chart and choose Select Data:

in the next dialogue box choose Hidden and Empty Cells

then in the next dialogue tick the Show data in hidden rows and columns box:

Answer 5

Hi p45cal,

that's the way i normally do it however that hidden and empty cells button it will not let me select it, it's greyed out