Using PivotTables for basic analysis - Microsoft Excel Tutorial

- [Instructor] By having the data set up in this flat file, meaning that we have raw rows and columns of data, there aren't any subtotals in this data, no empty columns or rows. Let's scroll down and look at the data really fast. Yes, we do have empty cells, but we don't have complete empty rows or columns. So let's scroll back up. Let's first think about what we might ask about this data. What would we want to know? And that helps us to understand how pivot tables can be so helpful. We might want to look at the donations by month or by quarter. We might want to look at 'em by state or payment method. But then we can look at 'em two ways, by quarter, by state. Maybe we'd like to know the donations that came from Ohio by Q1, Q2, Q3, Q4. Maybe we want a count of donations by quarter. That'll help us understand, say, if we see, say, $1,000 in one month, was that two donations or was that 25 donations? We can see all of that through a pivot table, with the data set up this way. So let's start looking at this data. My cursor is in the data set, I'm going to go to Insert, Pivot Table. Yes, New Worksheet. And this pivot table wizard is guessing the range of our data, so I'm going to scroll down and look at it. Okay, it grabbed the whole data set, click OK. Let's look at the data by state, by quarter. First of all, I'm going to put the donations into values. Now, the states into the row headers, and then the dates, let's grab the dates into the column headers. Now this is a big mess, it's got all 12 months of the year 2020 going all the way across. And this might be helpful, if we did want to look at the data this granular. All right, so what is this value? This 75 is November, for donations coming from West Virginia. The pivot table is also telling us that we have some donations from entities where the state field is blank. Okay, but let's look at this by quarter. I'm going to click in the header, select January, and I'm going to go to Group Selection. And I don't want Months, I want Quarters. Okay, now look at that. And I have not written any formulas. Now we can see, for Arizona we got $25 in quarter one and another $25 in quarter two, for a total of $50 for the year. And, with the cursor in the grand total column, I'm going to go to Data and then Sort Descending. There's so much information here available to us. We've got no donations from Illinois in Q4. Next, I'm going to remove the quarters from the columns. Just drag it out of the way, there we go. Now we have the sum of donations by state. Watch this. I'm going to grab state again, bring that down. Now we automatically have a count, 31 donations from Ohio that total $4,410. We can, oh, look at New York, just two donations accounting for $1,020. Scroll down, 134 total donations for a total of $18,965. Scroll back up. Now watch this. I'm going to go back to sheet one, and I'm going to insert a column. Now, full name equals text join, double click it. What's the delimiter going to be? It's going to be a space. And we have to put that in double quotes. Comma ignore empty, I'm not worried about that. Comma text, Janelle comma Acosta, enter. Double click and send this down, and make sure that it did go all the way down. Good. Now, I'm going to go back to the pivot table and then right click, refresh. Notice, over in our field list, we have a full name option now. Now, I'm going to drag the full name underneath the state. Look at what we see now. Alec Beecher has donated a total of $165 over three separate donations. Scroll down and look at this. We have sub totals, so Pennsylvania has accounted for nine donations total, for a total of $1,690. And Ruben Beecher has donated four times. And notice, when I click away from the pivot table, the field list went away, so I'm going to click back here and bring it back. Okay, scroll down. We have donations from Puerto Rico, 10 total. Now, here are those two donations from New York, the two people who donated. And Elyse has donated $1,000, and Jaclyn $20. One other thing, our organization is in Ohio and we would only like to look at the donations from Ohio and the surrounding states. We could go here and then select Indiana, Kentucky, Ohio, Michigan, Pennsylvania, and West Virginia, okay. So now let's look, we only have those states. We no longer have Illinois, Washington, Washington D.C., et. cetera. There's so much to pivot tables. And I encourage you to look into the learning library to get more information about pivot tables. There is so much here. Let's look at this value here, sum of donations. Click that arrow. Value field settings, so open this up. Here we have sum, count, average, minimum, standard deviation, variation. We can look at show values as, you can show 'em as a percentage of a grand total, percentage of a row, percent running total. I'm going to cancel this, but I wanted to show you that, because pivot tables are so vast. There's a lot you can do, just in the interface and not have to write a formula. Show you a couple of other things. I can bring state into filters, okay? Now it took the state out of the pivot table. But what we can do is go here and look at what is selected. Okay, they are no longer inside the pivot table, but those are our filters. And we can unselect everything. And then just look at Connecticut, okay. One whole donation from Connecticut in all of 2020. Can go back, select all, okay. We can go to insert, slicer, and we want say the state and the method. Okay, now we have these two slicers where we can look at the data. Okay, let's look at checks. Okay, these are the payments that have been done via check. Okay slide down, and we look at a total of 63 checks. Scroll back up, clear. Okay, but go back, so 134. All right. I'm going to pull state out of the filters. Let's say that we don't want to look at count anymore. Just drag it away, okay. Now we're left with the sum. Let's put the payment method in columns. Let's kind of drag this out of the way, so we can see, okay's look at cash. And, if we want to look at if a payment was made via a card, period let's click credit card, and then click this multi-select option and then click debit card. So now everything that is paid by a card is here. And, by name. But if we don't want the names, drag those out and then bring the states back and bring the slicers back over so we can see 'em. Okay, so I'm going to clear the filter, bring all of the data back. Please, I cannot say it enough, get to know your pivot tables. Open this data up. Think about it, think about some questions that you might have about this data. Move the data around in the pivot table. But there's so much insight here. And that's how you can do quick analysis with a pivot table. No formulas, just a lot of thought and imagination.