Create an Expense Tracker in Excel in 14 Minutes
ฝัง
- เผยแพร่เมื่อ 3 ก.ค. 2024
- Please note that I've updated the site since releasing this video. You can find the expense tracker here:
rockstarexcel.com/wp-content/...
For instructions on updating this for a new year, see hear: rockstarexcel.com/how-to-upda...
You can also get a free list of essential Excel skills by signing up for our newsletter here:
rockstarexcel.com/newsletter/
To learn how to easily make spreadsheets like this on your own, check out: rockstarexcel.com/excel-made-...
For more tips and tricks on Excel: rockstarexcel.com
I really appreciate your free expense tracker. It has made life alot easier. I used to use a notebook to track all my expenses but this makes it easier. I just enter in each expense as soon as its made. I also saved the file in my icloud folder to i can update my file from my phone. AMAZING!!! Thanks alot.
Glad you found it helpful!
@@RockstarExcel Hey question for you. Sometimes a transaction even if it has a date it gets categorized as undated. I wonder what's causing that. and I can't figure it out for the life of me.
thank you bro. was looking for something like this days now. came across your channel. luv the internet
Loved it! Really saving lives out here
omg! this is exactly what i'm looking for. thank you so much!
Great tracker! I might try to add a graph when I get enough data. Thank you for sharing!!!
Thank you. I have been looking for something like this. New subscriber.
This was very helpful - thank you!
Awesome! Such a useful video. By using the same formula I was able to add the income on this tracker in summary section to see if I am in complete debt or not for each month:) Thank you!
Glad I could help!
That's a great idea but how did you do it? I would like to add it to my worksheet if you could share.
Hi Steve, Very helpful session. This was i looking for
Thanks man for the useful content, this video in particular
Thank you! Awesome video. 🙂
😀👍🏼 nice work really helped me.
Thanks a lot for this!!
Thanks so much
Amazing 👌
sumif/ifs difficulty level professional. Good tutorial though, thank you for taking the time to do this.
Thank you!
Thank you
Hopefully you’ll see this, I have my spreadsheet functional, exactly like yours, however when I try to do the formula to check for example (making sure everything in July adds up, or the first sumifs check you do in the video after finishing your spread) I keep getting #spill when I do it. Not I only have expenses in July at this time as I just started with my first paycheck at my new job. But I can’t figure out why it’s getting multiple values ?
Hi Steve, thank you for this. I followed it diligently and it worked (of course). Following the comments below, I'm assuming an income tracker would work the same way, but how would I add it to my expense tracker to see my savings each month?
doesnt it bother you these creators rarely do follow up with their content?. its very frustrating.
Thank you for making the awesome worksheet!!! I'm using it for my game expenses, is there a way to add a profit/sell to spread sheet to subtract from expense total? Any help would be greatly appreciated.
I know this is old, but was there an updated version that builds upon this to include income, investments and current savings?
When will your beginner course be opening back up? I definitely want to sign up for it. I just discovered your channel. So disappointed I can't sign up now. I signed up for the mailing list.
This is very very close to exactly what I have been looking for! How hard would it be to add a running balance to the expenses page?
Thanks
Dude your a fucking legend giving this out for free!
How do I change the dates on this so I can use it for previous or next year tracking?
Very good
Hi Steve, Thanks for this awesome video, very educational!. I have very basic knowledge about excel and I was hoping if you could help me create this exact tracker but, for a 3-year view. I'm backtracking all these three-year data and it'll be so nice to have your guidance. Thanks, Bern
Still no reply from Steve? I am looking for the same answer.
follow his tutorial, very easy if you follow what he is trying to do, the sumifs() is very powerful; I would use If(xxx=true,sum(AA:xx) whatever but his solution using sumifs is better because you can stack range and criteria as much as you want for better data control and filtering. MS Excel is great.
Could you explain the dollar signs please
I understand it locks rows and Columns in places but I did not understand it in this example other than that thank you so much for this video you have helped me a lot
Hi,
The dollar signs turn it into an absolute reference.
You can find my detailed explanation on absolute and relative references here: th-cam.com/video/PrJF5cYKZhk/w-d-xo.html
Let me know if you have any additional questions, and thanks for watching the video!
How did you paste those categories
Hi! Firstly thanks so much for this video. It was a big help for me. However, when I enter the SUMIFS formula (exactly how you have it) the first row/category does not calculate. It puts zeroes all across but it works for all the other categories. I am using google sheets so I don't know if that plays a role or if I am just making an error
Hi Brianna,
It's hard for me to tell what the issue is without seeing the spreadsheet. If you send me a link to the Google Sheet at rockstarexcel at gmail.com, I can take a quick look at it.
Please note that I've updated the site since releasing this video. You can find the expense tracker here:
rockstarexcel.com/wp-content/uploads/Rockstar-Excel-Expense-Tracker-Free-Giveaway-V4.xlsx
You can also get a free list of essential Excel skills by signing up for our newsletter here:
rockstarexcel.com/newsletter/
The link doesn't work for me. I followed all your formulas but can't get the categories to populate on the Summary page. I have double-checked my formulas against yours but no luck. Any idea why?
I didn't use your dummy data but used my own data from 2022.
@@bcboy123 Hi Bruce,
If you send your spreadsheet to rockstarexcel at gmail.com, I can take a look at it and see what the issue is.
@@RockstarExcel Well, thank you Steve for taking a look at my spreadsheet and finding the error. It's amazing how a period or comma can change the outcome. Although you corrected my sheet I will also go back and make corrections on the original. You truly are a rock star, Steve!
where did u get the dummy data,can add link?
Hey, this is awesome! I’m having a hard time making my second spread sheet work with every category. For example, the Summary Sheet will accurately categorize all my info from the Expenses sheet, except for my “restaurant” category. Can you tell me what I did wrong?
Hi,
My best guess at this would be that there's a difference in what the categories are called between your Summary page and your Expenses page. The categories need to be EXACTLY the same for the spreadsheet to work. Perhaps you've called one "restaurant" and the other "restaurants" or something like that. Or maybe one has an extra space at the end.
My suggestion would be to copy the category name from the Expenses tab, and paste it onto the Summary tab.
Let me know if that doesn't fix it and I can see if there's some other issue.
Thanks Steve, This video was amazing. I would like to know whether it’s possible to insert more years and if it is possible, how could I insert more years to this same excel sheet. Thanks Sarah.
Hi Sarah,
It's easy to add more years. (Note these instructions are for the downloadable version. If you built a version yourself, the exact rows and columns may be different.)
1. In the Summary tab, highlight rows 6 and 8, then right-click and select "Unhide" to unhide row 7.
2. Highlight 12 columns, starting with the column that contains the Dec-21 summary data. (Columns M - X in the downloadable sheet.) Then click the Insert button. There should now be 12 blank columns between Nov-2021 and Dec-2021.
3. Highlight the four cells containing 10-2021, 11-2021, Oct-2021, and Nov-2021. (L7, M7, L8, and M8)
4. Find the small green box in the lower-right corner of what you've highlighted. This is called the Fill-Handle. Click and drag this to the right 13 columns, all the way to the Dec-2021 column. (Overwrite the Dec-2021 column.) This should fill in the months for Dec-2021 through Dec-2022 in columns M through Y.
5. Highlight a box going from the formulas in the Nov-2021 column to the formulas in the Dec-2022 column. (L10 - Y34) Hit Ctrl-R (or Command-R on a Mac) to fill the formulas to the right. You can then rehide Row 7.
That should do it. Let me know if you have any difficulties.
BTW, you should check out my Excel Made Easy class, which teaches you how to easily build sheets like this on your own. You can learn more about it here. Note that the registration deadline is tomorrow.
rockstarexcel.com/excel-made-easy/
Hi Steve! Is there any way you can make one just like this but weekly expenses rather than monthly? I would buy it off you if needed!
Beginners left the chat immediately. It would have been better if you have explained it briefly.
this just seems more complicated than need to be!!! OMG!!!
I think because he goes to dang fast
It is. Steve freeman does not know how to teach. More so a person that shows off his skills rather then helping
This is so basic… just pay attention to the spreadsheet and to what he’s saying. You will be just fine.
Pls how do I make this tracker for more than a year?
can you share the file?
Hello Sir, is that possible if you can provide the raw data file. So I can speed up the process. I am struggling with time to study a lot subjects preparing for a job test. Thank you.
my sumifs formula @8:07 comes out to 0.. any insight on why this may be?
I watched the video and wanted to be able to make a few changes to the spreadhseet after downloading. I wanted to change the year from 2021 to 2022 and also be able to make the columns wider for some entries. It is password protected so I am unable to make changes. Please advise Thank you
Hi! Thanks for reaching out. The password to unlock the spreadsheet is RockstarExcel.
You can also download a non-password protected version here:
rockstarexcel.com/wp-content/uploads/Rockstar-Excel-Expense-Tracker-Free-Giveaway-V4.xlsx
Tried downloading the free copy of the worksheet- signed up for Rockstar - page says to "Click here" to get download - doesn't work. How to get worksheet?
My apologies. For some reason I didn't see this comment until now.
Since releasing this video, I've updated the site. You can find the tracker here:
rockstarexcel.com/wp-content/uploads/Rockstar-Excel-Expense-Tracker-Free-Giveaway-V3.xlsx
Hi, Thanks for this. However, your download is missing the Month - Year Column
Sorry, it was hidden. Column E
This is a great spreadsheet. I started using it in 2023. I cannot figure out how to duplicate it for 2024.
Hi Linda,
Thank you for your kind words. You can find instructions on updating this for a new year here: rockstarexcel.com/how-to-update-rockstar-excel-expense-tracker-for-new-year/
How did you pull up dummy data for no where?
My French bank account doesn’t have a ‘running totals’ column ( which is usual on my UK bank statements). I wondered if ( as French banks don’t show this & won’t give me one -I have asked !), it is possible to automatically download my statements onto a budgeting App , which would provide this ‘running total’ feature ? This would make checking my account for possible errors easier…
Thanks 😊
As long as you can download the statements in Excel or CSV format, it should be easy to create an Excel template that gives you a running total. Just set up a formula you copy/fill to every line that adds a deposit, subtracts a withdrawal, and then adds that to the total from the previous line.
Then save this template, and when you download future statements, copy-paste them onto your template so you don't have to recreate your formulas.
Hope that helps.
What is the function for the Month-year Column? I missed that part.
Is the expense sheet supposed to auto populate totals into the summary sheet?
Hi Yaitza - Yes, the summary sheet should automatically calculate the totals from the expense sheet.
The month-year column is so that the spreadsheet will work over multiple years. If you were just looking at months without that, it would treat, for example, April 2023 and April 2022 as the same and return incorrect information.
Hope that clarifies things.
I have a doubt that i had changed the expenses sheet then why summay sheet is not update my latest expense amount
If you send a copy of your workbook to steve at rockstarexcel.com I can take a look at it to see what's going on.
Hi Steve, thank you for this video. How do I edit the formulas if I want it to state in euros rather than dollars?
If all your transactions are in euros, you don't need to change the formulas at all. Just change the formatting.
If you click the little arrow next to the dollar sign on the home ribbon, it gives you a drop-down menu that lets you select euro formatting.
@@RockstarExcel yes that solves my problem, thank you!
@@Natalie-rm1ep Happy to help!
@@RockstarExcel Hi Steve, I have another question. What is it that goes wrong when some of the data ends up in the ''Undated/Outside Date range''? I have dated those data the same way as the rest, but some of it ends up there anyway and I can't figure out why. Thank you!
I want to make an expense tracker in excel, with a similar format to this one, the difference is my company works with different contractors and I would like to have a summary of the whole year like you have in this video, and I would like to keep track of the expenses for each company. Do i just copy and past the same formulas into sheets 3. 4. 5 etc...?
My apologies. I didn't see this comment until now.
Yes, this should work exactly the same to split up expenses by company or contractor. Or any category you choose.
hi steve starting february all value i put goes to Undated/Outside Date Range . how to fix this?
Without seeing the spreadsheet, I have two guesses:
1. Double-check that the months are using the same year. You may have accidentally set the date as February 2022 on either the summary or detail page.
2. If you imported or pasted the data on the detail page, it may be in a text format that *looks like* a date format, but isn't actually a date format. To fix that, highlight the column with your dates, then go to the Data ribbon and select Text to Columns. On the first screen of the Text to Columns wizard make sure Delimited is selected. On the second screen, unselect all delimiters. On the third screen, choose Date format, and then select your preferred date format from the dropdown.
If neither of those fix it, send me the spreadsheet at rockstarexcel at gmail and I'll take a quick look to see what the problem is.
Where is the actual worksheet? The link took me to your course.
Apologies. Since releasing this video, I've updated the site. You can find the tracker here:
rockstarexcel.com/wp-content/uploads/Rockstar-Excel-Expense-Tracker-Free-Giveaway-V3.xlsx
the ads on this video are insane! haha
Thanks for video. But it was way beyond what I needed. And it was not simple. Still don't know why you made two tabs? I am just tracking the things I buy on Amazon and when I pay them. I think I can put a date and the expense on a spreadsheet and show when I paid them off.
Can you send it at email the dummy data?
Question...SO if I continue to add Categories to the list, will it continuously CALCULATE totals for me???
Yes, it should automatically update the totals to reflect whatever categories you use.
@@RockstarExcel Im having trouble with the formula. When I end my formula with $A9) the total or the info in B9 is 29-Apr...instead of the "total amount" as yours reads. Im using EXACTLY identical cells as you have shown. Please help me.
how can I do for daily transaction
It's even easier with daily transactions, because you don't need the helper column. Just use the SUMIF based on the date and it should work.
hello i like your video I wanted to see if you can help me with the expanse for my vending machine business with stocking and restocking profit also
I can absolutely help you. Email me at steve at rockstarexcel.com to set up a time to talk, or you can book an appointment here: rockstarexcel.com/book-a-free-discovery-session/
Baby Boi, can you help me with how to break into the business? Even if I have to start out working with/for you, I’m open to that. Plz help
this is a great expense tracker, but it is a shame that its obviously not beginner friendly.
Whenever I click on dining, it shows red and has no value. Am I missing a step?
Sir where is your dummy data? We need it to apply what you have teach about.
Thank you for your interest in my videos.
I don't have the example data I used in this video available for distribution.
My suggestion would be to not use dummy data. Instead, download your bank or credit card history and build this using real data. That way, in addition to the learning exercise you'll end up with your real expenses tracked.
It will also make it easier to recognize if you've made a mistake, since with real expenses you'll notice when the numbers don't make sense.
If you can't use real data, let me know and I'll try to walk you through the process of creating your own dummy data. But that's a bit more complicated than can be explained in a TH-cam comment.
I hope this is helpful.
While I don't have the original dummy data available, you can download similar data from here:
rockstarexcel.com/expense-tracker-alternate-dummy-data/
This is not beginner friendly at all
Not a bit. 😂😂
I cannot figure out what I did wrong
so it's not just me??? ive followed this video step by step several times, and i seem to continue to get this step incorrect!!! im so frustrated because this is what i need and ............ugh!!!
why not just use a pivot table for the summary page?
I discuss exactly that in my course.
For beginner-to-intermediate Excel users, pivot tables are good for quick and dirty analyses. But not so good for something you're going to use over and over again.
There are a lot of drawbacks with pivot tables. First of all, they look ugly. And the effort it takes to clean them up is generally more than the effort to reproduce their functionality with SUMIFS.
But the bigger issue is that when you use SUMIFS, your summary will update in realtime whenever you change or add to your data. While with pivot tables, you have to remember to refresh them. And if you forget, you'll be using bad data.
Another thing I teach is that everyone makes mistakes. Instead of hoping to be perfect, you should build systems into your spreadsheet to alert you when something is wrong. The simplest of these is a Check row that calculates things in two different ways to make sure they match. (For example, making sure the total of all your individual categories matches the total from the raw data.)
But with pivot tables, there's no good way to alert yourself when you forget to refresh them. So you end up with a pretty good likelihood that you'll end up using bad data without knowing it.
(Super advanced users can do some amazing things with pivot tables. But this video isn't meant for them.)
Hope that helps.
You can send me a copy?
I know you trying to sell a product, but this video would have been a lot more appealing if you took your time and talked through the steps in the video
I hadn't used Excel in a while granted, but he lost me at 1:42 already in the video when dragging out the rest of the months. I know its supposed to autocomplete like the video, but it doesn't. Edit: I see you have to select January AND Februrary, then drag it across.
It was a littlte hard to follow without telling us the step you kept silent.
Steve can you do us all a favor & clear your throat or take some NyQuil 😭😭😭
Too many missing instructions. No break down 😢
Let me prefece with I am BRAND NEW to Microsoft. However, you go too fast and dont explain which buttons you 're hitting. I am trying to follow to a tee but I have no idea what to click on the keyboard. I can hear your clicks but you don't explain what you are doing and then things just populate and we are left lost.
Promoting your course was the main objective here. Instead you just turn people off
Your not even explaining what it is you are actually doing? Your dragging mouse around clicking but not explaining!??