Ultimate Excel Course #11 - Master PivotTables for Powerful Analysis and Reporting

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
14 Lessons (1h 8m)
    • 1. Introduction

      3:20
    • 2. Creating a PivotTable

      8:09
    • 3. Changing the Values Function

      3:19
    • 4. Formatting Values the Efficient Way

      2:19
    • 5. Creating a PivotTable Style

      5:10
    • 6. Sorting PivotTable Fields

      2:36
    • 7. Grouping Data in a PivotTable

      4:55
    • 8. Show Values as Percentage, Difference and Ranking

      5:10
    • 9. Conditional Formatting with PivotTables

      3:41
    • 10. Filtering PivotTable Data

      3:59
    • 11. Refreshing a PivotTable

      4:48
    • 12. Creating a PivotChart

      5:22
    • 13. Using Slicers for Interactive PivotTables

      11:55
    • 14. Using the Timeline Slicer

      2:54

Project Description

Complete the following steps on the pivottable-exercise file. 

  1. Create a PivotTable from the Expenses table.

  2. Move the Category field into the Rows area, and the Amount field into the Values area.

  3. Sort the Category field into largest to smallest by the Amount.

  4. Format the amounts into an accounting format.

  5. Create another PivotTable from the Expenses table (or copy the existing one).

  6. Move the Date field into the Rows area and the Amount field into the Values area.

  7. Group the Date field into Months and Years (if necessary, as it may be done automatically).

  8. Format the amounts in an accounting format.

  9. Create a PivotChart from this PivotTable. Use a line graph to show the expenses over time.

  10. Remove the Field Buttons from the PivotChart. Also remove the Legend and Chart Title. Re-size the chart if needed so it looks good.

  11. Change the colour of the Line to something you prefer.

  12. Rename both PivotTables. The first one to ByCategory and the second one to ByMonth.

You can see an example of the end result in the pivottable-exercise-complete file.

Resources(1)

Student Projects