Sort pivot table in tabular layout

[Vivek D]

I have created a dashboard that shows some summary pivot charts and details in the form of a pivot table in tabular form showing a subset of all the raw data. All of these are connected to slicers which allow the users to click and look filter information and view details too easily.

Everything has turned out well but users now want the ability to sort the details table by any column or combination of columns just like a normal table.

Is that possible.

Note: I've attached a sample spreadsheet showing what I have and need.

 

[Hui]

Whats wrong with them clicking on the drop downs at the top of the table


and using Sort

or

Select any cell in the field they are interested in and using the Sort Buttons on the Data Tab?

 

[Vivek D]

Whats wrong with them clicking on the drop downs at the top of the table
View attachment 14572

and using Sort

or

Select any cell in the field they are interested in and using the Sort Buttons on the Data Tab?
View attachment 14573

Since the table is a pivot table the sorting doesn't work like it does with a normal table.
e.g. If I sort by Order Date first and then by Item, I can't do that as there is no Custom Sort option available.
Plus one more issue is that if I apply a sort to a column how do I remove the sort from that column. There is no remove sort option.

 

[Vivek D]

This is still continuing to be a request from users and the biggest hurdle to making the dashboard "complete". users want an ability to sort the table based on any of the columns (ideally by being able to click on the column titles). Anyone has a solution?

 

[NARAYANK991]

Hi ,

Can you copy the entire pivot table and paste link ? Your users can do what they want with the linked range.

If you can explain what the purpose of sorting the data is , a more appropriate solution can be suggested.

Narayan

 

[Vivek D]

I have created a Sales dashboard using pivot tables and slicers.

The dashboard consists of a number of charts showing the TCV by various dimensions e.g. by Business Unit, Deal Type, Size of TCV, Closing quarter etc.
Users can slice and dice the data using the various slicers made available in the dashboard.

On another sheet I have a pivot table (setup like a table i.e. tabular layout with repeat all item labels) that shows further details of each opportunity.
This is also connected to the slicers, so when a user chooses a set of options in the slicers, this table shows the exact records that make up those numbers in the charts.

This is turning out to be very convenient for the users and they are loving it as it can get them from high-level, right down to the details.

Here's what's needed:-
After applying the necessary filters and when they look at the details, users want to be able to sort detailed rows based on say Close Date (what is closing immediately?) or TCV value (which are the biggest deals?), or Phase ( A, B, C, D, E) etc

 

[Vivek D]

I just tried out your paste link suggestion which is basically referencing each cell of the pivot table in a new sheet.

This seems like it will provide what I need.. EXCEPT...

How do I make the table in the new sheet expand and contract as the number of rows in the pivot table increases or decreases based on the filters applied?

 

[NARAYANK991]

Hi ,

What you are explaining is not reflected in the file you have uploaded ; can you explain what you want done with the data and the pivot table that is available in the uploaded file ?

The uploaded file shows just 2 slicers , one for the Reps , and the other for the Regions. With this setup , what is the purpose of sorting the pivot table ?

The charts are based off the summary pivot tables , and not the base pivot table.

I am not able to understand what sorting the pivot table will do.

Narayan

 

[Vivek D]

The attached file is just a simple sample to illustrate the problem. Actual report is totally different, has a lot more data, slicers, charts, fields etc.

In the sample, say I choose "Central" in the slicer and then I wanted to order by order date newest first.. and after that I want to look at it by Total in descending order of amount? How would I do that?

 

[Vivek D]

Hi ,

Can you copy the entire pivot table and paste link ? Your users can do what they want with the linked range.

Narayan

The Paste Link option did not work out.

When the new linked table is sorted, the cell references change too i.e. the first row in the table that was initially say =Data!A2, after sort could be =Data!A100.

Now when the pivot table contents change and it has say only 20 rows, the first row in the linked sheet ends up blank because it is still referencing the 100th row.

 

[NARAYANK991]

Hi ,

I realised that later. I don't understand why your slicers are connected to PivotTable11 , since the charts themselves are based on PivotTable10 and PivotTable12.

Suppose you disconnect the slicers from PivotTable11 , then you can sort it the way you want. Is this not so ?

Narayan

 

[Vivek D]

PivotTable11 is meant to show the user the detailed records that make up the numbers in the charts (with only the important columns). That's why it's connected to the slicers.

 

[NARAYANK991]

Hi ,

Since the Pivot Table is a native construct of Excel , it will have its own constraints / limitations ; if you really want to give flexibility and power to your users , you might have to move away from using just a Pivot Table to using a full-fledged dashboard , which will have options for sorting ; this can be done using either formulae or VBA. Of course , this is going to involve some amount of work.

Narayan

 

[Vivek D]

Yes. Kind of realized that and started looking at some alternative ways to solve the problem. Have found a few. Will try them out and if everything works out post the solution here.