From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Pro tip: Formatting empty cells - Microsoft Excel Tutorial

From the course: Excel PivotTables: Mastering PivotTables and PivotCharts

Start my 1-month free trial

Pro tip: Formatting empty cells

“

- [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.

Contents