Excel PRO TIPS Part 5: PivotTables | Chris Dutton | Skillshare

Playback Speed


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

Excel PRO TIPS Part 5: PivotTables

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

15 Lessons (1h 37m)
    • 1. PivotTable Tips Intro

      0:18
    • 2. Customizing the Field List

      4:07
    • 3. Autofitting Column Width

      4:21
    • 4. Outline & Tabular Layouts

      7:46
    • 5. Counting Non-Numerical Fields

      6:05
    • 6. Grouping Dates

      8:03
    • 7. Enabling Multiple Filters

      5:00
    • 8. Grouping Values

      6:50
    • 9. Adding Value Calculations

      9:01
    • 10. Showing Empty Items

      6:22
    • 11. Configuring Slicers & Timelines

      10:23
    • 12. Conditional Formatting

      8:23
    • 13. Removing & Reviving Source Data

      8:19
    • 14. Adding Custom Sort Lists

      5:23
    • 15. Solve Order & List Formulas

      6:16
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

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.

259

Students

--

Projects

About This Class

Welcome to Excel Pro Tips for Power Users!

Please note that this is PART 5 of a 6-PART series, and that this is a limited version of the full course. To access additional course materials (including quizzes, 1-on-1 support, and Excel project files), please visit courses.excelmaven.com.

__________

FULL COURSE DESCRIPTION:

This course is NOT an introduction to Excel.

It's not about comprehensive, 101-style deep dives into Excel's core capabilities, or about showing off cheesy, impractical "hacks". It's about featuring some of the most powerful and effective tools and techniques used by Excel professionals, and sharing them through crystal clear demos and unique, real-world case studies.

Unlike most courses, this one is non-linear, meaning that you can bounce around and explore individual lectures freely. Each video is designed to be an independent and self-contained demo, designed to help you learn these tools and techniques in quick, bite-sized lessons.

__________

The tips and techniques covered in the full course fall into six parts:

  • PART 1: Productivity Tips (navigation, flash fill, cell protection, advanced sorting & filtering, etc.)

  • PART 2: Formatting Tips (freeze panes, row/column grouping, custom number formats, etc.)

  • PART 3: Formula Tips (auditing tools, fuzzy lookups, uniques/duplicates, randomization, etc.)

  • PART 4: Visualization Tips (sparklines, filled maps, custom templates, form controls, etc.)

  • PART 5: PivotTable Tips (slicers & timelines, custom layouts, value calculations, etc.)

  • PART 6: Analytics Tips (outlier detection, simulation, forecasting, CUBE functions, etc.)

__________

While the level of difficulty varies considerably, the demos generally start simple and become progressively more complex within each section. There are no strict prerequisites for this course, but keep in mind that some demos may be challenging without a strong foundational knowledge of the underlying concepts (like PivotTables or advanced formulas, for example).

Students who have completed the full Excel Maven stack will be well-positioned to maximize the value from this course:

  • Advanced Excel Formulas & Functions

  • Data Visualization with Excel Charts & Graphs

  • Data Analysis with Excel PivotTables

  • Intro to Power Query, Power Pivot & DAX

__________

It's time to start working smarter, not harder. If you're looking to maximize your efficiency, supercharge your productivity, and become an absolute Excel POWER USER, this is the course for you.

See you in there!

-Chris (Founder, Excel Maven & Maven Analytics)

__________

NOTE: Most demos are compatible with Excel 2007-2016 or Office 365 (some may be unavailable for Mac or Excel Online)

Meet Your Teacher

Teacher Profile Image

Chris Dutton

Founder, Excel Maven

Teacher

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?
  • Exceeded!
    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.

Transcripts

