CODEWITHSUNDEEP
excelgraph
Juan P. Sanchez
Juan P. Sanchez

Reputation: 11

Do not show blank cells on excel graph

I have a problem solving something out in excel. I constantly have to manage financial informations from dozens of company as part of some startup acceleration programs.

In this context, as part of some analysis, I have to show, through graphs, the revenue information of the last twelve months of each company. However, some companies began operating 12 months ago, some other 8 months ago, and so on. Moreover, some companies report financial information each month, some others each quarter and so on.

However, if I select the same range of cells (same columns but different rows) for each company, the graphs will show the blank spaces when we don't have the monthly information of one company.

In this context, it is very inefficient to manually change the range of cells that each graph has to show for each company whenever we receive information from one company. Hence, I'm wondering if there is a way in which you could select a range of cells for a graph, but that the graph do not show the cells that are on blank, which could be some cells in the beginning or some cells at the end.

I'm attaching an image to explain more my problem, in which I point out with a red circle the blank cells that I don't want to have.

Here

Upvotes: 1

Views: 6354

Answers (1)

Jsleshem
Jsleshem

Reputation: 735

Directly copying @harrymc answer from here.

There are various methods for avoiding the zeroes, none of them are perfect. Below are a couple of such methods.

Filtering the data set

  1. Select the data range.
  2. On the Data tab, click Filter in the Sort & Filter group, to add a filter to all of the columns.
  3. Click the drop-down arrow on the column's first row and uncheck 0.
  4. Click OK to filter the column, which will filter the entire row. Be sure to remove the filter when you're done.

Replace 0s with NA()

  1. Select the data set
  2. Click Find & Select in the Editing group on the Home tab and choose Replace, or type Ctrl+H.
  3. Enter 0 in Find what.
  4. Enter =NA() in Replace
  5. Click Options to display additional settings and check Match entire cell contents.
  6. Click Replace All.
  7. Click Close and OK.

For more information see the article How to suppress 0 values in an Excel chart.

My addition

  • One thing to note is that if these values are formulaic, then you can N/A them out via the formula bar. That would have the graph disregard the values.

Upvotes: 2

Related Questions