MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Creating a Hierarchy in a Pivot Table

Creating a Hierarchy in a Pivot Table »


April 26, 2018

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?

Improved Handling of Empty Cells in Pivot Table Source

Improved Handling of Empty Cells in Pivot Table Source »


April 25, 2018

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

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.

Round Up to the Next 20

Round Up to the Next 20 »


April 17, 2018

Sandy works for a construction company. They sell flooring in packages of 20 Board Feet. When their estimators go to a job, they might say that they need 441 board feet of material. Sandy needs a way to always round up to the next 20.

Summary of Summarize Data Week

Summary of Summarize Data Week »


April 14, 2018

For the last five days, I showed five different ways to create a customer summary from a detailed data set. Now it is time to vote for your favorite.

Summarize Data with Pivot Tables

Summarize Data with Pivot Tables »


April 13, 2018

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at using a pivot table to summarize the data.

Summarize Data with Consolidate

Summarize Data with Consolidate »


April 12, 2018

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at using the ancient Consolidate command to solve the problem.

Summarize Data with Advanced Filter

Summarize Data with Advanced Filter »


April 11, 2018

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Today I look at Advanced Filter and SUMIF to solve the problem.

Summarize Data with Remove Duplicates

Summarize Data with Remove Duplicates »


April 10, 2018

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. Adam from one of the large oil companies in Dallas provides a faster way using Remove Duplicates.

Summarize Data with Subtotals

Summarize Data with Subtotals »


April 9, 2018

Your manager needs a summary of total revenue, cost, and profit for each customer in a large data set. I typically solved this using the Subtotals command. But during a recent seminar in Dallas, an attendee suggested there is a faster way. This week is Summarize Data week and I will look at five different ways to solve the problem.

Excel Sort by Color with VBA

Excel Sort by Color with VBA »


April 5, 2018

Previously in Podcast 2093, I showed a simple VBA sort that works if you are not sorting by color. Today, Neeta asks for the VBA to sort Excel data by color.

Learn Excel By Playing This Game

Learn Excel By Playing This Game »


April 2, 2018

There are many different ways to learn Excel, and each of them have its own advantages as different people have different learning styles. For those who like to learn with fun and real experience involved there is a gamified tool to learn Excel – Hot Key Excellence (www.hotkeyexcel.com).