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


if statement excel 2010excel how to write a macro3d bubble chart excel 2010useful macros exceloption button excelexcel vba create new workbookexcel spreadsheet test for interviewsoftware development estimation templateexcel unlock password protected sheetexcel remove doublesawesome excel formulaswhat is countif in excelunhide cells excelms excel macro tutorialadding macros to excelfrequently asked questions in technical interviewexcel 2010 password protectunshare excelvba integer to stringsum vlookupinsert comment in excelwhat is hlookupvba lock cellsfor each vbaexcel macro querybest excel vba book for beginnersado microsofthow to unlock formulas in excelexcel macro current rowcomboboxesautofilter in vbadelete duplicate cells excelvlookup ref errorintroduction to sas pptvba string containsvba delete columnmerge multiple worksheetsestimation excel sheetproject dashboard template excelexcel vba new worksheetaverageifs exampleexcel forms tutorialddl dcladd developer tab to ribbonvba color index numberscreate pie chart in excel 2007genpact finance interview questionsexcel averageif formulaexcel 2010 unhide sheetvba to close workbookcopy and paste excel sheetvba xlvlookup easy examplewhat is descending order in excelvarious sql commandsvlookups on excelexcel macro delete rowshow to create a dashboard in excel 2007sorting objects worksheetsexcel sorting numbersimport worksheet excelexcel formulas sumiflistbox columnsexcel vba create rangehow do you unhide cells in excelwhere is advanced filter in excel 2010vba class examplevba save as csvsum vlookupcreating dashboards in excel 2007vba elsevba exit loop