 Statistics and Excel. Hamlet, Harry Potter and Statistics. Let's take a deep breath, hold it in for 10 seconds, getting ready for a smooth soothing Excel. First, a word from our sponsor. Well, actually, these are just items that we picked from the YouTube Shopping Affiliate Program, but that's actually good for you. Because these aren't things that were just given to us from some large corporation which we don't even use in exchange for us selling them to you, these are things that we actually researched, purchased and used ourselves. Ugg slippers. I usually walk around my home in just my socks, but I wanted a high quality pair of slippers that didn't have a heel on them so I can slip them on easily, give me a little bit more warmth than just my socks provide and which has a sole on them so I can deal with messes in the home such as spilled liquid or broken glass without getting my socks wet or my feet cut up and the Ugg slippers do a great job with that. I like the quality of the slippers. They feel like they're going to last a long time. They will probably outlast me, so I recommend the Ugg slippers. If you would like a commercial free experience, consider subscribing to our website at accountinginstruction.com or accountinginstruction.thinkific.com where we have many different courses. You can purchase one at a time or have a subscription model, giving you access to all the courses, courses which are well organized, have other resources like Excel files and PDF files to download and no commercials. Here we are in Excel. If you don't have access to this workbook, that's okay because we'll basically build this practice problem from a blank sheet and therefore you can just open up a blank sheet and work from there. If you do have access to this workbook, there should be six tabs down below. We're working two practice problems, each of those practice problems having three tabs related to it. The example tab in essence being the answer key, the end result, the practice tab having some pre-formatted cells within it so you can practice focusing in on the heart of the practice problem. The blank tab having just the data set so we can practice formatting the cells as well as the heart of the practice problem, in this case building a chart. Also note that I'm currently in dark mode. I think that's easier on the eyes, so I hope that doesn't throw anyone off. I do recommend the dark mode if you're going to be using Excel a lot, but the layout should be the same. It's just going to be dark in the dark mode. Let's go on over to the Hamlet, look at the Hamlet problem, see if we can solve the Hamlet problem. We're not going to solve Hamlet's problem. He has a lot of problems, but the problem that we're working with relation to Hamlet is going to have the data on the left-hand side and then we're going to be building this table from the data, this graph from the data, this bar chart from the data. Now if we go into the second tab, the practice tab, we've formatted the data on the left-hand side so it's a little bit easier to go right to the heart of just using this to create our graph. The third tab just has the data on the left-hand side and we'll practice formatting the cells and then organizing them and then creating the bar chart so we can practice the basics, the essentials of Excel as we go. Now note that if you don't have this data at all, it's not too much data so you could just simply type it in if you so choose and you can work from a blank sheet. You can also look this up online, you can look up the word count for Hamlet, that's what we have in here, the word count for Hamlet and then you can copy and paste that information into your Excel worksheet and then you'll have your data set and we can basically move from there. Because this is our first practice problem within Excel, we want to go over some fundamentals about Excel. So the first thing to note is that we can name any cell within Excel using this grid format using the lettering up top, A, B, C, D, E, F in this case and I think I was on F5 if I move down 1, 2, 3, 4, 5, F5. So this cell is F5 so we can name that cell, that naming is also over here so it's in F5. Also note that if you start typing in a cell then you're going to be within the cell so if I just start typing here now I'm kind of within the cell and if I click off of it now I'm off of the cell. If I want to go back in here and start typing again, if I simply click back on the cell and start typing, it's going to overwrite what I did before, right? It's going to overwrite what I did. Let's undo that. The undo, one way you can undo is right here. This button is quite useful. You can also have the keystroke control Z, you know, to undo. And boom, right? So that's going to be a quite useful button. So if you want to go in the cell then you need to double click on the cell. So if I want to go to the end of that F I need to double click on the cell and now I'm in the cell and I can continue typing from there. The other thing we just want to recognize is that if you put a formula into the cell they usually start with an equal sign. So if you put an equal sign into the cell then if you start clicking on other things over here Excel thinks that you're trying to create a formula. And that's why it's you get the sticky bit right where there's a formula. If you didn't want a formula you got to get rid of that equal sign and then you can basically move around again. The same would be if you started with a plus a plus Excel thinks it's going to be a formula. So it's going to be, you know, stuck into it there and you're going to have to delete that before you can basically move around again. Quick recap up top. This is what we call the ribbon up top. Many of the functions within the ribbon are going to be in the home page within the tabs of the ribbon home insert page layout formula. You've got the groups in each tab. So you've got the home tab with the group of the clipboard, the group of the font, the alignment, the number of the style and so on and so forth. So when you start using Excel you'll probably intuit intuitively go to these items up top. But if you want to formally describe to someone where something is, you can say it's in the tab home tab. It's in the group of fonts group, and then you're you're narrowing down to this kind of box. So that makes it a little bit easier. This is your quick task bar down here and most of the stuff is the default. These aren't in the default. I put these down here, but the undo is like in the default down here. And then this is your formula bar up top. So anything you type into a cell is also going to show up on the formula bar, which is useful, especially if you're, you know, your way out here somewhere, you're typing something like way out here. And you're not in the cell anymore, right, but you could still see the formula kind of up top. All right, so those those are just some essentials that we're going to that that we kind of want to understand going into basically any practice problem. Now down here, you can increase the size. I'm at currently 160. I can zoom in like this, or I can hold down control and scroll up on my scroll wheel. So that allows me to zoom in and out also quite useful. Now typically whenever I start a new sheet in Excel, I like to see the numbers in a number format. So right now you can see in the home tab and the numbers group were in the general formatting. So typically I'll select the entire sheet and I'll put my baseline or underlying formatting that I like to work in. You could do that with the drop down here number group and format it up top, but I like to right click on it and then go to the format cells. And then I'm in the number tab. It's currently in the general format. I'm going to I like going to currency, but then make the negative numbers bracketed and red. And then I remove the dollar sign. And in this case, I don't need the decimals. So I'm going to remove the decimals as well. And so that's my standard format usually for me. So I'll do that basically every time as my underlying data format. All right. And then in this data, sometimes it might be useful to put the labels up top. So this is actually the number of words in Hamlet. Now our idea here is that, hey, look, obviously, if I just take the data of the number of words, then it's not the same thing as Hamlet. I'm losing meaning by just having a list of the words in this format. However, this format of the words can give us some information because it can tell us how often, like Shakespeare uses particular words. You can do similar things with tropes and whatnot. And that might give you some information about how, you know, a writer is pursuing his craft, right? So and that could help that could be useful, even though it's not we're actually not telling the story here with this word count. So I'm going to add I'm going to add a row up top. Now a few ways you can add the row. Most people might think if I'm going to add the row, I'm going to highlight this thing and then try to drag it down like this. I can try to drag it down. That's one way you can do it. Probably not the most efficient way. I'm going to undo that. You can also highlight the whole thing and right click and cut it and then put it down here right underneath it. That's actually the same thing. I right click and I pasted it. That's actually the same thing as moving it the way we did it before. So that's actually a little bit more efficient oftentimes. But what I really can do, there's nothing on the right side of the screen here. So what I want to do is add a row above it. That's probably the easiest thing to do. So I can select the number one here and I'm going to say this whole row. I want to add something above it to fit my my headers. Now, if there was something on the right, I'd have to worry about doing it this way. But there's nothing on the right. So I can just right click and insert cells and that will insert an entire row. So hold on this hour. It's highlighting something. That's why get rid of the dancing ants select and then insert. All right, so there it goes. Now, if there was something on the right, then I could still do it that easier way. But it takes I can select like these two and so I just want to insert cells above it and push everything below it down. I have to be careful if there was something below it with this technique, but there's nothing below it. So that's what I want to do. Right click, insert. And then I want to say shift the cells down. So now I've got room above it. So I could say these are the words. I'm just typing in words. Notice it's formulated in the formula bar. If I want to move to the right, I could click over here and I can also hit tab. So I'm going to hit tab and that moves it to the right. And then here I'm going to, I'm going to say, well, let's say word count. Let's say word. I'm going to double click on here. Count and put count. And then over here I'm going to put words. All right. Now on these two, notice this cell is longer than what I have room for. And I was still able to override it on this cell. But I could try to make this cell longer. I could put my cursor in between A and B and extend the cell like that. My other methods that I might use here is I might say maybe I want to wrap the text home tab. I can go to select these two home tab. Let's go to the alignment group and then wrap the text. So what that does is it automatically wraps the text. We've got to be careful of that because it also makes this wide cell if there was anything to the right of it. But in this case, I'm okay with that. I also want to center the text, home tab, alignment, center in the text. So now that's not the one I wanted. Just keep it there. Center is this one. Center the text. Now because this is a header, you also might format the header with a different color or something like that. But in our case, and you might make it bold to make it a header, home tab, font group bold. Now the next thing I would like to do with this data set is sort it by the most used to the least used. So this is basically an alphabetical order. I want to sort it this way. So one way we can do that is I could I could select all this information and then go to the data tab and I can add a filter sort and filter adding the filter. And that puts us these little filters up top. So now I can sort the data. Now that works but it's not my favorite way of doing it because I kind of I feel like I might lose I would rather have it in a table usually. So what I will do is I'll turn off the filters and instead I'm going to select the data. Now note when I make a table, I could add the whole data set like this and then go to the insert tab and I'm just going to put a table around it. Or because there's no blank cells in this, there's no blank cells that are all populated and the first two are boldened. So Excel can note that that's probably a header. Then I can go to insert and table with just one cell highlighted just one and say insert the table. And it puts the what they call the dancing ants. The Excel is fun guy calls makes dancing ants around it. And that really they're more like marching ants. They can get a little groove on. If you put a microphone like a like like if you looked in real close, you can see they're actually dancing and not just marching. They got a lot more going on, but we can't see it because we're out. But any case, then you can see that we have the table range, which is don't worry about the dollar signs right now, but a one to B 21. That's the range of the data a one the cell to this range of B 21 is the range of the data. So we're going to say that looks good. We can also just see it visually insert the table. So now we have our table. Now note on a I can make a little skinnier again and it'll it'll still wrap the text. All right, so now we've got these filters that dropped down. So what I'd like to see is is the filter by the word count. So I'm going to filter it by the word count hitting this drop down. I want to go from a to Z. This is an alphabetical order. It's going to go but it's going to go from that'll take it the other way showing the smallest to the highest. Right. And then if I if I flip that and go from Z to a now I've got the largest, which is probably the most important on top. So if I was going to sort this data, this is the first thing we probably do. Right. We'd say, OK, here's all the words which uses which word is used most the Lord. Well, that might tell us something about the thing. Maybe it's clearly going to be a play about about, you know, lords. Right. Come up a lot of high brow people in it or something, you know, you would you would expect and so on. And so that gives us a little bit of a representation of the data. Now, if I wanted to visualize this data, we can do just the standard bar chart, which is generally fairly easy to put in place. I can select all of this and we can say insert. And then if I go to the charts, note the recommended chart. Sometimes it's a useful thing to look at because it gives you some different representations that you might be not be as familiar with. That gives you some good ideas of different ways to format the data. So I'm visualizing the standard bar chart. But of course, you could flip the accesses on it and represent it represented represented this way. Or you could you could see it this way and, you know, different kind of representations. But there could be a problem with this bar chart. So let's take a look at it. If I insert a bar chart just like this, notice there's an issue with it because, you know, obviously nothing's being represented here. Right. Now, one of the we could try to adjust the bar chart from here. But one of the things that'll make it easier to automatically put the bar chart in is to switch the order here. So I'd like the words on the left hand side and then and then the word count. Let's call it word not words count word count on the right hand side because that usually then Excel will pick up the X axis for the items that are on the left. So let's try to move that over. Now there's a couple ways we can move it. We could try to insert a column on the left and move it. But the easiest way to do that is to actually select the entire column we want to move. So I've got my cursor on B. I've got that arrow drop down. I select the entire thing. And then the key is that you have to hold down the shift and then you put your cursor on the on the in between spots. So you get those four arrows hold down the left click. We're going to click and drag now and drag it to the right and then let go. So now we've got the words on the right. The word count on the left. And so now we should be able to insert our table. Also note that when you insert the table you could select simply the data down below. If you pick up the headers Excel should really generally recognize that that that is the header. So I could pick up the entire thing with the headers like so. And then we can go into the insert tab up top. Once again we go into our charts and we're looking for the standard bar chart here. We have different variations. These being basically the same kind of chart. So I can look at it this way. This way we've got our more 3D kind of bar charts and so on. And then we have the axis is turned the other way with our bar charts. But let's just do the standard bar chart. So there we have it. I'm going to pull this. I'm going to put my cursor on that left part of it. Pull it to the left and then notice it put it put up top word counts. Now that might not be exactly the title we want. So I could click on this and say this is going to be. Let's say I'm going to go into it and say this is Hamlet word count. Right. So then we've got our title up top. I can make it a little bit larger. And in essence it does everything that we basically wanted to do to make our chart by just basically implementing it there. So which is nice. So now we've got this pictorial representation. Now I just want to point out that this is not exactly a histogram but it's given us a similar kind of thing. So if I go to the insert notice that we have up top these items we can insert a column or bar chart versus this one which is to insert a statistic chart. So if I hit the drop down we have a histogram here. So the histograms we'll look at later which looks similar in terms of a representation and the difference is that we have these buckets within the histograms because we're usually looking at an x-axis that has numerical data within it. So we can actually format the data to use a similar kind of bar chart. So we'll look at kind of histograms later but we want to keep those two things separate in our mind. But the concept is similar here. We're saying hey look this data on the left is giving us useful information in a lot of ways but it's not useful unless it's organized. It's great here that we can organize it from top to bottom and I can see the words that are used the most but of course the pictorial representation is going to give us even more data. So if I just look at this pictorial representation it's probably going to more easily show us exactly what's going on and more clearly show it easier to display to other people and all that kind of stuff. Now if you needed to add other items within it if I click on this I just want to point out if I click off of this table we only our tabs up top stop at well I have a quick book you might not have this last tab over here but you might stop and help you might not have developer either. But the point is that if I click on this table we get these two other tabs up top the chart design and the formatting tab. So if I go into the chart design tab you've got the add chart elements so if I hit the drop down axis title chart title data labels we've got the quick layout information you can change the color scheme fairly quickly up top so this is the default blue it's like the default color scheme so anytime you want to look a little bit more unique you might want to change it from the default. If you hit the drop down here you've got your other kind of custom designs within this area so you can get the same basic chart. If you wanted to switch the row and columns easily you can do that here here's the data set so if I go into the data set now you can see this is the word count and the information on the data set on the left if I wanted to edit this that shows us that this is the word count the series and the series values being shown here so if I wanted to change these I could go into here and change where they're coming from which we might do in future presentations so I'm going to close that but we had it pull in by default correctly because we organized the columns properly with the words on the left and the numbers on the right so it can give us the word count on the X and the numbers on the Y as basically the default change chart type so if we wanted to change the chart type we don't have to delete it and do it again which is what I normally do you could just go here and change the chart type from here and move the chart if we wanted to move the chart to a new sheet notice I could say instead of putting it in the same sheet maybe I want the chart in its own sheet and I can put it here and name the new sheet and then I'll make a new sheet down here so we can have it on its own if we so choose and then if I go to the format tab we've got some more the current section chart area we've got the insert of the shapes the shape styles the shape fill on the colors and what not that we can put more information or more fancy stuff into the word styles and accessibility arrange and the size now note you can go into a lot of those too by clicking on the elements in the chart so if I click on the element I could delete the title if I want or I can right click on the element and pull some options that way if I wanted to do something with the sidebar I can click on the sidebar and if I double click on it it'll give me the information related to it on the right if I wanted to do something with these bars if I double click on it it'll go to the format tab up top I can change the color and what not and I also have the information on the right which will open up here if I wanted to do something to the format down below I can double click on it here so there's multiple ways to get into the same stuff also on the right hand side if I hit the plus button you've got multiple ways to get into the same stuff a lot of the time so we've got the axis we've got the axis titles so if I want to put a title you can see on the axes I can click on this and then I can click on say this title and as I type in it I can call this word count now it doesn't show up here but I can see it up top so it is doing it even though it's a little weird I can say word count and then when I hit enter it'll populate and if I click down here and do the same thing this is just going to be the words and enter so now we've got our names that we have in there I can hit that if I say this is the chart title the data labels which are often useful to have I can add the data labels if I want so there's the data labels I can double click on the data labels and get more information how we're going to populate the labels on the right hand side if we so choose and the data table down below we've got the error bars so we could add a little bit more information on the error bars the grid lines you could turn them on and off a legend we don't need a legend because we only have one thing but if we have multiple things on the bar and then a trend line if we wanted to add the trend line so there's just a basic overview of the standard kind of chart so now that we've done this one time let's do it a little bit more quickly with a similar thing for Harry Potter so I'm going to do this quickly on Harry Potter I'm going to go to the Harry Potter tab and we've got our data so this is the most frequently used words in the series so let's do this a little I'm scrolling up I'm holding down control I'm scrolling up I'm going to pick my my information so I'm going to pick this information I like to make it into a table so I'm going to go into the insert and make it into a table and then I like to format the data by highest to lowest it's already formatted that way but let's do it again hitting the drop down Z to A and then if I want to insert a table on this data I can select the entire data set and it's as easy as going to insert and then charts I could do the recommended ones maybe some of these a little bit more unique or it's not a little bit different style of a table but I'm going to go to the make my own standard one like this way so there's our table and you might let's do a sideways one so here's the same kind of concept in the side format so we just changed the axes and then again I can double click on the item up top and say this is Harry I keep on saying Harry like Harry instead of the name Harry I don't know if that's a Harry word count and so there we have this one and so and then we could we could make changes to some of these items if we want if like this legend down here if we wanted to change that notice it's picking the minimum number and then the maximum and it's going up by units of a thousand so maybe we we can change the look and feel of this a lot by saying what if I wanted these units to not go up by a thousand but five hundred or something like that then it's going to change you know we could change the look of it so let's put it back to a thousand for now we'll see that a little bit more when we get to the histogram or I can reset it to auto here's the auto set it's at ten thousand and I put five hundred so that changes a lot but we'll talk more about that later so this will just keep with this so if I did the same thing down here we have two frequencies these are the most frequently used words and Harry Potter and then they they have these are displayed frequency of non-stop words meaning they're not like the and two which are going to be used more often so these might be more relevant words in some cases because they've removed the most common words that might not be as much of interest so let's do the same thing I'll just insert a table and then okay I'll sort it from Z to A and then we'll insert our table here insert charts and I'll make another sideways bar chart to match the one above and we'll say this is Harry word notice I'm looking up here because that's where it's typing word frequency with this is for non quote stop words quote and quote non non stop word there shouldn't be a quote here non stop words okay and this one Harry word count we'll just keep it with that alright you get the gist of it you don't need to be all specific on it and so we can kind of format these and that's that's the general that's the general idea so now we'll we'll move to the next time and see well what if this was what if this was like numerical data over here or something right and then we have then we might need to use a histogram which is similar to this and we can actually combine the data to still make use a bar chart so we'll talk about that in future presentations