Creating a chart and ignore empty rows/cells

Question

Hi, so I want to create a chart of top 5 people in a topic. In some cases, there's 2 same score so top 5 rank will be 6 or 7 people. How do I create the chart that automatically contains 5 or 6 or 7 rows according to the list of people?

In the example, I filter 5 top people in pivot table, but preparing 7 rows (in case there are same scores). The data shows 6 people in top 5 filtering. How do I automatically change the chart to show 6 people (removing N/A)

Answer 1

You have to filter out the N/A, the chart shows the visible values only.

Andreas.

Answer 2

You have to filter out the N/A, the chart shows the visible values only.

Andreas.

Yeah, I've done that.

But I mean, I want the chart to adjust the position. Not leaving a blank space, as it is shown in the red mark.

Is it possible to make the chart automatically adjust itself?

Answer 3

You have to filter out the N/A, the chart shows the visible values only.

Andreas.

Yeah, I've done that.

No, your data includes an empty rows and that's why the chart shows it. A chart shows data "as is", he doesn't "cheat".

If your data is based on a Pivot table, filter out the N/A in there and use a Pivot Chart.

If you need further help show me your file.

Andreas.

Answer 4

You have to filter out the N/A, the chart shows the visible values only.

Andreas.

Yeah, I've done that.

No, your data includes an empty rows and that's why the chart shows it. A chart shows data "as is", he doesn't "cheat".

If your data is based on a Pivot table, filter out the N/A in there and use a Pivot Chart.

If you need further help show me your file.

Andreas.

Yeah, that's exactly my question. How can Excel's chart adjust that reference.

And yes, the data is indexed from the pivot table (with a top 5 filter, but sometimes shows more than 5 due to same value).

But now I've found the answer on YouTube. For anyone having the same issue, here is the link:

Automatically skip blanks in Excel charts with formulas (ignore gaps in Excel chart axis) - YouTube

Answer 5

But now I've found the answer on YouTube. For anyone having the same issue, here is the link:

Automatically skip blanks in Excel charts with formulas (ignore gaps in Excel chart axis) - YouTube

That's what I call a complicated workaround for a simple problem.

Andreas.