What is the method to distinguish between zero values and null/empty entries in Google Sheets?

Other Questions Google Sheets
1

I am in the process of automating charts that visualize power readings for an equipment over the past eight quarters. I’m utilizing a pivot table to manage the raw data before generating charts. However, I’ve encountered an issue where the pivot table substitutes blank values in the original data with zeros, misleadingly indicating that power readings have dropped to zero when, in reality, the equipment was either out of service or the voltage readings were omitted for that quarter.

While manually filtering data for each piece of equipment would be an option, I want to monitor multiple equipment and their respective power charts simultaneously.

In the following sample pivot table, the empty rows for 03/03/2023 and the voltage readings skipped on 08/08/2023 are incorrectly displayed as zeros, which is problematic for accurately representing the data during visualization.

| Date       | L1/L2 | L1/L3 | L2/L3 | L1   | L2   | L3   |
|------------|-------|-------|-------|------|------|------|
| 03/03/2023 | 0     | 0     | 0     | 0    | 0    | 0    |
| 05/09/2023 | 476   | 478   | 478   | 0.31 | 0.32 | 0.36 |
| 08/08/2023 | 0     | 0     | 0     | 0.33 | 0.32 | 0.22 |
| 11/02/2023 | 487   | 488   | 490   | 0.18 | 0.22 | 0.19 |
| 02/09/2024 | 481   | 482   | 484   | 0.35 | 0.34 | 0.29 |
| 05/14/2024 | 479   | 481   | 479   | 0.31 | 0.32 | 0.36 |
| 08/07/2024 | 479   | 481   | 483   | 0.6  | 0.6  | 0.6  |
| 11/06/2024 | 484   | 484   | 487   | 0.2  | 0.3  | 0.2  |

How can I create charts that correctly highlight the distinction between blanks and zeros in my source data?

2

To differentiate zeros from null or empty values in your pivot table, you can use an additional column in your original data with a formula such as IF(ISBLANK(A2), "Null", IF(A2=0, "Zero", A2)). This will help you track the true nature of the readings. Then, in your pivot table, you can include these columns to use conditional formatting to visually highlight zeros differently from blanks. When creating charts, omit the “Null” entries, so the absence of data is clear and zeros are only shown when they are genuine readings.