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.

## Leave a Comment