{"id":24263,"date":"2021-12-01T08:48:35","date_gmt":"2021-12-01T07:48:35","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=24263"},"modified":"2024-04-11T20:32:52","modified_gmt":"2024-04-11T18:32:52","slug":"errors-when-grouping-by-dates","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/errors-when-grouping-by-dates\/","title":{"rendered":"Errors when grouping by dates"},"content":{"rendered":"

\"Filter<\/a><\/p>\n

With Pivot Tables<\/a>, we usually group dates to aggregate our sales numbers. And sometimes we get these dreaded errors when grouping by dates :<\/p>\n

\"Errors<\/p>\n

The most common reason for facing this problem is that some of the cells contain invalid data<\/strong>. The date column might contain blank cells<\/strong>, text values,<\/strong> or an error<\/strong>.<\/p>\n

Unfortunately, this message does not help you with the reason behind the error. So, you need to find ways to spot those errors and fix them one by one!<\/p>\n

Let us quickly get to the bottom of those errors when grouping by dates.<\/p>\n

 <\/p>\n

Exercise Workbook:<\/strong><\/em><\/p>\n

\n

Download excel workbook<\/i><\/span>Errors-when-grouping-by-dates.xlsx<\/span><\/a><\/strong><\/p>\n

\n<\/div>\n

\n

This is our current Pivot Table<\/a> setup:<\/p>\n

\"Errors<\/p>\n

 <\/p>\n

Method 1: Use Go To Special<\/strong><\/span><\/p>\n

STEP 1:<\/strong><\/span> Right-click on any date in the Pivot Table and select Group<\/strong><\/p>\n

\"Errors<\/p>\n

We get the “Cannot group that selection<\/strong>” error message. There is something wrong with our dates in the data.<\/p>\n

\"Errors<\/p>\n

STEP 2:<\/strong><\/span> Let us look for errors! Select the\u00a0ORDER DATE\u00a0<\/strong>column and press\u00a0CTRL + G<\/strong> then click\u00a0Special<\/strong><\/p>\n

\"Errors<\/p>\n

STEP 3:<\/strong><\/span> Select\u00a0Constants,\u00a0Text<\/strong>, and Errors<\/strong>.<\/p>\n

This will get the dates that have errors.\u00a0Click OK.<\/strong><\/p>\n

\"Errors<\/p>\n

STEP 4:<\/strong><\/span> The errors are now selected although we cannot see them. Click highlight and select any color.<\/p>\n

\"Errors<\/p>\n

STEP 5:<\/strong><\/span> Click on the arrow beside the column and select\u00a0Filter by Color<\/strong><\/p>\n

\"Errors<\/p>\n

You will now see the dates with errors. Let’s make a quick fix with these 4 dates.<\/p>\n

\"Errors<\/p>\n

Now the dates are fixed.<\/p>\n

\"Errors<\/p>\n

STEP 6:<\/strong><\/span> Right-click anywhere on your Pivot Table and select Refresh<\/strong><\/p>\n

\"Errors<\/p>\n

STEP 7:<\/strong><\/span> Right-click on any date in the Pivot Table and select Group<\/strong><\/p>\n

\"Errors<\/p>\n

It is working now!<\/p>\n

\"Errors<\/p>\n

 <\/p>\n

Method 2: Use Filters<\/strong><\/span><\/p>\n

A quick and easy way to find invalid data in the date column is to use the filter buttons<\/strong>. The filter dropdown will group all valid date values<\/strong> and all the dates containing errors will be left at the bottom<\/strong> of the list (ungrouped).<\/p>\n

This is because Excel will not recognize those error values as dates and will store them as text.<\/p>\n

In the example below, let us use the filters to find out the cells containing invalid data!<\/p>\n

STEP 1:<\/strong><\/span> Click anywhere on the table.<\/p>\n

\"Errors<\/a><\/p>\n

STEP 2:<\/strong><\/span> Go to Data<\/strong> > Filter<\/strong>.<\/p>\n

\"Errors<\/a><\/p>\n

STEP 3:<\/strong><\/span> Click the filter button<\/strong> for the ORDER DATE column.<\/p>\n

\"Errors<\/a><\/p>\n

As you can see all the correct dates have been grouped and the incorrect ones have been listed below.<\/p>\n

\"Errors<\/a><\/p>\n

STEP 4:<\/strong><\/span> Uncheck<\/strong> the date groups and click OK.<\/p>\n

\"Errors<\/a><\/p>\n

Now the column will display only the incorrect dates.<\/p>\n

\"Errors<\/p>\n

Let us quickly fix them and see how the grouping feature starts working again in no time!<\/p>\n

\"Errors<\/a><\/p>\n

 <\/p>\n

Conclusion<\/h3>\n

In this article, you have learned how to fix errors when grouping by dates in Excel Pivot Table<\/a>. Did you know there are many creative ways of doing grouping in Excel Pivot Tables?\u00a0Learn all about it here!<\/a><\/strong><\/p>\n

 <\/p>\n

Make sure to download our FREE PDF on the\u00a0333 Excel keyboard Shortcuts here<\/a>:<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"

With Pivot Tables, we usually group dates to aggregate our sales numbers. And sometimes we get these dreaded errors when grouping by dates : The most common reason for facing this problem is that some of the cells contain invalid data. The date column might contain blank cells, text values, or an error. Unfortunately, this […]<\/p>\n","protected":false},"author":2,"featured_media":25269,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"cos_headline_score":0,"cos_seo_score":0,"cos_headline_text":"Errors when grouping by dates","cos_headline_has_been_analyzed":false,"cos_last_analyzed_headline":[],"om_disable_all_campaigns":false},"categories":[4],"tags":[829,831,830],"acf":[],"_links":{"self":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24263"}],"collection":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/comments?post=24263"}],"version-history":[{"count":0,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/posts\/24263\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media\/25269"}],"wp:attachment":[{"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/media?parent=24263"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/categories?post=24263"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.myexcelonline.com\/meo-staging\/wp-json\/wp\/v2\/tags?post=24263"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}