But what if you wanted to know which person worked the most hours? Or which project had the most people assigned to it? You can just change the four fields (Person, Date, Project, Hours) around in the Pivot Table tool to produce a wide variety of different tables. Then, as if by magic, the left side of the sheet will show the hours worked by project by day:įrom the newly created pivot table, you can see that Thursday was the busiest day, and Training course took the most hours. I just drag Date, Project, and Hours into three of the four boxes, like so: Let’s start by making a table showing each project in a separate row, and each day in a separate column, with the values being the number of hours worked. On the left, you’ll see a blank template showing what your pivot table will look like, and on the right are the four field names (columns of your original data) which you can drag into four boxes, below, to make different tables. When the Create PivotTable dialog appears, just leave everything default and click Enter.Ī new sheet will be added. Click Insert item on the top bar and click Pivot Table to bring up Create Pivot table dialog. Here’s a sample, showing the hours worked by four people on some projects this past week: We suppose you have some data in a sheet of a workbook. Then do you know how to create a pivot table in Excel 2016? If not, please follow my words, I would show you the detailed steps of creating a pivot table in Excel 2016. To do this, select cell A1 and type Order ID.The pivot table in Excel offers us a good chance to analyse data in a easy way. Next in the Values section, click on the 'Sum of Order ID' and drag it to the Rows section.įinally, we want the title in cell A1 to show as 'Order ID' instead of 'Row Labels'.

In this example, we've selected the checkboxes next to the Order ID and Quantity fields. Next, choose the fields to add to the report. Your pivot table should now appear as follows: In this example, we've chosen cells A1 to F16 in Sheet1 as indicated by Sheet1!$A$1:$F$16. Select the range of data for the pivot table and click on the OK button. In the Tables group, click on the Tables button and select PivotTable from the popup menu.Ī Create PivotTable window should appear. Next, select the Insert tab from the toolbar at the top of the screen. In this example, we've selected cell A1 on Sheet2. Highlight the cell where you'd like to create the pivot table.

In this example, the data is found on Sheet1. To create a pivot table in Excel 2016, you will need to do the following steps:īefore we get started, we first want to show you the data for the pivot table. If you want to follow along with this tutorial, download the example spreadsheet.ĭownload Example Steps to Create a Pivot Table

Creating a pivot table in excel 2016