Manage Your Expenses Seamlessly with the Excel Bill Tracker for Enterprises

Excel bill tracker for enterprises

how to create a daily Financial tracker here in Excel with a couple of different and interesting features such as for example whenever you type in here the date the month is going to automatically change for you based on the date that you put before and here in the type in the description in the type you're going to have either expenses or incomes and the list in the description change based on the type to match the type option that we selected before so as income you're going to have salary side hustle orders and if you change expenses you're going to have a different type of description so you're going to learn how to create it here with Piel another thing that is very interesting is the dashboard itself where we can visualize the data with more preciseness and we can make a different analysis too such as see each one of the Ms and the incomes the expenses and the total balance for each one of the months that we have in the cash flow in other words basically we have here an addition with all the values but foll a certain criteria that is income in the month January February and on and on or expenses and the months and so let's take a look here on how can we create it step by step from scratch let's go now with a new blank workbook the first thing that I want you to start with is creating here the title for my ship so let's say cash flow or you can use actually a different name if you think it suits better with the thing that you want to create here now I want to input here the headers that are going to use to creating the table where we going to input the data that corresponds to to the incomes and outcomes so let's say in the cell B3 I want to start with the date and then I'm going to hit tab because instead of using the cursor to select a specific cell or the next colel if you hit tab it's easier you just need to press one btle okay just tab date and then month to exract only the month from a date that we inputed for example and the month is going to be very important in order to create the dashboard and to make analysis now the third information here is going to be let's say the type it can be either income or expense the next column I want to use description and then the value of the the thing that you expand or have as income and then a note if it's something important then you want to input here a note as the date let's start now with the date and fil in with a couple of informations just to see how it's going to work the date I want to input here January 1st of 2024 and then I'm going to press enter okay so this is how we going to inut the date it's perfectly a now with the month I want to extract the month from the date that I type in before so as I have it here January 1 of 2024 I have basically three different informations the month the day and the year but I want to use only the the month and to do it there is a function in Excel called it equal sign text text function let me double click here one two the text function basically can only have two different arguments the first one is the value where you can select the date just click over the cell here in the left and then comma now we can input here a format to the text so let's say as we have the complete date uh the month the day and the year I want to use only the month so I can open quotations here and then M mm three times the letter M close quotations and then close parenes I can press enter and I'm done whenever I change here the date the month is going to be automatically update for me only displaying the month that corresponds to the current date that is here to the left so let's say I want to change this date I can use um March 2nd of 2030 let's say enter and as you guys can see here the month is going to be automatically change for me whenever the date Chang as the type I'm going to have only two different options or it can be income or it can be expenses let's say and in order to make it faster and instead of manually typing any any anytime the same thing we can create here a data validation or basically a list where you can click in the sell and select one of those two options income or expenses so let me click here in the first cell and then I can go to data and here to the right I have data tools and this little icon here is where I'm going to click data validation here instead of using allow any value I'm going to change and use list and as the list I can have any type of different options such as for example income and then comma my second type of uh option here is going to be expense and if you have a third option a fourth option and on and on you can keep continues to add the options here and then use comma the third one one comma the the fourth one and on and on okay so you basically need to separate those options with the comma let me press here okay and we done so here I'm going to have two different types of options it can be income or expense if I have let's say an income I'm going to have a different type of description here it can be income uh maybe salary or wage or a side hustle side job or whatever and if I have let's say expensive it's can be a expense for groceries rent car or heat gas water pet and on and all okay so we have a lot of different options and in order to create this dynamically let's say I select here incomes in the description instead of manually typing the options I'm going to have a list to so I want to create here also a list with the options but the options need to match with the type income or expenses and in order to make that let's go here to to the bottom of this spr sheet and I'm going to click here in this plus sign new shet in order to add another sheet the first one I can right click here and click rename and we can again type here cach flow enter and the second one right click rename I can type in lists enter and as I have the lists here I can basically in the first cell create the title that corresponds to list and maybe here in the CB tree I can type in income income I think it's good and in the cell D Tre I'm going to input here expense okay now you need to list all the incomes and expenses that you have so let's say as income is just an example okay I have salary or wage you can use whatever you want to use side hustle or side job is the same thing and ERS as expenses just a couple of examples here this can be rent this can be car insurance or car F or car maintenance or on and on but I just going to leave car and then I have groceries or I'm going to type in food because it's easier to type in and also I have heat gas water and fat and others let's say okay so a lot of different options this is the list that we create and in order to match the type with the list that we can have in the cash flow we need to transform uh the lists into a table is of course there's a lot of different ways to match those lists this is a way that I like to do the most okay so transform first both of the lists that we have into into a table so let me select here everything and then I go to insert table and it's very important to make sure you check this option my table has headers yes so let me check here and then click okay now we're going to do basically the same thing for the second list in insert table my table has headers of course and then okay I can also change the colors so let's say income I want to change the table design and use a green one like this and as the expenses I want to use the table design as the orange one like this and we can also click here in in this in the column B for example or or specific in between the column B and C click hold and drag to the right to make sure we can increase the size of the column B I'm going to do the same thing here for the column D but uh let's match the size of uh both columns B and D let me click in the column B and now I'm going to press and hold the control key and also select here the column D now in between the column d and e I'm going to click hold and drve to the right that way I'm make sure I can match both sides of the both columns okay I say the First Column here I can make it a little smaller such as two in the width or it can be 1.86 okay now let's come back here to the cash flow and now we can create here the lists based on the the type and to do it we're going to use again a different function here that is equal sign the indirect function this function right here this function can help us to make the list match with the type but before we do it let me come back here to the list and here we need to change the name of both tables the the first one the second one basically we just need to select every theme in the first table and go to table design and here to the left table name within this bar instead of using table one I'm going to use income and then click out the selection the second table can has a name such as let's say expense and with those chain done now we have as the name of the first table income and the second one expense we can easily create the data validation here in the cash flow to match with the type so take a look here let me click in data and then data tools data validation as the allow any value I want to use list and in the source I want to input here equal sign inir function and then open parenthesis and I just need to select this cell right here where I have expenses just click here and yeah something very important here we need to get rid of this dollar sign because both dollar signs okay because I don't want to lock the cell in the same position I don't want to lock the reference so let me read it off here this dollar sign or to make it much easier instead of select the cell itself we can input the the name of the cell here manually that is expense is the cell D for so here within the parenthesis D for and then close parenthesis and then I'm going to press okay now whenever I have here this can be either expenses or income the description is going to change it's going to change based on the the type income I'm going to have a list and as the expense I'm going to have another type of list easy easy so easy easy as that okay so let's say expense car and as the value $100 and to make it the value the number in the correct format I can click in the the cell and go to the Home tab and select here the dollar sign to format as dollar or all other different cenes that you want to use here as the note I can inut here anything so let's say let me change here instead of using car I want to use food and as the note you can type in here a more specific description such as if you wish to okay you don't need to do the note it's just a a column that you can or not F in so let's say I bought I snack blah blah blah and Etc okay so just something to remember you why you expand or receive uh the value that you inut here now the last step that we I want to create here in the cash flow is make sure I going to make this First Column a little bit smaller such as 1.86 and all those other colors uh we also going to change the size but before we do it let's transform the data that we have here into a table so we're going to select everything click insert table make sure you select the this option my table has headers click okay and you can also change the colors theyel design and choose here a different one I'm going to stick with this this one right here the date I'm going to increase a little bit the math like this type maybe a little bit more description I'm going to make it the width equal to 20 and both here I'm going to increase a little bit too like this I think it's good now just to the Final Touch here I'm going to select basically everything and go to home and centralize and align in the middle everything just to make it even evenly now we done whenever you insert now a new row with new informations January 2nd of 2024 look at what's going to happen enter the month is going to be automatically upate here for you with the function that we did before also the data validation is going to be here and also the description the data validation that we have here also is going to be waiting for you here because whenever we create a table here in Excel whenever you add new rows new informations all the functions and formulas is going to be add automatically for you now it's time to create the dashboard itself and to create the dashboard I can click here in this plus sign to add a new shet let me click in the sheet click hold and dag to the right just to change here the order and organize it better let me right click now and rename this is going to be my dashboard and of course here the title is going to be also dashboard and I'm going to make sure I change this colum size to 1.86 okay now in the cb3 or before we start with dashboard I think there is some important informations to be visualized in the dashboard suest let's say how much income you have in each one of the months how much expenses you have and things like that okay just basic and simple things because with this catch flow of course we can input all the data that you you're going to have but uh we can't see pretty much anything we can't make analysis but with the dashboard we can so in the dashboard we can start with month and then I'm going to have January February and on and on but instead of manually typing all the months of the year I can stick with January click here in the down right corner of the cell click hold and drag down to make sure Exel continues the sequence for me like this to until December and now I'm going to have here income also expense and maybe the balance that is the difference between the income and expenses or the income minus the expenses we can create it now so balance equal sign income minus the expense enter now as the income I want to add up all those values that corresponds to the income type and you and whenever you have here many different types of incomes many different of type of expenses the dashboard is going to show you here only the informations that corresponds to the income and then only the information that corresponds to the expenses so basically here we're going to have a su sum summary okay for all the incomes and all the expenses for each one of the months as the the income I want to use for for both okay income and expenses I want to use the sum IF function because the sum IF function can sum not everything that I have but just if this thing match with the criteria and here we're going to have two different criteria the first one can be the income that is because it's going be our income or expenses so the first criteria is income and the second criteria can be the month so we have basically two conditions income the type and the month equal sign some ifs function some ifs let me double click here one two and as the some range I want to go back here to the cash flow and select the values don't need to select the headers okay just select the values or if you want to you can do it it doesn't matter so let's stick with the the second option select everything with the header and then comma Now as you can see here through the formula bar uh some function is asking me about the criteria range the first one the first one is going be either the month or the type so let's stick with the m select here everything with the headers and then tra now the Su is asking me about the first criteria and as we selected here the month as the first range we need to match those informations and select the way the first criteria as the month so let's came back here to the dashboard and select the month January okay simple as that now let me press here comma and now my second criteria range can be here in the cash flow the type select everything with the headers and then tra and the second criteria that I'm going to use here is going to be in the dashboard and the income you can basically click in the income or or mainly typing income within the formula if you choose the first option that is to click in the cell that corresponds to the income you need to make sure you lock this refence because if you click hold and drag down the function all the range is going to follow along okay so it's going to be moved out and if the income range or the income selection move it down we're going to lost the reference of our creativity so we need to make sure we can lock the reference of the income just right here it's not it's can't move up and to do it let me select here everything in the in this last range that I selected and I'm going to press f for key F4 key to upend the dollar sign before the letter c and before the number three that way we're going to have we going to make sure we can lock the reference now just that okay I can press enter and that's it so my income for January is equal to $500 and yeah it's correct and as you going to see later because I'm going to f in here with a couple of different informations the dashboard is going to match perfectly both of criteria that we have income and the month now let's click hold and drag down this F right here to make sure all the rows contain the same function and we basically done that double click here one two and as you guys can see it's perfectly correct to do the expenses we basically going to do the same thing but we're going to just change one little detail here that is instead of using income we're going to use expenses think about that we can click here in the preview cell double click select basically everything contrl C to paste and then within the expens cell want to control V to paste copy and paste but instead of using the income I'm going to use expenses simple as that enter now we're going to click hold and drag out to make sure we have the same function for all the rows and the balance is going to do are going to do the same thing now let me select all those values and change here in the hom tab everything to the dollar sign s dollar okay like this I can also click in this in the column c d and e in between one column and another click hold and drive to the right to make sure we can increase those size now I'm going to select here everything and then I go to home centralize and then input borders the headers I'm going to make it a little bit different such as bold and also the colors I can change later let's select here the M first and change bold and use a different colors such as this black one and the font I'm going to use white for the month I'm going to basically do the same thing here income I want to use a green color and a white text expenses I want to use a white text and a red color and a balance I want to use a white text and a blue colors like this now to make sure we can improve a little bit more here in the dashboard I can select everything here in the income and use Home tab conditional formatting and use color scales I can use this option right here green white color scale whenever you have it here the greatest value that you're going to have is going to be highlighted with the green color and the lowest one is going to not be highlighted so we're going to basically here have a a grade of uh these colors okay I scale so let's say I'm going to make typing in a couple of different numbers and as you guys can see those colors are going to match the value for expenses I'm going to do something similar Home tab conditional perating color scales and I'm going to use now a head one this one head white and for the buttons maybe we can use something similar conditional formatting color scales maybe I can use the first one okay I think this one is good now let me do something here I'm going to input a couple of informations here just to check how the dashboard is going to look like okay I just fill in here my cash flow with a couple of different values and as you can see the dashboard is working pretty well but maybe I can make some change here in the balance I don't like that much of this conditional formatting so I going to select everything here then I'm going to click Home tab conditional formatting clear rules clear rules from selected cells now again I'm going to use a different one Home tab conditional formatting and maybe I can click here highlight cell rules and everything that is greater than zero let's say it's because it's positive so I want to highlight with a green through okay and everything thing that is on Tab conditional forting less than zero is because it's negative so I want to highlight with head okay and yeah that's it now it's much better to see and visualize the data as the income I have here couple of different incomes and you you can see the the one that has here the the strongest scholar let's say has the largest value and the same thing here applies to the expenses now the last stch that we can have here is in this last row have a a total let's say equal sign some function double click one two and we're going to have an addition of the income expenses and balance enter let me click hold and drag to to the right and we're done now we can see everything that uh we have as incomes throughout all those months and also everything that uh we have as the expenses and the final balance right here that is basically the addition of all those values 1,700 so this is basically uh the value that was left in my bank account throughout all those months in this year so I hope you guys enjoyed this video and this is how we can create a very simple but useful spy in Excel to help us to track our incomes and outcomes our expenses if you have any questions or any suggestions to next videos let me know comment down below and I see you tomorrow as every day has a new video I see you there