Got that sinking feeling (again) when you get the error message “Cannot group that selection”? Especially on a date item. You know you’re not alone, and have fixed this a thousand times perhaps, but it still keeps coming up. Why can’t Excel simply recognise this blatantly obvious date and group it accordingly?!
So many things can cause this but it all comes down to the same thing – the underlying datatype is not what you think, i.e. it’s not really a date!
If the data is coming from a database, you could go cap in hand to your DBA (you do have one or a contact, right?) and ask for a better datatype – but they will probably explain you are getting the right type – and they’re probably right. Something is going wrong once it hits Excel.
You can try changing the Excel datatype in the ribbon’s Table Tools -> Design -> Number panel:
That sometimes works… but usually doesn’t! Which is why you’re probably reading this. Better to use an old trick in Excel to force the date change – it’s often the only way to hammer it home… use the “Text to Columns” button and go through the dialogue choosing YMD for the format.
You’ll know it’s done the trick when the column’s dates all shift over to be right justified instead of left. Now you just have to be careful, because if it’s an external datasource and you hit refresh to re-load the pivot, it will revert back to old date format! So don’t – just go to the pivot and use the drop-down to only refresh the current page, ie the pivot. That should do the trick!
You may be better off ensuring a better datatype coming through from the database – that sly DBA can actually ensure that, but it’s not obvious because it’s a slightly arcane datatype that’s required, not the usual one. Good ol’ Microsoft (or whichever company provides your database).
If you really want to avoid going round in circles each time, let us develop your pivot in PowerPivot or even better, PowerBI. We go right back to the database SQL to ensure your users don’t fall over this kind of hassle. Just drop us a line.
