Share via

Pivot Table Slicers And maintaining formatting when filtering

Anonymous
2018-09-29T00:20:14+00:00

Hi - Hope you can help. I have fumbled around for hours attempting to find a way for my pivot table to maintain its formatting even after I use a slicer. I have already unchecked "Autofit column" on update. When I use the filter, the pivot table changes its size. Is there any way to stop this?                                                                                                                                                                                                 

                 ** The Pivot right below is Before using the slicer to filter, And the Pivot below that is after using the slicer to filter. .... I apologize for the crummy copy and paste.                                             

PRODUCT 201810 201811 201812 201901 201902 Grand Total
APPLE 85,500 82,000 82,000 249,500
ORANGE 3,000 3,000
LETTUCE 0 0 0
TOMATO 2,123 0 531 2,654
PEACH 27,344 27,344
LEMON 0 0
GRAPEFRUIT 500 500
KIWI 31,284 31,284
Grand Total 89,000 2,123 31,284 82,531 109,344 314,281
SUM OF RECCURING REVENUE TERM END
PRODUCT 201810 Grand Total
ORANGE 3,000 3,000
Grand Total 3,000 3,000

Thanks, John

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2018-10-02T15:47:21+00:00

Hi John,

When we use slicer in pivot table to filter data, it is the expected behavior that the original pivot table will change. Thanks for your understanding.

Regards,

Tina

Was this answer helpful?

1 person found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2018-09-29T09:48:04+00:00

Hi John,

How did you get the second table as you provided? After you create the slicer, click an item in the slicer?

If yes, we'd like to explain that when you select an item in the slicer, the data for that item will be displayed in the report, the original pivot table will change.

You can try to click the button in the upper right corner of the slicer to Clear Filter, then the pivot table will change back.

Here are some articles for your reference:

Use slicers to filter data

Video-Use slicer to filter data

If above information doesn't help, to better understand the situation and help you, we'd like to confirm the following information:

1.What steps did you do before you getting the second table you provided?

Select Insert tab>in Filer group>Slicer>select the fields you'd like to filter and click OK, then you get the second table immediately?

If these are not your steps, please clarify your detailed steps.

2.Please capture a screenshot which containing the slicers you created and the pivot table, share it with us.

3.Please go to File>Account> capture a screenshot of Product Information and share it with us. (Note to mask your email address.)

Thanks,

Tina

Was this answer helpful?

0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2018-09-29T17:44:37+00:00

    Hi Tina - I had included a poor and inaccurate image in my original post. It is actually a single table. The top image is unfiltered. The 2nd image is filtered for the product oranges.

    The top, unfiltered image shows columns A through G.

    The bottom filtered image shows the table, auto reformatted to columns A through C.

    What I was hoping is that there was some way to preserve the original, unfiltered table size for columns  (in my case, columns A-G) - even if I filtered.

    In my example the product oranges only had data for 201810, so when I filtered for just the product oranges my table was automatically downsized to not show the other columns of 201811, 201812, 201901, 201912.

    I figure there is not a way to do this .... But definitely was hoping.

    Thanks very much,

    John

    Was this answer helpful?

    0 comments