Effortlessly Manage Your Bill Book in Excel for Support

Bill book in excel for Support

this is Colton with custom Excel spreadsheets and today I want to do a quick tutorial on how to create a quote form for your business so the first thing you want to do obviously is to open up a blank spreadsheet and go ahead and save it so you don't lose any of your work then we're just going to get started typing in some generic information up at the top I want to type your company name maybe a street address city state zip phone number whose the quote for you see I'm just getting a template set up here of course later you would come back and fill out all of the details as far as the real information but ideally what you want to do is get a template set up that you can save and use over and over again that's what we're doing here today and right now I'm not worried at all about formatting this may look terrible in the beginning we can always fix those things later so right now I'm just trying to get generally set up what kinds of things I want to show up on the forum and then later we can drill into the detail of how can we make it look nice [Music] okay this would be more or less your header then you might move down and say okay what kinds of things do I typically want to have most places you might have an item number a description and I'm going to skip over a couple of columns and then do a quantity unit price and two anytime you want to change the width of a column you can come up here and drag alternatively you can go to format your cell and change the column width to a specific number but generally you just want to get these things items laid out then you could do so a little bit of formatting just to get you started here let's add some borders so I'm going to select the table where when we type in the Baltimore information and come up here on the Home tab you see this icon do the drop-down arrow this brings up all of your border options so to start out I'm just going to do all borders to do a thin black line around all the borders I'm going to take out this extra column right here I don't think we're going to need that will do instead is just make column C really wide typically the description is going to be one of your longest items and we can actually take these columns now and copy them I'm just doing control C control V on the keyboard to copy them over I'll delete that out and actually let me sleep this will call them as well okay so now you have a general working quote layout now let's look at some of the simple formulas you would want to have in order to make this automate it so every time you're not having to calculate what your total is for example so typically you'd have a quantity again and a unit price and then you don't want to have to manually type in your total so to do that you just start with an equal so anytime you start out with equals that is beginning a formula in Excel so instead of just having a text or a number in there it recognizes it needs to do some calculation with this or some function so we'll do equals and then I'm just going to click the quantity type in the asterisk symbol times the other so now a quantity times unit price and hit enter and you see it did 25 now I can copy that I'm just doing ctrl C on my keyboard or you could right click that cell do copy and I'm going to come down and select all of these and do a ctrl V to paste it so now you can see I have this automated where it's doing that calculation for me and again the the formatting we can worry about later right now I'm going to go ahead and select those and Center them up just to make it look a little bit better the other thing you notice is you have zeros you may not want to see a zero where you don't have anything perhaps a couple there's a couple different options for getting around this one would just be once you have filled out let's say your item number and again I would want this to be centered and then a description now if these items aren't gonna be used for this particular quote I could just hide them so you can come over here select those rows I'm just clicking and dragging over the rows 19 through 23 I'm gonna right-click on any one of those and click hide so now what that does it just cleans it up or I don't have the zeros shown there so that isn't the way that I would do it but it's one of the simpler ways if you don't have a lot of knowledge in Excel where you can get to look reasonably good so now I'm going to unhide those and we also would want a total so you can see what your absolute total is for the I'm gonna come down here and put a total and so now we're gonna do a different formula this is going to be the sum formula so we're gonna start out with our equals and type in s um don't have to do all caps you can see it brings up a list here of potential formulas in Excel Psalm is one we want so I can either double click that or just type in an open parenthesis now what do I want to sum you see it's helping me out here what's the what are the numbers you can just drag click and drag beginning with the first one going down through the last and then close the parentheses on it hit enter and now I've got a total down there and typically since these are prices I might want to come up here to again on the Home tab and change the number type to currency then we get dollar signs and cents I'm going to Center that up and maybe since this is a bottom line I'll bold that and increase the font size and maybe put a border around that as well and again we could get into making this actually look nice in another video but typically that just gives the idea okay what are the formulas you're gonna need to know pretty simple multiplying two numbers and then doing a sum and that will typically get you everything you need as far as math on a form like this and maybe have taxes and those types of things that's going to be very similar so let's just say you had tax eight percent now over here you could just do equals one point zero eight since I'm marking it up by eight percent times what my subtotal was I would rename that to subtotal and then hit enter and there you go it's already giving you what you need on that nobody could do it I guess this would actually be beer so you see it's very basic mostly just multiplication and then some are gonna be the formulas that you need on a quote form like this now just to give you an idea of where you could take this ideally I think where you would maybe want to go would be to look more something like this where it's set up looks more professional you have all of your formatting done really nice maybe you have some functions like this where you have typical item numbers so you can click a drop-down and select from a list of a hundred items however many you have I'm going to do item three notice when I select an item it automatically updates the description automatically fills in a unit price and I get into mount based on the quantity so I'm going to change this to item one now it's a starter and I have two of those at 225 gives me a total of 450 so this is the kind of functionality you could ideally have in a sheet like this what I recommend at this point if you don't have the knowledge in Excel to create something like this will be to click the link in this video there's one down in the description - custom Excel spreadsheets and let us talk to you about maybe how we can help you either take a sheet you've started on and make it better by adding some of the more complex or advanced features like this or create one for you from scratch