These could be statements for your bank account that show withdrawals and deposits. Or it could be credit card statements showing purchases and refunds. It could also be expense reports or any other type of statements that have debits and credits. We'll learn how to create totals for those debits and credits and then make a summary report of the data using a pivot table. Here's how.
Beginning with statement data that you've imported from your bank or credit card company, the first step is to change the format of the data to a table. With any cell in the data selected, go to Format as Table on the Home tab of the Ribbon. Then choose whatever color/design scheme you like best. I've compiled 5 Reasons to Use an Excel Table as the Source of a Pivot Table to explain why we take this first step.
Start by selecting Summarize with Pivot Table, located on the Table Design tab. Then hit OK to put the pivot table on a new worksheet. (If it's been a while since you've worked with pivot tables or your not familiar with them, you can check out my tutorial series here: Introduction to Pivot Tables and Dashboards.)
From here, we can build out our pivot table by dragging fields into the different areas of our table. I put the Category field in the Rows area. Then I added the Debits and Credits field to the Values area. A natural question, once you have your debits and credits showing in your summary report, is how can I add them together? I'm going to show you two ways to go about that.
With any cell selected in the pivot table, go to the Pivot Table Analyze/Options tab and open the Fields, Items, & Sets drop-down menu. Choose Calculated Field. This opens a window that allows us to create a formula for a field that will add the debits and credits together. Name the field “Total” or whatever you like. Then create the formula by double-clicking Debit, typing the plus symbol ( + ) and double-clicking Credit. Then click Add to add this new field to the field list. 
Note: This formula works when the debits are listed as positive numbers and the credits are negative in your table. If your data has those reversed, or both are listed as positive entries, simply adjust your formula as needed (changing the plus to minus, etc.) When you hit OK, you will see the Sum of Total column added to the pivot table. 
The great thing about this set-up is that it is really flexible for manipulating and changing. We can add or delete fields within the pivot table, or change the layouts and filters, and the calculated field will still work.
For this option, we are making our adjustment to the source data range, not the pivot table itself. We'll add a column to the source table (I've labeled it “Amount”) and then create the same simple formula that adds the debits to the credits. Because it's an Excel table, that formula will carry all the way down the column and give you the totals for each row. Now that the column is added to the source data, you'll also see it in your list of fields to work with after you refresh the pivot table. (Keyboard shortcut for the refresh is Alt + F5 .) Now you can move the new Amount field to the values area and see it appear in your pivot table.
Both options give you the exact same results in terms of your pivot table. The calculated column method has a slightly better advantage in that the totals being added to the source data might be helpful if that data is also being used to create other reports, summaries, tables, or files. The calculated column is also useful for tying out numbers to the summary reports. You can filter the table and see the sum of the visible rows in the Total Row of the table.
One thing I wanted to mention is that you can remove the debit and credit fields from your pivot table and still retain the total column, if you wish. The table will still calculate accurately, whether those fields are actually showing or not. I hope these solutions are helpful for you as you take a look at your bank or credit statements, so that you can quickly see what you are spending or saving in each category. If you have any questions or comments about this post, please feel free to leave a comment below.
