Excel Question

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?

Thans very much for any help.

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
Frank Jones $1,250
Frank Jones $950
Frank Jones $22,456
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

Have you tried clicking on the 2 to the left of the cells so it only displays the subtotals and then sorting? I think that will work for you.

What “2”? You mean where the outline shows? Tried that, doesn’t help. Thanks, though.

  1. Delete those subtotals. It’s terrible spreadsheet management.
  2. Learn how to do a pivot table - it does exactly what you’re looking for.

I’ll outline it quickly here, using your data (labeling Column A as “Name” and B as “Sales”:

Name Sales
Bob Smith $245
Bob Smith $925
Bob Smith $3,450
Frank Jones $1,250
Frank Jones $950
Frank Jones $22,456

*Note that I took out the subtotals.

  1. Select Columns A & B.
  2. Go to Data->Pivot Table and Pivot Chart Report…
  3. Click “Next”, “Next”, “Layout”.
  4. Drag “Name” to the white area called “ROW”.
  5. Drag “Sales” to the area called “DATA”.
  6. Double-click “Count of Sales” and select “Sum” in the menu, click “OK”. Click “OK” again. Click “Finish”.

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.

  1. Select the whole layout
  2. Click on “Format as Table”
  3. Choose a design you like.
  4. That’s it…you can now sort on any column and everything to either side gets moved with it.

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.

Hey, that’s a good one! Won’t use it for this project, but I can think of a dozen other times I could use it.

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.