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 using a Mac computer.
To view how to create a pivot table using a PC, click here.
1. On the Your Members page, click 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 Data > Pivot Table
A pop-up box displays the options for creating your pivot table.
Use the 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 Labels section. This immediately displays your members names across the columns.
6. Click and drag Credential Type and Reference down to the Row Labels section. This immediately categorises the credentials by type.
7. Finally, click and drag DateExpires down to Values section. This populates the main table with expiry date 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 question mark symbol 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 button and select your preferred date format.
11. Click OK 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 Totals button in the header and de-select both the Rows and Columns options.
You'll notice that the Grand totals rows and columns are removed.
13. You can now view, amend and save your pivot table as you want.