MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

Save As Keeping Original Open

Save As Keeping Original Open »

June 5, 2018

There are many times in a month where I need an Excel workbook to create many copies of itself. I usually get into a logic bug by opening *the* workbook and I start to write code to loop through a list and use File, Save As to save a copy of the workbook.

Pivot Table Search Bug

Pivot Table Search Bug »

May 31, 2018

An interesting problem from Sam in Nashville: Sam wonders why his pivot table does not correctly refresh after he uses the Search box in the Filter drop-downs in his pivot table.

Pivot Table No Custom Sort

Pivot Table No Custom Sort »

May 30, 2018

Sometimes, if you actually know the name of a feature, it is easy to find how to control that feature. But with today's topic, most people who get stung by this "bug" have no idea why it is happening. They will report a symptom along the lines of: "For some reason, two of our employees keep appearing at the top of our pivot tables". I will often predict that the employee is named June or May or maybe even Friday.

Round To Quarter Hour

Round To Quarter Hour »

May 29, 2018

Another question from my Atlanta Power Excel seminar: How can you round billable time up to the next quarter hour?

Inserting Alternating Rows

Inserting Alternating Rows »

May 24, 2018

Today's question from the Atlanta Power Excel seminar. "I have data formatted with every other row in grey. When I insert two new rows in the middle of the data, both of the new rows end up grey instead of following the pattern."

VLOOKUP To Two Tables

VLOOKUP To Two Tables »

May 23, 2018

Today's question from Flo in Nashville: I need to do a VLOOKUP for a series of item numbers. Each item number is either going to be found in Catalog A or in Catalog B. Can I write a formula that first searches Catalog A. If the item is not found, then move on to Catalog B?

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.