Pivot Tables for everyone. From simple tables to Power-Pivot: Useful guide for creating Pivot Tables in Excel

By Olga Maria Stefania Cucaro

This work stems from the need to develop some Excel sheets to reach the required results.
For example, in the workplace it often happens to have data that without a precise sorting or a filter or processing based on a certain field may seem unusable.
Here are some features of the programs that process spreadsheets such as Excel from Office or Calc from OpenOffice or Calc from LibreOffice, as well as others, can be useful for achieving the result we want to obtain.
In this volume we will explain in a simple and effective way the usefulness of some important functions of the processing of spreadsheets and pivot tables that are essential for any work that focuses on data analysis.
Today it is no longer possible to ignore the use of Excel or other software for processing spreadsheets for both business and leisure purposes. In this work we will dedicate ourselves to the processing of quantitative data for work purposes, taking into consideration various examples that will explain how to solve the problems proposed through subsequent steps that will lead to the solution of the same.

• Language: English
• Publisher: ResearchFreelance
• Release date: Jan 9, 2022
• ISBN: 9791220883221

Book preview

Pivot Tables for everyone

Useful guide for creating Pivot Tables

Of

Olga Maria Stefania Cucaro

© Copyright 2021 by Olga Maria Stefania Cucaro - All rights reserved.

It is not permitted to reproduce, duplicate or send any part of this document electronically or in print. Copying of this document is strictly prohibited. All the programs included in this book are also copyrighted by the author / creator of the work.

The news, information, images and external tables present in this work are used for training purposes only.

Dedicated to:

To my mother who has always believed in me and to my IT professors who made my working life easier

Summary

Introduction 5

What are Pivot Tables 6

What Pivot Tables can be used for 8

Set the data for creating the Tables 9

Useful Excel functions for setting data 9

Filters and sorts 16

Examples of work problems solved with functions and tables 21

Example No. 1 TOTAL COST FOR TWO-MONTH OCCASIONAL EMPLOYEES 21

Example No. 2 HOURLY AND TOTAL COST FOR OCCASIONAL EMPLOYEES 34

Example N. 3 SUMMARY SALARY TABLES BY COMPANY FRAMEWORK 40

Example No. 4 SUMMARY TABLE OF EMPLOYEE ACCESSORY CHARGES 44

Example N. 5 SUMMARY TABLE OF AGENT SALES 53

Relationships, data model and Power Pivot 60

Relations and data model 60

Power Pivot 63

Conclusions 75

Introduction

This work stems from the need to develop some Excel sheets to reach the required results.

For example, in the workplace it often happens to have data that without a precise sorting or a filter or processing based on a certain field may seem unusable.

Here some features of the programs that process spreadsheets such as Excel from Office or Calc from OpenOffice or Calc from LibreOffice, like others, can be useful for achieving the result we want to obtain.

In this volume we will explain in a simple and effective way the usefulness of some important functions of the processing of spreadsheets and pivot tables that are essential for any work that focuses on data analysis.

Today we can no longer ignore the use of Excel or other software for processing spreadsheets for both work and leisure purposes. In this work we will dedicate ourselves to the processing of quantitative data for work purposes, taking into consideration various examples that will explain how to solve the problems proposed through subsequent steps that will lead to their solution.

What are Pivot Tables

Pivot tables are tables organized according to the choices of the operator. This type of tables processes one or more source tables that can be contained in an Excel sheet. The source table must contain the header of each column to allow the creation of a Pivot Table. To insert a Pivot Table go to Insert and click on Pivot Table as in the following figure:

In this source table we have entered for each day of the year the sales made in a company with the details of the agent and the point of sale.

In this example that we will analyze in the next chapters we have created a Pivot table that