Excel PivotTables: Advanced PivotTable Techniques for Analyzing and Presenting Data Faster

1. Introduction: When I first started my career as a financial analyst and I learned how to use pivot tables, it gave me a really powerful way to explore and analyze my data to find trends I could then report back to my team and executive management for different meetings, reports, and dashboards. I'm Al Chen, a Solutions Architect at Coda. Today's class is going to be about using pivot tables with advanced techniques for refreshing, for sorting and filtering, and for adding calculated fields to pivot tables. I continue to use pivot tables after 10 plus years of being a power Excel user because I'm constantly faced with really big datasets. I need a quick way to summarize the data into reports and dashboards. That's what pivot tables, a really classic feature in Excel, allows me to do. We'll learn some advanced pivot table techniques in this class, such as adding calculated fields to your pivot tables to make quick calculations on the fly. We'll also learn how you can add formulas to your Excel files using the GETPIVOTDATA formula to pull data out of your pivot tables. We'll also discuss how you can extend your pivot tables using pivot charts, using slicers, and make your pivot tables more interactive. Also, how you can refresh your pivot tables using macros and buttons in Excel. This class is all about how you can add these advanced techniques to your pivot tables to make your pivot tables more interactive, but also to help you as a data analyst to explore the trends and insights about your data. After you take this class, I hope you'll participate in the class project, which involves looking at a big dataset of employee attrition data. Your goal is to take this data, summarize it in these really easy to use dashboards for your end audience, and to make the data much more interactive and insightful for your end audience. I hope you'll add your project to the class project gallery. I'm looking forward to seeing your questions, comments, and more importantly, the insights you gather from doing this project. 2. Creating and Setting Up PivotTables: We'll be creating some PivotTables. This may be a review for some of you, but after we create them, I'll show you some more advanced techniques for setting up your PivotTable and for visualizing your data in your Excel file. We have a pretty much blank Excel file here. In step 1, we want to select all the data in the raw data worksheet, which is the last worksheet in our file, and we're going to create a PivotTable and insert it right here below. I'm going to click over to "raw_data". This is a bunch of data related to telecom information, so customers that have subscribed to a telecom service with Internet and phone. After I select this data, I can click on "Insert", "PivotTable", and I'm going to click on "Existing worksheet" and click on around "A14" back in this worksheet, and then hit "Okay". Now we have our blank PivotTable. Let's add multiple dimensions to this PivotTable. Let's first start by adding total charges, which is the total amount that customers are paying for their phone and Internet service, so total charges goes into values. You notice by default it changes to count, so I'm going to right-click this, go to Field Settings and turn this into a sum. I'm going to add payment method to the column, so this is how the customer paid for their Internet or phone service, so drag the payment into columns, then finally, putting the gender into the rows. Now we have a nice PivotTable of these different dimensions. One last thing I'd like to do is turn these numbers into a currency, because right now it's just an absolute regular number. I'm going to go to Field Settings again, and then click on "Number", change this to a currency. Now, that's in a format that I like to see because now know know this is actual $s. Let me just add one more column or dimension to the rows just to make it a little more extensive, so there we go. Now for step 3, we're going to add the contract column to the report filter. Let's look up here, this is the type of contract that the customer has with their telephone service. If I click on this report filter, I can filter my PivotTable just to a certain contract type depending on what data I want to see. That's a really nice, useful feature to add to your PivotTables. Now, if you look at step 4, we're going to double-click into a cell on a PivotTable to find the underlying data behind that specific data point. For instance, if I want to look at credit card payments that are done by our customers who are female and do not have any dependence, I can double-click on this "$654,000" value. If I double-click on that value, you'll notice that the result is all the underlying rows from my raw dataset that make up that value, so you can drill down into that specific value. This really helps with doing exploratory data analysis, so that you can find the true drivers for your data. I'm going to delete this worksheet for now. Finally, one feature I like to do sometimes is instead of creating multiple PivotTables where I want to see a filter for just month to month and then another one for one year. I sometimes separate these out into three separate worksheets, if I have three different filters right here. What I can do is click on the "PivotTable", click on "PivotTable Analyze", then click on this "Options" drop-down, and click on "Show Report Filter Pages". If I click on "Show Report Filter Pages", it's going to give me this option to only create pages off of contracts, which is the report filter I've added here. I click on "Contract", hit "Okay". You'll see what comes up are three individual worksheets where each of these three PivotTables is filtered down to the contract. This one is two-year, this one is one year, and this one is month to month. More importantly, it also renames the worksheets, so I don't have to go in and rename the worksheets on my own. This is a way to be more productive with creating multiple PivotTables based on a filter you set in your report filter. A few tips about looking at PivotTables. I like to look at my PivotTables in the classic PivotTable layout. The way you can do that is going to Options here. In the Display tab, you can click on "Classic PivotTable layout", hit "Okay". The reason why I do that is because, then I can see a column header for each of the properties in my rows and in my columns, so it just makes it easier to know what kind of data I'm looking at. Look at this, compare to this, which in my opinion it's not as easy to see what my data looks like. One last tip to call out is the metric you drag into your values box here, is probably the most important part of your PivotTable to get right. This is usually the currency, the count of customers, some specific metric that you want calculated inside your PivotTable. In this case, for us, it's going to be total charges, which is total amount our customers are paying for telephone and Internet service. In this lesson, we created a PivotTable and added a bunch of fields to the PivotTable to look at our data, specifically, in this case, telecom data for our customers. In the next lesson, we'll get into filtering, sorting, and slicing our data, so we can better look at trends within our dataset. 3. Filtering, Sorting, and Slicing: In this lesson, we'll be talking about how you can filter and sort your data within a PivotTable by different dimensions. We'll also talk about how you can add slicers to your PivotTable to filter your table in a very interactive way so it's easier to use for your teammates and for your colleagues. We have our PivotTable here and it's set up with a few dimensions already in the columns and rows. Let's start with Step 1 and add tenure to the rows as an additional report filter to the PivotTable. I'm first going to click on Tenure, drag this to rows. You can see this gets added into the rows here. Let's add an additional report to our filter. Maybe we can drag in PhoneService. Now we have two of these filters, which we have in our PivotTable. This is a pretty long-looking PivotTable now. Now I'm going to double-check that the total charge for a given payment method is matching with what we see in this PivotTable, just double-check. Let's take a look at paying method bank transfer here with pay by females with no partners. If I double-click there and I scroll over to the very right of my data set, I should see my total charges column. If I sum up these numbers really quick, it is $1,245. I can also add a sum number here, just to make sure 1245. That matches up with what we see in this cell right here, which is good for us because we know that our PivotTable is done correctly. But this is just to further emphasize the point that you can always double-check the numbers in the PivotTable to see what the underlying data is for a given number in the PivotTable. Let's delete that worksheet for now. I'm going to remove partner now from the PivotTable and add in from Step 4, add in Internet Service. Let's drag in Internet Service. I'll put this right in between Gender and Tenure. Let's actually skip back to this step here, Step 3. We want to sort by total charges descending and filter for the only top 10 values. You can actually sort by two different things, by the values along the columns and values along the rows. I'm just going to sort by total charges for now. Click on here, the Tenure, and then sort by sum of total charges, and click on "descending". You'll see that now the Tenure is not sorted, but rather the total charges along the right-hand side from my grand total is now sorted. Now, maybe I only want to see the top 10 charges for females with internet service, tenure, so on and so forth. Another way I can quickly filter my PivotTable is by clicking on this "filter by value", top 10. Let's say I want to see the top 10 items here. Now my PivotTable is further sorted and filtered to only show the top ten total charges for each of these categories I've set up in my PivotTable. You can play around with these different filters and sorts to only show the data that matters for you and for your audience. I'm going to undo some of those filters for now. Let's go to Step 5. We're going to add a slicer now on the Contract column, and we're going to allow multiple selections on the slicer. If I click on the PivotTable, PivotTable Analyze, Insert Slicer. It's going to ask me which column I want to add a slicer to. I'm going to click on Contract. It's right down here. Hit "Okay". You'll get this little drop-down right here which you can format to your liking. What's really interesting now is if I click on, let's say, let me just narrow these columns a little bit. If I click on "Month-to-month", you'll notice that not only does this filter change, but it also filters my entire PivotTable as well. If you click on one year, all the data will change. If I want to click on multiple values at a time, I just have to click on these checkboxes, and then I can click on and select multiple ways to filter my data set using this slicer. The slicer is a much more interactive way to filter data versus having to click into these drop-down and selecting these checkboxes. This is just a better way for your colleagues to filter the data that they want to see in a dashboard or report. Now, I'm going tenure from this PivotTable, and now we have a simple slicer that allows me to slice and dice this data set. If I copy this PivotTable and then put over here and just do a paste, you notice that if I click on this slicer, it still filters this PivotTable down here. I want to maybe disconnect this PivotTable from my slicer. How can I do that? When you right-click on the slicer and go to report connections, you'll see that there are two PivotTables, these two that are connected to this slicer. If I just uncheck this PivotTable, which is PivotTable 8, this slicer now won't impact. Well, just like PivotTable 8 was this one, it won't impact that slicer. You can always see the name of a PivotTable by clicking on the PivotTable, clicking on PivotTable Analyze, and then you'll see the name up here in the top-left. Finally, what if you want to lock your Excel file but allow your colleagues to be able to use the slicers? This is a really common way if you don't want your teammates to get involved and mess up your data or mess up the structure of your Excel file. What you can do is click on the PivotTable, right-click on it and go to slicer settings, sorry, let's right-click and then go to, let's bring up this menu here. Size and Properties, I believe it is. Click on properties and uncheck locked. Just some other things here. You can also reposition your PivotTable here and add additional columns if you want to so that it looks more easy to use. Sometimes you have many different properties in your slicer, which you may want to organize a little better in your slicer. You can unchecked locked here. Then in your review tab, click on Review, Protect Sheet, you want to allow users to select locked cells, we'll uncheck that, but also use PivotTable chart. You uncheck select locked cells, check off use PivotTable reports, and then hit "Okay". Now, this worksheet is locked, I can't get any of the cells. But I can still use the slicer, which is important for your team and your colleagues to be able two still interact with your PivotTable to see the data they want to see. We did some quick filtering and sorting in our PivotTable, but we also added a slicer to make our PivotTable more interactive for our colleagues. We also talked about how you can lock your Excel files so that only the PivotTable slicers available to use to look at and analyze the data. In the next lesson, we'll talk about how you can add calculated fields to your PivotTable to create new metrics that is not available in your current data set. 4. Adding Calculated Fields: In this lesson, we'll be talking all about calculated fields, which are formulas that are constantly running in your PivotTables as you're moving dimensions around in your PivotTable. We have a PivotTable here based on our customer telecom data. We have payment method, gender on the rows, phone service in the columns, contract in the filter and total charges in the values. What if we want to know what our average monthly charge is for all of our customers, no matter how we structure our PivotTable. This is where we would do a calculated field. I can click on PivotTable Analyze in the ribbon. Click on fields, items and sets, click on calculated field. The way we can calculate the monthly charge is taking total charges divided by tenure. Usually when you have a calculated field, you might want to add something before the name of the fields, something like calc, or even something like a star. I like to use Calc and we'll call this monthly charge. The formula we want to tell Excel to basically take the total charges, which is the total running charges our customers are paying us, and dividing it by the tenure. Once we have this formula in our CalcMonthlyCharge field, I can hit okay. There you can see Excel automatically adds this monthly charge column to my PivotTable and I can quickly see what the average monthly charge is for my dataset. Now a lot of people wonder why we had to do this calculated field, why we couldn't do this in our dataset. We could go to our very last tab, which is our raw data and you could in theory add a column here that says average monthly charge and you could take equals total charges divided by the tenure, which is right here and then drag this from the downside. Now you have the average monthly charge at the row level for every single one of our customers. If I go back to my PivotTable, I can change the data source to include that extra column of data I added. Before it was at column t, I'm just going to change this to letter u, hit okay. You'll see there is now a average monthly charge column, which I calculated manually on the backend. But you notice that when I add to my PivotTable, it doesn't match up with what is in my CalcMonthlyCharge calculated field. That's because no matter how you change this setting, you can change this to an average, it doesn't really match up with what this value actually is, and this is an important concept to grasp because the calculated field applies to the calculation. When you add the average monthly charge, which is that column we added here in our dataset at the very end, it doesn't match up with what we see in our calculated field because the calculated field calculates at the row level, whereas when you take this, you're just taking an average of the calculation. This is the more accurate way to look at the average monthly charge for our dataset. I'm going to drag this out for now and just re-collapse these coms a little bit. It's important when you do calculated fields to do them off of the raw metrics instead of off of columns that contain formulas within your raw dataset. That is one of the key takeaways for step two. Step three, we're going to create a calculated item, which is different from a calculated field by combining these three different categories, bank transfer, electronic check, and mailed check. We're going to combine these into one group and we're just going to call them all checks. Actually we're just going to combine mailed check and electronic check into one group called all checks because we want to consolidate our report a little bit. I can click on PivotTable Analyze. You have to make sure you're clicking inside one of your fields. Go to field, items and sets, calculated item, and we're going to call this, the name all check. The formula is simply going to be electronic check, insert item, plus mail check, insert item, hit okay. You'll see that I have this additional all check calculated item which is a summary of my mail check and electronic check properties. In this case, I would probably want to uncheck electronic and mailed because my all check group already contains those two values. Let's go into deeper into how we can make some more advance calculated fields. Let's create a calculated field called CalcServiceFee for any customers who have an average monthly charge of a few dollars or more, we're going to charge them two percent for just managing high bills, let's call that. I'm going to go back into PivotTable, analyze, fields and sets, calculated field, and we're going to call this one CalcServiceFee. What's interesting here is you can actually add if statements to your calculated fields. I can write if, left parentheses, the monthly charge, which is, if you recall, this is another calculated field we calculated very beginning. If the calculated monthly charge is greater than 50, then we're going to take the CalcMonthlyCharge, and multiply it by two percent. If it's not greater than 50, we're just going to return back a zero and then close out our if statement. An important thing to note here is you can use if statements and you can create calculated fields off of other calculated fields in your PivotTable. Is it okay? You can see here, it's a really small amount, but let me just add some more decimal places to this number. Let's add two decimal places. You can see here that these numbers are basically two percent of the average monthly charge. But the important thing is that you can add these if statements and calculated fields on top of other calculated fields. I'm going to drag the calc service fee and the total charges out for now. Let's do another more complicated one which is create a calculated field called calc transaction fee only for customers who pay with credit card and this is going to be equivalent to 2.9 percent multiplied by the monthly charge plus $0.30, which is a pretty common charge you add for customers who pay with a credit card. I'm going to click on PivotTable Analyze, fields, items, sets, calculated field. We're going to call this CalcTransactionFee and this is if the payment method, let's look for that over here, insert. If this payment method equals credit card automatic. Let's make sure we get the cases right. Then we want to take the CalcMonthlyCharge or other Calculated Field, multiply that by 2.9 percent and also add on that $0.30 calculation for credit card charges, zero to close out the false case of our if statement. Let's see what happens if I hit okay. You're going to see it gives you a bunch of zeros. The reason for this is that calculated fields, one copy about these more advanced calculated fields is that you can't use a column header or a field header, in this case, PaymentMethod, inside your if statement in a calculated field. The workaround for this scenario is to go back to your raw data, and we're going to add a helper column called credit card? Let's just delete this related for now. We're basically going to add a one or a zero to this column if the PaymentMethod is a credit card. We'll write equals if cell R2 equals credit card automatic. Give this a one, otherwise give me a zero. Let's drag this formula down. Now this is simply telling us whether or not the customer paid with a credit card and that's denoted by a one or a zero. Now if I go back to my PivotTable where I have my transaction fee, let me just make sure that this is referencing the right dataset. We have to make sure it goes all the way to column u, which is right, hit okay. I'm just going to refresh this one time just to make sure I have the most updated data. Now I'm going to go back to my calculated field, click on the drop-down here, calc transaction fee, and instead of PaymentMethod equals credit card, I'm going to say delete this part of the if conditional statement and say credit card?, that was the new column we added. If that is greater than zero, then we'll do the calc monthly charge, which is our calculated field for calculating multi charges, multiplied by 0.029 or 2.9 percent and add $0.30, hit okay. Now you can see that the transaction fee only changed ever slightly for our credit card transactions. I'll just add some more decimal places to this number so you can see more detail what this looks like. You can see that we only have transaction fees for the credit card payment because that was what we showed in our last dataset. For bank transfer and all check, it doesn't match the if condition, so therefore, we only see transaction fees for this calculated field. We talked about how you can add a calculated fields to your PivotTables and also do some more advanced things to your PivotTable formulas by adding if statements with other calculated fields in them. In the next lesson, we're going to talk about the best ways to visualize data from a PivotTable, which is using pivot charts. 5. Creating PivotCharts: In this lesson, we'll be talking about how you can create PivotCharts, which are just as flexible as PivotTables to visualize your data. We'll also discuss how you can save templates that match your company's brand guidelines. Looking at our customer telecom data, we want to create a basic PivotChart off of this PivotTable. I can click on "PivotTable Analyze", and then "PivotcChart" right here. This is a pretty basic PivotChart off of your PivotTable. It doesn't look super fancy. Let's just add some basic formatting to this. Maybe we can change this bar color to a different color. Let's just change this to a different fill color right here. Let's change this one to maybe like a nice green, and maybe we can make the background of this a little more interesting to see. I can go to Format Axis. Actually, we can just leave it like this. Let's leave it just like this for now. We also want to be able to filter this table based on this contract column here that's in our report filter. Let's add a slicer to our PivotTable like we did in a previous lesson. PivotChart and Pivot Analyze, Insert Slicer. We're going to be using the contract column again. Hit "Okay". Let's zoom in just a little bit here. You'll notice that when I click on one of these options, not only does it change the PivotTable, but it also changes the PivotChart. This is a way you can connect all of your data, your PivotTable, and your charts together into one place so that everything is super dynamic. The important thing here to know is that you can edit and add images to your PivotCharts. But more importantly, you can turn this into a template. If I right-click this chart and go to Save as Template, your templates get added to this chart templates folder in your desktop. I'm going to call this MyChartFormat. Hit "Save". Now we can reuse that chart for use as a template for a future chart we create. Let's create a new PivotChart. Go to PivotTable Analyze, PivotChart. We have this same boring chart we get by default. But now let's right-click on the chart, go to "Change Chart Type", and then go to "Templates" You can see "MyChartFormat" that I saved in the previous step shows up here. When I click on that, it automatically changes the format of that chart to match whatever template I had set up here here. You can spend a lot of time getting the brand guidelines and brand colors, add company logos look perfectly on this chart, and then reuse that template on future charts like this one right here. One last thing I'd like to do with my charts is make the title of the chart very dynamic. Let's click on this chart's "Design", "Add Chart Element", "Chart Title", "Above Chart". Now, by default, it says chart title, and normally, you probably would just manually type something in this title. But if you go to the formula address bar, write equals, and then click on "Cell A1", in this case, hit "Enter", you'll notice that the PivotTable chart will change depending on whatever is in this cell right here. I can say my cool chart and this will automatically change. It's a much more dynamic way to edit the titles of your PivotCharts or ordinary charts as you're building them out for reports and dashboards. You don't have to double-click into this and change the chart title every single time. We just walked through how you can add PivotCharts to your PivotTables to make your data really visually pleasing for your colleagues and for your teams. We'll now get into using the GETPIVOTDATA formula in Excel to pull data out of your PivotTables to be used for reports and dashboards. 6. GetPivotData for Custom Reports: You're going to learn how to use the GETPIVOTDATA formula in Excel to get data out of your pivot table. This is really useful as your pivot table is changing and you want to apply some custom formatting to the values you take out of your pivot table. We have this pivot table here with payment method, StreamingMovies, TotalCharges, Average tenure and just to double-check here, you can see here that the average tenure is not a sum because we want to find the average of length of all of our customers for this telecom company for these given metrics. Let's go to Step 1. Let's just reference a cell of pivot table into cell G15. If I zoom in here a little bit and I write equals a cell, let's say, I go to 1,145, you notice what this GETPIVOTDATA function gives me. The first parameter for this function is the sum of the metric in question. In this case, it's total charges, how much were being paid total from our customer. A12 is simply the location of our pivot table. Then the following parameters are where you used to in a SUMIFS formula where you have different criteria and the actual criteria range. StreamingMovies is no, PaymentMethod is bank transfer automatic. That's why this 1,145 corresponds to this number. Sum of total charges where it's a bank transfer and the StreamingMovies is no. In cells G18 and G19, I want you to find the sum of total charges and average tenure, but I want you to formulaically get these numbers from the pivot table. Now, we were going to be looking at also customers who pay with a credit card and don't have phone service. Let's just write this from scratch. Let's write equals GETPIVOTDATA. We want to get the dynamic reference of sum of total charges, which is this cell right here. I've written this out for you already. The pivot table is yes, in cell A12 and we can make this an absolute reference and we got that from looking at our previous GETPIVOTDATA function. We want to see the payment method for those who pay with credit card. It's, I believe, Credit card automatic. Then also for people that had no streaming movies. Let's write StreamingMovies is the column and the answer is no. You'll get an error. The reason why is because when you reference the F17, there's multiple charges at the metric that we want to sum, you have to actually add an ampersand and two double quotes after it to make sure that the GETPIVOTDATA function knows how to reference the correct values. If I drag this down, you'll also see that this average of tenure is 36, which matches up with those who pay with a credit card, and had a no streaming movies, and had an average tenure of 36. One last thing about the GETPIVOTDATA function in terms of general metric options is make sure that you have the GETPIVOTDATA function turned on in the first place, which you can find in the options just make sure Generate GETPIVOTDATA function is turned on. Now, let's actually build out a dashboard starting in H34 using cell references to the payment type, whether the customer streams movies and the tenure. Let's start right here. We basically want to fill this table out in our Excel file. The reason why we would use this table instead of using just the pivot table directly is because we can better format this to our needs versus formatting in a pivot table, which might be a little tricky. Let's start writing the GETPIVOTDATA function in this cell right here. Let's just reuse this function right here because we already got the GETPIVOTDATA function set up for us. I'm going to copy that, insert right here. Sum of total charges, we want to get that for StreamingMovies. Instead of hard-coding this note here, we can actually reference this column right here, which I've hard-coded with yes and no as a helper column. I can lock in the column reference. Then for PaymentMethod, I'm just going to select this cell right here, which contains the PaymentMethod and lock the column but you also notice that I also need to pull in the tenure. That's broken out by these numbers. Let's actually drag in the tenure column into this pivot table. Tenure, we'll put that into rows. Let's remove average tenure for now, we don't need that. Now, we have the tenure broken out by PaymentMethod and StreamingMovies for total charges. Let's go back to our pivot table function. StreamingMovies is yes. This is for some reason got put into the quotes. Let's make this to G34 as a reference and we're going to lock this column. Then finally we're just going to write "tenure", that parameter is going to be this number, which is just the number of months of tenure lock in the row. If I hit "Enter", I can now drag this from all the way across to the right and also drag it down. I can see the total charges for these payment methods and for these streaming types. If I drag copy this formula down here, it's also going to work out correctly because I'm referencing the PaymentMethod and in this case the StreamingMovies is going to be no. I can drag this down and also to the right. Very easy. This is how you can create a very custom-made dashboard with all the color formatting you need by pulling data out of your pivot table even as your pivot table is changing structure. The important thing to note here is that whatever fields you pull in or reference in to GETPIVOTDATA function, you have to have those fields in the pivot table itself, which is why I had to put tenure into the rows. The final step, let's dynamically reference pivot tables on other worksheets using the INDIRECT function. You'll see how you have to use single quotes to use the INDIRECT function. In previous lessons, remember how we broke out these filters, these reports into Month-to-month, one year, and two years? These are just three different pivot tables but filtered by contract. Let's go back to this table and we want to add references to those values for total charges by gender for these specific report filters. If I write equals, I want to get male Month-to-month. Let's go to the Month-to-month tab. The total for male is going to be right here. If I hit "Enter", let's take a look at the formula and see how we can make this more dynamic. We can change this value, instead of being hard-coded, change this to male and make this column header and lock in the row. How do we change this Month-to-month so that we can dynamically reference these cells right here? Well, this is where you would use the INDIRECT function. I'm going to delete the Month-to-month right here for now. I'm going to write INDIRECT, left parentheses and I'm going to write double quote, single quote, double quote, ampersand, then reference Month-to-month, this cell right here and I'm going to lock in the column reference, put another double quote, double quote, single quote, double quote, hit another ampersand, and then put this exclamation park A3 within double-quotes as well. Hit our parentheses. This INDIRECT now will properly reference these cells as the work that I want to reference. Now, you can see this gets me the same value but if I drag this down, this 1,958 now references the one-year worksheet for male, which is 1,958 and I can basically drag this over to female as well, properly fill out using the GETPIVOTDATA function but also this little workaround for referencing other worksheets within my file using the INDIRECT function. We talked about different ways you can use the GETPIVOTDATA function to pull data out of the pivot table into more custom formatted reports that you can present to your team and to your colleagues. In the next lesson, we're going to talk about ways you can refresh your pivot table using macros. 7. Refreshing PivotTables with Macros: In this lesson, we're going to use macros to automatically update different layouts and views of your PivotTables. We have a PivotTable driven off of our main dataset. But one thing I want to do differently with this PivotTable, is go to raw_data and turn this, our raw dataset, into a Excel table called TelecomData. Just select everything from cell A1 to the very end. We have about 6,000 rows of data. Go to "Insert", "Table" hit "OK" with the headers and we're just going to call this TelecomData. Now when this table changes, when we add new rows or new columns, this will automatically update our source. It's really important when we now change the source of this PivotTable to reference that new table. If I go to "Change Source" instead of location, you can just say TelecomData. If I refresh this, everything shall work out as expected. No data changed, so we won't see any changes right now. I want you now to just quickly add a new role of data to this table, our raw data. This is click, go down here, hit "Enter". Let's just give us some random values. You noticed how we get new data added. But let's go to the total charges. Let's just add some really large number, like one million. If I go back to my PivotTable, you can either right-click "Refresh" or just click on "PivotTable Analyze" and hit "Refresh". You'll see this new blank Total added to the bottom because our TelecomData table changed and therefore our raw data changed and therefore our PivotTable changed. Let's just delete this row for now, because we don't want to mess up our dataset. Delete row from our table. Let's go back to this set. Now, we're going to turn on the Developer tab in our Excel file. This is going to be different for PC users and Mac users. But on the Mac, you can click on "Excel", "Preferences", "Ribbon" and "Toolbar" and then at the very bottom, you should see on the right-hand side, "Developer", hit "Save". Now you're going to see the Developer tab added to your Ribbon. I want you to click on "Developer" and then click on "Macro". I'm sorry, I want you to rather click on "Record Macro" and call this Macro "PaymentOnly". What I want you to do now is hit "OK" and then the Macro is recording now. What a Macro is essentially we're recording a bunch of steps to tell our Excel file to do this every single time we run the Macro. Right now it's recording. I want you to go click on the "PivotTable", "PivotTable Analyze". We're going to say "Clear All". Then also click on "Refresh". This is going to refresh our data. Now we're going to set up our PivotTable to show the layout we want. Let's take "Total Charges", add that to our "Values". Let's just make sure this is a sum of our values. Also, just make sure, the Field Settings we have the number format that we want. Hit "OK". Now we're going to click on "Payment Method". Put this into the rows. We just increase the font size a little bit so we can see this data a little bit better. I can also zoom in here. Now we just have the PivotTables set up the payment method. Now I can go back to stop recording. You won't see anything happen from here, but let's repeat steps four and five with two more macros for internet gender or the interne. T services in rows. Gender and columns and another one Macro called ContractTenure where tenure's in the rows and contract is in the columns. Let's follow this again. Record Macro. We're going to record one now, called Internet Gender. Hit "OK". The steps are, go to "Clear All", "Refresh" and now we're going to add Gender into the columns and then Internet Service. Let's where is Internet Service into the rows. Then finally Total Charges into the Values. Then we just make sure that this is also a sum. Then hit "Stop" on the recording. Then do this one last time for the Contract Tenure. I want to show you something at the very end. Record Macro. We're calling this one Contract Tenure. Hit "OK". Click on the "PivotTable", "Analyze" and we're going to click on "Clear All" again. Probably very used to this now, "Refresh". We're going to put Total Charges again in the Values. Let's make this a Sum. Then we want to have Tenure in the rows, Contracts in the columns. Let's hit "Stop" on the Macro. Okay. We now have three different macros and we're going to assign each of those macros to a button in our Excel file. Now you can either use a button like this, one of these standard buttons from the Develop tab. We can call this like this Button1_click. Assign this to Contract Tenure. We have our three macros right here when I clicked on "Button", I can assign this one to Contract Tenure. I can right-click this and rename this to Contract Tenure. But I like to actually use the regular kind of drawing options within Excel. I just think that you can edit the buttons a little better and add more colors, so it looks a little more fun to use. Let's go to "Insert", "Draw a Shape" and just make a basic shape like this. We can call this one Payment Only. What you can do here is, you can right-click on this button and Assign a Macro. We're going to assign this one to the Payment Only button. Then let's just create another shape for our last Macro. Let's assign this a Macro of Internet Gender. Just write in Internet Gender. Now if I click on any of these buttons, my PivotTable will automatically refresh to show that specific type of layout that I want. The reason why you might do this is because sometimes you want to have a quick way to change the layout really fast without your team and colleagues having to drag and drop columns in to the table. The alternative is you can have PivotTables built out in to individual worksheets like this, but this can be kind of hard to cycle through. Depending on your use cases where you think about how you want to improve the user experience and user interface. The important thing is that you can't undo the results of our macros, so just make sure you get it right the first time when you are recording your macros. Finally, if you want to see the code for your macros, you can go to "Developer", "Visual Basic" and then click on "Modules" here and you'll see the code for all the macros you've wrote in this file. You can see here this is Payment Only. This is Internet Gender, and this is Contract Tenure. This is beyond the scope of this class, but you can also get into actually editing the code of our Visual Basic code for your macros. We showed you how you can use macros to automate the refreshing of your PivotTables and how you can also edit the code for your macros and assign them to buttons to make it easier for your colleagues and teams to refresh your PivotTables. 8. Final Thoughts: Congratulations for taking this class on advanced pivot table techniques in Excel. Some of the key takeaways from this class, we learned about how you can use pivot tables and make them much more interactive for your team and for your colleagues. We also talked about how you can add calculated fields to make running calculations in your pivot tables, all the way to macros to automatically refreshing your pivot tables to use. Another thing I want to call out is that the techniques you learned in this advanced pivot tables class can be applied to a lot of other data analysis platforms like Google Sheets and Tableau. I really hope you will take the skills you learn from this class and apply them to the class project, where you'll be building out pivot table reports and charts and ultimately, a dashboard off of employee attrition data, which you can then use to present to someone internally at your company, and I also want you to take insights you can glean from analyzing pivot table data. I look forward to seeing your project in the project gallery for this class. Feel free to post your analysis, ask each other questions, I'm also happy to answer any questions you have. Good luck with the projects.