Replacing blank cells with 0 in a QUERY pivot in Google Sheets can enhance the readability and interpretation of the data. This can be achieved effortlessly by leveraging modern Google Sheets functions like LET or MAP.

It’s important to note that 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 alright. You can still replace blank cells with 0 in the QUERY Pivot result without directly modifying the formula. Instead, we’ll use a wrapper to substitute blanks with zero.

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

Sample Data and QUERY Formula: Setting Up Your Example Sheet

Let’s work with a minimal sample dataset featuring 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.

The following QUERY formula in cell E1 generates a Pivot table, organizing payment dates in rows, items in columns, and aggregating the payment amounts.

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

See 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. Let’s go to our solutions.

You May Like: Learn Google Sheets Query Function: Step-by-Step Guide.

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, allowing 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 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 facilitates the evaluation of formula expressions by 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

Given that you now have two solutions to replace blanks with 0 in 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.

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.