Lessons

Lesson Info

Segment 10 - Excel - Formatting a Spreadsheet

The next thing that we want to dio is get set up to do some subtraction so first I want to put in what we expect our average revenue to be for each for each of the show's so I'm going to put in a fourteen expected revenue and again this is just kind of a budget it's an estimate its it could be a real number but it might not be and for body mind spirit I'm going to make it three thousand for the c l crafts we're gonna make it two thousand nor wack will be twenty five hundred no notice I'm not putting in any dollar signs or decimals in here for peace are I want it to be of four thousand five hundred but you know that I should just reminded me I didn't put in any decimals but I do want to have some decibels in here so I'm going to make a couple changes to some of these numbers here let's make electricity seventy five I'm going to go to be five and going to change it to seventy five dollars and seventy five cents and then click on it and auto fill it to the right and I want to make my ad i...

n the program so I'm going to be seven I want that to be one hundred twenty five dollars and fifty cents and notice when I hit her hit taber return it takes off that end zero and that's right now because I haven't set this up his money yet but I will so I'm gonna take that twenty five fifty and I'm going to pull that all the way across as well and you'll see that my totals will update themselves okay so now I have my expected revenue and I want a grand total on that as well so I'm going to click on f thirteen and I'm going to auto fill that down one more time so I can see how much I'm planning to make on the whole on my whole show season so if that twelve thousand dollars here so now let's see how much profit we're going to make because it's one thing to make the money but it's another thing to actually realize how much you're spending in order to make that money so in a fifteen type in profit and we're going to do some subtraction so now my profit is going to be my revenue minus my expenses so what's the first thing I have to do to start a formula equals and I'm going to take my revenue so I'm going to click on b fourteen and I wanted to a minus which is just a dash up there next year zero and I want to subtract my total expenses which is be fourteen so if that be fourteen minus b thirteen and I'll hit return and some looking to make about sixteen hundred dollars profit no, we don't have to do that over and over again let's go ahead and use our auto fill handle and pull that all the way across to column so far so good. Okay, all right, so now I want to show you another one of those formulas under the auto some and let's do an average let's find out what are you? Our average cost is going to be across all of these shows, so little put an average class and so what I'm looking at here is I want to know what my average is on all of these expenses right here. So I'm going to go back up to my sigma appear up to my auto some button and drop that down and I'm going to choose average and it's going to try and take an average of all the numbers up above it but that's not what we want, so I'm going to click on b thirteen and drag across two e thirteen so make sure you don't include f thirteen cause we don't want her total and with our average we're just looking to see our average expenses and so I can see that my, um average expenses are going to be, um fifteen hundred dollars actually I want to see my average revenue let's do that one more time this time I want to see average revenue and so it will be the same thing in column in row seventeen we're going to d'oh since we did expenses and the revenue is right below it. We can actually do that auto fill handle because when it was here, it was when I'm doing the average cost. It was looking at row thirteen and because I'm pulling it down one it's going down one appear too so it's doing fourteen automatically so I can see both my average cost and my average revenue just by using my auto fill handle, okay how's that could so now functions and formulas are not just about numbers, there are text functions. There are also of date functions as well, so I want to put in another row below the my title so that I have a place to put my date and what I want to insert a row there's a couple different ways that you can go about it one is simply to click on road, too, and when you're on the home ribbon, if you look over on the right, we have an insert button and the default is to insert a row, but if he dropped it down, you could also insert columns as well, but we do just want to insert a row, and so boom there is that roe now there's, another little secret that I have that I'm not really going to build it in here, so I'm just going to do it and then I'm going to undo it, but I want to show you that if I want to insert several rows at a time let's say I forgot about some expenses and I have three more things that I want to add if I want to add it below row thirteen so if I highlight fourteen, fifteen sixteen and I can either go up to that insert button or I like to right click on this remember when I say right click if you're on a mac trackpad it's two fingers or you can control click and I can choose insert off of this menu too and when I d'oh because I highlighted three rows, it inserted three rows so you don't have to sit there and go insert insert in certain, certain, certain, certain, certain you can highlight however many and doing all in one shot. Now I don't actually want those rows there, so I am going to go up tio my undo button or is command z to get rid of it and now we have all of our data in let's start making it look good ok, so we've got everything in here but we've got things cut off we have missing dust mel's we could make this look a lot better than it is and before I get started doing all that one thing that I do want to point out is that excel uses the same themes that we saw in word earlier and so at the end of the home ribbon now honestly, you don't usually wind up using themes very often in excel but because you're creating a brand with your own colors it's nice to be consistent so I'm going to drop down the themes and we've been using infusion so I'm going to go ahead and click on infusion and you can see immediately my fonts jumped a little bit it's using a different font set than it does by default and when I start using the colors it's going to use those same purples and greens that we were using earlier now I also want to point out that I have been using infusion which is a little brighter than I might use professionally there's all different kinds of color set and some of them are subtle and some of them are elegant and some of them are garish and I went for kind of the brighton and energetic for the purposes of this course but you can pick whatever suits your sensibility okay, so what I want to do first is I want tio work on cell a one this trade show budget and so the first thing to point out so I click on sell a one and the very first thing is pay just like in word you have all the same formatting tools you can pick your funds you can pick your font sizes we can increase it and decrease it you've got bold italic underlining all of that is the same so first I want tio making a little larger so I'm going to increase the font size and I think sixteen looks good maybe eighteen so making a little bigger and then I'm going to go down to the font color and if I just clicked on this button right here it would make it bright red but if I drop it down I can see all of my theme colors and don't forget again that we do have access to that more colors wheel where you can store all the colors for your company and reuse those very quickly and easily but we're going to make the train show budget I'm going to use one of the dark purples you're welcome to choose any color that you like maybe because this is a trade show budget you know I'm going to go to green instead so I'm going to use a dark green color you to repeat the formula for average cost please yeah ok so in the average cost what I did here was I went up to my auto some button and I dropped it down and I chose average and the first thing it does wrong is just look at whatever's up top above it and so in order to change it I'm doing my average cost so I'm looking at my total expenses which is row fourteen so I'm going to click on the fourteen and drag across tio e fourteen and then hit return that was from me okay, okay okay so going back up to trade show budget and sell a one I'm going to go ahead I'm going to make that bold so it stands out a little bit more and now the next thing that I'm going to do is I want trade show budget to be exactly centered over all of my data so I'm going to highlight from aim one toe f one all of these cells right there and then once I do that I have emerged button right here and when I click on merge it merges all those cells so now a through f have just become one cell and then it also centered it all in one step now what's interesting about these merge cells is that if I try and click on e one or f one or c one, they don't exist anymore. I can see right here in this name box that it says a one so if you are doing anything fancy down the line sometimes emerged cell can actually get in your way and you might have toe emerge it and then perform whatever action you're doing it than we merge it again but this is nice because now even if I expand the width of it, it will continue to merge this across these cells. Ok, so the next thing that I want to dio is center all of my column headings, so I'm going to choose from a for tow f four and I'm going to use my center button for those of you who like keyboard commands its command e because commands he was copy so it's, the second letter command e and now that's in the middle. I'm also going to make my those headings bold while I'm here and let's, go ahead and make everything in column a bold also now I want to point out something I'm not highlighting just the column header! I could choose a four and that would select the entire road to make it bold or I could choose column a to make everything bold, but I don't know what I'm going to be doing yet in columns g h I or rose twenty one, twenty two, twenty three and so I may wind up holding things that I don't want bold id so I just over time have come to just select the cells that I want to highlight, and so we are going to make those bold as well ok, so next I want to take all of these numbers and turn them into currency so I'm going to highlight from b five down tto f eighteen and so a click on b five dragged down tto f eighteen and highlight all of those cells and then I want to change this teo currency and so there's right here is our number formats and right now it says general right now it does recognize that anything that's text is left lined and anything that's a number or value is right aligned so that the decimals in the numbers line up we wanted to click on that though and turn change it to currency and then you see what that does hear it put in the dollar science it put in the it moved all of our decimals to two decimals so we have point zero zero if there's no sense we have point fifty if there is change and for the numbers that are large and put in comus also you can see those down here now there are two different currency formats currency looks just like this but there is also an option for accounting and what accounting does is it puts the dollar sign on the far left side of the column and so it spreads it out a little bit more. This is what accountants like to see however, when you d'oh accounting style they generally will do the first caught the first row but they're not put the dollar signs underneath so I want to show you how to do that so I want to show you a couple of new techniques here so I want you to click on selby six and we're only going to make this change to our values in the table so that goes to staff for peace are so I'm going to hold my shift key down and click on sell e thirteen east our staff and it highlights all the cells and going across and then all the cells going down so especially when you have large ranges of cells to select it's a lot easier to do a shift click then it is to drag her dread to drag because we've all had the experience of office just taking off on us and having scrolling, scrolling and scrolling so it's easier to dio the shift clip trick so now I want tio actually go into a cell formatting window so I'm going to either right click or two finger click or control click to get my pop up window and then I'm going to go down to format cells and it brings me in to see the formatting that I have for accounting there's a lot of formatting thatyou congeal here we're just going to focus on the one thing that we're looking at but I have two decimal places and I want to change the currency symbol to none which is going to take off this dollar sign just from these cells right here, and so I'll click ok? And so those dollar signs are gone now in the same way. Maybe I don't really care about my small change here, so maybe I don't even want to see my decimal places. So with those same cells highlighted b six t thirteen there's, two buttons right here, the first one increases the number of decimal places, and the one next to it decreases the number of decimal places, and I'm going to knock that down to no decimals. I'm going to leave the decibels for all of my other revenue and profit numbers, but I took them away here, and I want you to notice something that they're not gone when I click on the ad in the program, even though it says one twenty six here that actually rounded up the real number is one twenty five, fifty and so it's taking into account all of the different all of the change. But it's just showing you the number rounded it's, not changing the calculations at all. So that's how to high increase and decrease your number of decimals? Well, now that we've done that let's, pay attention to our ourselves because some of these air cut off, and so when I want to resize a column, I have a couple ways of doing it one is that I can hold my cursor over the line between the column headers you see how it turns into that double headed arrow I can click and drag and make these as wide or as narrow as I want them to be um a better way of doing it would be on that same line double click and then what it does is it perfectly fits the text to the longest thing in that column so you can't go wrong now I don't want to have to do it a to b to c so I could click on the eh not they won but the actual a and it highlights that whole column and I can drag that all the way over to f and now I can pick any of the lines between the columns and double click and it will then go ahead and best fit to the column so now each one of these is now a little shorter than it wass except for body mind spirit which I shall got longer so for body mind spirit because the name's so long I even have another approach that I can take and we're going to wrap set that sell to rap and so I'm going to click on selby for actually let's do this to all of road for so I'm going to click on the four and because I'm doing it in the row numbers it highlights the whole row and I'm going teo I can either do the right click and format cells like I did before or I should show you that on the menu I have the format menu and sells and you're in here so often that they even gave it the keyboard command command one so that's another way of getting there so go into format cells and I'm going to go to the alignment button right here and I'm going to tell it to wrap the text and what that's going to do is allow me to have two lines of text inside one cell and while I'm there I don't want that to be in the bottom of the cell I want that to be the vertical yeah, I want that to be in the center of the cell some changing that vertical to center and I'll click ok and now I'm going to shrink selby and so boom there it is and now it's instead of just getting cut off the way it did before it moved it to a second line but you know something odd about this? What what didn't go right when I did that nose down at the bottom, my expected revenue turned to number signs an average revenue turned to number signs what that mean watch out for that what that means is that the cell is now too narrow to display your numbers that air in there and so instead of cutting it off the way it did with the text you can't because if the number got cut off, you don't know if it cut off the million's or if it cut off the decimal places, so instead of truncating it, making you guess it just shows says here this is too short, so now I'll go ahead and I'll make this a little longer until the numbers off it. So by making the rap that allowed this to go to two lines and by making it vertical center, it said it so that these column headers are in the middle of the cell. Maybe I might want them on the bottom. That might look really nice, but I wanted to show you that's how you can make the text fit into the middle. So anybody have any questions about that? Okay, alicia, because it looked like when you moved column being shrunk it c, d e and f followed, but did I just imagine that, um you imagined that it did it did it when I have all of the it did it when I will wait, maybe it did know it was just the one but the formatting because you're on the line that's why the best buy what made my ice? You're right, everything everything did move it, yeah, it does center itself yeah, we have a question here now if you were to add a line in here so you have to put in a new line seven all those formulas that you have with then re calculate reformulate when you when they moved and we have that question came from ray or because if he of all age, if you add at the bottom like if I do it a new row fourteen it may or may not, but if you add in the middle it will incorporate it, so if you add down at the bottom of the end, you may wind up having to adjust your formulas, but if you've put in, say, sixteen total five to fourteen and then you add in a new fifteen self, sixteen will reflect the changes it should, but sometimes it doesn't it kind of depends on how you put that new row in there, so always keep an eye out for that, okay, so now I've got my numbers looking a little cleaner and a little easier to recognize. Now what I want to do is I want to put a little bit more visual distinction between my labels and my data itself, so we're going to look at borders and so I want to start with putting a line between my headings here, so I'm going to go ahead and highlight from a four toe for again I'm not going to just click on the four because I don't want that line to extend out into g h I j k just wanted under these numbers and now I'm going to go teo my borders button and this is one of those that if your screen is expanded, the borders button sometimes disappears and so you may actually need tio go up too there's the borders of the borders and shaving off of the menu but usually you can hit it right from right from here so here's my borders button and I'm going to click on the borders button and I'm going to give it a thick bottom border I can see here that I have outside borders inside borders top bottom left rape insides and diagonals but then these are the ones that are generally used to set apart your numbers and your totals so money is a thick bottom border here and you can't really see anything until you click away and boom there's that thick bottom border and I'm going to d'oh the same thing under for row fourteen I'm going to make a line above row fourteen and I'm going to go up tio the border's button again but notice this time there's no thick top border on this list so I'm going to do a custom border here I'm going to go all the way down to border options and I'm going to tell it, okay, first I want here's the weight of the line that I want and I think I actually want this slightly thicker one so I'm gonna quick on the thickness of the line and then in this picture here I click where I want that border to bay and you can see that you have buttons to turn on tops and bottoms and diagonals and insides you know, you can really go through any of these, but I just want top so I've got a top border if I wanted I could also change the color, I could go ahead and use that same green that I used earlier and I'll click ok, so now I have a border and let's also put a hairline border between our numbers and our grand totals, so I'm going to highlight from f five down to f thirteen and I'm going to go up back to my borders button and I'm just going to do a simple left border can't see it until I click off and there it is now in addition to the borders, shading comes in handy and I'm going to use a lot of people will shade there, their totals, their expenses in this case, I'm going to keep it simple and I'm just going to put a border around or shade my total profit, so I'm looking at sixteen and I want to put both a border around it and shade it to really make it stand out, so I'm in f sixteen and I'm gonna start with the border, so I'll go back to my border again, and I'm going to tell it that I want an outside border, which is going to put a border around all four sides of it, and then to shade it, I'm going to go to the classic paint can right here and shoes of phil color. Now, when you're choosing a phil color, one thing that's really important, don't choose a dark color, make sure you choose a light color because two reasons first of all, if you choose a dark color, then you're probably going to obscure the text that's in it and the second one is it's also going to use up your toner a lot faster? So both for finances and for practicality, choosing another she's in a lighter shade is a good idea. So here's the number that I want to stand out and so, you know, I think that's even dark for my taste, so I'm going to click on it again and I'm going to go up one green shape there, so now I've got my profit standing out above everything else.