Mediocre Excel User Seeking to Improve on Bills Sheet

[Woejeber]

Hi all,

I would say I'm just barely past the beginner level of using Excel to its fullest potential and I have been slowly but surely improving over the years. My main focus for learning has been a sheet I've been using for my bills each month. I started knowing absolutely nothing about Excel back in 2013 and I don't feel like I've improved much beyond the basics. I have an example sheet mocked up using random numbers to keep my actual finances private.

Here is the link to the folder containing it. I'm currently at work and cannot download minisheet. Let me know if there's a better way to do this.

Excel Forum - Google Drive

drive.google.com

The purpose: I have 2 bank accounts: my bills and my debit card. Bills are all on autopay and after an allowance is deposited each month, the remainder of my check goes into my bills account. As interest rates are non-existent, it kind of doubles as a savings account. As the majority of the bills are consistent and any small changes can be made as things go, I can see what the potential balance of my account will be in the future, all the way out until I stop creating a new sheet for that next month. Currently I can see what my balance, and therefore savings, should be in Dec 2025.

I'm sure there are ways to improve this to be more efficient and/or neater. What would the hivemind that is this forum board suggest?

Thank you for any and all suggestions. Feel free to ask any questions needed. I will try to answer in a timely manner.

 

[cooleomter]

Hi all,

I would say I'm just barely past the beginner level of using Excel to its fullest potential and I have been slowly but surely improving over the years. My main focus for learning has been a sheet I've been using for my bills each month. I started knowing absolutely nothing about Excel back in 2013 and I don't feel like I've improved much beyond the basics. I have an example sheet mocked up using random numbers to keep my actual finances private.

Here is the link to the folder containing it. I'm currently at work and cannot download minisheet. Let me know if there's a better way to do this.

Excel Forum - Google Drive

drive.google.com

The purpose: I have 2 bank accounts: my bills and my debit card. Bills are all on autopay and after an allowance is deposited each month, the remainder of my check goes into my bills account. As interest rates are non-existent, it kind of doubles as a savings account. As the majority of the bills are consistent and any small changes can be made as things go, I can see what the potential balance of my account will be in the future, all the way out until I stop creating a new sheet for that next month. Currently I can see what my balance, and therefore savings, should be in Dec 2025.

I'm sure there are ways to improve this to be more efficient and/or neater. What would the hivemind that is this forum board suggest?

Thank you for any and all suggestions. Feel free to ask any questions needed. I will try to answer in a timely manner.

I would like to propose this Project:

Bills.xlsm
	A	B	C	D	E	F	G	H
1	Date	Month	Day	Year	Description	Category	Debit	Credit
2	1/1/2024	1	1	2024	Funds	Funds	$ 2,000.00
3	1/1/2024	1	1	2024	Funds	Funds	$ 2,150.00
4	1/2/2024	1	2	2024	Personal Loan	Expenses		$ 500.00
5	1/5/2024	1	5	2024	Wells Fargo	Expenses		$ 50.00
6	1/9/2024	1	9	2024	Car Loan	Expenses		$ 300.00
7	1/12/2024	1	12	2024	Netflix	Expenses		$ 20.00
8	1/13/2024	1	13	2024	Spotify	Expenses		$ 20.00
9	1/16/2024	1	16	2024	Chase	Expenses		$ 200.00
10	1/17/2024	1	17	2024	Hulu	Expenses		$ 20.00
11	1/17/2024	1	17	2024	Max	Expenses		$ 20.00
12	1/18/2024	1	18	2024	Rent	Expenses		$ 1,000.00
13	1/21/2024	1	21	2024	Car Insurance	Expenses		$ 125.00
14	1/27/2024	1	27	2024	Cellphone	Expenses		$ 100.00
15	1/28/2024	1	28	2024	Rent	Expenses		$ 100.00
16	1/28/2024	1	28	2024	Personal Loan	Funds		$ 100.00
17	1/28/2024	1	28	2024	Personal Loan	Funds		$ 100.00
18	1/28/2024	1	28	2024	Personal Loan	Expenses		$ 2,000.00
19
20
21
ACCDATA

