If you try to group pivot table items in Excel, you might get an error message that says, “Cannot group that selection.” For older versions of Excel, if you had a problem grouping pivot table items, it was usually caused by blank cells, or text in number/date fields. For Excel 2013 and later, there’s another thing that can prevent you from grouping — the Excel Data Model.
Count Items in a Cell with SUBSTITUTE
Do you use the Microsoft Excel SUBSTITUTE function very often? It’s a handy way to count items in a cell, when they’re separated by commas or spaces. The examples below show different ways to use this function – have you tried the variation in the last example?
Scroll Through Filter Items in Excel Table
To see specific data in an Excel Table, you can select an item from the drop down filter in a column heading. Someone asked me if there was a way to scroll through the items, instead of opening the filter list each time. This technique uses a pivot table, which could be hidden on a different sheet, and a spin button, to go up or down in the list of items.
Continue reading “Scroll Through Filter Items in Excel Table”
Advanced Excel Training Master Bundle
This offer has expired.
Please see the Contextures Recommends page for Excel training recommendations.
Excel Roundup 20171214
Here is an end-of-the-year Excel Roundup, with articles that I’ve read recently. To get weekly links and articles, sign up for my weekly Excel newsletter. Merry Christmas, and happy holidays, and come back for a new blog post in January!
What Are the Rules for Excel Names?
As Juliet famously said to Romeo, “What’s in a name?” And she was talking about rows (misspelled as “rose”), so maybe Juliet was using a spreadsheet at the time. There are special rules for Excel names, but you might be surprised to see what is allowed.
Excel Holiday Planner Workbook
Long ago, I created a Christmas planner in Excel, and it has evolved over the years. Well, tomorrow is the first of December, so you’ve probably finished your planning for this year. But, if you haven’t, this year’s new and improved version of the workbook might help. Now it’s an Excel Holiday Planner – you can pick any date as your holiday, instead of being Christmas themed.
Do You Still Need Excel Subtotals?
Excel has a SUBTOTAL function, which ignores hidden or filtered rows. There is a Subtotal feature too, that quickly groups your data, and adds one or more rows of subtotals. Do we still need these Excel subtotals though, now that we have pivot tables and the AGGREGATE function?
Excel Data Validation Troubleshooting
How many data validation rules do you have in the giant Excel workbook that you use every morning? If you inherited that file from somebody else, you might not have any idea what’s on some of the sheets. Your workbook could be slowing down, or maybe it’s even crashing, and you’re not sure why. Use these macros for data validation troubleshooting, and see if you need to clean things up.
Create a Tartan Pattern in Excel
Suddenly, it’s November, and it’s time to plan what you’ll wear on St. Andrew’s Day (November 30th). He is the patron saint of Scotland (and other countries), so of course you’ll need a kilt, made in your family tartan. Fortunately, you can create a tartan pattern in Excel, if your family doesn’t have a tartan. Thanks to John Marshall for creating this handy tool.