Pivot for office workers: Using Excel 365 and 2021

What we’re going to cover

Pivot tables are often looked at with respect and reluctance. Their results can appear very sophisticated. But indeed, most of day-to-day Pivot tasks can be completed with only a bunch of mouse clicks.

By far the most common real-life case uses single lists and tables as data base. We therefore will focus on that. The handling of complex data models would exceed scope and intention of this little booklet. To get such a task done, I recommend other books existing already.

All screenshots are done using Excel 365. Still, there is hardly a difference to Excel 2021 and previous versions.

All example data can be downloaded from

www.ShortAndSpicy.online

Now have fun demystifying Pivot tables!

Pivoting, what exactly is it about?

When pivoting, it doesn’t only mean flipping data around. We can also summarize data, find intersections, and customize the figures to deliver relevant information.

Maybe this made it already clear: Pivot functions do include personal preferences. According to their tastes, different editors may prefer different solutions. After reading this booklet, you will know how to create your favoured one.

Generally, most data to be processed by Pivot functionality, is delivered by different software. For many of them, Excel has interfaces, too. Which means, Excel could access this software directly. Still, most administrators of the respective solution will block it. Instead, they supply exported data so they can be handled separately without posing a threat to the source. Therefore, we’ll be working with ‘flat’ Excel sheets without calculations nor relations. Just like in normal office life.

The window

For our example, we’ll be using an example file with fictional data. It will be furtherly developed from chapter to chapter. As not all possibilities can be combined, we have a new file for each chapter, so nothing interferes needlessly. You can download the zip from www.shortandspicy.online. To be processed in a Pivot table, this file is very short. Still, for our case, it’s absolutely sufficient.

Generally, all data sources must meet three different conditions:

They must have a simple, even structure. It means, no areas with merged or split cells

They must have a unique header

Each row must