Excel – How to plot a graph of only periods with non-zero values

chartsmicrosoft-excel-2013

I have a machine that runs on an irregular schedule, and records the power usage into an Excel file. The machine automatically turns on at a different time every day and is sometimes off for a few days. For example:

20/3/2017 13:00:00  0kW
20/3/2017 14:00:00  13.1kW
20/3/2017 15:00:00  12.9kW
20/3/2017 16:00:00  0kW
...
21/3/2017 13:00:00 0kW
21/3/2017 14:00:00 0kW
21/3/2017 15:00:00  12.5kW

Notice that a number of the entries show a usage value of zero. I want to plot a graph of the data showing only the periods when the values are non-zero, and have no gap for the excluded values.

What the graph looks like with the table I have:

What the graph looks like with the table I have

What I actually want – gap for zero is totally removed:

What I actually want - gap for zero is totally removed

How can I do that?

Best Answer

You need to remove 0 values from your data to let Excel understand you don't want to plot it.

Easiest way for that is to use a calculated column:
=IF(D2=0,"-",D2)

Theoretically, now you can set your chart now, and it'll exclude those points, however I couldn't set it up with a normal charts.

But it works with a pivot chart:

  • Select your data and go to insert - charts - pivot chart
  • Set:
    • rows: date time
    • values: non-zero; set "summarize field by" to average
  • right click on the chart - change chart type - 2d line chart

enter image description here

Update

If you want to exclude times with 0 also from X axis range, then just filter them out in pivot table (move "non-zero" column to filters)

Or, in this case you don't even need a calculated column, you can plot and filter directly your "values" column.

Related Question