What's new
Fantasy Football - Footballguys Forums

Welcome to Our Forums. Once you've registered and logged in, you're primed to talk football, among other topics, with the sharpest and most experienced fantasy players on the internet.

Excel Help - Budgeting Spreadsheet (1 Viewer)

wilked

wilked

Footballguy
First, thanks for reading and offering to help

Basic idea is I will be exporting my transactions from my bank account.  Example will be:

A             B                C

1/10/18   Target      -45.45      

After importing into Excel I will be assigning a category to each transaction in D.  These will be pulled from a list via drop-down, ie standardized categories.  In the above example you might call it "Household Items"

I also added a 5th column (E) with the formula "=month(A1)", so in this case it would return 1.

Here's where I need help.  I would like a separate "Summary" worksheet which has Categories in the first column, and months across the top.  Something like this:

Categories                 Jan             Feb            Mar

Household Items       45.45

Gas

Restaurants

I would like this to auto-populate ideally.  I am also macro-savvy, so if needed I can build a macro but would love auto-population.

Thoughts?

I know there are services (mint, YNAB, etc) but I want to do this manually.  

Thanks

 
A few random thoughts...

If you use MONTH(), it's going to return the numerical value.  So if you're going to use "JAN" as the heading in your summary, I'd suggest changing it to =TEXT(A2,"mmm").

You're going to need YEAR() also if you use this for more than just 2018.  Otherwise, you're summary will start to compound the monthly totals.

You can use SUMIFS to calc each item for each category per month.  =SUMIFS(Details!C:C,Details!D:D,$A2,Details!E:E,B$1,Details!F:F,$N$1)

N1 is where I'd put the year in your Summary sheet where it'll lookup the year in col F of your Details sheet.

Another option is to just use a Pivot Table instead, but it would be in row format.

 
I threw together a little something that seems to work...

Cell B2: =SUMIFS(Trans!$C:$C,Trans!$D:$D,"Household Items",Trans!$E:$E,"1")

"Trans" is my sheet of transactions as you laid out above

B1 contains my "JAN" column header

A2 contains my "Household items" row header

So you'd just need to adjust each formula for (a) the category name in the middle of the formula and (b) the month at the end of the formula

Cell B3: =SUMIFS(Trans!$C:$C,Trans!$D:$D,"Gas",Trans!$E:$E,"1")

Cell C2: =SUMIFS(Trans!$C:$C,Trans!$D:$D,"Household Items",Trans!$E:$E,"2")

. . .

 
Interesting...am familair with SUMIF but not with its cousin SUMIFS.  Let me give it a shot, thanks

 
This is what pivot tables do with ease.

Select all your data, Insert > Pivot table

Month goes in the Columns

Expense Category goes in Rows

Amount goes in Values

When you add more rows, you'll just update your data source to include the new rows. It's incredibly simple.

 
Nathan R. Jessep said:
I threw together a little something that seems to work...

Cell B2: =SUMIFS(Trans!$C:$C,Trans!$D:$D,"Household Items",Trans!$E:$E,"1")

"Trans" is my sheet of transactions as you laid out above

B1 contains my "JAN" column header

A2 contains my "Household items" row header

So you'd just need to adjust each formula for (a) the category name in the middle of the formula and (b) the month at the end of the formula

Cell B3: =SUMIFS(Trans!$C:$C,Trans!$D:$D,"Gas",Trans!$E:$E,"1")

Cell C2: =SUMIFS(Trans!$C:$C,Trans!$D:$D,"Household Items",Trans!$E:$E,"2")

. . .
Click to expand...
You can replace these with $A2 and it'll just pick up the names from there when you auto-fill.  That way you avoid having to change each name by hand.

 
Ignoramus said:
This is what pivot tables do with ease.

Select all your data, Insert > Pivot table

Month goes in the Columns

Expense Category goes in Rows

Amount goes in Values

