In the first two parts of our Advanced Budget Project for Excel 2016 in Windows, we set up the Budget itself and then created a Ledger table to keep track of our expenditures. In the Ledger table, we set up filters using Excel’s Slicers so that we could see the total expenditures for each expense category and each month. The Slicer buttons are slick, but it will be tedious to use them to examine every single category and every month. There must be a better way.
And there is, of course. The fastest and easiest way to summarize a table is to use Excel’s PivotTables. This amazing feature has somehow obtained the reputation of being difficult, but I will let you in on a secret: Even though Pivot Tables are incredibly powerful, they are also surprisingly easy.
In this third part of our Advanced Budget project, we will set up a Pivot Table to see how the expenditures in our Expense Ledger are doing. In just a handful of mouse clicks, we’ll be able to peruse a summary of total expenses for all categories and months at the same time. We’ll also use two Excel features to give us a visual summary of our expenditures. Sparklines will show us a basic look at how our expenditures vary month to month, while a Pivot Chart will give us a much more detailed look.
Once you create a Pivot Table, you will be amazed at how easy it was. But don’t tell anyone. Let everyone continue to believe that this feature is too difficult for most people. And, of course, they will all be very impressed when you create them so quickly.
For instructions on Creating a PivotTable, click here.
For complete instructions on the Advanced Budget Project along with other projects in our Advanced Excel Course, click here.
— Rich Malloy