# Excel Question **URL:** \ **Category:** Factual Questions\ **Created:** [February 16, 2010, 7:52pm UTC](https://boards.straightdope.com/t/excel-question/529243 "2010-02-16T19:52:38Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [February 16, 2010, 7:52pm UTC](https://boards.straightdope.com/t/excel-question/529243/1 "2010-02-16T19:52:38Z")
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. ---
**Author:** ![minor7flat5](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/minor7flat5/32/258_2.png) [@minor7flat5](https://boards.straightdope.com/u/minor7flat5)\ **Post date:** [February 16, 2010, 7:56pm UTC](https://boards.straightdope.com/t/excel-question/529243/2 "2010-02-16T19:56:47Z")
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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [February 16, 2010, 8:02pm UTC](https://boards.straightdope.com/t/excel-question/529243/3 "2010-02-16T20:02:50Z")
> [@minor7flat5](#): > > 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 ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [February 16, 2010, 8:26pm UTC](https://boards.straightdope.com/t/excel-question/529243/4 "2010-02-16T20:26:49Z")
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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [February 16, 2010, 8:33pm UTC](https://boards.straightdope.com/t/excel-question/529243/5 "2010-02-16T20:33:02Z")
> [@jonesj2205](#): > > 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. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 16, 2010, 8:35pm UTC](https://boards.straightdope.com/t/excel-question/529243/6 "2010-02-16T20:35:59Z")
> [@Saintly\_Loser](#): > > 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 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”. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 16, 2010, 9:37pm UTC](https://boards.straightdope.com/t/excel-question/529243/7 "2010-02-16T21:37:46Z")
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. ---
**Author:** ![Whack-a-Mole](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/whack-a-mole/32/141_2.png) [@Whack-a-Mole](https://boards.straightdope.com/u/Whack-a-Mole)\ **Post date:** [February 16, 2010, 9:39pm UTC](https://boards.straightdope.com/t/excel-question/529243/8 "2010-02-16T21:39:21Z")
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. ---
**Author:** ![Spud](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/spud/32/347_2.png) [@Spud](https://boards.straightdope.com/u/Spud)\ **Post date:** [February 16, 2010, 11:14pm UTC](https://boards.straightdope.com/t/excel-question/529243/9 "2010-02-16T23:14:57Z")
> [@Munch](#): > > 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. 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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [February 18, 2010, 1:49am UTC](https://boards.straightdope.com/t/excel-question/529243/10 "2010-02-18T01:49:33Z")
> [@Munch](#): > > 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 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. ---
**Author:** ![Saintly\_Loser](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/saintly_loser/32/4045_2.png) [@Saintly\_Loser](https://boards.straightdope.com/u/Saintly_Loser)\ **Post date:** [February 18, 2010, 1:50am UTC](https://boards.straightdope.com/t/excel-question/529243/11 "2010-02-18T01:50:36Z")
> [@Whack-a-Mole](#): > > 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. 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. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [February 18, 2010, 2:13am UTC](https://boards.straightdope.com/t/excel-question/529243/12 "2010-02-18T02:13:21Z")
> [@Saintly\_Loser](#): > > I didn’t build this spreadsheet, so I’m stuck with it. l. 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? ```auto 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. ---
**Author:** ![Ruminator](https://avatars.discourse-cdn.com/v4/letter/r/b9bd4f/32.png) [@Ruminator](https://boards.straightdope.com/u/Ruminator)\ **Post date:** [February 18, 2010, 2:30am UTC](https://boards.straightdope.com/t/excel-question/529243/13 "2010-02-18T02:30:30Z")
> [@Saintly\_Loser](#): > > And this gives me a chance to learn more about pivot tables. I’m barely familiar with them. 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.