When you add more rows, you'll just update your data source to include the new rows. It's incredibly simple.
Click to expand...
This - you don't even need your column E, as the Pivot Table can automatically group the dates by month. All you do is add in your category in your source data, then click to the pivot table, right click and select refresh.

And if you set up your data as a table, and set that as the data source for the pivot table, no need to update your data source, it too will automatically expand as you add in new data.

 
Ned said:
:goodposting:

Pet peeve of mine when folks don't do this.
Click to expand...
I did not know about this. In the past when I've set data up as table I haven't been happy with some of the formatting and restrictions I've run into. Nothing major, I just feel like it has slowed me down with some of the automatic stuff that happens that I might not be expecting.

I guess I need to be doing this more. 

 
Recently learned the set data as table. Awesome tool. 

Regarding SUMIFS vs pivot, is there any benefit to the SUMIFS. When reading OP I thought pivot right away. Maybe there's a reason not to.

 
odin33 said:
Recently learned the set data as table. Awesome tool. 

Regarding SUMIFS vs pivot, is there any benefit to the SUMIFS. When reading OP I thought pivot right away. Maybe there's a reason not to.
Click to expand...
I wondered the same after reading you guys' posts. I didn't think of Pivot Tables initially because I don't use them a lot in my day to day work. 

Found this: http://blog.softartisans.com/2013/07/31/sumif-vs-pivot-tables/

IN CONCLUSION

PivotTables are generally faster because they are automatic. Some of the price you pay for being automatic is that layouts and charts aren’t easily customized. Also, if you’ve never worked with PivotTables before, they can be daunting. Using multiple areas as a data source is almost impossible, even if a wizard is available (but hidden).

Formulas are easy to use, so it may save time if you’re comfortable with them, but you will need to create everything from scratch including the formatting. Formulas may also be the only realistic option for complicated data sets, especially if the data is on separate worksheets.
Click to expand...

 
Old nerds use array formulas without these fancy SUMIFS, Pivot Tables and Data Tables.  More easily scale-able. 

 
Ned said:
:goodposting:

Pet peeve of mine when folks don't do this.
Click to expand...
I have tons of excel pet peeves, but this ain't one.  Here's a few:

- Links to external workbook.  puh-lease

- Merging cells to center text across a bunch of columns.

- those +/- expand/collapse instead of hiding rows

- not freezing the header rows when there are tons of rows 

 
Brony said:
I have tons of excel pet peeves, but this ain't one.  Here's a few:

- Links to external workbook.  puh-lease

- Merging cells to center text across a bunch of columns.

- those +/- expand/collapse instead of hiding rows

- not freezing the header rows when there are tons of rows 
Click to expand...
I hate, hate, HATE merged cells when in trying to copy/paste data! Bane of my existence in my previous line of work. 

 
on but off topic......so my 'wife' hired a woman with little excel knowledge.  what would be your go to website to learn excel basics and more?

 
MikeIke said:
I hate, hate, HATE merged cells when in trying to copy/paste data! Bane of my existence in my previous line of work. 
Click to expand...
Every file my warehouse sends, six merged rows on top, two merged on the bottom. Both have to be deleted before I can do anything useful with this data.

Don't link to external sheets, but especially don't link to sheets saved on your desktop. Nothing better than getting a file and #REF in every cell.

 
Brony said:
I have tons of excel pet peeves, but this ain't one.  Here's a few:

- Links to external workbook.  puh-lease

- Merging cells to center text across a bunch of columns.

- those +/- expand/collapse instead of hiding rows

- not freezing the header rows when there are tons of rows 
Click to expand...
Not using ALT+Enter within a cell but instead filling two cells in different rows

Entering rows and rows of data but with blank rows in between so that you can't sort and filter

 
If you aren't allowed (by the FFA) to use the Merging across columns, how do you do column headers? Or is it kinda sorta OK if it is only at the top?

 
Mr. Ected said:
If you aren't allowed (by the FFA) to use the Merging across columns, how do you do column headers? Or is it kinda sorta OK if it is only at the top?
Click to expand...
On the ribbon on the top, click on Alignment bottom right corner (Alignment Setting).  Under dialog box for Format Cells go to Alignment tab and choose "Center Across Selection" under Horizontal Alignment.  

