Excel Pivot Tables Tutorial

Home/Pivot Tables/Excel Pivot Tables Tutorial

Excel Pivot Tables Tutorial to create Pivot tables and pivot charts in Microsoft Excel 2003,2007,2010,2013. Learn from basics like what is a pivot table, how to make and use pivot tables. And advanced pivot tables concepts like formatting pivot tables, pivot tables formulas and calculated fields, purpose, definition and usage and examples of pivot tables, PowerPivot and use in SQL Server, pivot charts, dynamic pivot tables, summarizing, filtering, sorting and refreshing pivot tables, limitations, shortcuts and best practices while inserting pivot tables. Detailed explanation on Filter and Field Settings, Options, Page Fields, Row fields, Column Fields, Value fields and Number formats, aggregates, totals, sub totals, row totals, column totals, changing data source, using pivot slicers and report layouts and dealing with blank rows . Change, Move and Delete Pivot tables.

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


Excel Pivot Tables Tutorials – Learning Path

Follow the below step by step learning path to understand and learn pivot tables in the best and easy way to master in pivot tables. Please not all the topics are applicable to Excel 2003 to 2013 except Slicers. Slicers are introduced in Excel 2010 and available in 2013.
Basics Pivot tables tutorials
Excel Pivot Tables: Introduction

  • What is Pivot table – Pivot table Definition in Excel
  • How to use Pivot Tables in Excel.

Creating Pivot Tables in Excel: Step by step tutorial

  • How to create Pivot tables in Excel.
  • How to Make Pivot tables – Step by Step Tutorial.
  • How to modify, Move Pivot tables in Excel.

Advanced Pivot tables tutorials:
Pivot table Data and Pivot Cache

  • What is Pivot Cache?
  • Understanding Pivot table underlying data.
  • How to change Pivot table data source.
  • Deleting Pivot tables in Excel
  • Pivot table Slicers in Excel 2010 and 2013.
  • Pivot tables Advanced Settings and Options Tutorials

Pivot table Fields Settings and Options

  • Understanding Pivot table Filters or Page Field.
  • Pivot table Rows fields.
  • Understanding Pivot table Column Field.
  • Understanding Pivot tables Values Field.

Excel Pivot Tables Advanced Settings and Options
Pivot Charts in Excel

  • Creating Pivot Charts
  • Pivot Charts settings and options in Excel.

Pivot Tables Total and Sub Totals in Excel

  • Pivot table Grand totals
  • Pivot tables Sub totals – Showing and Hiding.

Pivot Tables Layouts

  • Pivot tables Layouts – Compact, Outline, Tabular Form
  • Repeating All Item Labels.

Pivot Tables Calculated Fields

  • Creating and Adding Calculated Fields in Pivot tables

Pivot Tables Values Fields

  • Aggregates (Sum, Count, Average, min, max, and product) in Pivot Tables.
  • Calculations like percentage % of row total, % of column total, Ranks.
  • Number formats in Pivot tables.

Dealing with Pivot Tables

  • Expand and Collapse Options in Pivot table.
  • Grouping and Ungrouping items in Pivot tables.
  • Sorting and Custom sorting Pivot tables.
  • Filtering items in Pivot tables
  • Pivot tables Limitations and Best Practices
  • PowerPivot and SQL Server
  • Creating Dynamic Pivot tables

Sample Data for Pivot tables

We are going to use this sample data to explain all topics in Pivot Tables Tutorials. This is a dummy data store data using for explaining Pivot tables Concepts. This is a dummy super market data, it is easy to understand the pivot tables concepts by generating summary reports using this data.
There are 12 fields (Order ID, Oder Date, Order Month, Order Year, Sales, Quantity, Profit, Region, State, City, Category, and Sub-Category) and 10000 records in this sample data. We will see the pivot tables examples, it will be easy to understand the concepts with this large enough data set.

Excel Pivot Tables Basic Tutorials

Excel Pivot tables Basic tutorials will help you to understand the basic concepts of Pivot tables in Excel. We will what is the Pivot table in Excel and Definition of Pivot tables in Excel, How to create and use pivot tables in Excel, How to modify, and move pivot tables in Excel. We will see what is use of pivot tables and purpose of pivot tables.

Excel Pivot Tables: Introduction

These introductory topics on Pivot Tables will give you an overall understanding on Pivot tables. In these topics in Excel Pivot Tables Tutorials we will learn, what is Pivot Table in Excel? And how to use pivot tables and uses and advantages of Pivot tables in Excel?
a. What is Pivot table – Pivot table Definition in Excel
b. How to use Pivot Tables in Excel.

Creating Pivot Tables in Excel: Step by step tutorial

Following tutorials will help you to create pivot tables in Excel. These step by step quick tutorials will help you making pivot tables, modifying and moving pivot tables in Excel.
c. How to create Pivot tables in Excel.
d. How to Make Pivot tables – Step by Step Tutorial.
e. How to Modify, Move Pivot tables in Excel.

Excel Pivot Tables Advanced Tutorials

Pivot tables advanced tutorials will help us to deal with advanced uses of pivot tables. We will see what Pivot cache is, understanding Pivot tables underlying data, data source, filters and page fields, rows fields, columns fields and values fields in Pivot tables. Deleting pivot tables in Excel completely.

Pivot table Data and Pivot Cache

