Excel Question 1

[cranebill]

I have a spreadsheet with approx 20k records. I have one column that is a region and the other columns are basically dollar amounts for different aspects of incoming and outgoing income. How can I do an sheet to sum up the different columns per region instead of manually doing this?

Thanks

 

[AnotherHiggins]

Have a look at Pivot Tables.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ItIsHardToProgram]

How about filters?

Excel is not the best program for your issue, you should be using access. We have the same problem.

HTH,

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[AnotherHiggins]

->Excel is not the best program for your issue

Dude. Are you aware of Pivot Tables? Based on the description of the table in the OP, I'd say that cranebill should be able to produce an easy-to-read, interactive summary in about 30 seconds.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[ItIsHardToProgram]

If hes processing a data from somewhere to somewhere it is not the best program.

that is what I meant.

If he only use that sheet for all his information/reports, it can be convenient.

"Knowing that you know is the greatest sign of stupidity, knowing that you are ignorant is the best proof of intelligence.

 

[cranebill]

Ok looked at pivot tables:

Played with it a bit however the data format is wrong for what we need. It appears as follows:

Region
Detroit Revenue 1234
Expenses 4567
Chicago Revenue 1234
Expenses 4567
Total 76798987

We need it to look like this:
Region Revenue Expenses
Detroit 1234 4567
Chicago 1234 4567
Total 3456 798987

Can this be done with the pivot tables?

I keep dragging and dropping columns to page, row, column, and data. Nothing seems to be working.

 

[AnotherHiggins]

Click on the the header in column B - it probably just says "Data". Drag it onto the word that says "Total".



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

er, that should read: "the cell that says 'Total'"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[cranebill]

Worked like a charm, thanks

Star for you

 

[AnotherHiggins]

Glad to help.

Have a great weekend


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.