This is the most underrated personal finance video on CZcams. If you grind 3-6 months of past transactions it's almost automatic. I only need to categorize expenses from vacations or odd e transfers. It's also easy to drill down to problem areas in your spending (for me it was pets).
Brilliant! After YEARS of manually inputting my expenses data, this is the solution I’ve been looking for. Thank you for sharing this, you have saved me hours of work and improved my excel skills!
Thanks so much for your kind comment. It’s for people like you that I make content like this for. Please share with anyone you think could benefit. Thanks again. Dan
Hey Dan, I am currently doing my end of year statements because I want a true idea of how I spend and not just a guesstimate. I saw all the comments before really getting into the video and I was insecure and almost clicked off the video; everyone here seems like they have a basic idea of Excel and I really, realllly don't, I just make pretty tables. But I thought I'd try and.. this video within just 20 Minutes (!!) has absolutely upskilled me on excel. Your explanations are in depth and easy to understand and now I have the best reflection of my personal finances. I cannot thank you enough!!!!! Liked, subscribed, shared, all of that! Wow thank you :) I'll be using my own little excel sheet for years to come. This is priceless work you are doing
Thank you so much Aaliyah. Your message has really uplifted me today and I am so happy for you that you received so much value. I started this channel last year to help people like yourself so it is my pleasure. Best wishes! Dan
This is fantastic thank you so much! I will say as someone who literally works with Excel for a living, this can be made much simpler by using a Table in your Data tab. It will automatically copy all your formulas down, and you can make your PivotTable based on the Table rather than a selection of data, which means if you add or delete columns (among other things), you won't have to reset/redefine your data source. Just a lil tip from my experience making my sheet from this tutorial!
Thanks for watching. I agree about comments about tables and encourage their use. However for my audience, I want them to understand the basic concepts first. Many viewers of my channel have never used a spreadsheet before for instance and I don’t recommend tables for them.
Yes awesome Dan. Again think I mentioned a few weeks ago when trawling through all these types of vids that I really appreciated the Aussie accent!! And way of communicating. Your comment to make sure the cells (lines/ rows etc) matched exactly on each sheet helped solve my Vlookup error too😅
This is it! I searched for hours to find this exact information! Dan, you want more views? Different keywords and title! This information is well presented, succinct, and easy to follow - thank you!
Thanks so much Juli☺️. Appreciate the feedback and I’m happy you are getting value out of this. Out of curiosity which keywords were you using to search? I will consider updating the title to make it easier to find. 😊
I was very nervous about starting an excel spreadsheet to see our income and expense sheet as I did not have a lot of practice besides just data entry. I have never used formulas. I tried creating my spreadsheets based on a couple of different videos. This BY FAR is the best tutorial I have ever seen for excel! I am so thankful for this video I have saved this as my favorites tab to share with a few friends that I know want to do this too! My question for your Dan: I used my credit card statement for my original model to practice with because we charge everything to it. I would like to be able to add my checking accounts to this as my "income" does not go to my credit card but to my checking account. Is there a way to put it all together? Or do you have a personal opinion on how I should create a sheet that will have the income and expense together? Income in debit account and expenses on credit card? I want to show my husband a side by side on what our positive or negative outcome is each month. Thank you in advance!
Thank you so much for your very kind comments. I’m so happy this has had a positive impact. I use this template to combine both my credit card and checking statement together for the same purpose and I encourage you to do this too. All you need to do is put you checking account report underneath the credit card report in the data tab and refresh the pivot table. Each month you would keep adding new data where the previous month’s data finished off. Best of luck!
A couple of hiccups but I got there in the end, had to google how to shift an excel row as my excels an older version (shift+left click+hover and move Income to the top). Great video
Glad you got there in the end and that you like the video. Hope you find it helpful. I upgraded to Microsoft 360 so apologies if the buttons didn’t match up with your version.
Thanks so much for watching! I am happy that you have found this video helpful. I have a few other videos showing how to add a budget and separating business and personal transactions if you are interested that you can find on my channel. Please subscribe if you haven’t already, to see new videos from me.
That’s great to hear. I am glad it is helping. Don’t worry, it will get easier each month as you add more data and you will be rewarded with new insights into your data as times goes by. I spend only a few minutes each month updating it.👍
Hi Dan really liked your video, so nice to have the Aussie accent :-) I can follow 95% of what you say however have hit a hurdle (not copying across tabs the same as yours) and for the life of me can not figure out why as have re-looked at your instructions at least 15 times and can’t see why it won’t do the same as you. Is it possible to ask you a question and show you a screenshot? Leanne
Hi Leanne. Thanks for the kind note. If you have added extra columns then it could be that your pivot table range needs to be expanded. Maybe check that step again in the video. Also my email is listed in my channel page if you wanted to show me a screenshot. Best wishes!
You can buy my Excel Template that I use in this video at my online store here: moneywithdan.etsy.com In the template, I also have a dedicated page for frequently asked questions and my answers (FAQs). I also include an alternative Data tab for bank downloads with two columns!
Hi Dan, great video and very clear explanations. Instead of having an exact match for the 20 first characters of the transaction description in your ref table can you lookup for a series of words? For example on line 25 instead of "Transfer to CBA A/c Mthly" we could lookup for matches to "Transfer to CBA" directly into the transaction description and another one could be just "BIG W".
Thanks for watching. The vlookup can be changed to not look for exact matches by changed the last part of the formula from false to true . I don’t usually do this as I have found it can be unreliable but maybe you can test to see if it works for you.
Thanks for the video, Dan! This is amazing and I am feeling overwhelmed with what I see. 1. I am stuck at this phase. When I try to copy the LEFT formula from Data tab to Ref tab, it shows #REF!. How do I resolve it, please? 2. When I download my bank statement, it would show the credit and the debit amount. Shall I make it into 1 column? 3. What if I have 2 bank accounts?
Hi Shortness. Thanks for watching. 1. Try copy and pasting special values only. It sounds like you are just pressing copy which brings the formula across from the other page, hence the error. 2. You can make it into 1 column or you can change my formulas around slightly to look at two cells. I don’t have a videos on how to modify sorry so best to do what you understand. 3. This methods works well with many bank accounts so long as the csv files are in the same format, which they should be if from the same bank. I use this method for 4 accounts and it works just fine. Thanks
This is great!! Is there some way to add an if to the =Left(B2,20)? My reason is one company I use to make many online purchases uses a different set of characters with every purchase. As an example, Amazon purchases seem to be posted to my bank account with unique characters with every purchase. The first 15-20 characters are different for every entry. With each purchase I have to assign a category. This past year I have to create 169 entries. In order to make it work I would need to change the formula to be =Left(B2,9) 9 does not include enough information for the majority of my other transactions. Any recommendations! Thank you
Thanks for watching. I’m glad it helps. Yes there are many ways to do an IF formula or you can use a MID formula instead of a LEFT so that it starts after the variables.
Really like this video and is extremely helpful. My issue I’m having is sometimes I’ll buy different item from same shop(say bunnings might be repaired or maintenance vs a one off purchase, or different bill from the same place eg gas and electricity or PayPal multiple user. Is there a way to break this up into further into more detail? Or am I at the point where manual input and review is the only option to include this detail? Many thanks!
Thanks for watching. unfortunately that kind of detail requires manual input whether it is in Excel or an online tracker as the data doesn’t exist in online bank statements. I know some people try and use different cards or banks accounts to separate business vs personal expense but I can’t seeing it working deeper than that. Best wishes!
Thanks for the video, it was very useful to learn more about excel this way after learning the basic formulas. It has certainly helped but I'll have to do most manually for now, mostly because I'm still a student so there's not much of a pattern most of the time, but also because in the netherlands you can send payment requests to your friends and this creates a unique identifier you can't work with, because it could fit in all categories. Something I would've liked to see in the video is how to add a second bank account, but I'm sure I can figure it out. Thanks for improving my excel skills and teaching me about pivot tables :)
As others have said, this is so helpful. Thank you. Question: How do we split transactions into different categories? For example, I buy groceries, apparel, supplements, etc., from Costco. Thanks again!🤑
Very well presented tutorial, I am familiar with most of what you have shown but putting it all together was very helpful to me as I have been trying too do the auto update with dropping in my CSV file for some time now, now I can personalize it to my Budget sheet.Great work.
Hi Kishorchandra. I created another 3 minute video that breaks this part down separately which may help you. You can find it here: czcams.com/video/pUo0kO60mB8/video.html Was there a part that you are struggling with in particular that I can answer as there are a few steps involved? Also, I created a template that is available to buy if you wanted to skip making your own and you can find that here: www.etsy.com/au/shop/MoneyWithDan
@moneywithdan the problem I am having is when I COPY THE REFERENCE ITEMS AND GO TO PAST THEM THEY WON'T PAST. What is pasting is the word REF all the way down? I have tried this so many times and I can't figure it out!! Can you please help I love this concept!!!!
Thanks for watching. You need to press the right type of “paste” function for “values” only. What you are doing is pasting everything including the formula which gets an error as the formulas does not work in that new tab. I suggest that you watch that part of the video again where I show you which paste button to press. Best of luck!
Thank you so much, this is so useful and easy to follow! Is there a simple way to add subcategories with the vlookup method? I have tried fiddling with the formula but no luck so far. Also I wasnt able to produce a pie chart while my expenses were represented as a negative value, so I used ABS to make all the numbers positive. Ideally I would love to have a side by comparison of income and expenses in a bar chart - any idea how to do that? Thanks again so much for your methodical approach 🙌
Thanks for watching. I created a video on adding subcategories that you can find here: Setup SUBCATEGORIES to Track Expenses in EXCEL | SYNC for FREE #6 czcams.com/video/UhBxbjd5Gn0/video.html In regards to pie charts, I left my expenses as all negative values as it didn’t bother me. I just filter out income , which is the only positive value, so the graph works properly just like I do in the video. You can play around with pie charts and bar graphs. Unfortunately I don’t have another detailed video on graphs and they can be quite personal when you choosing formats. Best wishes
Hi Dan, I know you did this some time ago but THANK you very much, it was excellent and so well explained. I plan to use this for credit card statements - do you have something similar that if for credit cards? If not I will adapt this, thanks again. Leanne
Thankyou for this excellent tutorial.I have managed the pivot table one month's of data, I was wondering how you managed to get the data from the second month in a list with all of the unmatched items together at the bottom so you could easily cut and paste them together to the reference sheet. Thanks for any advice
Hi Helen. Thanks for watching. I show this in more detail towards the end of the video. There is an index in the description of this video that will show where it is. If you haven’t already please press the like button to help share the video and please subscribe to see more videos from me. Thanks
Unfortunately I find the focus in this video somewhat blurry, and even after changing the quality to the highest resolution, the font is so small one can't follow just what formulas are being typed and which elements are even being selected. Is there a "Full Screen" version that would allow better viewing? (not just the CZcams Full Screen view)
Hi Dan! I swear this is the only video that is close to coming to the answer I am looking for. I know that for data purposes the percentages will always be out of 100%. But how can I create a graph that shows your expenses in correlation to your income? For example, I would like the pie chart to be out of $2,000 as that is the monthly income and for example, and have the pie chart show me that 30% is rent, 10% eating out, groceries etc etc of HOW I am spending the $2k. I hope that makes sense? Thank you!
Hi Elizabeth. Thanks for watching and for your kind comment. I do this personally too. The easiest way to do this is to create a simple formula to the side of your pivot table next to each expense to calculate the % of sales and any difference would be savings or deficit. There are more complex ways but I would need to create a whole video to explain sorry and I don’t have one ready but will consider it next. Sorry I couldn’t help further. Dan
Thank you so much for this. Greatly needed and appreciated. Question, how would I enter a previous month/week into the data sheet and would that automatically migrate to the ref sheet? Is this even possible?
thanks so much! you sure can add it with much ease and you only need to add new purchases that didn’t appear in earlier statements. I cover this in more detail at 13:24 if you want to re watch it.
First of all great tutorial! How would you handle this if you had a budget account as well for fixed expenses? Would you simply extract from that account as well?
Thanks so much for your support and appreciate the comment. I made a seperate video on how to add a budget step by step to this this same template which you can find here czcams.com/video/DYsBcwJ9kAs/video.html .
Hi very well explained in simple way, I am in the process of making my budjet sheet but i have three differnet bank accounts with different data columns, finding it very hard to arrange them , any tip please ?
I am glad you found this helpful. I have this issue with one bank account and I copy and paste the cells one by one into the columns of the other accounts so that the data is recorded in Excel consistently. It is a little more work but not much more and the formulas will still work.
@@MoneywithDan Thanks for your quick response, I have manged to arrange the data in same sheet and differnt bank accounts. Bravo! One more thing i watched another video on how to make an interactive budget sheet where you can simply scan your receipts and record data. please try to make some video on that topic in future, thanks again mate
Hello Dan, thank you for the instructions, however, I am struggling with one cell coming up as #N/A, I have read the message below and tryed this. Also when i add the next statement data, - most of the cells are coming ups #N/A - any further suggestions will be so appreciated 😀
Hi Lilly. I think you have coped the Formulas from the LEFT copy in stead of pasting value only when creating your unique identifier. Maybe double check that formula. Let me know if still having issues.
This is such a great video but I'm getting a #SPILL! error in my Category column on the Data sheet? I'm missing the col_index_no number i think; (lookup_value, table_array, col_index_no, [range_lookup). Can anyone help!? Much appreciated!!
Sounds like you have typed a wrong cell or range in your vlookup formula. Sometimes this happens to me when I accidentally forget to select the field to compare a table with. Maybe recheck that section of the video again and follow what I did.
Hi Dan , I’m having an issue with my pivot table. I keep getting an error message that says “the pivot table field name is not valid. To create a pivot table report you must use data organized as a list with labeled columns. If you are changing the name of a pivot table field you must type a new name for the field” I have done all the previous steps and don’t understand
Hi Kelcie. You need to have a heading name at the top of all columns that you have selected to create a pivot table. Make sure you don’t have any blanks fields at the top row of your selection as this will cause this error. Hope this fixes this issue for you!
@@MoneywithDan Way of running code that can automate a lot of this. Finding that using =LEFT.. doesn't work for transactions that hide the date somewhere in the first 20 chars
for transferring my savings in and out it says the same thing in the description is there any easy way i can differentiate between savings transferred in and out thank you for this vid it’s amazing
Thank you. Glad you liked the videos. Yes that is correct as transfers in and out are designed to net off to zero using this method as they are not true expenses. To add more descriptive categories for transfers, I review my transfers category in detail each month and then manually override the VLOOKUP formula result for a particular line if the automatic category description that was assigned for certain transfer meant something unique on its own such as to pay or repay for expenses to an account I did not own (e.g. friends and family for gifts). However, I don’t have many transfers each month and manually over riding the formula is not a big time burden to warrant a complex formula to be written, and I usually just leave transfer to the default description of “transfer” since they typically net to zero each month. Hope this helps!
My csv files downloads with two columns for transactions values. Such as, debits and credits are separated and all numbers are positive. How can I consolidate the values to one column of positive and negative values for easy computing?
Hi Jordan. There is a way to do this by adding formulas to summarise into one. I haven’t created a video to do this but I have created a template with a variation that does convert this that I have added to my Etsy store if you are interested with written instructions - a link to the store in included in the description of this video. Hope this helps !
This is awesome! Just what I was looking for, thank you so much! Just wondering, is there a way to have dropdown values in the category column in the ref worksheet? So instead of having to type them, you could select from a dropdown list. Furthermore based on that, would it then be possible to have another column for subcategory which would be a dependent drop list based on the value in the parent category column? For example, instead of groceries, the parent category would be "food and dining" with subcategories being "groceries", "takeout", "coffee", etc. In the ref worksheet, ideally, when you manually assign the category and subcategory once, all future transactions from the same merchant would be auto-updated. This is what I've been envisioning, but have been unable to fully automate so far. Would be great to see a video like this if it seems interesting to you :) Thanks again!
Thank you so much for your ideas and feedback. All of those things are definitely possible with an extra column and a few simple formula and settings. I would need to create another video to show how to do this as there are a number of steps. I was considering what video to make next and this has come very timely!
Thanks for watching. There is a way to do it but you need to add an extra two columns but my video doesn’t cover that unfortunately. I created a template that solves the problem at my store if your interested and the link is in the description.
@@eetswad no problem 👍. I don’t have any ideas for Excel tutorials at the moment. I am happy to hear any ideas or suggestions for videos if there is anything you’re interested in. Thanks!
Good question. It works with card transactions as well as online payments if you pay with either debit or credit cards. However, If you mostly withdraw cash from your account and pay your bill and make purchases at physical shops with that cash then this method, and most mobile budget apps for that matter, will not give you much detail and may not be valuable to you.
Hey Dan! Thanks again for sharing this great solution with us. But unfortunately I struggle with it. Easy to make mistakes and it's pretty difficult to convert the CIBC bank transactions. Can you recommend a 'offline' version (no information on cloud stored) that is a bit more user friendly? I am willing to pay if needed.... Just want say again that your tool is great! It's more me that is struggling. Thanks! Niels
Hi. Thanks for watching and sharing your feedback. Unfortunately Excel is the only offline option I can think of that allows automation of categories. Apps are popular for this reason but I understand your concern about not wanting your data somewhere in the cloud. Sorry I can’t help much more except suggest you try and watch the video again to keep learning and practicing with Excel.
I am have loads of problems with the copy and paste of unique values from the data page to Reference page ...it keeps coming up as ref # ..can any one guide me here?
Hi Nicolas, did you paste “value only” instead of paste “all”. When you paste you need to left mouse click and select paste only. I suggest rewatching that part of the video as I show the difference.
I select the corner of the category I want to move by clicking and holding with left mouse button, move it where I want and let go of the button. It just a drag and drop if you click on the right spot.
@@gavinmedeiros2076 that’s a shame. I am no that familiar with Google sheet functions unfortunately as I use Excel and some features are not compatible. Best wishes
Thank you for your kind comment! Absolutely you can add multiple bank accounts and I do that too. All you need to do is paste the CSV data from the second account each month underneath the data from the CSV of the other account in the "Data" tab just like adding a second month of transactions. The CSV file should include a column with the bank account name or number in each row. In my video the bank account details is in column D and I have bank account 1 & 2. When you include the data from the second account you can add "bank account name" to the pivot table in either the filter or the column. I also prepared a follow up video to this one if you're interested to learn more here: czcams.com/video/YwmpFS8fBZ4/video.html
@@MoneywithDan Thanks! I'll be sure to do that when I get to that point :) I do have one more question if you don't mind answering. Do you know how to format or set a rule for the Transaction Value cells to be negative and positive like yours are in the video? The transaction data that I exported from my bank did not automatically format the Transaction Value cells to be negative and positive, but rather they are all positive with a separate column labelled Credit/Debit to identify them. Essentially I need to reformat them or set a rule to do something like "If Credit, then set as positive value" and "if Debit, then set as negative value" for the existing numbers. I've tried looking it up and watching other tutorials on conditional formatting, but so far I haven't found a way to do this specific thing. I'm not sure if this is even a conditional formatting matter haha.
@@robbiechan6941 Hi Robbie. Thanks for the question. There are a few ways to do this but the simplest way would be to add a new column with a new formula called “IF” formula. To use an IF formula, in the first cell of the new column in a row that has data, the formula would look like this IF(A2=“debit”,-B2,B2) where A2 is the field that has debit/credit data and B2 is the transaction value in the bank statement data that has no negatives. Then ensure you copy and paste the formula down. Make sure you give this new column with an IF a heading and include it in the pivot table range so you can add this column in the value field of the pivot table, instead of the other column with values that has no negatives. This should ensure you expenses are negative like mine. Let me know how you go.
G'day Dan, how would I go about creating an automatic list of the transactions I haven't yet catagorized? Instead of manuallly sorting through ones that I might have done before, Thanks in advance.
Hi Blair. You will only need to add a category name when there is an error in the vlookup formula in the category cell. I show an example of this when adding a second month of data at 14:04 of this video. Hope this helps!
@@MoneywithDan I'm going through old statements and because I've copy pasted the whole colomn there is alot of repeat transaction names to sort through, I just highlighted it and deleted duplicates but now I cant see my income catagory, all the other catagories are there? I've hit refresh and tried removing some, still no show. Thanks for prompt replies!
I love your video, but am having trouble using the LEFT formula. It doesn't return any result, and just shows the formula in the cell instead of the result. If anyone knows why that might be, let me know. Otherwise, thank you so much for walking us through your process. It's really well done.
Hi. Thanks for watching. Did you put the = sign at the beginning? The other issue may be because automatic formulas option may be turned off. It’s easy to turn on and you can find the steps using google. Hope this helps.
@@MoneywithDan Thanks for your help! I'll try that. And yes, I was using the = sign at the beginning. Edit: I had the "Show Formulas" button on, so switched it off and now have no issues with the LEFT function. Thank you for showing the way!
Good video I purchased your template have a slight issue I am using the data value 2 columns as I have debit/ Credit I think my columns are the wrong way round and at present I have category #N/A column E is credit values presumably I need to swap around or can I use column D ?
Hi Bill. Firstly thanks for watching and buying the template and for your question. The debit and credit columns shouldn’t matter as they all sum into column F “transaction value”. If category column is showing n/a it is because it can’t find a match in your “ref” tab and isn’t affected by the values you enter into column D to F. Hope this clarifies. Feel free to email me at enquires.moneywithdan@gmail.com
@@BillUptonITConsultant sorry to hear you’re having troubles. If your pivot table is still looking at my source data then it sounds like you need to update the data source range of the pivot table. I cover this part at 8:46 of my video and I suggest that you delete the current range in the pivot table and then reselect the range in the tab where your data is located in full just as I do in the video. I hope this resolves the issue.
Thanks for watching. You can use a MID formula instead of the left formula to skip the starting number of characters that cover the date. Try googling MID formula.
Hi Bethany. Sounds like you need to paste numbers only and not paste all which would bring across the formulas which you don’t want. Let me know if that works.
@@MoneywithDan I worked it out, I need to type a fresh formula =left(sheet!cell,number of character) now let hope wth the altered formula the rest works 😃 🤞
I am trying everything you said but when I do de =vlookup for the categories not all the different categories from REF match with the ones from DATA TAB, It just avoids some of them and shows a category that is written more often, you know what I mean by this? and do you know how can I solvent this problem?
Hi Jose. It sounds like your ref data contains very similar characters and are not unique enough for the vlookup to get a clear match. Try adding more character size in the left formula in the data tab up to 25 or 30 and copying pasting that to your ref tab.
Hi Dan, I bought your template, could you please guide me how to refresh on Summary tab as I am unable to see the changes that I have made. The summary tab page is empty.
Hi there. Thanks for purchasing the template. If you click on the pivot table first (anywhere) and press the right mouse button a new menu should display. In this new menu click on “refresh”. Hope this helps!
@@MoneywithDan It worked, Thanks. This is the best Excel template, I have unsubscribe from PocketSmith and previously used many other online websites. For data safety and easy to use this is the best. Thank you for your efforts. Cheers
Hi there. It sounds like you have selected a column that does not have a name at the top of the column. If you enter some characters then it should clear the problem. Let me know if the problem persists.
@@MoneywithDan thank you! It worked! I have another question - so my credit card run from the 22nd of each month to the 22nd of the next month. So when I see the monthly view I would have to see 2 months worth at a time. Is there a way I could combine it so it reads month 1 to month 2 in the pivot table? Thanks
@@DiLLZGFX I prefer that view too. If your credit card extract file has dates in the data then this template should organise your data by month by default without having to do anything extra.
I set this up last month and it worked great but I ran into a snag this month when I had a new category of expense. When I refreshed my Pivot table it updated with a column for the new month and all the expenses in the old categories tallied, but nothing came up for the new category I made. Do you know how I could fix this?
I am happy to hear it is working for you and thanks for watching! To fix this issue I have provided instructions in this video at the time stamp 14:16 and I explain what to do when adding the second month of data where new transactions appear. Basically you need to add a unique identifier each time there is a new transaction you have never had before as there is no formula reference. Over a few months you will develop a list of unique identifies that is more complete for less frequent expenses that are either quarterly or annually and you will find that you will need to add fewer and fewer unique identifiers. This is the algorithm learning your expense patterns and the more data over a longer period of time then the less addition of unique identifiers you will need. Best wishes!
@@MoneywithDan Hi Dan - thank you so much for the quick answer, though that's not actually been what my issue is. I have created unique identifiers for all my new transactions, the issue has come when one of these is in an entirely new category that I didn't have last month. In the video the new transactions still fall under the existing Dining and Motor Vehicle categories, and that has worked fine for me. However I had one transaction that I had to make a new category for (local council fees), and this hasn't transferred across to the pivot table. Do you have any knowledge on how to fix this?
@@otizings there are few possibilities and one likely one is that the new category may be unselected. If you go to the pivot table and at the drop arrow at the top of the column, click on it, and see if your new category is selected with a tick next to it. If you deselect one item in the previous month, like blanks, then every time you add a new category it automatically defaults to unselected new categories in the pivot table after you refresh. One less likely option if the pivot table range or formula range maybe excluding your new category so check your formula to see if they are working. Hope this helps
@@MariaAlvarez-xn2lv yes there is. I have another video that uses the same excel template but also walks you through adding a subcategory. You can find the video on YT here czcams.com/video/VwqSAiqMyQc/video.html . You can fast forward to 16:03 where I add talk about “tax type” and refer to private versus business expenses, which is essentially adding subcategory to my template and you can give it any description you like. Hope this helps
Do you mean when paying with cash instead of card or electronic transfer? If you withdraw cash from your account at a store, bank machine or at the bank teller, then that would appear as a transaction on your account and you can can give the withdrawal transaction a category name if you use that cash withdrawal for a specific purpose, like pay for groceries for example. However, if pay mostly with cash for lots of different types of transactions and rarely/never with a card then this method may not be the best option for you.
Great video, really helpful. I got one issue when adding a new month, I get #N/A in vlookup on every single new expense I add. At first, it looked to be because every transaction had a unique Visa ID, but it happens even when I narrow the number of characters so that the ref. is the exact same. Got any tips on this? Keep up the good work.
Thanks for sharing! it does sound like the type of transaction is not unique enough to get an exact match due to unique transaction numbers you get with this type of payment. You could try lengthening the number of characters in the LEFT formula and also changing the VLOOKUP formula to not look up an exact match by changing it to TRUE at the last part of the formula. Hope this helps!
@@MoneywithDan Hi Dan, thanks for sharing this. I'm having the same problem, my credit card payments all have a unique ID but when I switch the formula to true, it starts to confuse all of the categories I created. Is there any way around this?
@@williambroverman2856 hi there. If you have a unique ID created then you should enter FALSE in the Vlookup formula (instead of TRUE) as I do in the video to only look for exact match. Did I understand correct? Or do you mean you do not have a unique ID? If no unique ID is it because there is a date at the beginning of the text? If so I suggest using a MID formula instead of a LEFT which essentially allows you to skip characters that are not constant.
@@MoneywithDan Hi Dan, thanks for the speedy response. When I entered the first month's data and created the unique category ids using the left formula everything worked out perfectly. When I added the second month of information the left formula didn't recognize any of the new transaction descriptions. There is a big jumble of numbers at the beginning of each transaction description that isn't a date or a constant id number from my card. I tried changing the left formula to TRUE to see if it would pick up a pattern from the jumbled numbers and sort them into the categories I created. Based on what I've read in the comments (and you just suggested) I think I'll have to figure out the MID formula. This is all new to me and it's hard to start learning from scratch and then apply it in a new scenario. Any tips on how to get it working just for this? I just bought your online template. Hoping it makes things simpler. If not, still happy to support someone who makes themself so available to everyone who has reached out for support. All the best
@@williambroverman2856 thanks so much for you support. I think you will benefit with the MID formula for your situation. Many banks add number at the beginning of the text for things like dates making it hard to be unique. Hopefully the sequence is predictable so you know how many characters to skip constantly. It may not always be perfect for irregular expense but you should be able to automate most of the regular transactions. Best wishes!
Hi Dan, great video thanks! However I have problem with the CSV data from my bank. I downloaded it, and the description column has date and card number in it, preceding the store name, despite there being a separate (LH) column with date too. So the "left" formula will bring in this variable data, which will be different every time, and can't be used for category allocation. I live in the UK, maybe banks here just don't send the pure description over?
Hi Carys. That’s easy fixed, you just need to adjust the LEFT formula to bring across more characters until it is unique. I defaulted to 25 but you go as high as you need to. Hope this helps!
@@MoneywithDan Hi Dan , thanks so much for your reply. The string will never match to future transactions though, however long, because it contains the date?
@@carys8027 true. Instead of using a LEFT formula you could use a MID formula which is similar but it will bring across fields after a number of characters like the date. I had a similar question from a viewer and it fixed the issue. I don’t have a video about this formula but this link should explain it exceljet.net/excel-functions/excel-mid-function . You would just use the MID instead of LEFT and everything would work the same. Hope this helps
Thanks for watching. That would be adding an extra step to an already very long video. I chose to keep it simple for my viewers and avoid further complication. If you want delete duplicate cells for a cleaner ref tab then there is a button called “remove duplicates” under the “Data” ribbon at the top.
I haven’t come across this many identical transactions for personal transactions accounts before where they each for different different category types. Usually multiple transactions with the same description have the same category name so it hasn’t been an issue before. Do the 400 transactions have exactly the same description? I assume you have tried increasing the number of characters in the Left formula until they are unique as I show in my rent example within the video. I also assume the 400 transactions are for completely different categories and you want to give them seperate category names. If the transactions are bank transfers you could give them a different description when you transact so that they are unique in the future.
Hey I’m having some trouble on the pivot table the problem is when I try to put the amount/value of transaction it goes in there as the count of the category. EX if I have Gas station as my category the total transactions next to it should be 100 dollars but instead it says 4 (because of how many times it was categorized as gas station). We can discuss more maybe on your Instagram if that’s fine
Hey there. No problem and it’s a common problem. in the pivot Table you need to change the count to sum function. Try following following this link with step by step details and screenshots excelinexcel.in/ms-excel/formulas/count-to-sum-function/ .let me know if you still get problems!
![Bank Statement download [CSV analysis in Excel] in 2021](/img/n.gif)
This is the most underrated personal finance video on CZcams.
If you grind 3-6 months of past transactions it's almost automatic. I only need to categorize expenses from vacations or odd e transfers. It's also easy to drill down to problem areas in your spending (for me it was pets).
Thanks so much! Glad you like it 👍
Brilliant! After YEARS of manually inputting my expenses data, this is the solution I’ve been looking for. Thank you for sharing this, you have saved me hours of work and improved my excel skills!
Thanks so much for your kind comment. It’s for people like you that I make content like this for. Please share with anyone you think could benefit. Thanks again. Dan
@@MoneywithDan Will do!
Excellent Instructions and Template from ETSY! I have been searching for something clear and straightforward. This is it for us, Novices!
Thanks so much Gavin! I’m so glad that this is helping you!
Hey Dan, I am currently doing my end of year statements because I want a true idea of how I spend and not just a guesstimate. I saw all the comments before really getting into the video and I was insecure and almost clicked off the video; everyone here seems like they have a basic idea of Excel and I really, realllly don't, I just make pretty tables. But I thought I'd try and.. this video within just 20 Minutes (!!) has absolutely upskilled me on excel. Your explanations are in depth and easy to understand and now I have the best reflection of my personal finances. I cannot thank you enough!!!!! Liked, subscribed, shared, all of that! Wow thank you :) I'll be using my own little excel sheet for years to come.
This is priceless work you are doing
Thank you so much Aaliyah. Your message has really uplifted me today and I am so happy for you that you received so much value. I started this channel last year to help people like yourself so it is my pleasure. Best wishes! Dan
Excellent presentation. I learned more from this video than I have in many Excel classes.
Glad you enjoyed it!
Great tutorial. I was able to set it up for my personal bank and credit card accounts. Thanks!
Well done 👍
This is fantastic thank you so much! I will say as someone who literally works with Excel for a living, this can be made much simpler by using a Table in your Data tab. It will automatically copy all your formulas down, and you can make your PivotTable based on the Table rather than a selection of data, which means if you add or delete columns (among other things), you won't have to reset/redefine your data source. Just a lil tip from my experience making my sheet from this tutorial!
Thanks for watching. I agree about comments about tables and encourage their use. However for my audience, I want them to understand the basic concepts first. Many viewers of my channel have never used a spreadsheet before for instance and I don’t recommend tables for them.
Yes awesome Dan. Again think I mentioned a few weeks ago when trawling through all these types of vids that I really appreciated the Aussie accent!! And way of communicating. Your comment to make sure the cells (lines/ rows etc) matched exactly on each sheet helped solve my Vlookup error too😅
Thanks so much for the kind words. You picked my accent correctly 😁
I have just realised how many hours of my life I have wasted. Thanks for the Information. you have made my life easier.
Glad to hear it is helping you! I have other videos if you are interested on other budgeting tips available on my channel page. 👍
This is it! I searched for hours to find this exact information! Dan, you want more views? Different keywords and title! This information is well presented, succinct, and easy to follow - thank you!
Thanks so much Juli☺️. Appreciate the feedback and I’m happy you are getting value out of this. Out of curiosity which keywords were you using to search? I will consider updating the title to make it easier to find. 😊
@@MoneywithDan I found this video pretty easily after a few minutes of searching so unless you changed it I think it's good?
@@rever4217 thanks so much for answering. I didn’t make any changes so I guess It’s CZcams.
I like that once it is set up you just need to download another CSV file from the bank and it is automatically refreshes in seconds. Great idea!
Yep simple as that!
I was very nervous about starting an excel spreadsheet to see our income and expense sheet as I did not have a lot of practice besides just data entry. I have never used formulas. I tried creating my spreadsheets based on a couple of different videos. This BY FAR is the best tutorial I have ever seen for excel! I am so thankful for this video I have saved this as my favorites tab to share with a few friends that I know want to do this too!
My question for your Dan: I used my credit card statement for my original model to practice with because we charge everything to it. I would like to be able to add my checking accounts to this as my "income" does not go to my credit card but to my checking account. Is there a way to put it all together? Or do you have a personal opinion on how I should create a sheet that will have the income and expense together? Income in debit account and expenses on credit card? I want to show my husband a side by side on what our positive or negative outcome is each month. Thank you in advance!
Thank you so much for your very kind comments. I’m so happy this has had a positive impact.
I use this template to combine both my credit card and checking statement together for the same purpose and I encourage you to do this too. All you need to do is put you checking account report underneath the credit card report in the data tab and refresh the pivot table. Each month you would keep adding new data where the previous month’s data finished off.
Best of luck!
Exactly what i was looking for! Thanks for the amazing content
Best expense traking. Better than any expence app. Thanks
thanks so much! glad you like!
A couple of hiccups but I got there in the end, had to google how to shift an excel row as my excels an older version (shift+left click+hover and move Income to the top). Great video
Glad you got there in the end and that you like the video. Hope you find it helpful.
I upgraded to Microsoft 360 so apologies if the buttons didn’t match up with your version.
Awesome simple and direct explanation. Perfect what i was looking
Thanks so much for letting me know. I’m so glad that you found it helpful!
This is soo useful, thank you! Nice to have someone talking about money specifically for Australia, I’m subbed!
Glad it was helpful!
Awesome. I'm thinking of the best way to organize multiple data sets like from your bank + credit card sheets.
Thanks for watching. I think this will definitely help you.
this is exactly what I was looking for.
That’s great! Thanks for letting me know!
This was so simple. Thank you LOADS! It would have taken me hours to google this on my own. Exactly what I needed.
Thanks so much for watching! I am happy that you have found this video helpful. I have a few other videos showing how to add a budget and separating business and personal transactions if you are interested that you can find on my channel. Please subscribe if you haven’t already, to see new videos from me.
The only video that I was looking for , Thanks a ton!
Love it!! I will show this to other members of the family.
Awesome! Thank you!
Thank you for posting this helpful video
Thanks for watching. Glad you liked it!
This is a fantastic video. Exactly what I was looking for.
Thanks so much!
Thanks a lot Dan! This helped me a lot. Was looking for a while for an offline version. It took me a few hours to build. But I made it^^
That’s great to hear. I am glad it is helping. Don’t worry, it will get easier each month as you add more data and you will be rewarded with new insights into your data as times goes by. I spend only a few minutes each month updating it.👍
Hi Dan really liked your video, so nice to have the Aussie accent :-) I can follow 95% of what you say however have hit a hurdle (not copying across tabs the same as yours) and for the life of me can not figure out why as have re-looked at your instructions at least 15 times and can’t see why it won’t do the same as you. Is it possible to ask you a question and show you a screenshot? Leanne
Hi Leanne. Thanks for the kind note. If you have added extra columns then it could be that your pivot table range needs to be expanded. Maybe check that step again in the video. Also my email is listed in my channel page if you wanted to show me a screenshot. Best wishes!
Ah this has taken so much stress way thank you. Awesome video
So glad it is helping!
You can buy my Excel Template that I use in this video at my online store here: moneywithdan.etsy.com
In the template, I also have a dedicated page for frequently asked questions and my answers (FAQs). I also include an alternative Data tab for bank downloads with two columns!
Thankyou for this, helped a tonne
Glad it helped! Thanks for subscribing!
Absolutely amazing guide. Subscribed!
Thanks! I am glad you have subscribed. Plenty more on the way.
Hi Dan, great video and very clear explanations.
Instead of having an exact match for the 20 first characters of the transaction description in your ref table can you lookup for a series of words?
For example on line 25 instead of "Transfer to CBA A/c Mthly" we could lookup for matches to "Transfer to CBA" directly into the transaction description and another one could be just "BIG W".
Thanks for watching. The vlookup can be changed to not look for exact matches by changed the last part of the formula from false to true . I don’t usually do this as I have found it can be unreliable but maybe you can test to see if it works for you.
Absolutely lovely- thank you! That was surprisingly fun to watch. 😅
Please share more you're very detailed.
Thanks so much for the comment. I am glad you enjoyed it. I plan to upload more soon!
Wow thank you very much!!!
You’re welcome!
Thanks for the video, Dan! This is amazing and I am feeling overwhelmed with what I see.
1. I am stuck at this phase. When I try to copy the LEFT formula from Data tab to Ref tab, it shows #REF!. How do I resolve it, please?
2. When I download my bank statement, it would show the credit and the debit amount. Shall I make it into 1 column?
3. What if I have 2 bank accounts?
Hi Shortness. Thanks for watching.
1. Try copy and pasting special values only. It sounds like you are just pressing copy which brings the formula across from the other page, hence the error.
2. You can make it into 1 column or you can change my formulas around slightly to look at two cells. I don’t have a videos on how to modify sorry so best to do what you understand.
3. This methods works well with many bank accounts so long as the csv files are in the same format, which they should be if from the same bank. I use this method for 4 accounts and it works just fine.
Thanks
This is great!! Is there some way to add an if to the =Left(B2,20)? My reason is one company I use to make many online purchases uses a different set of characters with every purchase. As an example, Amazon purchases seem to be posted to my bank account with unique characters with every purchase. The first 15-20 characters are different for every entry. With each purchase I have to assign a category. This past year I have to create 169 entries. In order to make it work I would need to change the formula to be =Left(B2,9) 9 does not include enough information for the majority of my other transactions. Any recommendations! Thank you
Thanks for watching. I’m glad it helps. Yes there are many ways to do an IF formula or you can use a MID formula instead of a LEFT so that it starts after the variables.
This is brilliant
Thanks so much,
Really like this video and is extremely helpful. My issue I’m having is sometimes I’ll buy different item from same shop(say bunnings might be repaired or maintenance vs a one off purchase, or different bill from the same place eg gas and electricity or PayPal multiple user. Is there a way to break this up into further into more detail? Or am I at the point where manual input and review is the only option to include this detail? Many thanks!
Thanks for watching. unfortunately that kind of detail requires manual input whether it is in Excel or an online tracker as the data doesn’t exist in online bank statements. I know some people try and use different cards or banks accounts to separate business vs personal expense but I can’t seeing it working deeper than that. Best wishes!
Thanks for the video, it was very useful to learn more about excel this way after learning the basic formulas. It has certainly helped but I'll have to do most manually for now, mostly because I'm still a student so there's not much of a pattern most of the time, but also because in the netherlands you can send payment requests to your friends and this creates a unique identifier you can't work with, because it could fit in all categories. Something I would've liked to see in the video is how to add a second bank account, but I'm sure I can figure it out. Thanks for improving my excel skills and teaching me about pivot tables :)
Glad you liked the video and thanks for watching. I appreciate the feedback too!
As others have said, this is so helpful. Thank you. Question: How do we split transactions into different categories? For example, I buy groceries, apparel, supplements, etc., from Costco. Thanks again!🤑
Thanks for watching. This would be a manual process where you would override the formula and type in what that category should be. Hope this helps.
Wow!! such an amazing explanation!
Thanks so much!
Dan, do I continue to add additional years or make a new workbook? PLEASE advise.
Thanks for watching. I keep all the years in the same workbook for ease of analysis.
Great vid mate, subscribed
I am happy it is helping. Thanks for sharing this!
Very well presented tutorial, I am familiar with most of what you have shown but putting it all together was very helpful to me as I have been trying too do the auto update with dropping in my CSV file for some time now, now I can personalize it to my Budget sheet.Great work.
You're very welcome! glad you like it.
Hey Dan. For a dim wit like me can you elaborate how to down load transactions from your bank account, in details please. Thanks.
Hi Kishorchandra. I created another 3 minute video that breaks this part down separately which may help you. You can find it here: czcams.com/video/pUo0kO60mB8/video.html
Was there a part that you are struggling with in particular that I can answer as there are a few steps involved?
Also, I created a template that is available to buy if you wanted to skip making your own and you can find that here: www.etsy.com/au/shop/MoneyWithDan
@moneywithdan the problem I am having is when I COPY THE REFERENCE ITEMS AND GO TO PAST THEM THEY WON'T PAST. What is pasting is the word REF all the way down? I have tried this so many times and I can't figure it out!! Can you please help I love this concept!!!!
Thanks for watching.
You need to press the right type of “paste” function for “values” only.
What you are doing is pasting everything including the formula which gets an error as the formulas does not work in that new tab.
I suggest that you watch that part of the video again where I show you which paste button to press.
Best of luck!
Same thing happened to me. REF all the way down the column. I used Paste Value and it worked!
Thank you so much, this is so useful and easy to follow! Is there a simple way to add subcategories with the vlookup method? I have tried fiddling with the formula but no luck so far. Also I wasnt able to produce a pie chart while my expenses were represented as a negative value, so I used ABS to make all the numbers positive. Ideally I would love to have a side by comparison of income and expenses in a bar chart - any idea how to do that? Thanks again so much for your methodical approach 🙌
Thanks for watching. I created a video on adding subcategories that you can find here: Setup SUBCATEGORIES to Track Expenses in EXCEL | SYNC for FREE #6
czcams.com/video/UhBxbjd5Gn0/video.html
In regards to pie charts, I left my expenses as all negative values as it didn’t bother me. I just filter out income , which is the only positive value, so the graph works properly just like I do in the video. You can play around with pie charts and bar graphs. Unfortunately I don’t have another detailed video on graphs and they can be quite personal when you choosing formats.
Best wishes
Hi Dan, I know you did this some time ago but THANK you very much, it was excellent and so well explained. I plan to use this for credit card statements - do you have something similar that if for credit cards? If not I will adapt this, thanks again. Leanne
Thanks Leanne. I use this for both credit cards and bank accounts as it is in the same format. So this is it. Hope it works out!
Thankyou for this excellent tutorial.I have managed the pivot table one month's of data, I was wondering how you managed to get the data from the second month in a list with all of the unmatched items together at the bottom so you could easily cut and paste them together to the reference sheet. Thanks for any advice
Hi Helen. Thanks for watching. I show this in more detail towards the end of the video. There is an index in the description of this video that will show where it is. If you haven’t already please press the like button to help share the video and please subscribe to see more videos from me. Thanks
Unfortunately I find the focus in this video somewhat blurry, and even after changing the quality to the highest resolution, the font is so small one can't follow just what formulas are being typed and which elements are even being selected.
Is there a "Full Screen" version that would allow better viewing? (not just the CZcams Full Screen view)
thanks for letting me know. I think CZcams has reduced the quality of the video feed recently as this is the first comment I’ve heard like this.
Hi Dan! I swear this is the only video that is close to coming to the answer I am looking for. I know that for data purposes the percentages will always be out of 100%. But how can I create a graph that shows your expenses in correlation to your income? For example, I would like the pie chart to be out of $2,000 as that is the monthly income and for example, and have the pie chart show me that 30% is rent, 10% eating out, groceries etc etc of HOW I am spending the $2k. I hope that makes sense? Thank you!
Hi Elizabeth. Thanks for watching and for your kind comment. I do this personally too. The easiest way to do this is to create a simple formula to the side of your pivot table next to each expense to calculate the % of sales and any difference would be savings or deficit. There are more complex ways but I would need to create a whole video to explain sorry and I don’t have one ready but will consider it next. Sorry I couldn’t help further. Dan
Holy shit I am mind blown lol
Thank you so much for this. Greatly needed and appreciated. Question, how would I enter a previous month/week into the data sheet and would that automatically migrate to the ref sheet? Is this even possible?
thanks so much! you sure can add it with much ease and you only need to add new purchases that didn’t appear in earlier statements. I cover this in more detail at 13:24 if you want to re watch it.
When making a reference tab and copying and pasting the last column in the data tab… It keeps giving me the #REF! and I don’t know what to do??
Hi Morgan. Did you paste “all” or paste values only only. I recommend watching that step in the video again as it should work.
This is awesome, thank you. So i could do the same thing with tracking CC expenses correct?
Hi. Thanks for watching. Yes you sure can do the same for credit cards. I use both bank accounts and credit cards combined.
First of all great tutorial!
How would you handle this if you had a budget account as well for fixed expenses? Would you simply extract from that account as well?
Thanks so much for your support and appreciate the comment. I made a seperate video on how to add a budget step by step to this this same template which you can find here czcams.com/video/DYsBcwJ9kAs/video.html .
Hi very well explained in simple way, I am in the process of making my budjet sheet but i have three differnet bank accounts with different data columns, finding it very hard to arrange them , any tip please ?
I am glad you found this helpful. I have this issue with one bank account and I copy and paste the cells one by one into the columns of the other accounts so that the data is recorded in Excel consistently. It is a little more work but not much more and the formulas will still work.
@@MoneywithDan Thanks for your quick response, I have manged to arrange the data in same sheet and differnt bank accounts. Bravo! One more thing i watched another video on how to make an interactive budget sheet where you can simply scan your receipts and record data. please try to make some video on that topic in future, thanks again mate
@@nido007 I am glad that helped and it is working. Thanks for your suggestion and I will take a look and see if I can create something for that!
Hello, I am using a MacBook. I don't know if it makes a difference, but I don't have the drop-down menu.
If you are using Excel for Mac then it shouldn’t make much difference.
Hello Dan, thank you for the instructions, however, I am struggling with one cell coming up as #N/A, I have read the message below and tryed this. Also when i add the next statement data, - most of the cells are coming ups #N/A - any further suggestions will be so appreciated 😀
Hi Lilly. I think you have coped the Formulas from the LEFT copy in stead of pasting value only when creating your unique identifier. Maybe double check that formula. Let me know if still having issues.
This is such a great video but I'm getting a #SPILL! error in my Category column on the Data sheet? I'm missing the col_index_no number i think; (lookup_value, table_array, col_index_no, [range_lookup). Can anyone help!? Much appreciated!!
Sounds like you have typed a wrong cell or range in your vlookup formula. Sometimes this happens to me when I accidentally forget to select the field to compare a table with. Maybe recheck that section of the video again and follow what I did.
Nice work.
How can I make the calender go from salary date to salary date?
Try adding the transaction date column in the pivot table and using the filter button.
Hi Dan , I’m having an issue with my pivot table. I keep getting an error message that says “the pivot table field name is not valid. To create a pivot table report you must use data organized as a list with labeled columns. If you are changing the name of a pivot table field you must type a new name for the field”
I have done all the previous steps and don’t understand
Hi Kelcie. You need to have a heading name at the top of all columns that you have selected to create a pivot table. Make sure you don’t have any blanks fields at the top row of your selection as this will cause this error. Hope this fixes this issue for you!
@@MoneywithDan awesome, I just had the same issue and trying to work out what caused it
@@barrys8503 thanks for reading through the comments to find the answer! Best of luck.
Excellent video!
I'd love to have access to my bank transactions through an API in case anyone knows one that works with the AUS big 4?
Thank you! Not sure what you mean by API?
@@MoneywithDan Way of running code that can automate a lot of this.
Finding that using =LEFT.. doesn't work for transactions that hide the date somewhere in the first 20 chars
for transferring my savings in and out it says the same thing in the description is there any easy way i can differentiate between savings transferred in and out
thank you for this vid it’s amazing
Thank you. Glad you liked the videos. Yes that is correct as transfers in and out are designed to net off to zero using this method as they are not true expenses.
To add more descriptive categories for transfers, I review my transfers category in detail each month and then manually override the VLOOKUP formula result for a particular line if the automatic category description that was assigned for certain transfer meant something unique on its own such as to pay or repay for expenses to an account I did not own (e.g. friends and family for gifts). However, I don’t have many transfers each month and manually over riding the formula is not a big time burden to warrant a complex formula to be written, and I usually just leave transfer to the default description of “transfer” since they typically net to zero each month. Hope this helps!
@@MoneywithDan ahhh thank you so much
My csv files downloads with two columns for transactions values. Such as, debits and credits are separated and all numbers are positive. How can I consolidate the values to one column of positive and negative values for easy computing?
Hi Jordan. There is a way to do this by adding formulas to summarise into one. I haven’t created a video to do this but I have created a template with a variation that does convert this that I have added to my Etsy store if you are interested with written instructions - a link to the store in included in the description of this video. Hope this helps !
This is awesome! Just what I was looking for, thank you so much! Just wondering, is there a way to have dropdown values in the category column in the ref worksheet? So instead of having to type them, you could select from a dropdown list. Furthermore based on that, would it then be possible to have another column for subcategory which would be a dependent drop list based on the value in the parent category column? For example, instead of groceries, the parent category would be "food and dining" with subcategories being "groceries", "takeout", "coffee", etc. In the ref worksheet, ideally, when you manually assign the category and subcategory once, all future transactions from the same merchant would be auto-updated. This is what I've been envisioning, but have been unable to fully automate so far. Would be great to see a video like this if it seems interesting to you :) Thanks again!
Thank you so much for your ideas and feedback. All of those things are definitely possible with an extra column and a few simple formula and settings. I would need to create another video to show how to do this as there are a number of steps. I was considering what video to make next and this has come very timely!
@@MoneywithDan I'm so glad to hear that! Very grateful your time and effort into this
Hey, great video! How do I work around my statement having credit and debit colomns?
Thanks for watching. There is a way to do it but you need to add an extra two columns but my video doesn’t cover that unfortunately. I created a template that solves the problem at my store if your interested and the link is in the description.
Ah no worries, so this will fix my issue? thanks heaps. Will there be more excell tutorials? they're gold.
@@eetswad no problem 👍. I don’t have any ideas for Excel tutorials at the moment. I am happy to hear any ideas or suggestions for videos if there is anything you’re interested in. Thanks!
@@MoneywithDan No idea what else I should know on excel haha, anything finance related really or trading. Your tutorials are very easy to follow.
What if you don't have any online banking transactions???
Good question. It works with card transactions as well as online payments if you pay with either debit or credit cards.
However, If you mostly withdraw cash from your account and pay your bill and make purchases at physical shops with that cash then this method, and most mobile budget apps for that matter, will not give you much detail and may not be valuable to you.
Hey Dan! Thanks again for sharing this great solution with us. But unfortunately I struggle with it. Easy to make mistakes and it's pretty difficult to convert the CIBC bank transactions. Can you recommend a 'offline' version (no information on cloud stored) that is a bit more user friendly? I am willing to pay if needed....
Just want say again that your tool is great! It's more me that is struggling.
Thanks!
Niels
Hi. Thanks for watching and sharing your feedback. Unfortunately Excel is the only offline option I can think of that allows automation of categories. Apps are popular for this reason but I understand your concern about not wanting your data somewhere in the cloud. Sorry I can’t help much more except suggest you try and watch the video again to keep learning and practicing with Excel.
@@MoneywithDan thanks for your fast response! Much appreciated. I gonna give it another try:)
I am have loads of problems with the copy and paste of unique values from the data page to Reference page ...it keeps coming up as ref # ..can any one guide me here?
Hi Nicolas, did you paste “value only” instead of paste “all”. When you paste you need to left mouse click and select paste only. I suggest rewatching that part of the video as I show the difference.
At 11:06, how do you move the income category to the top of the pivot table in google sheets? I am trying but unsuccessfully.
I select the corner of the category I want to move by clicking and holding with left mouse button, move it where I want and let go of the button. It just a drag and drop if you click on the right spot.
@@MoneywithDan that doesn't seem to work in Google sheets. Thanks for the reply Dan!
@@gavinmedeiros2076 that’s a shame. I am no that familiar with Google sheet functions unfortunately as I use Excel and some features are not compatible. Best wishes
Thank you so much for sharing this info! I do have a question though, is it possible to have 2 separate bank accounts together in this one Excel file?
Thank you for your kind comment!
Absolutely you can add multiple bank accounts and I do that too. All you need to do is paste the CSV data from the second account each month underneath the data from the CSV of the other account in the "Data" tab just like adding a second month of transactions.
The CSV file should include a column with the bank account name or number in each row. In my video the bank account details is in column D and I have bank account 1 & 2. When you include the data from the second account you can add "bank account name" to the pivot table in either the filter or the column.
I also prepared a follow up video to this one if you're interested to learn more here: czcams.com/video/YwmpFS8fBZ4/video.html
@@MoneywithDan
Thanks! I'll be sure to do that when I get to that point :)
I do have one more question if you don't mind answering. Do you know how to format or set a rule for the Transaction Value cells to be negative and positive like yours are in the video? The transaction data that I exported from my bank did not automatically format the Transaction Value cells to be negative and positive, but rather they are all positive with a separate column labelled Credit/Debit to identify them. Essentially I need to reformat them or set a rule to do something like "If Credit, then set as positive value" and "if Debit, then set as negative value" for the existing numbers. I've tried looking it up and watching other tutorials on conditional formatting, but so far I haven't found a way to do this specific thing. I'm not sure if this is even a conditional formatting matter haha.
@@robbiechan6941 Hi Robbie. Thanks for the question. There are a few ways to do this but the simplest way would be to add a new column with a new formula called “IF” formula.
To use an IF formula, in the first cell of the new column in a row that has data, the formula would look like this IF(A2=“debit”,-B2,B2) where A2 is the field that has debit/credit data and B2 is the transaction value in the bank statement data that has no negatives. Then ensure you copy and paste the formula down.
Make sure you give this new column with an IF a heading and include it in the pivot table range so you can add this column in the value field of the pivot table, instead of the other column with values that has no negatives. This should ensure you expenses are negative like mine.
Let me know how you go.
@@MoneywithDan It worked! Thank you for making it so comprehensible!
G'day Dan, how would I go about creating an automatic list of the transactions I haven't yet catagorized? Instead of manuallly sorting through ones that I might have done before, Thanks in advance.
Hi Blair. You will only need to add a category name when there is an error in the vlookup formula in the category cell. I show an example of this when adding a second month of data at 14:04 of this video. Hope this helps!
@@MoneywithDan I'm going through old statements and because I've copy pasted the whole colomn there is alot of repeat transaction names to sort through, I just highlighted it and deleted duplicates but now I cant see my income catagory, all the other catagories are there? I've hit refresh and tried removing some, still no show.
Thanks for prompt replies!
All good sorted it out just had to redo the data source, cheers!
I love your video, but am having trouble using the LEFT formula. It doesn't return any result, and just shows the formula in the cell instead of the result. If anyone knows why that might be, let me know. Otherwise, thank you so much for walking us through your process. It's really well done.
Hi. Thanks for watching. Did you put the = sign at the beginning?
The other issue may be because automatic formulas option may be turned off. It’s easy to turn on and you can find the steps using google.
Hope this helps.
@@MoneywithDan Thanks for your help! I'll try that. And yes, I was using the = sign at the beginning.
Edit: I had the "Show Formulas" button on, so switched it off and now have no issues with the LEFT function. Thank you for showing the way!
Good video I purchased your template have a slight issue I am using the data value 2 columns as I have debit/ Credit I think my columns are the wrong way round and at present I have category #N/A column E is credit values presumably I need to swap around or can I use column D ?
Hi Bill. Firstly thanks for watching and buying the template and for your question. The debit and credit columns shouldn’t matter as they all sum into column F “transaction value”. If category column is showing n/a it is because it can’t find a match in your “ref” tab and isn’t affected by the values you enter into column D to F. Hope this clarifies. Feel free to email me at enquires.moneywithdan@gmail.com
Hi Dan I still cant get pivot table to refresh it just has data from your template not what I have inputed
@@BillUptonITConsultant sorry to hear you’re having troubles. If your pivot table is still looking at my source data then it sounds like you need to update the data source range of the pivot table. I cover this part at 8:46 of my video and I suggest that you delete the current range in the pivot table and then reselect the range in the tab where your data is located in full just as I do in the video. I hope this resolves the issue.
I have a bank statement full of transactions that start with the day the payment was authorized rendering the unique identifier trick ineffective.
Thanks for watching. You can use a MID formula instead of the left formula to skip the starting number of characters that cover the date. Try googling MID formula.
5.40 When you copy and paste the references. I keep getting an error message "Invalid Cell Reference Error" any ideas??
Hi Bethany. Sounds like you need to paste numbers only and not paste all which would bring across the formulas which you don’t want. Let me know if that works.
@@MoneywithDan I worked it out, I need to type a fresh formula =left(sheet!cell,number of character) now let hope wth the altered formula the rest works 😃 🤞
Alternatively copy and paste "values and number formating" not just paste everything (that includes the formula which stuffs it up!)
@@bethanylinfitt6752 that’s correct. Paste values only.
I am trying everything you said but when I do de =vlookup for the categories not all the different categories from REF match with the ones from DATA TAB, It just avoids some of them and shows a category that is written more often, you know what I mean by this? and do you know how can I solvent this problem?
Hi Jose. It sounds like your ref data contains very similar characters and are not unique enough for the vlookup to get a clear match. Try adding more character size in the left formula in the data tab up to 25 or 30 and copying pasting that to your ref tab.
Hi Dan, I bought your template, could you please guide me how to refresh on Summary tab as I am unable to see the changes that I have made. The summary tab page is empty.
Hi there. Thanks for purchasing the template. If you click on the pivot table first (anywhere) and press the right mouse button a new menu should display. In this new menu click on “refresh”. Hope this helps!
@@MoneywithDan It worked, Thanks. This is the best Excel template, I have unsubscribe from PocketSmith and previously used many other online websites. For data safety and easy to use this is the best. Thank you for your efforts. Cheers
@@amcclinical1781 thanks so much for the kind note. I’m glad this has been helpful too you!
Hi dan, I cant get the pivot table to work. It keeps saying that the pivot field name is not valid
Hi there. It sounds like you have selected a column that does not have a name at the top of the column. If you enter some characters then it should clear the problem. Let me know if the problem persists.
@@MoneywithDan thank you! It worked! I have another question - so my credit card run from the 22nd of each month to the 22nd of the next month. So when I see the monthly view I would have to see 2 months worth at a time. Is there a way I could combine it so it reads month 1 to month 2 in the pivot table? Thanks
@@DiLLZGFX I prefer that view too. If your credit card extract file has dates in the data then this template should organise your data by month by default without having to do anything extra.
I set this up last month and it worked great but I ran into a snag this month when I had a new category of expense. When I refreshed my Pivot table it updated with a column for the new month and all the expenses in the old categories tallied, but nothing came up for the new category I made. Do you know how I could fix this?
I am happy to hear it is working for you and thanks for watching!
To fix this issue I have provided instructions in this video at the time stamp 14:16 and I explain what to do when adding the second month of data where new transactions appear. Basically you need to add a unique identifier each time there is a new transaction you have never had before as there is no formula reference.
Over a few months you will develop a list of unique identifies that is more complete for less frequent expenses that are either quarterly or annually and you will find that you will need to add fewer and fewer unique identifiers. This is the algorithm learning your expense patterns and the more data over a longer period of time then the less addition of unique identifiers you will need. Best wishes!
@@MoneywithDan Hi Dan - thank you so much for the quick answer, though that's not actually been what my issue is. I have created unique identifiers for all my new transactions, the issue has come when one of these is in an entirely new category that I didn't have last month. In the video the new transactions still fall under the existing Dining and Motor Vehicle categories, and that has worked fine for me. However I had one transaction that I had to make a new category for (local council fees), and this hasn't transferred across to the pivot table. Do you have any knowledge on how to fix this?
@@otizings there are few possibilities and one likely one is that the new category may be unselected. If you go to the pivot table and at the drop arrow at the top of the column, click on it, and see if your new category is selected with a tick next to it. If you deselect one item in the previous month, like blanks, then every time you add a new category it automatically defaults to unselected new categories in the pivot table after you refresh.
One less likely option if the pivot table range or formula range maybe excluding your new category so check your formula to see if they are working.
Hope this helps
@@MoneywithDan Thank you, that was the problem, I had unselected the blanks last month!
@@otizings great! I have made that mistake myself over the years. I am glad it was an easy fix then. 👍
Can you share a template of this for download please?
I plan to release a template in the new year. Thanks for watching and I will provide an update when it is available.
Am I able to do all this into one sheet? Like the category part and the statement transactions?
Yeah sure. It looks a little messy but it can be done. Best of luck!
@Money with Dan is there a way to do subcategories?
@@MariaAlvarez-xn2lv yes there is. I have another video that uses the same excel template but also walks you through adding a subcategory. You can find the video on YT here czcams.com/video/VwqSAiqMyQc/video.html . You can fast forward to 16:03 where I add talk about “tax type” and refer to private versus business expenses, which is essentially adding subcategory to my template and you can give it any description you like. Hope this helps
@@MoneywithDan thank you so much! Once I'm done figuring out which category some of these transactions go to, I'll check it out lol
And what if you have no on line banking transactions???
Do you mean when paying with cash instead of card or electronic transfer? If you withdraw cash from your account at a store, bank machine or at the bank teller, then that would appear as a transaction on your account and you can can give the withdrawal transaction a category name if you use that cash withdrawal for a specific purpose, like pay for groceries for example.
However, if pay mostly with cash for lots of different types of transactions and rarely/never with a card then this method may not be the best option for you.
Great video, really helpful.
I got one issue when adding a new month, I get #N/A in vlookup on every single new expense I add. At first, it looked to be because every transaction had a unique Visa ID, but it happens even when I narrow the number of characters so that the ref. is the exact same. Got any tips on this?
Keep up the good work.
Thanks for sharing! it does sound like the type of transaction is not unique enough to get an exact match due to unique transaction numbers you get with this type of payment. You could try lengthening the number of characters in the LEFT formula and also changing the VLOOKUP formula to not look up an exact match by changing it to TRUE at the last part of the formula. Hope this helps!
@@MoneywithDan Hi Dan, thanks for sharing this. I'm having the same problem, my credit card payments all have a unique ID but when I switch the formula to true, it starts to confuse all of the categories I created. Is there any way around this?
@@williambroverman2856 hi there. If you have a unique ID created then you should enter FALSE in the Vlookup formula (instead of TRUE) as I do in the video to only look for exact match. Did I understand correct?
Or do you mean you do not have a unique ID? If no unique ID is it because there is a date at the beginning of the text? If so I suggest using a MID formula instead of a LEFT which essentially allows you to skip characters that are not constant.
@@MoneywithDan Hi Dan, thanks for the speedy response.
When I entered the first month's data and created the unique category ids using the left formula everything worked out perfectly. When I added the second month of information the left formula didn't recognize any of the new transaction descriptions.
There is a big jumble of numbers at the beginning of each transaction description that isn't a date or a constant id number from my card.
I tried changing the left formula to TRUE to see if it would pick up a pattern from the jumbled numbers and sort them into the categories I created.
Based on what I've read in the comments (and you just suggested) I think I'll have to figure out the MID formula. This is all new to me and it's hard to start learning from scratch and then apply it in a new scenario. Any tips on how to get it working just for this?
I just bought your online template. Hoping it makes things simpler. If not, still happy to support someone who makes themself so available to everyone who has reached out for support.
All the best
@@williambroverman2856 thanks so much for you support. I think you will benefit with the MID formula for your situation. Many banks add number at the beginning of the text for things like dates making it hard to be unique. Hopefully the sequence is predictable so you know how many characters to skip constantly. It may not always be perfect for irregular expense but you should be able to automate most of the regular transactions. Best wishes!
Hi Dan, great video thanks! However I have problem with the CSV data from my bank. I downloaded it, and the description column has date and card number in it, preceding the store name, despite there being a separate (LH) column with date too. So the "left" formula will bring in this variable data, which will be different every time, and can't be used for category allocation. I live in the UK, maybe banks here just don't send the pure description over?
Hi Carys. That’s easy fixed, you just need to adjust the LEFT formula to bring across more characters until it is unique. I defaulted to 25 but you go as high as you need to. Hope this helps!
@@MoneywithDan Hi Dan , thanks so much for your reply. The string will never match to future transactions though, however long, because it contains the date?
@@carys8027 true. Instead of using a LEFT formula you could use a MID formula which is similar but it will bring across fields after a number of characters like the date. I had a similar question from a viewer and it fixed the issue. I don’t have a video about this formula but this link should explain it exceljet.net/excel-functions/excel-mid-function . You would just use the MID instead of LEFT and everything would work the same. Hope this helps
@@MoneywithDan Yes. The MID formula worked for me - thank you!
Great to hear! Thanks.
at 6:42 couldn't you just delete the duplicate first???
You can if want. The vloopup will always select the first from the top and ignore the lower ranked ones.
Why have you not deleted repeating values on the REF tab? What you have done makes no sense. The rest is OK
Thanks for watching. That would be adding an extra step to an already very long video. I chose to keep it simple for my viewers and avoid further complication.
If you want delete duplicate cells for a cleaner ref tab then there is a button called “remove duplicates” under the “Data” ribbon at the top.
the problem when you have 400 transaction with different name XDD
I haven’t come across this many identical transactions for personal transactions accounts before where they each for different different category types. Usually multiple transactions with the same description have the same category name so it hasn’t been an issue before.
Do the 400 transactions have exactly the same description? I assume you have tried increasing the number of characters in the Left formula until they are unique as I show in my rent example within the video. I also assume the 400 transactions are for completely different categories and you want to give them seperate category names.
If the transactions are bank transfers you could give them a different description when you transact so that they are unique in the future.
Hey I’m having some trouble on the pivot table the problem is when I try to put the amount/value of transaction it goes in there as the count of the category. EX if I have Gas station as my category the total transactions next to it should be 100 dollars but instead it says 4 (because of how many times it was categorized as gas station). We can discuss more maybe on your Instagram if that’s fine
Hey there. No problem and it’s a common problem. in the pivot Table you need to change the count to sum function. Try following following this link with step by step details and screenshots excelinexcel.in/ms-excel/formulas/count-to-sum-function/ .let me know if you still get problems!