Cell Formulas
Range	Formula
B2:B21	B2	=IF(A2="","",MONTH(A2))
C2:C21	C2	=IF(A2="","",DAY(A2))
D2:D21	D2	=IF(A2="","",YEAR(A2))

Cells with Data Validation
Cell	Allow	Criteria
E2:E1048576	List	=deslist
F2:F1048576	List	=Catlist

With summary sheet:

Bills.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1				Yearly Savings				Monthly Debt Payments:						Total Debt w/o Car
2				Choose Year	2024			Month	1					Choose Year	2024
3
4				Category	Sum of Debit	Sum of Credit		Description	Sum of Credit		Total Debt Payments:	$ 3,250.00		Description	Sum of Credit		Savings:	$ 2,950.00
5	This Monthly Result			Expenses		4455		Car Loan	300					Chase	200
6	Total Monthly Bills:	$4,455.00		Funds	4150	200		Chase	200					Personal Loan	2700
7	Total Debt w/o Car:	$2,950.00						Personal Loan	2700					Wells Fargo	50
8	Total Debt Payments:	$3,250.00		Savings:	$ (305.00)			Wells Fargo	50
9	Total Income:	$4,150.00
10	Total Needed Per Check:	$2,227.50
11	Add'l Funds Per Month:	-$305.00
12
13				Monthly Savings
14				Select your Months	1
15
16				Category	Sum of Debit	Sum of Credit
17				Expenses		4455
18				Funds	4150	200
19
20				Savings:	$ (305.00)
21
22
23
24
25
26				Monthly bills
27				Choose Year	2024
28				Total Debt w/o Car:
29				Row Labels	Sum of Credit
30				Expenses	4455
31
32				Total	$ 4,455.00
33
34
35
36
37				Monthly Income
38				Month	1
39
40				Category	Sum of Debit
41				Funds	4150
42
43
44				Savings:	$ 4,150.00
45
46
Summary

Cell Formulas
Range	Formula
L4	L4	=SUM(I5:I8)
R4	R4	=SUM(O5:O7)
E8	E8	=GETPIVOTDATA("Sum of Debit",$E$6,"Category","Funds")-GETPIVOTDATA("Sum of Credit",$F$5,"Category","Expenses")
B6	B6	=E32
B7	B7	=R4
B8	B8	=L4
B9	B9	=E44
B10	B10	=B6/2
B11	B11	=B9-B6
E20	E20	=GETPIVOTDATA("Sum of Debit",$D$16,"Category","Funds")-GETPIVOTDATA("Sum of Credit",$D$16,"Category","Expenses")
E32	E32	=GETPIVOTDATA("Credit",$D$29,"Category","Expenses")
E44	E44	=GETPIVOTDATA("Debit",$D$40,"Category","Funds")

sorry I have no google drive to send you the file.

 

[cooleomter]

Hi all,

I would say I'm just barely past the beginner level of using Excel to its fullest potential and I have been slowly but surely improving over the years. My main focus for learning has been a sheet I've been using for my bills each month. I started knowing absolutely nothing about Excel back in 2013 and I don't feel like I've improved much beyond the basics. I have an example sheet mocked up using random numbers to keep my actual finances private.

Here is the link to the folder containing it. I'm currently at work and cannot download minisheet. Let me know if there's a better way to do this.

Excel Forum - Google Drive

drive.google.com

The purpose: I have 2 bank accounts: my bills and my debit card. Bills are all on autopay and after an allowance is deposited each month, the remainder of my check goes into my bills account. As interest rates are non-existent, it kind of doubles as a savings account. As the majority of the bills are consistent and any small changes can be made as things go, I can see what the potential balance of my account will be in the future, all the way out until I stop creating a new sheet for that next month. Currently I can see what my balance, and therefore savings, should be in Dec 2025.

I'm sure there are ways to improve this to be more efficient and/or neater. What would the hivemind that is this forum board suggest?

Thank you for any and all suggestions. Feel free to ask any questions needed. I will try to answer in a timely manner.

This autosave whenever you input on accdata sheet