MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

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.

Place People on Bell Curve

Place People on Bell Curve »

June 19, 2018

Jimmy in Huntsville wants to plot a bell curve showing the average scores of several people. When Jimmy asked the question during my Power Excel Seminar, I thought back to one of my more popular videos on YouTube.

Combine Based on Common Column

Combine Based on Common Column »

June 14, 2018

David from Florida asks today's question: "I have two workbooks. Both have the same data in column A, but the remaining columns are different. How can I merge those two workbooks?"

Comma Just Comma

Comma Just Comma »

June 7, 2018

There are a few things that continue to drive me crazy about Excel. One of them is the "Comma Style" icon that is in the middle of the Home tab of the Ribbon. The tooltip for this icon claims that it will add a thousands separator to your numbers.

VBA Insert Picture Bug

VBA Insert Picture Bug »

June 6, 2018

I needed to have an Excel macro insert a picture in Excel. I turned on the macro recorder, inserted the picture. But when I used that recorded code, the picture will not appear on any other computer. Instead, I get a red X where the picture should be.

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.