Tuesday we launched them so I really appreciate you joining us in this episode we're gonna create an incredible and dynamic expense tracker it's gonna track all of your expenses and all you need to do is take a picture with your phone of the receipt that receipts gonna get automatically loaded into Excel all you need to do is add a few details and it's gonna save a full picture of their sheets gonna be displayed you're gonna be able to track all of your expenses by month by year it's gonna be an epic training and a beautiful application if you have not done so yet please check.
Out our mentorship program if you love Excel and want to take it to the next level is fact especially in this day and age when working from home is so important you can do just that and I'll teach you exactly how you can earn amazing income with your Excel skills by creating applications that can be sold night and day all year around and I'm doing that in my mentorship program so you hope will join us for that in this last week of the mentorship program we created an amazing invoice application or we created a dynamic.
Pop-up menu it's one you won't want to miss a menu like you've never seen before where users can create their own custom invoices I'm going to show you how to take that talent and bring it into your own application so you can create your own invoices and sell them on the marketplace so you can get yourself reoccurring them I hope you join us you can find us at my excel mentor.com and learn all the details about this amazing program tons of students are really happy with the training they're getting and increasing their skills so that they can become independent and financially.
Free alright let's get started on this training I've got a lot to cover so we're gonna try to get it all in in the amount of time so bear with me as I design this we're gonna take those first few columns columns a and B and we are going to make them for admin so we're gonna call them great because I want to make sure that will eventually hide those columns we're gonna put some things in there that we want anybody else to see the first thing what I want to do is I want to make sure that we have a title on this we're gonna call it its expense tracker and that's just.
Gonna be the general name and let's go ahead and give it a nice larger font perhaps something like our Arial black and we'll increase the font something nice and then I'm gonna extend it over to probably all the way through J so I'm gonna merge and center those cells and give it a format so I'm gonna right click format and just give it some kind of a fill pattern maybe we're gonna go with this color right here and I'll do a fill effects so we do a fade down I've got a different format here on this one so give it a little bit of a light green color and the row below I'll do the same.
But just a little bit lighter that way we'll have a nice fade effect throughout there think and we'll go in fact we'll go all the way over to let's say calm and I want to extend this way a little bit we're gonna use a lot of that so we'll format those cells and then give this fill effects here just a little bit lighter using that medium color and then we'll do that okay great so I just want to fill that and that we can also unmerge these that I'm just gonna drag these cells over to extend all the way to let's just say yeah I've got a really amazing in this we're gonna show you how.
To get all the contents of a folder inside Excel you know listen we're gonna be that without VBA at least a portion of it without VBA and then we'll use VBA to refresh it so it's gonna be really a unique training something you have seen before and so I'm excited to show that part of you because that's brand-new we have never done that before I can bring in all the contents of the file folder okay so basically what I want to do is I want to bring in the logo now just kind of an expense tracker logo but I've got one saved here we're gonna cut we're going to do this really basic here I'm gonna insert a picture.
Here and I've got a wallet here picture I'm just gonna go with that's something basic there I want to graph at the top of this will increase this one down there we go what I want to graph at the top of this something really nice and basic so let's create a background for that box so I'm gonna insert a shape here and I'm gonna use a rounded rectangle here and I'm gonna start let's say around D and go all the way to J probably down to about say 8 something like this I'm just want a very slight rounded corner here like this and then I.
Don't want any borders on it so I'm going to say no outline and we're gonna give it a shape fill of white so that's good I'll put a little bit of a shadow behind it so it looks so it kind of Bray's out so I'm gonna put our graph behind that and I'm gonna duplicate that because I want a title and then I'm gonna shrink it up so I'm gonna put this title section up here and I'm gonna bring it up here and then I want to call this title section something like expense overview or not it's in white font now but I'm gonna change it and I'm gonna change it to black font so that.
Obviously it's a actually I'll go ahead and change the background to degree and then it's gonna be there we go so that's good that's what I want to see but let's give it a title and something like a little bit of a capital probably a little bold so it stands out more so we can see it's gonna there we go and bold okay that's nice expenses overview make sure I spelled that right overview good alright so this is where I want our expenses to show up but I want to put a line graph over here so that it's kind of looks nice and clear so we can see an overview of our expenses and we'll get.
On to that graph sort shortly but now that I have that what I also want to some field I want to I want to know by
Year so I want to know I want to be able to select different year so we're gonna have different year so let's just say like 2020 or something like that and then this field we're gonna have different year so let's color that why cuz I want the user to be able to select different years from a drop-down list in fact we can just go ahead and build that right now the other data validation I'm gonna create a list here and I'm just gonna put in let's just say - 0 1 9.Comma - 0 - 0 & 2 0 - 1 of course you can customize this I mean years you want but I'm just gonna put 3 years so now we could user can select between the drop-down list okay and then I also want some dates so I want to be able to use her in to receive so we're gonna put in a date here and then I want to type and then I want the receipt here also so the receipts gonna go in hatred so I want the total receipt here and then also I want to know the amount of course we have to know the amount of the expense.
And I want the category this way you can assign categories such as different types of expenses and stuff we'll create a small list for that all right so let's color these I'm gonna put these in in white here so we can see them clearly so we know the user is going to be filling those in so we're gonna go with a white font and then I want to put a border around them so I'm gonna right click format those cells and I'm gonna put something a little bit closer to the theme maybe a light here actually fill white and the border we're gonna go to the border something like a little bit.
Light border just something really I'm gonna go with a very light theme here and kind of just not a dark color here so I don't put the dotted line on the left and then I'm gonna basically do the same thing here under the date here and then I'm gonna write justify these I should have left justified those also gonna format those cells to give it a nice look and feel this is where the user is going to be entering their data so that they can do it and will do a table down below - so that you can see that and then I'm gonna put the left border top and the bottom here okay so.
Now we're getting to see our form and how these are gonna be the form slits I want this a little bit darker like because I want these five fields are gonna be our form so I'm gonna format those cells sorry it's off the screen right click format I'm gonna go with a little bit darker color on here I'm gonna put in an outline the idea is that I want these form to be into the user I don't have some buttons up here let's create those buttons we're gonna do a very basic button let's zoom in here make this look nice I want these rounded edges the same so we can do that notice.
When we shrunk it up we we want the rounded edges so I want those the same let's bring this out a little bit there we want them matching the top looks nice and all the way to the left okay good so we get a nice view now what I want to do is I want to create some buttons so again I'm gonna insert shapes here and I'm gonna use the rounded rectangle and then this time I'm just gonna use it really we're not gonna use icons today we're just gonna use something really really basic something really nice and that it looks good and we'll go with an orange because I want to really I want to stand out in this case and then no.
Outline and then what I want there I want to push put ad expense so many's a + ad expense this one we're gonna keep it really really simple give it that kind of a flat design it's gonna kind of look cool you know something a little bit different and then we'll write just fight and then I'll make it a little bit bigger and then bold in fact let me make those that's a little bit too big but I'm gonna go I'm gonna shrink it up and I'm gonna make them capitals cuz I want to so we'll make it a little bit smaller I want it that big alright that looks pretty good and bring it down okay I.
Also want to do the same thing I want to check for receipts I want to know if we're gonna be having receipts so there's gonna be receipts stored in the folder and we need to know are there any receipts check for receipts in fact let's not do this let's make a dynamic but even better I'm gonna make this green what if we have a button that has different text based on whether there's receipts or not so let's do that we're gonna have this is gonna be dynamic text but we'll just keep this temporarily but I'm gonna show you how to create a dynamic text because if there are.
Receipts in the folder that need to be brought I'm gonna say I want this to say there are six receipts or review six receipts or something like that so we'll put that in so it's just temporary but it's looking good so far we're gonna put a graph up here let's start to build out our table now I want an expenses table here so I'm gonna put this house are expensive so I'm gonna merge and center this and then create expenses and I want all the expenses here so we'll have this a date amount type category.
And then receipt location and then I'll want to probably a picture I want a
Picture of the receipt right here so we'll have this all stored in the table down below let's zoom out a little bit so we can see that okay so we've got that so let's Center that and I'll give it a bit of a format so I'm gonna format those cells I'm just right clicking it's a little bit off the screen but all I'm doing is right-clicking and going to format cells you know how that is so I'm gonna give it a fill a fill effects of a lighter color basically the same thing what we've done using the same theme.That we have going from the medium to the very light and on the top one I'm gonna go just a little bit darker so on this top one I'm gonna do it a little bit darker format the cells also keep in mind that there's a free download just with the links down in the description every once in a while there are some issues you know maybe I missed something in these live videos or something you never know and so but of course I always make sure there's a corrected version available to download and it's a free download just check the links in the description whether you're on Facebook or YouTube.
Format the cells again we're gonna go over the border and so there'll always be a downloaded version for you I make sure to get that to you each and every week okay give it a just a basic border there we go so this gives you a nice and now we can color this just something basic so I'll go with a white background and I want to go with a border form the scroll absolutely let's go up here and then we're gonna format those cells just something basic so that we can get by and then what I'll do is I'm gonna put a thick border on here and then I want the top and the bottom here like that.
Something like that there we go that's nice so we can really stand out we can really see it I'm gonna bring this down here alright so we've got our basic covered now we've got everything pretty much covered now we can start to build out the formulas and the formats and the named ranges we also have to add a lot of named ranges because I want to make sure that all the expenses get tracked and to make sure that everything is going to be synced completely and we have gonna be a really great formula that we're gonna show you how to bring those names or receipts into the formula okay so just add no fill on that okay so now everything's consistent so we're starting to build.
Out let's see what we want to put inside this hidden admin area first I want to know if it's a fall to receive we're going to be loading so you can basically add expenses whether they're with or without a receipts but I need to know that because I need to know whether we're gonna have a link so I'm just gonna put folder receipt and it's gonna be just a true or false true or false something like that we need to know we also need to know if it's a new expense so let's just put new receipt and that's gonna be boolean true or false also also I want to know how many.
Receipts to review how many do we need to read this is gonna help us build out our body text do we have four receipts in the folder that we need to resume do we have 20 and we have none I need to know that I'm we're gonna use a formula on that okay I need to know the button text that's in button text this is gonna be our dynamic text let's call this button text so what I want to know is I'll go through that formula shortly but then also I need to know the selected row when we select a row from this table I need to know so for example let's say it's 21 now you put you need to know.
That we're gonna highlight it based on that I also want some months we're gonna be tracking how much per month so we need to know the month so let's just put months here and with months here and the amount per month I'm just gonna use short names on these we don't have a lot of space so all you need to do is use a short name is just scroll down January all the way to December here and then I want a total here well I told all of the total amount here so it's gonna be basically equals the sum of whatever all these months are and then we'll add in.
Those formulas once we get some amounts okay great so we want the sum of all of that so it's starting to come together now and let's color these so we know we can decipher exactly what what they are and then give us some borders and the same thing for here just so we can color it and borders then I'm gonna left justify this so we can see it all right it's coming together now we're gonna start out with some formulas and D but we really need some data to start with I've got some data actually I'm gonna put it right here I've got some data.
Right here I'm gonna copy that data just some basic data I'm gonna put it right in here and I'm gonna paste it in here and then of course we do needs formats don't we so we need these date formats so I'm gonna format this as a short date and I'm gonna format this as an amount okay good it's starting to look nice together we're getting an idea and let's make this bolt so it stands out a little bit more control B will get us there let's add some types and categories we need some lists and we also need some named ranges too let's create a dynamic.
Named range for our expense date table so let's do that let's just highlight some cells go into the name range go to the formulas here name manager here and go new and I'll want to create a using offset so we'll just call this expense date and then tab down and we'll start it out with offset and we're going to include the header but we don't want to include it in case there's no data we want to make sure there's no errors including the headers going to help us but we also have to go one row down and then comma comma count hey I want to count all the text I also want to.
Include the headers - I'm one included but I'm Adam make sure we exclude it we're including the headers because when there's no data there's no air but we also have to let's just use a highroad 999 and then what I want to do is I want to subtract one from here so we're gonna count a and then minus 1 and then comma 1 that's just the column tab out tab back in make sure the dancing ants cover all of our data that lets us know that our formula is correct it is correct so let's do that now let's do another one for a mount so new and then we'll just call this a mount.
Let's call it expense amount and then we're gonna tab over and what data we want will we want again equals offset and I want to start out in the header but then I want to go one row down comma comma count hey what are we counting we're counting all of the data including the header but subtracting 1 again 9 9 9 and then close parentheses minus 1 because we're gonna take one out from the header comma 1 and then any print sees tab out tab in make sure that encompasses all I ever dated great ok now that we have our data now that we have our formulas we.
Can start to build out our graph here so let's do that let's start with some months I want to know how much in January we want a formula actually that's gonna total all these mounts but only in the month of January so how do we do that well let's keep it start we know what do we know I know this month one right now I know it's on Row one so how do we get that so what is the formula we're gonna be as renew sunnites equals some ifs yeah that's right we can use some this could be multiple criteria but what is this some range the some range is of.
Course our expense amount expense amount that's what we just created an image and what is the first criteria it's gonna be based on the date so it expense date is what we're gonna be focused on and what is that it's going to be the first criteria of that it's gonna be greater than or equal to the first of the month or January in this case for this row and what is that the date of what January if we want the year we've got the year here f4 to make sure it's absolute comma.
Now what is the month well the monthly r is january we can't use the word january we can use 1 which is the first month of the year so how do we get 1 we know it's row 11 so how do we go from 11 to 1 well we just subtract 10 so row which is 11 minus 10 is going to get us one now we have the month what about the day that's gonna be 1 alright great so that is our first criteria what about our second our second criteria is going to be we want that to be less than or equal to the end of the month so how do we write that well first of all the criteria in this case of course is the same which is expensed date we have that and what.
About the criteria for that quotation less than or equal to and the end of the month reviews eeo month for that again the start date in this case would be date what is the year same year and we're gonna use that make sure we're going to f4 to automatically absolute comma what is the month say month as before as we did in this case it gets gonna be row minus 10 that's gonna get us 1 and it's gonna allow us to drag it down comma what is the day 1 okay so we get the end of that month and in this.
Case we wanted to meet number of months would be 0 so I'm gonna close that out now we have the end of month and now we're gonna close out our summits so we hit enter we see it's 25 which is exactly what we want if we were to change this to 115 we see that it changes to 37 which is exactly what we want okay great so we have our formula work and if we made sure we have absolute on what we need to which is e 10 to keep that consistent now all we need to do is drag that formula down to all of our.
Months and it's gonna automatically appear excellent it looks looking really good we don't have any data for May which is correct no data in fact we can just change this to five five so we can get data in May and we have a total here excellent all right let's now since we have our data we can build out our graph our little graph here or a little line chart it's gonna be really nice let's go ahead and insert that let's highlight all the data here not including the totals yet and we're gonna insert and I'm gonna just gonna go for a basic column something really simple like this but I.
Want to change the look and feel I don't really want it to look exactly like this we're gonna update it because I want to keep it very simple so to do that let's get rid of this series we don't need that'll just click the lead on that I also want to change the color of this so I'm gonna right click and go to format the data series and I'll just gonna go to the fill I want a solid fill maybe just something consistent like this green here would be perfect I want a green consistent okay now I don't also don't want this border around here so we can right click and then go to again.
Format the chart area go to border and this click no line there we go now we got something very nice now I also want the total I also want the total what is the total of this so let's create some something to link this total I want to know the total so insert I'm gonna insert in this case a text box and I'm gonna link that text box to the total so I'm gonna put it right here and I'm a safe equals what do I want it equals the total and this is going to be this one b23 so now we have it now all we need to.
Do is format B 23 as it occurence e because I want to format as currency nice and we can get rid of the borders here shave fill will do no fill on this and will do no outline on this one but I need to know what that is also let's increase the font a little bit make it bold so we can clearly see it stretch it out a little bit and what is this let's add another text box here so we know what it is just call this annual total annual total okay that looks nice and then we can Center this just so we get.
An idea of how it's coming I'm gonna also Center this one so they're both centered and then make this a little bit bold here okay now they're both bold and we just get rid of the border on this one I don't need a border on this very good now we got our format all filled out and we got our design really pretty set up we can add some more firm moves in here looking really good now let's see how do we get this I also want another button here I also want to duplicate this and just put save expense so something like save expense and we can actually add a check mark into here just like we do if.
We did it let's add a space we can do insert you know sometimes we have logos but this time let's just do a symbol something keep it simple let's add this symbol right here so we don't have to add logos okay good we stretch the button out a little bit save expense well it looks kind of nice all right and let's add title to this as we do save expense button and then we'll just call this one add expense button add expenses okay so we've clearly label our and then we'll have this called check receipt but.
Okay good so we've got all our buttons labels so we know when we're gonna say them and now we'll just we're gonna only gonna show the save and the Attucks not at the same time so we can put them over on the top of each other I also want to remove this and format the gridlines so we don't want any good we don't need any gridlines just have a nice clean look excellent now it's looking nice and I'm gonna group these all of these together to graph everything together so we can move them all together it's gonna be very nice that way we can shrink them down and we'll shrink them a little bit and what I also want to do is I want to.
List out all the files now I've got a folder here of all the receipts let's take a look at this I've got a bunch of receipts here and I've also got an archive folder so what I want to do is I want to link this folder so to do that I'm gonna show you how to link the folder and all the recipients on how to load that so I'm gonna copy this folder and then we're gonna go down in to put that file folder in a specific file and I'm gonna put that probably in over here and let's just say l2 here and what I'm gonna do is I'm gonna also I want every single file in that folder so to get that I'm gonna use a special formula I'm.
Gonna use the backslash and then I'm going to use the asterisks which is gonna do note every single file how do I list the files I'm gonna list of all the files in that folder I don't put them right here nin so how do I get that well the first thing we do is we have our file path right here in l2 so how do we get all the files well we can use the named range for that plus a formula I'll show you how to do that so into the formulas name manager we're gonna create a brand new one we're gonna call it list files and then what I'm going to do is I'm going to give it a refers to what is.
It it's going to be equals files what are the files it's going to be the files of l2 all the files of l2 then we're gonna use index to pull those and extract those out so click OK and click close so now that we've created the name now all we need to do is create an index equals index what are we indexing we're conditioning the lists files that system only created and what is the raw number we'll the row number is the index number right move the files go from 1 2 3 4 so we just simply need to number those the same as the row number to do that we just type in row and then open a closed.
Parenthesis and then close the parenthesis a that's gonna get us our first look receipt 16 that is the same exact file here receive 16 so when we drag it drown it's gonna show 17 12 and 13 right below it so all I need to do is copy the formula and bring it all the way down here and paste the formula excellent except I don't like this ref so how do we do that we'll just wrap that in if air so if air and then we'll just go to the end comma and then just leave it blank or we can put it to 0 I guess and that's it so then we can close.
The parentheses so now when we drag it down or copy it paste the formula down we won't show that hair paste front look great so now we have a list of our files perfect so now we can take that combined with this and create our receipt image and put it in here so now how do we do that how do we show the picture well I'm going to show you how to do that with just a little VBA code now that we have the file now that we have all the files we can go through these files and bring these into our application and then show.