How to Create a Pivot Table in Excel 2016

Learn How to Create A Pivot Table in Excel 2016/2013: Excel Pivot Tables & Charts – A Step By Step Visual Guide

Practice Projects and Solutions Included for Beginners

How to create a pivot table in excel 2016

Pages: 95.

Formats: Pdf & Epub.

Guarantee: 45 days.

Download Ebook: Download here.

Order Paperback: Contact us.

This book is for every Excel user. It teaches how to create a pivot table in Excel 2016. It is widely agreed that close to 60 percent of Excel users leave 80 percent of Excel untouched. That is, most users do not tap into the full potential of Excel’s built-in utilities. Of these utilities, the most prolific by far are the pivot table and pivot charts. Despite the fact that pivot tables and charts have been a cornerstone of Excel for more than 16 years, they remain one of the most underutilized tools in the entire Microsoft Office Suite.

Having discovered this book, you are savvy enough to have heard of pivot tables and charts or even have used them on occasion. You have a sense that pivot tables and charts have some power that you are not using, and you want to learn how to leverage that power to increase your productivity quickly.

With only this book, you will be able to create basic pivot tables and charts, increase your productivity, and produce reports in minutes instead of hours. Within the first 3 chapters, you will be able to output complex pivot reports with drill-down capabilities accompanying charts. By the end of the book, you will be able to build a dynamic pivot table reporting system.

Table of Contents

How to Use This Book

Download Link for Exercise Files

1. Introduction to Pivot Tables

  • What is A Pivot Table?
  • Basic Concepts
  • Conditions to Create a Pivot Table
  • Limitations of a Pivot Table

2. Creating A Pivot Table

  • Source of Data
  • Structure of the Pivot Table
  • Creating Your First Pivot Table

3. Creating A Pivot Chart

  • How to Drill-Down Pivot Table Data
  • Adding More Rows (categories) to Pivot Table
  • How to Create A Pivot Table Chart

4. Slicers & Advanced Filtering

  • Practice Project 1
  • Timeline Slicer
  • Slicer
  • Additional Information
  • Advanced Filtering

5. Calculations in Pivot Tables

  • Calculated Fields
  • Practice Project 2
  • Adding A Basic Calculated Field
  • Removing Or Changing Calculated Fields
  • Inserting Logic Fields (if…then)

6. Customizing Pivot Tables

  • Making Major Cosmetic Changes
  • Making Minor Cosmetic Changes

Book Preview

Learn How to Create A Pivot Table in Excel 2016

This book can be used as a tutorial or quick reference visual guide. It is intended for users who are comfortable with the basics of Microsoft® Excel® and are now ready to build upon this skill by learning Pivot Tables and Charts.

This book assumes you already know how to create, open, save, and modify an Excel® workbook and have a general familiarity with the Excel® toolbar (Ribbon).

Most of the examples in this book use Microsoft Excel 2016. However, the functionality and formulas can be applied with Microsoft Excel version 2013. Although the screenshots in this book use Microsoft Excel 2016, functionality and display are not very much different if you are using Excel 2013.

Please always back-up your work and save often as we go. A good best practice when attempting any new functionality is to create a copy of the original spreadsheet and implement your changes on the copied spreadsheet. Should anything go wrong, you then have the original spreadsheet to fall back on.

Download Link for Exercise Files

The exercise files we will use later in this book are available for download at the following website: https://goo.gl/e1SsZV.

Chapter 1: Introduction to Pivot Tables

Databases contain raw data on various topics, and are usually arranged in a tabular form. In many cases, data overload may make it difficult to use the information and convert it into relevant knowledge.

What is A Pivot Table?

A pivot table is a simple, yet powerful, technique which enables Excel users to turn the data overload into well-organized and meaningful knowledge.

By using a pivot table, users can perform various calculations on their data, such as calculating the average, counting, finding the minimum and the maximum values and so on.

Furthermore, the pivot table enables us to filter and sort the data easily and quickly. Users may focus on some or all parts of the data, even when the data tables are huge (some databases may contain a million or more records); thus users can obtain their desired data clearly and concisely.

A single data table can be used to create dozens of reports and charts for analyzing the data, with many cross-sections, simply by dragging fields to the appropriate locations.

Thus, the pivot table enables us to better understand processes and trends. It is also a useful tool for decision making. The pivot table data can be based on an existing Excel file or on other databases (i.e. Access or an SQL-based database).

Since a picture is worth a thousand words, here are some examples of pivot tables, derived from the same database of Fig. 1.0 showing the details of factory employees:

how to create a pivot table in Excel 2016
Fig. 1.0: Database of factory employees

The following pivot tables were derived from the database above:

how to create a pivot table in Excel 2016
Fig. 1.1: Number of employees in each department

Distribution of genders in each department:

how to create a pivot table in Excel 2016
Fig. 1.2: Distribution of genders in each department

Average salary in each department:

Average salary in each department
Fig. 1.3: Average salary in each department

Average salary in each section, by role:

Average salary in each section by role
Fig. 1.4: Average salary in each section by role

Basic Concepts

This chapter presents basic concepts relating to pivot tables. While studying and practicing, the following concepts will become clearer:

Data Table: A raw data set, arranged in a table. This can be used as the source of a pivot table.

Pivot Table: A table that displays data in different intersections, as described in this book.

Column: A vertical section of the table consisting of data of the same type, i.e. first name, ID, city etc.

Field: The column’s header is called a “field”.

Cell: The cell is the intersection of a row and a column, and contains the data of the table.

Item: The data in a cell. For example, New York and Detroit are items in the “City” field.

Record: A data collection which appears in one row and belongs to the same entity, e.g. all the table data which displays information regarding one person.

Conditions to Create a Pivot Table

Necessary Conditions:

  • Each column must have a title.
  • The title should be written in a single row.
  • In a column, all the items should be of the same data type (numbers, dates or strings).
  • The data table should not contain any merged cells.
  • The data table should not contain subtotals or grand totals.
  • Empty rows or columns should not remain within the table (if an empty row or column remains, Excel will treat the table as two different ones).
  • After creating a pivot table, do not change the titles of the fields, otherwise the pivot table values will be deleted.

Desirable Conditions

  • Unique names for each column (when two fields are given the same name, the title of the second field will be appended at end with 2, e.g. “salary2”).
  • Complete data for all records (when data is missing, the calculations will only be applied to the available records. This can be observed in the cases of calculations such as averages, etc).

Limitations of a Pivot Table

  • Number of pivot table reports in the worksheet: Limited by the available memory.
  • Unique items for each field.
  • Row fields or column fields in the pivot table report: Limited by the available memory.
  • Report filter in the pivot table report: 256 (May be limited by the available memory).
  • Value fields in the pivot table report: 256.
  • Formulas for calculated items in the pivot table report: Limited by the available memory.

Important Note: Due to the limitations of the pivot table, and depending on your personal computer data, you may prefer to save the exercises appearing in this book in a separate file or worksheet for each chapter.

Read more about how to create a pivot table in excel 2016…

” width=”20″ height=”20″>

Leave a Comment