MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Excel Make Pivot Tables Expandable Using Ctrl+T. Photo Credit: Kyle Glenn at Unsplash.com

Excel 2019: Make Pivot Tables Expandable Using Ctrl+T »


May 16, 2019

If you choose all of columns A:J and you later want to add more records below the data, it takes only a simple Refresh to add the new data instead of having to find the Change Data Source icon. In the past, this made sense. But today, Change Data Source is right next to the Refresh button and not hard to find.

Change What Drives You Crazy About Excel. Photo Credit: Mikail Duran at Unsplash.com

Excel 2019: Change What Drives You Crazy About Excel »


May 15, 2019

I‘ve managed to lobby the Excel team to get a few changes into Excel. It isn‘t always easy. It took me eight years of lobbying to get the Repeat All Item Labels feature added to Excel 2010. It took seven years to get the Pivot Table Defaults feature added.

Excel Specify Defaults for All Future Pivot Tables. Photo Credit: André Roma at Unsplash.com

Excel 2019: Specify Defaults for All Future Pivot Tables »


May 13, 2019

It took me six years, but I finally convinced the Excel team that a lot of people prefer Tabular layout for pivot tables to the Compact layout that became the default layout in Excel 2007. If you have Office 365, you now have the ability to specify pivot table defaults.

Excel Find the True Top Five in a Pivot Table. Photo Credit: Zhu Hongzhi at Unsplash.com

Excel 2019: Find the True Top Five in a Pivot Table »


May 9, 2019

Pivot tables offer a Top 10 filter. It is cool. It is flexible. But I hate it, and I will tell you why.

Excel Why Do Pivot Tables Count Instead of Sum?. Photo Credit: Majkl Velner at Unsplash.com

Excel 2019: Why Do Pivot Tables Count Instead of Sum? »


May 8, 2019

In almost every seminar, someone asks why pivot tables default to count instead of sum. This long-standing problem was fixed in May 2018 for Office 365 subscribers. The Count was triggered if you had one revenue cell that contained text or an empty cell.

Change the Calculation in a Pivot Table. Photo Credit: NordWood Themes at Unsplash.com

Excel 2019: Change the Calculation in a Pivot Table »


May 6, 2019

Pivot tables offer a myriad of calculations in the Field Settings dialog box. Here is a faster way to change a calculation.

Another Way to Calculate Year-Over-Year. Photo credit: Denys Nevozhai at Unsplash.com.

Excel 2019: Another Way to Calculate Year-Over-Year »


May 2, 2019

Instead of creating a formula outside of the pivot table, you can do this inside the pivot table. Start from the image with column D empty. Drag Revenue a second time to the Values area.

Create a Year-over-Year Report in a Pivot Table. Photo Credit: Henry & Co at Unsplash.com

Excel 2019: Create a Year-over-Year Report in a Pivot Table »


May 1, 2019

Let’s say you have two years’ worth of detail records. Each record has a daily date. When you build a pivot table from this report, you will have hundreds of rows of daily dates in the pivot table.

Excel Fill in the Blanks in the Annoying Outline View. Photo Credit: Devin Avery at Unsplash.com

Excel 2019: Fill in the Blanks in the Annoying Outline View »


April 29, 2019

If your pivot table is in Tabular or Outline Form and you have more than one row field, the pivot table defaults to leaving a lot of blank cells in the outer row fields.

Excel Format One Cell in a Pivot Table. Photo Credit: Atilla Taskiran at Unsplash.com

Excel 2019: Format One Cell in a Pivot Table »


April 25, 2019

This is new in Office 365 starting in 2018. You can right-click any cell in a pivot table and choose Format Cell. Any formatting that you apply is tied to that data in the pivot table.

Excel Format a Pivot Table. Photo Credit: 五玄土 ORIENTO at Unsplash.com

Excel 2019: Format a Pivot Table »


April 24, 2019

The Design tab has a gallery with 84 built-in formats for pivot tables. Choose a design from the gallery and the colors change.

Excel Rearrange fields in a pivot table. Photo Credit: Pierre Châtel-Innocenti at Unsplash.com

Excel 2019: Rearrange fields in a pivot table »


April 22, 2019

The power of pivot tables is the ability to rearrange the fields. If your manager decides you should put Regions across the top and products down the side, it is two drags to create the new report.