Hide blank head rows in Pivot

Question

Usually I use the filter to take out the blanks, but this is not adequate this time. Since I have three "columns" of my excel-sheet in the "Row Labels" section, called "Project-Category" (1-3), Functions (1-5) and Sub-Functions (1.1-1.3 & 2.1-2.3). So not every Function has a sub-function. Thats why some Function has a "Blank" in the third column "Sub-Function".

If I filter out the Blank in my Pivot all the numbers from the functions, without sub-functions, disappears and only those function with sub-function stays remaining.

I tried as well collapse all functions without sub-function and just expand these function with sub-function. But the issue is, that Function 1 has sub-function but only in Project-Category 3 and not in Category 1 and 2. So when I expand it in Category 3, it expands as well in category 1 and 2, showing a Blank row.

What is the best way to oppress the blanks without loosing all the numbers? And it should be resist after update the pivot.

Thanks for your support.

Answer 1

You can try the following:

Display or hide blank lines

You can display or hide blank lines after a row or item.

Rows

Select the row field, and then on the Options tab, in the Active Field group, click Field Settings.

The Field Settings dialog box opens.

TIP: You can also double-click the row field in outline or tabular form.

To add or remove the blank rows, click the Layout & Print tab, and then under the Layout section, select or clear Insert blank line after each item label.

Items

Select the item in a PivotTable report.

On the Design tab, in the Layout group, click Blank Rows, and then select Insert Blank Line after Each Item Label or Remove Blank Line after Each Item Label.

NOTE: You can apply character and cell formatting to the blank lines, but you cannot enter data in them. Change how items and labels with no data are shown

Click the PivotTable report.

Click the Display tab, and then under the Display section, do one or more of the following:

Show items with no data on rows Select or clear to display or hide row items that have no values.

NOTE: This setting is only available for an Online Analytical Processing (OLAP) data source.

Show items with no data on columns Select or clear to display or hide column items that have no values.

NOTE: This setting is only available for an OLAP data source.

Display item labels when no fields are in the values area Select or clear to display or hide item labels when there are no fields in the value area.

NOTE: This check box only applies to PivotTable reports that were created by using versions of Microsoft Office Excel earlier than Office Excel 2007.

For more information Click Here