I often create PivotTables in MS Excel. I have had a recurring hassle when I use dates and want to analyse data by month. If I simply format my column to show only month and year, the pivot table will not group my information together.
Historically I would create a new column, type in the month, and then copy and paste for each row in that month. But I have now found a much better way to work around this problem:
- Select the column heading for the date column (e.g. column C).
- In the menu bar select Format > Cells. Select the Custom category. Enter your preferred format in the Type field (e.g. mmm yy). Click OK.
- Press the Ctrl + A keys on the keyboard to select all text. Press Ctrl + C to copy.
- Open a blank MS Word document. Press the Ctrl + V keys to paste the text. MS Word will represent the dates with your preferred formatting.
- Press the Ctrl + A keys again, then Ctrl + C keys again to select and copy the text.
- Return to MS Excel. Press Ctrl + V to paste.
- Create your PivotTable. The dates will now display in the correct format.