I have a not-too-large Excel spreadsheet (perhaps 400 rows and 25 columns). One column (let’s call it “Total Sales”, containing a dollar amount) has subtotals. The rows detail each salesperson’s sales, one row for each item sold, so that each salesperson may have a number of rows. There is a subtotal in the “Total Sales” column.
How do I sort this worksheet by those subtotals, so that the sales for the salesperson with the highest dollar amount appear at the top of the worksheet, then the sales for the person with the second highest dollar amount, and so on?
Doesn’t the regular sorting tool work? Click on a cell in the column and then click the “A/Z” button.
Don’t select the column, though, or Excel will sort the data in the column and leave everything else where it originally was, certainly not what you want.
No, it doesn’t work. I thought that might be because there are subtotals in the column by which I want to sort (not sure - I didn’t create this spreadsheet).
What I’ve got is:
Bob Smith $245
Bob Smith $925
Bob Smith $3,450
$4,620
Frank Jones $1,250
Frank Jones $950
Frank Jones $22,456
$24,056
What I want to do is sort the table so all of Frank Jones’ sales appear first, with the subtotal, and then all of Frank Jones’ sales, with their subtotal, and so on (there are a lot more than two salespeople).I
I’ll also point out that it all works just as well with a much larger spreadsheet. So if your extra 23 columns contain things that you’d like bunched together and summarized, it’ll do that for you. (I.e. if you include “Type of Sale”, it’ll put all the Apples together, the Oranges together, etc.) You just put that column under “Rows” along with “Name”. It will create this extra subtotal row that many get rid of - you just right-click on it and select “Hide”, which will then apply to all the Subtotals for that category for all salesmen.
Come back if you get stuck. We love pivot tables around here.
Exactly my thoughts when reading this. I hate it when people try to make the spreadsheet “pretty” without using the functions of the tool. I frequently have to get a file from someone and spend an hour to get it in shape to pull the information from it that they want.
Even worse is I have one director that thinks that hiding a row is the same as deleting it and can’t understand why the totals (that they do by hand on a calculator) don’t match those on the sheet.
I didn’t build this spreadsheet, so I’m stuck with it. Anyway, yes, a pivot table seems to be the way to go. Problem is that all the names and other data were entered pretty inconsistently (“Bob Smith” here, “Robert Smith” there). I’ll have to clean it up a bit first.
But thanks for the advice. And this gives me a chance to learn more about pivot tables. I’m barely familiar with them. I’m the Word guru around here, but I’m not nearly as good at Excel.
Screw that, man. If you get stuck cleaning it up to do anything useful with it, show the mouthbreather that made it the right way to do it. Because the way it sounds, a pivot table isn’t going to work with the chart described in the OP. Is this what it looks like?
Salesman Item Price
Bob Apples 1.00
Bob Oranges 2.00
Bob Computers 100.00
Bob Sub 103.00
Tom Apples 4.00
Tom Oranges 12.00
Tom Computers -100.00
Tom Sub -84.00
*Tom dropped a computer
If so, a pivot isn’t going to work until you get rid of those subtotals.
If one doesn’t learn pivot tables, he will eternally suffer “spreadsheet hell.”
This happens because he will be given a spreadsheet that he has to calculate subtotals for. Eventually, he puts a bunch of conditional sums (e.g. SUMIF()) cell by cell or rearrange columns to make the subtotal command work the he wants. All of those steps basically reinvented pivot tables. Pivot tables get it done in 30 seconds. The manual method takes hours and is full of errors.