Excel PivotTables Part 1: PivotTables 101 | Chris Dutton | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Excel PivotTables Part 1: PivotTables 101

teacher avatar Chris Dutton, Founder, Excel Maven

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

44 Lessons (3h 3m)
    • 1. Class Materials and Outline

    • 2. Getting to Know the IMBD Dataset

    • 3. Setting Expectations

    • 4. Why Pivot Tables?

    • 5. Structuring Your Source Data

    • 6. Inserting Your First Pivot Table

    • 7. Navigating the Field List

    • 8. Pivot Table Options: Analyze and Design

    • 9. Selecting, Clearing, Moving & Copying Pivots

    • 10. Refreshing & Updating Pivots

    • 11. PRO TIP: Dealing with Growing Source Data

    • 12. How Pivot Tables Actually Work

    • 13. Number Formatting

    • 14. PRO TIP: Automatically Formatting Empty Cells

    • 15. Table Layouts & Styles

    • 16. PRO TIP: Using Tabular Tables to Create New Source Data

    • 17. Customizing Headers & Labels

    • 18. Conditional Formatting

    • 19. PRO TIP: Data Bars with Invisible Text

    • 20. Sorting Options

    • 21. PRO TIP: Why is my Alphabetical Sorting Wrong?

    • 22. Label Filters & Manual Selections

    • 23. PRO TIP: Using Label Filters with Wildcards

    • 24. Value Filters

    • 25. PRO Tip: Enabling Multiple Filters

    • 26. Grouping Data

    • 27. PRO TIP: Automatic Date Grouping

    • 28. Using Slicers & Timelines to Filter Data

    • 29. Breaking Out Report Filter Pages

    • 30. "Summarize Values By" Options

    • 31. PRO TIP: Avoiding the "Count Of" Trap

    • 32. "Show Value As" Calculations

    • 33. Show Values As: % of Column/Row

    • 34. Show Values As: % of Parent

    • 35. Show Values As: Difference From

    • 36. Show Values As: Running Total

    • 37. Show Values As: Rank

    • 38. Show Values As: Index

    • 39. Inserting Calculated Fields

    • 40. Calculations in Pivots vs. Raw Data

    • 41. PRO TIP: Calculating Using Counts

    • 42. PRO TIP: Calculating Using Counts (part 2)

    • 43. Inserting a Calculated Item (not recommended!)

    • 44. The Solve Order & List Formulas Tools

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

This class is Part 1 of a three-part series covering data analysis with Excel PivotTables and PivotCharts.

PivotTables are an absolutely essential tool for anyone working with data in Microsoft Excel. Pivots allow you to quickly explore and analyze raw data, reveal powerful insights and trends otherwise buried in the noise, and provide fast, accurate and intuitive solutions to even the most complicated questions.

In Part 1 of this course, I'll show you when, why, and how to use PivotTables, introduce advanced sorting, filtering, and calculation tools, and guide you through interactive, hands-on demos and exercises every step of the way.

We'll cover everything you need to know to get up and running with PivotTables, including:

  • Raw data structure
  • Table layouts & styles
  • Design & formatting options
  • Sorting, filtering, & grouping tools
  • Calculated fields, items & values

From there we'll take what we've learned and move on to Part 2, which introduces PivotCharts, slicers, timelines and custom dashboards. Finally we'll wrap up with Part 3, where we'll tie it all together and explore several advanced, real-world case studies!


  • Microsoft Excel (2010, 2013, 2016), ideally for PC
  • Mac users are welcome, but note that the PivotTable interface varies across platforms
  • Basic experience with Excel functionality (charts & formulas a plus!)

Meet Your Teacher

Teacher Profile Image

Chris Dutton

Founder, Excel Maven


Chris Dutton is a Certified Microsoft Expert and Founder of Excel Maven, with more than a decade of experience specializing in data science and business intelligence. His work has been featured by Microsoft, the Society of American Baseball Research (SABR) and the New York Times.

Excel Maven provides high-quality online analytics training, hands-on workshops, and project-based consulting services to more than 100,000 students across 180+ countries.

See full profile

Class Ratings

