Creating a Pivot Table - PC Users

A pivot table allows you to easily filter and view only the data that is most relevant to you.

The steps to creating a pivot table vary depending on the version of Excel you are using and whether you are on a PC or a Mac computer.  

The steps below demonstrate how to create a pivot table on a PC:
To view how to create a pivot table using a Mac, click here.  

1.  On the Your Members page, click on the Download as CSV button.  

This will automatically download all of the credential information displayed in the table for your members into a type of Excel file called a CSV file - which simply allows data to be saved in a table structured format.

2.  Save the CSV file to an easily retrievable location and open.   

Note:  In newer versions of Windows, your CSV file will display in the bottom of the Industry Smart window.  You can also click this to open the CSV file.

Your data will display in a table format, similar to what you view within Industry Smart. You now want to take this data and display it in a way that makes sense to you, and is visually easier to read and interpret.  We will do this by creating a Pivot Table.  

3.  Click on Insert > Pivot Table

A pop-up box displays the display options for your pivot table.  

Use this pop-up box to identify the data to be included in the table.  Your entire worksheet is automatically selected, so typically you do not need to change this.   

You also identify whether you want the pivot table to display within a new worksheet, or within the existing worksheet.  Typically a new worksheet would be most helpful, so the default option is fine.  

4.  Click OK to accept the default options.  

A new worksheet automatically opens and displays an blank table.  

You now need to define the information you want to display within the columns, rows and cells using the PivotTable Builder.

Scenario:  In this example, we want to identify which of our members credentials are soon to expire, so we can take appropriate action.  

We will put member names across the top column and display the credential categories and subcategories down the rows.  

5.  Click and drag MembersName down to the Column section.  This immediately displays your members names across the columns within your new worksheet.

6. Click and drag Credential Type and Reference down to the Row Labels section.  This immediately categories the credentials by type.  

7.  Finally, click and drag DateExpires down to Values.  This populates the main table with values.

Note:  You'll notice that the expiry date displays as a single number and not a date.  We now need to change the format of the date cells so the date displays correctly.  

8. Click on the drop down arrow next to the Count of Expiry Date field.  This displays a new pop up box. 

9. Change the option from Count to Max.   

10.  Click on the Number Format button and change the date to your preferred date format.

11.  Click the OK button to return to your pivot table.

You can now see clearly your individual members across the top of the spreadsheet, their credentials running down the side and the expiry date of each within the table. 

Tip:  You'll notice that the pivot table settings automatically apply grand totals to your table, which are not relevant in our situation.  

12. To remove the grand totals, click on the PivotTable1 > Options menu.

This displays the PivotTable Options box.  

13.  Click on the Totals & Filters tab and deselect the Show grand totals for row and Show grand totals for columns check boxes.

 14.  Click the Ok button to return to your pivot table.  

 15.  You can now view, amend and save your pivot table as you want.  

 

 

 

 

 

 

 

Have more questions? Submit a request

Comments