Custom column in Excel pivot table

0
votes

I am using a pivot table to display data. In the pivot table I have countries and under each country couple of names. Then how many training a person led and how big attendance there was.

I have two questions:

1) In the data source, I created additional column where is displayed the last training date (latest date) for every person. Meaning that in the source data, in every row for one person, there is always the same "Last training date". I would like to take this information a show it in the pivot table. But anywhere I put it, it's always used as another filter and it makes the table much more complex. I would like to have just a custom column with this info without having to expand the whole table. It's the last column in the example image.

2) Same applies for the attendance. If the training has been cancelled, the attendance is always 0. Is there a way to hide the whole column? Same for the total of attendance. I don't really need these columns.

enter image description here

1
excelpivot-table
Why not have a column indicating if training was cancelled and use that as a page filter so that only training that wasn't cancelled shows? And can't Last Date go in the rows labels to the right of CZE.....? You can use pivot report layout settings and put to Show in tabular form. – QHarr

1 Answers

0
votes

Try This:

First question:

  1. In the main table change the [Date Field] to type Number
  2. Put the [Data Field] in values into the pivot table
  3. Select any value where is the column [Date Field] and right click:
  4. Go to Summarize values by select Max
  5. Again right click. Go to Number Format...
  6. Select the date format what you like

Second question:

I would like to see a data's sample. It could be fake. I have some ideas but is difficult to see just guessing...