# Yet another Excel thread - Subtotals **URL:** \ **Category:** Factual Questions\ **Created:** [February 17, 2014, 3:02am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517 "2014-02-17T03:02:38Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Daylate](https://avatars.discourse-cdn.com/v4/letter/d/9dc877/32.png) [@Daylate](https://boards.straightdope.com/u/Daylate)\ **Post date:** [February 17, 2014, 3:02am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/1 "2014-02-17T03:02:38Z")
Using the subtotal function in Excel is rather straightforward and easy. However, the default formatting after using the function isn’t what I want. For example, let’s say that column A contains a list of dates, companies and their income for that date - something like this: (The list has previously been sorted so that the companies are grouped together.) Col A\_\_\_\_\_\_Col B\_\_\_\_\_\_\_\_\_\_Col C 1/2/14\_\_\_Company A\_\_\_\_\_\_532.12 1/6/14\_\_\_Company A\_\_\_\_\_\_ 31.58 1/10/14\_\_Company B\_\_\_\_\_\_123.00 1/15/14\_\_Company C\_\_\_\_\_\_ 10.52 1/20/22\_\_Company C\_\_\_\_\_\_100.31 (Sorry for using underlines - couldn’t preserve the columnar formatting by just using spaces.) Now if the Subtotal routine has been run, calculating the subtotals for each company, Excel will correctly calculate the total amount of income for each company and insert this into a line after each group of companies. For Company A this list will have the statement “Total Company A”, and then in column C, the total amount - in this case, 563.70. This will continue for the entire list, showing the total amount each company earned and inserting it after each group of the same company. My problem with this is that, to me, having these subtotals placed in the same column as the individual incomes (C, in this case), results in a big list with all the numbers, both individual and subtotals, right under each other, and is somewhat confusing to read and/or review. Like this: Col A\_\_\_\_\_\_Col B\_\_\_\_\_\_\_\_\_\_Col C 1/2/14\_\_\_Company A\_\_\_\_\_\_532.12 1/6/14\_\_\_Company A\_\_\_\_\_\_ 31.58 **Total Company A\_\_\_\_563.70 1/10/14\_\_Company B** 123.00 **Total Company B\_\_\_\_123.00 1/15/14\_\_Company C** 10.52 1/20/22\_\_Company C\_\_\_\_\_\_ 100.31 \_\_\_\_\_\_Total Company C\_\_\_\_110.83 As you can see, this format is not clear or easily read What I want Excel to do is to not put the subtotals into column C, but instead leave the cell in column C blank, and place the subtotal instead into Column D. So the result would look like this: Col A\_\_\_\_\_\_Col B\_\_\_\_\_\_\_\_\_\_Col C\_\_\_\_\_\_Col D 1/2/14\_\_\_Company A\_\_\_\_\_\_532.12 1/6/14\_\_\_Company A\_\_\_\_\_\_ 31.58 **Total Company A\_\_\_\_\_\_\_\_\_\_\_\_\_\_563.70 1/10/14\_\_Company B** 123.00 **Total Company B\_\_\_\_\_\_\_\_\_\_\_\_\_\_123.00 1/15/14\_\_Company C** 10.52 1/20/22\_\_Company C\_\_\_\_\_\_ 100.31 \_\_\_\_\_\_Total Company C\_\_\_\_\_\_\_\_\_\_\_\_\_\_110.83 To me, this format is much clearer than the previous one. So far, nothing I’ve tried (including reading the help files), will generate this formatting. If any Excel experts out there can help, I’d be most appreciative. ---
**Author:** ![Daylate](https://avatars.discourse-cdn.com/v4/letter/d/9dc877/32.png) [@Daylate](https://boards.straightdope.com/u/Daylate)\ **Post date:** [February 17, 2014, 3:21am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/2 "2014-02-17T03:21:13Z")
Missed the edit window. The second sentence on the first paragraph should have been: “For example, let’s say that column A contains a list of dates, Column B the companies and Column C, their income for that date - something like this:” ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [February 17, 2014, 3:30am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/3 "2014-02-17T03:30:08Z")
You can do something close with a PivotTable. Example [here](https://skydrive.live.com/redir?page=view&resid=9867ED46F373003E!2820&authkey=!AGreZ8UFhobUKnk). The subtotals live in their own columns, but that same column contains redundant individual values… so close, but not exactly what you want. ---
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [February 17, 2014, 3:36am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/4 "2014-02-17T03:36:01Z")
The subtotal function takes a reference, so you can put this in D3: =subtotal(9,c1:c2) unless I’m missing something obvious about what you are attempting. ---
**Author:** ![Reply](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/reply/32/15952_2.png) [@Reply](https://boards.straightdope.com/u/Reply)\ **Post date:** [February 17, 2014, 3:40am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/5 "2014-02-17T03:40:00Z")
> [@Folacin](#): > > The subtotal function takes a reference, so you can put this in D3: > > =subtotal(9,c1:c2) > > unless I’m missing something obvious about what you are attempting. I don’t think the subtotal function (as opposed to the wizard/command) can group results by company name. Using that would require a lot of manual definitions of ranges, such as subtotal(c1:c4) for company A, c5:c19 for company B, etc. ---
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [February 17, 2014, 3:51am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/6 "2014-02-17T03:51:30Z")
Never mind the above - I guess you’re working with Excel 2010, which I’m not familiar with. Carry on without me. ---
**Author:** ![Daylate](https://avatars.discourse-cdn.com/v4/letter/d/9dc877/32.png) [@Daylate](https://boards.straightdope.com/u/Daylate)\ **Post date:** [February 17, 2014, 5:41am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/7 "2014-02-17T05:41:16Z")
Forgot to mention the I still use Excel 2003. Please don’t tell me to upgrade. ---
**Author:** ![Daylate](https://avatars.discourse-cdn.com/v4/letter/d/9dc877/32.png) [@Daylate](https://boards.straightdope.com/u/Daylate)\ **Post date:** [February 17, 2014, 5:44am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/8 "2014-02-17T05:44:26Z")
That pivot table might be OK - the output was much more readable. Now I’ve got to read up on pivot tables, not having used one before. Might learn something useful! Thanks ---
**Author:** ![keno](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@keno](https://boards.straightdope.com/u/keno)\ **Post date:** [February 17, 2014, 4:52pm UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/9 "2014-02-17T16:52:06Z")
Filter so only subtotal rows are showing. In the first subtotal row, format column c cell to text color is white. In column d enter formula “=offset(xy,0,-1)” where xy refers to the cell the formula is in. Copy those two cells Select only visible cells (I think its alt-😉 Then paste the tow copied cells ---
**Author:** ![jonesj2205](https://avatars.discourse-cdn.com/v4/letter/j/ecc23a/32.png) [@jonesj2205](https://boards.straightdope.com/u/jonesj2205)\ **Post date:** [February 18, 2014, 3:54am UTC](https://boards.straightdope.com/t/yet-another-excel-thread-subtotals/681517/10 "2014-02-18T03:54:59Z")
Not sure if this helps you at all, but do you know on the left side of the table there is a 1, 2, 3. If you click the 2 you get the subtotals without the individual values. Maybe that’s all you need, maybe you can copy them and paste (special, values) 1 column to the right or wherever you want it.