{"id":3467,"date":"2018-07-16T10:01:35","date_gmt":"2018-07-16T16:01:35","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=3467"},"modified":"2020-06-01T15:43:53","modified_gmt":"2020-06-01T21:43:53","slug":"using-pivot-tables-to-analyze-income-and-expenses","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/excel-tips\/using-pivot-tables-to-analyze-income-and-expenses.html","title":{"rendered":"Using Pivot Tables to Analyze Income and Expenses"},"content":{"rendered":"<p><i>An Introduction to Pivot Tables in Excel by Guest Author and Microsoft MVP, John MacDougall<\/i><\/p>\n<div class=\"wp-post-image\" itemprop=\"image\" itemscope itemtype=\"https:\/\/schema.org\/ImageObject\">\n<img decoding=\"async\" width=\"300\" height=\"240\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/introduction-to-pivot-tables-in-excel.png\" alt=\"Introduction to Pivot Tables in Excel\"><meta itemprop=\"url\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/introduction-to-pivot-tables-in-excel.png\"><meta itemprop=\"width\" content=\"300\"><meta itemprop=\"height\" content=\"240\"><\/div>\n<p>Everyone deals with tracking income and expenses. It's a fact of modern day life, and if you don't track your money, you should.<\/p>\n<p>Excel can be a great tool to track your money. The <a href=\"https:\/\/www.vertex42.com\/ExcelTemplates\/income-and-expense-worksheet.html\" target=\"_blank\" rel=\"noopener noreferrer\">Income and Expense Template<\/a> by Vertex42 is an example of a spreadsheet you can use to track where your money is coming from and where it is going.<\/p>\n<p>One of the most basic tasks of managing your money is categorizing your expenses so that you know what you are spending your money on each month. In this post we'll take a look at how you can analyze and summarize your income and expenses using Pivot Tables!<\/p>\n<div class=\"contents\">\n<p>This Article (bookmarks):<\/p>\n<ul>\n<li><a href=\"#definition\">What are Pivot Tables?<\/a><\/li>\n<li><a href=\"#create\">How to Create a Pivot Table in Excel<\/a><\/li>\n<li><a href=\"#tools\">Pivot Table Tools<\/a><\/li>\n<li><a href=\"#build\">Building a Pivot Table<\/a><\/li>\n<li><a href=\"#calculated-field\">Adding a Calculated Field to a Pivot Table<\/a><\/li>\n<li><a href=\"#monthly-summary\">Creating a Monthly Summary<\/a><\/li>\n<li><a href=\"#running-total\">Show Values as a Running Total<\/a><\/li>\n<li><a href=\"#about\">About the Author<\/a><\/li>\n<\/ul>\n<\/div>\n<p>To follow along as we build a simple pivot table, download the file that we are using for the examples in this article:<\/p>\n<p class=\"downloadlink\"><span class=\"icon16 excel\"><\/span><a href=\"\/Files\/examples\/IncomeExpense-PivotTable.xlsx\" onClick=\"ga('send','event', 'Downloads', 'Examples', 'IncomeExpense-PivotTable.xlsx');\" class=\"bigbtn\" rel=\"nofollow\"><b>Download the Example File<\/b><\/a> (IncomeExpense-PivotTable.xlsx)<\/p>\n<p>Watch the video we created to go along with this article!<\/p>\n<div itemprop=\"video\" itemscope itemtype=\"http:\/\/schema.org\/VideoObject\">\n<iframe title=\"Watch the Video\" width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/OdEXf3k2vLU?rel=0\" frameborder=\"0\" allowfullscreen><\/iframe><meta itemprop=\"name\" content=\"An Introduction to Pivot Tables in Excel\"><meta itemprop=\"description\" content=\"Learn how to use Pivot Tables in Excel to easily summary data. This example shows to analyze income and expense data by account, category, and date.\"><meta itemprop=\"thumbnailURL\" content=\"https:\/\/cdn.vertex42.com\/blog\/images\/thumbnails\/video-an-introduction-to-pivot-tables-in-excel.png\"><meta itemprop=\"embedURL\" content=\"https:\/\/www.youtube.com\/embed\/OdEXf3k2vLU?rel=0\"><meta itemprop=\"uploadDate\" content=\"2018-08-21\">\n<\/div>\n<h2 id=\"definition\">What are Pivot Tables?<\/h2>\n<p>A Pivot Table is one of the most powerful and useful tools available for quickly summarizing data in a spreadsheet. The PivotTable feature was introduced in Excel 5 back in 1994, and has seen dramatic improvements in function and ease-of-use since then.<\/p>\n<p>Pivot Tables are both fast and flexible. With pivot tables, you can easily filter, sort and summarize your data and turn thousands of rows of data into actionable insights.<\/p>\n<h2 id=\"create\">How to Create a Pivot Table in Excel<\/h2>\n<p>Creating a pivot table is really simple!<\/p>\n<p>(1) First, select the data you want to include in your pivot table:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Select-any-Cell-Inside-the-Data.png\" alt=\"Select any Cell Inside the Data\" width=\"596\" height=\"339\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>In this example, our data is inside an <a href=\"https:\/\/www.howtoexcel.org\/tutorials\/everything-you-need-to-know-about-excel-tables\/\" rel=\"noopener noreferrer\" target=\"_blank\">Excel Table<\/a> named Vertex42 and if we select any cell inside this table, Excel will know we want to use the whole table of data. Even if the data is not in a table, Excel will guess the range of data based on selecting a single cell of the data.<\/p>\n<p>(2) Next, go to the <strong>Insert<\/strong> tab and press the <strong>PivotTable<\/strong> command.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Insert-Pivot-Table.png\" alt=\"Insert Pivot Table\" width=\"327\" height=\"156\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>This will open the <strong>Create PivotTable<\/strong> dialog box:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Create-Pivot-Table.png\" alt=\"Create Pivot Table\" width=\"577\" height=\"346\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>This menu allows us to choose the location of the data we want to analyze and where we want the resulting pivot table to live in the workbook.<\/p>\n<p>(3) Because we already selected a cell inside our table, Excel has already populated the Table\/Range field with the name of our table, so we don't need to change anything here.<\/p>\n<p>(4) Next, choose where you want the new pivot table (New Worksheet or Existing Worksheet).<\/p>\n<p>The default choice is to appear in a new worksheet. Excel will create a new worksheet that contains the pivot table. The other option is to choose the location in an already existing sheet and we can use the up arrow icon on the right of the location input box to do this. We will stick with the default option.<\/p>\n<p>(5) When you are happy with the options, press the <strong>OK<\/strong> button to create the new pivot table.<\/p>\n<p>Now we have a new blank pivot table:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Blank-Pivot-Table.png\" alt=\"Blank Pivot Table\" width=\"217\" height=\"343\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>It doesn't look like much now (because it's blank), but there is a lot we can do with it. If you'd like to learn just about everything pivot tables can do, check out my article <a href=\"https:\/\/www.howtoexcel.org\/pivot-tables\/pivot-table-tips-and-tricks\/\" rel=\"noopener noreferrer\" target=\"_blank\">101 Pivot Table Tips<\/a>! We'll be using a few of these tips to help analyze our income and expense data.<\/p>\n<h2 id=\"tools\">Pivot Table Tools<\/h2>\n<p>With the active cell inside the pivot table, you will see two new <strong>PivotTable Tools<\/strong> ribbon tabs labeled <strong>Analyze<\/strong> and <strong>Design<\/strong>:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/PivotTable-Tools-Analyze-and-Design-Tabs.png\" alt=\"PivotTable - Tools, Analyze and Design Tabs\" width=\"498\" height=\"157\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>These only show up when a pivot table is selected.<\/p>\n<p>You should also see a new window pane docked on the right side of the worksheet called the <strong>PivotTable Fields<\/strong> list:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/PivotTable-Fields-List.png\" alt=\"PivotTable Fields List\" width=\"277\" height=\"584\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>This window pane is the command center we'll be using to build and edit our pivot table.<\/p>\n<p>If the PivotTable Fields window pane isn't visible, then you can right-click anywhere inside the pivot table and select <strong>Show Field List<\/strong> from the menu, as shown in the image below:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Show-Field-List.png\" alt=\"Show Field List\" width=\"295\" height=\"343\" class=\"aligncenter imgshadow\" \/><\/p>\n<h2 id=\"build\">Building a Pivot Table<\/h2>\n<p>A blank pivot table isn't useful, so let's make something with it.<\/p>\n<p>Adding fields to a pivot table is easy. Simply click-and-drag any of the fields listed in the top area into any of the <strong>Filters<\/strong>, <strong>Columns<\/strong>, <strong>Rows<\/strong> or <strong>Values<\/strong> areas.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Drag-and-Drop-any-Field.png\" alt=\"Drag and Drop Any Field\" width=\"277\" height=\"584\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>(1) For our example, drag the <strong>Account<\/strong> field into the <strong>Rows<\/strong> area and both the <strong>Income<\/strong> and <strong>Expense<\/strong> fields into the <strong>Values<\/strong> area.<\/p>\n<p>This will create a summary of the income and expenses for each account, like this:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Pivot-Table-with-Income-and-Expense-by-Account.png\" alt=\"Pivot Table with Income and Expense by Account\" width=\"341\" height=\"116\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>(2) Remove the <strong>Account<\/strong> field from the <strong>Rows<\/strong> area by dragging it back to the field list or anywhere outside the PivotTable Fields pane.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Remove-a-Field-from-the-Pivot-Table.png\" alt=\"Remove a Field from the Pivot Table\" width=\"335\" height=\"273\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>(3) Drag the <strong>Category<\/strong> field into the <strong>Rows<\/strong> area to get a view of spending by category:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Pivot-Table-with-Income-and-Expense-by-Category.png\" alt=\"Pivot Table with Income and Expense by Category\" width=\"353\" height=\"249\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>This example shows how with pivot tables we can easily slice and dice the data into many different views to summarize the data.<\/p>\n<h2 id=\"calculated-field\">Adding a Calculated Field to a Pivot Table<\/h2>\n<p>Instead of showing the Sum of Income and Sum of Expense as separate columns, we might be more interested in the net transaction for each row of our data.<\/p>\n<p>The net transaction is the income amount minus the expense amount. We can add this to our pivot table with a <b>calculated field<\/b>.<\/p>\n<p>(1) With the pivot table selected, go to the <strong>Analyze<\/strong> tab and select the <strong>Fields, Items &amp; Sets<\/strong> command then choose <strong>Calculated Field<\/strong> from the menu.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Add-a-Calculated-Field.png\" alt=\"Add a Calculated Field to a Pivot Table\" width=\"531\" height=\"322\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>(2) In the <strong>Insert Calculated Field<\/strong> window, name the new field <strong>Net Transaction<\/strong> and add the formula <strong>Income - Expense<\/strong>. Tip: Double-click on any field name in the Fields area to use it in the formula.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Insert-Calculated-Field.png\" alt=\"Insert a Calculated Field\" width=\"447\" height=\"307\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>(3) Press the <strong>OK<\/strong> button and the field is now listed in our PivotTable Fields window:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/New-Calculated-Field.png\" alt=\"New Calculated Field\" width=\"277\" height=\"307\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>You can use this field just like any of the other fields in the data. You can drag it into the Values area of the pivot table, and you don't even need to include the fields it's based on.<\/p>\n<h2 id=\"monthly-summary\">Creating a Monthly Summary<\/h2>\n<p>A monthly summary is a common type of report we want to see when analyzing our spending. Summarizing spending by category and month will allow us to see how income and expenses vary from month to month and compare this to our budgeted amounts.<\/p>\n<p>Pivot tables are ideal for analyzing date and time data. When adding a date or timestamp field into a pivot table, Excel will automatically group it into Months, Quarters and Years. Other grouping options are available too, like hourly and by minute.<\/p>\n<p>If you add the <strong>Category<\/strong> to the <strong>Rows<\/strong> area, the <strong>Date<\/strong> to the <strong>Columns<\/strong> area and the <strong>Net Transaction<\/strong> to the <strong>Values<\/strong> area, you will get a nice monthly breakdown of spending by category, as shown in the image below.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Pivot-Table-with-Net-Transaction-by-Category-and-Month.png\" alt=\"Pivot Table with Net Transaction by Category and Month\" width=\"475\" height=\"282\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>The result is a table that allows us to easily compare our spending month-to-month. For example, we over spent our monthly entertainment budget of $300 in February.<\/p>\n<p><b>Note:<\/b> If Excel doesn't automatically group the dates by month, you can right-click on any of the dates in the pivot table and select <strong>Group<\/strong> from the options. Then in the grouping options, select the group <strong>By Months<\/strong> option.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Group-by-Month.png\" alt=\"Group by Month\" width=\"456\" height=\"373\" class=\"aligncenter imgshadow\" \/><\/p>\n<h2 id=\"running-total\">Show Values as Running Totals<\/h2>\n<p>Most bank statements will show the new account balance after each transaction. This is very useful for reconciling our spreadsheet with our bank statements.<\/p>\n<p>If our original data doesn't show a running balance, we can create one easily with a pivot table.<\/p>\n<p>(1) Add the <strong>Date<\/strong> field into the <strong>Rows<\/strong> area, the <strong>Account<\/strong> field into the <strong>Columns<\/strong> area and the <strong>Net Transaction<\/strong> field into the <strong>Values<\/strong> area.<\/p>\n<p>This will show the net transaction amount by day and by account, but this is not yet a running balance:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Net-Transaction-by-Day-and-Account.png\" alt=\"\" width=\"606\" height=\"268\" class=\"aligncenter imgshadow\" \/><\/p>\n<p><b>Note:<\/b> Excel might automatically group the date field into months, quarters and years. You can remove this grouping by right-clicking on any of the dates and selecting <strong>Ungroup<\/strong> from the menu.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Ungroup-Dates.png\" alt=\"Ungroup Dates\" width=\"606\" height=\"366\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>The account balance is the opening balance plus any previous transaction amounts. So, you need a running total in order to see the account balance for any given day.<\/p>\n<p>(2) Right-click anywhere on the net transaction field inside the pivot table and select <strong>Show Value As<\/strong>, then choose <strong>Running Total In<\/strong>, then select Date for the base field.<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Show-Values-as-a-Running-Total.png\" alt=\"Show Values as Running Total\" width=\"716\" height=\"487\" class=\"aligncenter imgshadow\" \/><\/p>\n<p>The pivot table now shows the account balance each day for each account:<\/p>\n<p class=\"center\"><img decoding=\"async\" src=\"https:\/\/cdn.vertex42.com\/blog\/images\/pivot-tables\/Running-Total-of-Account-Balance.png\" alt=\"Running Total of Account Balance\" width=\"606\" height=\"308\" class=\"aligncenter imgshadow\" \/><\/p>\n<h2 id=\"conclusion\">Conclusions<\/h2>\n<p>In this post we explored what pivot tables are, how to create them and a bit about what they can do.<\/p>\n<p>We were able to quickly summarize our data by account or category without any formulas. It only required a few point-and-click steps.<\/p>\n<p>We learned how to add new calculations into our pivot tables using calculated fields, which allowed us to analyze the net transaction amounts in our income and expense data. We then used a running total calculation inside our pivot table to find the account balances at any given day.<\/p>\n<p>Pivot tables are incredibly powerful and useful tools. Make sure to add them to your toolbox.<\/p>\n<h2 id=\"about\">About the Author<\/h2>\n<p>John MacDougall is a Microsoft MVP and former actuary who regularly shares his knowledge and passion for Excel and Power BI through his website <a href=\"https:\/\/www.howtoexcel.org\/\" rel=\"noopener noreferrer\" target=\"_blank\">HowToExcel.org<\/a>. John eventually left the financial world to join the stimulating tech world where he worked on data analysis in advertising. He currently works as an independent Excel and Power BI consultant.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This introduction to using Pivot Tables in Excel shows how to analyze monthly income and expenses by account, category, date, etc. Download the example spreadsheet to follow along. By guest author and Microsoft MVP, John MacDougall.<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[78],"tags":[],"class_list":{"0":"post-3467","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-excel-tips"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3467","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=3467"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/3467\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=3467"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=3467"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=3467"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}