All You Need to Know About

Excel Pivot Tables Tutorials

Learn how to perform data analysis using Pivot Tables in Excel. You can rank values, group data by quarter, month, week and so much more!

Here are the top things on what you can do with the Pivot Table Data Analysis in Excel:

Use An External Data Source To Import Data Into An Excel Pivot Table

When creating an Excel Pivot Table, what happens if your data source is in another location?

Would you have to copy your data into the same spreadsheet?

Well, NO!  You can simply use the External Data Sources feature in your Pivot Table and Excel will magically import the data for you!

You can import data into your Pivot Table from the following data sources:

  • Excel workbook
  • Microsoft Access database
  • SQL Server
  • Analysis Services
  • Windows Azure Marketplace
  • OData Data Feed

For our example, we will import data using two data sources, an Excel workbook and an Access file.

 

Import from another Excel Workbook:

External Data Source

Import From Microsoft Access and into Excel:

External Data Source

Wondering how this is even possible? Read on!

DOWNLOAD FILES

Import from another Excel Workbook:

STEP 1:  Go to Insert > Tables > PivotTable

External Data Source

 

STEP 2: Select Use an external data source and click Choose Connection.

External Data Source

 

STEP 3: Select Browse for More.

External Data Source

 

STEP 4: Select the Excel file with your data. Click Open.

External Data Source

 

STEP 5: Select the first option and click OK.

External Data Source

 

STEP 6: Click OK.

External Data Source

 

STEP 7: In the VALUES area put in the Sales field, for the COLUMNS area put in the Financial Year field, and for the ROWS area put in the Sales Month field

Your Pivot Table is ready from the Excel data source!

External Data Source

 

Import From Microsoft Access and into Excel:

 

STEP 1: Now let us try for an Access data source!

Go to Data > Get External Data > From Access

External Data Source

 

STEP 2: Select the Access Database Source file in your desktop or company file path. Click Open.

External Data Source

 

STEP 3: Select PivotTable Report and click OK.

External Data Source

 

STEP 4: In the VALUES area put in the Sales field, for the COLUMNS area put in the Financial Year field, and for the ROWS area put in the Sales Month field

Your Pivot Table is ready from the Access data source!

External Data Source

More Ways to Import External Data into an Excel Pivot Table:

You can also use this functionality to get data from other source types: SQL Server, Analysis Services, Windows Azure, and oData Data Feed

 

 

Group Sales by Weeks With Excel Pivot Tables

I am sure that your boss has asked you to come up with Total Sales numbers per week at least once in your current role!  If not, he will….very very soon!

Now your data has only sales for each individual day, which makes you scramble to come up with a solution.   How will you be able to group them together and get the sales report your boss needs?

Formulas?  Maybe…well NO!  Too difficult.

Macros?  Don´t know how to code!

Pivot Tables?  HELL YEAH BABY!

Yes, Pivot Tables are quick and reduce the risks of making any errors….ah yeah, I almost forgot…it is also easy to add new data to your sales report!

In the example below I show you how to get the Sales Grouped by Date:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: Insert a new Pivot table by clicking on your data and going to Insert > Pivot Table > New Worksheet or Existing Worksheet

group-by-date-01

 

STEP 2: In the ROWS section put in the Order Date field.

Notice that in Excel 2016 it automatically groups dates into Years & Quarters.  We do not need this expansion.

Click the Undo button:

group-by-date-02

 

After clicking Undo, you will only have the Order Date in the Rows area:

group-by-date-03

 

STEP 3: Right click on any date in your Pivot Table and select Group

group-by-date-04

 

STEP 4: It was able to determine our date range (minimum date and maximum date).

Deselect Months and make sure only Days is selected (depicted by the blue color).

Set the Number of days to 7.

This will group our dates by a weekly range (every 7 days).

Click OK.

group-by-date-05

 

STEP 5: Notice that we have Date Ranges now which are 7 days long each :

group-by-date-06

 

STEP 6: In the VALUES area put in the Sales field.

This will get the total of the Sales for each 7-day date range you have defined:

group-by-date-07

 

You now have your total sales for each 7-day period!  If you like you can insert a Pivot Chart to visually show the data to your miserable boss!

group-by-date-08

 

Running Total In % with an Excel Pivot Table

A Running Total In is the accumulation of values over a certain period, like days, month or years.  It is sometimes referred to as the Year to Date analysis.

A Running Total In takes one period’s values, then adds a second period, then a third period…and so on, to give the accumulated values up to a certain period.

This can be show in dollar form or in percentage form.

With an Excel Pivot Table we can easily include this calculation without the need to use any formulas.  Here is how:

DOWNLOAD EXCEL WORKBOOK

STEP 1: Insert a Pivot Table by clicking in your data and going to Insert > Pivot Table and choose to insert it in a New or Existing Worksheet

insert a pivot table

create pivot table

 

STEP 2: You will need to put the Months Field in the ROW area, the Year Field in the COLUMN area and then put the Sales Field in the VALUES area two times!

pivottable fields

 

STEP 3: Right Click on a Totals cell and choose Remove Grand Total

remove grand totals

 

STEP 4: Right Click on a Sum of SALES2 value and select Show Values As > % Running Total In  > Base Field: Month

% running total in

Base Field

 

STEP 5: You can manually change the name of the Sum of SALES2 Field by clicking in the cell’s title within the Pivot Table:
manually change field name

 

STEP 6: We can insert a Pivot Chart by clicking in our Pivot Table and going to PivotTable Tools > Analyze/Options > Pivot Chart

pivot chart

 

STEP 7: From the Insert Chart dialogue box you need to choose the Combo chart and “check”the Secondary Axis box for the YTD% Series and press OK

insert combo chart

 

You now have your % Running Total In values on a Pivot Table and also shown graphically on a Pivot Chart:

pivot table ytd

pivot chart ytd

 

Latest Tutorials

Want to get better at Excel?

Join Our Excel Community

Get our Free Weekly Excel lesson plans that cover the must know Excel features and tips to make you better at Excel!
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]
[l]