Filter wrong data out of pivot table in Excel

Question

I've a lot of data I must analyse but some data is wrong. That gives a problem for draw my pivot charts and tables. If there were no errors the chart must look like this:

But it doesn't. Here is what I get:

Of course the wrong fields are more then only that. In total I have more than 300k rows correct or not. I've no idea how many percent is wrong.

Update one: The data I've get was taken every 5 minutes over a period of 5 years. Sometimes is it possible that there is no data on the 5^th minute but on the 6^th or 4^th minute. Well that mistakes must be filtered.

Update two: Here is some fake data from my pivot table where you can see that the strike through lines are wrong:

╔═══════════╦════════╗
║   Time    ║   kWh  ║
╠═══════════╬════════╣
║ 07:40:15  ║ 32,65  ║
║ 07:45:25  ║ 40,83  ║
║ 07:46:05  ║ 0,00   ║
║ 07:50:57  ║ 50,38  ║
║ 07:55:14  ║ 55,71  ║
║ 07:56:20  ║ 0,00   ║
║ 08:00:14  ║ 60,72  ║
║ 08:05:25  ║ 61,42  ║
║ 08:10:54  ║ 70,78  ║
║ 08:15:10  ║ 80,75  ║
║ 08:16:01  ║ 0,00   ║
║ 08:20:25  ║ 85,06  ║
║ 08:25:02  ║ 85,46  ║
║ 08:30:10  ║ 100,73 ║
║ 08:35:25  ║ 100,21 ║
║ 08:37:10  ║ 0,00   ║
║ 08:40:47  ║ 115,60 ║
║ 08:45:45  ║ 120,16 ║
║ 08:50:01  ║ 128,77 ║
║ 08:55:25  ║ 140,75 ║
╚═══════════╩════════╝

Background information: the information goes about solar panels and the production in kilo watt hour of it per 5 minutes.

My questions are now:

• How can I filter that wrong data explained above out of my pivot tables and pivot charts in Excel version 2013?
• Can I calculate how many percent is wrong?

Answer 1

You need to insert a new column and there you can check whether the data is ok:

=MOD(MINUTE(A2),5)=0

This will give TRUE for correct data and FALSE for wrong. Just filter it in the pivot chart.

If you want to count wrong and correct data it's also easy in a pivot table.

Update

Filtering out wrong values

After inserting the new column, just insert a pivot chart on the data with the following settings:

• report filter: "correct", set it to TRUE
• axis fields: "time"
• Values: Average of "kWh" (same result would be with sum too)
• change chart type to "line chart"

Count wrong and correct values

In a pivot table set:

• column labels to "correct"
• values to Count of "time" in "value field settings" - "show values as" select "% of grand total"

note

getting your data from the site it wasn't automatically recognized my excel as time, I had to use TIMEVALUE() function as in first screenshot; best approach however to convert already the original column to time if it's text