Hi Kenji, I am a great admirer of your videos. I have seen so many videos of Excel of various UA-cam instructors, but I haven't found a video suit to my requirement. I receive both invoices and packing list in excel format from China. Following is an example but not the actual one:- Invoice Apple Juice Pineapple Juice Mango Juice Orange Juice Grape Juice Packing List Apple Juice Keels 12956 Pineapple Juice Keels 12957 Mango Juice Keels 12958 Orange Juice Keels 12959 Grape Juice Keels 12960 Apple Juice 1280 Cargill Pineapple Juice 1281 Cargill Mango Juice 1282 Cargill Orange Juice 1283 Cargill Grape Juice 1284 Cargill Apple Juice Laughs 1123 12 Pineapple Juice Laughs 1123 13 Mango Juice Laughs 1123 14 Orange Juice Laughs 1123 15 Grape Juice Laughs 1123 16 Now you may have noticed that in the packing list the products comes with extentions such as the name of the production company and a product ID.. I wanted to eliminate the company name and the product ID from the list and produce a new list while keeping complete item names in the packing list. Is this possible in excel. What I do now is "TEXTSPLIT" the description. Delete the unnecessary data manually and join the Description again to get the list as I wanted.
Great model! How to include or keep record of the ballance of your different bank and different creditcard accounts and your cash? I have looked through a lot of your videos without finding any video covering this. Hope you make it in the future.
Thank you so much for making this video! It helped me a ton. Let me tell you about my process... I have Business drawings and not Salary, and I take drawings as much as I need. So, technically i have 0 savings. I didn't create the Savings account, but I've created "Other" Category instead of Savings. It includes loans i have given to others which they'll give me back, and family support which doesn't classify as personal expenses.
Hey @KenjiExplains, I would love to see you add a section on debts. I have student loan payments to pay off and I'd really love an integrated way to track and input this in the spreadsheet. None of this budget tracking or excel spreadsheets come easily to me so I love your free downloads and video tutorials on them. Please include a debt payment (and tracker) into your spreadsheets for those of us with debt! Even ones to pay down a car, or mortgage on a payment plan. It would be such a helpful tool to include that I feel is really missing since most average people have to use payment plans to afford larger items/expenses.
Great information as usual. Question. I presume you can add to the income/expense table. I did; however, when I copied the formula to Expenses and Savings, I get 0 for expenses for the months. Any idea?
I had the same issue. However, when I checked I made a spelling mistake. I used 'Expenses' as my Header in Dashboard sheet instead of 'Expense' as in the Entries sheet.
Thank you Kenji. The only issue I see is this only works for 1 year. If you start adding another year, there is no way to separate the data. Is there a quick fix for this vs saving each year as a separate file?
Brother, your video was very helpful, but I am struggling to match some of the formulas. Could you kindly share your template with us as an example, where you have already worked on it?
Hi Kenji, this is another amazing vedio. Can you make a video on transferring 12 months' credit cards pdf statements combine with bank statements to excel? I transferred the file by each month then copy paste to one table. Maybe you have better way to do it. Thanks.
Thank you so much for this video! I have a question, I’m a student studying abroad and use two currencies-JPY and EUR. My main currency is JPY, but there are times I use my EUR debit card for purchases, and since I’m spending in JPY, I find it much easier to track everything in one currency. (in my case JPY) In the past, I’ve manually converted my home currency (EUR) to JPY, but the exchange rate fluctuates, making my balances inaccurate over time. I’ve tried different Excel formulas to make this process automatic, but I keep encountering errors. Could you make a guide on how to set up a spreadsheet or formula that handles currency conversion dynamically, considering the fluctuating rates?
Great efforts Kenji. Love your videos man! is it possible to link a google forum for the transactions on Phone and then they would be automatically exported to Excel? I feel that would be more convenient instead of manually inputting the transactions on a laptop
Could you do a video that goes a step further by making categorizing the transactions dynamic? Maybe you could just download from the bank or account and put it into a folder to add it to the excel, if possible?
Hi Kenji, thank you very much for this useful video! I will definitely use it for my personal projects :) ! Just one thing, I was testing it out by myself, but I think I found a problem with the EOM formula.. because the MONTH formula will produce a month number (e.g. 7 for July) then the EOM formula would take this 7 as the 7th of Jan 1900.. thus will always yield 31/01/1900 as the EOM.. so all percentage of month lapsed will be based 31.. won't work for February for example
Hi, I have set up your template and modified it some, to include extra sheets...for each Month, i.e. January Entries, February Entries, March Entries and so on. I have managed to get formulas in place for the "Year To Date Monthly Breakdown", but I am having trouble getting a formula in place to get the 'Year to Date Breakdown'. I cannot seem to enter the correct formula to consolidate multiple sheets of data. I have only tried the Income side and nothing seems to work. I am fairly sure I am just slightly off, but have tried numerous variations. Can you offer any input on a formula so that I can continue setting this up? Once I get things set up for my "Personal Bookkeeping System", just for me and my husband, as we are retired now and I am leaving Quickbooks behind, I would love to show it to you, as your template is the main foundation for it. Thank you. Regards, Cheryl
Help. Im getting stuck on this walkthrough. When i put in the sumifs formula, I recieve a 0 value for january. Ive only put in two entries for salary and 1 entry for expense.
I fixed mine, it seems like excell has changed the uses of "," and ";"... So instead of saying "=TEXT(B5,"mmmm")" try using a semicolon instead where the comma should be like: "=TEXT(B5;"mmmm")".... This did the trick for me
I have a list of data that is about 2400 seconds long, and includes data up to the hundredths decimal place. Is there a function or filter I can use in order to just keep the integer values and delete all entries with decimals?
2:22 Hello, I hope you can see my comment here I'm doing exactly what you are doing for the xlookup command, but I always getting #name error. I tried many videos for solution but none worked. Please advise. Thank you for your videos.
I’m using a Swedish version of Excel. Converting date into text of a month does not work in the same way as you show in the video. The Swedish standard is: 2024-01-01. I can do: =MONTH(B5) which shows as 1, 2, 3 etc, but show month in text, I can’t do it?
I fixed mine, it seems like excell has changed the uses of "," and ";"... So instead of saying "=TEXT(B5,"mmmm")" try using a semicolon instead where the comma should be like: "=TEXT(B5;"mmmm")".... This did the trick for me
Hello- my result in cell C11 is a 0 (zero). I cannot see why, I've debugged the formula, rewritten it a few time to no avail. Any suggestions? Thanks- WS
I am far from an Excel Wiz but you may be able to group all the 2024 tabs manually and then hide them all and make some changes to the dashboard to switch between years. I could be wrong or there could be an easier way lol
In this video, two corrections are needed: The SUMIFS function was used unnecessarily for summing the amount for income and expenses by category. Instead, the SUMIF function should be used. The formula used for calculating the percentage of the month is not accurate. It should be corrected to: =DAY(C4)/DAY(EOMONTH(C4,0)).
have you managed to find a solution? the same error and I really can't understand where the mistake is. I see the other videos and i see them write the same exact command, it work for them but not me!
Hey Mrs Vlasco .. after searching for little bit more, the only thing that the it could be reasonable for this error that is XLOOKUP function is not identified in our excel system. The think that give me the hint that in most videos I watched that once they write XLO .. the excel list will automatically identify that the user is trying to write XLOOKUP. In my case and yours we don't have that function unfortunately. Only solution worked for me that I tried LOOKUP function and it completely worked for me. Hope you find this useful.
This function does not work for me is it because I am using excel 2010 ? (=XLOOKUP(E5,Table2[Category],Table2[Income / Expense]) All i get is #NAME? any help please
For that there are professional software applications that can pull the data directly from the bank. I think because of the safety concerning privacy and security this possibility through an API will not be given away by the banks. You can however work with the data you downloaded from your bank. Make an automated import and conversion proces for your financial workbook
@@joukenienhuis6888 I understand. It’s not a major inconvenience to export a new file every time you need a feed but a live link would work wonders I know that Xero and other accounting software allows you to do this if my memory serves me correctly Naturally, I’d think any user who requests their banking provider to provide said means is a sophisticated user and should have some awareness of security risks. And the biggest risk is having you transactions information stolen. Sloppy work has to have been done on the API for someone to potentially gain your login information, take your money etc
Wow Mrs Mary Gail really helped me for real, trading with her has been going smoothly for me as I've been able to raise over almost four btc when I started at 10k in my first deposit
HEY Kenji , Thanks for the video . Can you tell me please why my sumifs formula is returning to zero though I am doing everything accordingly ? @KwnjiExplains
🚀Get the FREE Data Analytics Guide from Hubspot: clickhubspot.com/kgv
Great video Really I like it.
Hi Kenji,
I am a great admirer of your videos.
I have seen so many videos of Excel of various UA-cam instructors, but I haven't found a video suit to my requirement.
I receive both invoices and packing list in excel format from China. Following is an example but not the actual one:-
Invoice
Apple Juice
Pineapple Juice
Mango Juice
Orange Juice
Grape Juice
Packing List
Apple Juice Keels 12956
Pineapple Juice Keels 12957
Mango Juice Keels 12958
Orange Juice Keels 12959
Grape Juice Keels 12960
Apple Juice 1280 Cargill
Pineapple Juice 1281 Cargill
Mango Juice 1282 Cargill
Orange Juice 1283 Cargill
Grape Juice 1284 Cargill
Apple Juice Laughs 1123 12
Pineapple Juice Laughs 1123 13
Mango Juice Laughs 1123 14
Orange Juice Laughs 1123 15
Grape Juice Laughs 1123 16
Now you may have noticed that in the packing list the products comes with extentions such as the name of the production company and a product ID..
I wanted to eliminate the company name and the product ID from the list and produce a new list while keeping complete item names in the packing list. Is this possible in excel.
What I do now is "TEXTSPLIT" the description. Delete the unnecessary data manually and join the Description again to get the list as I wanted.
This helped me so much. I even created a column to add which bank/wallet it belongs so I could track cashflow. Thank you so much!
Great model! How to include or keep record of the ballance of your different bank and different creditcard accounts and your cash? I have looked through a lot of your videos without finding any video covering this. Hope you make it in the future.
I Appreciate Yur Efforts and your Kind Support and Knowledge Spreading to the people God Bless You
Amazing video. The simple but effective Finance Tracker im voing to build one for myself. Thanks for this video
Simple, and really useful. Thanks a lot!
Thank you so much for making this video! It helped me a ton.
Let me tell you about my process... I have Business drawings and not Salary, and I take drawings as much as I need. So, technically i have 0 savings. I didn't create the Savings account, but I've created "Other" Category instead of Savings. It includes loans i have given to others which they'll give me back, and family support which doesn't classify as personal expenses.
Your video help me a lot brother.
Keep it up
Glad to hear that!
Hey @KenjiExplains, I would love to see you add a section on debts. I have student loan payments to pay off and I'd really love an integrated way to track and input this in the spreadsheet. None of this budget tracking or excel spreadsheets come easily to me so I love your free downloads and video tutorials on them. Please include a debt payment (and tracker) into your spreadsheets for those of us with debt! Even ones to pay down a car, or mortgage on a payment plan. It would be such a helpful tool to include that I feel is really missing since most average people have to use payment plans to afford larger items/expenses.
This works awesome! After building it, I’ve tried adding a monthly category totals area but can’t figure out a formula to make it work.
I have been looking forward to this Mr Kenji. Thank you so much
great video. very nicely demonstrated in a way i can follow 10/10
This is what I was looking for. Thank you
My favourite UA-camr Kenji the best.
Wow, that's really useful! Thanks for teaching me something new. Keep rocking it!
Got it done and it is pretty good. I would like it better if I could do plan vs actual comparisons. Any suggestions on how to incorporate this?
This dashboard is great! I only added one more section which is "investment", works perfect now! Thank you :)
Thanks a lot
Great information as usual. Question. I presume you can add to the income/expense table. I did; however, when I copied the formula to Expenses and Savings, I get 0 for expenses for the months. Any idea?
I had the same issue. However, when I checked I made a spelling mistake. I used 'Expenses' as my Header in Dashboard sheet instead of 'Expense' as in the Entries sheet.
@@VishnuVardhan-sj6nd Thank you! That was the issue.
@@VishnuVardhan-sj6nd Thank you so much for this information. I was starting to pull my hair out LOL
This was awesome as usual. Thank you Kenji
This video will definitely help my small business
Thank you Kenji. The only issue I see is this only works for 1 year. If you start adding another year, there is no way to separate the data. Is there a quick fix for this vs saving each year as a separate file?
Brother, your video was very helpful, but I am struggling to match some of the formulas. Could you kindly share your template with us as an example, where you have already worked on it?
Thank you very simple and very useful.
Hi Kenji, this is another amazing vedio. Can you make a video on transferring 12 months' credit cards pdf statements combine with bank statements to excel? I transferred the file by each month then copy paste to one table. Maybe you have better way to do it. Thanks.
Great video honestly, is there anyway to also include subscriptions and when those payments will go in this spreadsheet template?
Thank you so much for this video! I have a question,
I’m a student studying abroad and use two currencies-JPY and EUR. My main currency is JPY, but there are times I use my EUR debit card for purchases, and since I’m spending in JPY, I find it much easier to track everything in one currency. (in my case JPY)
In the past, I’ve manually converted my home currency (EUR) to JPY, but the exchange rate fluctuates, making my balances inaccurate over time. I’ve tried different Excel formulas to make this process automatic, but I keep encountering errors.
Could you make a guide on how to set up a spreadsheet or formula that handles currency conversion dynamically, considering the fluctuating rates?
Simple amazing personal tool! Many thanks.
Glad you like it!
Great efforts Kenji. Love your videos man! is it possible to link a google forum for the transactions on Phone and then they would be automatically exported to Excel? I feel that would be more convenient instead of manually inputting the transactions on a laptop
I don't see any updates in dashboard for February entries.
Hi Kenji, thanks for the informative video. One question, how is the budget tracking done here?
Could you do a video that goes a step further by making categorizing the transactions dynamic? Maybe you could just download from the bank or account and put it into a folder to add it to the excel, if possible?
Hi Kenji, thank you very much for this useful video! I will definitely use it for my personal projects :) ! Just one thing, I was testing it out by myself, but I think I found a problem with the EOM formula.. because the MONTH formula will produce a month number (e.g. 7 for July) then the EOM formula would take this 7 as the 7th of Jan 1900.. thus will always yield 31/01/1900 as the EOM.. so all percentage of month lapsed will be based 31.. won't work for February for example
I rewrote like this and it works =DAY(C4)/DAY(EOMONTH(C4,0))
Great video Kenji thanks a lot !
Im trying the sumifs formula to calculate the Monthly Category breakdown but its not working
I would prefer the category breakdown to be monthly. How do I approach that?
I'm having trouble in the sumifs part pls
Help the value comes to zero even though I have some income in January
@@yorkbv2107 no I left it at that. That was the only part I couldn't solve.
Banking apps allow you to download excel files of transactions so this allows data to be included. Alternative is manual inputting.
🖐
Nice video 😊 thanks a lot I’ve learned a lot from you 🎉
Nice video! 👍 can you make more videos on careers in business and finance?
Greetings sir,
Im unable to download the Excel
How do i go about it.
Thanks for your usual promptness
i was unable to download as well
how did you make the category table? Is it just a normal table?
hello, hope you're doing well.
what if my excel doesn't have XLOOKUP, how could I suppose to set the formula?
thank you very much!
Help full video.
Thank a lot brother. :)
Would you be willing to share your built worksheet? I'm a Baby Boomer and the formulas are a challenge. Your completed ws would be perfect. Thank you!
Kenji what's your desk setup?
This is super helpful!! Thanks
Hi, I have set up your template and modified it some, to include extra sheets...for each Month, i.e. January Entries, February Entries, March Entries and so on. I have managed to get formulas in place for the "Year To Date Monthly Breakdown", but I am having trouble getting a formula in place to get the 'Year to Date Breakdown'. I cannot seem to enter the correct formula to consolidate multiple sheets of data. I have only tried the Income side and nothing seems to work. I am fairly sure I am just slightly off, but have tried numerous variations. Can you offer any input on a formula so that I can continue setting this up? Once I get things set up for my "Personal Bookkeeping System", just for me and my husband, as we are retired now and I am leaving Quickbooks behind, I would love to show it to you, as your template is the main foundation for it. Thank you.
Regards,
Cheryl
Help. Im getting stuck on this walkthrough. When i put in the sumifs formula, I recieve a 0 value for january. Ive only put in two entries for salary and 1 entry for expense.
Me Too
Somehow my Excel does not work with the "=TEXT(B5, "mmmm") setting. Pops an error message. :
same...
I fixed mine, it seems like excell has changed the uses of "," and ";"... So instead of saying "=TEXT(B5,"mmmm")" try using a semicolon instead where the comma should be like: "=TEXT(B5;"mmmm")".... This did the trick for me
Excellent Brother
Useful for me , thanks
Great job Kenji
Thanks buddy❤
I would like a video on the least useful things college taught you based on your career choice.
I have a list of data that is about 2400 seconds long, and includes data up to the hundredths decimal place. Is there a function or filter I can use in order to just keep the integer values and delete all entries with decimals?
awesome, thx for sharing!
2:22
Hello, I hope you can see my comment here
I'm doing exactly what you are doing for the xlookup command, but I always getting #name error. I tried many videos for solution but none worked.
Please advise.
Thank you for your videos.
Hey I was having trouble with it as well until I realized i did not include a comma(,) after selecting E5
can you do a divident tracker too?
maybe in the future!
Yhat would be awesome @@KenjiExplains
Does this work on a Mac with Numbers?
I wish you start this tutorial from scratch. Because many of us don’t know how to create the category & income/expense.
Bro it’s available to download lol
I’m using a Swedish version of Excel. Converting date into text of a month does not work in the same way as you show in the video. The Swedish standard is: 2024-01-01. I can do: =MONTH(B5) which shows as 1, 2, 3 etc, but show month in text, I can’t do it?
I fixed mine, it seems like excell has changed the uses of "," and ";"... So instead of saying "=TEXT(B5,"mmmm")" try using a semicolon instead where the comma should be like: "=TEXT(B5;"mmmm")".... This did the trick for me
how do you create the headline "personal finance"
Hello- my result in cell C11 is a 0 (zero). I cannot see why, I've debugged the formula, rewritten it a few time to no avail. Any suggestions? Thanks- WS
Great vedio
Thanks but could you tell me the difference between xlookup and vlookup
thanks! I have a video about that here: ua-cam.com/video/3MdPSHkyfdI/v-deo.html
thanks so much
Thanks buddy
This is Art
it says my expenses and income are the same? fix for this?
What about rolling over into 2025 and we still want to input our income/expenses on the same tab?
I am far from an Excel Wiz but you may be able to group all the 2024 tabs manually and then hide them all and make some changes to the dashboard to switch between years. I could be wrong or there could be an easier way lol
In this video, two corrections are needed:
The SUMIFS function was used unnecessarily for summing the amount for income and expenses by category. Instead, the SUMIF function should be used.
The formula used for calculating the percentage of the month is not accurate. It should be corrected to: =DAY(C4)/DAY(EOMONTH(C4,0)).
Love that dashboard but some formulas did not work
The =XLOOKUP(E5,Table1[Category],Table1[Income / Expense]) at minute 2:35 did not work for me
have you managed to find a solution? the same error and I really can't understand where the mistake is.
I see the other videos and i see them write the same exact command, it work for them but not me!
Hey Mrs Vlasco ..
after searching for little bit more, the only thing that the it could be reasonable for this error that is XLOOKUP function is not identified in our excel system. The think that give me the hint that in most videos I watched that once they write XLO .. the excel list will automatically identify that the user is trying to write XLOOKUP. In my case and yours we don't have that function unfortunately.
Only solution worked for me that I tried LOOKUP function and it completely worked for me.
Hope you find this useful.
where to download excel template
In my first month, I typed exact formula but I didn’t get month as january
Pls how can i download the template
This function does not work for me is it because I am using excel 2010 ? (=XLOOKUP(E5,Table2[Category],Table2[Income / Expense]) All i get is #NAME? any help please
Banks need to introduce an API that would allow you to pull the entries from you account through a refresh table
For that there are professional software applications that can pull the data directly from the bank. I think because of the safety concerning privacy and security this possibility through an API will not be given away by the banks. You can however work with the data you downloaded from your bank. Make an automated import and conversion proces for your financial workbook
@@joukenienhuis6888 I understand. It’s not a major inconvenience to export a new file every time you need a feed but a live link would work wonders
I know that Xero and other accounting software allows you to do this if my memory serves me correctly
Naturally, I’d think any user who requests their banking provider to provide said means is a sophisticated user and should have some awareness of security risks.
And the biggest risk is having you transactions information stolen. Sloppy work has to have been done on the API for someone to potentially gain your login information, take your money etc
where to download the templet?
unable to open download in excel 2007?
Tough for new comer...
Wow
thanks!
How does this trading stuff work? I'm really interested but I just don't know how it go about it. I heard people really make it huge trading
Please how do I contact her?
I strongly agree with you guys, Mary's team is the best. My funds are safe and ROI is always sent directly to my external wallet exchange monthly
I really appreciate your feedback. Thanks
Wow Mrs Mary Gail really helped me for real, trading with her has been going smoothly for me as I've been able to raise over almost four btc when I started at 10k in my first deposit
She is a scammer, she ran away with my money😅
HEY Kenji , Thanks for the video . Can you tell me please why my sumifs formula is returning to zero though I am doing everything accordingly ? @KwnjiExplains
Me too though it worked for certain months