Lessons

Lesson Info

Segment 11 - Excel - Charts & Conditional Formatting

The next thing that I want to show you is actually one of my favorite things to play with in excel, and I'm leave just going to flip over to the other one so you can see what what's going to happen. We're going to work with conditional formatting, and what conditional formatting allows you to do is color code your cells so that they dynamically change so that you can see your standout figures understand our values and your trends just simply by looking at the numbers instead of sitting here and having to go okay, my booth is more expensive than anything else except my beads, and I don't pay that much money for internet, even though I thought I did, you know, instead of sitting here national comparing the numbers in my head, conditional formatting makes it really readily apparent. So let's, we're going to do three different kinds of conditional formatting, so let's, start with those grand totals, so I'm going to click on five and highlight down tto have thirteen note that I'm not doing ...

the ones below the line. I'm just doing the actual expenses themselves and up on the homeland, and we have this big giant, conditional formatting button, so I'll click on that so the first one that we're going to do is called data bars. And under the data bars we have greedy in't fills and solid phil's I'm a big fan of the grady in't fills both because they don't obscure the text them because they look cool so I'll go ahead and staying true to my color scheme I'm going to go ahead and choose my green data bar and now what that did want to click off of it is it found my highest value and it filled the cell and it found my lowest value and made that small but now I can very easily see how my expenses air comparing and again it is dynamic if I realized that a weight in it's going to cost me more an airfare and I start changing my numbers around the bars are going to change too so these air a wonderful way of instantaneous understanding I went and I put that number back now another way of using conditional formatting is by using arrows and icons and so this time let's look at our total expenses so I'm going to highlight just before from body mind spirit tio east are for total expenses, so be fourteen t fourteen and I'm going to go back up to conditional formatting this time doing icon set and look at the different things you have here the material it turns your excel into arc you have arrows anything from a three hour arrows scale toe a five arrow scale you can do it in gray scale, you can have traffic lights, you can have like consumer reports style indicators you can have flags um symbols, star ratings, pie charts, even bars so whatever is going to give you the best representation, you can choose any of those style sets and I'm going to go ahead and we're going to dio the four arrows since we have four cells and I'll click on that and it's giving me you cable first it's now made this too narrow because it's taking up space in here so I'm going to highlight those columns so I'm clicking on the b and dragging to the and I'll double click toe wide in those out or in this case it wants me to drag, so I dragged them a little wider and I can see the numbers here now because these numbers are all in the same range it's saying k this is low expenses, low expenses, low expenses, high expenses I'd like a little bit more range here, so I'm going to highlight those four again and go back to conditional formatting again and go back to my icon sets again and I want to see if I get anything different when I choose the five arrow scale and sure enough now it's showed me that this one is not the lowest it's a little bit more it's still below average it's pointing down so they're now I can see what you're my highest expenses and my lowest expenses now let's, I should take a look at our prophet using conditional formatting also but a different one so same thing I'm going to highlight b sixteen across t sixteen I'm looking at my profit for the four shows and I go back up to conditional formatting and this time I'm going to use my color scales and you have color scales on the green yellow red you've got green, white, red and red, white and blue and I'm going to choose the red to green actually that gave me the reverse of what I want I'm going to do the color scales and dio green to read instead because what makes better sense to my brain is seeing my biggest profit is in the green and my lowest profit is in the red and so now I can see color coded by the rainbow. This is my first my worst next best next best, best and again as your numbers change here, these numbers were all update themselves as well. Do you have any questions on conditional for men? Why? Because we didn't have earlier way did miss it alicia don't jump back too far, but when we have the field where you change the size off the column and therefore the number itself disappeared, is there any way you can change to make the number fit without having to go back and change the column I suppose you could reduce the size yeah, you can reduce your font size then you will be able to read it apparently the only way it would be a lot smaller and everything else around it that that would be the eleventh viewing changing the font you might be able to find a smaller font that uses that takes up less space has a little less koerting I can okay, so now that we have all kinds of colors and all kinds of prettiness going on let's go in and do some charts so we're going to do two different kinds of charts I'm going to do a column chart and I'm going to do a pie chart and so when you're doing a chart, you have to be careful not to include your totals when you're doing a column chart because that will you'll see but human spirit, crafts nor wacky star totals and some throw off all the rest of your numbers. So for the column chart, we're going to go from a four and I'm going to shift click on the stars staff so e thirteen so I am including the labels but I'm not included the totals, so now that I have those, I'm going to go up to a new ribbon that we haven't looked at before the charts ribbon and there's a lot of different kinds of charts that you can generate here, so if I click on column, I can see all kinds of different column charts and there's line charts and pie charts and bar charts, all kinds of things and I generally recommend, especially for dana analysis, generate a couple different kinds of chart it's because you never know how you're going to see and think about your data differently, so we're going to to to useful ones and you know, you could always play with them so the one that I'm going to choose, so I'm going to we have, ah, I'm partial to the three days the three d column, the three d cylinder, the three d cones just cause that's my aesthetic, I'm going to go ahead with a clustered cylinder, and so I'm going to click on that clustered cylinder, and what that does is it drops its pack dab in the middle of my chart, so I need to slide over a little bit here so that I can see. And now I want to pick up the edge of the chart on point right over to the blue and moving it over to the right hand side so that it has enough room and I generally find when I'm doing charts that the wider it is, the easier it is to see, and so I'm moving the handle right here until I get that double headed arrow it's real important to have the right shape cursor to make this work so I'm looking for the one that goes to the left and right and I'm going to drag it over to the right the wider it is the easier it is to see and so now I can see my different expenses according tio my different trade shows the color scheme is of course because I changed the theme normally you would see this in orange, blue, purple, red one of the things that I like to look at is these buttons right here for switching the plot right now is showing me the four shows and the different expenses for the show's if I switch the plot, it goes the other way it shows me each of the shows and then each of the different expense this is I think the first one is probably a little it more more viable but let's do some changing of this we can see you know I don't have the same beat cost on all of my shows the larger shows have more being cost than the smaller shows, so if my smallest show maybe instead of four fifty I'm looking down here at my color to see which is my smallest show and so maybe I only spent three hundred beads at that show which is going to start changing these as well and then for my show where I do my best maybe I spend five hundred fifty dollars now when I go back over and look at my beads I can see that my chart has changed as well now with the charts you also have some formatting that you condemn you know I also want to point out that the just like before you are menus your your ribbons change if I'm clicked off of my chart I don't have any chart options if I click on my chart then I have options for chart layout chart formatting as well and so with the charts I'm gonna go back to the main charts and maybe instead of this multi color I want to change the charts style some looking over here at the gallery and I'm gonna pull that down and I'm going to change this one so it has this black multicolored backgrounds it really is striking toe look at all though goodness gracious I would not want to print this you know your tonal be gone like that, but it makes it really easy to see a chart on the screen now I also want to add a title to this so I'm going to go to my chart layout ribbon again I don't see it you're not clicked on your chart and I'm going to go to this chart titles button and I want the title above my chart and so I'm now going to call this trade show budget and something else I liked to dio is I like to put my, um my legend below the chart again the wider it is the easier they are to say so I'm going to change this legend button and moved the legend to the bottom and if I want I can again get that thin double headed arrow and make it a little taller if I want to so those are a couple ways that you can modify a bar chart and you can see there's a lot of different things that you can dio that's just the tip of the iceberg now I'm going to do a pie chart instead and it's going to be another way of looking at this same data but looking at it by this time using those actual totals so now when I'm doing a pie chart, I need these labels and I need this data way over here but I don't need all the numbers in between so there's kind of a trick to this I'm going to highlight from a five down to a thirteen so the labels booth through staff and I'm going to hold down my control key on my keyboards really important you've got the right key so double check the control key and now I'm going to highlight those same cells in my totals row but it's really important to tell you the correct it's not the control she it's the command key sorry about that commands key so I actually have to go back and re highlight my a thirteen now command f five through f thirteen and you can see that they're both shaded at the same time so it's the command key that's the key right there so I've got them I've got those and we're going to go back to the charts ribbon and I'm going to choose a pie chart and I'm going to dio a three d exploded pie which is going to say that a great name and that's going to allow it to not only look cool in three dimensional but we can actually pull the pieces apart so I'm going to a three d exploded pie again it drops its smack dab in the middle of the page so I'm going to grab an edge this time I want the four directional arrow because I'm moving in I'll pull it down below so now I can see all of my general expenses again I'm going to get that thin left to right arrow make it a little wider so that I could get a little bit better data and let's play with this a little bit so first I want to put on figures so I can see what these actual numbers are now if I just hold my cursor over each pie slice it will tell me what it is, what the value is and what the percentages and I think those percentages are particularly useful right now, so I'm going to go back to the chart layout ribbon and I'm going to choose data labels and I wanted to be percentage, so click on the percentage and now it's added those percentages to each of my pie slices. Now, if I want to emphasize the pie slices, I can drag these in and out. Now I'm going to preface this with saying that it's not an exact science, and so I'm going toe probably take me a couple tries to get this to click exactly right, so if I just click once and drag, it brings the whole pie together, so just one click and poll then it's not exploded anymore, then I could maybe I want to emphasize my largest and my smallest, so I'll click off of the pie and back on just one slice and cross my fingers and then drag and yes, it just pulled that one pie slice out now. Do you notice that when I pull out a pie slice, it makes the whole chart a little bit smaller? So you do have tio take that into consideration? You might want to make the chart a little bit bigger, but I've got that thirty percent pulled out and I may be I want to pull out this skinny little three percent right there and that skinny little three percent right there so that's how you can explode you're patrick now again with colors there's a lot of things that I can actually do here I'm going to go back to I can work with my fills and my lines and my effects and so if I want this pie slice to be a different color than the purple I can go up to the phil and change anyone pie slice simply by picking a different color if I want I'm going to undo that I can also do some special effects if I do a command, eh no work click off click arm I'm going to click slightly outside the pie chart which is going to this is where the not an exact science part comes in because the charts are really complex let me actually say it this way I'm going back up to the chart layout ribbon and right here there's a little drop down for current selection and so you can modify any of these parts of the of the chart itself so the siri is one is what's actually the data in the chart and so when I choose siri's one it's allowing me to select all of the pie slices and when it's referring to siri's what it's referring to are these totals over here and so a selected siri's one to make sure that I'm slipping, applying this to the right thing and I'm going to go up, teo the format and down to fill, and we saw this a little earlier, I'm going to go to phil effects and then to fill here, and I'm going to fill this with the texture, just like we did earlier, I'm going to make it match the postcard that we made in this morning, and so I'm going tio use this texture and click ok? And it looks like it actually already was that texture just very subtle, so you can on these go in and choose all different kinds of grady ints and all different kinds of directions, and so you can have it a look what I just did right there. I made it a greedy int instead so you can pick different kinds of styles and radio works really well for chart for price, for a pie chart, doing a radio centered will have the color go from the inside to the outside, and I can change the level of the grady in't make it whiter or purple er by moving these in and out, so I'll go ahead and I'll click ok, and so that actually, is it going to work for the pie chart because it's just turned all my colors the same but if I had chosen for example, if I had done that with just one of these, if I had chosen just the number the might body, mind spirits it would have changed just these purples in here and because I want my colors back I'm going to london, ok? Let's do the same thing I did before and move my legend to the bottom somebody remember where I go to do that it's back under chart layout and then I click on legend and then I put it at the bottom and I'll make it a little taller to give it more room so that's how to do my pie chart okay, so I'm going to zoom out on my whole project so you can see the whole thing and the last thing that I want to dio now they look at this beautiful spreadsheet that we just made. We have data, we have data analysis, we have two different ways of looking at the same data and you can see again why it's a good idea to try to different kinds of charts. Um for those of you who have the downloaded files that came with the bonus this chart, I did a little bit differently instead of doing all of the data, all I did with that one was the total expenses and the expected revenue let me pull that over! So I looked at the revenue versus expenses for those two shows, and I did that by highlighting that the show names and holding the command key and highlighting the revenue and expenses so that's how I made that chart there. Oh, heck, I've got it highlighted and might as well make that charge to chart the bar chart this time just for kicks and so there's my compared revenue and expenses. Okay, so now that I have all this wonderful data, I need to print it out and there's a couple things that you should know about printing. So I'm going to go to the layout tab, which is where all the controls are for the actual piece of paper that all this is going to go on, and I generally find that with excel spreadsheets, that landscape works a lot better than portrait, because usually you wind up with wide data, so the first thing I'm going to do is change the orientation and xl smart enough to know that landscape is usually the default so that's on landscape right there now, one of my favorite things that people don't know about is this fit to right here that if I don't have a fit to on that, keeps wanting to put it on there, what happens if I do a print preview? Okay, so mine right now keeps default into it, but normally you almost always wind up with data that's cut off either have a column on the right hand side or some rose across the bottom and you don't want to have to sit there like make it, you know, shrink it up or have to work real hard on that. So when you there's a fit too that's both in your print window and also right here in your, um in that window right there, not sure why it keeps expanding on me like that. There we go, so the fit to will scale everything just small enough that it fits this way and just small enough that it fits this way as well, and I can see the number right here that it's scaled it to fifty nine percent of it's normal size. I also before I print want to put on a header and footer, so I'm going to click on my head and footer right here and I can either go with some of these pre created headers or I can customize it, and when I customize it, it puts ah a left section, a center section and a right hands section, so I'll put up here trade shows twenty fourteen in the center of the header and click ok and in the footer I'm going to do some additional options for you, so the first thing that I want to dio is say what page I'm on and I'm going to put that on the right hand side so I want this to say page one of one so I have to type in the words and then I'm going to put in the page number and then I'm going to type space of space and then my total number of pages in the left hand section I want today's date so little click on this insert date button right there and in the center one of the things that I find really handy is putting in a file path so that if I want to open this up later on I know where on my computer it wass and so I have right here this file path or I could do the file name now if this is going to be a document that's going to be shared with other people, I would just do the file name and not the file path because the file path shows all the now all your files structure on your computer so if this is just for you, that whole file structure is going to be just fine and you can see it here here's my date here is that whole path from files toe folder to folder to folder to folder to file name and then the page number right there, and I'll click ok. And now, when I would go to print here's the preview of what that would look like, complete with my headers and footers. So there in an hour, fifteen minutes is all your basics.