Go Back

Creating and Using an Advanced Excel Pivot Table

We can create advanced Excel pivot tables to correlate different variables within our raw data. Pivot tables provide an exciting and quick approach to clean and format our data efficiently. The steps below will walk through the process.

Figure 1- Example of An Advanced Pivot Table

How To Create an Advanced Excel Pivot Table

  • We will create our data to show the sales made by a business in a particular period
  • Our spreadsheet will contain the Sales Date, Category, Product, Quantity, Sales rep and amount in Columns A, B, C, D, E, and F respectively

Figure 2 – Setting up the Data

  • We will select any cell within our data
  • In the Insert tab, we will click the Pivot table
  • Within the Create Pivot table dialog, we will check that the data range is correct and click OK.

Figure 3 – Creating an Advanced Pivot Table

  • We will now see an empty pivot table and to the right of the worksheet, a Pivot Table field list task pane. This is where we will assign our data fields.

Figure 4 – An Empty Pivot Table Field List Task Pane

  • We will drag the “Date” and “Product” field into the ROWS label

Figure 5.1- Pivot Table Fields

 

  • Again, we will drag the “Amount” into the Values label

 

  • Now, we will drag the Sales Rep into the Columns label

Figure 5.2 – A Typical Advanced Excel Pivot Table

Quick Tips to Use Advanced Pivot Table Techniques in Excel

 

Slicers

 

We use slicers to refine the data in our Excel Pivot table so that we or other users can customize the pivot tables without difficulty and fast.

  • To add a slicer, we will click within the Pivot table and check for the Analyze tab on the ribbon above our sheet.

Figure 6.1 – Pivot Table tools to insert slicer

  • We will click on Insert Slicer, select the slicers we want and click OK

Figure 6.2 – Making a choice of the slicer

Figure 6.3 – Inserting Slicers

 

Timelines

 

When our data contains dates, we can use Timelines to select data from a specific period. We must ensure that our dates are formatted as a date in the spreadsheet.

  • To add a timeline, we will select our Pivot table, click on Pivot table tools, and then, timeline. A window will pop up. Here, we can select the specific timelines we want to see.

Figure 7.1 – Pivot Table tools to insert timeline

Figure 7.2 – Inserting Timelines

 

Tabular view

 

Sometimes, an excessive number of fields may cause issues with Excel when creating layers to write formulas on. We can view our pivot table in a tabular form rather than the default setting to allow us to write formulas on our data more efficiently.

Figure 8 – Displaying Pivot Table in Tabular View

 

Calculated Fields

 

We can add a new column that wasn’t in our raw data into our pivot table with calculated fields.

  • To get started with calculated fields, we will click on our Pivot table
  • We will Click Analyze on the ribbon, then fields, Items & Sets and finally select calculated fields

Figure 9.1- Clicking on Fields, Items & Sets

  • In the pop-up window, we give our calculated field a name, such as Total Order Cost. We will enter the Total Order Cost formula given below into the allocated space, and click OK

=Amount*Quantity

Figure 9.2 – Inserting Calculated fields

 

Recommended Pivot tables

 

We can use recommended pivot tables if we are confused about setting labels in our pivot table.

 

  • We will highlight our data, go to the Insert tab and select Recommended Pivot Tables

 

Figure 10.1 – Clicking on Recommended Pivot table

  • In the popup window, we can click through the thumbnails on the left side to view the recommended Pivot table options

Figure 10.2 – Using Recommended Pivot tables

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar