I have raw data that includes hundreds of employees who have completed many different training courses and I am needing to insert a pivot table that shows the course name as the column labels, the employee name as the row/filter and rather than return a sum or count of value, I need it to show the date that employee completed the course (which is in the raw data) Is there a way to do this in Excel 2010 using pivot tables? Does anyone have a better suggestion?
Excel Pivot Table – How to return a date in the row value
datemicrosoft excelmicrosoft-excel-2010pivot tableworksheet-function
Related Solutions
- Right-click on a cell in your month and/or year column, choose "Field Settings"
- Layout and Print tab
- Uncheck "Show items with no data"
P.S. This will hide months with no data within your desired period as well.
Also, since you control the source data, I find that adding permanent columns for years and months is great. For the months, I'd recommend having them as "2011-01-01, 2011-02-01, 2011-03-01" in your data and format the field as desired ("mm" or "mmm", etc.)
This can let you have huge amounts of data - just use Year and Month as row labels and/or filters to get the periods you want without the < and > totals that groups make.. Since they're raw data, the PT will always be simple and work predictably. It also lets you use features not-available on groups, such as regrouping them further in seasons or however you wish.
What you are looking to do is "depivot" your data, then repivot it to another layout. This link is very useful, for the "depivot" step. I've used this technique for years and it's truly a time-saver!
Here's how to depivot your data.
1.With your layout as shown, hit Alt+D,P. This will bring up the old Excel 2003 Pivot Table Wizard, which looks like this. Select "Multiple consolidation ranges" then "Next": 
2.Choose "I will create the page fields", and "Next":
3.Highlight your range so that it appears in the upper "Range" box, then click 'Add' to copy that range to the "All ranges:" box. Click "Next".
4.Choose where you want to put the Pivot table. I usually choose "New Worksheet"; click "Finish". You'll get this:
5.Uncheck "Row" and "Column". You'll be left with a one-cell pivot table:
6.Double-click the one cell. You'll get your data arranged in a table format on a new sheet. You can now use the table as the basis for your new pivot table layout. Note carefully the placement of the fields in the "Row labels", "Column Labels" and "Values" areas:
Best Answer
Is this what you are maybe looking for. If it is, the assumption here is that one individual only went on a specific course once. Therefore under value field settings you can pick either sum or max or min and then format the numbers of the pivot table as dates.