How to Replace Blank Cells with 0 in a QUERY Pivot in Google Sheets

Replacing blank cells with 0 in a QUERY pivot in Google Sheets can significantly enhance the readability and interpretation of data. And the best part is, it can be done effortlessly by leveraging modern Google Sheets functions like LET or MAP.

But here’s the thing – you don’t need to fully comprehend the QUERY formula you’ve used to apply this trick. Whether you’ve crafted the formula yourself or not, it’s totally alright. You can still replace blank cells with 0 in the QUERY Pivot result without directly modifying the formula. Instead, we’ll use a clever wrapper to substitute blanks with zero.

Let’s dive into a sample dataset that includes text, number, and date columns. This will help you understand how the formula handles various data types while replacing blank cells with 0.

Sample Data and QUERY Formula: Setting Up Your Example Sheet

Let’s start by working with a minimal sample dataset that features three data types: Date, Text, and Number.

In the provided sample data (please refer to the image below), column A holds the payment made date, column B contains the item or the type of expense, and column C holds their corresponding amounts.

Now, here’s the QUERY formula in cell E1 that generates a Pivot table. It organizes payment dates in rows, items in columns, and aggregates the payment amounts.

=QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1)

Take a look at the two green dots (manually placed) in the QUERY Pivot output. How do we fill those blank cells with 0? Of course, we can’t enter 0 in those cells because that will break the QUERY formula. But worry not, my friend, I’ve got the solutions for you.

Replacing Blank Cells with 0 in a QUERY Pivot Using the MAP Function

The MAP function is a useful LAMBDA helper function in Google Sheets that allows us to map each value in an array to a new one.

In this context, the array is the result of a QUERY formula, such as a Pivot table. By employing the MAP function, we can iterate through each value in the Pivot table and replace any blank cells with zero.

Syntax:

MAP(array1, [array2, ...], lambda)

Generic Formula:

=MAP(array1, lambda(cell_value, IF(cell_value="", 0, cell_value)))

Simply substitute array1 with your specific QUERY formula.

For example, in my case, the formula to replace blank cells with 0 values would be:

=MAP(QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1), lambda(cell_value, IF(cell_value="", 0, cell_value)))

Note: The formula will convert dates to date values. Therefore, select the dates in the Pivot table and apply Format > Number > Date.

This formula effectively transforms the specified QUERY result, replacing any blank cells with 0 while leaving other values unaffected.

Formula Breakdown:

• QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1): This serves as the array or range of values that you intend to manipulate.
• lambda: This is a function defining the transformation to be applied to each element in the array.
• cell_value: It is a user-defined name within the lambda function, representing each cell value in the array.
• IF(cell_value="", 0, cell_value): This IF logical test is the lambda function. It takes each cell_value from the QUERY Pivot as input and applies the following logic: IF(cell_value=””, 0, cell_value)

Replacing Blank Cells with 0 in a QUERY Pivot Using the LET Function

The LET function comes into play when we need to evaluate formula expressions using declared named arguments.

Syntax:

LET(name1, value_expression1, [name2, ...], [value_expression2, ...], formula_expression)

Generic Formula:

LET(name1, value_expression1, ArrayFormula(IF(name1="", 0, name1)))

In this generic formula, replace value_expression1 with your QUERY formula.

For instance, to replace empty cells with zero in a QUERY Pivot, you can use the following formula:

=LET(name1, QUERY(A1:C, "Select A, Sum(C) where A is not null group by A Pivot B", 1), ArrayFormula(IF(name1="", 0, name1)))

Just like the MAP formula, here as well, you need to format the date values to dates in the Pivot result.

Formula Breakdown:

• name1: This is the name assigned to the QUERY formula (value_expression1). You can choose a more meaningful name, like “query_formula.”
• QUERY(A1:C, "SELECT A, SUM(C) WHERE A IS NOT NULL GROUP BY A PIVOT B", 1): The formula (value_expression1) whose result can be referred to later with name1 that was declared before.
• ArrayFormula(IF(name1="", 0, name1)): This expression is evaluated using the LET function. It checks if the value of name1 is an empty string. If true, it returns 0; otherwise, it returns the original value of name1.

Conclusion

Now that you have two solutions to replace blanks with 0 in a Query Pivot, you might find yourself a little uncertain about which one to opt for.

Feel free to choose either, as both are straightforward formulas and performance-oriented. It’s worth noting that both formulas will convert dates/timestamps to date values. Simply ensure to apply the relevant formatting from the Format menu by selecting those date values.

If you want to explore more ways to level up your Google Sheets skills, head over to Crawlan.com for more expert tips and tricks!

Related:

1. How to Fill Empty Cells with 0 in Pivot Table in Google Sheets.
2. How to Use QUERY Function Similar to Pivot Table in Google Sheets.
3. How to Format Query Pivot Header Row in Google Sheets.
4. How to Pivot Multiple Columns in Query in Google Sheets.
5. How to Retain All Column Labels in Query Pivot in Google Sheets.