Collaborate on Blank Invoice Template Excel for Non-profit Organizations with Ease Using airSlate SignNow

Blank invoice template excel for non-profit organizations

okay so this is the basic introduction part two of excel office 365 and we'll be looking at cell referencing relative absolute and mixed and then creating an invoice in excel 365 using the drop down list feature so cell references last video i showed you the auto fill feature when i hovered at the bottom right of a cell until you saw a black plus line pop up and then i left clicked on my mouse and held down that left side of the mouse while i dragged in the desired direction whether it was to the right going across columns or whether it was down going down a row and all these autofill values then were placed into the cells that i had highlighted this could be done with formulas and then i also showed you can be done with days of the week like monday tuesday wednesday thursday or months january february march or anything else that comes in sequential order there may be circumstances where you will not want a cell reference to change what's being filled into a desired cell so instead of it being a relative reference you would call that an absolute reference and that's where you do not want to change something that's being copied or felt so to designate a specific formula holding on to one value and it could be a value of a column and row or a value of just a column or just a row so if it's a column and row that would be an absolute reference if you only wanted it to be just a row or just a column that would be a mixed reference now as i'm saying this to you it might not make sense but we're going to have an example i'm going to bring it down here to a chart so you can actually at least visualize what would be typed in to the formula bar so we did this last relative cell referencing we did this equal sign a5 plus b4 meaning whatever value was in a5 was added to whatever value was in b4 now we're going to be looking at these different types of simplistic formulas so in this case this would be called an absolute cell reference because we're using both a dollar sign in front of the column as well as a dollar sign in front of the row so the cell reference for a5 is absolute but b4 is not b4 you adjust ingly in whatever direction you're you're filling towards now this one would be an example of a mixed cell reference because i don't have a dollar sign in front of both the column and the row instead i only have the column so the column a here is going to be the one that we're going to keep so we have equal sign dollar sign a5 plus b5 so cell reference for a5 has column a as the absolute value but the row will adjust ingly just as b5 will adjust ingly to what direction we felt and this next one is an example of row only and using these same numbers i had equal sign a dollar sign five so the dollar sign 5 means we're locking into the fifth row so the cell reference for a5 has row 5 as absolute but the column will adjust ingly just like b5 will adjust ingly to what direction we filtered i'm going to give you an example of all of these in the exercise we're going to do at the end in a minute the other thing we're going to look at is how to use the drop down list feature in excel so the way you do that is you select the cell that you're going to want to create the drop down list and on the ribbon you're going to select the tab data and in the data tools section you're going to select data validation a data validation pop-up dialog box will come up and then you're going to click in the allow section there's going to be a drop down list you're going to go to select list once you select list a source box will pop up and you're going to click inside that source box and you're going to type all the text or numbers separated by columns with no spaces between them and that will be whatever will be displayed in that drop down list when you're done you click ok now note if you've made a mistake with the above steps and it's just too disastrous to try to simply go back in and change you can just go back into that cell go back to data data validation and then click the clear all which would be at the bottom left of the dialog box and then just start all over again but i'll i'll reference a little later if you just need to do a small edit you can follow the same steps and then just make small adjustments to your list so we're going to create an invoice from the an excel template i want to create an invoice with sales including services and products so that we can look at non-taxable and taxable items as well as show examples of using both absolute and mixed cell referencing let's start by searching templates provided by office 365. i come over here i'm gonna go to excel and then i'm gonna come over here to the templates that we did this in the last video and i wanted to show you this template right here i opened up this template and i made it a little bit more custom for myself because if you look here it just kind of gives everything is going to be blank here and you need to fill it in so i'm going to go right into mine but i wanted to show you where i got it from i went to the template list and i selected simple service invoice but i'm going to make it service and product so i named it sample invoice come up here and as you can see i have it as nicole the business instructor this is an invoice i have my invoice number section here the date section here custom id and then i'm located in pennsylvania new jersey but i decided to pick pa sales tax eight percent and then i put over here instead of putting an average uh address since i travel i just said servicing pa new jersey new york and then i would place my phone number and this is a email now over here whatever i feel comfortable with adding or whatever information i have on the customer i would add here i just put a bunch of stuff here but i think the notes section could be really important because there you could say you know thank you very much for your business and i wanted to just let you know next month we're having a 25 off all office products or whatever no if you're delivering and you say this you know this is your tracking number um please feel free to call me if you haven't received it by this date so this is where you would put an important message um to your customer now here's the the main course of our invoice and this is where i'd want to put the drop down option because this is where i'm going to list either the product or service that my um customer would be purchasing you know what before we do that i remember it just popped into my head i remember last video i said uh i wanted to show you insert picture and i said uh inserting a picture would be a great thing to do in an invoice so let's do that so i'll go to insert tab and then i'm gonna go to pictures i'm gonna go this device and then this is my picture so then i'm going to bring it over i think i want it over here where'd it go let's see oh it went all the way down there let me bring that up i'm going to put it right there unless it looks good like that okay think about it you could put a picture or your company logo or if you have um like a company slogan or a saying that you always have or that you'd like to put on your receipts so you could put that anywhere around here and i would try to make this look as professional or if you're into designing nails or hairs creative that you'd like to get with this but i'd like to keep it pretty professional so i'm not going to put more than just my picture all right so let's go down to um creating our service um and product list so as i said you would go to the data tab and in the data group or the data tools you're going to go to data validation and then from there this is the dialog data validation pop-up box and under allow you're going to search for list there it is once we select lists that's where that source box comes now remember i said you're not going to put any spaces between the comma and the next item so you could have spaces between the first uh you know whatever it is that you're writing so for example my first thing is going to be service package number one i could put spaces between there so i'm going to put service package number one i got the spaces there but now once i put the comma i'm not going to put a space because i'm gonna put the next one is service package number two oh see i put a space by mistake service package number three comma service package number four comma so those are all my services that i provide and you know i'm sure i think all states are this way but i know the state i'm in service um you do not tax so that would be my non-taxable item but i also sell um certain office products especially you know to my uh self-employed or my simple start customers that have very small businesses and they're really just learning how to organize their paperwork and record keeping so um i kind of have a little office package bundle or i sell them separately so i'm going to put all the items separately i usually talk about the bundle um when i go for my first meet and greet so i'm going to start with my binder the next one is the three hole punch the next one is tabs and divider pack notice i'm not putting any spaces after the column of each item the next one is pack of labels the next one is portable file holder and the last one is usually after i have the initial meet and greet with my clients and we agree that it went well give him some time him heard of some time to think about what package they want and what they want to purchase and when they call me in for the second meeting is when i'll have a contract ready for us to sign and i take i'll have an invoice ready to print out and i take half of my service fee down so i'm going to add to this list agreed upon deposit all right so this is done and i'm going to click ok so now whenever i'm ready to develop a invoice for any of my customers and obviously this would be my sample invoice and then if i wanted to i'll always keep this sample invoice by the way and when i'm ready to do one i'll just copy and paste it and then just save it under another name because remember we use office 365. so if i came into my sample invoice and just started typing on it it's going to automatically save as i'm typing and i don't want to lose my very fresh template so just for future reference this is probably as far as i would go in saving because i have everything i want on the top and i have my drop down list here which i can fill in like this so now i'll have a bunch of drop down lists see and i will not want to go further in typing anything in because then it auto saves so this would be my sample template invoice that i will always use i would just copy and paste it and or like i would go into it and save it as another name and then just type in information i hope you understand what i'm saying because office 365 auto saves all right so now let's look at our drop down list see everything i typed and i don't see any spaces here so i definitely didn't put any spaces in the comma and it's not too bad if you did put a space that's just going to be moved over and you could just go in and edit it if that's what you if you're uncomfortable with that space being there okay so this is everything i typed in there and i'm going to say for this client i'm not going to fill the client's information which i just want to show you how we would um fill this out and put simplistic i'm going to use simplistic formulas there's other ways to do it as well but i'm going to start off with something simplistic so my this client said they wanted to do the service package number one and when it comes to service packages there's only one all right so the unit price for that is one thousand dollars it's not taxable so for this i'm just going to simply say this is this times this and it's a thousand dollars the next one is they purchased three binders they decided they didn't want an office package but they did have three employees and they decided they'd get a few things for me so they purchased three binders and i have a brochure that i bring with me and i would have right there what my price list is and so that's twenty dollars my binders are twenty dollars and what happens is i like to in my invoice specifically put what tax is to each item the reason why is if a client decides later i actually said i wanted to purchase the tabs and divider pack which that's the next thing i'm going to put in but i found it cheaper at staples or i realized i had them so can you take that off i'll know exactly what the price was that they paid for that item so i happen to um keep this separate so this i like to know exactly what the taxes for each item now notice the tax for pa is eight percent okay my business is located in the mpa so i have to have my sales tax to pa which is eight percent so the formula that i would put here for the sales tax would be equal sign the c6 which is the quantity or c17 i'm sorry which is the quantity times d17 which is the price so that's 3 times 20 right and now i'm going to times that by now watch what i'm going to do i'm going to bring this down for all the products that are going to be purchased so i want to use an absolute reference here okay so i'm going to put dollar sign and you see this right here this is f 8. so i'm going to put dollar sign f dollar sign 8. now let me explain to you why i did that as i scroll down all of the items that they purchase they're going to be multiplying these regular references okay these are relative references so as i drag them down they're going to go instead of c17 they'll go c18 c19 and so on but what will not change is this multiplying it by the eight percent that won't change because i put the dollar sign before the f and the eight meaning i'm locking in on this cell to be the value that i want to multiply to these items okay so i'm going to push enter and it's five dollars okay so now i'm going to put let me go into the next one before i do that um the next thing that they have is tate uh tabs and dividers and they wanted three of them so i'm gonna bring this down a little so we can see the whole list tab and divider pack and they want to purchase three of them and the tabs and dividers are five dollars each so in this one i'm going to drag this down one and it should automatically apply the 3 times 15 i'm sorry 3 times 5 which is 15 i'm doing it in my head times um .08 now what i'm noticing is it is rounding so that means that i must have this set up to a number field that does not have it to two decimal places and i did this on purpose because it is important that when you're dealing with money and especially if it's money that's owed to the government that it is as accurate as possible so you're going to want to make sure that you have the numbers set to the dollar sign at the two decimal places okay so i'm going to come down here highlight this and i'm going to go over here to the home i'm going to come over here and i want to make sure that i have it set at two decimal places okay see what a difference because originally it said five dollars and one dollar well it actually evened itself out didn't it but i just wanted you to note that um the change actually does make a difference especially if it went beyond so if this had been instead five dollars and forty cents and it rounded it to five dollars that 40 cents is going to come out of my pocket because i still have to owe that money that money still has to be paid to the state whether i charge the customer correctly or not all right so i just wanted to show you that um let me take these off all right i'm going by one more um product so i'm going to delete these as well all right so let me go to the next thing so the next um office supply they are going to purchase is the portable file folder so i'm going to select the portable file folder where is that scroll down a little there it is and they purchased one of those and that was at 50 okay and when i did that it automatically had put in the 50 times point zero eight or times eight percent which is four dollars now i need to come up with a formula that is going to add my the the purchase price the total amount that they have purchased the items for plus the sales tax so what i'm going to do here is i'm going to put equal sign and i'm i i'm going to just tap on it i'm going to put c 17 times see a d17 and then i'm going to put plus f17 and push enter and it comes out to 64.80 okay and once you do that you could just come down here and this should come out to 1620 and this should be 50 54 so wonderful everything is correct so far so just want to scroll up and say all right so i have my service package which was a thousand dollars but that is not tax applicable because service fees are not taxed in pennsylvania so i just put a thousand dollars here but the products that i sell are so i had to apply the tax and me myself i always like to have each item's tax and the value separate so in case at any point because i always sit down with my client and it might take them it might not be until a month or two or three that we i satisfy you know the my contracts and at any point they might say you know what i i don't want uh the tab in divider pack so i can go right over here and say okay that came out to 1620 i'll make sure to credit your balance for 16.20 and they could see it clearly as well on their invoice what exactly they get back for that and remember i said i have agreed upon deposit whenever i meet with my client the second time when we sit down and sign the contract i ask for 50 of my um the service fee up front so that would be 500 i'm just going to put this over here as a negative 500 let me put okay and i have it set where it's it goes parentheses and it's it's red so my agreed upon deposit from them would be 500 meaning i'm coming here i'm ready to print this invoice out to give to my client they're going to be writing me a check for 500 that i'll be depositing and holding on to until my services are finished and now over here for the total owed that's going to be equal sum and i could put it for the whole column if i wanted to for the number values but i just want to uh select this because i believe i do have the tax number over there and i don't want to throw it so now my client can clearly see they owe 635 dollars by the end of the contract okay and then i have over here thank yous for your business if you have any questions about the invoice please contact and then you can put your name or if you have an employee that handles that their name their title and their information okay so that would conclude my invoice using the drop down you see so you come over here and you apply it ingly now i want to come over here because i want to show you mixed cell referencing so here we have the list of my packages i have four packages and just to give you a heads up like what am i talking about here i'm a qbo pro advisor i'm a quick book um intuit pro advisor so i go with companies i sit down and they come up with a plan on what exactly they want me to do so they might want me to come in and take whatever existing accounting software they're using and bringing it into qbo which is the cloud-based um software program so i'm going to come up with a package like okay this is the services that i'm willing to provide so i'm going to and depends on what size the business is how long is it a year two years or visit a couple of months and then also additional things that most clients want me to to train their employees and then how many weeks do you want me to train their employees so based on what they tell me i decide which package plan is best for them okay now if they go with package three or four i offer a discount where i come up with i think i just mentioned it when i was doing the list an office package and in the office package i give them two binders and um a hole puncher to tabs and dividers to labels and a portable file folder for very inexpensive but not only that i also give you an additional discount if you're if you select package number three or package number four which is two thousand and twenty five hundred as an incentive maybe to say yeah all right for the extra 500 and i get even more of a discount okay so and by the way when i say here one package two package three package because they might decide well i wanna buy five office package because this might be a larger company and they want to get each one of their managers you know an office package for their department okay so why i did this is because i want to show you both the row mixed referencing and this the column mixed referencing so here what i'm going to put here is going to represent what the five percent discount would be for a client that's picking package number three with one office package so they're automatically say i'm doing package number three which is two thousand dollars for my services but they're also gonna purchase the discounted office package but one of them so since they're only getting one they're gonna get a five percent discount so let's look at what formula i would put here so remember you always start with equal signs to let excel know i'm about to put in a formula so i want to put the dollar sign on the column b why because let's leave it's b here b is where i have the numbers for or the the value of package three and four package three is two thousand package four is twenty five hundred so i'm gonna put equal sign dollar sign b because i want to do my um column to be frozen so i'm gonna say plus b five and then i'm going to multiply that by parenthesis because this time i'm not going to separate tax to not separate tax you go one minus and then you put whatever the parent the percentage is not tax i'm saying this percentage off so the percentage is is five percent but that's in d with d4 so i put d and then i put dollar sign four why would i put dollar sign for because i want this to go across the columns because i'm the 2000 is going to be the same for each one of these what changes is up here the percentage you understand so over here i'm showing someone with package number three for two thousand dollars how much they would get that office package for with the you know with the discount the discount that they would get off this this uh package deal i'm gonna go across here showing them the different percentages okay so let me push enter and show you what i'm talking about so i would tell the client you'd really be paying 2 000 for my services but if you buy this discounted office i'm even giving you a discount on the office package because that's what i do with all my clients you get a discounted office if you buy it all together but you additionally i'll give you five percent off your package price so you're only going to be paying 1900 for the package three now what i could do with this five percent is i can drag this down one and it's all gonna it's gonna autofill the 2500 at the five percent why because here i was holding on to the column b so it went from 2 000 to 2 500. i hope that makes sense so they kept the b but they went up from b5 they went b6 so they chose they took the number that's in b6 and now they multiplied it to this value five percent okay now when i come across to do the 10 and the 20 when they buy more office packages they're going to get more of a discount off their package so let's drag this this way and see what happens so now you can see what happens now they're getting 10 percent and why did the 10 apply because i am putting the percentage here where the is going on the 4 this row right here this row four i had it going from five percent to ten percent to twenty percent by putting that dollar sign in front of the four that's what you call mixed reference so this would be a row mix reference an example of a row misreference and this would be an example of a column mixed reference i hope that made sense if not maybe look it over again but let me just drag down so you can see the prices and so now when i sit down with the client i can show them well look this is how much you would pay for package four you would get it for two thousand dollars if you decided to buy three or more of my office bundles now this is a brand new company and they're very unorganized they probably and i can show them i always bring a sample with me and they say oh wow this is very impressive they might say hey it's worth it i save myself 500 by buying three packages with the package for all right so i just wanted to show you um how to do the drop down list as well as how to use all the difference reference styles the absolute the mixed and we already had um reviewed the basic uh relative so let's go to our last last slide where i just kind of summarize it for you so the drop down list feature in excel has its limits because you will be limiting the entries that could be selected in a specific cell so when i went to my invoice and i had that drop down i can't type in there now i must select one of those items that i put in remember i did service package number one service package number two separate package number three so i only can select one of those specific items that are on the list in that particular cell i can't start typing over however you can't edit it though so if i add a new product suppose i add you know a pen holder or a calendar a desk calendar so all i have to do is take those same steps that i said above by going to the data tab and selecting data validation you would do those same steps but once you get to the source stop box you can just simply go in there and add remove or edit any items that are already there on the list because that list will pop up okay so you can always edit that but if you wanted to do this and you were sending it to your client they would only be able to select from that drop down so you would want to make sure you have everything available in that drop down list okay it's great for invoices stock lists or inventory lists order forms that you'll send a client time sheets if it's time sheets the employee can click and select their name and how many hours they worked a job question form and much more so this is a great feature to learn in excel if you like to use excel for your business forms and um that's it that's that's all i have for for this one i had to believe have one more excel before i move on to word i had a few requests for word but anybody who's seen my videos and says hey i'd like you to cover this or that put it in the comments and i certainly will get there get that um covered for you alright thank you for watching