In this video, the instructor demonstrates how to adjust your default PivotTable settings to apply a custom value to cells containing either blanks or error values.
- [Instructor] One quick pro tip as far as number formatting is concerned, there's actually an option that I didn't find out about for many many years that allows you to customize what to do in the case of blank cells. So in this case, let's manipulate our pivot to see some of those blank cells. Now instead of revenue and budget, let's pull budget out so that we're only looking at revenue, and instead of title as a row labels, let's look at the data by country, by genre, as columns. So now we have this two dimensional pivot table where we're looking at data by country in our rows, and by genre in our columns.
And as you can see, we don't have data for every combination of genre and country. So for instance, we don't have records of any action movies in the Czech Republic. Same with adventure, animation, biography. So if these blank cells bother you, and you'd prefer to replace those blank values with something else, there's an option in your pivot table tools that will allow you to do this. All the way on the left, you'll see this options box. Just click on it, and in the layout and format tab, right here in this format section, there's a box that says for empty cells, show.
And right now it's blank. You can change that to anything you want. In this case, I think it's safe to assume that if there's no data, then that's the equivalent of driving zero dollars in revenue, so I'm going to change that to zero, and press okay. And as you can see, now those blank cells populate with zeros. So certainly not critical. Everything will work properly whether I replace those blank values or not, but it's helpful to know that that option does exist.
- Establishing a layer convention and property filters
- Structuring your source data
- How PivotTables actually work
- Number formatting and conditional formatting
- Sorting and grouping options
- Troubleshooting incorrect sorting
- Value summarization and value calculation modes
- Inserting calculated items
- Visualizing data with PivotCharts
- Designing interactive dashboards
Skill Level Intermediate
Learning Excel Data-Analysis (2015)with Curt Frye2h 24m Beginner
Part 1: Introduction1m 8s
1. Getting Started
Getting to know IMDb2m 21s
2. PivotTable 101
3. PivotTable Formatting
4. Sorting, Filtering, and Grouping
5. Calculated Values, Fields, and Items
6. Visualizing Data with PivotCharts
7. Wrapping Up
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.