MrExcel Publishing
Your One Stop for Excel Tips & Solutions
Microsoft MVP

Creating a Hierarchy in a Pivot Table

April 26, 2018 - by Bill Jelen

Creating a Hierarchy in a Pivot Table

Recently, a friend of mine wondered about the Drill-Down and Drill-Up buttons in the Pivot Table Tools tab of the Ribbon. Why are these perpetually greyed out? They take up a lot of space in the Ribbon. How is anyone supposed to use them?

Drill Down & Drill Up buttons
Look for the Power Pivot tab to the left of PivotTable Tools tabs

After some research, there is a way to use them, but you have to use the Data Model and use the Power Pivot diagram view to create a hierarchy. If you don't have the Power Pivot tab in your Ribbon, you will have to find a co-worker who has the button in order to create the hierarchy. (Or, if you just want to try the feature, download the Excel file that I created: Hierarchy.xlsx)

Pivot Table Tools - Analyze on the Ribbon
Pivot Table Tools - Analyze on the Ribbon

First step - convert your pivot source data set to a table using either Home - Format as Table or Ctrl + T. Make sure that the option for My Table Has Headers is selected.

continue reading »

More Reading

Improved Handling of Empty Cells in Pivot Table Source

Improved Handling of Empty Cells in Pivot Table Source »

April 25, 2018 - by Bill Jelen

Quietly and without any fanfare, Microsoft has changed the default behavior for handling empty cells in a pivot table source. In my live Power Excel seminars, when I get to the pivot table section of the day, I can count on at least one person asking me: "Why do my pivot tables default to Count instead of Sum when I drag Revenue to the pivot table." If you have Office 365, a change is coming to Excel to reduce the number of times this happens.

How to Wrap Data to Multiple Columns in Excel

How to Wrap Data to Multiple Columns in Excel »

April 19, 2018 - by Bill Jelen

Gwynne has 15 thousand rows of data in three columns. She would like to have the data print with 6 columns per page. For example, the first 50 names in A2:C51, then the next 50 names in E2:G51. Then move the third 50 rows to A52:C101 and so on.

read more news »

Featured Products

Supercharge Excel When You Learn to Write DAX For Power Pivot

Supercharge Excel When You Learn to Write DAX For Power Pivot »

Hands-on book to learn and master the DAX language!

Excel Subtotals Straight to the Point

Excel Subtotals Straight to the Point »

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.

M is for (DATA) MONKEY

M is for (DATA) MONKEY »

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.

Supercharge Power BI

Supercharge Power BI »

If you want to be able to supercharge Power BI, you need to learn to write DAX. Data Analysis Expressions (DAX) is the formula language of Power BI and Power Pivot for Excel. This book prompts the reader to put your new skills to the test. Written to give you hands-on practice using Power BI Desktop and writing DAX.

Your Excel Survival Kit

Your Excel Survival Kit »

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.

100 Excel Simulations

100 Excel Simulations »

Covering a variety of Excel simulations, from gambling to genetics, this introduction is for people interested in modeling future events, without the cost of an expensive textbook.

see more products »