How to avoid blank cell in Pivot table

Help Activities
, ,
1

Hi Team,

I want to avoid blank cell in pivot , Please help me anyone for this.

image

Regards,
Raja G

2

@Raja.G

Before applying the Pivot, try to filter the data table using the filter data table activity to remove the blanks.

3

@Raja.G,

Clean the data before applying pivot.

4

Hi @Raja.G ,

  • Fill Blank Cells:
    In the Layout & Format tab, check the box for “For empty cells show” and enter a value (e.g., 0 or N/A).
  • Filter out blank rows:
    You can also apply a filter on the Row Labels to exclude blank values manually.
5

Hi Team,

Still i am not getting solution please anyone help me.

Regards,
Raja G

6

@Raja.G,

Share sample data file to try.

7

Hey @Raja.G

Approach 1 : You can keep the Pivot in a template Sheet , and apply a Label Filter to it.
Please refer the Screenshot below for more details :
This image shows an Excel pivot table filter menu with various filtering options and checkboxes for selecting specific items. (Captioned by AI)

A screenshot shows a pivot table in Excel with a filter applied to exclude blank column names and various categories listed in the "Row Labels" section. (Captioned by AI)
A screenshot shows a pivot table in Excel with a filter applied to exclude blank column names and various categories listed in the "Row Labels" section. (Captioned by AI)962×325 18 KB

Approach 2 : If you are generating the Pivot each time , then you can use macros to apply filters or to remove the Blanks.

Hope it helps you out!
-Vikas :slight_smile:

8

Hi, @Raja.G

It can be help to you. I used find/replace aktiviti

This image shows the process of creating a pivot table in an Excel file and replacing values using specific fields and destination sheets in an automation workflow. (Captioned by AI)
This image shows the process of creating a pivot table in an Excel file and replacing values using specific fields and destination sheets in an automation workflow. (Captioned by AI)479×809 28.6 KB

Result:
image

After you can filter empty rows.

10

Hi @Kismet_Tosun ,

Now working for me, but i want enter row of blank , here only removed blank found cell

The data showing like below, sum total available but that is single cell

image

11

Hi, @Raja.G

I added somethings. It is working well

1:

This image shows an automation workflow for creating a pivot table from data in an Excel file, where the pivot table summarizes the "Bildirlim No" field as rows and the "Maliyet" field as sum values. (Captioned by AI)
This image shows an automation workflow for creating a pivot table from data in an Excel file, where the pivot table summarizes the "Bildirlim No" field as rows and the "Maliyet" field as sum values. (Captioned by AI)514×793 41.7 KB

The image shows a sequence of steps in an automation process within an Excel workbook, involving finding and replacing values, logging the results, copying and pasting ranges, and then deleting specific rows. (Captioned by AI)
The image shows a sequence of steps in an automation process within an Excel workbook, involving finding and replacing values, logging the results, copying and pasting ranges, and then deleting specific rows. (Captioned by AI)598×816 27 KB

Result;
The image shows a spreadsheet with two columns: "Row Labels" and "Sum of Maliyet," displaying numerical data along with a grand total of 2576.5. (Captioned by AI)

12

Hi @Kismet_Tosun ,

If possible please share xmal.

Regards,
Raja G

13

Hi, @Raja.G

Sure, Here is it

Test-221_PivotBlancRows.xaml (16.4 KB)
Data.xlsx (20.4 KB)

14

@Raja.G Are you check it??

15

The only way I’ve found that works is to format the cells in the pivot table with a custom format so that it hides the blank text values. If the screenshot I loaded doesn’t appear, use this setting in the ‘Custom’ field in the format cells dialog:
#,##0;(#,##0);#,##0; The semicolon separates the formatting in sequence for:
Positive value
Negative values
Zero values
Text Values
(Blank) is text so by putting nothing after the 4th semicolon, text values disappear. Afterward, if you select what looks like an empty cell, you will still see (blank) in the formula bar. It still there. you just can’t see it and it isn’t annoying.

image
image532×484 10.3 KB

16

Here is the only way I could get it to work. I did find and replace. Replaced (blank) with 
 and then I changed the font color in that row to the same color as my background :D. I guess I didn’t have to find and replace, but it makes it look like it isn’t there and it is not adding the blank rows to my count.

1 Like