Create a Flattened Pivot Table for Reuse

December 2, 2022 - by Bill Jelen

Problem: Why would they put three different kinds of information in column A? Doesn’t this make pivot tables as silly as the person who created the bad data set back in “Add a Customer Number to Each Detail Record”?

The default Compact layout puts Region, Customer, and Product all in Column A. — Figure 842. Microsoft is mixing 3 fields in one column.

My goal is to use the pivot table to make a summary, then convert to values for use as a new data set. Having three different fields in column A is really bad form.

Note: I’ve met one person who likes compact view. He has 15 fields in the Row Area of his report. Compact layout allows that report to fit on a screen.

Strategy: It is very annoying that Microsoft made this new view be the default. Luckily, it is only a few clicks to go back to the proper view.

More Reading

Convert Your Data to a Table Before Adding Records »

December 1, 2022 - by Bill Jelen

I have 100 new records to paste below the original data that is in the pivot table. How do I do that?

Why do I Get a Count Instead of a Sum? »

November 30, 2022 - by Bill Jelen

When I choose Revenue, it goes to the Rows instead of the Values area. When I drag Revenue to the Values area, it defaults to a Count instead of a Sum. As of 05/2018, this has been fixed in Office 365!

Update Data Behind a Pivot Table »

November 29, 2022 - by Bill Jelen

I’ve discovered that some of the underlying data in my pivot table is wrong. After I correct a number, the pivot table does not appear to include the change.

Microsoft Excel Inside Out (Office 2021 and Microsoft 365)

December 2021

Dive Into Microsoft Excel for Office 2021 and Microsoft 365 and really put your spreadsheet expertise to work. This supremely well-organized reference packs hundreds of timesaving solutions, tips, and workaroundsall you need to make the most of Excels most powerful tools for analyzing data and making better decisions.

Programming PowerPoint with VBA

February 2022

This book assumes you already use PowerPoint and want to automate or enhance your presentations using Visual Basic for Applications (VBA). This book includes VBA samples for working with layouts, themes & masters, creating tables, drawing objects, charting, animation effects and event programming.

Your Excel Survival Kit - Second Edition

March 2021

This book helps you realize that Excel is on your side. It can be your friend, not your enemy. If and when you upgrade your Excel skills you will experience many positives: you will learn to love Excel, you will learn how to use Excel to become an invaluable asset in your workplace.

Master Your Data with Power Query in Excel and Power BI

August 2021

Despite the moniker "data monkey," we information workers are often more like data magicians. Our data seldom enters our world in a ready-to-consume format; it can take hours of cleaning, filtering, and reshaping to get things ready to go. Power Query will make this process faster the first time and reduce it to a single button click every subsequent time.

see more products »

Create a Flattened Pivot Table for Reuse

More Reading

Featured Products