Pivot Table or Subtotals?

[Justinian]

I have a massive LADWP spreadsheet with over 25,000 rows. Each address has at least five or six rows of data entered and there are hundreds of addresses. I want to subtotal all this nonsense but when I do and collapse the data so there is only one row per unique address, I lose other column labels. For example:

Column A = Vendor Account
Column B = Account Type
Column C = Invoice Date
Column D = Invoice Amount

When I subtotal, I do a change at every address and sum under invoice amount. Once I collapse the data by clicking on the 2, I only see titles for address and amount. I lost the titles for the other columns. How can I fix this?

 

[sheetspread]

To fill the blank cells adjacent to the subtotals you can highlight, F5-special-blanks, type =, the reference of the first cell with the label, then ctrl-enter

 

[Smitty]

If you use a Pivot Table you won't lose your headers.

 

[Justinian]

I inserted a PT and lost my headers.

 

[Justinian]

What I would like to do is use subtotals but have each column header shown on the subtotal lines, not just at the top header. So when the subtotaled category show a line below the data in bold, that is where I want these other headers to appear.

 

[Smitty]

I inserted a PT and lost my headers.

Did you include those fields in the PT?

Did you use Tabular view?

 

[Justinian]

I tried including them but it kept adding the separate column headers within the same column. So instead of column A being vendor account, it was vendor account, account type, invoice date, and invoice amount.

What is tab view?

 

[Smitty]

Tabular view will fix that. Goto Design-->Report Layout-->Show in Tabular Form.

 

[Justinian]

I must be doing something wrong because it is still adding separate column headers to the same column. Where should I be placing the columns in the drag fields?

 

[Smitty]

Here's an example with Account Type/Vendor Account and Invoice Date in the Rows field and Invoice Amount in Values and the default Compact form:

Excel 2012
	A	B
3	Row Labels	Sum of Invoice Amount
4	Comm	218
5	123	218
6	1-Jan	81
7	1-Feb	40
8	1-Mar	61
9	1-Apr	36
10	Gov	217
11	456	217
12	1-Jan	94
13	1-Feb	47
14	1-Mar	45
15	1-Apr	31
16	NGO	179
17	789	179
18	1-Jan	62
19	1-Feb	3
20	1-Mar	88
21	1-Apr	26
22	Grand Total	614
Sheet4

And here's in Tabular form:

Excel 2012
	A	B	C	D
3	Account Type	Vendor Account	Invoice Date	Sum of Invoice Amount
4	Comm	123	1-Jan	81
5			1-Feb	40
6			1-Mar	61
7			1-Apr	36
8		123 Total		218
9	Comm Total			218
10	Gov	456	1-Jan	94
11			1-Feb	47
12			1-Mar	45
13			1-Apr	31
14		456 Total		217
15	Gov Total			217
16	NGO	789	1-Jan	62
17			1-Feb	3
18			1-Mar	88
19			1-Apr	26
20		789 Total		179
21	NGO Total			179
22	Grand Total			614
Sheet4