Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

How to sort pivot table by value ?

Mar 16, 2011 10:04PM PDT

Hi,

I have a block of excel data (Excel 2003) which contains client name, client a/c number, month and value.

I want to create a pivot with a/c number and client name for rows, months 1-12 as column heading and grand total for rows and columns.

So far, no problem, but I want the rows sorted not by a/c number or name, but by grand total of rows, so client with highest total for year is first in table, and client with lowest total is last.

Any ideas how to achieve this ?

Thanks for reading.

Discussion is locked

- Collapse -
Re: pivot table
Mar 16, 2011 10:14PM PDT

1. Copy the contents of the table to the clipboard.
2. Copy/paste values to another sheet
3. Sort the table on that other sheet.

You might like to record or write a macro to automate all of this.

Kees

- Collapse -
Re: sorting pivot table
Mar 16, 2011 10:20PM PDT

I was hoping for a method that was 100% automatic, as each new months data is added, and the table refreshed, it would always have the highest value as the first row etc.

I tried adding 'Value' as the first field, as well as being the data field. This worked as long as no client had more than value, but as soon as they have values in two or more months, you get multiple rows for the same client.

- Collapse -
You should be able to click on the down
Mar 17, 2011 12:17PM PDT

arrow for the row label or name column ...click on 'More sort options'.....then click on 'Sort Ascending' or 'Sort descending', then select by count or sum of records.

If you add entries within the original spreadsheet rows...then update/refresh the pivot table should work like I think you want it.

Let us know.

VAPCMD

- Collapse -
re: You should be able to click on the down arrow..
Mar 17, 2011 7:21PM PDT

When I click on the down arrow for name column, I see Show All, then a list of all the names in the table, all have a tick to the left, the only buttons are OK and Cancel, I can't find 'More sort options' anywhere Sad

- Collapse -
Re: Pivot table sorting
Mar 17, 2011 7:26PM PDT

Is the method you suggested above an Excel 2007 function ?

I am using Excel 2003

- Collapse -
I'm using 2007 but I'm thinking 2003 had a sort
Mar 18, 2011 10:29AM PDT
- Collapse -
Also . . . quite sure there's another way to do the
Mar 19, 2011 1:56AM PDT

same thing (sort the pivot table row total ascending or descending) without using the the Excel Pivot Table Wizard but I don't have Excel 2003 to test it. I don't recall ever using the Pivot Table Wizard to use the ascending/descending sort capability.

Just remember that adding another entry to the spreadsheet will only be included in the pivot table results if you 'refresh' the data .... you'll probably need to add any new entries after the 1st entry and before the last entry in the underlying spreadsheet for any new entry to be included in the pivot table after using the 'refresh data. I just added an entry after the last entry in a spreadsheet and refreshed the associated pivot table and the new entry was not entry was included in the total. When I inserted the same new entry somewhere between the 1st and last spreadsheet entry and refreshed the pivot table...the new entry was automatically added to the row and grand total results.

Try it and let us know.

VAPCMD

- Collapse -
Re: inserting a row
Mar 19, 2011 6:49AM PDT

That's a well-known thing in Excel.

If you have a formula with a range, say =sum(a2:a7) then
1. if you type anything outside range (even in a1 of aCool the formula isn't changed
2. if you delete a row in between, the formula adjusts itself to =sum(a2:a6)
3. if you insert a row in between, the formula adjusts itself to =sum(a2:8Cool
4. if you insert a row above or below the range, the formula isn't adjusted either

That's how Excel works. With a sum it's best to include the row above and below what you see. Then when you insert a row below the last one filled, it's a row inside the range, so the range is extended and still contains all visible rows + one above and one below. But that's a trick that doesn't work with a range that defines a pivot table, I think (but I didn't try, it's possible it just neglects the empty row, like sum does).

Kees

- Collapse -
Understand...was just trying to help the poster with
Mar 19, 2011 9:01AM PDT

the issue of adding new entries at the end (bottom) of the spreadsheet and expect 'pivot table refresh' to subsequently include the new data in the row or column totals. Confirming ...I tried adding another ROW at the end of a spreadsheet and when I refreshed the pivot table it didn't change the the row or column totals.

Hoping this will make it easier to keep the spreadsheet and associated pivot synchronized when additions or deletions are made to the spreadsheet.

VAPCMD

- Collapse -
Re adding new entries
Mar 20, 2011 7:22PM PDT

Having the data expand outside the range of the pivot is not an issue, the data source is aleady laid out in a way that adequate lines for all future months of the current year are already included, they just don't have data yet.

I still think that sort by row toal is a 2007 function only, I can't find it in 2003.

But would love to be proved wrong.

- Collapse -
Did you go thru the tutorial ? It's right there in the
Mar 21, 2011 9:35AM PDT

tutorial I gave you a link for !

VAPCMD

- Collapse -
Re Tutorial
Mar 21, 2011 11:40PM PDT

Many thanks, have now got it to work.

Happy

- Collapse -
You're welcome. PS.....
Mar 22, 2011 9:55AM PDT

You can also right click on an entry in the 'Client Name' column, click on 'Field Settings', from the Pivot Table Field box click on 'Advanced' and click to sort ascending or descending as desired ...without the Pivot Table Wizard. That's how I used to it before migrating to Excel 2007.

VAPCMD