Pivot tables are an amazing tool for understanding large amounts of data…like transaction or summary data from your general ledger.
In this example, we use general ledger data (account number, description, amount, and month). Here is a sample of the data:
Create our pivot table
- Insert tab> Pivot table
- Select columns A:D. Your data my be columns and rows like A1:D100 for example.
- Right click inside of blank pivot table > Pivot table options
- Display tab. > Classic PivotTable (my preference. Gives you drag and drop).
- Now you are able to drag and drop the fields into the blank table.
- Drag “Major” to the far left.
- Drag “Description” to the right of Major.
- This adds a total line. We want to remove this. Double click on the “Major”
- Subtotals & Filters > Check “None”.
- Now drag “Amount” into the columns field.
- If it defaults to “Count”, then double-click the “Count of Amount“.
- Change to Sum. From here you can fix the formatting to currency.
- To get the months. Drag over the “Month” column to the top of “Total”. Highlighted here in yellow.
After you drop the “Month”, it should look like this:
The finished product. In doing this, you might get rows and columns listed as “Blank”. To remove these, just use the drop down and un-select “Blank”.