Explore your customer data using Excel pivot tables to build your customer profiles.
What You Need
For a pivot table, you need to have your data in one sheet with the first row labeled with the field names.
Select the columns you want to include as the source data for your pivot table, click Insert / PivotTable. A pop-up screen will autofill the range you selected as the data, and ask you if you want the PivotTable as its own new worksheet or within the current one. Select "New Worksheet".
The PivotTable builder is a simple drag and drop canvas.
On the right, you will see the names of your PivotTable Fields. These are the field names from the first row of your data.
The empty canvas looks like this:
Under the PivotTable fields, there are four sections of the report in which you can drag your fields:
1. Filters: select values to include/exclude
2. Columns: column headings
3. Rows: row headings
4. Values: the calculated values in that cell
Drag and drop your fields to start to build your report.
In this example, I am filtering by Location, counting the number of customers by Title (row) and Original Lead Source (column).
You can see that there are 4 customers with the title CEO with the Original Lead Source of LinkedIn.
When you have many different values of a field, it makes sense to group values together. For example, if you have a lot of lead sources, you could group them into two groups: Paid, Unpaid. This makes it easier to segment and understand your customer profiles.
To group data, select the data you want to group together, click PivotTable Analyze / Group Selection.
You are now ready to explore your data. Analyze for insight! Drag and drop different fields, perform different calculations and groupings. You can have multiple dimensions to your PivotTable to slice and dice any way you want to. This is where the magic happens.
To help you visualize the data, you can select the PivotTable and Insert/Chart to the worksheet. This chart is dynamic and will change as you change the PivotTable. It is a powerful tool when you are exploring your data.