Effortlessly Manage Your Client Payment Tracker Excel for Retail Trade

Client payment tracker excel for Retail Trade

hello i'm steve friedman from rockstar excel today i'll show you how to design an expense tracker if you take our rockstar excel beginner to rockstar class you'll learn how to make things like this on your own even if you've never used excel before before we get started some words of encouragement you don't know what you haven't learned yet if you're an excel beginner i'll be using tools you aren't familiar with in the beginner to rockstar course we take the time to teach these tools and make sure you understand them in this video my goal is to show how quickly and easily you can create useful spreadsheets but you do need to actually learn how to do so that's why we teach the course so don't get scared or intimidated if you get a little lost once you take the course this will all seem easy the course is very accessible and user friendly even if you aren't a numbers person or are afraid of spreadsheets find out more about the course at this link i'll tell you more at the end of the video i'm using a stopwatch to see exactly how long it takes to create this spreadsheet from scratch which i'm starting now so the first thing you want to do is save your spreadsheet uh in case your computer scratches that way or crashes that way you'll have uh an autosave so let's call this rockstar excel expense tracker and i'm going to go ahead and name this tab summary um and then i want to lay this out so let's call this january 21 feb 2021 and then once i've done two months here uh let's just drag this over okay and then i'm going to put total here um and we're going to put categories here but i'll leave that blank for now and i'll come down here and put total um and here and hit ctrl r to fill that across and some here down or ctrl d to fill down and copy paste okay so that is the general layout but there's one extra step which i'm doing something a little clever here uh and you'll see why i'm doing that in a minute but i want to do here above the month and year i'm doing equals month there and quote quote and year [Music] here okay okay it's oh well sometimes i make mistakes too uh i did not um forgot to close the parentheses so my formula didn't work uh that should fix it right so and now fill this across so now we've got here it's just telling you the month in the year um and you will see why i needed to do that in a second um now uh we go to another create another tab here and let's call this one expenses okay so uh here on the expenses tab uh i want to [Music] put date expense amount category note and i'm just adjusting the column let's go ahead size make that and just to put them at kind of the reasonable size for uh what we're information we're going to put into it so now this is basically laid out but i'm adding another column here which i'm calling mine here um okay and uh so this is essentially laid out um before i put formulas before i fill it in um i would actually normally put the formulas in but to make it a little clearer what i'm doing i'm going to put some dummy data into it um so this is some dummy data that i prepared earlier uh just to make this video a little clearer that i'm copying and pasting here um so now what i want to do is go ahead and do the same thing i did before of putting the month in year so equals month there this time i'll close the parentheses and quote dash quote here that should be an open parenthesis there oh that actually should be there should be an end there okay let's fill that down by double clicking and i actually want to go here and [Music] i actually want to kind of scroll down to like you know 10 000 rows or down up here and then just hit ctrl d to fill down and now right um yeah that's fine okay um so now what i want to do here is i'm going to paste in the categories for my dummy data now users that i give this spreadsheet to will be able to make up whatever categories they want that are useful for them that fit their personal life or their business [Music] add some extra rows here [Music] okay uh so now what i'm going to do is equals sum so i'm using a sum if some if s formula to as the main way the spreadsheet works so equal sum if s and the sum range is going to be the amount and i hit f4 which puts these dollar signs there [Music] and the criteria range is going to be this month year column and i'll hit f4 again and then criteria one is going to be that i'm going to hit f4 twice so that there's a dollar sign before the six but not before the b and comma and then criteria range two i'm going to do the category column and hit f4 to put dollar signs and uh then criteria two and i'm going to click here and hit f4 three times so there's a dollar sign before the a but not the nine and close parentheses okay and now i am going to fill down and across and this spreadsheet is essentially functional at this point um and now all i have to do is kind of review error check it and format it but at this point this spreadsheet is um going through adding up the different uh uh expenses by category and sorting them into month uh but i'm still gonna keep the stopwatch light running because this is pretty ugly looking um you want to give this to your boss uh so um the first thing i want to do is put this into a better format so i'm going to use the accounting format what i want to do here is put a little format that put a top and bottom there let's make this bold in this bold and this fold um but something that is important is to do some error checking that um so now for error checking we want to make sure that this total equals the total for the month um so what we want to do is so this time since it's some if the order is reversed and go here first um comma and then go back to the criteria is this and hit f4 twice so we've got the dollar sign there and then we go back to the sum range which is here and close parentheses and then minus that and let's make this comma format so now we want to make sure these are all zero and let's hit ctrl r to fill this over and we want to do the same thing here um only so we do equals sum if and range will be the category for to put dollar signs there comma criteria would be that and we want to hit f4 three times comma and the sum range is here f4 whoops and then minus that let's change that to a comma boom so we just found an error here but before i deal with that i just want to do equals that minus [Music] some okay so here we found an error which is why we do the error checking so what this is telling us is if you add up all of the merchandise category uh then the total is twenty dollars and 78 cents more than the total for the merchandise category for the year um and here if any of these numbers weren't zero and by the way when you use comma format if it's zero it shows up as a dash um so if any of these numbers weren't zero that would be telling us that the total expenses for the category or i'm sorry the total expenses for the month don't add up to the total of the categories for the month so if this wasn't zero that would be telling us there's an expense that doesn't have a category this not being zero tells me that there's an expense that is not in one of these months so my guess is there's well let me go try and find that expense [Music] easy way to do that is to just search for 20.78 and here we go um so that was from the previous year i will just delete it and that's fixed okay um and then i will go ahead and hide this row because it's ugly looking and hide this column and yeah that is it i'm going to stop the stopwatch the stopwatch is showing 14 minutes so that's how long it takes to create a fully functional well polished and error checked expense tracker uh it was actually only eight and a half minutes when i had the basic functionality and if i wasn't narrating and explaining what i was doing i think i easily could have gotten everything done in under 10 minutes so that's just how easy it is to create powerful tools in microsoft excel and this wasn't too complicated or advanced and it's all stuff that we teach in the rockstar excel beginner to rockstar class um you could probably go through and format this a bit nicer but you know i just kind of wanted to show you how to do this uh the um so uh the actual one that i'm distributing i'll i'll do a little bit more to make it look nicer um so uh about that rock star excel beginner to rockstar class uh could winning excel win your next job or save you hours of time as an entrepreneur your mission should you choose to accept it is to increase your excel skills 100 or more in four weeks so that you'll be able to get twice as much done in half the time amaze your boss and potential employers and enjoy unparalleled job security the four-week course runs february 2021 with registration opening january 2021 find out more at the link on your screen or in the video description if you're watching this after january 2021 check the link for information on future courses if you're a beginner or even even if you've never used excel before we'll turn you into an excel rockstar by the end of the course you'll be able to make spreadsheets like this all on your own as a former chief financial officer with over 20 years experience i've found there are a gajillion different complicated functions in excel but only a handful of simple ones you need to know to do 90 of the useful things you can do with excel the rockstar excel beginner to rockstar course teaches you the those useful functions in a user-friendly way along with our proprietary rockstar method that shows you how to choose when and why to use them imagine a task that used to take you hours being done in seconds blowing your boss's mind by how much you improve efficiency being automatically safe from layoffs because your company won't dare lose your expertise that's what the rockstar excel beginner to rockstar course can give you and if you aren't interested in the course as a thank you for watching this video i'd like to give you the tracker i just created for free simply go to the link on your screen or in the video description for your free download and check out rockstarexcel.com for videos blog posts courses or to get excel questions answered and more thanks for watching this video and be sure to check out the rockstar excel beginner to rockstar course so you can become a rock star once again i'm steve friedman from rockstar excel thanks for watching