Of pivot tables, named ranges and excel tantrums

I love excel. Really I do.

And I love pivot tables.

But what I don't love is that !%$!@#$!$@#!@#$ "Can't group by that selection. (nyah nyah nyah!)" dialogue box.

It most often pops up when I want to group by a date range. You know - aggregate by months or days or somesuch.

The good thing is that there is a way around it.

The bad thing is that I do it so infrequently that I'm constantly forgetting how. And, for some reason, 99.99% of the google results are poorly formatted tutorials that have so many ads intersperced, ya can't read the !$#!$#!@#$ advice.


Today I got sick of it. So now, for Your Reading Pleasure (and my forever future reference) I present:

"The Almost Totally (in)Complete Guide to Getting Past the Can't Group By Pivot table error dialogue in Excel 2003"
"At least for grouping dates."

Step 1:
Make sure you ain't selecting blank lines.

I know, I know. If you've "Imported" your excel data, it's soooo much easier to select the columns when you're creating your pivot table.

This is a Bad Thing.

Why? Because when you create the pivot table you won't be able to group by ANY FIELD!!

That's right. For some reason yet unknown to man (but probably obvious to those smarties who regularly develop in multi-dimensional database technology) if a field has blank rows, Excel's pivot table engine barfs.

So...how do we make sure we're not getting blanks?

We can do this one of two ways, either select the explicit range of cells (ie, A1:F23135) OR use a named range.

If this is a one-off, I'd use the range of cells. If this is a table that's going to be updated semi-regularly, I'd suggest using a named range as the source for the pivot table.

The super-niftyness of a named range is simply, you can use a formula to determine how big you want it to be. (ok, ok, I think technically it's a "dynamic named range"...)

To wit, assuming you're a normal human being and your data (or, more properly, it's header) starts at cell A1 of a given sheet, here's how you create a named range that only allows non-blank rows:

1) From the Insert menu, choose Name=>Define

2) You'll get something like this. Well, kinda like this. (you won't have any ranges named yet)



3) In the Names in workbook: section, name your range.

(Get it? You give your range a name so now it's a named ra...nevermind.)

Ok, got the name? Good. Now, in the "refers to:" section, type the following:


*********CautionCautionCautionCaution*********

Weird stuff happens in this particular dialogue box.

Arrows don't work...quite...right.

Yes, arrows. You know, those plebeian controls you use to back up when you type "shetname" and correct it

If you use them, whatever the active cell is on the worksheet, it's address gets put in the box.

So use the mouse and the delete keys...but watch out for them arrows.

You have been warned.

*********/endCaution*********


(resuming where we left off...)

in the "refers to:" section, type the following:

=OFFSET(sheetname!$A$1,0,0,COUNTA(sheetname!$A$A),14)

• sheetname = the name of the worksheet your data is on
• $A$1 = the first cell in your range (think, top left corner)
• 0,0 = the horizontal and vertical offset you want from this cell.
• (So if you put in 5, 8...we'd actually start the range 5 cells to the right and 8 cells below A1. Funky. I know.) No human has ever actually had a use for this bit, but if I hear of anything I'll let you know.
• COUNTA(sheetname!$A$A) = This could be a static number (it actually refers to the number of rows we want) but we're using a formula. Counta returns a count of the nonblank rows in a range...so this essentially says "look at column A and give me a count of how many rows are non-blank for this cell"
  
• The more astute of you have caught the assumption...what if a few rows don't have a value for a? Will they be included? Maybe. Remember...this returns a "count", so if you have 10 rows and 2 of them don't have a value in the A column, you'll only get the first 8 rows. Bummer. So best tag a column that's always populated.
• Where was I? Oh yeah. The last item 14. This is the number of columns you want included in the range. This can also be made dynamic, but in this case I know how many columns. (so there)

4) Now click the Add button.

That's all there is to it.

Now, when you're defining your hypothetical pivot table, when they ask you to define the range, just type the name of the...err...named range you created above and "POOF". (literally - "your spreadsheet will be, like, smokin dude!")


What is that you say? What if your data contains rows that SOME are blank and some are not? How do you deal with grouping incomplete data?

Yes, it's possible. Maybe I'll write that one up tomorrow. Until then, cheerio!