Or, do Ctrl-1 with keyboard and it brings up this menu too. 

At first blush, clicking the Merge and Center on the ribbon seems easier/faster.  But, to be really fast with Excel, you need to use mostly keyboard and mouse only when necessary.  For instance, Ctrl-SPACE selects an entire column and formatting can be done quickly on that column. The merged cells throws this system off.   If Excel replaced Merge & Center button with Center Across Selection button, it would make life better for all, but here we are. 

 
Brony said:
Mr. Ected said:
If you aren't allowed (by the FFA) to use the Merging across columns, how do you do column headers? Or is it kinda sorta OK if it is only at the top?
Click to expand...
On the ribbon on the top, click on Alignment bottom right corner (Alignment Setting).  Under dialog box for Format Cells go to Alignment tab and choose "Center Across Selection" under Horizontal Alignment.  

Or, do Ctrl-1 with keyboard and it brings up this menu too. 

At first blush, clicking the Merge and Center on the ribbon seems easier/faster.  But, to be really fast with Excel, you need to use mostly keyboard and mouse only when necessary.  For instance, Ctrl-SPACE selects an entire column and formatting can be done quickly on that column. The merged cells throws this system off.   If Excel replaced Merge & Center button with Center Across Selection button, it would make life better for all, but here we are. 
Click to expand...
Very cool. I guess you could make a macro and then a button for a ribbon or a shortcut to make the process just as easy.

 
Last edited by a moderator:
Brony said:
I have tons of excel pet peeves, but this ain't one.  Here's a few:

- Links to external workbook.  puh-lease

- Merging cells to center text across a bunch of columns.

- those +/- expand/collapse instead of hiding rows

- not freezing the header rows when there are tons of rows 
Click to expand...


MikeIke said:
I hate, hate, HATE merged cells when in trying to copy/paste data! Bane of my existence in my previous line of work. 
Click to expand...
Number 1 with a bullet

 
Mr. Ected said:
Very cool. I guess you could make a macro and then a button for a ribbon or a shortcut to make the process just as easy.
Click to expand...
This is what I have done on my work computer.

Here is the code just in case you are interested. It is in a "PERSONAL.xlsb" file that opens automatically in the background when Excel is launched so that the macro is available. Pretty sure I took all the directions to do this from peltiertech.com or chandoo.org many years ago and have just stuck with it:

Sub CenterAcrossSelection()
With Selection
If .HorizontalAlignment = xlCenterAcrossSelection Then
.HorizontalAlignment = xlGeneral
Else
.HorizontalAlignment = xlCenterAcrossSelection
.VerticalAlignment = xlCenter
End If
End With
End Sub

 
Thanks @acarey50. That is slick, and I will be copying it for my home use (I am a Mr. Mom, so I just play around with this stuff)

Ignoramus said:
This is what pivot tables do with ease.

Select all your data, Insert > Pivot table

Month goes in the Columns

Expense Category goes in Rows

Amount goes in Values

When you add more rows, you'll just update your data source to include the new rows. It's incredibly simple.
Click to expand...
I get emails from a site called ExcelJet, that does training for Excel, and I happened to get one today for Pivot Tables!

Here is a video that describes how to do a pivot table, and compares it to a report that is made using SUMIFS. They compare the numbers of clicks and functions that are used in the various reports. There is also an article about it.

 
You must log in or register to reply here.

Similar threads

C
Microsoft Excel Help!
Replies
5
Views
394
Captain Cranks
Captain Cranks
C
Microsoft Excel - Conditional Highlighting Formula
Replies
9
Views
388
slackjawedyokel
slackjawedyokel
hotdogcollars
Excel Guru Help - I will pay you...
Replies
13
Views
352
hotdogcollars
hotdogcollars

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Top