building a pivot table in excel 2013 for mac

Tip: Double-click on any field name in the Fields area to use it in the formula. (2) In the Insert Calculated Field window, name the new field Net Transaction and add the formula Income - Expense. (1) With the pivot table selected, go to the Analyze tab and select the Fields, Items & Sets command then choose Calculated Field from the menu. We can add this to our pivot table with a calculated field. The net transaction is the income amount minus the expense amount. 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. Adding a Calculated Field to a Pivot Table This example shows how with pivot tables we can easily slice and dice the data into many different views to summarize the data. (3) Drag the Category field into the Rows area to get a view of spending by category: (2) Remove the Account field from the Rows area by dragging it back to the field list or anywhere outside the PivotTable Fields pane. This will create a summary of the income and expenses for each account, like this: (1) For our example, drag the Account field into the Rows area and both the Income and Expense fields into the Values area. Simply click-and-drag any of the fields listed in the top area into any of the Filters, Columns, Rows or Values areas. If the PivotTable Fields window pane isn't visible, then you can right-click anywhere inside the pivot table and select Show Field List from the menu, as shown in the image below:Ī blank pivot table isn't useful, so let's make something with it.Īdding fields to a pivot table is easy. This window pane is the command center we'll be using to build and edit our pivot table. You should also see a new window pane docked on the right side of the worksheet called the PivotTable Fields list: These only show up when a pivot table is selected. With the active cell inside the pivot table, you will see two new PivotTable Tools ribbon tabs labeled Analyze and Design: If you'd like to learn just about everything pivot tables can do, check out my article 101 Pivot Table Tips! We'll be using a few of these tips to help analyze our income and expense data. It doesn't look like much now (because it's blank), but there is a lot we can do with it. (5) When you are happy with the options, press the OK button to create the new 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. Excel will create a new worksheet that contains the pivot table. The default choice is to appear in a new worksheet. (4) Next, choose where you want the new pivot table (New Worksheet or Existing Worksheet). (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. 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. This will open the Create PivotTable dialog box:

(2) Next, go to the Insert tab and press the PivotTable command. 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. In this example, our data is inside an Excel Table named Vertex42 and if we select any cell inside this table, Excel will know we want to use the whole table of data. (1) First, select the data you want to include in your pivot table: With pivot tables, you can easily filter, sort and summarize your data and turn thousands of rows of data into actionable insights. The PivotTable feature was introduced in Excel 5 back in 1994, and has seen dramatic improvements in function and ease-of-use since then. A Pivot Table is one of the most powerful and useful tools available for quickly summarizing data in a spreadsheet.

Building a pivot table in excel 2013 for mac