It is important to understand the Pivot tables Data, underlying data, Pivot cache. We can create multiple pivot tables from one pivot cache. We can change the settings of pivot tables not to save the underlying data. So that the file size will be smaller. And we will see the use of Pivot tables Sliders in Excel 2010, and 2013.
a. What is Pivot Cache?
b. Understanding Pivot table underlying data.
c. How to change Pivot table data source.
d. Deleting Pivot tables in Excel
e. Pivot table Slicers in Excel 2010 and 2013.

Pivot table Fields Settings and Options

Everything in Pivot table will depending on the proper alignment of Fields in the Pivot table. We will see the different types of Pivot Fields. And we will see the different options for each field type.
f. Understanding Pivot table Filters or Page Field.
g. Pivot table Rows fields.
h. Understanding Pivot table Column Field.
i. Understanding Pivot tables Values Field.

Excel Pivot Tables Advanced Settings and Options

Advanced tutorials on Pivot table’s settings and options will help us to change and format the pivot tables as per our requirement. We will see how to use pivot charts by linking to pivot tables, sliders in Excel pivot tables in Excel 2010, 2013, and Power Pivot and SQL Server Connectivity. Grand totals, sub totals in Excel Pivot tables, Pivot table’s layouts like Compact Form, Outline Form, Tabular Form and Repeating All Pivot table items labels. Value fields and its settings like aggregates, number formats, % of row total, column totals. We will also see grouping and ungrouping, expanding and collapsing, sorting, filtering the items in pivot tables, Pivot table limitations, best practices. Creating and Adding Calculated Fields in Pivot tables. Creating Dynamic Pivot tables

Pivot Charts in Excel

Pivot charts are another useful integration in Excel. We can link a chart to Pivot table, so that we can make the charts dynamic to pivot tables data. We will see the different options in Pivot table charts like how to create pivot charts, how to hide pivot table axis field buttons, etc.
a. Creating Pivot Charts
b. Pivot Charts settings and options in Excel.

Pivot Tables Total and Sub Totals in Excel

It is easy to summarize the data using Pivot tables. We can provide Grand totals at column or row level. And we can also provide sub totals. We will see different options for formatting Grand Totals and Sub totals in Excel Pivot tables.
c. Pivot table Grand totals
d. Pivot tables Sub totals – Showing and Hiding.

Pivot Tables Layouts

Pivot table layouts helps to arrange and change the style of Pivot tables. You can choose different layout forms available in Pivot tables like Compact Form, Outline Form, and Tabular Form. We can also toggle repeating the items in pivot tables.
e. Pivot tables Layouts – Compact, Outline, Tabular Form,
f. Repeating All Item Labels.

Pivot Tables Calculated Fields

Calculated fields in Excel Pivot tables will help you to create new fields based on the existing fields. This will help you to reduce the file size. For example, if you have the date field in your data, you can create week, month, quarter, period and year fields from one fields. And you can also create other fields like %change, % growth, etc.
g. Creating and Adding Calculated Fields in Pivot tables

Pivot Tables Values Fields

When we come to the Values or Data fields in the Pivot table, we can do lot more things by changing Values Fields settings. We will see different options for Value Fields and its use in real time projects.
h. Aggregates (Sum, Count, Average, min, max, and product) in Pivot Tables.
i. Calculations like percentage % of row total, % of column total, Ranks.
j. Number formats in Pivot tables.

Dealing with Pivot Tables

The following topic will help you to deal with Pivot tables. We will see the options for Expanding, collapsing, sorting, filtering pivot tables, and limitations and best practices of Pivot table.
k. Expand and Collapse Options in Pivot table.
l. Grouping and Ungrouping items in Pivot tables.
m. Sorting and Custom sorting Pivot tables.
n. Filtering items in Pivot tables
o. Pivot tables Limitations and Best Practices
p. PowerPivot and SQL Server
q. Creating Dynamic Pivot tables

LIMITED TIME OFFER
By |May 5th, 2014|Pivot Tables|0 Comments

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

Leave A Comment


Related pages


formula sumifsearching for duplicates in excelsas macros interview questionssql sortingexcel filter based on cell valuevba object orientedunhide all excelworksheet tab excelwhat is pivot chart in excelv lookup functionvba application waitexcel project portfolio dashboardvba append querycombobox multiselectmicrosoft excel 2007 vbaiserrorvlookup between two valuesexamples of macros in excelsas do while loop examplewhat is sumifvba variant typevba name rangeexcel layout templatesvba redim arraypie chart examples in excelvba save excelexcel chart macroexcel chart formulaautofit column widthvba access connection stringmacros excel examplesvba save excel filehlookup excel 2013how to find double entry in excel 2007sort command in exceladd developer tab to ribbon in word 2007delete empty cells in excelhow to program macros in exceladvanced filter excelinstr in vbavb 6.0 interview questionsexcel vba clearcontentsexcel formula to hide rowscreating dashboard in excelexcel vba select sheetvba excel syntaxvba to open workbookexcel chart vbavba excel case statementexcel advanced vlookupchoose function excelacspreadsheettypeexcel12delete sheet rowsnamed ranges vbahow to add a blank row in excelsas sql tutorialindex formula excel 2007vba lastrowunhide cells in excelexcel auto sortcontains vbaapplication worksheetfunction countashortcut key for excel 2007ascending and descending order worksheetsvba macros excelhow to write excel macros for beginnersexcel autofit row heightexample for vlookup in excelvlook up tutorialrun query from vbaactivex exampleunlocking excel sheetexcel vba read csv filevba select case examplebeginners sql tutorialprogramming excel macroshow to delete unwanted cells in excelexcel delete blank cells