Microsoft Excel: A Beginners' Guide to Pivot Tables in Excel

Microsoft Excel: A Beginners' Guide to Pivot Tables in Excel

by Simon Sez IT
Released November 2021
Publisher(s): Packt Publishing
ISBN: 9781803247915

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

We need to analyze data these days since we live in a very data-driven world. Therefore, data is important in any business, whether you are looking for trends or forecasting future business needs. As there is a lot of importance placed on data analysis, you must have the relevant skills to analyze data effectively.

Pivot Tables is an essential Excel skill for jobs that involve analyzing and visualizing large amounts of data. Pivot Tables are an interactive way of quickly summarizing large amounts of data by grouping and aggregating data sets and analyzing data clearly and effectively.

This course will discuss the importance of cleaning your data before creating your first Pivot Table. You’ll also learn how to create Pivot Charts and how to format your Pivot Tables and Charts. This course includes downloadable Excel data files that the instructor uses in the guide to follow along.

By the end of this course, you'll learn the skills required to be able to clean the data, put it in the Pivot Table, analyse the data efficiently, and keep it updated with a single click.

Therefore, by opting for this course, you’ll be able to take your own information and leverage Pivot Tables to answer questions with ease.

What You Will Learn

  • Learn basics of Pivot Tables and Charts
  • Clean and prepare your data
  • Create a basic Excel Pivot Table and format numbers in it
  • Use multiple fields and dimension, and methods of aggregation
  • Create Pivot Charts based on Pivot Table data
  • Add new data to the original source dataset, and update Pivot Tables and Charts

Audience

This course is for anyone who works with Microsoft Excel even if you've never used data analytics, Pivot Table and Pivot Charts. This course is aimed at people who have never used Pivot Tables before and would like to get started. It is also helpful for those who understand Pivot Tables but would like to expand their Excel skills.

Individuals with basic knowledge of Excel 2019/365 and those upgrading from previous software versions will also be benefitted from this course.

Knowledge of MS Excel is useful but not mandatory.

About The Author

Simon Sez IT: Simon Sez IT has offered technical courses for individuals, small businesses, and Fortune 500 companies since 2008, with thousands of employees who can benefit from the easy-to-learn and hands-on software training. It offers over 8,000 video tutorials on a range of software programs.

Simon Sez IT ensures stress-free eLearning and enhanced employee productivity—whether you implement new software or a technological upgrade in your work environment. With over 600,000 students from 180 countries, Simon Sez IT is the preferred online learning choice for individuals and businesses worldwide.

Table of contents

  1. Chapter 1 : Introduction to the Course
    1. Course Introduction
    2. What are Pivot Tables and Why are they Useful?
  2. Chapter 2 : Preparing Data for Analysis
    1. The Importance of Cleaning Data
    2. Removing Blank Rows
    3. Removing Duplicate Rows
    4. Clearing Formatting
    5. Applying Number Formatting
    6. Changing the Case
    7. Removing Spaces and Non-printing Characters
    8. Merging and Splitting Columns
    9. Convert Numbers Stored as Text
    10. Finding and Replacing Text
    11. Spell Check
    12. Exercise 01
  3. Chapter 3 : The Importance of Excel Tables
    1. Excel Tables versus Pivot Tables
    2. Format Data as a Table
    3. Exercise 02
  4. Chapter 4 : Creating a Basic Pivot Table Report
    1. Using Recommended Pivot Tables
    2. Creating a Pivot Table from Scratch
    3. Pivot Table Ribbons and Fields
    4. Pivoting the Fields
    5. Adding Multiple Dimensions
    6. Deleting Fields and Locking the Report Layout
    7. Exercise 03
  5. Chapter 5 : Aggregation and Grouping
    1. Methods of Aggregation
    2. Combining Methods of Aggregation
    3. Grouping and Ungrouping Data
    4. Exercise 04
  6. Chapter 6 : Formatting Values
    1. Applying Number Formatting
    2. Handling Cells with No Data
    3. Exercise 05
  7. Chapter 7 : Layout Options
    1. Subtotals and Grand Totals
    2. Choosing a Report Layout
    3. Inserting Blank Rows
    4. Exercise 06
  8. Chapter 8 : Formatting Pivot Tables
    1. Applying Pivot Table Styles
    2. Modifying Pivot Table Styles
    3. Creating a Custom Pivot Table Style
    4. Exercise 07
  9. Chapter 9 : Sorting and Filtering
    1. Sorting Pivot Table Data
    2. Filtering Pivot Table Data
    3. Top 10 Filter
    4. Exercise 08
  10. Chapter 10 : Creating Pivot Charts
    1. Selecting the Right Chart Type
    2. Clustered Column and Bar Charts
    3. Line Charts
    4. Pie and Donut Charts
    5. Map Charts
    6. Applying Chart Layouts
    7. Exercise 09
  11. Chapter 11 : Formatting Pivot Charts
    1. Adding a Slicer
    2. Formatting a Slicer
    3. Adding a Timeline
    4. Connecting Slicers and Timelines
    5. Exercise 10
  12. Chapter 12 : Applying Conditional Formatting
    1. Highlight Cell Rules
    2. Data Bars
    3. Color Scales
    4. Icon Sets
    5. Exercise 11
  13. Chapter 13 : Updating Pivot Tables and Pivot Charts
    1. Adding Data to the Dataset
    2. Updating Pivot Tables and Pivot Charts
    3. Exercise 12
  14. Chapter 14 : Course and Next Steps
    1. Course and Next Steps

Product information

  • Title: Microsoft Excel: A Beginners' Guide to Pivot Tables in Excel
  • Author(s): Simon Sez IT
  • Release date: November 2021
  • Publisher(s): Packt Publishing
  • ISBN: 9781803247915