Miscellaneous PivotTable Wizardry

Drill Down


In the Dashboards and PivotTables class, we talked about "how many African-American female seniors are taking AP Calculus?" and questions like that. We changed from the number of students to percent, and stuff like that.

But nowhere in the material do we answer the question, "Who are those students? Can I get a list?"

Yes. Double-click the number, and that will open a new sheet with just those kids.

Not Zero Percent - Blank

Today's Stumper of the Day was, "How do I show blank instead of zero for my percent distribution?" One of our colleagues asked about this when looking at the population distribution of our students - if there are none there, why show 0%? Why not show blank instead?

Go to (Value) Field Settings, click Number, and choose Custom. Your format should read as follows:

##0.00%;(##0.00)%;" "

Custom number formats in Excel take three parts - what do to if the number is above zero, what to do if the number is below zero, and what to do if the number is zero on the dot. Separate with semi-colons.

Thusly, ##0.00% refers to the positive percent. 0.00% means, show at least one zero to the left of the decimal place, and always show two to the right of the decimal. Put a percent at the end. The ## means, add extra digits as needed to display the number appropriately.

In turn, (##0.00)% means, show the negative (below zero) numbers in parentheses, and then the percent sign. So -10% would be (10.00)%.

Lastly, the " " means, show a null for zero. We put the space in quotes, because it's a literal - text requires quotes.

What If the Source Data Changes?

With a PivotTable, it's entirely possible that you'll have to add more rows. You might have to add more rows at the bottom, or delete rows - who knows? The amount of data on which your PivotTable is based may change.

There are two ways to handle this quandary, depending on your version of Excel. I'll address pre-2007 (PC) and pre-2011 (Mac) first.

Pre-Excel 2007 (PC) and Pre-Excel 2011 (Mac)

Before you even create a PivotTable, go back and do this step first. Create what's called a dynamic named range, so that the data source shrinks and grows with the data in the sheet.

Go to cell A1, then Insert, Name, Define.

The name should be something like Data.

For your Refers To, enter the following formula:
=OFFSET(A1,0,0,COUNTA(A:A),COUNTA(1:1))

Then to the PivotTable. The source of your PivotTable is no longer SheetName!$A$1:$J$256, for example. Instead, your source is Data.

In short, this named range says, "Start at A1, and then make the range as high as there are rows, and wide as there are columns." It's completely flexible.

Dave Hawley does a great job of documenting it here

Excel 2007/2010 (PC)

Go to the Insert tab, choose Table. Let Excel make a table out of your data. Try it, you'll like it. Click OK.

When the Table Tools tab shows up, click Summarize with PivotTable. The table will expand and contract automatically.

Excel 2011 (Mac)

Click the Table tab, then click New. If prompted, press OK. Then, click Summarize with PivotTable.

Miscellaneous Function Wizardry

Select More Cells Faster

If you want to highlight ALL the cells that are being used in a worksheet (so you can refer to them in a formula) use SHIFT+CTRL+END; that will select left and right, as well as up and down, at the same time.

Three Lists and Nary a Match

In our class exercise, we have three lists that don't add up: the State list, the School list, and the District list.

To find out whether or not a student on the District list shows up on the State list, it's pretty straightforward:
=MATCH(A2,'State List'!A$1:A$A500,0)

In turn, to find out whether or not a student on the District list shows up on the School list, it's equally straightforward:
=MATCH(A2,'School List'!A$1:A$A365,0)

How about the kids that show up on one, both, or neither? This might work:
=IF(AND(ISNA(D2),ISNA(E2)),"Not on either list",IF(ISNA(D2),"Missing from state list",IF(ISNA(E2),"Missing from school list","On both lists")))

Just a little IF/AND/ISNA logic put together. Remember, ISNA will return TRUE if the lookup function will return an #N/A error, and FALSE if the value is found.

Restricting Data Entry in a Cell Excel 2007/2010 PC, Excel 2011 Mac

Click the Data tab, then click Data Validation.


You'll have a few options to choose from:
  • Whole number (between, greater than, greater than or equal to, less than, less than or equal to) - 1 or 2 might be OK, but 1.5 isn't
  • Decimal (same options) - 1 or 1.5 is OK, if it's within threshold
  • List - you determine what values you want to show up; the list might be a list of teachers - you write it like this: Moreland, Dutton, Gonzales
    Remember that with List, you can also refer to a set of cells (on the same sheet) or a named range (on another sheet)
  • Date (same options as number) - but it has to be a valid date - no February 31st
  • Time - same restrictions as Date
  • Text length - it can only be 3 characters, 7 characters, 9 characters
  • Custom - one way to try to avoid entering duplicates - =COUNTIF($A$1:$A$500,A1)=1