MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

Insert 2 Decimals

Insert 2 Decimals »

May 22, 2018

A question from my Atlanta live Power Excel seminar: Is there a way to type 12345 and have Excel enter 123.45? I call this Adding Machine mode, because it feels like the 1970's era adding machine with the switch to automatically add two decimal places.

Replace Spaces With Empty

Replace Spaces With Empty »

May 18, 2018

Frank from New Jersey downloads data every day where the blank cells actually contain one or more spaces. This is frustrating because =ISBLANK() won't recognize these cells as empty.

Loan Survey Explosion

Loan Survey Explosion »

May 17, 2018

Today's question from Quentin who was at my Atlanta Power Excel seminar. Quentin has to generate the same 7 survey questions for each of 1000+ customers in Excel.

Return All VLOOKUPs

Return All VLOOKUPs »

May 16, 2018

Kaley from Nashville is working on a ticketing spreadsheet. For each event, she chooses a ticketing plan. That ticketing plan could indicate anywhere from 4 to 16 ticket types for the event. Kaley wants a formula that will go to the lookup table and return *all* matches, inserting new rows as appropriate.

Date Time to Date

Date Time to Date »

May 15, 2018

Ian in Nashville gets data every day from a system download. The date column contains Date+Time. This makes the pivot table have multiple rows per day instead of a summary of one cell per day.

Slicer Selections in Title

Slicer Selections in Title »

May 14, 2018

Joy attended my Houston Power Excel seminar and asked if there was a way to show the items selected in a slicer in a cell above the pivot table.

Some Trailing Minus Numbers

Some Trailing Minus Numbers »

May 10, 2018

I download data from the system and some of my numbers have a trailing minus sign. How do I convert only the ones with a trailing minus sign?

Filter by Quarter in Pivot Table with Daily Dates

Filter by Quarter in Pivot Table with Daily Dates »

May 9, 2018

How can you filter a pivot table by quarter when your pivot table data only has daily dates? It is fairly easy to do using these steps.

Problem With Two AutoSum

Problem With Two AutoSum »

May 7, 2018

You have two columns of numbers. There is an AutoSum at the bottom of each column. While inserting rows, the AutoSum in one column stops working. What could be causing that?

Slicer for Two Data Sets

Slicer for Two Data Sets »

May 4, 2018

Controlling multipled pivot tables is one of the main benefits of slicers. But both of those pivot tables have to come from the same data set. When you have data from two different data sets, using one slicer to control both data sets becomes more difficult.

Pivot Table Median

Pivot Table Median »

May 2, 2018

This question comes up once every decade: Can you do a Median in a pivot table. Traditionally, the answer was No. I remember back in 2000 when I hired Excel MVP Juan Pablo Gonzalez to write an awesome macro that created reports that looked like pivot tables but had Medians.

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?