Expectations Met?
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Class Materials and Outline: everyone. Welcome to Microsoft Excel Data Analysis with Excel Pivot tables. Now, before we dive in, let's take a minute to review the course materials as well as the outline that will follow along the way. And this course includes a ton of supplemental material that helped me become a true pivot table Expert will have access to 50 plus downloadable Power Point slides. These are really helpful resource or reference guide for when you're offline. Er, on the go, I recommend that you print these out, mark them up, take notes and just keep them on hand for when you need to quickly reference something that we've covered in the course. Now, throughout the course, you'll see about 100 interactive hands on demos to keep you engaged from start to finish. These include downloadable workbooks that you can use to explore and learn from home at your own pace. And this course is absolutely packed with quizzes, knowledge checks and homework exercises as part of each chapter. And these are designed to help reinforce key concepts and make sure that you're retaining the information every step of the way. And last but not least, we've got the final section of the course, which dives into a bunch of different interesting case studies to really put your skills to the test by applying the tools that you've learned in a variety of real world context. Now, in terms of the course outline, this is broken up into six primary sections. Section one is all about pivot Table 101 So this is laying some of the groundwork, setting the scene for some of the more advanced tools and techniques that will be covering later in the course. So it's all about how to structure source data, how to navigate the field list. We'll practice creating and manipulating some tables and views, and then from there, will dive into Section two, which is all about formatting. This is the look and feel of your pivot. Tables will cover conditional formatting rules, default and custom styles, table layouts, cetera. Section three is where things start to get a bit more interesting, so this is sorting, filtering and grouping tools. We'll talk about label on value filters, wild cards, grouping options, etcetera and then by Section four will really hit our stride. Calculated fields and values. This is where pivot tables really start to add a tremendous amount of value. So practice creating custom calculated fields and items, summary ization modes and much, much more There. Now, Section five is great. This is where we'll kind of shift gears a little bit and start thinking in terms of data. Visualisation and pivot charts give us the perfect tool to kind of add that visual element visual layer on top of a pivot tables. So in Section five will show you how to build a whole bunch of different types of pivot charts. We'll walk there, slicers and timelines. I'll even show you how to create a pretty powerful, dynamic dashboard from scratch. And that takes us in the final section. My favorite section. Of course, the case studies now for Sections one through five will be working primarily with the I. M. D B movie database, which I'll introduce in the next lecture. Section six is where I'll make all sorts of different data sets available to you that we can use to practice and apply the skills that we've learned throughout the course. So we'll talk about burrito ratings from San Diego restaurants. I've got actual shark attack records dating back to 1900 got San Francisco salary data. Baseball team stats, Spartan race, Facebook data and much, much more. So hope you're excited to dive in. I know I'm excited to take you through this journey, and if you're ready to roll, let's do this. 2. Getting to Know the IMBD Dataset: I want to take a minute or two and talk about the I M D B movie database, since we will be working with this file for the first few chapters of the course. If you're not familiar with I M D B, it's an acronym that stands for the Internet movie database, and it contains all sorts of information about movie titles released around the world. So we'll be looking at a cut of the IMDB data for the first several sections, which gives us movie titles, attributes, budgets and revenues from 1922 2015. This is a great file to use to practice pivot table concepts because it contains a number of interesting dimensions and measures. Now the way to think about dimensions. It's kind of those columns that you can use to categorize your data, to roll it up or break it down in interesting ways. So in this case, are dimensions include the movie title, the release date, whether it was color or black and white. What genre it falls into the language country rating and lead actor and director name the measures which are quantitative fields or the metrics that we actually care about include Facebook likes for the lead actor, the total cast, the director and the movie as a whole, plus the IMDb score, which is on a scale of 1 to 10. The total number of reviews the film duration in minutes. Gross revenue and budget. So a bunch of interesting fields that we can use to analyze and explore here and the concepts covered include pretty much everything. I will talk about formatting and conditional formats. Sorting, filtering and grouping tools will do a nice deep dive into calculated values and fields and then go into pivot charts as well. So let's switch over to excel and see exactly what we're working with. So here's what the file looks like. An Excel. This is the I. M D. B movie database file. It's available in the course. Resource is, As you can see, we've got our dimensions here in blue. Got her measures here in green. Looks like we have about 18 columns coming out through column are and scrolling down. Got 3700 and 26 rows, just not a huge file. It's not a very small one, either. It's a nice kind of size that will give us plenty of flexibility to really showcase how pivot tables can be used to explore and analyze this data set. So go ahead and open this Excel file. Take a peek through yourself. If you have any questions, let me know. Otherwise we're ready to move on. 3. Setting Expectations: The last topic that I want to quickly talk about before we jump in is setting expectations for the course. First, I'll be using Excel 2016 for all of the demos and exercise is what that means is that what you see on your screen may not always exactly match what I'm showing on mine, and some features might be unavailable in earlier versions of Excel. Second, I built this course with PC users in mind. If you are a Mac user, you can absolutely apply all of the same key concepts that we cover here. But keep in mind that your user experience might be considerably different across platforms . Third, it's not always about right or wrong when it comes to Data. Analytics is an art, and there are often a number of ways to attack the same problem. My goal here is to give you tools and techniques that you can use to explore your own analyses in your own ways. Finally, speak your mind for better, for worse. If you need support, the course discussion board is a great tool to get a conversation started or feel free to message me directly, and I'd be happy to help. If you have any thoughts, let me know. Your feedback is a critical part of making this the best course it can possibly be, so please don't hesitate to share your thoughts with that. Let's dive in. 4. Why Pivot Tables?: All right. Welcome to Pivot Table 101 Now the purpose of this section is to really just lay some of the groundwork before we get into the more advanced topics. So we'll practice inserting pivots, manipulating views, exploring some of basic options and then later in the course, will start to dig in deeper. So let's start with a question. Why pivot tables? Why're pivot tables one of the most important and powerful tools and excel when it comes to data analysis? So let's answer this with an example. Let's say you have state level data spanning multiple years and you're looking at total population and student population. If you only had access to this data and it's raw format, how would you answer the following questions? Number one. Which state had the highest population in 2002? All right, that was not so bad. Maybe you could just throw some filters on the head or row, drill down to 2002 and then sort column C to answer the question that way. Definitely a perfectly valid approach. But what about number two, in which year was overall U S population the highest? Now you may need to start adding some intermediate steps, you know, toe aggregate the data at the national level and then start sorting from there so definitely still doable. But now it's becoming a little bit more tedious. Which brings us to Number three, which states saw a decline in student population rate as a function of total population between 2003 and 2004. Now we're starting to get into the type of question that pivot tables are incredibly good at answering. Well, of course, you could answer this question without using a pivot. It would involve multiple, often tedious steps to get there. And that's exactly what pivot tables, air designed to do very quickly and very efficiently. And last but not least, what if you don't even know what you're looking for? So each of those three questions that we just talked about assume that you have some sense of the story or the trend or the insight that you're trying to expose. What if you're approaching a data set in a purely unbiased way, meaning that you don't know what the story is going to be, and you need an analytics tool that's going to help expose those insights for you. That's another key benefit of pivot tables and another reason why there's such an important tool in Excel. So quick. Pivot Table 101 The way I usually describe it is that pivot tables allow you to easily organize, filter summarized and analyze raw data. So very famous Einstein quote. Analyzing data without a pivot is like hammering a nail with the noodle now. Obviously, Einstein didn't actually say that, but it gets the point across. Fact is, if you're analysing data in Excel and you're not using pivot tables, you're making your life 10,000 times harder than it needs to be. Pivot tables are the tools that enable efficient Data analytics and Excel, and they will make your life much, much easier. So think of a pivot table. It's kind of like a layer that sits on top of a raw data set, so you're not actually replicating the data. You're just creating a lens through which you can slice and dice and filter and organize and really explore your data in a very flexible and powerful way. So to wrap things up, I want to talk about the five key benefits to remember about pivot tables. Number one. They're powerful. They allow you to uncover new insights and answer important questions about your data. Second, they can be beautiful. You can apply custom styles, conditional formatting rules and even charts and graphs to help bring those pivots. Toe Life number three They're incredibly fast. You can build customized views, add filters, calculate brand new fields in a matter of seconds. Fourth, there Accurate. This is an important one. By automating calculations through a pivot table, you can minimize human error and avoid mistakes that you would have made trying to approach the same problem manually and last but not least, number five. Pivot tables are unbelievably flexible, so you can manipulate table layouts, create dynamic views and reports that you can update in a just with the click of a button. So there you go. Powerful, beautiful, fast, accurate and flexible. 5. Structuring Your Source Data: all right now, before we get our hands dirty and start working with actual pivots, it's incredibly important to understand that a pivot table is on Leah's strong as the data behind it. There is a right and a wrong way to structure source data, and this lecture is about helping you understand the difference. A good data structure is rectangular with variables laid out his columns with clear header names and observations laid out as rose. If your datas flip flopped or transposed, you really won't be able to analyze it in any sort of logical way using a pivot. Good source data is also free of unnecessary formatting formats like number types, font styles, cell fills. Etcetera won't pass through to a pivot table anyway, so it's a best practice to keep things simple and clean on the back end, your source data should only contain dimensions and measures. Think of dimensions as categorical fields and measures as numerical fields don't add any additional header footer rose, since Excel will automatically identify column headers based on the first row in your source data range. Finally, Ross Source data shouldn't contain any sub total rose or calculated fields. Pivot tables air designed to do those sorts of calculations for you and to do those in a more accurate and flexible way. So adding any fields aside from the raw values and your source data will only make things more complicated now. On the flip side, a bad data structure might be transposed with variables as rose and observations as columns . It might be laid out in an unstructured or non rectangular way. Or maybe it contains unnecessary formatting calculated fields, misleading column names or extra header rows. The bottom line is that the Onley roll your source data needs to play is to store the raw values for your pivot table. To analyze your best bet is to keep things clean and simple and apply your formats and calculated fields using the pivot table itself. 6. Inserting Your First Pivot Table: Now let's go ahead and practice. Inserting a pivot table in Excel and luckily Excel makes it incredibly easy to do so. In the insert menu. You have two options. You can select pivot Table to create a blank pivot, where you determine what data you're analyzing and where the pivot table will live. Or you can use the recommended pivot table options, which will allow you to browse some pre populated starting points. Now, if I'm being honest, I don't find the recommendations very helpful. Personally, I preferred it. Just build my pivot tables from scratch, but it's important to know that those recommendations do exist if you choose to use them. So let's jump into excel and actually practice this. All right, so here I am, an Excel. I've opened up the I M D B movie database file, available in the course. Resource is, and there are a few different ways that I can insert a pivot from here. Excel is actually really smart and helpful when it comes to inserting pivots. So the simplest way to do this is to simply select any cell from within the source data that you care about going to the insert menu, press pivot table. And, as you can see, even though I only selected one cell, it's identified. Arrange from a one through our 37 26. If I scroll to the right, you'll see that it's identified are as the last column and row 37 26 as the last row. So even though I had only manually selected one cell, Excel was able to identify the outer bounds of this data range and populate the reference accordingly. Now, the second part of this prompt says Okay, where do you want this pivot table to be placed by default? It will typically show new worksheet, which is what I prefer. Otherwise, if you have an existing worksheet that you'd like to add the pivot table to, you can do that as well. In this case, let's choose new worksheet and press OK, and there you go. I've got a new work. She created a blank pivot table template here, and then we have our field list off to the right, which we're going to dive into in the next lecture. So that's the simplest way to insert a pivot table. Keep in mind that some other options are to select the specific data range first. So if I only selected, let's say a one lips, let's say a one through p 26 and then it shows Insert pivot table. Since I had selected a specific range, Excel in this case says okay, he doesn't care about any of the columns after P or after Road 26 will use this reference for the pivot, and you could press okay there. 1/3 approach, which is a bit more manual, would be to insert a new tab first, then choose insert, pivot table and then navigate to the actual IMDB movie database and select your data from there. So a few different ways to do it, I'd recommend the first approach that we took. I'll just delete this sheet, Um, because it really is the simplest way to get started. So there you go. We have a blank pivot. The next lecture will talk about actually creating and manipulating some views 7. Navigating the Field List: The first step towards mastering pivot tables is understanding exactly how the field list works. Think of the field list as your cockpit or your command center, where you can drag and drop your fields to customize exactly how the data in your pivot table will be displayed. Now, there are five key sections of the field lifts. To pay attention to the first section on top, lays out all of the columns available in your source data, with check marks for any fields that are currently included somewhere in the pivot. In this case, we're looking at a simple data set containing Onley, four fields, state year, total population and student population. Beneath that, you have four boxes, filters, columns, rose and values. And these four boxes determine what data you're looking at and how it's filtered and organized in your pivot table. So I actually like to start in the lower right box and dragon any quantitative or numerical fields that I care about analyzing. In other words, these air the metrics that I can count things like sales revenue, population clicks, etcetera. In the example shown here, we only care about total population now. The next step is determining how we want to summarize those values. Do we want to see total population by state by year by something else, depending on how you choose to summarize your values? In this case by state, you can drag that field to either the Rose or the columns boxes. If I drag state into the rose box in the lower left, I'll end up with a pivot 50 rose tall with a row for each state, along with the total population for each. If I drag state into the columns box in the upper right, I'll end up with a pivot 50 columns wide with a column for each state instead of a row. And then finally, we have the filters box in the top left, where weaken store any field that we might like to use to filter the entire pivot table view by dragging in year like we see here, we're now able to view state populations for any specific year or any specific sub selection of years by manipulating that filter. So let's jump into excel and actually practice this. All right, so here we are in Excel or in the new sheet that we created to insert a pivot based on our I M D B movie database time and over here on the right, we've got our field list with a nice blank canvas to work with. So first and foremost, let's double click that tab name. Just name it Pivot to be a bit more clear. And let's practice manipulating fields in the field list to create different sorts of views within this pivot. So, first and foremost, I'm going to scroll through and just see exactly what measures and dimensions I have to work with here. So I've got a number of different categorical fields like title release date genre, and I've got measures in numerical fields like reviews, revenues, budgets, etcetera. So to kick things off, I'm interested in seeing gross revenue for this data set. So I'm gonna pull that into the Values box. And as you can see, it shows me just the total sum of all of the gross revenue in the entire data set. And I can actually drag that field in in a few different ways, and vice versa. I can drag it out by just releasing it back into the field list now, instead of clicking and dragging. It could have also just check the box next to the variable name. And if Excel identifies that field as New Miracle, it will automatically populate the values box for me. So now, obviously, just seeing the total revenue for the entire data set isn't particularly helpful or useful in anyway. What I really care about doing is breaking that revenue down and summarizing at different levels and by different categorical fields. So to start, why don't we break down revenue by movie title so I can click title and drop it right here in the Rose box? What that gives me is one row for each title in the entire data set, along with the Associated gross revenue total. Now it could have dragged title over here into the columns box, which gives me the correct numbers. But as you can see, it's really just not very readable or intuitive in that format. So I would drag it back and keep it in the Rose Field for now. So this is nice. I've got gross revenue broken down by title. Next thing I can do is add a filter if I choose. So what if we only want to look at films for a particular country, I can click country and drag it right here into the filters box. As you can see, Excels added a new filter right above my view, with the option to view data for a particular country or subset of countries so I can click the box and say, OK, update my view toe on Lee Show me films produced in Canada. As you can see, the whole view updates accordingly. Or it can say only, show me films in Australia and there you go and I don't have to be limited to just a single filter. I can also pull in languages well, so Narcan filter by country by language, it can pull in color or black and white. In this case, let's only look at color films in Australia, and you can see that our view updated and the same goes for our row or column labels. Right now, we're only breaking down the data by title, but we can drill deeper into this. So let's say we want to see gross revenue by both genre and title CEO just nested these two fields together. So I'm looking at the gross revenue by title and by genre. So within the action category for films produced in Australia that air in color, we find thes particular titles, which drove this much revenue, and the genre as a whole drove this much revenue. So as you can see, we're getting more and more deeper insight into this data set that we never would have been able to easily see otherwise just by clicking and dragging and dropping some fields using the field list. So now let's drag title out so that we're only looking at the revenue by genre, just like we were able to add multiple filters and multiple row labels. We can also look at multiple value fields. So why don't we take budget and drag budget and as well Now we're looking at revenue and budget by genre. Let's go ahead and clear these two filters that were no longer limited to just Australian color films. Now we're looking at all countries, all languages, all color or black and white films. So one thing you may have noticed when we added the second Value field in here is that a new label popped up here in the columns box that says values. And basically you can click and drag and move. This values field around just like any other field and what this does, it allows you to determine exactly how to organize the values in your pivot. So right now the values are organized as columns. As you can see here, we've got a call him for revenue at column for budget. We drag this into the Rose Field after genre. You can see that now our values are organized or stacked one above the other in terms of Rose. And as you move this, you can get a sense of what it's actually doing. So in this case, the values category comes first. All of the gross revenue figures, first by genre, followed by all of the budget figures by genre. Beneath that, and then we could drag it back to columns, which is the default view. And that just gives you some flexibility if you want to just exactly how your values are organized in your pivot table view. So more on this to come later. But here's a quick primer on how to create and manipulate views using the Pivot table field list 8. Pivot Table Options: Analyze and Design: So once you've added a pivot table in Excel, you'll see a new menu header called Pivot Table Tools. And within that, two tabs analyze and design. The's taps contain all of the tools and options that are specific to pivot tables. So, for example, if we drill into the analyzed tab here, you'll find any tool, an option that will help to supplement our analysis. Using this pivot table, we've got general options. We can expand and collapse. Fields group things together, insert slicers and timelines, weaken, refresh the pivot or change the data source clear, select or move the entire table. We can add calculated fields and even insert things like pivot charts, so we'll be diving into each of these tools in depth in the upcoming sections of the course . I just want to make you aware that this is where these tools live and that they're organized into these two tabs. Within the pivot tables header. So the second tab is designed, and this is where you can customize the look and feel of the table itself so you can choose whether or not to show sub totals or grand totals. You can adjust the table layouts here determine how to deal with blank rows or row headers . You can turn on or off banded rows or columns, and you can choose from specific pivot table styles, which just adjust the self formatting and fill options to give it a unique look and feel. I will also talk about creating your own styles as well in the next section of the course. So there you go at a high level pivot table tools include analyze and design options. 9. Selecting, Clearing, Moving & Copying Pivots: Let's talk about some of the simplest pivot table tools selecting, clearing, moving and copying pivots so you can find these options in the analyzed tab. Under your pivot table tools, you can clear, select and move. So starting with their clear options, you have two choices here. Clear All essentially wipes your pivot table clean, so any fields that you dragged filters, rows, columns or values will be completely removed. You won't be deleting any data. In fact, you won't even be deleting the link to your data. You're simply returning to a blank canvas, as if you had just inserted a brand new pivot. On the other hand, clear filters will only affect fields with filters in place. So by clearing filters, any field that's filtered will be reset to show all values so that your entire pivot table will end up being automatically unfiltered. Next up, you've got select options. Now, if you're using certain table layouts, you can select individual components of your pivot either just the values, just the labels, all of the labels and values. Honestly, I don't find those options very helpful or useful, but what I do find helpful is the option to select the entire pivot table, and that will select any cell associate with the pivot table. And once you have it selected, you can then copy and paste to create duplicate versions, every pivot table in other locations in your worksheet or your workbook. And then last but not least, you have your move options. These allow you to simply take an existing pivot table and move it from place to place, either into a brand new worksheet or into a new cell or location within the existing worksheet. So let's actually practice this so back in Excel, let's take a look at our pivot tab, and why don't we go ahead and apply some filters here? So let's look it on Lee movies in French that were produced in France at our color. So we've got three filters, each drilling down to particular filtered value. Now, in my pivot table tools that can drill into my analyzed tab. And here in the actions pain, you'll see those clear, select and move options that we talked about. So why don't we start with clearing the filters? So keep an eye on rows one through three. When I clear the filters. All of those go back to showing all values. And now my entire pivot is unfiltered. Keep in mind that if I had added filters to my role labels as well, which will practice doing later in the course, those would be cleared as well. So now if I go back into clear and this time clear all, you'll see that I've just wiped everything clean. So all of my fields that I had dragged into filters columns rose and values are now back here in the field list, So the link to my data is still there. In fact, if I press control Z, you could see there back into their original places. And essentially all that's doing is it's automating the process of clicking and dragging these fields out back into the field list. Now let's jump back into tools and take a look at our select options. So in this case, these 1st 3 options are actually great out because I'm using a compact table. We're gonna talk a whole lot more about table layouts in the rest of the course. But for now, let's just talk about selecting the entire pivot table. So when I do that. Any cell that's associated with the pivot is highlighted. So I filter rose, I roll labels, my values, everything, even my grand total row. So from here I can press control, see to copy it. So let's say I want to just basically replicate this pivot and put a second instance of it right here and sell it to press control V and paste it here and now I have two incidences of my pivot table and as you can see, it's copied. The style, the format, the layout, everything And what's great about this is that even though both of my pivots are tied to the same data source, they operate independently. So I can say, OK, let's see all the Australian movies here on the right and Canadian movies here on the left . And as you can see, they operate independently, which is great. But now what if I decide? OK, actually want one pivot table per sheet. I can go ahead and select that second pivot, go back into tools and here's where I would use the move option. So let's just move it. I don't want to create a copy of it. I just want to actually take this one pivot and just move it somewhere else. And let's put it into a new worksheet. When I do that, you can see that Excel basically just created a new worksheet and dropped it right there. If I returned back to my pivot sheet, that second instance is gone, and I'm just left with the 1st 1 here and now. Side note. One thing you might have noticed is that when I have a cell selected that's outside of the pivot table range, my field list disappears and my pivot table tools ribbon goes away as well. All you need to do is just select any cell that's associated with their pivot table and you'll see that feel this pop up again as well as the pivot table tools ribbon. Um, and if you don't see the field list, you can right click and it'll show up as ah Schofield list here at the bottom so you can hide it that way and then show it that way again. So there you go. Basic pivot table, analyzed tools clearing, selecting and moving pivot tables 10. Refreshing & Updating Pivots : So when you're working with pivot tables, you'll be taking one of two general approaches. When it comes to your source data, that source data will either be static or dynamic. What I mean by that said, sometimes you'll have just one set of data that will never, ever change, that you want to use a pivot table tool to analyze. When that's the case, we'll call that static source data. What's more common is having source data that you may need to edit or tweak or add data to moving forward that you want to attach Pivot Table two. And in that case, your source data is dynamic. And when you're working with dynamic data, it's important to understand how to refresh an update your pivot table as you make changes to that source data. So there are two tools in the analyzed tab called Refresh and Change data source, which allowed you to do just that. Now the difference between the two is that refresh will update your pivot table based on changes made within the defined source date of range or table. So, for instance, if you're using a range of data from a one through B 100. Imagine that you're drawing a box around that range. Any change that you make within that box will be captured by the Refresh Command. Any change that you add outside of that box, like adding data to rose 101 12123 or pasting new data into column, C, D, e or F. Those will not be captured by refresh. For changes like that, you need to use the second option, which has changed data source and what this does. As you might expect, it allows you to refresh the pivot to reflect changes outside of that defined source ranger table. So, for instance, stacking on new columns or new rows of data. A common reason this is used is when you have something like time serious data, for instance, where you constantly need to be stacking new data on to capture the most up to date data. When you have a case like that, that change data source option will be helpful, and also we'll talk through some pro tips specifically related to working with growing source data in the following lecture as well. But a little sneak preview at that pro tip. When you are working with dynamically growing or changing data there to really helpful tips here, you can either format your source data as a table or you can use column only range references. So no row references to help you work with data that's changing. And again, I'm gonna talk through that in the at the next lecture. But for now, I want to go through some of the basics with refresh versus change data source. So let's jump into excel and take a look. Okay, so here we are in the IMDb movie database. First and foremost, let's just delete this new sheet that we had created in the last lecture. And if we go ahead into pivot table tools, analyze, you'll see this little data pain here with those two options. Refresh and change data source. So as a reminder, if we click change data source, it will tell us exactly how we're defining that source data. And in this case, we're looking at a data range from a one through our 37 26. And if you scroll down, you can see that that green box that's surrounding the data so compress okay and Now let's practice using the refresh option first. So jump into my source data tab and scroll up to top. And why don't we just make one change to one of these data points just to show you how it works? So Row five, this movie called 42nd Street just gonna arrow over to the gross revenue cell here in Q five. Right now it's 2.3 million. Let's just manually change that to something really obvious. Like 9999999 So I manually made that change. I made a change within the confines of my source data. If I go back to pivot, first thing we'll do. It's just unfiltered ER, and instead of looking by genre, pulled out into the filters and let's pull title into the Rose. Now, if I scroll down 42nd Street here in Row 55 that's that's the movie that were interested in . You'll notice that it's still reflects that $2.3 million gross revenue because the pivot doesn't know that I've made any change the source data yet. So to tell the pivot table Hey, I've made it change the source data. Please update to reflect it. That's where we going to pivot table tools and press refresh So you can see that 2.3 million change toe that Siris of nines that we added right there. And it's a simple Is that so? That's how you use refresh. So let's go back. Just gonna You can leave it if you want. I'm gonna change it back toe 2.3 million And now that's an example of making a change within a specific source data, right? Another kind of nuanced point is that if I were to add a new column here in Colin S, the pivot table refresh option would not see that data because it's outside of the range. But if I right click, call him are and insert a new column from somewhere within that box, we do around the range. And let's just name this new column just as an example that will be reflected by refresh, even though I've created a new column since I created it within the range, it actually told Excel to expand that range to now extend through column s instead of are so back in my pivot scroll down in my field list so we can see pivot table tools. When I press refresh, you'll see that new column. I show up right there and vice versa. If I right click and delete that column, go back in a Tibbett and refresh again. There you go. It disappears. So now let's talk about change. Data source. Perfect example is if we added a new column and s, or what might be actually more likely is adding data similar to bottom. So I'll give you a more specific example when we dive a little bit deeper in the next lecture. But for now, let me just right sample title here in row 37 27. If we go to our active it and we hit refresh, nothing's gonna change because it's still on Lee going through row 37 26. So what, I would have to dio Does that have to go to change data source? It's girl all the way down, and I can either re select by dragging a new range. Or it can just change the 37 26 2 37 27 press OK, and if we cook that button again and scroll down. You'll see that it now includes the new road that I added. Now, when we scroll down all the way to the bottom, there's a new sample title. Obviously, I didn't add any data in there, So the show it was blank, but it's now included. So that's an example of how to capture a change made outside of the defined source data range using the change data source options. So that was the manual option. There are at least two ways to do this a lot more efficiently, which we're gonna talk about next, so stay tuned. 11. PRO TIP: Dealing with Growing Source Data: in the last lecture, we talked about some of the more basic refresh and data source options, But now I want to share to pro tips that are really, really helpful when you're working with dynamic or growing source data. So back in our I M. D B movie database file, remember that the last tip that I showed you was to simply stack or a pen new data into rose beneath the existing range. In this case, we added a fake title called Sample Title in Row 37 27 and from there we had to go back into the pivot pivot table tools and change the data source manually capture that new data . But the fact is there more efficient ways to accomplish this same task. First option is to remove the Rhone numbers from our source reference, which will essentially tell Excel to pull in any rose from within those columns so that you can simply refresh rather than adjusting that reference each time. And so, in our tools, gonna go into change data source and all I'm going to do is remove the fixed reference to wrote one and the reference to Row 37 27 so that I'm left with a column only reference. And again, this just means that excel. We'll be looking at any cells contained within columns. A. Through are no matter how many rows down they fall. So I press OK, you may notice one change, which is that a new row appears with a blank. And again, that's just because now our source data scrolling down doesn't end at the last row with data in it, it extends all the way down to all rose in the worksheet, which obviously includes some blanks. So the benefit of doing this is that now, when I stack data underneath my existing data like sample title to, for instance, now I don't have to manually change the range. I could just hit, refresh, and, as you can see, sample Title two pops up could do the same thing with sample Title three. Refresh. There it is, So it's certainly save some time taking that approach now. The second option, which is even more efficient and a bit more scalable. It's to format our source data as a table rather than a range and the difference. Looking at our movie database tab is that arrange to excel. It's simply some collection of data points or cells across rows and columns, whereas a table can be named and referenced as a self contained unit. And one really nice benefit of tables is that they can adapt and grow automatically as you stack more data. So to show you that, why don't we just get rid of these three rows with our sample titles and I'm gonna scroll all the way to the top, and what we're gonna do is select cell a one mean use the control shift arrow right, arrow down shortcut, which contains all of the cells within my range and appear in the home menu. Just gonna choose format as table and I'll pick the format. I like just gonna choose this 1st 1 and it says, OK, where's the data and defaults? The selection that I just made and I checked the box that says Yes, my table does have headers right here in row one, and when I press OK could see that it's formatted it as a table, and it's launched this new option called table tools and within the table tools. I could give this table and name. Let's just call it movies. And what's great about this is that now we can go back into my pivot. I go into the change data source options. And instead of a traditional range reference like this, I can simply type the name of the table movies. When I press OK, you can see that I still have all the same data. All the same fields values everything. It's just that now movies refers to all of the same data in the form of the table on the IMDb movie database tab. So we simplified the reference, which is great. But the added benefit is that now I can stack data and automatically be absorbed into that table and to show you how this works. Have a second Excel file called 2016 movies, and this is available for download on the course. Resource is if you choose to follow along. Basically, this is just a fictional set of additional movies that we can use to practice stacking onto our existing IMDB database. So we've got some new movies that were released in 2016 that aren't captured in our current data set, so all I would need to do is select all of my data here and roast two through nine. I can do this because I know that my column headers match. Exactly. And if I just copy from this workbook and go to my movie database tab with my table, watch how cool this is. If I select a 37 27 I use control V to paste that new data. You can see that it's been absorbed into the table itself, so I don't have to redefine the table change any source references. Those new rose have just become part of the table. And that way in Pivot Tab, just like before, all I need to do is hit, refresh, and there you go. You'll see all of those new rose that were added to the table. Now, if you want to get really advanced, you can take this step further and actually populate this table based on a link to an external data source, like a sequel table or view that can refresher update automatically. But for our purposes here, this will certainly get the job done. Now let's say you change your mind and you don't want this to be formatted as a table anymore. All you need to do is go back in your table tools and click this convert to range button. And when you do that, yes, essentially, we now just have a range of data again. Even though it maintained the formatting of the table. These cells air now, technically just part of a range. And when we do that, we have to go back into our pivot, only change your data source. You can see that it's no longer referencing that table name called movies. It's now defaulted to referencing the actual range from a one. There are 37 34 so there you go to more efficient ways to deal with dynamic or growing source data using pivot tables. 12. How Pivot Tables Actually Work: So to wrap up this pivot table 101 section wanted to include one lecture to talk about how pivot tables are actually working. So in other words, when you pull back that curtain, what exactly is Excel doing behind the scenes to populate thes values? So Step one is about detecting and evaluating coordinates, and by coordinates, I mean the role labels, columns or measures and any filters that you've defined in a pivot table view. So in this case, for interested in knowing how this number 9 73 was calculated, we first start by looking at the corn. It's that generated. So in this case, the state is Arizona. Measure is total population, and the filter includes all ages. So those are our coordinates. And given those coordinates, Excel then jumps into the source data, and it isolates Onley the cells that meet those exact criteria. So in this case, there are five individual cells or values for the state of Arizona for all ages. Now, once Excel knows which sells, meet those criteria. Step two is about applying any arithmetic, so sometimes you want to return the sun, the Count Max or the men in this case, we want to return the average of total population. So given these five cells from step one, Step two is about applying that average. And then finally, Step three is one. Excel just displays the result so it takes those five numbers. Add some up divides by five, and that returns the 9 73 that then gets displayed in the pivot table. So a cool note here you can actually double click on any specific value in a pivot table, and it will generate a new tab showing the exact source data used to calculate it. It's gonna be a great tool to help Q A or just get a better sense of exactly what's going on behind the scenes. So let me show you exactly what that looks like back here in our I M D B movie database pivot. Let's go ahead and apply some settings. I'm gonna pull title out and bring genre into the role labels. And let's just apply some filters here. What's on Lee? Look a genres for Australian movies, since they were curious how this $843 million revenue number was calculated and this reflects the sum of all of the gross revenue for all action movies in Australia to dive in deeper, all we need to do is double click that Selby seven. It creates this new sheet with a table that's essentially a subset. Are sampling of her full raw data filtered down based on the coordinates of my pivot table view. So as you can see, column D Onley includes titles where the genre equals action and country and columns F is filtered down toe on Lee show movies from Australia. So now if we scroll over to this gross revenue column column que and select those values, we can see that the sun is 843261855 Jumping back to activity 843261855 So that tells exactly how that value is being calculated by showing us the actual subset of data that went into producing it. So let's go ahead and just delete that sheet. This won't change any of her actual source data. Everything else is preserved here in our original movie database tab, But there you go. Nice little tip to better understand exactly how excels producing these values 13. Number Formatting: now that you have a pretty good sense of how to insert and manipulate pivot tables and how to deal with the source data that feeds into them, it's time to start thinking about formatting. And by formatting I mean customizing look and feel and style and design of the pivot table itself. So you may have noticed up to this point that the numbers have been pretty tough to read. And that's because by default, Excel will just apply a general number format, which really has no formatting applied to the numbers at all. This makes it very tough to read large numbers like this, which don't have any comma separators for thousands. There are no dollar signs to indicate that we're looking at monetary data. So the first tip that I'm gonna show you in this formatting section is how to adjust number formats. It's really easy to do a couple of ways to do it. What I'd recommend doing is selecting the column that you're interested in reformatting right, clicking the column header and choosing this third option here for number format. This will launch the format cells pain, which should look familiar for pretty much anyone who spent much time working in Excel. This is where you can customize exactly how you want your numbers toe look really great tool when it comes to formatting dates or times or percentages. In this case, we're looking at revenue and budget, both of which are currency fields. So choose currency. It's defaulted to add this dollar sign here, which is great. And since we're rounding out two whole numbers, I don't need any decimal points. So gonna drop that down to zero press. OK, so there you go. I can apply the same process that budget field. Um, one thing to know you can select the column header, or you can select any individual field from within that column, and it basically accomplishes the same thing. So currency, no decimal places. Okay, you can see that even though I had only selected this one sell, the entire field is formatted consistently. What's nice about this is that right now we're looking at the data by genre, so we have a pivot that's seven or eight rows tall. If we pull genre out and drag titling as you could see, the formatting persists. No matter how our pivot table grows or shrinks or changes 14. PRO TIP: Automatically Formatting Empty Cells: one quick pro tip. As faras number four, Manning's concerned there's actually an option that I didn't find out about for many, many years. That allows you to customize what to do in the case of blank cells. So in this case, let's manipulate our pivot to see some of those blank cells. Now, instead of revenue and budget, let's pull budget out so that we're only looking at revenue. And instead of title as a role labels, let's look at the data by country by genre as columns. So now we have this two dimensional pivot table where we're looking at data by country in a rose and by genre in our columns. And as you can see, we don't have data for every combination of genre and country. So, for instance, we don't have records of any action movies in the Czech Republic. Same with adventure animation biography. So if these blank cells bother you and you'd prefer to replace those blank values with something else, there's an option in your pivot table tools that will allow you to do this all the way. On the left, you'll see this options box. Just click on it, and in the layout in format tab right here in this format section, there's a box that says four empty cells show and right now it's blank. You can change that to anything you want. In this case, I think it's safe to assume that if there's no data than that's the equivalent of driving $0 in revenue. So I'm gonna change that to zero and press OK. And as you can see now, those blank cells populate with zeros, so certainly not critical. Everything will work properly whether I replaced those blank values or not, but it's helpful to know that that option does exist. 15. Table Layouts & Styles: next up, I want to talk about table layouts and styles. This is a really important lecture because there are a few reasons why you very well may want to use different types of table layouts. And in fact, the default options that Excel provides, in my opinion, really aren't the most useful formats at all. So let's take a walk through, and I'll show you what some of these options are. So again, within pivot table tools, you've got analyzed options and design options for this lecture. Let's focus on the design tab, and here you've got your layout choices on the left and all of your style options to the right. Now the style options really speak for themselves, just like you conform at a table. You can also form at a pivot table with all of these different stock templates that Excel provides. Personally, I like to keep it simple and just go with one of the light options. If you don't want any formatting and all, you can choose the upper left option. Let's just stick with something kind of like this and then other style options. Here you can turn on or off the shading on your row or column headers, and you can add banding two rows or columns. Just help differentiate those. Personally. I typically don't use banded rows or columns, but totally subjective up to you. And then that brings us to the layout options Here in the left. I want to take some time to really talk about what these options provide. And to do that, I'm going to reorganize this pivot table just a little bit. And I'm gonna pull a genre out of the column labels and nested under country as a second set of Roe labels. And let's also bring in color black and white and drag bed in. It's 1/3 element, so by default, as I do this, you can see here in the pivot table that it's essentially nesting those dimensions or categories one under the other, all contained within one column called Roe labels. And this is what we call a compact table style. And it's the default style at Excel will use as a starting point. And if we go up to the report layout options, you can see that compact form is the first option here on the list. So one thing that I don't like about compact form is that it combines or condenses thes fields such that it becomes very difficult or impossible to apply things like filters or sorting options to these individual fields because they're all contained within the same row labels. Which brings me to the second layout style, which is actually the one that I prefer by default called the Outline Form. And when I switched the table toe outline form, it essentially breaks out that one roll able column into three individual columns, one for each dimension or field here in my role able box. So now I have a column for Country column for Genre and a column for black and white there still nested in within each other as they should be. But now I have the option to apply things like sorting options, label filters, value filters, etcetera, and we're gonna talk all about how to do sorting and filtering later in the course. But just keep in mind that the outline form is a really nice way to kind of blip these fields out so you can add it them as you wish and then our third report layout, which we're gonna talk about in depth in the next lecture. It's called tabular form. When you click on tabular form, it essentially tries to recreate the look and feel of a table. So what it does is that it actually puts the country genre and color black and white label all on the same row to kind of replicate what this data would look like as a raw data set or table on again. We're gonna dive into that and show you a quick example of that. In the next lecture, let's jump back Teoh outline form again and talk about some of these other options. One options to repeat the item labels. And that just fills in the blanks again. As it sounds, it's just repeating the labels in each row I feel in Turn that off. You could just choose this last option here and then similar option here with blank rose. If you want to just space things out, make it a little bit more readable. You can insert a blank row after each section of data, and then go ahead and remove it. If you choose. You don't want that, and then finally, we've got our sub total and grand total options. This is just really determining. If you want to show these totals, either for your rose or your columns, you can toggle, ease, honor off right now. Actually, just have one grand total line, which would show up the very bottom, and I've just turned it off, so there's nothing after row for 18. If I turn that on, there you go. It pops back up and then, similarly, with sub totals, the use of the totals that show up for individual categories or row labels. So if you don't show the sub totals, you'll see all those numbers disappear. Whereas if you do enable sub totals, you can choose to visualize them at the bottom of the group, like so or at the top of the group like this. Eso Australia Revenue as a whole 1.5 billion action movies in Australia, 843 million black and white action movies drove nine million, whereas color drove 834 million. So those sub totals do help to kind of show how things air aggregating at different levels of your data, but sometimes you don't want those so you can toggle it was on and off right here. So those air table layouts in a nutshell. Next up, we're gonna talk specifically about using tabular form to essentially produce a new source data set using a pivot table. 16. PRO TIP: Using Tabular Tables to Create New Source Data: So one pro tip relating to table layouts is using the tabular layout to create new source data, and I'll let you know what I mean by that. So let's say in this case we've got our I Am D B movie database. It's a ton of rows of data. It's at the title level with all these different categorical fields and metrics. And let's say, using the pivot, we just want to aggregate that data up to a slightly less granular level. So instead of by title, maybe I just want table of data that shows revenues by country by genre. And that's the deepest I wanted to go. In that case, I can exclude anything from a pivot that I'm not interested in in this case. Let's pull color and black and white back into filters. And now I've got the data that I really care about revenue by genre by country. The problem is, if I copy this and paste it somewhere else, it's not in a very useful format. So I can't just take this and loaded into a sequel table or an access database. Um, and even if I move it to a new work sheet in Excel. I've still got these these blank rows, these totals repeating values. So this view is helpful from a data exploration and analysis standpoint, but not necessarily helpful in terms of serving as new source data. So what I can do here is change from the outline Modric currently in tabular and make a couple adjustments to turn this into a nice, clean table that we can use elsewhere. So the first thing I'm gonna do is repeat all the item labels and then I'm gonna turn off the grand totals and turn off sub totals. And there you go. This gives me a nice, clean, tabular format with one line item that shows the country the genre and the revenue. This means that there's no overlap. There's no duplicated values. There's no extra blank rows. Now all I need to do is grab this table and move it elsewhere and save that as whatever I need to a CSP unloaded into a database tool, whatever might be. But this essentially has allowed me to aggregate my data at a completely different level of granularity and produce a new rock tabular data set that I can use somewhere else 17. Customizing Headers & Labels : So another pivot table formatting trick that's very, very easy, but not always intuitive is the fact that you can change and customize your column headers . So right now we've got our role labels, which are named exactly what the Fields Air named country and genre but for gross revenue when we pulled it in, it automatically assigned this label some of gross revenue, and that will show whatever summary ization mode you're using on. We'll talk about that more in the calculated value section. But some of gross revenue isn't a very friendly name, so it's a want to change that to total revenue. Instead, all we need to do is select that header label and go up in the formula Bar and just type in the new name right there. And it just changes the column header to whatever it choose. The one thing I will point out here is that if I wanted to name this gross revenue, which is the name of the raw Data column, I'm gonna get this error that says the pivot table field name already exists. So a little work around here. If I just add space at the end, Excel will treat that as a totally separate value and allow me to drop that in so little tip there. Sometimes you want to use more friendly or more intuitive names for it columns. That's how you do it. 18. Conditional Formatting: one of the coolest and most useful ways to customize your pivot. Table formatting is using conditional formats, so conditional formatting rules could be applied to pivot tables, just like they could be applied to normal data ranges. And you can access these formatting rules right in the home menu of your screen. And for those of you who aren't familiar with what conditional formatting is, it's essentially a formatting rule that you can apply that's based on the underlying value of the cell. So, for instance, you can use text or value based formats and maybe want to highlight values that are greater than a certain number or highlight text fields that contain certain text strings. That's one option. You can also use data bars. It's a really great way to visualize relative values like the blue bars shown here. You can use color scales, which is an awesome way to make very easy heat maps, icon sets which are like up and down arrows and check marks and things like that. And then if you want to get really advanced, you can dive into actual formula based formatting rules, which we won't really cover here. If you are interested. You can check out that section in my advanced formulas and functions course where we dive into that piece. So let's jump back to our pivot table and actually test some of these things out. Alright, So back in our I M D B movie database pivot, let's use some conditional formats here in a gross revenue column to really bring this data to life. And to do that, just gonna select the entire column of data can either drag down or it can use the control shift arrow down shortcut. And once I have that whole field selected in my home menu, I can drill down into the conditional formatting rules. Now I'm gonna kind of cruise through this, just really scratch the surface. I do dive into these much, much deeper in my data visualization course, So if you're excited by this and you want to learn a little bit more, recommend checking that out. But for now, we'll give you kind of just a taste of how these could be used. So starting with highlight cell rules, this is a great way to highlight based on the cell's value itself. If we were looking at a text field here, you'd see these other options available, so text that contains certain strings or duplicate values. In this case, we're looking at a numerical field, so our options are limited to greater than less than between or equal to. Let's format, any values greater, then a certain number. We can just type that number right here, so I'm gonna choose 100 million, and we'll format it with Green Phil with dark green text press. OK, now, any value in that range that I've selected that's over 100 million is highlighted in that green shade. So there you go. I can press control Z just to undo that. In fact, let me press control. Why to put it back? Because the other way to remove formats, it's to go into conditional formatting manage rules at the bottom. You always have access to this manage rules choice, and when you get in their it list out any formatting rule that's applied to the selected range. So right now it's just the one that we created, so we can go ahead and either edit that if we want to change the format, for instance, or we can delete it and press supply. And now we're back to a clean slate here. So let's test some of the other ones. We've got top and bottom rules where you can highlight you know, a certain number of items like top 10 top five top percentiles highlight cells that are either above or below the average for the selection. Really, really useful features here. So let's choose top 10. And instead of 10. I only want to see the top five in terms of revenue and we compress. Okay. As we scroll down, we should see five selected cells. So there they are. These are the top five revenue driving titles press undo to get a clean slate again. And now we've got some cool visual icons, starting with data bars, and this again is a really nice way to show relative volume. You have just different colors and styles here. I'll just choose blue bars. And as you can see, these are all pretty low volume here. So that bars air small. The reason for that as we go down is that the U. S. As you can see here, is a really, really big player in the movie market. So really, the number of ticket sales and therefore the number of dollars produced in revenue really dwarfs all other countries in the status sets. That's why we see very small bars appear for most countries. And one thing that's worth pointing out here is that even though I've applied this rule to the specific range from believe, see, let's see, see 73 c 1 31 or 1 71 even though I've applied it to that specific range. If I were to pull country out, that conditional formatting rule will adapt to this new range. So I don't have to clear that rule and set it again. It will kind of morph as my pivot table changes shape, which is really useful. So why don't we select this range that the rule is applied to now and go in and we'll manage our rules and we can select and delete it from here. You can also just choose clear rules from the select. It sells, essentially does the same thing and let's take a look at a color scale so again it gives you some options. But all of these are totally customizable. In the manage rules section so we can choose a scale from green to red, where greenest high and red is low. We can flip it red to green. Can you shades of red and blue. Let's just do a standard red to green. And this will give us a sense of which genres across the board are driving more revenue than others. So just from looking at this, you can tell that action drives the most. And that adventure, comedy and drama are the other high producing genres. And then let's select this again. We'll do one more example. I'm gonna clear this color scale that we just created. And now let's do some icon sets so this really it does the same thing. It just allows you to apply different types of icons, like up and down arrows, stoplight shapes, stars, flags, etcetera. So let's drop some flags in there, expand the width of the columns that we can see the numbers and then with all of these icons sets. When you manage the rules, you can select the rule and edit it in order to determine exactly how you're defining a green flag versus the yellow versus a red. So all these rules air completely customizable. So there we have it. That's a very quick run through of how to apply conditional formatting rules to pivot table fields. 19. PRO TIP: Data Bars with Invisible Text : Okay, well, we're on the topic of conditional formatting. There's one pro tip that I just have to share with you guys, and it's using a combination of data bars with an invisible text effect to create a really nice visualization. So first things first, let's go ahead and clear these flags that we had set in the last lecture and what I want to do here is pulling a second instance of gross revenue. So even though it's already inserted into the pivot, I could just drag that field again and create a second instance of revenue. And the reason I'm gonna do this is because I'd like to have one column that continues to just show me the raw values themselves on their own and a second version of the column to treat purely as my visualization column. So, like I showed you in the last lecture, I could just select the values, go into conditional formatting and add data bars here. That works OK. My problem with this is that I don't like the overlap between the numbers and the visuals themselves. I think it's a little bit messy. It's tough to read, so I really want to get the numbers out of this second instance, Especially since I'm gonna keep them preserved in the first instance of my gross revenue column. So a lot of people will tell you. Okay, Yeah, you can select the data and you can do a font size of one or something like that. Or maybe choose your fund white, and that will get you 90% of the way. They're usually, but you still have some weirdness like this, and it doesn't always do what you expect it to do. So I'm gonna undo those changes and show you a better approach, and that's using a custom number format. So when you right click and go into number format and down here in the custom option, I can type a specific custom self format here. And here's the key. Few type three consecutive semi colons and press OK that tells Excel to make the font invisible, and that's exactly what we're trying to do here. So now we've got the numbers themselves and the Associated Data Bar, So I've got a really nice visualization that tells the story very well. So there you go pro tip, using data bars combined with custom self formats to create a really cool visualization effect 20. Sorting Options: All right, Welcome to Section four. Sorting, filtering and grouping. Now, up to this point, we've been really sticking with the basics. We learned how to bring in and insert pivot tables, manipulate views, deal with source data and use formatting tools to customize the look and feel. But realistically, we're not even scratching the surface in terms of how powerful these pivot tables can be. And this section is designed to start to show you some of those capabilities specifically in terms of sorting, filtering and grouping. And these are the tools that will start to allow you to slice and dice and explore and really analyze your data in much more meaningful ways. So when you're looking at a pivot for any of your column headers, you should see this little drop down menu, which activates the sorting and filtering options can also right click on any value within the column to get to the same menu. And in here you'll see a combination of sorting options like a dizzy zito, a or more sort options, which allow you to choose ascending or descending orders based on other metrics, which will talk about and then you've got your filter options is while you're label filters , which are based on text your value filters, which are based on numerical fields, and your manual selections here at the bottom. So let's jump to our pivot and start out by practicing some of these three sort options. So here we are, back in her IMDb pivot. And again, we're looking at genres with gross revenue. Two instances, one showing data bars, one showing her raw values. Now, if we want to do some sorting of this genre field, as you can see, it's defaulted to a dizzy, which is pretty standard. If we switch that to Z to A, as you might expect, the order just reverses. So now we have westerns at the top and action down at the bottom. You also have the ability to customize the order of this list if you choose just by dragging and dropping. So let's say I want to move mystery up. It could select the mystery line and shift my cursor right to the edge until it turns into this four pointed shape and then click and drag and just move that to where I choose. So that's always an option. But When it comes to sorting options, the most powerful tool falls in this more sort options bucket. And if you open that up, what this allows you to do is order your genres or whatever role labels you're looking at, either ascending or descending by one of the fields or metrics that's currently in your pivot table. So right now we've got our two measures of gross revenue. I could choose either one. Let's pick our 1st 1 and we're gonna order the genre's descending by gross revenue when you press OK, as you can see now, actions at the top, followed by comedy than adventure and drama. And our data bars reflect that as well. So similarly, I could go back in the sort options and choose ascending instead. And that just reverses the order here. Now, one thing that school is that if we dragged in another field, let's say budget, for instance, and I want to go ahead and format dis consistently currency with no decimals. Now, if I go back into my sword options and go into descending again, I've got budget as an option as well. So the more fields that I bring in the more sorting options that I have. So in this case, let's sort descending by budget. And as you can see, our chart of revenue follows a very, very similar pattern, just a few exceptions. Which makes sense because if films that had the highest production budgets tend to be the ones that sell the most tickets and generate the most revenue, and then keep in mind that right now we only have one set of role Abel's with genre. But we could pull others in a swell. So let's say we wanted to see by country first. Remember, we're looking at a tabular table layout right now, which isn't the best format for analyzing or exploring data. So we're going to the tools design report layout outline form, and why don't we go back in a report layout and do not repeat those item labels? So now I've got a cleaner outlined view. If I wanted to show sub totals here, it could turn those back on the top of the group, and now, because we're in outline form, were able to access our sorting and filtering options for any of our role labels. So I consent specific rules for country or specific rules for genre. So in this case will keep our genre sorted. Descending my budgets And why don't we apply a second sorting to our countries from Zito? A. So as you can see now, we start with West Germany than USA, and we're going in reverse alphabetical order. And within each of these countries, the genres are sorted, descending by budget. Since that's the sort logic that we set. So tons of options here. Tons of functionality sorting tools, air really useful when it comes to starting to drill into your data and identify trends and insights. 21. PRO TIP: Why is my Alphabetical Sorting Wrong?: all right, quick pro tip here is Farrah. Sorting is concerned. There are certain cases where you may want to try toe sort a list alphabetically, and you see some weird values that don't seem to be following true alphabetical order. And I'll show you an example of that and why it sometimes happens. So to do that, let's kind of reset our pivot table view a little bit. I'm gonna pull country and genre out in a brink title in as a role able and let's lose that second instance of gross revenue. So in my title column, let's just set it a dizzy and this should be alphabetical, but you'll see some weird titles in the top of the list. Got Mae and Rose seven Friday, November. These are actual movie titles, but they start with the letter M, F and N. There's no reason why they should be at the top of the list. When we're sorting alphabetically from A to Z, you can see that starting here in Row 10. From then on out, everything looks to be pretty accurate. Got our movies that start with numbers on and then heading into the A's B. C's D's, so everything else seems fine. It's just these three titles at the top, and one thing you may have noticed is that these air all date related titles, So the words may Friday and November are part of excels. Custom lists behind the scenes that help it understand how to interpret those as dates. Andi. Since custom lists take precedence over alphabetical ordering, we end up seeing those fields at the top of our list. So luckily, this is an easy fix. All you need to do is go in your pivot table tools, options on the left, going to totals and filters, and just uncheck this box that says, Use custom lists, went sorting and press OK, and once you do that, those three values will disappear. They'll show up later in the list according to normal alphabetical logic, and there you go, so you may or may not ever run into that issue, but if you do now, you know what's causing it. 22. Label Filters & Manual Selections: all right, so we talked about sorting options. Now it's time to talk about filtering. And filtering is one of the most common and powerful tools that you'll be using constantly when you analyze data with pivot tables and we'll stick with our title column here and just drill down into the sorting and filtering options. And in this lecture, I'm gonna talk through two components here, going to talk about this manual selections box here the bottom. And then we'll go into the label filters, which air filters that are triggered by text based criteria. So starting with the manual box here, the bottom, as you can see it, shows me a list of all of the values in the column. In this case, several 100 unique movie titles. And what this option is great for is if you want a pinpoint one or two titles to show in your pivot or on the flip side if you want to show your entire list with the exception of certain titles, and this is a great way to enable very manual sub selections of your lists that you maybe can't capture with traditional label or value filters so really useful part of this tool is the first check box that says Select all. What that allows you to do is basically toggle the entire list on or off. That way, if I have them all selected and I only want to show three specific movies, I don't have to de select, you know, 400 others to be left with what I want. I can toggle them all off and then turn on just the titles that I want. So in this case, let's say I want these three titles 10 days in a madhouse. 10 Things I Hate About You and 100 to Dalmatians can manually select those three and press . OK, there you go. On the flip side, I can select all of my titles de select those three and I'll have my fullest minus the three that I d selected. So you may be saying all right, that's convenient because those three titles were right at the top, very easy to navigate to, which is a good point because sometimes you want to find titles that are buried somewhere deep in your list. So if I select all you'll see that I've got a lot of items might be pretty tedious to use this scroll bar to find the exact items that I'm looking for, and that's what this search box right above us four. So if I want to find only movies that contain a certain string or word like snow, for instance, it will dynamically filter that list right here in the view to show me on Lee the titles that meet that criteria. So by default, this box at the top will be checked That says, select all search results and that will automatically apply the selection to any title with the word snow. When you press OK, you can see there about nine titles here with the word snow or the text string snow as part of the title. Now, one last thing to note about that option. If I go back to select all press, okay, got my starting point again. Now what if I want to use this search bar just to find a specific title, but I don't want all of those titles to be selected. So let's say, for instance, we only want to show snow dogs here in the titles I could search for snow. They're snow dogs right there. But you see that it's it's selected all of the other titles that have passed the criteria, including Dead Snow Snow Day. If I don't want those, I could either de select them here or it could just uncheck that first box and press snow Dogs press. Okay, so that will filter my list down to just that one title. And to show you the reverse effect, let's go ahead and select them all again. If I wanted to show all of my titles. With the exception of Snow Dogs, I'll go through a similar process here. Search for Snow. And now here's the catch. If I want Excel to return the entire list, not just titles containing snow. With the one exception of the Snow Dogs title, I need to select this second button, which says Add current selection to filter that way. The list that's return will be the entire list of titles, not just the titles that contain snow. So by checking that box now, I essentially have the entire list selected, and I could go ahead and uncheck snow dogs and press OK, and if we so liked all of a could just do a control F and try to find snow dogs in this list. And, as you can see, it can't find it because we've filtered that one title out of the list. So there's the manual selection options at To clear those, you can either press the select all button at the top or just press this clear filter from title, and that will accomplish the same thing. Next up, we've got label filters and again, these air any filters that are triggered by text based criteria. So text that equals a certain string does not equal begins with ends with contains. And then you might be wondering why there would be options like greater than less than or between, which feel like value related options here. Best way to think about it is think of alphabetical orders kind of ah, sequence. So if we say show me titles that are greater than be, it will look at the first letter in the title and Onley give me titles that start with B, C, D E, and so on, and the same applies for less than or between eso walk there. A few examples of those in terms of equal or does not equal those air typically used when you want to drill down to just either a single title or an entire list. With the exception of a single title. And for those types of tasks, I'd recommend using the custom selection boxes like we just talked about the label filters that you'll most commonly used are things like begins with ends with and contains. So let's start with begins with, You know, it's a simple is this. Let's show Onley titles that begin with the letter D press. OK, and there you go. We have all of our D titles listed out, and you can get more specific than this to you can say, All right, show me any titles that begin with D E. And now it's on Lee de Titles. And you can continue that process, obviously, and getting more and more granular if you choose, does not begin with well, essentially, just do the inverse. If you choose all the titles that don't begin with D, you'll see all your A's B's sees, and then you'll jump straight to your E titles because you've excluded any D titles Really , really straightforward there. Same logic applies for ends with and does not end with contains basically gives you a bit more flexibility to say, Show me any title that contains the word night. I don't care if it's the first or the last word, or if it's buried somewhere in the middle. It will show me any titles that contain that text string n I G H T. And then last but not least, we've got our greater than less than or between greater than, for instance, will show me any title greater than or equal to whatever text oring I choose. So are, for instance, will give me titles starting with R s all the way down to Z and then between just allows you to put a second criteria onto it so greater than or equal to be and between be And, let's say, e one thing to notice that the first item is greater than or equal to, so this will contain be titles. The second criteria is just less than so. This will return B, C and D titles, but not e titles, and you can just play with that and get a sense of how it's working. So B. C's and D's. There you go. And then the last thing that will call out here is that any time you launch one of these dialog boxes, the actual operator here in the beginning, like begins with their ends with this is dynamic. So you can actually access all of those operators from within the same dialog box here. So those air basic filtering options, the next lecture is gonna talk about how to make these a little bit more dynamic and complex, using these wildcard options here, these question marks and asterisks, which is a great way to enable some more sophisticated filtering logic. So let's go ahead and just clear the filter from the title lists that we have a clean starting point. There we go. Basic rundown of label filters and manual selections. 23. PRO TIP: Using Label Filters with Wildcards: so quick. Pro tip when it comes to label filters, is using wild cards. So if I drop into our sort and filter options here and go into begins with, you'll notice these two lines of text down at the bottom. The question mark is used to represent any single character. The asterisk is used to represent any Siris of characters, so these are the two types of wild cards you can integrate into these filters to get a little bit more sophisticated. So like we talked about in the last lecture, if I want to show all of the titles that begin with a certain letter very straightforward, just type the letter press. OK, this case, I've got all my titles that begin with a very simple going back into that label filter. What if I want any title where A is the second character? Now? I can't use the standard tools. I have to use some wild cards here. So if I want a to be the second letter, all I need to do is put a question mark in the first position and I can use a capital a lower case. A. This isn't case sensitives that's fine. And basically what question Mark says is Alright, Excel, give me any title. I don't care what the first letter of the title is, but the second letter has to be a when I press OK, Now you see all these titles that start with B C. D and so on. But a in every case is the second letter. That's a good example of using the question Mark wild card. And we can also do clever things like, you know, if we want the first letter to be s and then something in the second position and end to be the third and press OK, we'll get any titles. Where s is the first? The second letter can be anything I a Oh, you And the third letter is an end. So we're getting more and more complicated here and more specific with our filter conditions. Now the difference between using a question mark and an asterisk is that each question mark represents a single character, whereas the asterisk can represent any length of characters. So to show an example, let's add a second question mark between the S and the end press. Okay, Now we have titles that are s anything, anything. N so asked his number one and as number four and going back in. If we were to take those two question marks, delete those and replace them with an asterisk. Now what? We're gonna show our titles that start with s and contain any string of characters leading to an end somewhere else in the title. So press okay, we see a much larger list. All of them start with s and all of them have an end. But I don't really care where the end falls. And then finally, let's do one more example with the asterisk wild card where we can say Show me all the title sets. Start with the letter A contains some string of text followed by the entire word night, so I compress. Okay, we get these nine titles Hard Day's Night, a Knights Tale, A Night at the Roxbury and Nightmare on Elm Street, etcetera. And there you have it. So these wild cards air really great tools when you want to get more complicated and more sophisticated with your filters. So let's go ahead and clear that, and very have it 24. Value Filters: and we talked about label filters, which are text based filters. Now we're gonna talk about the value filters, which, as you might expect, are triggered by value based criteria. This is where you see those typical conditional statements like equals does not equal greater than less than between or top 10. And to be honest, most of these are super straightforward. If you drill into any of these options, just like with the label filters, do have the option to toggle those different condition ALS from right here within this dialog box and then all the way on the left, you can choose which value you're interested in. So in this case, I have to values columns here in my pivot, some of budget and gross revenue so I could imply this value title either one of them. So, for instance, some of budget equals 12 million. Well, obviously filter my list toe only titles with the budget exactly equal to 12 million, same idea as label filters as faras clearing them is concerned. I can clear them right from here in the column header and also drill in and clear them from within. The value filters options as well, so it's going clear that jump back in and practice one of the between options. So again, choose your metric budget or revenue. Let's say gross revenue is between one million and 10 million press. OK, there you go. You'll only see gross revenue for titles where revenue falls within that 1 to $10 million range. Now the last one. I want to spend a bit more time on eyes. The top 10 option, which is really cool. It actually sounds more simple than it really is. Once you drill into the top 10 dialog box, As you can see, you don't just have the top option. You can choose the bottom a swell, and instead of showing 10 items you can show five. You can show 20. You can show 100 really, whatever you want. One thing that's really cool about this is that you can show either the number of items based on the rank, or you can show the titles are items that fall within a certain percentile. So, for instance, right now we'll show top five items or titles by gross revenue and press. OK, I get a list of five titles that have generated the highest gross revenue total in our sample Avatar, Jurassic World's The Avengers, The Dark Knight and Titanic. But now, if we go back into that value filter option and say, Show me the top 5% instead of items now what I'll show are all of the movies that fall into the top fifth percentile in terms of gross revenue. So we see a larger list that includes some additional titles like E. T. Frozen Iron Man, etcetera. So whereas before we're looking at a specific number of titles five now we're looking at any titles that fall within a certain percentile. In this case, the fifth percentile. It's a really great tool for exploring your data based on value specific criteria. 25. PRO Tip: Enabling Multiple Filters: So I want to share with you guys a tip that I wish someone had taught me years and years ago, which is how to enable multiple filters in a pivot table. And this is something that's really easy to do once you know where to look. Because it's kind of buried in the pivot table options. So let me show you what I mean. Here, let's go ahead and clear our filter to give us a starting point. And let's say we want to drill down to only titles with a simple label. Filter begins with s so we're only looking at s titles Now, Now, let's say OK, I've got my ass titles among titles that begin with S what's filter down again to show on Lee the S titles with a gross revenue over $100 million so I could go in. When I opened up that sorting and filtering menu, I see a check box next the label filters line, which indicates that there is a label filter applied. If I drop into my value filters, she is greater than we can say. All right, gross revenue is greater than 100 million. Press OK see what happened here. It basically overrode the label filter and removed it. So now I'm showing all titles, no matter what letter they start with where the value of gross revenues greater than 100 million. If I drill down into my options again, you'll see that the check mark essentially just moved from label filters over to value filters. And this new filter that I applied now takes priority in order to prevent that from happening in order to allow my label filter to remain in place as I add this value filter on top of it, I can go into pivot table tools, options totals and filters, and simply check this box that says, allow multiple filters per field. And when I do this now, it can drill back into my options. We'll just kind of do this in the reverse order. I've still got my value filter in place for revenue greater than 100 million. So now let's add the label filter that we had originally, which is begins with s. And when we do this, it generates the exact list that we're looking for. We have titles that begin with s and among those titles that begin with S We're looking at Onley those which generated a revenue of over 100 million. And when we look at that menu again, now we see two check boxes, one which indicates that there's a label filter applied and a second, which indicates that there's a value filter applied. So an unbelievably helpful tool once you know where to look for him And let's go ahead and just clear these off. Next up, we're gonna talk about grouping data. 26. Grouping Data: another powerful pivot table tool is the ability to group data together, and what this lets you do is define custom collections or groups of values and analyze them as a bucket rather than individual items. So to show an example, let's start by on Lee showing movies with the words star. And here you can see that we have a number of films in the Star Trek, Siri's and a number of films in the Star Wars. Siri's So right Now, our data's at the title level, meaning that these revenue and budget numbers that we're looking at are specific to each individual title within those groups. Now, if we wanted to lump or group those together and analyze them at a broader level, what I can do is actually just select the field that I'm interested in. Let's start with these six individuals Star Wars titles, and I can either go into my pivot table tool analyzed tab where you can find the group pain or it can simply right click and choose group option. And when I do this, it's a little bit confusing at first. But essentially what Excel did was create a second generation of the title column called Title to which groups those selected titles into a new custom group called Group One. You'll notice that for all of the other titles in The pivot that I hadn't grouped, you see the same name for both the title column and the new title two column so I can select where it says Group one and give it a more meaningful name like Star Wars, for instance. And similarly, I can go up to my title two column header and change that to something like Group Title, which is a bit more intuitive. And even though I had Onley selected the Star Wars titles as my first grouping, I can continue the process now and apply the same logic to something like Star Trek. I'll just shift Click to grab all of these similar Star Trek titles, right Click and Group those, which creates a new group to containing all of my Star Trek titles. So go ahead. Same idea, renamed Group to Star Trek. And there you go. And what's great about this is that even though this new group title field is dependent on my original title column, I can pull that original title column out of Pivot and Onley. Analyze my new group titles. I've condensed this view toe only show a single line item for Star Trek and a single line item for Star Wars. So now these budget and revenue titles are inclusive of each title that falls within these groups. And then, if you decide that you no longer want to group these, it's a simple is right clicking the name choosing on group and it will blow it right back out into the title level data so I could do the same thing for Star Wars here. And there you go. So once I've done that, you'll see something interesting, which is that I just ungroomed the only two groups that we had created in a column title reverted back to our original, which is title as opposed to group title because we no longer have any group values in here . So really cool option toe aggregate your data and compare one group of values against another that was an example of had a group text based fields. Next up, we're gonna talk about how Excel deals with date grouping specifically 27. PRO TIP: Automatic Date Grouping : all right, I want to take a minute or two. Talk about date grouping specifically because it looks a little bit different from the exercise that we just walked through. And the reason for that is that in recent years, believe 2016 or 2013 Excel started to integrate some capabilities toe automatically, detect date fields and create groupings and hierarchies behind the scenes without any input from the user, which can certainly be helpful. But it's also a little bit confusing if you don't know what's going on. So let me show you an example. We're going to start by just clearing this title label, filter that we put in place, and I'm gonna drag title out of the pivot table. And what we're gonna do here is pulling the release date field. But before we do want to dive into that movie database raw data tab and just get a sense of exactly what that columns formatted like. So release date here and Colin being I've got data at the daily level. So April 15th 1935 January 30th 1969 and so on. So again, daily level release date data here in column B back in our pivot when I grabbed release State, pull it into my role labels. Something kind of weird happens here, which is now all a sudden I'm looking at data by year. There's a new column created for quarters, and you know, if I scroll down and drill into these values, seeing data organized down to the month level, but I don't even see the actual release date itself anymore. And what's going on here is that Excel automatically recognised that I'm looking at dates and it said, Hey, Chris, maybe you're gonna want to roll up the data by month or by quarter or by year, And that's why it created these new columns for me Now. I don't like that it does this by default, but once you get over that initial learning curve and see what's going on, it can actually be pretty helpful. So to help diagnose exactly what excels doing here, I can go into my pivot table tool analyzed tab and take a look at some of these group options. And the first thing I'll do with any of thes date field selected could be the month that quarter of the year going to go into this group field button here, and this tells me exactly what Excel just did. So it created additional groupings at the month, quarter and year level. And as you can see, Day isn't even selected. So I've lost the ability to look at release dates at the daily level. And if that's something that I want to do, all you need to do is click days again and press OK, and that adds 1/4 layer or fourth column all the way down to the actual date level. And I've got years, quarters, months and release dates. You might be saying, All right, I've got raw data at the daily level for a reason, and that's because I want to look at the data by day. I don't care about years. Quarters are months, and that's totally fine. There two approaches that you can take to get things back to the daily level that you're comfortable with. The first, which I'd recommend, is actually holding on to these new columns that excel created for you, even if you don't choose to use them right now. So if I don't care about years, quarters or months, what I can do is take that field and just drag it out either into the field list or into my filters. Could do the same with quarters and the same with months. So now I'm left with my release date, just like it exists in the raw data tab. But now I also have the ability to manipulate these filters. So if I only want to look at data for a given year or quarter or month, I now have the ability to do that without manually calculating or grouping. And he feels myself now. The other approach is to simply select one of the date fields that had been automatically grouped, and either go into my group pain in the analyzed options or right click to feel itself and just select on group. And when you do that, you can see that those year, quarter and month fields disappeared and my data reverts back to the original format in the raw data sheet, which is daily level data. So let's go ahead and undo because I do want those year, month and quarter fields accessible that will give me the flexibility to use them if I choose or continue to look at the data at the daily level. So there you go. Quick primer on how Excel deals with auto grouping dates. 28. Using Slicers & Timelines to Filter Data: now, one of the coolest options when it comes to filtering data with pivot tables is the ability to use things called slicers and timelines. And basically, slicers and timelines are just visual representations of filters, so they work exactly the same way as pulling a field and dragging it into the filter box in your field list. The only difference is that they add a nice little user friendly interface to help filter your data. Now the only difference between a slicer and a timeline is that slicers can be used for any field timelines air specifically designed for dates, so they have a bit more of a chronological layout. So let's jump into our pivot and try inserting some of these. Okay, so the first thing I'll do is actually pull release date out of the pivot, and why don't we pull country in? So now we're looking at data by country, and I'm just gonna apply a sort option here, sort of descending by revenues that we lead off with the USA than UK, Germany, France, Canada, etcetera. What if we want to see how this list of countries changes based on different genres? Specifically, so one way to do that would be to pull genre right here into the filter list and just kind of make adjustments here and say, Okay, among Westerns, really only two countries in the data set that produce Westerns. I'm going down to comedy's looks like the U. S. A. Is the leader there kind of similar order? So that's one option. The second option is to go into a pivot table tool, analyzed tab and insert genre as a slicer. Someone insert slicer here. I have access to any of the fields that I'm able to filter on, can choose genre from here and press OK. And as you can see, it dropped in this nice little kind of stand alone filter preset to comedy. Since that's what I had genre filter, too. And now that I have this slicer version, I can actually pull genre out because I don't need it to exist in my filter list as well. So now as I interact with this box, I'm changing the data based on the selections that I'm making. So now I'm only looking at adventure movies, Onley animation movies on Lee comedies and so on and so forth. So within the slicer, you have a few customization options. The first is to enable multi select. So if you want a user to be able to select more than one genre at a time, that setting lousy to do that, it's now. We're looking at a combination of comedies and documentaries, for example, and you may have noticed that a new menu is created called slicer tools, and within their we've got a few options. I won't cover every single one of these, but you have some really helpful settings here where you can change the name or display name of your header. You can change the default, sorting and ordering. You can choose to hide or show items that have been deleted from your source data. So a lot of good tools there, just like any other table, you can choose your styles so your colors and fills and borders. You've got alignment tools that will help you kind of snap thes two grids if you want to align multiple slices and timelines together to create a dashboard, and then one really useful option over here to the right is this columns option. So right now I've got one tall slicer, all of my genres listed out vertically. I can change that, too, and basically create a slicer that's two columns wide, and that's really just a matter of, you know, personal style or look and feel. It's really good option tohave. Then you can change height and width properties as well, just like you could edit any other shape and excel. So there you go. That's my slicer. Now let's go ahead and select one of the pivot table fields go into our tools again, and this time let's insert a timeline. Now, when I insert a timeline, the Onley field that populates is released dates. Since that's the only field in my data set, that's a date field, so I can select, release date and press OK, and it drops in another little visual interface where I can now filter down based on this date field so you could dry. You can select individual values and so on so forth. One thing I want to set right off the bet is this Drop down option here, so this determines at what level you want the user to be able to filter dates. In this case, it's defaulted to filtering by month. Let's say we want Onley filter by year. Now you can drag to create ranges. You can select individual years one by one. It just creates a really nice, really intuitive user friendly interface to help filter the data that you're viewing right here in your table. 29. Breaking Out Report Filter Pages : now the last thing I want to cover in this section is a tool called report filter pages, and this isn't a really well known tool, and it's not that commonly used. But in cases where you do need to use it, it can save you a ton of time. So essentially you would use the show report Filter pages option to create new tabs for each value that any given filter can take in the screenshots we're looking at here. We've got a filter for year that can take three values. 11 4012 or 2013. By using the show report filter pages and selecting that year, Filter Excel will automatically create a brand new tab with that filter set Teoh each of its possible values. So in this case, we'd end up with three new tabs one for the year, 2011 1 for the year, 2012 and one for the year 2013. So it saves the time and effort of manually changing those filters, replicating and copying the tab and then starting over again. So let's jump into our pivot and give this a shot. Okay, so here we go first things first, let's get rid of this slicer and timeline to remove them. I can simply clear the filter in the top, right? Select the edge of the shape and press delete can do the same thing here with the release date. Delete that one and now we're back to her unfiltered pivot table view with data at the country level. And so let's say, for example, I work for IMDb and my boss comes by and says, Hey, I need a list of all of the movie titles produced by each country as a separate report. So what I could do is maybe take title and pull it in its the second row label. And now I'm looking at titles by each country. But the problem is these aren't separate reports, so I might have to copy and paste these into different workbooks. Another option would be to drag country out into the filters field and kind of manually make my adjustments this way and say, OK, here, my USA titles copy this paste duplicated somewhere else, change USA to Thailand, your my Thailand titles and so on and so forth. As you can see in this case, we've got, you know, several dozen different countries and taking that approach would be extremely tedious. So what I can do is just unfiltered that option. And I'm gonna use this filter pages tool in excel, which a lot of people actually don't know about. And to access that you can go into your pivot table tools options on the left and just click this drill down arrow to the right. And instead of going into your options, you're gonna choose this second option called show Report filter pages. And when you select that, it will populate a list of any filters that you currently have in place on your pivot, which are the ones shown here and allow you to select any one of thes to break out individual Thames. So in this case, I care about creating separate reports or separate tabs one for each country, which would show everything that's shown here in the current pivot table view. So for each country, I'll show all the titles that budgets and the revenues. So really, all I need to do is press OK. It's gonna run through, automatically replicate all of those and create a whole bunch of new tabs, each of which is set to a different filter setting for my country filter. And they're gonna have separate reports showing the movie titles broken out by country. And keep in mind my original pivot table in my data was all preserved these air additional tabs or reports that were created. So if I change my mind and I don't need these tabs here or remove these tabs elsewhere, I need to get rid of them in this workbook. I can just select the 1st 1 arrow all the way over until I get back to my original pivot tab. Gonna select all of those country tabs that were created, right, click and delete. And now I'm right back to where I started. So there you go. Nice. A little shortcut using the report filter pages to automate a task that otherwise would be extremely tedious. 30. "Summarize Values By" Options: All right, Welcome to Section five, Calculated values and fields. Now, we're really starting to cruise, really? Starting to unlock some of the true analytical potential of pivot tables. We started to get there with sorting and filtering tools, which could be really valuable but calculated values and fields. This is where we're gonna be creating brand new metrics. We're gonna be customizing how are showing and calculating values in our tables, and we'll really start opening the door for some much more advanced and creative analysis. So let's start simple with summarizing values in every pivot table value field. There's a summarized values by option, and this determines how numbers should be treated when they're rolled up our aggregated. So let me give an example. So far in this course, you've been spending a lot of time focusing on budgets and revenues for movie titles, and those titles can be rolled up or aggregated at higher levels by genre or by country. And when we roll up those revenue numbers, essentially what we're doing is we're adding each of the individual line items, or each the individual titles that fall within that category and summarizing those revenue numbers to produce some grand total that's displayed in the pivot table. But here's the thing. You don't have to some those values up. You can use the summarized values by options to aggregate your data or display your data in different ways. So instead of the some of revenue across titles, you can show the count of observations. Or you can show the average revenue or the maximum revenue or minimum whatever field you're looking at. The point is that we do have different options when it comes to actually summarizing the values, so this will be a bit more clear. Wouldn't we actually take a look at some data? So let's jump back into a pivot table and take a look. All right, here we are back in her I m d B movie database pivot, just like we left it coming out of Chapter four. And for each of these two metrics, budget and revenue were summarizing the values based on a some be right click. Any value in the column you can roll down to summarize values by you could see that check mark next to the sum option, so both budget and revenue are currently being summarised using a some now because their data is at the title level and I'm looking at titles here. This is the most granular view that I can possibly get to. Instead of looking by title, let's pull that out and drop in genre as a role able instead. Now, when we see a number like this $67.5 billion in revenue essentially, that's summing the individual revenues from titles that are categorized within the action genre. But what if we didn't want to show the sum of revenue in this case? It makes sense. But for the sake of demonstration, let's play with some of these other options. If instead of some we chose count could ignore the dollar sign. But essentially, what this is doing is showing the count of rows or observations that fall into this bucket . So 900 means that there are 900 titles in our raw data categorized in the action genre. They're 1018 comedy titles, 362 adventure titles and so on and so forth. So that's an example of how something like Count actually could be a pretty useful way to summarize the data other options air pretty straightforward. You can take the average revenue, which, instead of summing the title level revenues for action films, it's taking the average of those revenues. And then you can also look at the maximum. This case, 760 million would be the single highest grossing title in the action genre. Bucket vice versa. You can look at the minimum 162. I don't know. I don't know what that movie is, but it apparently didn't do too well and then finally got some less intuitive or lesser known ones. You could take the product, which in this case, really doesn't make sense at all. Then, if you drill into more options, see some additional ones scrolling down you can count on Lee number cells. You can take standard deviations or variances and so on so forth. So I access this menu just by right clicking any value in the field. You can also get to that menu right down here in the field list and click this arrow to the right and going to value field settings. And here you go. I've got my summarized options right here, so let's set this back to some, since that is what makes sense in this particular case, and there we have it. So basic options for changing the way your summarizing your data as it gets rolled up into a pivot table. 31. PRO TIP: Avoiding the "Count Of" Trap : when it comes to summarising values using pivot tables. There's one thing that's extremely important to understand, and it's something that I call the count of trap. And if you've been using pivot tables for a reasonable amount of time, I'd be willing to bet that you've run into this issue whether or not you've realized it, and to show you what I mean. Let's go into our pivot and we'll drag gross revenue out of the field list and then select it and pull it back into values. Now, when we do that, you'll see that Excel defaulted to summarizing those revenue numbers as a some. So we're looking at the some of gross revenue, and that makes sense. In this case, revenue is a counting metric. It's a volumetric. So when we roll up revenue at the genre or country level, it makes sense to take those individual revenues by title and some them together to display a total. But then why? When we try to drag in a similar accounting or volume metric like total number of reviews, does it default to count of now? This drove me absolutely nuts for the longest time, and it's not because it's a tough problem to solve. All you do is right, click and changed. The sun problem is that if you don't catch this or you're not vigilant about looking out for it, your numbers will be way, way off when you're expecting them to be summed. So the golden question is, Why does this happen? And the answer has to do with blank and non numeric cells. So let's do a little demo here. Let's pull revenue and total reviews back out, and we'll go into our movie database and I'll just scroll up to the top. Now here's my gross revenue column. And keep in mind, this column was the one that was properly defaulting to the sum of mode. But what if I choose one specific value and delete the values? That's a blank cell? If we go back to her pivot, refresh the pivot and then drag gross revenue in now all the sudden it's defaulting to count off the exact same issue that we're seeing with Total Reviews column simply because there's one cell in the entire column that has a blank instead of a value. So in this case, a few ways to fix it. I could replace the blank with a zero. I could right click and change the summer ization mode to a some, but you've got to be vigilant about watching out for when that happens. Now let's just control Z a few times so that we replace this with its original value. I want to make sure that stays there. Let's follow the same logic to take a look at our Total Reviews column. So will help us understand or diagnosed the issue with this column as well. So I'm going to use a little shortcut, Select 02 Then we use the control arrow down shortcut and that jumps all the way down to the last contiguous cell containing data. So if there is a break in data like a blank cell, for instance, it will take me directly to it. If I continue down with control, Arrow takes me all the way the bottom, which means that this is the on Li Cel 0 22 76 with a blank value. So to fix that, all I need to do is replace it with a zero back to my pivot refresh and then dragged total reviews and now I'm getting a some exactly like I'd expect so not a tough fixed, but something that will absolutely drive you off the wall until you understand why it's really happening. And keep in mind that the same applies, you know, whether it's a blank cell or whether it's a text string like in a, um, whatever it might be. So bottom line, just make sure that you're watching out. Make sure that you're using the correct summer ization modes and that you don't have fields that you're accidentally summarizing with account instead of a some. 32. "Show Value As" Calculations: all right, Next up, we've got something called show values as, and this is one of the single most powerful and important capabilities that pivot tables have to offer. So take notes, pay attention. This is really important stuff. You'll find the show values as menu right beneath the summarized values by options that we just talked about. Basically what show values, as options do is they allow you to provide additional layers of calculations that change the way you view the numbers based on your pivot table view. So it's important to distinguish this from the summarized values options that we just talked about. So think of it this way. Summarize actually determines how the actual core numbers are calculated as their aggregated, so as some account on average men Max, by contrast, the show values as options allow you to look at those numbers and display those numbers through all sorts of different lenses. Toe add more insight. So in this case, here in the screen shot we're looking at, we've got a metric called order quantity. But instead of looking at the actual quantity or number of orders for each row, were viewing that metric as the percent of the column total, rather than just the whole numbers. So that's just one example. You've got tons of other choices here and calculate the difference from running totals. Ranks in next values, etcetera. So in this slide that lay out a few examples. And this is a really nice way to just demonstrate how the same metric in this case revenue can be displayed six different ways to reveal all sorts of different insights that you can't get with the wrong numbers alone. So to set the stage a bit, we're looking at ticket sales or ticket revenues by genre by year, over a four year period and our first revenue. Colin. There is kind of the default no calculation column, so that's just the total sum of revenue for each of those years and genres. But as we move to the right, we'll see five additional iterations of that revenue field, each with a different show values as calculation applied to it. So the second column is the percent of the total column, and that gives you a really great sense of composition and relative share by year and by genre. So at a glance, I can tell that action movies as a whole drove 58% of the total revenue over this four year period. I can also tell that action movies in 2012 specifically drove 14.74% of the revenue in the sample and then similar cut of the data, using a calculation called Percent of Parent, where genre is the parent. This puts a slightly different spin on it so that you can see the relative composition of revenue by year within each genre. So for adventure films, for instance, I can easily see that 2013 drove the largest share at just under 30% and 2011 was a relatively slow year on Lee, driving 14.51%. Then you've got other options, like the percent difference from which is a perfect way to look at, ah, year over year percent changes. Without doing any manual calculations on your own, you can look at running totals each year. You can add rank values, which will assign a ranking either large to smaller, small to large based on certain values. So I'm going to dive into a bunch of these different calculations with actual hands on demos. But for now, just keep in mind that the show values as options are incredibly valuable tools for unlocking hidden insights within your data. 33. Show Values As: % of Column/Row: the first show values as calculation that I want to talk through is one of the most powerful and also one of the most common, which is the percent of column or percent of Roe, depending on how you're viewing your data. So in our pivot, for those who have been following along, we're gonna go ahead and tweak this view a little bit and pull budget and reviews out. We're gonna drag in gross revenue, and here we go. We're gonna continue to view by genre. Let's just go into our design tools, an ad grand totals for rows and columns. So at this point, we're looking at the some of gross revenue, with no additional calculation broken down by genre, which may be exactly what I need. But let's say someone asks. Alright, what share of total revenues were driven by comedy films versus documentary films? I could certainly make those calculations by dividing the revenue numbers into the grand total. But that's an extra step, and it's manual, and I would love a way to automate that or to skip that manual step. So what I could do here is right click. The values go into show values as and select the percent of column total calculation. And when I do that, you'll see that the entire column totals up to 100% and each genre has been translated into a percentage, and this is a really great way to visualize or analyze things like composition or relative share across items or categories. So, for instance, what this tells me is that action films as a whole accounted for 36.46% of total gross revenue within my sample. Now it's important to keep in mind that you have to be aware of your pivot table coordinates as you're interpreting these numbers. And what I mean by that is that if we instead had certain filters applied here now all a sudden, if I'm interpreting this number, I can't say that drama films accounted for 42.1% of revenue as a whole. I'll have to say that in the country of Spain, among color film, specifically, drama films accounted for 42.1%. So just a quick word of warning. Make sure that you're being vigilant about understanding the context of your pivot table view, so that's percent of column. Now if we were to grab that genre breakout from Row labels moving into column labels Now all the sudden, each genre is on its own column, so the column totals each become 100% which isn't very useful. And that's where the percent of Roe total calculation comes in. So I can just right click show values as percent of Roe total, and now this row as a whole totals up to 100% and each genre displays its relative share as a percentage. So another way to visualize this is to take a second field like country pull country into the role labels. And now we have kind of this matrix or this two dimensional pivot table view. And right now we're still looking at these values as percent of row. So each individual row here is totaling up to 100%. But scrolling down the columns do not add up to 100%. So when I right click any value, show them back as the column total. Now my columns add up to 100 but my rose do not So really, the two calculations are doing very, very similar things The only difference is that you'll use one versus the other, depending on exactly how your datas organized or structured. So there go percent of column total percent of Roe total really common, really powerful show values as tools. 34. Show Values As: % of Parent : next up, let's talk about the show values as calculation called percent of parent, and to demonstrate what that means going to adjust our view a bit. And instead of showing genre as column labels, I'm gonna nest it under country as secondary role labels and will continue to show gross revenue. Let's just double check that we're currently showing us percent call in total. So this might be the view that you're looking for, Assuming you're looking for insight at the country and genre level, what this allows you to do is say OK for the U. S. A. As a whole, revenue accounted for 89.4% of the grand total, which again, all the way, the bottom equates to 100%. Australia only accounted for 0.81%. And then, if you drill a level deeper biography movies produced in the USA accounted for 3.39% of the total. That's important to note that if we add the's genre level sub totals up, they equate to 89.4% which is the U. S. A. Total not 100%. But what if we wanted each country to take a value of 100% so that we can split out the relative share of genres specific to each country total. To do that, we need to use a calculation known as percent of parent. So if we right click and go into our show values as menus, we've got a few options here. Percent of parent row percent of parent column or percent of parent total. I like to use this third option because it allows me to determine exactly what I want my parent to be. So if you select percent of parent total, it will ask you what your base field is. In other words, which feel do you want to set as your parent or 100%? And in this case, you only have two options genre or country since we're looking at data at the genre level setting genre as our base field or parent doesn't make a lot of sense because every role just equate to 100%. If we go back in and set our base field or a parent to country and press OK now each country or each parent is set to 100% and Now we're able to understand the composition of genres within each of those countries. So now I have to change the way I'm interpreting these numbers a bit and say that comedy films in the U. S. Accounted for 21.54% of the U. S. Revenue, not total revenue as a whole. So this gives you a bit more insight, especially as you get down to the lower volume or lower revenue countries like the UK, for instance, Now you can much more clearly see the distribution or composition of revenues at the genre level because we've set country as our parent. So there you go. That's a quick primer on using percent of parent to change the way your viewing these percentages. 35. Show Values As: Difference From: all right, Next on our list, we've got two more show values asked calculations, difference from and percent difference from and these air really, really helpful tools, especially when you start getting into time. Siri's trending so analyzing things like day over day or month over month or year over year , trending these difference from calculations could be unbelievably helpful. So let's go ahead and tweak our view a bit. Instead of showing country and genre, I'm gonna pull country out. And let's drag years in a secondary role labels. And you'll notice here that my gross revenue calculations, which are still set 2% of parent total, are giving the N A values because my base field was sent to country and country is no longer in this view again, a good reminder that the show values as calculations are tied to the specific view that you're looking at. So I can just cancel that. And for now, why don't we just revert back to no calculation so that we're looking at basic revenue totals, and now I've got a ton of data here all sorts of years. I really only want to look at a small window so I'm gonna de select them all. Go down and let's just look at some more recent data from 2012 2015 and press. OK, so now I'm looking at a four year sample broken down by genre with the some of gross revenue totals, and what I'll do is just format these cells as a currency. I'm gonna leave this column in place just as my baseline in my reference point and pull in a second iteration. And this column column d will be the one that I apply my percent difference in my difference from calculations, too. So, just like with the percent of column and percent of Roe calculations, these air calculations that I could easily make myself as an analyst, it's just a manual step that I don't have to take, and I would prefer to automate. So when the question comes up, how has action movie revenue trended year over year? Or what was the percent drop in biography revenue between 2014 and 2015? I obviously have all the information I need alone and column C to create those calculations and to answer those questions. But the show values as calculation will allow me to do it on the fly with no manual step at all. So what I'm gonna do here is right click show values as the difference from and when you select difference from this dialog box opens up asking you to select a base field and a base item out the base field is the basis by which you're comparing changes. And since I'm looking for year over year trending, my base field will be year now for base item. I have a few options here. The first option is to select one specific fixed year like 2012. For instance. If I do that and press OK and again, we'll format these as currencies. Now, what this is showing me is revenue for each of these years 2013 14 and 15 each, compared to 2012. So this number here tells me that action movie revenue in 2015 was about $140 million less than it was in 2012. So that might be what I'm looking for. But what I actually find more valuable and more generally useful is, rather than choosing a fixed year scrolling up to the top and selecting this previous option. Now when I press okay, this is giving me revenue changes year over year over year. So now this number is telling me that action movie revenue dropped $1.3 billion between 2014 and 2015. So typically a better way to show trending using this type of calculation against the previous year. And then finally, I'm gonna pull in 1/3 instance of gross revenue and this one I'm going to show the values as the percent difference from again. My base field is years, and my base item is previous. Press okay automatically formats to the percent. And as you can see, what it's doing is translating these numbers and Colin D into percentages. So it's essentially just creating a different lens through which I can explain and communicate these year over year trends. So 2014 action movie revenues up 51.7% year over year, and there you have it difference from percent difference from really valuable tools. When it comes to time. Serious analysis 36. Show Values As: Running Total : all right, so we talked about how difference from and percent difference from could be a great way to analyze time series data. One thing that I want to call out before we move on is the fact that the difference from calculations use what's called positional references, meaning that these calculations are specifically based on the structure and order of these relative fields, genre and years. And because they use positional references, it does limit some of the sorting options that I otherwise would have had. So for genre. If I wanted to sort these descending by gross revenue, I'm no longer able to do that because I'm using custom calculations in this case, my difference from and percent difference from calculations that use positional references . So when you run into this error, it may limit you from doing some of the sorting options that you could have done otherwise . So you may need to do any sorting that you need to before applying or adding these custom calculations. So moving on the next show values as calculation that I want to show is another one that's very, very good at analyzing Time series data, and that's called running total and percent running total, so it operates very similar to the difference from calculations. In fact, I can just right click column D. And instead of showing these as a difference from, I'm gonna show these as a running total in where my base field is years. And when I press okay, you'll see that the first year in the sample. In this case, 2012 returns the total revenue for that one year and then the next value 6.897 billion is the total of the 2012 revenue plus 2013 and then each line is essentially an iteration of that calculation. So 2014 will add on another 4.857 billion and so on and so forth so that this bottom total in my running total calculation will always represent the sum of all of the years in my pivot table view. So in this case, the some of these four years looking down here in the bottom right is 15.309 billion, which matches right here, 15.39 billion and then just like difference from running, Total also has a percent variation, so I can change this 2% running total in base field, equal two years press. Okay. And there you go. So in each case, it rolls up to 100% for the final year in the sample. And it clearly shows the growth in revenue totals year over year over year. So there you go running total percent running. Total Another great tool to analyze time series data. 37. Show Values As: Rank : the next show values as calculation that I want to cover is a pretty simple one called Rank . So in a pivot table view, I don't need both of these columns D and E anymore. So I'm gonna pull out the third instance of gross revenue and let's go ahead and edit column D to show these values as a rank rather than the running total. So I'm gonna go in my show values as option go all the way down to my rank options. Now it can choose either smallest to largest or largest to smallest. If I choose largest to smallest, that means that the highest revenue total within a given parent field will return a one. If I choose smallest to largest, the smallest value within that range will return in one. So in this case, let's go with largest to smallest, and the base field will determine kind of the range of data that we care about. So if we choose years here, the output will essentially be ranking from 1 to 4 within each of these genres. And what that will tell me is which of the four years drove the most revenue denoted with the rank of one, and which of these years drove the least revenue to noted by a four. So let's press OK and see what that looks like and there have it. So within the action genre, 2014 generates value a rank of one because it generated the most revenue, and 2013 produces a rank of four since it drove the lowest volume of revenue. And again, that's within the action genre specifically for only these four years. And you could make the same exact comparisons diving into any of these genres by analyzing the rank numbers in the same way. So what if we right click and go back into our dialogue box? And instead of choosing a base field of years, What if we chose genre? Now we press okay. The year over year rankings are totally ignored. We'll just see the same ranking for each year. And instead, this is the number that we care about at the parent level, which in this case, is genre. So the one here denotes that action movies as a whole for this four year sample drove the number one ranked volume of revenue, or the highest total of revenue adventure was ranked number two animation ranked number seven All the way down. And you can see the 13th ranked category is thriller. The 12th ranked category is scifi and so on and so forth, so different way kind of producing insight, using different variations of that rank calculation. 38. Show Values As: Index : all right now, the last show values as calculation that I Want to share with You Guys is one called Index , and this is by far the most mysterious calculation. It's a bit more complicated than the other calculations, and as a result, a lot of people never even use it. So allow me to clarify exactly what the index calculation is used for and how you might be able to apply it in different ways. So in our pivot table, let's go ahead and pull out this rank calculation that we had created in the last lecture. And instead of looking at data by year, pull years out into the Filters column, and I actually want to look at a five year sample of data so we select 2011 as well press OK, and now we want to look at a two dimensional view of this data. So instead of just by genre, I want to also compare by country as columns. But instead of all of these columns where you have a lot of really low volume countries mixed in here, it's just kind of adding noise, so apply of value filter to the country. Columns use the top 10 option toe limit this down to just the five items or countries in terms of gross revenue. So there we have it. We've got a nice compact view here, and now we can do is test showing these values as an index, which is down here all the way at the bottom. And what this does is it converts it into a number. And I'm actually gonna format these with just two decimal places, so you can compare them a bit more easily, and you'll notice that each column equates to an index of one and each entire row also equates doing index of one and the best way to actually interpret these results. It's toe look row by row and compare the index values across countries for each particular genre. So, for example, revenues from action movies produce an index value of 0.98 for the U. S. 1.3 for the U. K. Zero for New Zealand, 196 for France and 165 for Australia. And comparing those numbers, we see that France generates the highest value, followed by Australia than the UK, then the U. S. A. and what the's index values tell us is which genres carry the most weight or importance in terms of the contribution to each country's revenue as a whole. So even though the United States drives a much larger volume of action movie revenue, if we were to increase action movie sales by some flat percentage or, for instance, bump up the action movie ticket prices, the U. S film industry as a whole would see a smaller percent lift in revenue than France, Australia or the UK. And in fact, if we change the show values as calculations, 2% of column total would kind of see a similar story here. So action movies account for the largest share of revenue in the French market, followed by Australia UK US, which follows the same pattern as their index values. So it's press undo there. But that's another way to kind of understand the relative weight that certain genres carry within each of these countries. Now I want to take this a step further toe, actually make this really and give you some context to really nail down what this means so we can actually use calculations and real numbers to see this effect taking place. Now, bear with me. This might get a little bit complicated, a little bit messy. If you do want to follow along, feel free. If not, that's cool too. But basically what? I'm gonna do it manually create a new revenue column that captures a hypothetical increase in action movie revenue specifically. And then we're gonna look at a pre post comparison at the country level. So in my raw data, gonna scroll over and add a new column here to the right of gross revenue. It's called test revenue, and I know we're not covering formulas in this course, but I'm gonna do is use a conditional statement here and say, If my genre field D two equals action, then I want to perform some calculation, which in this case is gross revenue times will say 1.2 to denote a 20% lift comma over to the value of false clause. So if my genre is not equal to action, I just want to return Cute, too, which is the original gross revenue total. So close that parenthesis off, apply this formula down and as you scroll down, you should see cases where action movie titles are applying the 20% lift and non action movie titles are not. So if we go back into our pivot and refresh, we'll see this new test revenue Colin become available. And now, before we do anything else but I want to do is remove the index calculation here and just show the actual values themselves. I'm gonna grab this data, copy it at a new tab and just paste it right here as a hard coded reference point. So this is no longer a pivot, these air, actual hard coded values that I'm just gonna use to reference once we drop in the new revenue calculation that we just created. So now on my pivot, instead of looking at revenue, I'm gonna pull out revenue and show the sum of test revenue, and we can go ahead and format that as a currency, no decimal places. And now all I really care about is this grand total line. We can filter our country to get back to where we were before with just the top five items , and we end up with the same list. US, UK, New Zealand, France, Australia. Now, if we grabbed these five new totals, which again account for that hypothetical 20% action film lift that we just created. We can copy that, paste it right here beneath our old totals and drop in a percentage change calculation to see how it moved so equals. B 17 minus B 16 and parentheses divided by B 16 conform at that as a percentage, if a decimal point center it. And if I just drag this calculation over now, we can see the percent change as a result of the action movie revenue adjustment that we applied. And just like the index values predicted, France sees the largest increase to its overall countrywide revenue a 15.8% followed by Australia at 13.3 than the UK at 8.3 and finally the U. S. A. At 7.9. So that's the exact same trend that are index values predicted. So that's a pretty cool way to see this index value in a bit more of a tangible sense and actually see it in action so that I don't want to confuse things. So I'm gonna delete this sheet that I just created, and I'm gonna delete this column that we created and just kind of get back to our starting point, refresh the pivot again and pulled just gross revenue back in here. So obviously this example is a little bit abstract. But imagine that instead of looking at movie ticket sales by genre and country, we were actually analyzing revenues by product, category and sales territories, which in that case, this type of index analysis might be really helpful when it comes to things like determining the impact of price changes for certain products or modeling out future sales region by region. So anyway, that's your intro to the show values as index calculation hope it helps. 39. Inserting Calculated Fields: Another really important capability of pivot tables is the option you insert. Custom calculated fields and calculated fields basically just allow you to create brand new measures based on any existing numerical fields. So in this case, gone into my analyzed tab into the fields, items and sets dropped down, selected calculated field. And then from there you have this dialog box that allows you to give your new field any name you choose and then define a calculation based on any existing fields. So you see your field list right here within the dialog box, and you can simply double click items to pull them into the formula. So in this case, I've added a new calculated field called percent students. That's equal to the student population divided by the total population. Now a very important pro tip on this slide. Don't calculate your rate metrics. Things like click through rate cost per click sales per day, whatever it might be. Don't make those calculations in your raw data sheet. Use calculated fields to do so. This is exactly what calculated fields are designed to do, and it ensures that those fields are calculated properly, no matter how your data is rolled up for sliced and diced. So let's hop over the pivot and actually practice building some of these. Alright, So first things first. For those of you who have been following along, just gonna change the view that we're looking at here a bit. We no longer need countries. I'm gonna pull that out. And instead of analyzing the data by genre, I'm gonna drop in title as my role labels instead. And in addition to revenue, what's pulling budgets Now? We're looking at some of budget, some of gross revenue. We can keep this year filter just looking at a five year sample right now and then. Last but not least, let's just format both of these fields as currency with no decimal places. And there you have it. So now my task is to create some new calculated fields based on these budget and revenue numbers. And to do that, I can go into my pivot table tools, analyze tab, drop into this fields, items and sets menu, and choose the first option calculated field. Now this launches that calculated field dialogue box. And here's where I can add a name to my new metric and define the formula itself. And looking here, you can see all of my pivot table fields listed out right here. So essentially, I can build a formula to create a new field built upon any of these new miracle metrics currently included in my data set. So the first field that I want to create is a metric called profit, and the formula here will be pretty simple. It will just be the gross revenue, and I can double click to populate it in my formula bar, minus the budget and by default, any fields that are included in a calculated field formula will use the default summer ization mode of a some. So this is the sum of gross revenue minus the sum of budget. Even if I'm looking at budget as a count or an average, or men or Max as far as calculated fields are concerned, these will always treat the fields as a son. So I'm gonna talk a little bit more about the implications of that note in one of the upcoming lectures, but important to just keep that in mind. So in this case, super simple new field called profit profit equals revenue minus budget press. OK, and there you go. It populates right here in the pivot shows up in the field list as a new field, just like any other rock column that came from my raw data. And as you can see, it's populating properly. So 10 days in the madhouse had a budget of 12 million somehow only generated $14,616 apparently didn't do very well, so the profit is negative, just under 12 million. And then now that I have this calculated field, I can use it and manipulate it and sort and filter on it just like any other field. So I can sort my titles descending by the sum of profit now. And this adds a whole different layer of insight that they otherwise couldn't have easily accessed. And this tells me that Jurassic World was the most profitable film over this five year period, generating a profit of $502 million. That's a lot of money, followed by the Avengers Hunger games, despicable me and so on and so forth. So it's important to call out that even though profit is a calculated field that's built on budget and revenue. I don't need to have the budget in revenue columns here in the pivot for this calculated field to operate. In fact, if I drop budget and revenue out, this is still gonna function exactly as we'd expected to. So let's go with a second example and dive back into our calculated field options. And why don't we create another one called R A Y or return on investment? And it's gonna be kind of a similar calculation. Except this time I want to take the ratio of gross revenue divided by budget. So the ratio of revenue to budget press OK and this one off format as a percentage with no decimal places. And what this R O, I feel now tells me, is for every dollar that went into the budget for a film, how much revenue was returned. So in this case, there's a 4.35 to 1 return on the budget for Jurassic World versus a 2.83 or 283% return for the Avengers. So just another way to create a new calculation to provide a different lens or different layer of insight to this data set because the sum of profit tells one story in terms of the volume of profit, whereas R. A Y paints a slightly different profitability picture in terms of the ratio. So now if we sort the titles descending by our ally, we get a completely different story. So in terms of the percent return, the gallows is actually our strongest performer by far. There you go quick primer on using calculated fields toe add brand new metrics and insights to your pivot tables. 40. Calculations in Pivots vs. Raw Data : all right, so in the last lecture is shared with the guys pro tip that basically said, Never calculate rates or rate metrics in your raw data itself and to instead make those calculations using pivot table calculated fields. Now it's a bit of a nuanced point, but it's an important one, and I think it's worth digging into a little bit more. So let me give you guys a demonstration of why this is so important and what the implications are of taking each approach. So in this case, we already calculated a field called R A Y, which is calculated as the revenue divided by the budget. Now that's a good example of a rate metric. So had I instead gone into my raw data and inserted a brand new column here and insert it right after Q and call it R a y Rock. I could just calculate that right in here as Colin Q. Divided by s just budget and apply that down. I don't want to mess up the formatting so I can go into the auto fill options fill without formatting, and I could just turn that into percentage, really just to draw attention of the fact that this one column column are is a calculated rate, whereas all of my other columns are counting or Volumetrics. So with that, it can jump back to my pivot, going to pivot table tools hit, refresh, scroll down. And I could see that Newfield R a y raw and when I dragged that in the sum of our oi rock doesn't really make any sense because you really don't want to add up percentages. So what we're gonna do is change this to the average and press OK? And then when we form at this as a percentage with no decimal points, see that we get the exact same values that we got using the calculated field version of our ally. So you might be saying, What's the point? This is great. I'm getting the same answer. Why can't I take either approach? And the answer lies in how we're viewing the data. So right now we're looking at this data at the title level, which, if you remember, our raw data is at the title level. So this is the most granular view we can get. There's no grouping and no aggregation happening here, so that there's really only one number in the raw data that's populating for each title, since each title only populates a single row. So in this case, it really doesn't matter if we view this as a some and average men or Max, it's going to give us the same data because the max of one number is equal to the sum of that one number is equal to the minimum of that one number and so on and so forth. So if you're looking at data with no aggregation than in that one particular case, it doesn't matter how you calculate the values. The problem arises when you start grouping and rolling up and slicing and dicing your data . So instead of title, we can pull that out and drop in something like Country, for instance. Now all the sudden you start to see these differences. 113% vs 99 1 44 versus 1 72 And the reason why we're seeing these differences is that the average of our ally Raw is just taking a straight average of any our ally numbers that fall into this bucket, whereas are calculated R. A. Y is recalculating based on the budget and revenue numbers generated by any view that we're creating this pivot table. So it's properly waiting those values and returning the accurate measure of our ally. So you'll notice that in some cases, typically countries with a low number of titles in the raw data we get an exact match like Indonesia and Iran. But others that have more data points in our sample, like the UK and USA, are showing much more significant discrepancies. So this same logic applies no matter what kind of calculation you're making. The key point is that your raw data should Onley contain those counting or volumetrics, and any sort of rate calculations should be done right here in your pivot table using those calculated field options. So with that, let's go ahead and just delete this column. I don't want to confuse things. We'll go back into our pivot, pull this out and just do a quick refresh. Now we're back, kind of where we started with. So hopefully that helps shed some light on why that pro tip is so important and why calculated fields are so important when it comes to data accuracy and data quality 41. PRO TIP: Calculating Using Counts: all right. Time for another pro tip. This one's about using calculated fields with counts. And if you recall in the intro lecture about calculated fields, I mentioned that any metric that's included in a calculated field calculation will be evaluated as a some by default. But there's actually a way around that. I'm gonna show you how it works. So first things first. Let's just or pivot here instead of data by country. It's pulling data by rating and on Lee filter down to G PG, PG 13 and R and there you go. We can pull out profit and can pull out our a y. Those were the calculated field that we had created. Keep in mind that they are still here available for us. If we choose to use them later on, they don't disappear, and we're gonna look at gross revenue. So looking at this view here still have the five year timeframe selected, and we're looking at total revenues broken down by ratings. So, looking at this view, you may be tempted to say that PG 13 movies performed better than any other rating, and that's true. If you're defining performance in terms of the total volume of gross revenue. But as an analyst, I would argue that it's not really an Apple. Snapple's comparison, and the more meaningful measure of performance might be the gross revenue per title or per film. Because we know that fewer films were released that are rated G or PG compared to PG 13 and R and in fact, we can see that simply by pulling title into our pivot and comparing the number of titles. Another way to do that quickly. It was just to change the summer ization mode from some to count, and that essentially says that there are 10 instances or 10 data points of gross revenue that fall into the G bucket. And since our data is at the title level, that means that they're 10 titles assigned to that rating and then moving our way down, we can see there 104 PG titles, 305 PG 13 and 320 are titles. So when you look at it simply as a count of titles becomes clear that it's really not an even playing field, and then it doesn't make sense to compare the performance of PG 13 are our ratings as a whole against G or PG. So if we pull in another instance of gross revenue, one thing that we can do in this case it's simply changed the summer ization to an average and format. It is currency, and what this will do is it will give us the number that we're looking for. So this tells us the total revenue divided by the number of titles. Since again our data is at the title level. We don't care about waiting the revenue differently for any certain titles. In this case, it's a simple is that we can now see that G movies, even though they drove the lowest volume of revenue, actually had the highest revenue per title of almost 110 million. Now that approach works fine in this case because it's a relatively simple case. But what if you wanted to approach the same question using a calculated field? If I go into pivot table tools, fields items and sets calculated field and create a new field called revenue per title, how could I go about creating a formula to capture this? The first approach might be gross revenue divided by title. But the problem with this is that title is a text field. It's a dimension, not a metric. So dividing by a text field doesn't really make any sense. And if I try to do that, so we'll give me this div zero error. So let's go back in. Calculated field will drill down to that revenue per title metric and really, what we want here isn't gross revenue divided by title? It's gross revenue divided by the count of titles. Now, in theory, this sounds right, and on paper, it's exactly what I want. But when I press OK, it's returning values. But they're not the correct values. It's actually just returning the sum of revenue. It's not dividing them by the count of titles, which should return these values here in column D. And the problem lies in the fact that any metric included in one of these calculated field formulas will always evaluate using a some no matter what. Even if I'm showing gross revenue in my pivot table view as an average as a men as a Max as account, it doesn't matter as part of a calculated field, and in the context of this dialog box. It will always evaluate to a some. So even though I tried to force it to take account, that's just not doable in this scenario. But there's good news. We do have a way to get around this, and the approach we take will actually give us some tools that we can use to answer some much more sophisticated, complicated questions moving forward, which will practice in the last section of this course. When we dig into some case studies, I'm gonna go ahead and close this box. And here's the approach. I'm gonna actually go into my raw data itself and add a new column, and I'm gonna name this column number of titles and I'm naming it number of titles because I know my data is at the title level. If my data was at the review level, I would label this column number of reviews. If my data was at the date level, I would call it number of days. In this case title level. I'm creating a new column called Number of Titles and this column Will Onley take a value of one in every single row, so double click to apply it down gonna fill that without formatting. And now the Onley value in the entire column is one. So if we jump back to our pivot, go into our tools and refresh, I will see this new field you just created called number of titles. And this is the field that we can now use in our calculated field. So if we go back in, we open up our last version of revenue per title. Now step back and think about this for a 2nd 2nd half of this equation. We're trying to return the count of title or, in other words, the number of rows in the actual data set. Now the count of the title is equal to the sum of that new metric that I just created, called number of titles. And because calculated fields can only evaluate metrics as a some just dropping in. This number of titles feel that I just created will do the tricks and I'm looking at the some of gross revenue divided by the sum of number of titles, and we press OK, there you go. Let's format as currency boom. We get the exact values that we had taking our average approach now again. Obviously, the summary ization approach that we took in column D in this particular case is the easier answer. But I wanted to take this opportunity to introduce this concept of using account column as a means of enabling some of the more complicated, calculated field problems that we're gonna address later in the course. 42. PRO TIP: Calculating Using Counts (part 2) : Okay, so now that I've introduced this concept of using count columns for calculated fields want to give you one more example? That's a little bit more tangible in the case of this I. M D B movie data. So show you how we could calculate something like revenue per title using that count column , that number of titles column, but that it wasn't particularly useful in this case because we could just take the gross revenue field and summarize it by an average to give us the same answer. I want to show you an example of a case where you can't simply change to an average. You have to use that count column, So I'm gonna pull rating out so that we're looking at the data at the title level. You can see that the count is one. In every case, since each title represents one row in Iran data and I want to pull in two more fields here , pulling the cast Facebook likes as a son and the movie Facebook likes as a son. And now let's say that I want to create a new calculated field that sums these two fields together. So easy is going into my fields, items and sets, creating a new calculated field called Total. Facebook likes in the formula really a simple as cast Facebook likes, plus the movie Facebook likes as a whole. And there you go. It's format that as a number with a thousands separator and no decimal points. And there you go. So I've got total Facebook likes. Right now we're at the title level, so this view makes perfect sense. And I could sort or filter this. However I want based on this new, total Facebook like column. And it's a simple us that now where it gets a little bit more complicated is that if instead of title level, you're looking at this new calculated field at a higher level like rating, for instance, now you can see these total Facebook likes Buck. It'd by G, PG, PG 13 and R Films. And if someone asked you hey, which rating or which type of film tends to drive the most Facebook activity in terms of likes, it would be tempting to say PG 13 and R, but the problem is that this calculated field is still purely volume based, So it's true that PG 13 films did generate the most total Facebook likes across cast and movie likes. But we also know from The Count column that PG 13 and R movies are much more prevalent in the sample than G or PG. So what we need to do is account for that and factor that count in so that we can translate this field into something that's a bit more apples to apples. So something like Facebook likes per title or average total. Facebook likes now, where us Before with revenue, we could just change the summer ization mode because this is a calculated field. I don't have that option. So if I did have the option, it would be a simple is just changing toe average. But because this is a calculation and calculations need to use sums, I can't do it. So I've got to go back in my fields items and sets and create a new calculated field called Facebook likes her film or per title or per movie or whatever you choose to use. And this will be defined simply as total Facebook likes that I defined. Divided by that counting column, that number of titles column and that will do the exact same thing as summarizing this column as an average, just using the number of titles, some to do so so press OK and my new field pops up, and this now gives me a much more accurate comparison point of engagements per film. And we actually do see that the strongest performing does tend to be PG 13 followed by our , which just follow the volume trend. But that's kind of more of a coincidence than anything else. So that's an example of when you need to use a column like this. Count column when you can't just change the summer ization mode to an average so that lets go ahead and just drag these fields back out just to get to where we started with and I'm gonna pull title in and there we go. So that's another example of using account column as part of a calculated field 43. Inserting a Calculated Item (not recommended!) : so in the pivot table, analyze options. You may have noticed that in addition to calculated fields, there is also an option for calculated items. Now. The difference between the two is that calculated fields are built on numerical variables, while calculated items are built on non numerical dimensions or categories. So, in a nutshell, calculated items allow you to create new dimensions or categories using formulas built on existing dimensions or categories. So, for instance, if you have movie ratings like G, PG, PG 13 and R, and you want to create a new category called Kids, which is equal to all of the G ratings combined the PG rating data. You can do that using calculated items now. Pro tip. Big, bold red text here. Don't use calculated items unless you absolutely need to. I'm gonna be honest. I am not a fan of calculated items at all. They're finicky. They're dangerous. In many cases, they're not the most efficient way at to accomplish simple tasks. 99.9% of the time, you're better off simply using the grouping tools that we've already talked about or adding new categories or columns within your source data itself when you're trying to answer questions like this. Honestly, the only reason I'm even including this is just for the sake of being comprehensive and letting you know that this option does exist. But please, please be cautious when you do use thes, I'm so let me show you an example and kind of walk through why I am not a fan of using calculated items. So here in our pivot, we're gonna follow the same demo that I just showed you in the screen shot on the slide. We're gonna take the movie ratings and we're gonna try to group the G and PG data together into a new calculated item called Kids. So to do that, I need to get rid of some of these fields. I only need one instance of revenue here. Change the summer ization to a son and format it as currency. I don't need title level data so I can pull title back out, and that should just about give me my starting point. So I've got my group's revenue rolled up. Buy ratings with G PG, PG 13 and R. Now, to insert a calculated field, you need to select one of the values within a non numerical dimension. So either your role labels or column labels. In this case, my ratings are my role labels. And once you do that, you go into pivot table tools, fields, items and sets, and this calculated item option will be available to you. Now here's the thing. When I press it, I get this error, which says the report, feel this group. You can't add a calculated item to group field blah blah, blah on group button. Okay, so this is the first reason why I don't like calculated items. They're totally buggy. They only work about half the time that I want them to. And it's telling me that this rating field is grouped, but realistically, it's not grouped at all. It's filtered, but it's not grouped. The problem that it has with my pivot table is actually with the release date. Eso If I just pull release date in and rating out, and I ungroomed that release date field and pull it back out of the pivot, we can go ahead and drop rating back in. And now keep in mind, we're looking at all dates, not just the years that air filter on before. And now if I go into pivot table tools, fields, items and sets. Now I can access these calculated item options so a little bit annoying, especially because the date grouping really was unrelated to my ratings. So I should have been able to create a calculated item on this field, but for some reason excelled in like that. So it is what it is. Here we are in the calculated item dialog box like we talked about. I'm gonna name this item kids, and you can see that it selected the rating field and listed out the items contained within that field. So the kids rating is going to equal rating of G and note how I could just double click, just like it did with my calculated field formulas, plus the rating of PG. So simple is that G plus PG equals kids and press OK, so it's actually inserted a new row or line item for kids. But do you see why this is kind of a dangerous thing to do? That kids total of 48.273 billion is correct. It does equal the revenue from G plus PG combined but the fact that it added it as a new role able means that now I'm double counting both of these revenue totals, and it's producing a grand total number that's completely inaccurate. So this is fine if you're extremely cautious about it, and you recognize that this line item is not independent from the line items above it. But honestly, you're just asking for trouble as soon as you start mixing in calculated items with non calculated items. So like we talked about what I'd actually recommend doing here is, let's go ahead and go back into our calculated items and we can select this kid's item. Let's delete it. Press OK now our grand total returns to normal 183.8 billion, and we can accomplish the exact same thing that we just did using grouping. So I can just select G and PG choose group and give that group a name called Kids and then the design options. If we want to show sub totals, There you go. There's your kids total, which is calculated as G plus PG, and there's PG 13 and R and I basically accomplished the exact same thing without messing with my actual raw data or skewing my grand totals. So there we have it. That's kind of the ah quick case study on calculated items and why I hate them. But that said, I'm sure there are some more valuable applications. Just word of warning. Use them at your own risk. 44. The Solve Order & List Formulas Tools : all right. So as much as I'm not a fan of calculated items, there are two more features that I want to walk you through in Casey do choose to use them and those features in the analyzed tab under the fields, items and sets menu, and they're called solve order and list formulas. So to demonstrate, those actually need to recreate some calculated items. So first things first, let's ungroomed this rating feel that we had created. So now we just have our original list of four ratings, and I'm gonna go ahead and create that same calculated item that we did in the last lecture called Kids and Kids Will equal G Ratings plus PG press. Okay, let's also go into the design options and turn off our grand totals. And now what I want to do is actually pull color and black and white into the columns field . And it looks like, actually of three values. Ah, one of which is this B and W version, which doesn't seem to have any data. So I can just filter that one out and only include black and white and color. And now, with one of these two column headers selected. I can go back into my fields items and sets and create a second calculated item. In this case, I'm gonna call it percent color. And the formula here will be color divided by the sum of black and white and color. So essentially, this will return the percent of total revenue driven by color films out of the total so color divided by black and white plus color. Okay, and there it is now. One other minor annoyance of working with calculated items is that you lose a bit of flexibility in terms of self formatting, So this is a percentage that I just created. But if I right click and choose number format as a percentage, you'll see that it also converts thes revenue totals two percentages as well, which really doesn't make any sense. So I can undo that. And I can get around that by forcing it into percentage, selecting the entire column D and from the home menu simply choosing percentage there. So there you have it. This is our view that now contains two calculated items. This row here is defined by the kids calculation, which is G plus PG, and this column here is defined by the percent color calculation, which is color divided by both columns, some together Now The catch is that this one cell D 15 is actually technically impacted by both of these calculations. And when that's the case, one of the two needs to take priority, and that's exactly what the solve order is used for. So if we go back in to analyze and shoes solve order here, it lists out the two calculated item formulas that I haven't placed in this pivot table. I've got my kids rating, which is G plus PG, and I've got my percent color, which is color over black and white plus color. You see this note at the bottom that says, if the value in a pivot table cell is affected by two or more calculated items which cell D 15 in this case is, then the values determined by the last formula in the solve order, which is the percent color calculation and that is what we want. And that is what's going on here. So the percent color is taking 47.66 billion, dividing it by the some of these two numbers and returning 99%. If, however, we took this kid's rating and moved it down into the priority slot and closed the dialog box. Now, if we reform at this is a percentage now, you can see that the kids calculation, which is RO 11 plus, wrote 12 is taking priority. So it's resulting in 100% plus 98 equals 198% which obviously isn't what we want in this particular case. So it's jumped back to analyze, open up or solve order and will shift percent color back down to priority slot, and that will adjust back. And now, finally, you have this list formulas option. And when you launch the list formulas, Option Excel will create a brand new sheet summarizing all of your calculated fields and calculated item formulas along with the respective solve orders. So if you're dealing with a word sheet with 10 or 20 or hundreds of different formulas and calculated items, this could be a really nice way to automatically document how those formulas are working and which ones are taking priority. But again, if you're keeping it relatively simple, if you're primarily using calculated fields as opposed to items you may or may not ever need to use either of these tools. So I'm gonna go ahead and delete this sheet, and there you have it solve order and list formulas.