MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


Pivot Table Timelines Forget What Year it Is

Pivot Table Timelines Forget What Year it Is »


March 16, 2018

Today's bug in Excel was discovered by Mynda Treacy and involves the timeline slicer in Excel. Mynda was working on videos for one of her awesome courses and found a pivot table where the timeline slicer kept adding new years.

Merge Worksheets Using Consolidate

Merge Worksheets Using Consolidate »


March 15, 2018

Excel offers a Consolidate command on the Data tab. This ancient command lets you consolidate data from multiple worksheets or workbooks, even if the data is a different shape.

Using AutoSum in Tables

Using AutoSum in Tables »


March 14, 2018

Direct from the Excel development team, Project Manager Ash Sharma shares another of his seven favorite tips about Excel. This is one that I never knew.

Set Defaults For All Future Pivot Tables

Set Defaults For All Future Pivot Tables »


March 13, 2018

I was horrified when I saw my first Excel 2007 pivot table. For some inconceivable reason, Excel was putting three different row fields in column A. Why would anyone think this was a good idea?

Alt+E+S for Paste Special Options

Alt+E+S for Paste Special Options »


March 12, 2018

It is mouse-free Monday. Today, I want to introduce you to Alt+E+S which is the gateway to the wonderful world of Paste Special options.

I Use VLOOKUP Far More Often than Index and Match

I Use VLOOKUP Far More Often than Index and Match »


March 10, 2018

I conceptually know in my head that INDEX and MATCH are superior to VLOOKUP. But to this day, I will confess that I use VLOOKUP 99% of the time. Here's why.

Excel is Revealing Protected Very Hidden Worksheets

Excel is Revealing Protected Very Hidden Worksheets »


March 9, 2018

You likely know that there is a way to hide worksheets. But did you know that Excel can Very Hide worksheets? This is a trick used by the super-Exceller for years. But now, Microsoft is calling us out when we very-hide something with VBA.

Back in to an Answer Using Goal Seek

Back in to an Answer Using Goal Seek »


March 8, 2018

Buried under the What-If Analysis drop down on the Data tab, the Goal Seek function will allow you to back in to an answer quickly and without having to play the Higher!/Lower! Game.

Replacing VLOOKUP using the Data Model and Relationships

Replacing VLOOKUP using the Data Model and Relationships »


March 7, 2018

Don't have Power Pivot? Doesn't matter. Most of Power Pivot is built in to Excel 2013 and even more in Excel 2016. Today, our tip from Ash is joining tables in a pivot table.

TEXTJOIN with an Array

TEXTJOIN with an Array »


March 6, 2018

One of the best new features coming in Excel 2019 is the TEXTJOIN function. If you've ever been frustrated with CONCATENATE, you will love TEXTJOIN. Today, some secret versions of TEXTJOIN.

F4 to Repeat the Last Action

F4 to Repeat the Last Action »


March 5, 2018

There is a useful key in Excel that leads a secret double-life. Most people know that F4 is famous for adding dollar signs to your formula references. But do you know what F4 does when you are not in Edit mode?

I Will Sometimes Merge a Cell to Create a Really Wide Sparkline

I Will Sometimes Merge a Cell to Create a Really Wide Sparkline »


March 3, 2018

Merged cells in Excel are evil. You should never merge a cell. The Center Across Selection option will give you the same look without the evil of merged cells. Saturday confession: I merge cells to create really long sparklines.