Excel Error – Pivot Table Does Not Group Dates Properly or Give Errors

I work with large data in spreadsheet and pivot table comes very handy in summarizing  the data. However, often I encounter with an error  when I try to group the multiple sets of date.

Types of Pivot Table Date Grouping errors

  • Pivot Table does not Group dates properly – It gets grouped into just one Group namely ‘Group 1.’
  • Sometimes it gives an error that that data set can’t be grouped.

Various reason for not grouping dates

  • Dates are in text format or non-date format
  • There are extra characters hidden in some of the cells of date column
  • Date locale issue – your locale (PC) uses DMY date format and the text date is using MDY date format (or the other way around)
  • Selection Issue

How to fix this error?

a) Convert Text Formatted Dates into Real Date Format

  • Select the date column
  • Hit Ctlr + H to open ‘Find and replace’
  • Type / (forward slash) in ‘Find what’ and in ‘Replace with’
  • Hit Replace All
  • All Text formatted date will be converted into Real date format

Type / (forward slash) in ‘Find what’ and in ‘Replace with’

b) Remove extra characters in some of the cells of date column

  • Select the date column
  • Hit Ctlr + H to open ‘Find and replace’
  • In Find what space, Type Alt key, and then type 0160. Nothing will type in space and it will remain empty. Also, Keep Replace with space blank.
  • Hit Replace All
  • All Text formatted date will be converted into Real date format
  • Also, make sure to manually remove any visible funny characters, words, alphabets.

1) Type Alt key 2) Type 0160, Nothing will type in space 

C) Fix Date Locale Issue

  • Select the date column
  • Go to menu >  Data > Text to Columns, select Delimited options and hit next
  • Keep everything blank in this window and hit next
  • Select date and in drop down menu select the date type that your system is using. You can use trial and error method by selecting different format and see what works for you.
  • Hit finish
Go to Data> Text to Columns, select Delimited and hit next.
Keep everything blank in this window and hit next
Select date and in drop down select the date type that your system use. You can do trial error by selecting different format and see what works for you.

D) Fix Selection Issue

After creating Pivot table if you do not select date properly then also it may not group as per month and year.

Sometimes, if you select dates not properly then it will get grouped into just ‘Group1’

To fix this selection issue. All you need to do is select any cell and take your cursor to lower part of selected cell until a downward arrow appears and then click to select all other cells.

1) Select any cell 2) Take your cursor at lower part of the cell until downward arrow forms 3) Click to select rest of the cells
All cells will selected like this then right click and hit Group

Please let me know if all these tricks solves your Pivot table date grouping issue or not.

How to Fix – “Be careful parts of your document may include personal information” Error Message in Microsoft Excel

Lately, my Excel file started popping-up an error messages. It’s so annoying as it pops-up every time I save the file which I do like every 30 seconds.

“Be careful parts of your document may include personal information that cannot be removed by the document inspector.”

I fixed this error as below –

Step 1 – Open your spreadsheet and go-to File

 

Step 2 – Click Options

 

Step 3 – Go-to Trust Center

 

Step 4 – Click Trust Center Settings

Step 5 – Click Privacy Options and Un-check the box that says “Remove personal information from file properties on save” and click OK.

 

Done. That annoying message will now stop bothering you.

How To Easily Copy Excel Sheet Table With Rows And Column Headings In Word Document?

Here is a short video tutorial on how to easily copy Excel sheet table with rows and columns in word document.

If you have any further query or any other problem related to word and excel spreadsheet please feel free to drop your question in comment section.

How to open a CSV file if you don’t have MS Excel installed?

When I joined my new company, I was given a Mac and was asked to do all my works with that. That Mac didn’t have MS office and it was not allowed to install as well. So I was finding it very difficult since I had to deal with MS excel and CSV files.

Ultimately I found the solution of my problem, I am sure its a common problem for many people and therefore I would like to share it.

How to open a CSV file if you don’t have MS Excel installed?

It involves two easy steps:

  1. Upload on Google drive
  2. Once uploaded, open it with Google spreadsheet

Upload on Google drive

– Go to drive.google.com and click on New, then select upload files option and choose your CSV file to upload.

– Open it with Google spreadsheet

– Once the upload is complete, just click on the file and it will open in a Google spreadsheet and Yay… you are all set to go.

Important: Just remember one thing, once you have uploaded the file, try to rename it, else later you might not be able to find it. So either rename it to something which you can remember, or add the link to any other spreadsheet, or add it to any folder which you remember.

Still confused? Watch this video.