 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
| |
See Also:Excel Subtotals ||Making the SUBTOTAL Function Dynamic || Bold Excel Subtotals Automatically ||Sum Every Nth Cell || Count of Each Item in a List || Excel Pivot Tables
Grouping Fields
Grouping data in an Excel Pivot Table can at times be very frustrating as when you try to group, Excel can tell you that it "Cannot Group that Selection", is "Unable to Group" the particular field you are trying to group, or the result of the grouping is not what you expect. Let's address these common problems and tell you the reasons as to why they occur.
Cannot Group that Selection
The first problem "Cannot Group that Selection" is normally caused by one of two things. The first, and most likely cause, is due to the fact that your Pivot Table data range includes blank cells within the field in which you are trying to group. For example, if you had a column for dates for Date of Birth and within that column somewhere there was only one blank cell, Excel would not allow us to group by Date of Birth. This is because Excel does not know what it should do with the one blank cell in regards to grouping with date cells. Basically, what this means is your column must:
Obviously, it will be quite common once you have created a Pivot Table of data that you would be adding new information to that table on an ongoing basis. The temptation is always there to make your data range for your Pivot Table extend well past the last row of information in your table and thus include many blank cells. Obviously you would do this so when you add more information to your table it would automatically be included in your Pivot Table the moment you refresh it. Extending the data range of your pivot table well beyond the last row in your table will not only cause grouping problems, but also force Excel to reserve excess memory for all of the blank cells you have included. This, depending on how many extra rows you include, can end up increasing your file size by 100% or greater. The solution to this common problem is quite simple and comes in the form of using a dynamic named range .
The other common reason for not being able to group data is that you have both text and numeric data in the same field. This one can be a little bit hard to spot at times, but just remember by default numbers are right aligned and text is left aligned. Normally, simply going back to your data table, selecting the column which contains what you think is numeric data, changing the alignment to General under Format>Cells>Alignment will force Excel to return the numbers to their original state, although this will not change their format. You may have to widen your column a little, to actually see whether your data is left aligned (text) or right aligned (numbers). For more details on dates & numbers seen as text see: Convert Dates || Excel Calendar for Valid Dates ||Excel Date and Times || Converting Text Numbers to Real Numbers
Excel Pivot Tables |Free Excel Pivot Table Tutorial From Microsoft |Pivot Table reports 101 |25 easy Pivot Table Reports |About Using Microsoft Query to Retrieve External data |Create a PivotTable report |Tips for Working With Pivot Tables These pages and all content is the sole property of Microsoft.
Pivot Table Assistant for Microsoft Excel | Pivot Table XL Stat |O2OLAP for Excel
Still Need Help? FREE Excel Help Here!
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader PackageTechnical Analysis in Excel With $139.00 of FREE software!
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates