| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Categorize Income and Expenses?
I have a checking account register, columns A, B, C, where DEP = deposit,
numbers are check numbers, and positive and negative amounts as shown below. A B C CHK# Category Amount Dep Festival $30.05 1023 Paint -$14.00 1024 Chalk -$33.00 1025 Paint -$12.00 Dep Member $100.00 Dep Festival $44.00 I would like a report in another worksheet or just a reformat of the data into 2 main categories of Income and Expenses and within each to sum (add) each category . For example, Income Festival $77.05 Member $100.00 Expenses Paint $26.00 Chalk $33.00 How can I do that? Thank you, |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Categorize Income and Expenses?
Add column D with formulae along the lines of:
=if(a3="Dep","Income","Expense") and a heading of "Type" (or any heading other than "CHK#","Category", or "Amount"). Then select the entire range, including the row with headings and execute a Data/Pivot Table command sequence. It seems likely that you haven't used pivot tables before, so you may want to read up on them before doing this. They'll be very helpful for you. Best of luck. Jim "gee14" wrote: I have a checking account register, columns A, B, C, where DEP = deposit, numbers are check numbers, and positive and negative amounts as shown below. A B C CHK# Category Amount Dep Festival $30.05 1023 Paint -$14.00 1024 Chalk -$33.00 1025 Paint -$12.00 Dep Member $100.00 Dep Festival $44.00 I would like a report in another worksheet or just a reformat of the data into 2 main categories of Income and Expenses and within each to sum (add) each category . For example, Income Festival $77.05 Member $100.00 Expenses Paint $26.00 Chalk $33.00 How can I do that? Thank you, |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Categorize Income and Expenses?
Thank you, that worked pretty well. I have some beginning PivotTable
knowledge, so I did that part, what I didn't know was the intermediate step of creating the new column to get the INCOME/EXPENSEd separated. The only thing is that Expenses shows first alphabetically in the PivotTable and then Income. Is there anyway to move Income to the top? Thank you "Jim Skrydlak" wrote: Add column D with formulae along the lines of: =if(a3="Dep","Income","Expense") and a heading of "Type" (or any heading other than "CHK#","Category", or "Amount"). Then select the entire range, including the row with headings and execute a Data/Pivot Table command sequence. It seems likely that you haven't used pivot tables before, so you may want to read up on them before doing this. They'll be very helpful for you. Best of luck. Jim "gee14" wrote: I have a checking account register, columns A, B, C, where DEP = deposit, numbers are check numbers, and positive and negative amounts as shown below. A B C CHK# Category Amount Dep Festival $30.05 1023 Paint -$14.00 1024 Chalk -$33.00 1025 Paint -$12.00 Dep Member $100.00 Dep Festival $44.00 I would like a report in another worksheet or just a reformat of the data into 2 main categories of Income and Expenses and within each to sum (add) each category . For example, Income Festival $77.05 Member $100.00 Expenses Paint $26.00 Chalk $33.00 How can I do that? Thank you, |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Categorize Income and Expenses?
Right-click on the item that reflects both ("Type" in my example, I think),
then click on "Field Settings", then "Advanced". Select "Descending" under "Autosort Options" then click "OK" a couple of times. Glad that you already knew about pivot tables, and glad I could help. Jim "gee14" wrote: Thank you, that worked pretty well. I have some beginning PivotTable knowledge, so I did that part, what I didn't know was the intermediate step of creating the new column to get the INCOME/EXPENSEd separated. The only thing is that Expenses shows first alphabetically in the PivotTable and then Income. Is there anyway to move Income to the top? Thank you "Jim Skrydlak" wrote: Add column D with formulae along the lines of: =if(a3="Dep","Income","Expense") and a heading of "Type" (or any heading other than "CHK#","Category", or "Amount"). Then select the entire range, including the row with headings and execute a Data/Pivot Table command sequence. It seems likely that you haven't used pivot tables before, so you may want to read up on them before doing this. They'll be very helpful for you. Best of luck. Jim "gee14" wrote: I have a checking account register, columns A, B, C, where DEP = deposit, numbers are check numbers, and positive and negative amounts as shown below. A B C CHK# Category Amount Dep Festival $30.05 1023 Paint -$14.00 1024 Chalk -$33.00 1025 Paint -$12.00 Dep Member $100.00 Dep Festival $44.00 I would like a report in another worksheet or just a reformat of the data into 2 main categories of Income and Expenses and within each to sum (add) each category . For example, Income Festival $77.05 Member $100.00 Expenses Paint $26.00 Chalk $33.00 How can I do that? Thank you, |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Categorize Income and Expenses?
Thank you, just perfect!
"Jim Skrydlak" wrote: Right-click on the item that reflects both ("Type" in my example, I think), then click on "Field Settings", then "Advanced". Select "Descending" under "Autosort Options" then click "OK" a couple of times. Glad that you already knew about pivot tables, and glad I could help. Jim "gee14" wrote: Thank you, that worked pretty well. I have some beginning PivotTable knowledge, so I did that part, what I didn't know was the intermediate step of creating the new column to get the INCOME/EXPENSEd separated. The only thing is that Expenses shows first alphabetically in the PivotTable and then Income. Is there anyway to move Income to the top? Thank you "Jim Skrydlak" wrote: Add column D with formulae along the lines of: =if(a3="Dep","Income","Expense") and a heading of "Type" (or any heading other than "CHK#","Category", or "Amount"). Then select the entire range, including the row with headings and execute a Data/Pivot Table command sequence. It seems likely that you haven't used pivot tables before, so you may want to read up on them before doing this. They'll be very helpful for you. Best of luck. Jim "gee14" wrote: I have a checking account register, columns A, B, C, where DEP = deposit, numbers are check numbers, and positive and negative amounts as shown below. A B C CHK# Category Amount Dep Festival $30.05 1023 Paint -$14.00 1024 Chalk -$33.00 1025 Paint -$12.00 Dep Member $100.00 Dep Festival $44.00 I would like a report in another worksheet or just a reformat of the data into 2 main categories of Income and Expenses and within each to sum (add) each category . For example, Income Festival $77.05 Member $100.00 Expenses Paint $26.00 Chalk $33.00 How can I do that? Thank you, |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how can I track a rental properties expenses and income | Charts and Charting in Excel | |||
| how do i setup a table of daily income and expenses | Excel Worksheet Functions | |||
| how do i download templates for income, expenses, and misc. | Charts and Charting in Excel | |||
| template for monthly income/expenses corresponding to IRS Schedule | Excel Discussion (Misc queries) | |||
| church expenses, income work sheets | Excel Discussion (Misc queries) | |||
Linear Mode
