MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

How to Use RegEx in Microsoft Word

How to Use RegEx in Microsoft Word »

August 20, 2018

Word comes with a powerful search capability. Using wildcards is also a great feature. However, it is possible to use VBA and VBScript Regular Expressions library together for more sophisticated searches. Today we discuss how to get RegEx into the VBA, and make complicated searches easier.

Geography Data Type

Geography Data Type »

August 7, 2018

Office 365 subscribers will soon start seeing a new feature on the Data tab: Linked Data Types for geography or stocks.

Sentence Case in Excel

Sentence Case in Excel »

August 6, 2018

Although Excel is not a word processing application, sometimes you might need to change the given text to Sentence Case. There is a way to do this by combining some text functions for a single sentence, but multiple sentences. Today we will discuss about a VBA solution to achieve this in Excel.

Surveys and Forms in Excel

Surveys and Forms in Excel »

July 26, 2018

A new survey tool debuted on the Excel Insert tab on July 24. The official name of the feature is Forms... complete with a new Office-type "F" logo. But that seems confusing since Excel VBA already has User Forms and the Developer tab has Forms controls. This Forms tool is very easy to use. I've been using an online version of this in my live Power Excel seminars since March 2018 and it works very well. The change today is the perfect embedding of the Forms experience in the workbook.

Checkmarks in Excel

Checkmarks in Excel »

July 19, 2018

Creating checkmarks in Excel is usually a hassle. Yes, you could type a capital letter P and change the font to Wingdings 2, but who can remember that combination?

Magnifier in Excel

Magnifier in Excel »

July 12, 2018

When I am doing a presentation at a Staff Meeting or my Live Power Excel Seminar, I have started to use the Windows Magnifier to show details in the Ribbon or dialog boxes.

Pivot Table With Text in Values Area

Pivot Table With Text in Values Area »

July 10, 2018

Can you build a pivot table with text in the values area? Susan from Melbourne Florida has a text field and wants to see the before and after of that text.

Commas Stop Working in Formulas

Commas Stop Working in Formulas »

July 6, 2018

A bizarre problem today: Suddenly, Excel formulas will no longer accept a comma as the argument separator. You start to type =VLOOKUP(A2, when you use the mouse or arrow keys to select the lookup table, Excel beeps with "There is a problem with this formula." Yes! Of course there is a problem with this formula…I haven't finished typing the formula yet.

Fill Merged Cells Down

Fill Merged Cells Down »

July 2, 2018

Merged cells are the problem today, but these are vertical merged cells. Take a look at the figure below. If there are 7 rows for the Midwest region, the word Midwest appears only on the first row. Someone is then merging the remaining six blank cells with the Midwest cell to create a merged cell that is 7 cells tall.

AutoSum from Top

AutoSum from Top »

June 28, 2018

AutoSum is the first formula that most people learn in Excel. Today's question: Is there a way to place the AutoSum at the top of a column of numbers?

Show or Hide All Shapes

Show or Hide All Shapes »

June 26, 2018

Today's question is from me. I have a workbook that I use frequently. It has a mix of illustrations and macro buttons. I've been using the same workbook for years.

Power Map From Two Data Sets

Power Map From Two Data Sets »

June 21, 2018

A question from my Melbourne Florida Power Excel seminar: Is it possible to display two different data sets in Excel Power Map (Excel 2013) or Excel 3D Map (Excel 2016+). I knew this was possible because I've seen it in a Microsoft demo. I just wasn't sure how Microsoft did it.