1. PivotTable Tips Intro: Hey, everyone. This next section of the course is all about exploring and analysing data with pivot tables . I'll show you had a group dates and values. Define your own custom sort lists and even revive deleted source data from the pivot cash. We've got a lot to cover, so let's get started. 2. Customizing the Field List: I want to share a pivot table. Pro tip, you guys. It's a very basic tip. One star, but very helpful nonetheless can talk about how to use some specific pivot table field list options to help you stay organized. Now, by default, the Pivot table field list is usually stacked, so you see a list of fields on top and you're areas pains at the bottom, your filters, rows, columns and values. So the top of that feel list you'll see a gear icon that will give you a menu of options. And so this is what the default generally looks like, which for many cases will be just fine. But in cases that you have a very large number, source tables like If you're using power, pivot on top of a complex data model or linking to a sheet with many, many, many fields or columns. There a couple other options that may be helpful to you. Number one is showing your fields and areas side by side, which is this second option here. What I like about this is that it gives you that entire vertical pain housing your fields so it makes it a lot easier to search and scroll through those columns if you have a very large number of them, and then the second option is sorting a to C as opposed to the data source order. So if you've got your columns aligned in a meaningful way in your source table, you may not care about this option, but otherwise, sorting alphabetically can help you access those fields a bit more quickly. So common use cases again. Updating that layout to show all your fields in one vertical pain, especially for dealing with data models and to sorting most fields alphabetically in cases where your source order isn't necessarily meaningful. So let's jump into our pro tip workbook if you'd like to follow along and I'll show you a couple of very quick examples of what this looks like. All right, so he around the pro tip workbook in the Table of Contents tab for quick Navigation. We're in the gray pivot table tip section, and I'm gonna look for the field list options demo again. One star. Very basic demo. It's like right out, and here we've got a pivot table against the I Am Devi movie database. We're looking at movie titles and genres and ratings, along with some values like gross revenue and budget. So here's my field list on the right side of my screen. If you don't see your field list, you can either right click cell in the pivot and toggle ish hide or show, usually the last item in the menu. Or you can go into pivot table tools and this show pain here all the way on the right. Well, as you toggle that field list on and off. Now, within that field list, here's that gear icon that will let you customize the layout and by default, where stacking our areas and fields. Here's that second item, which basically creates to Collins. We can drag it out to give ourselves a little more room. Now we have all of our columns or fields here in this left column, and we have our four pains or areas in the right or filters, rows, columns and values. Now, if we look at our fields, will see that they're not sorted in any sort of alphabetical order, have got titled and release date color genre language, and in this case this is just fine, because I put my calculated fields in my numerical measures at the end, which is a habit that I generally follow. But if that's not the case, you can go back and say, Let's sort these fields a dizzy budget cast color country, etcetera, which can make this field list a little bit easier to navigate, especially if you've got hundreds of comes here. You also have a search bar as well, which makes it very easy to drill down and find individual fields that way as well. So it's really a simple is that you've got a few other options here, like just showing the fields or just showing the areas. I personally don't find those very helpful, so I generally stick to one of these 1st 2 options. In this case, let's go back to our stacked option kind of drag it back out. And there you have a customizing or pivot table field. Listen, layout 3. Autofitting Column Width: all right. I want to take a few minutes and talk about how we can prevent our pivot table columns from auto fitting there with now by default, every field and a pivot will adjust the underlying column with automatically to accommodate the longest value in that field. And normally this is helpful because it means that all of the text in our fields will be visible by default. But it can be really inconvenient if you're working with a number of very long text based fields that might have hundreds of characters or words. So you may end up with something that looks like this here. We've got a long text based description field, and we've pulled into a row labels, and as a result, it's stretched column B well beyond the width of our window. She's making it very tough to work with now simple fix here to prevent that automatic re sizing or auto fitting from happening. All we have to do is jump into our options tab or options button within her analyzed tab, and we're gonna click or uncheck this box that says Auto fit column widths on update. And that's in the layout and format town of the Pivot Table Options Dialog box. And then once you do that, it will basically prevent column B from re sizing or whichever call in this field lives in as you drag that field into your row or column labels. So one other tip here, you can use the wrap text option from your hometown to create something that looks like this where you're still able to see all of the text in that field. It just kind of rolls it into new rose instead of putting all of that text on a single line . So the use cases here, working with text fields like survey results or social media posts very common use case. Or if you just want to force your pivot table column with to remain fixed, no matter how your table way out has changed or adjusted. So let's jump into a pro tip workbook and I'll show you what this option looks like. All right, so if you'd like to follow along, go ahead and open up your Excel Protect workbook. I'm in the table of contents and look for the gray pivot table tips section. Specifically the Auto Fit column with demo and go ahead and link straight out to attempt. Now what we have here is a simple pivot. The fields here, based on wine tasting data, we've got information about individual wines and wineries, country of origin and province. We also have the taster names and this text based description from the tasting, as well as their point rating and a price per bottle of wine. Now, if we grab this long, text based description field and pull it into our role labels after the taster name, see what happens here It's stretched column B way, way out, kind of beyond the window. And it's made it very difficult and awkward to work with, as you can see, just tryingto even just navigate through the view here, which is quite annoying because it's fit to the longest description in this entire field. So watch what I'm gonna do here. I'm gonna pull description out, which is gonna kind of shrink column B back down. I'm gonna go into pivot table tools, analyze and drill into my options here and within that layout and format tab. This box that says auto fit column widths on update is generally checked by default. All we've got to do is uncheck that box press OK, and then watch what happens when we pull description back in. There we go now. Column B is an average column with it didn't stretch it out to auto fit, and now I have a couple options. Aiken Stretch it myself to with that I feel comfortable with, but note that it's still cutting off my text. Who is one description? Perot. One update that I often like to make cases like this. It's select all of column B head to my hometown and activate this wrap text option in the alignment pain and that basically just extends or wraps the text to multiple rows in cases where it would have been cut off. And now I'm able to see all of the text in these descriptions and customize exactly how wide eyed like column B two b. So there you have it. Simple but effective tip. Preventing your pivot table fields from auto fitting. Those column widths 4. Outline & Tabular Layouts: all right, This next pro tip is an extremely important one for anyone who works with pivot tables in Excel. We'll show you how we can display pivots in different types of table layouts or forms, specifically outline or tabular form. Now, most pivot tables by default will show in what's called compact form and that groups all of your role labels together or nests, um, into a single column. So from your pivot table tool tab, the design group, specifically to see a report layout drop down, showing your different table layouts or form options. And this is what compact form typically looks like by default. We've got one value column here, the average price, and we have to row labels, country and a province field. Now the problem is that in compact form, these two fields get kind of nested together and both live within column A. And that means we have one column header accessible for things like sorting and filtering. And we can't apply our own sorting or filtering rules to each of those two fields individually, so that seriously limits our ability to slice and dice and explore these different role labels. And for that exact reason. I recommend that you often used the second option here. Outlined form. An outline form looks very, very similar to compact form. The only difference is that it takes those roll labels and it breaks them out into their own columns. Which means that now we have access to additional column headers and column labels, including those sorting and filtering tools. So outlined form is a fantastic format to use any time you really need to explore or analyze Iran data, and then you also have tabular form. And in this case, I'm looking at the tabular layout with repeating item labels and tabular form, essentially formats. Your pivot, kind of like a table gets rid of those extra spacing. Rose and formatting rules things like that. And with a little bit of clever formatting and a few options like repeating the item labels hiding the sub totals, choosing not to display grand totals, we can end up with something that looks like this, which essentially it looks just like a nice clean source data table that we can use for further analysis. Now, One thing to note, you can change your pivot table behavior and adjust your default from compact form to something like outline or tabular. I'll show you just what that looks like in a second, but again, to recap those common use cases outline form. Great way to explore your data sort and filter multiple fields individually and tabular form with repeating labels and no grand totals or sub totals could be the perfect approach to create new source data that you can package up copy, paste and use for other analytical purposes. So with that, let's jump into excel. I'm gonna show you what each of these table forms or layouts looks like. And then how we can use our options menu to change the default behavior. All right, so go ahead and open up your pro tip workbook and we're gonna drill into the outline and tabular layout demo in our gray pivot table tips section. Go ahead and link out to that sheet, and what we're looking at here is a simple pivot table built on top of our wine tasting data. Now, if you've been following through the course, this should look pretty familiar. Countries, provinces, wines, tasters and some values like points and price. Now you'll notice is that I've pulled in two fields into the role labels here, country and province. But because we're in the default compact layout, both of those fields have been nested together in column A and because I only have this one drop down, it makes it very, very difficult to apply different sorting or filtering rules to each field individually. Like, for instance, if I wanted to show only the top five countries but also sort my provinces by a certain metric, those types of combinations or individual filter sorting settings really don't work very well in this compact layout. So I'm gonna do is head to pivot table tools designed tam going to report layout and switch it to show an outline form. All that's going to do everything else gonna stay the same except those two fields that had been nested in Column A are now broken out into a country column and a province column. And more importantly, now I have labels assigned to each of those columns, which will make it much, much easier to apply those different combinations of filtering and sorting rules. So, generally speaking, I use outlined form almost exclusively. I very rarely use compact form for that exact reason. Now let's take a minute and talk about tabular format. So we've got data behind the scenes or source. Data is pretty granular, goes all the way down to the individual wine level. And let's say we wanted to roll that data up were aggregated at a higher level of granularity like maybe we only want thes four fields, for instance. We only care about data at the province level in cases like that pivot table, just like we're doing here, can do an excellent job aggregating those numbers up. So we're looking at the overall point rating and overall average price by province and by country. Problem is, if I copied and grabbed the data out of this pivot, I would still have a lot of manual work to do to put it into a nice, clean, tabular format for analysis. I've got these kind of header and sub total rose here. I've got blank rows that I'd have to fill in, and this is where tabular form really comes into play. So here in our design town, let's switch from outline to tabular form, and what we'll do is go back into that menu and choose repeat all item labels that fills in those blanks. And we're getting closer what we need here. The only problem is we still have these extra total rose, which really isn't a good thing when it comes to producing a raw, tabular data set for analysis. So all we need to do sub totals and say we don't want to show sub totals and grand totals. We don't want grand total. So off for rows and columns. And there you go. As you scroll through. This looks like a nice, clean, unformed, matted, tabular data set. So what I would do here is then take this. Copy the data and the pivot control shift down to grab the whole list. Copy it, pasted this values somewhere else. And then you're often running. You can analyze this data, however you see fit. So I got a great way to kind of convert a very granular data set in two different formats or different levels of granularity for different analyses or different purposes. Now, last thing I want to show you very quickly here. If you want to change the default behavior and tell your pivots never to default to that compact form. You can actually do that in your Main Excel file options. So options and it going to your data tab here and see right here at the top, it says. Make changes to the default layout of pivot tables. Go ahead and click that edit button, and here's where you can say, All right, here's I want to treat sub totals, grand totals and the report layout. This is the key here. Note how it says show in compact form. That's likely what your version of Excel is showing as well what you can do. Switch it over to another option here and highly recommend the outline form. So there you go. You can change your layout, change your default behavior and create the layout or table style that best suits your needs. 5. Counting Non-Numerical Fields: all right for this pro tip. I want to share with you how we can use pivot tables to count non numerical or text based fields. So when you're working with a pivot that values pain in your field list is almost always used for quantitative numerical fields. Your numbers, these airfields that you can add, multiplies tracked and summarize in different sorts of ways. But here's the thing. It can also be used to analyse the count or the frequency of non numerical fields of text based fields. And because you can't really aggregate or summarized text based on a summer on average or Max, what we're gonna do is use the count of summer station, which is the default to display frequencies for each of those text based values. So let me show you an example of what I mean by that. We're gonna be looking at her i m D B movie database here, and the idea is to understand the distribution of our data of our titles. Based on other fields like genre, for instance, we pull genre into both row labels and values as account. We can produce a view like this and because we know that our source data is at the individual movie title level, meaning each row represents a different title. This tells us that there are 900 titles that fall into the action genre. There are 362 titles or rose that fall into the adventure genre and so on and so forth, and you can follow this same approach for any non numerical field like ratings. For instance, follow that same process poor rating into both throw labels and values as account. And here we can see that there are 91 G rated titles there, 553 PG rated titles and so on. So a great way to explore our data from a different angle to really understand the distribution of titles based on different categories here. Now, one thing to note. If you pull a numerical field into that values pain and it defaults to account just like this, when you'd expect it to be something like a some that typically indicates that there's a problem behind the scenes that either values air formatted as text. Or you may have blank or missing rose that are confusing Excel. So keep that in mind To recap these common use cases. Number one analyzing the number of rows or observations in a data set that fall into specific categories or buckets, just like we're showing here. Also very helpful for generating source data for stats charts like Hissed A Grams or Pareto charts, which are designed to work with frequencies and distributions. So with that, let's jump into excel and practice counting some of our non new miracle fields. All right, so if you'd like to follow along, I'm in the table of contents here, go to your counting text fields demo in the gray pivot table section. Go ahead and link out to that sheet and here have got a basic table layout pivot table that have created on my I mdb movie database. And this is something like you'd expect to see right. I've got a text based, non numerical, field like country here in my role labels, and I've got a value field, a numerical column like gross revenue here, my values and showing those revenues as a son. Now, what if I didn't care about the total revenue that each country produced? I actually just want to see how many times each country appears in my data set. Well, what I can do is pull gross revenue out, and all I need to do is grab another instance of country, drop it into values it's gonna default to count of, because I can't really summarize it any other way. And there you go. So now these counts are basically telling me the number of times this country appeared in the rose of my source data. So Afghanistan appeared one time Australia 39 France 104. And now, knowing my source data because my source data is at the title level, I can interpret this count of country as the number of titles that were produced by each of these countries and column A. So as we scroll through, I see USA makes up the vast majority, 2944 titles or rose in my data set, and I can adjust to modify this count just like any other numerical value. You know, I can sort my countries descending by that count, for instance, to really understand the distribution of titles based on the country's and again the same process applies no matter which non numerical field you're looking at here. So instead of countries, we can pull both of those out and say, OK, let's pull in languages Instead, we get a unique list of all of the languages here when I pull it into my role labels. Then we grab the second instance into values as account as you'd expect. Here we see the vast majority of titles falling into the English language 3543 37 French titles, 12 Japanese and so on. Now, one last thing I want to show you. If we pulled language out and we actually grab title and pulled title into arose as well as your values. What we'll see here is a one for every single row. And remember why that's the case? Because this is the granularity of our data. Our data is at the title level, meaning that each row is uniquely represented by a movie title, and that's why we see a count of one in every single row of this table. So there you have a great tool to keep in your back pocket. Excellent way to kind of observe and analyse your data in a slightly different way and understand the distribution by counting these non numerical fields or categories. 6. Grouping Dates: This next pro tip is a popular one because it's a very common topic that I hear about all the time. Which is how do I group my dates, or how do I handle my date? Specific fields within my pivot tables now pivots by default. Do include standard tools for grouping date fields. You can turn days into months or quarters or years, etcetera. Now, sometimes you're dates will automatically group, and sometimes they won't. And this depends on your excel settings. I'm gonna show you how we can head to our options menu to see whether or not that automatic date grouping feature is enabled. Now, for this demo, we're gonna be working with movie title release dates, which is obviously at the day level, and I'm gonna show you two approaches. First, we're gonna show how toe automatically group those dates by adjusting our settings. And then we're gonna right click and manually drill into those grouping options. And in this case, we're grouping dates by months, quarters and years. And when we do that, we end up with something like this. You'll notice that we've created new fields that only exist in our pivot, not the source data titled Years and Quarters. And those fields can be used just like any other. You can pull quarters out. You can pull the release date out, which is now actually capturing the month name and basically manipulate them just like any other dimension in your table. But if you're like me, you might find this kind of clumsy and cumbersome kind of nests them all together. It makes it very hard to access your original release date field once you've grouped them. So I'm gonna actually show you an alternative approach as well, which I find to be a bit more flexible by actually creating some new fields in our source data via calculated columns using some simple date and time functions. So the use cases here, regardless of which approach you take. It's all about rolling up granular data like daily data in order to analyze trends or patterns at a higher level, like month or quarter or year, or creating high level summary tables or charts that are sourced from more granular source data so that let's open up our pro tip workbook. Let's look at these release dates and explore a few different ways that we can group or aggregate those dates at different levels. All right, So from the table of contents, go ahead and look for the grouping dates Demo in the gray pivot table tips section and link straight out to that sheet. And what we've got here is a blank pivot table. It's gonna be sourced from our I m D B movie database. Have actually made that source data accessible in the next tab. Grouping dates, parentheses, data. So pretty simple data set here. Just a quick sample from our I. M D B movie database. We've got titles and column a the release dates and column B and then a few different numerical metrics. Total number of reviews and the gross revenue in column C and D So back to our pivot tab. The grouping dates tab because my Excel settings are defined to automatically group dates and pivots. Watch what happens when I grab this release date column and pull it into Rose. See how it created it Kind of blew up into three different fields years, quarters and release date, and it's kind of nested the data all together into years quarters and in this case months And what you'll notice is that even if I go into my design tab, change this to an outline form. It's still kind of gets collapsed and rolled up kind of an awkward way that I personally don't really like. Now it could pull quarters out and release date out and just aggregate data by year, For instance, you know, pulling my numerical metrics like some of revenue or what I'm going to show you in just a minute. I could define a year column in my actual source data and have it accessible to me as well as my original release dates. So I'm gonna hit Control Z to undo a few times all the way back to my original pivot with nothing in my areas pains now, depending on your settings, you may not have seen those release dates group in the way that they did when I pulled them into row labels. What we're gonna do to check that is head to our file menu drilling to our options and had to the data options here now in the data options section right here at the bottom, you'll see a check box that says disable automatic grouping of date time columns in pivot tables. So in most cases this will be unchecked, which means that the automatic grouping is enabled. If you don't want your dates to group, just go ahead and check that box and press OK. I actually prefer to keep that box checked because I like to define the groupings on my own exactly like I want them. So now if I grab that release date and pull it into Rose, it's just staying right there as release date in its original form. Non grouped. So from here, if we did want a group, this data, let's say up to the year or month level we could right click and access are grouping tools , which you can also access in your pivot table. Analyzed tab grouping tools right here group feel Both will take you to this grouping dialog box, which basically allows you to determine. Okay, here is the range of dates I care about. Release dates from 1923 2015 and select individual fields that you want to include in your grouping. So, for instance, days, months, quarters and years in press OK and it's created three extra columns here for each of those grouping levels, and it's nested them right here within column A. Now, again, that may be exactly what you want. That may be just fine. You can use any of those fields individually, but I'm gonna show you an approach that I generally tend to take more often. So I'm gonna control Z to undo that. And what I'm gonna do is actually navigate to my source data, which is in this grouping dates, data tab, and let's insert two columns here right after Colin be, and I'm gonna name one of them released month, one of them release year. And here it could just use basic date and time functions to reference that release date and categorize a month and a year. So column see, it would be a month of B two and year would be equals year be to and that these air formatted kind of silly because they've basically inherited the release date format here. So that's right. Click format the cells as general press. OK, there you go. So month is 9 September releases 1920 boom, DoubleClick And now we have months and years as a new column right here in our source data set. And if we go back to our pivot pivot table tools can refresh. Since we've made a change within the bounds of our original table, and now we have these two fields available to us. So just like any other weaken grab month, pull it into row labels. Is there all the dates in January and all the dates we scroll down in February, March, April, etcetera? Or we can pull those individual fields out and say, You know what? I just want toe organize or aggregate my data by year. Let's look at the total sum of gross revenue based on the year of the release date here, so I prefer this approach gives me a little bit more flexibility now. Those fields are actually defined in my source data if I want to use them elsewhere as well . But again, there's no right or wrong here, totally up to you. Two different ways to group your dates using Excel pivot tables 7. Enabling Multiple Filters: all right, time to share one of my favorite pivot table pro tips, applying multiple filters to a single pivot table field. Now, by default, you can either apply label filters, which are based on text or value filters, which are based on numbers to a single given field in a pivot table, but not both at the same time. So in other words, if you had a label filter applied and then added a value filter, that new value filter would overwrite or wipe out the existing label filter. So to change this, what we're gonna do is head to our pivot table options. We're gonna drill into our totals and filters tab and check this box that says, allow multiple filters per field. Now, as you might expect, what this will allow us to do is apply filters that are based on both text and value attributes at the same time. So in this case, what we're gonna do is filter down to titles that end in the number two, which is a label filter and also drove a certain volume of revenue, which is a value filter. So the use case here is really just any time you want. Apply more complex or custom filters to your pivot tables that incorporate both those text and value based conditions or criteria. So let's jump into excel and practice, filtering some fields based on both label and value filters. All right. Now, if you're following along, head to your pro tip workbook and go to the enabling multiple filters demo in the great Pivot Table Tips section. And when we link straight out, what you'll find is some movie title data here. Got titles and column A. We've got the gross revenue that each title produced are generated here in column B, and as you can see, we're sorted descending by gross revenue. So these top grossing movies right here at the top include Avatar, Titanic, Jurassic World, The Avengers, etcetera. So let's say we're exploring this data a little bit, and we want to see performance for our Sequels specifically so we could simply drill into her column header. Here, in this case, would apply a label filter, since it's a text based filter and we'd say, Let's show the titles that end with a space and then a two right press OK, and there we go, our titles have filtered down Onley. These titles ending in two, which in most cases would be are Sequels, and that's helpful on its own. But what I want to do, really, is produce a filter down list based on the gross revenue as well. So I don't want to show all of the Sequels here. I only want to show the Sequels that, you know, in this case, let's say, drove over $200 million of revenue so it should produce you know, these top 10 items in this case so we can go back into our column header and let's jump in value filters. This time, I say, Let's filter down titles that are greater than where gross revenue is greater than 200 million, which is a two, followed by eight zeros. Riggio and press OK, and look what happened here. Our title label, Filter for Sequels, is now gone, and if we scroll down or Gross revenue column, um, does end right above 200 million so that value filter is working. But our label filter is now gone, and if we look at our header, you'll see it will check box next to value filters which tells us we do have a greater than filter applied. But that check that was next to label filters is now completely gone. Now, what that tells us is that we need to enable multiple filters here in order to do what we need. So let's drill into our pivot table tool time options down here on the left, we're gonna go into our totals and filters Tab and right here in the middle, in the filter section, you will see a check box that says, allow multiple filters per field. Go ahead and give that a check and press OK. And now, in theory, still got our value filter applied here. So now we should just be able to go back and apply that label filter ends with to drill down to just are Sequels. So again ends with space to press. OK, and there you have it. Now we're left with just those 10 titles that both end in the number to label filter and drove at least 200 million in revenue our value filter and looking at the header here we see those check marks applied to both types of filters. So obviously you can use this in many, many different ways, using any combination of text and value based criteria. So keep that one in your back pocket. Great way to create more complex or custom filter criteria by enabling multiple filters in your pivots. 8. Grouping Values: I want to take a few minutes to talk about a pivot table pro tip that I actually didn't discover until quite recently. And that's how to use pivots to group numerical values. Now, many people are familiar with grouping text fields or dates and pivot tables. But what most users don't realize is that you can also group numerical fields into custom bins or buckets. Now, to do this, what we're gonna do in this demo, we're gonna pull a numerical field into our role labels like this. In this case, we're looking at the price point per bottle of wine, and this feels a little bit wrong because we're always taught, you know, to pull those quantitative, those numerical fields into the values pain of our field list, not into rows or columns. But in this case, that's exactly what we want. And what we're gonna do is right click one of the values in this column and navigate to the group option, just like we would with a date or a text field. But the difference is that this dialog box that we get is specific to dealing with values so we can determine a start value an end value and the group or been size. So in this particular case were saying that our wine prices start at $0. Want extend it to 1000. We want a group these prices in $200 bins or buckets, and that spits out something that looks kind of like this. Now note that instead of showing each individual price point that's been translated into these groupings and by pulling in a field like the count of the wine name, that's telling us that there are 48,383 wines and are sampled that are priced from 0 to $99 there, 1282 that are priced from 100 toe 1 99 and then all the way down. At the other end of the spectrum, we have three unique wines that are priced over $1000 per bottle. Now, if you wanted to undo and go back to your original unique price points, just right, click those groupings and choose a new group to get back where you started. So this type of value grouping is an excellent way to analyze things like the frequency of observations that fall into different buckets. In fact, it's a perfect way to create bins to use as source data for a HIST a gram or Pareto chart, which are designed specifically to visualize distributions. Now to recap, these use cases again analyzing the counter frequency of observations that fall into specific groups or bins of values, or creating higher level summary tables or charts from more granular data sources. So let's jump into a pro tip workbook. Let's open up this pivot based on our wine tasting data and practice bundling or grouping thes price values. All right, so if you'd like to follow along, open up your pro tip workbook, head to the table of contents and look for the grouping values Demo in the gray Pivot Table Tips section and go ahead and link straight out to that sheet. And what you'll find here is a basic pivot table layout. It's built on our wine tasting data, and we just have to fields in view right now. Got wine varieties and column A. Those are our role labels, and we have the average price per bottle in column B. Those are values, and this is very standard very common type of pivot table layout. Got a text based field as role labels. You've got a numerical or quantitative field as values. So from here we could apply, you know, some text based groupings, you know, maybe want to select everything with cabernet sauvignon in the wine type title and create a group containing just those items. That's a very common type of grouping with pivot tables, but we're not gonna do that. We're gonna take a slightly different approach and we're actually gonna pull our value field into our role labels. And we're gonna pull a text field in this case, the wine name into our values. So we're gonna kind of flipped the script here to create a totally different view of our data. So to do that, let's pull wine variety out, and we're gonna take that price field and pull it straight from values into Rose. And when we do that, we now see a unique list of price points listed out row by row. And from here, what we can do is basically say OK for each of these price points for $4 winds, $5 wine, $6 wines. How many observations are rose exist in our data set, and since our data set is at the individual wine level, I can grab this wine name column, pull it into the values and represented as a count. This is telling me there are seven wines that are priced at $4 a bottle. We have 16 priced at $5 and so on and so forth as we scroll through this table. So this is great. It's already kind of a helpful way to understand the distribution of wine prices in the sample, but it's a little bit overwhelming because I have so many different price points here. To try to make sense of this is a great use case for grouping these prices into bins or buckets. So let's right Click can select any one of these prices Cheese group and here received this dialog box that's specific to grouping numerical fields. So let's say we want to start our bucket first bucket at $0 and in this case got some lines that go all the way up that high, but very, very few. So let's end at 1000 and the buy option, where it says 100 that's the size of each been. So do we want groups of prices in $10 increments or 100 or 1000? Let's start with 100 see what that looks like and simply press. OK, so what that does is it basically converted those individual price points into these bins or buckets? And from here we've got accounts of wine names so you can see the vast majority fall under $100. We've got about 1200 almost 1300 that falling 100 to 200 point range and so on so we can treat these values just like anything we can sort our buckets. We can create a pivot chart based on these frequencies, copy it, paste them into a new sheet and create a HIST, a gram or Pareto chart. But bottom line is that by using these value grouping tools were able to explore and analyze this data set from a totally different angle or through a totally different lens, and really get a sense for how these price points are distributed throughout our data set. So there you have it quick crash course on using pivot table value grouping tools 9. Adding Value Calculations: all right, let's talk about one of the most useful and powerful pivot table tools analysing your data using value calculations Now. In a nutshell, value calculations allow you to display underlying raw values from all sorts of different angles. So instead of just displaying a standard some or count of or average, you can show those value based fields in very interesting ways, like the percent of a given column or row or the percent of a given parent category, you can show values as the difference from or percent difference from a given base item. So that's great for things like month, over month or year over year changes. You can calculate running totals, which displays those values as a cumulative total within a given base field. Or you can show those as ranks which just like it sounds, doesn't show the actual volume. But it shows the rank of that volume based on low to high or hide alot rules. Now keep in mind this isn't a comprehensive list. There are additional options as well, but basically what this is going to allow us to do is show something like this, or we're looking at gross revenue totals by month in this case month one through nine. But instead of just stopping there, we can actually right click those revenue totals or created duplicate copy and show those values in different ways. So here's some of the options. We talked about the percent of grand, total percent of row percent of parent difference from running total and so on and so forth . So here you can select one of these options, like running Total in, for instance, selected based Field of Release month, which isn't column A. What that does is display those revenues as a cumulative running total month over month over month. Now, a couple quick things to note here number one a quick tip and recommend dragging in multiple copies of the field you're looking to analyze so that you can keep an original like we're doing here. And compared against these value calculation versions of that field and second note here, keep in mind you're not actually changing the values themselves. The core underlying raw values are not changing. All we're doing is changing the way those values get displayed within our pivot table and Onley within her pivot table. So common use cases here. One is analyzing time serious data as a running total by day, month or year or calculating time. Serious trends like month over month, year over year, quarter over quarter, etcetera. Second common use case exploring the composition of your values in terms of the percent share by things like category month, year, etcetera. So what we're gonna do is jump into excel and we're gonna take a look at some Olympic medal data practice analyzing and exploring that data using these types of value calculations. All right, so had your pro tip workbook. We're in the pivot table tips section here, and we're gonna drill into the value calculations demo link straight out to that sheet. And what we're looking at here is data from six different Olympic games got winter 2006 through the summer 2016 games. And in this case, we're looking at the medal count for gold, silver and bronze medals for the United States specifically. So these values you see in column B is there kind of uncalculated just the basic count of metals. Based on each of these years, we can see that the U. S. 1 992 total medals overall during this period. But what if we want to enhance this view a little bit analysed? These metal counts in some different types of ways. Kind of add a bit more context or learn a little bit more about what's going on here. Well, the best way to do that is with calculated values. But before it just right click and show values and different types of ways, which would overwrite these original uncalculated values and column B. What I'm gonna do is just head to my field list, grab that metal field and I'm gonna pull in four more duplicate copies of that column. So I've got count of metal counted mental 234 and five, all of which is currently displaying that same raw count. Now the first question that I might want to answer is I know the US 1 992 total medals. But show me how that total breaks down for each of these six Olympic Games. And if I want to show that as a percentage or composition of metal counts that can right click this second generation column C and let's show these values as the percent of the column total. Now you see that grand total row add up to 100% and that 100% is broken down by each of the six row labels. So we can see that in 2008 the United States won 31% of all of the metals that it earned in this entire sample compared to 2006 where it only 14.84%. And instead of keeping the header something that's very confusing, like count of metal, too, can actually just click into the formula bar and give this a custom title like percent uh, column in this case. And now for Colin D. This count of metal three say we want to tell a similar story, But instead of showing the percentage one each year, I'd like to rank out these years based on the volume of metals. So for something like that, to simple as show values as a rank and because a large number is a good thing in this case , we want to rank largest to smallest, and our base field here, in which we're ranking is based on the years press OK and the same story kind of comes to the surface. 2008 was the number one ranked Olympic Games in this sample for the U. S. We won 309 medals, and on the flip side, 2006 was the lowest medal count, which is the sixth ranked year and same story here. We can change our header to something like rank. And now what if we don't really care about the medals won each individual year? All we care about in this particular case is the total and how that total is changing over time. Well, for something like that, the running total calculation is a great option, so it can show these values as a running total can also use percent running total, which just adds to 100%. Let's go ahead and do just the plain running total in our base field again is our role. Labels are years. Press OK, and now 2006 shows the 48 medals 2008. We add 309 to the 48 2010. We add another 89. You can see how this total medal count is just growing over time as this cumulative cone. So there's a quick running total change, that header running total. And as a final example now, what I really want to understand is how the medal count has changed year over year or, in this case, Olympic game over Olympic Games. Since it's a two year gap, and what we can do here is use a few different options. We can use either something like the difference from or the percent difference from. So in this case, if you care about percentages, you could choose this option to care about the actual count of metals, your beer. You could use this first option. Let's go ahead and do this. And now our base field, just like always, is our year rule labels. Now we have one other option here as well, which is the base item, and this is the base from which you're calculating that difference. So in this case, we don't always want to calculate our difference against a particular year in like 2006 or 2008. We always want to calculate this difference based on the previous item in our row labels, so we're going to select previous here and press. OK, and now check this out. It's telling us that in 2008 the United States earned 261 medals more than they did in 2006 . But then in 2010 they earned 220 medals, fewer than 2008. So this is now showing that pattern of year over year differences in metal counts. So let's go ahead and change that header to difference from. And there you go. So there are additional options here, some more complicated ones that'll cover and other courses like the index option. But this is a great demo of how some of the more common options can be used to analyze and explore your data in a pivot table in a number of different interesting ways. 10. Showing Empty Items: Let's take a few minutes to talk about how we can customize errors and blank rows within a pivot table. Now by default. As you'd expect, pivots won't display row labels for items where values don't exist. However, you can force excel to show those items, even in cases where there is no data in your source table. So let me show you what I mean by that. Here we're looking at these some of gross revenue cross movie titles categorized by countries and genres. And we have cases like Australia, where we have many titles in our sample across many different genres. Action adventure, animation, biography, etcetera and other countries like Afghanistan, Argentina or Aruba. That may only have a small number of titles in our sample that span Onley one or two genres like drama, crime or action if we want the genre. Colin column B In this case, to display every possible genre for every country. What we could do is simply right. Click that column header in this case genre. Navigate to our field settings and check this box at the bottom of the list that says show items with no data and what that will do is force the pivot table to display the full set of genres for each country in the table layout. And from here, what we can do is navigate to our layout and format options and customize exactly how these empty values, as well as potential error values, get displayed. Now you might be wondering why you'd ever really want to do this and kind of create Rose for data that will just equal zero anyway. And it's not very common at all. But one use case is for displaying all of these possible items within your table layout, even those blanks or zeros, so that you can create a consistent layout or template for other purposes or similar use case. Maybe you expect all of these items to be populated, and if they're not, you want to be able to flag them or draw attention to those missing values. Another option here. Reformatting blanks or errors simply to clean up user facing reports or dashboards built with a pivot table. So let's jump into our pro tip workbook. I'm gonna show you how we can add these items with no data to a pivot table and then customize exactly how they get displayed. All right, So if you'd like to follow along, head to your table of contents, we're gonna drill into the showing empty items demo in our gray pivot table tips section. Go ahead and link straight out. And just like we described, we're looking at IMDB movie database. Here got the sum of revenue and some of budget and column C and D, broken down by country and by genre. And just like we had described, you've got countries like Australia, Canada and France that have generated titles across a number of different genres and other countries like Colombia or Finland, where maybe only one or two genres are represented here in our data set. So in this case, let's say I would like to include a row for every possible genre across each country. To do that, I can right click the column header in this case, be one for genre. Head to my field list settings or field settings, and in the layout and print tab, all I need to do is check this box that says show items with no data press, OK, and there we go. Now all 17 possible genres are represented for every single country within our table layout . And once we have this in place, what we can do is customize exactly what these blank rose is, Rose with no data actually look like. So to do that, we're gonna go into pivot table tools options and right here in the layout and format tab, we've got to check boxes here, one to customize how to format error values and one to customize how to format empty cells . So by default, those empty cells air formatted as you guessed it, empty cells. But we can change that to a different value. If we'd like, could use text like an A could use a value like zero here, since, technically, that is accurate. You know, comedy films in Afghanistan did dr $0 in revenue, but it's also slightly misleading because the fact is, those values air simply missing from our data set. So let's jump back. And I think I prefer the blank cell here for those empty cells. Go ahead and press OK, And now what if we had a calculated field here in our values list as well? Something like our ally, for instance, which is simply gross revenue divided by budget. In this case, that should be a percentage. So let's change the value field settings. Trump's Let's change the number format. Excuse me, 2%. Here we go. And now, because this calculation is running against every single row in our pivot, including rose with blank denominators, we get all of these div zero messages here. Um, and this is the default behavior. What we can do is have back into our options and customize what those error messages look like. So it's check that box and we can show them as a blank, like so which I think I actually prefer. Or, if you'd like, you can customize the value here something like an A And again, this is a case where I would not replace those error messages with a zero, because that will do things like skew any averages you might take, or any calculations that you might make against this are Y Field. And it tells kind of ah, misleading story because they are why the return on investment was not 0%. For these genres, it simply didn't exist. So let's jump back to options actually prefer to use blanks for both of these, so that all we see are the roads that are populated with data from our source table. So there we have it showing items in her pivot table with empty data. Not the most common pivot table tip in the world, but one that could be a great help when you need it. 11. Configuring Slicers & Timelines: I want to take a few minutes and talk about one of my favorite pivot table features, adding visual filters with slicers and timelines. Now these sound like very fancy, complex tools, but they're actually quite simple. Slicers are basically just interactive versions of a filter, and timelines are just slicers that work with dates. So it's a simple is that inserting? These are very simple as well. You can drop men right from the field list by right clicking a given field or column and choosing at a slicer. Or, if you're looking at a date specific field, add as timeline. And when you add that slicer in, it will create a new workbook object containing a list of possible user selections. And those selections will dynamically tie back and filter the pivot table itself just like a regular filter in a drop down cell. Now, by default slicers, will Onley interact with the pivot table from which they were created. But that said, if you have multiple pivot tables generated from the same source data, you can use the report connection options from your Slicer Tools menu to essentially tie a single slicer to multiple pivot tables and in the demo are about to dive into. We're going to do just this. We're gonna control an athlete, pivot and an event pivot using a single set of slicers and timelines. Now remember, each of these pivots needs to share the same source data or be related in some way. Can't just try to control two unrelated tables using a single slicer. Now final note here by default, the selections or the items that you see in a slicer, will visually indicate cases where no data is available. So if you had a slicer for season and a slicer for sport and you selected summer as the season, you wouldn't want to show all of the winter sport options in that second slicer because those will generate no values. In most cases, those will be great out by default or in some cases not even visible in the slicer. And that's certainly a helpful feature. But one thing to keep in mind is that it can slow down performance if you're dealing with a very, very large or complex tables with a lot of interactions between many slicers Now, common use cases here for one, adding user friendly visual filters to reports or dashboards that you build with pivot tables and pivot charts. And second, using slicers to clearly and visually indicate exactly how a table was being filtered. So with that, let's jump into excel and practice, filtering down some data using these slicers and timelines. All right, so if you'd like to follow along, go ahead and open up your pro tip workbook and we're gonna head to the slicers and timelines demo in the gray pivot table tips section. So go ahead and jump right out to that tab. Slicers and timelines and what you'll see here are to pivot. Tables aren't in place. One of the left is called athletes and the one that right is called events and in both cases were just looking at the count of gold, silver and bronze medals earned either by event or by individual athlete. And we've created a little bit of space above both these tables so that we can essentially create a control panel with slicers and timelines to dynamically filter the tables below. So it's almost like creating a mini little type of dashboard here. So let's start with our athlete time head to our field list, and the first slicer I'd like to insert is a simple one for season only two options summer or winter right click add as slicer and there we go. It's inserted. This new worksheet object that could be treated just like any other object could hold Ault . Snap it to your grid if you like, can resize it, dragging around just like so, and watch what happens as I make different selections to this slicer pivot table from which it was created. Filters just like it would have this been in normal filter as opposed to a slicer. So when I have summer selected, I see the top metal earners for summer Olympic sports. Michael Phelps and Ryan locked both USA swimmers. In this case, same goes for winter. Now we see on Lee the Winter Athletes in the pivot, so let's go ahead and unfiltered. Let's go back into her first pivot. I want to add a second slicer now for sports, so right click Add a slicer, drag it in place. And one thing to note here is that in this case I have a very tall and long list kind of packed into a small piece of real estate. So what we can do is change the slicer tools to adjust things like this style alignment arrangement and the number of buttons. So in this case, let's make this different color to kind of differentiate it from our season slicer. Let's drag it out quite a bit here and instead of one single column of buttons, let's make this something like three columns that just makes it a bit more readable, and it makes the scroll bar a little bit easier to interact with Here. Now, check this out. If I select summer from my season slicer and scroll down at the bottom of my sports slicer , you'll see these great out options Alpine skiing behalf on bobsleigh and you'll notice that by no coincidence, those are the winter sports. So excels visually identifying the invalid options here. Because if we were to select ice hockey, for instance, note that there are no roads left in the pivot because no athletes competed in ice hockey during a Summer Olympics, so we can go ahead and unfiltered. That and one thing that we can do to kind of customize how this works is select the slicer go into our slicer tools and slicer settings here on the left and see this check box. We're visually indicating items with no data. That was the faded style that we just saw. Another option that tends to work really well is hiding items completely than have no data . So if we check that box instead and press OK now, those winter sports don't even appear within this list. Same thing if we click winter. Now we see on Lee the winter sports and the summer options don't exist. So this is a nice, clean style nice approach option to use. But again remember that it can slow things down If you're working with very large or complex tables. And one other thing to note here by default, you've got single select enabled meaning that as you change your selection, you're only dealing with one particular item or selection out of time. Check this box with the check marks that enables multi select so I can see athletes who competed in Alpine skiing or biathlon or bobsleigh, and that's kind of up to you, depending on the data. Whether or not you want to enable this multi select option this case. Let's keep it as single. Select for now. I'm gonna go ahead and clear this, and we're going to do one more example here from our athlete. Pivot back to our field list. This time I want to work with the year field when I right click, notice that now this last item ad as timeline is finally active. And it wasn't for other two cases. Season in sport. If we click, add as timeline, it's creates another slicer like object except with a different kind of look and feel. Because this is a timeline designed specifically to work with dates, a couple of things to call out here you may see that it's kind of rolling up things in a funny way. We only have data by years, so we really don't care about months here at all. So you can change this little drop down two years, and we can go into timeline tools, which look very similar to slicer tools. And we can do things like adjust the style, maybe want this to be green and then to interact with this timeline. It's a simple is clicking. Dragging. You can extend the handles you can click toe Look at Onley individual years, and you'll note that just like the other versions, the slicers, the pivot table filters dynamically. So essentially, what we've done is create a set of visual controls that users can use to explore any different combination of data from this pivot, based on the season, the sport and a specific time frame. You can look at summer athletes, for instance, who competed in archery and only from 2014 through 2016 and instantly were able to drill down our pivot table to show that exact combination of Rose. Now, the final thing I want to show you is this report connections option. So that's unfiltered. Each of these existing slicers and timelines and what we're gonna do one by one, select the slicer and into our slicer tools. Click this report connection option. Now this shows me any valid pivots in the worksheet that I can connect this slicer, too. And as you can see, you've got a check box next to athletes and a blank check box next to events, which is this second pivot right here. All we need to do is check that box press OK and now watch what happens? We select summer and both the athletes and the events filter accordingly. Same with winter. And we've basically just tied this one slicer to both of these pivots. We're gonna do the exact same thing for sport. Report connections tight to events as well. And the timeline, timeline, tools, report connections. Yeah, that's first. Okay. And now, just like before, you can filter on any combination of values. And now both of these fields. Both these pivots will update to reflect those selections. So they're ago slicers and timelines. Great way to add visual filters and user friendly tools to your excel pivot tables. 12. Conditional Formatting: All right, let's talk about how we can bring our pivot tables toe life with conditional formatting. Now this is a four star, rather advanced tip, and that's only because there are some nuances here that make conditional formatting with pivot tables a little bit trickier than conditional formatting with standards cell values. So we're gonna start by adding conditional formatting rules. Those same old familiar rules from the hometown data bars color scales. Icahn sets, etcetera to highlight patterns or trends in the pivot table itself. Kind of like this. In this case, we've added formatting to visualize patterns in average prices and average point ratings for different wines for a given filtered country, in this case, Canada. Now, unlike traditional cell formatting, we can actually specify some options for how the pivot table format reacts to changes in the table layout. So, in other words, these options here allow us to determine the scope of the formatting that we've applied, so you can scope that formatting to a selected range of cells to all cells for that given metric in this case, average price or to all cells for that given metric, and those specific role labels, which is wine name in this case Now, generally speaking, I prefer that second option all cells showing these particular values. And what that's going to do is allow that format to stick. Even if we pull wine, name out of the row labels and a different field like wine variety in, I'm gonna show you exactly what that looks like. Assumes we jump into excel but quick summary of the common use cases, using color scales to draw attention to patterns or trends in the data and applying formatting rules in general that stick regardless of how your pivot table layout changes. So with that, let's jump into excel and practice, applying some custom conditional formats to our pivot table. All right, so if you'd like to follow along, open up your pro tip workbook and head to the conditional formatting demo in the gray Pivot Table tips section, and here you'll find a wine related pivot. We're looking at wind names on Roe labels and for each of these wines, are tracking the average price and the average point rating. And, as you can see, here were filtered Onley toe Look at Canadian lines specifically. Now the idea is to turn this from a plain old table into a visual tool that we can use to identify the best combinations of price and point rating. In other words, I want to create some sort of a tracking tool to help me draw attention to those wines that are affordable and very highly rated. And to do that, we're gonna apply some conditional formatting rules, like color scales and data bars. So let's start with our average price column here. Generally speaking, I would suggest selecting the first row here and using something like the control shift arrow down shortcut to highlight all of the valleys with the NYT column. But in this case, for the sake of demonstration, I'm just gonna select a chunk of data here about 10 rows or so. Now, watch what happens when I apply a conditional format in this case, a color scale. And since low prices are good in this case, I want those low prices to be green. I'm gonna choose the second option here red, yellow, green, and when I click, you'll notice this little pop up appear right where we would normally see something like the auto fill options and This is a special pivot table formatting menu that allows us to determine the scope of this formatting rule. So by default it's set to be selected cells that I had dragged out. But I have two other options here as well. Any cell showing average price values, regardless of the role labels, or any cell showing average price values specifically for wine names. So to show you an example, let's choose that third option. You can see that it's applied it down to every sell in the column, even though I had only selected about 10 or 11 cells. But watch what happens now if we take my name out. Pull wine variety in that formatting is lost because we had determined the Scope toe Onley format when we're looking at both average Price and the wind name specifically. So let's go ahead and swap variety back out. Why name back in and there's our formatting again. Now we can't get to that same pop up menu anymore right now, but we can access those same tools but heading to conditional formatting manage rules, and we can edit this color scale that we just created, and you'll see those same three options right here at the top of our list. So instead of this third option here, let's select all cells showing average price values again, regardless of the row or column labels press O'Kane. Apply that rule. As we can see, nothing changes. But now watch what happens. But why name out wine variety in and check it out the formatting sticks, even though we swapped in different role labels. So, generally speaking, that's the scope that I like to apply when I'm playing with formats in pivot tables. And let's just revert back that we're looking at. Why names again? Now let's focus on this point rating column here. So same thing I can select one cell. I can select multiple cells. The whole column. Whatever you choose. This case, I want data bars. Okay, So instead of a standard data bar option, there's some other values here. Other options that I'd like to customize. So I'm actually going to go into more rules at the bottom of that menu, and here again, we can access thes scope options here. Let's choose the second option, and instead of showing the actual point rating as text, let's Onley show the data bars by checking that box. And because there's a very narrow range of scores here, it's really nothing under 75 or 80 and nothing above 100. Let's set a minimum number of 75. Just two more clearly. See the differences between these bars now last but not least, going to give it kind of a dark blue color. This was totally up to you and press OK, and there you have it. It's applied those custom data bars all the way through the column, just like you'd expect. So now the purpose of our tool is starting to kind of emerge right. We're looking for low price lines at the top of the list that also have a high point rating based on the size of this bar. So we see some pretty good options here. Great options there. Good one here. One last thing that we can do to really take this to the finish line is apply. One more rule to this point rating column that's going to conditional formatting highlights cell rules. And let's basically say any of these cells that are greater than 90 points let's give it a green fill with a green texts. Press OK, and then click the pop up and apply it to all cells showing average point rating values and check it out. That just highlighted any of these cells here with a point rating of 91 or greater, so we can see that these Canadian wines get some really good high rated wines for very low price points. Only $17 in these cases, $18 in these. And now, because we've built this tool and because we apply these formatting rules to a dynamic, flexible pivot table, it means that we can do something like this instead of looking at Canadian wines. Maybe you want to look at Italian lines. Press OK. All of our same formatting rules apply automatically, which is incredible. So now let's scroll through these Italian wines as we can see nothing above 90. Yet until we get to Here we go. So here's the first road that was flagged as a 91 point wine. This is a 2010 Chianti Classico for $12 so we basically just created a nice little tool that allows us to identify these great opportunities to get high rated lines for a very affordable price. And there you have it bringing your pivots toe life using conditional formats 13. Removing & Reviving Source Data: all right. This next pro tip is incredibly important for anyone who works with pivot tables and excel . We're gonna talk about the pivot table cash and specifically how that cash can be used to both remove and revive your pivot table source data. Now, what most users don't realize is that any time you insert a new pivot table, a pivot cash is generated as well. So think of that pivot cash as essentially a compressed, duplicate copy of your source data that lives out of sight. Behind the scenes about this cash is incredibly important because what it allows you to do is actually delete your original source data without actually impacting the pivot itself. And that sounds crazy. You can actually delete the table or cell range that you used to create a pivot and continue to use that pivot just like you normally would. You could modify your table layouts, your row and column labels at slicers and pivot charts, all because you're now working off the data in cash. And there are a couple of reasons why you might want to do this, one of which is to significantly reduce your file sizes, especially if you're dealing with large tables that don't need to be refreshed or updated over time. So to make this work, we need to adjust some very specific settings. I would say we have a basic pivot table like this for this demo. We're gonna be looking at San Francisco salaries. So we're looking at the average base pay for 2011 2012 and the grand total overall. And if we drill into our pivot table, tools are options. Menu. Look at the data tab. You'll see three different options here in the pivot table data menu, and we want to check the 1st 2 boxes here. The 1st 1 save source data with files ensures that that pivot table cash gets saved with your workbook. Second, enable show details. This is basically your lifeline in case you need that source data back because what it allows you to do is double click any unfiltered, grand total cell in the pivot like this one, and it recreates the entire source table from scratch from cash in a brand new worksheet. I'm gonna show you exactly what this looks like. An excel, but obviously very, very powerful, very useful tool to keep in mind. Now, as far as common use cases, there are two that come to mind first, like I mentioned, removing static source data to reduce your file size or increase your processing speed. And second, it's a great way to limit accessibility in order to prevent users from actually accessing or changing the raw data itself while still allowing them to explore or analyze that data through the pivot table interface. So let's jump into a pro tip workbook going to show you exactly how this pivot table cash works. All right, so if you feel like following along, open up your pro tip workbook, head to your table contents and we're gonna drill right into the removing and reviving data demo that forced our demo in a gray pivot table tips section Click link. And what you'll find is a table of data tables called SANFRAN salaries. We've got employee names by year, their job title and two metrics that we're working with here base pay and overtime pay. And if I just used the control arrow down shortcut to jump to the bottom, I could see that we've got just over 16,000 rows or observations of data here 16,085. Now keep that number in the back your mind because we're actually gonna return to that number in just a bit. So it's not a huge amount of data, but its considerable to enough to kind of save some file size if I were able to kind of clear this out. So the idea here is to create a pivot based on the source data and then get rid of the table itself so that we can just work from cash and reduce our file size. So with any cell here in the table selected going to insert Tibbett, drop into a new worksheet and let's just name it something meaningful. Double click. Maybe it's sand friend. Give it. There we go. Let's just kind of create a basic view here, but a year into filters, maybe job titles on Rose base pay on values. And let's show those as an average here looks good, and, ah, when we filter down to just 2012 alright, so I've got a good starting point here. Basic pivot table layout. Don't worry too much about the details. What we really need to make sure is that our settings are specified in a way that will allow us to remove or delete that actual source table. So let's go into pivot table tools. Options drill into the data tab, and these three check boxes are the ones we care about right now. Number one saved the source data with file Check that box because, yes, we want the cash to be saved with this workbook number to enable show details. Check that box because, yes, that's our lifeline in case we need to get our source stated back. And then this third box refresh data when opening the file that when you don't need to check because refreshing the data won't be possible once we delete this source table because Excel is not gonna be able to find the table or range of cells from which this pivot was created. So it won't have anything to refresh. In fact, if you've tried to refresh some of the other pivots throughout this course, you may have gotten an invalid reference error. And that's exactly what you see here because what I've done is strip out all of the source data to reduce the file size for this project workbook. So let's go ahead and press OK. Here. Sendings look good. Now watch this. Go to our actual source data. We can right click the tab and delete the entire tab containing that whole table of data. We jump back to San Fran. Pivot, check it out. Maybe we don't want 2012. Data may want 2011. Boom. Looks good. Let's pull in overtime pay as well. Let's view it as an average. Not a son. Looks good. And then, hey, instead of job titles, is polos into filters and actually bring in employees name from our field list. There we go. So as you can see, we're able to modify and play with just this pivot exactly like we would if our source data still existed. But our tab is gone. It's been completely deleted, and that's why it's so powerful toe work off cash when you're dealing with static tables like this. Now keep in mind if we went to pivot table tools trying to refresh. Here's that error that we're getting. Reference isn't valid because X L can't find that Sanford and salaries table anymore, and that's OK, because what we can do here is clear. Any filters that we have like this or you can actually go into the clear option and say Clear any filters And it doesn't even matter what our field list looks like. What values are in here, what we're looking at for Roar COLUMN labels All we need to do is jump down to any grand total cell. Either be 15 7 18 or C and double click and watch what happens We created in new sheet. In my case, it's called she 12 and it includes all of that source data the name year, the job title based, paying the overtime pay and check it out if we scroll all the way to the bottom 16,085. So we've revived all of that source data using that show Details DoubleClick feature. So from here, maybe we add some new calculated columns. Maybe we stack in some data that we need. We can refresh our pivot now that our table exists again and then go ahead and delete it once more. So obviously an incredibly powerful tool and one that a lot of Excel users really aren't quite comfortable working with. So there you have it that's your crash course on the pivot table cash and how it can be used to actually delete and then revive your source data. 14. Adding Custom Sort Lists: I want to share with you a five star pivot table tip. We're gonna talk about organizing fields using custom sort lists. Now the reason this is an expert level tip has nothing to do with the level of difficulty. In fact, it's quite easy to implement and everything to do with the fact that 99% of Excel users out there simply have no idea that this option even exists. So by default, Excel has a number built in sort lists to help organize fields that can't be sorted with traditional rules. So things like week, days and month names, which don't follow any sort of alphabetical or reverse alphabetical logic. You just have to know that Monday comes before Tuesday. March comes before April, and so on and so forth, and that's exactly what custom sort lists are designed to do. And in this demo, we're gonna use very similar process to sort Olympic medals because, just like weekdays, there's a very particular order here that doesn't necessarily follow any standard rules. So what we're gonna do is had to file options advanced and then drill all the way down to the general section and click this edit custom list button where it says create lists for use in sorts and fill sequences. And what we're gonna do is create a new list and tell Excel, Hey, these air Olympic medals, they're meaningful. I always want gold to be first, followed by silver, then bronze, then n A. And once you've saved that list and you return to your data or your pivot table now, that custom sort will take precedent over the standard alphabetical sorting rules. And you can use that same old a dizzy option to now sort in the order that you've defined so really helpful tool, especially if you have a lot of items and you can't click and manually drag them into the order that you need. So common use cases any fields that can't be sorted with traditional means like apparel sizes small, medium and large or like we see here, Olympic medals Gold. Silver Bronze can also be really helpful for creating custom fiscal calendars to override standard months sorting. So, for instance, if your fiscal year starts in May, you could create a custom list to define the month order for your specific fiscal year. So let's jump into Excel and practice actually creating and applying one of these custom sort list. All right, now, if you'd like to follow along with me, go ahead and open up your pro tip workbook, head to your table of contents and look for the customs sort lists five star demo in the gray Pivot Table tips section. And when you link out to that sheep what, you should see his data from the 2016 Summer Olympics and we're looking at the medal count , the count of medals awarded based on the metal type gold, silver, bronze or any, and also based on these sports. Now the challenge here is that this metal field and column B try to sort it alphabetically . A dizzy. We end up with bronze on top and silver on the bottom, which doesn't make much sense. Same story if we reverse it Tizzy day. Now we just get silver on top, followed by any and then gold and bronze. So we have two options. In cases like this, Option one is to select the fields, hover over the edge until you get the four pointed arrow and click and drag them into a different order and that's fine. For cases like this, only four items in the field. I'm definitely feasible. The other option, which is a bit more scalable, is to create a custom sort list. So let me show you how exactly that would work. We're gonna go into our file tam drill into our options, had to advanced and then scroll almost all the way to the bottom until you get to this general section. And the last item in the general section says create lists for use in sorts and fill sequences, Edit custom lists. That's what you wanna check and you get to this dialog box Here you can see those week, day and month name custom lists that I mentioned earlier already baked in here and to excel . And all we're gonna do is adding new list. We're gonna type in the order of those medals separating each line using the enter key. It's a gold silver. Enter bronze, enter an A and let's go ahead and click. Add and it's added that list right here in a custom list window press OK on. Okay, again. Now, when we return to our pivot, you'll notice that it didn't sort automatically So you may need to go back into that column and now press a dizzy and see how it sorts it. Now it's gold, silver, bronze and a because that custom list that we just created takes precedent. And we essentially overruled the order that would have been generated using traditional alphabetical sorting. Which case bronze would have shown up at the top. So there you have it. This was a pretty simple example, but a great tip to keep in your back pocket any time you have a list that follows a nontraditional sort order. 15. Solve Order & List Formulas: all right for this pro tip. Want to talk about changing these solve order to prioritize your pivot table calculations. Now this is a five star, expert level tip. Odds are this is something that you may never need to use, but it's a helpful tool to have in your back pocket just in case. So where this does come into play is if you're pivot. Table contains multiple calculated items and fields, which could potentially overlap. In those cases, you'd use thes solve Order dialog box to determine the priority in which those formulas are evaluated. So from your pivot table tools, you can drop into your fields items and sets menu and click the solve order button toe access that dialogue box now within that dialogue box window, the last formula in that list will override those above. I'm gonna show you an example of exactly why that's so important in our demo. In just a second now, you may have noticed that within that fields, items and sets dropped down. There's another option. They're called list formulas, and what list formulas actually does is generates in new tab in Excel to document all of your calculated items, and fields as well as that sort order. So that could be a really helpful tool for providing documentation to your end users, especially if you're dealing with very complex tables. So to recap here, number one use case determining which calculations should take priority in conflicting cases and number two again. If you need to produce some documentation for your pivot tables, especially if they contain a large number of calculated items and fields that list formulas , Tool is a great way to do that. So with that, let's hop into our demo and excel, and I'll show you exactly when, why and how this solve order tool can be used. All right, So go ahead and open up your pro tip workbook. If you're following along, head to your table of contents and we're looking for the pivot table tips Gray section here , specifically the solve order and list formulas demo. So go ahead and link out to that sheep, and what we find is what seems to be a pretty simple pivot table layout built on our I. M. D B movie database. We're looking at gross revenue values here, broken down by language on rose regions on columns catches. We also have some calculated items here at play as well. Specifically, this percent English calculation in Roast seven and this North America Regional calculation here in column D. Now, to see what's going on here, we can drill into our pivot table tools, fields, items and sets and open up our solve order dialog box. Here. You can see that there are indeed two of these calculated items that are present in our layout percent English, which is taking the English values and dividing by the total across all four languages English, French, Japanese and Spanish. And then we have this North America regional calculation which is taking the Canada values and adding them to the USA values. Now remember how this solve order dialog box works. The value in a cell is determined by the last formula in the solve order. And right now that North America formula lives beneath the percent English calculation. So North America, in this case, will take priority. So let's close out of this box. Return to our pivot, show you what's going on here. So at this point are percent English calculated item. It's technically supposed to impact thes three cells That's highlight in green B seven, C seven and D seven at the same time are North America. Calculation is impacting these cells d 3456 and seven and note the Intersect here, which is one cell that's D seven. So you've got two conflicting things happening right here in D seven. You're percent English. Calculation is telling Excel, Hey, take this value in D three and divided by the sum of D 3 36 and give me the percentage. At the same time. The North America calculation is saying, No, no, don't do that. Take the value in B seven and just add it to the value in C seven Now both of those calculations can't be true. It can't happen at the same time, and that's exactly where the solve order comes into play. And remember that because the North America formula occurred lower in the list, that one takes priority. And in fact, that's exactly what's happening here. We're just adding these two percentages, which generates 1 98.69% and that doesn't really make sense here, because the percent of English calculation should be a percentage under 100%. And in theory, that should really be the one to take priority here, as opposed to this North America calculation. So all we need to do is go back to our tools, feels items and sets solved order, and we'll take this English or percent English calculation, and we can't drag. It will click the move down button, moving all the way at the bottom, so it always takes priority. So let's go ahead and click close, and now check it out. That value change from 1 98 to 99.94 which is the correct calculation based on that percent English logic. So that's the solve order and why it's important. Last thing I'll quickly show you is the list formulas option, which you can access right here from that same menu, fields, items and sets. When you click that list formulas, Excel will create a brand new worksheet for you that lists out all of your calculated fields all of your calculated items, as well as the specific solved order in which those formulas get evaluated. So again, very helpful documentation tool, especially when you're dealing with very confusing or complex pivot tables So let's go ahead and delete that sheet. We don't need the documentation right now, and there you have it using the solve order and list formulas tools.