ACCME Annual Reporting – Pivot Table

Modified on Fri, 16 Jan at 11:23 AM

How to create the ACCME Annual Report as a pivot table in excel

Rename the first sheet RAW.
- RAW will be the original data that was received and will be kept as a back up incase there are any issues.
Create 1 new sheet, Pivot Table Data.
- Pivot Table Data will be the data set used to be displayed in the Pivot Table.
Copy all of the columns and rows from RAW into Pivot Table Data
Pivot Table Data will require some formatting changes to get the data to display properly in the Pivot Table. Make the changes outlined below.
1. Course Publication Date column
  1. Insert a new column after Course Publication Date (typically column H), this will be our helper column.
  2. use the following formula to extract the date
```
=DATE(VALUE(MID(H2,8,4)),MONTH(DATEVALUE(LEFT(H2,3)&" 1")),VALUE(TRIM(MID(H2,5,2))))
```
  3. Apply the formula to the rest of the rows.
  4. Copy the data in that column (excluding the header) and paste the values into the Course Publication Date column.
  5. Select all of the rows in the Course Publication Date column (excluding the header), then right click those same selected cells and select Format Cells.
  6. Format the cells as a Date for Category with the format 03/14/12
  7. Delete the helper column you inserted
2. ACCME Activity ID column
  1. Select the first row with data in it then select the rest of the rows. After selecting all of the rows go back to the top and click the yellow warning icon.
  2. Select Convert to Number
3. Credit Amount Claimed column
  1. Select the first row with data in it then select the rest of the rows. After selecting all of the rows go back to the top and click the yellow warning icon.
  2. Select Convert to Number
  3. Right click the selected data and select Format Cells as Number for Category and 2 for Decimal Places.
4. Max Credit Amount Allowed column
  1. Select the first row with data in it then select the rest of the rows. After selecting all of the rows go back to the top and click the yellow warning icon.
  2. Select Convert to Number
  3. Right click the selected data and select Format Cells as Number for Category and 2 for Decimal Places.
5. Insert the following columns
  1. PhysicianCMEUser, formula
```
=IF(AND(D2="Physician", L2="CME"), 1, 0)
```
  2. NonPhysicianCMEUser, formula
```
=IF(AND(D2="Non-Physician", L2="CME"), 1, 0)
```
  3. PhysicianCMECredit, formula
```
=IF(N2=1, J2, 0)
```
  4. NonPhysicianCMECredit, formula
```
=IF(O2=1, J2, 0)
```
  5. ABPUser
    , formula
```
=IF(E2="ABP", 1, 0)
```
  6. ABPNUser, formula
```
=IF(E2="ABPN", 1, 0)
```
  7. ABIMUser
    , formula
```
=IF(E2="ABIM", 1, 0)
```
  8. ABPCredit
    , formula
```
=IF(R2=1,J2,  0)
```
  9. ABPNCredit
    , formula
```
=IF(S2=1,J2,  0)
```
  10. ABIMCredit
    , formula
```
=IF(T2=1,J2,  0)
```
Setup the Pivot Table work sheet
1. Click the Insert tab in the top ribbon.
2. Click the Pivot Table option and use the From Table/Range Option
3. Highlight all the cells that have data in them including the headers starting at Course Name to the end
4. Use the New Worksheet option and click OK
5. Under PivotTable Fields
  1. Drag Course Name to the Rows section
  2. Drag Course Publication Date to the Values section
    1. Click the drop down next to it
    2. Click the Value Field Settings
    3. Change the Summary to Max
  3. Drag ACCME Activity ID to the Values section
    1. Click the drop down next to it
    2. Click the Value Field Settings
    3. Change the Summary to Max
  4. Drag Max Credit Amount Allowed to the Values section
    1. Click the drop down next to it
    2. Click the Value Field Settings
    3. Change the Summary to Max
  5. Drag PhysicianCMEUser to the Values section
  6. Drag NonPhysicianCMEUser to the Values section
  7. Drag PhysicianCMECredit to the Values section
  8. Drag NonPhysicianCMECredit to the Values section
  9. Drag ABPUser to the Values section
  10. Drag ABPNUser to the Values section
  11. Drag ABIMUser to the Values section
  12. Drag ABPCredit to the Values section
  13. Drag ABPNCredit to the Values section
  14. Drag ABIMCredit to the Values section