Excel Charts and Pivot Tables

[MUSIC] All right, greetings, everyone, and welcome to another great episode of OfficeProTV. I'm your host Daniel Lowrie. And in today's episode, well, we are starting our new series on Excel charts and pivot tables. And guess, what joining us in the studio is our resident expert on that very topic. Our good friend, Ms. Vonne Smith. Vonne, we're glad to have you in this new series. It's gonna be a lot of fun. How it's going today? It's a great day Here at ITProTV. [LAUGH] We do have fun here, don't we? Yes, wait, we're OfficeProTv. Yeah. Yes. Same thing. Well, we've got this here. Yeah, we're all [INAUDIBLE]. It's all one big family. [LAUGH] Yes, and we're exploring the family of charts and pivot tables in this series. This is our first in the series that we're gonna start off with, just some basic foundational knowledge. Because we really wanna just separate out the charts and the pivot tables. Because it seems that everybody wants to know how to do a pivot table. It's like, just make a pivot table. And it's like, my gosh, it's the scariest thing in the world. And it's actually not that bad, once you break it down. Same with the charts. If you really kinda just like, let's talk about the terminology first. What is a series? What is an axis? What is a value label thing? [LAUGH] [LAUGH] Hey, guess what? We have a PowerPoint, so I know what we're talking about, so. [LAUGH] Well, I think it's funny you bring up the terminology. I think that is probably the very beginning of the, you start getting heart palpitations and hesitations about doing Excel. Because it's got some strange terminology. Pivot table, that just sounds like math to me. And that makes me have anxiety attacks. Yeah, and it's one of those, okay, we need a report. You need to generate this now, and it's like, what? And so it's a lot of, well, I know what I would need it to look like. Chart needs to look like this, but I can't make the numbers do it. And that can be a little bit of a struggle. Now, of course, we're gonna have perfect demonstration, where, look, everything works perfectly! That's right. And in the real world, it really also comes down to, is your data set up properly to be able to show the report that your boss wanted two days ago? And you're just like, I don't know how to do a chart so. [LAUGH] So we're gonna try and just start from the very beginning. This is why we have our introduction to charts in the particular topic of this series. And so we're gonna just start getting familiar with this. So I do have a PowerPoint ready to go. It's called Parts of a Chart, and this is something that I've actually used in a couple other series. So if it looks familiar, you've seen it before. So now this is where, in this particular slideshow we're talking about, okay, well, we could have a table of information. Now, of course, in Excel everything's in a table, in a sense. What I mean by table is we have everything in columns and in rows. It is a spreadsheet application, to clarify that formatting as a table. Tables in Excel are a little different. But if we're using the generic terminology, that's what we've got. So if we could see, all right, well, it looks like definitely Bishop sleeps more than Fuego, and they both seem to play a decent amount. But it looks like Bishop's got him won there and Fuego definitely likes to meow more than Bishop. I'm looking at that. But wouldn't it be a lot easier if we had some graphical representation of our data to say, wow, well, then yeah, the orange bar is much bigger than the blue bar right here? And then which one is the orange? Orange is Bishop, so a chart is really just a graphical representation of our data. Just like we have have smart art, which is a graphical representation of our data right here. So we can visually see what our information is trying to tell us right here. So this is where we have, this is a clustered column chart, and then you can see that we have our series labels on there. We also have our different value axis and we have our legend. See, I'm already using that terminology if a chart right from the very beginning. But what is this doing? It is comparing the hours, or really we're comparing Fuego and Bishop to each other in each category of sleeping, and eating, playing, and meowing. So you could see that along the bottom. And how are we measuring this? Well, it says hours right over there on the left-hand side and we have something at the top that says, Activities Hours a Day. So all of these things are just going to kinda help explain what this information, well, what we're actually looking at. So let's jump into some of that terminology that we're going to be using within Excel, and really what Excel is going to be forcing us to use in this. Everywhere where you see the orange, that is known as a data series. It's a series of information that has different points along the way. The 12 hours of sleep that Bishop does is a point. The three hours that he eats, that is in point. And you can see that we're trying to correlate where that works within this tabular data with. This is more within PowerPoint. But you'll see it when we see the example in Excel, that that column, Bishop is a column, Fuego is a column and the activities is a column. And in the rows, you have the sleeping and the eating, and so that's where you have the rows going across the bottom. And the columns are the different, well, in this sense, columns of the chart. So we have our value, access, and category access, because those are the categories of what we're comparing. The sleeping and the eating of Fuego and Bishop, so that's what we need to have across the top. Because we are working on an x and a y-axis, or what they're saying is category on the bottom, value on the left. Let's see what's on the next slide, and then all sorts of parts of your chart. You have the chart's title, well, that's pretty self-explanatory. [LAUGH] Yeah. That's the thing up at the top. [LAUGH] So tell me more about this title of which you speak. [LAUGH] Yeah, the title is the title. It's at the top. Yeah. [LAUGH] About the thing you're looking at. [LAUGH] [LAUGH] Exactly, yeah, so we have the title, the legend. What does blue represent? What does orange represent? And that's what I'm looking at. And then, of course, we have an axis title that this is the activity and the axis title here, where we have it. And I'm just kind of emphasizing that the data series, the grid lines, and all of this is, we're plotting this chart. So, this is the plot area all around in here, which is different than the chart area. The whole box that everything lives, and all of these are selectable items within a chart. And so, knowing what you want to format, well, you gotta know where to click and what you want selected. So we have some nice references right here to that terminology. The thing on the bottom, and I've seen these types of things, I've seen charts that have this, well, table at the bottom that gives you that information. This is known as a data table. Because while the bars will go ahead and represent, I can easily see that Bishop sleeps more than Fuego. And these are all whole numbers, but what if this was 12.8256 and 8.2584? That's where you might need to see that information in its entirety, so you can add a data table to make a little bit more sense out of it. But then you could also switch this. Now, this is just another way that we could interpret this data. Instead of in one category we're comparing the sleeping, eating and playing, now we have it as Fuego. And all the things we're comparing is the amount of sleep, eat, play and meow. So you could see that it's just It's really just take the information and just flipping it on it's side, okay? So now you can kind of compare Cats as the category rather than the Activity as the category. And I probably should've changed that label right here, that it's not really that activity anymore. It would be Cats, so [LAUGH]. [LAUGH] But, that's okay. We'll pretend that didn't happen [LAUGH]. We'll have the line graphs, cuz there's all sorts of different types of charts. We have our clustered column here. And then we have a line graph. Graph and charts, those are kinda used interchangeably. We don't really call it a line chart. At least that's not how I remember doing this. Cuz I remember having to do this in school and put the little dots. And then draw all the lines like that. Yeah, I think they just called it a line graph where I went to school. Yeah. Yeah. Yeah, graph, chart. Chart, same thing. Same thing. Six of one. Half dozen or the other Some other. Did I say it right? You did, actually [LAUGH]. Yes [LAUGH]! So proud of you. Score [LAUGH]! [LAUGH] All right, so we have different ways, and this has specific style. We've talked a lot about formatting graphable elements with styles in our Excel advanced series. So all the same things that you can do with a shape or an image where you can, well I guess we'll stay with shapes. Fills, outlines, the effect where you can put drop shadows, you can put glows. You can make anything 3D. All of that applies, because this is a graphical element. This is a piece of art, if we'll call it, to go ahead and show the information. Then here's another type of chart that we see a lot of times. This would be a pie chart, or a pie graph. And this does have some styles applied to it. You still have a legend. But when this time what we're doing is you're really only comparing one series, one category, that Fuego, where you don't have Fuego and Bishop in here. So pie charts is just one thing that has pieces of it. So you could see that we're looking at the activity here. And I also have some data labels on here of what is the actual value. And then what percentage of the whole that is. And guess what? I didn't calculate that. I didn't do any of that math. Luckily, Excel does it for me. All I have to do is check off a little check box of Use Percentages. Like, yes, no math [LAUGH]. And then of course we have an exploding pie right here. That's what it's actually called the amount of explosion. And one little piece right here. And so we can see different ways that we can take our data labels. Instead of having them here in the middle with a specific style, we have them on the outside. And then there's also changes where the legend goes. So you can see that you can start moving around these different components. But again, knowing that these things out here are known as data labels. And knowing that this thing down here is a legend. And you're just kinda getting that terminology, is going to be really important. Now the nice thing is that you gonna have a lot of help along the way, when you start inserting charts and you start formatting it. And we're gonna take a look at finish chart in just a second, within Excel, not just in PowerPoint here. But I always like to end on, whoo whoo. I like that. Yeah [LAUGH]. That is a special, special background you have going on there. [LAUGH] Yeah, right. [LAUGH] The slide changed, and he went like, whoa! [LAUGH] [LAUGH] And this is to prove a point of well, while you have the capability of putting a picture of Bishop in the background of your plot area, making some bubbled textures within your data series- It may be ill-advised. That's what I think you're trying to tell us [LAUGH]. Yeah, the whole idea here is to have a graphical representation of your data. And not have people go, I have no idea what I'm looking at [LAUGH]. Yeah, so something more along like this, that was a little clearer information. That's what we want to get the point across. But I will show you that you can add patterns, and textures, and gradients, and different colors all over the place, if you want to, of formatting theses elements. Cuz, if you really think about it, this is, it's just a rectangle. Yeah, this is a rectangle, that's just a line. These are graphical elements, and of course the data's attached to it. So just know that even if you can, you might not want to [LAUGH]. All right. Just cuz you can doesn't mean you should, right Vonne? Yes, exactly. So I have an example file that we will look at. Again, just getting our feet under us and just getting familiar with it. We're not talking about how to insert the chart just yet. And we're not gonna do any crazy formattings. It's really more of, okay, so I have a chart. If I select it, what happens? Just like if I was selecting an image or some type of shape, you are going to have your contextual tabs up at the tart. At the tart? [LAUGH] Is that what I just said [LAUGH]? Yeah, it looks like a tart [LAUGH]. I think I was trying to combine top- Raspberry or blueberry, maybe? Mm, that sounds yummy. It does. I'll have that. Yeah [LAUGH]. So [LAUGH] we have the top of our ribbon with our chart tools. That's what I was trying to say, it just kind of blended together. We have a design tab. And on this design tab you have the different things that would be more associated with the design of the particular chart. All of those elements that we were just talking about, like the data labels, the chart title, you can add them into here. Now notice, some of them are grayed out. And that could be, well I know it is because of the type of the chart that we have created here. This is a pie chart, and so there aren't going to be as many options. Like we can't really put in lines here or trend lines, because that's not applicable within this particular type of chart. But where we put the legend, that's part of it, so I could go ahead and see that. And let me just scroll down just a little bit so you can see that the legend is right here, so I can go ahead and move it around. Remember I said that it'll help you out a lot along the way. I mean, these are some good graphics right here that say, the right means it's gonna go to the right. [LAUGH] Hey, imagine that [LAUGH]? So, and then the left and the bottom. And so you also have that hover in the live previewing, that's the hover to discover. Yeah, that's always nice, right? The live preview stuff is where, well, I don't know, cuz back in the day what you had to do, you had to click on it, look at it. And go, okay, maybe I like that. Ctrl+Z that, go back, do it again. Okay, look at that. Okay, well hold on, how'd this look, right? It's a real pain in the butt process. This makes it easy. You just hover over it, and then you watch it go. That's nice. Yeah, that is. And once you get the information in the chart right, all of the rest of it is gonna, hopefully- Right, just style, personal preference. Yeah, and it makes it a little easier, cuz they have really beefed this stuff up a lot. And then the data labels, now that I know. Even if I didn't know the terminology of what is a data label, it shows you those things that are gonna be inside, [LAUGH] outside. And the hovering, it's a little. Well here, you know what? I'm gonna just take the, whoo, hello! No, you're not [LAUGH]. The Ctrl-Z [LAUGH]. I'll just move this over here so you can see when I start adding those different labels it's going to preview them for me. They're a little small right now. But that's nice that you could see the difference. That's pretty good. Right. I like that. Right, that was pretty easy, wasn't it, yeah [LAUGH]? Yeah, super. So adding the different chart elements will help you along here. Let's go ahead and select this one over here. You also have different ways that you can do your layouts, okay? A lot of pre built stuff. It's kinda like with the smart art. Remember, once we get it in there, you just go ahead a throw style on it. And it's like, you're done, right? So and this gives me lots of different options and presets if how it can have certain amount of elements combined into a specific layout. And then I can quickly choose that. We have our change colors, that's pretty self-explanatory. What is this pulling off of? It's pulling off of the theme. I went ahead and picked a theme that had some pinks and purples in it. Imagine that. And so you can go ahead and change that. And then you have your overall chart styles to where it's going to format each one of these elements individually. Well, actually not individually, they have the different ones. And then it's all part of a group and it is a style. So we can look at that. That switching, remember we talked about how we were comparing instead of the different categories down at the bottom, we flipped that around. Depending on the chart, it's just a one quick little button where it just goes ahead and flips. You would also change the chart type, then you can move it over and you can also select the data. Now the other thing to watch while I am on my chart is what's being selected. Because the Design tab, this is kinda like an all encompassing type of thing, it's gonna format the entire chart. But when we're getting over into this Format tab over here, this is where it can start getting a little specific as to what do I have selected. So, all those things that I told you about in a chart where you have a plot area, you have the grid lines, it's all about what you have selected and that's really anything it's not just particular charts. But watch when I hover over different areas. You see I hover over and it gives me that tooltip that says plot area. I hover over here, that's my series. What series is it? It's the Fuego series. What point is this? This is the Eat, that's where I have. And my value is 10. Let me scooch on over here and let's move this over a little bit so you can kinda see it all together. Whoops, hello. Let's move this way over there. Well, nop. No you're not. No we're not, how about we do this? I will scroll down. You push it to the back. There we go, okay, there. So we can see it a little closer together. So when I have this selected, if I click on that series, notice over here in the chart you see how you have some different kinda colored boxes that happen to give you an indication that this is the Bishop. So, again, it's trying to pull it together for you, to know that, okay, well, then that's that series, if I select this, that's the Fuego series. And I also know that I have the entire series selected, because I see one, two, three, four. I can see the dots on each one of those columns as opposed to selecting this series. If I hover here and click, that is the plot area. Or if I hover right there, there is my grid line and I click it and that selects the grid lines. So really kind of, slowing down, paying attention to what am I clicking on, what am I hovering over? Giving those tool tips along the way will really kind of let's you know what you are selecting because that's when you come up in this Format tab. This is where you can start changing the format of that select element. So if I came into the Fill, notice that I'm changing the plot area. If I had been selected here, then I would be filling in the chart area. So many different places and using this Format tab will be able to format those particular elements that you have selected. And then the WordArt styles over here will be how the text looks. Now I have everything selected, if I throw this on like, okay, that's a little hard to read. But you can see that it is applying to everything as opposed to if I had single selected, the chart title and applied it there. So just knowing where you're at, what you've clicked on, what you wanna do. Slow down think about it. And then having information that's beautifully formatted such as this where everything is in the right columns and in the right places is pretty easy in that sense. But we'll go through some different examples of, maybe it's not as straightforward as this. And try and give you different examples of how you can get your charts to be able to show what you want. But again, just trying to get a foundation right now, just a quick introduction of that terminology, where to click, the different tabs up at the top. And then what we're going to do is jump in, in how to actually insert this information. How we can change because this is tied together. So if I change this to 15, well, it's gonna change in the chart as well because it's saying that I'm charting this particular range of cells. So making sure you have the right data in there and changing is going to be very important. All right, Vonne, that was a very nice primer on working with charts inside of Excel. Starting to get our feet wet in that arena. it's gonna be a lot of fun as we move forward cuz as you can see it was really cool, it gives us great graphics. And if you are doing any type of presentation, this can help you out a lot to make something very nice that everybody can follow along with very easily. Vonne, I think you did a great job on this episode, we do appreciate your time and effort that you put into it. I do appreciate our audience out there for watching, but guess what? We have run out of time for this episode. Signing off for OfficeProTV, I've been your host Daniel Lowrie. And I'm Vonne Smith. And we'll see you next time. [MUSIC]