Lessons

Lesson Info

Segment 23 - Excel - Data Tables

We started with excel in the very beginning of the course, and so this is kind of a way of coming around full circle and in the first session of the course, way back when we spend our time making a spreadsheet completely from scratch and so now think about excel is it's one thing to do your planning, which is what we did before, but it's something completely different to make good business decisions. And so my goal for the next hour and a half is to show you how to do decisionmaking using your excel tools, so we're specifically going tio look at some sales data and see how to analyze it and, um and, well, let's, just take it from there. So when you sign up for the course, you get to download our course files and the one that I'm going to be working in to start with today is this customer purchases, you'll see that there's two copies there's the customer purchases final, which is our outcome that's what we're going toe that's our end result at the end of the unit, so we're going to star...

t with raw data, so go ahead and open up customer purchases into your excel, and I'm going to use this arrow up here in the corner to expand my window to give myself the maximum real estate and let's take a look so what I have here is a list of information what this one is a list of my shows from the previous christmas with the customers contact information and then over on the right hand side I collect the birthday because it's an astrological jury company and then I have what items they've purchased, what length, what color and how much they paid, and yesterday we move, we used this to send thank you notes to people using a mail merge. Today we're actually going to analyze this data, and we're going to be able to see what items sold the most and which shows did the best for me what's interesting about this just fundamentally is that excel is designed to be a calculations program, but because it's set up in rows and columns, people tend to use it for collecting lists of information and so there's a few little known tools, specifically the data tables where you can now work with this information list. So click anywhere inside that information, and it will make the data that we're going to do a data table and it's going to make it out of all the continuous cells. So it will expand out up and down and left and right until it comes to blank lines, and I'm going to go to a new ribbon that we haven't explored yet in this program for tables if you've been with us before, you'll notice that this is the same table tools that we saw in word and the same table tools that we even saw it in one of so we are going to go to the new button right here, and when I click on it, it now gives me it turned my first row with all of my data labels, it put little drop down arrows next to each one of them, and so now it's turned my row one into a relative headers. Now, before we go any further with it, I want this to look a little nicer than it does right now because of the baby actually easier to work with when we color it now this just before we do covering before you're going to feel like I should have reminded early in apologies to interrupt you, but don't forget everybody when you click the free r s v p or enroll button on the course page on the website, you will get access to all of the zip files that alicia has made available for this particular course, and then you can follow along. This particular spreadsheet alicia is working with is one of the zip files those available completely for free just click our sleepy or enroll, and you'll have access to all of these very about that no good thank you. So I have what's called a gallery appear in the middle of the ribbon of table styles, and when I hold my cursor over, this little arrow appears that's not there when my cursor is off of it, so I'll put my cursor over it there's the arrow, I'll click on the arrow and it drops down my tables. Now you can choose anyone that you like. I'm going to use this one right here, this medium purple because purples, one of wandering stars colors, but you are absolutely welcome to choose any color that floats your boat, so I'm going to click on that and boom! All of a sudden I have shading and some white text and borders and banded rose. Now, if I turn my attention to the upper left hand corner where the table options are, don't touch the hetero we want that to be on because that's what's going to give us all the data tools that we need. But if you don't like the banded rose, if you find them distracting, you can just turn that check mark off and they go away and you could just turn those on and off and on enough so you also have the option of doing the first column, which will bold your first column now in this case. I don't have labels going down the left hand side all my labels air across the top so usually you turn this on when your column one you're your column a has information that's listed across so I'm going to turn that off but here is one of my favorite little tools right here this total row so those hetero and then total road I'll put a check mark there and what happens is down at the bottom I have a new row thirty one that was not there before and this total row allows me to do in titanius statistics without even having to insert any formulas at all myself. So if you notice in a column uh oh where it says price paid I can see a grand total down at the bottom of my price paid and so I can see that the sales at over these three days were two thousand six hundred seventy three dollars let me go ahead and zoom that in a little bit and coming up here to my zoom and I'm gonna bring this up to one hundred fifty so that you can see my screen a little better also note that because I turned this into a data table instead of seeing a b c d when I scroll down it's actually putting the labels up at the top of the data instead it's a little harder for me to tell you to look at row g right now, but I can now tell you hey, look a birthday now with that price paid if I click on it so I'm clicking on price paid row thirty one there's a little drop down right here and if I click on that I can change what statistic I see they're so right now it's giving me my grand total but I could choose average and I can see that my average sale was ninety two dollars in nineteen cents I can click on it again and to account and I can see that I made twenty nine sales and now I'm going to go ahead and put that back to some let's look at it in another way here's my birthday maybe somebody wants to know what the average age of my customers are I can go down to the totals row in thirty one under birthdate and drop that down and let's do an average and it will actually find me an average of the birth year so my average cussed shimmers somewhere in their late forties. So all of this is just this total row right there and you can turn it on and off and see, you know, even standard deviations in variants he really have a lot of options at your fingertips does anybody have any questions or comments about the totals around? Ok, holly is really excited and that okay so now let's go back up to the top row, I'm going to zoom back out a little bit and now, let's, look at some of the ways that we can sort and filter this information so maybe I want to see my customers sorted by name so I can click on the drop down under last name. And now I consort that ascending. And so now we re sorts my entire table alphabetically by last name. Now here's exactly why this is so handy. Have you ever been an excel table and you quick in the cell and you do a sort and just that one column sorts and all the rest of your data stays in place? Has that ever happened to any of you? You instantly blow your entire spreadsheet that excel has a tendency if you highlight one column and sorted it sort of just that one column and everything else stays where it was and all of a sudden you don't know who bought what or whose year goes with what. So when you take that first initial step of turning into a table, then all the rows stayed together as you do any sort of any filtering, and so that will save your table for you you can, once you have a sort in place, I concede e that there's a little up arrow here in that so in that dropped down, so I can tell that I have a sort that's actually in place here when I now what I want to do is I want to analyze my sales by my event, and so I want to group them by the event, and then I want to sort them by the prices ascending. Now, in order to make that happen, I actually have to do the sort in the reverse order. So the first thing that I need to do is sort by price, so move over to column. Oh, and run your sort ascending. And so now you see all of the purchases by price and then move over, but all the way back to column b and sort that columnist sending and now and I'll quit this little x in the corner to get rid of that drop down. And now I can see all of my christmas bizarre sales, and now they're in numerical order. So that's, the way to do multiple groups you have to start with at the smallest level on don't go up to the biggest level, the one technique I haven't shown you, you'll notice that I'm sitting here scrolling back and forth and back and forth between these if I want to be able to scroll and still see my column b with my events. And I want to be able to scroll down and still see my column one with my headings you can actually freeze your pains so I'm going to click in cell c two which is the first person's name on the list so the intersection to see and to and I'm going to go up to the window menu if you do have your screen in full screen the way I d'oh you have to jam your cursor way up to the top in order to make the menu rochelle and I'm going to go to a window and I'm going to go down to freeze pains and when I do that you're going to see a very faint hairline to the left and above and what that allows me to do is now when I scroll teo the right my show stays in place so now I can see christmas bazaar and the price both at the same time and if I go the other way if I scroll up my head arose stays there no matter how far down I go so freezing the pains is a great way of being able toe always see your headers even as you work your way around your spreadsheet when you want to get rid of those frozen pains, I go back up to the window menu and choose it choose unfreeze pains and those lines go away and now I'm back to my regular scroll the next thing I want to demonstrate is filtering, which is my favorite well, this was one of my wealth factor is the first time that I saw this that was one of my big excitement moments, so hopefully it will be for you so when I'm looking at this list maybe I just want to focus on my sales from the holiday blitzen so what I'm going to dio is filter so I'm going to go back to my column b event little drop down right there and I can see down here of filter and right now everything's on select also I'm goingto unclipped the check mark next to select all and I'm just going to put the check mark in front of holiday blitzen and what that now did is just show that one of them I can see that I have this in place both because I see a funnel now right here in the little arab and look over on the numbers on the left hand side see how these air all blue those air indicating to me that I'm not saying all of my rose rose one through twelve are completely missing and from twenty six to thirty one are completely missing so if you ever see blue here you know that you had hidden rose the corollary would be the same if any of my columns had turned blue I might have some hidden columns in there so by doing this, I can now do that same sort of analysis just on that one show. So now I can see for example, how much money I made just on that one show and then I can change it. I'll go back to my holiday blitzen and I'm sorry. Just back to my event column b and that will turn off holiday blitz in an alternate on the portland festival of the last minute and I can see him and it make nearly as many sales they're only made four sales and I only actually made three hundred nineteen dollars. So what does that tell me as a business owner? Don't do that one next year and you can filter on more than one thing. Maybe I just want to see how many planet items I sold at a particular show. So even though I have this one filter on I can now go over to column l two the item purchased and I can either do a search right here just for the word planet and now it just is showing me the ones that have the word planet in it or maybe I just want to see necklaces and so I can search within my results and I just noticed a soon as I start typing, it just does the matches with it automatically and, you know, I could also hear I could use any one of these cells to add account to this just so I see how many records I have and I have right now so I have that filter on from necklace this is why it's so dynamic I could now come back to event and maybe I want to see my holiday blitzen and by portland vessel of the last minute but not the christmas bazaar and so it just added that next show and again all I'm seeing are necklaces sold because I searched for the word necklace does everybody see why this is such a wonderful data analysis tool now when I want to come back and show all of my items again in the one I want all of right now I'm in this this is my event work pain and so I'll click right here on select all and so all the events are now back and I'll close that and I'll come backto item purchased and I will go back to select all on that and now everything is back where it should be so that's how to turn your spreadsheet into a done a dynamic filter and do some basic statistical analysis yes if we want to add another function in the title role how can we do it the ones that air there are the on ly ones that are available to you so there's no way to add oh actually I'm sorry I'm wrong more functions right here. So let me find one, that, actually, that was a phone number. That's not going to be helpful. Um, so if I click on the drop down and I go to more functions, I can then use the function browser and choose any function. That's in the whole program. The great question. Thank you. And I'm gonna hit escape to get out of that and go back to the data that I had there.