MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips


What Characters are Legal in a Worksheet Name

What Characters are Legal in a Worksheet Name »


February 16, 2018

Worksheet names can be changed to almost anything. It is the *almost* part that is fuzzy for me. There are a few characters that aren't allowed in a worksheet tab. But how do you figure out which characters they are? Today, I will show you how.

How Many Days Between X and Y

How Many Days Between X and Y »


February 15, 2018

Counting the numbers of days between two dates in Excel seems deceptively simple. But if you simply subtract the earlier date from the later date, you might be mis-counting the number of days.

Distinct Count in Pivot Tables with the Data Model

Distinct Count in Pivot Tables with the Data Model »


February 14, 2018

Today's tip is from Excel project manager Ash Sharma. I asked Ash for his favorite Excel tips and will feature them for the next seven Wednesdays. His first tip involves a really simple way to calculate a distinct count in an Excel pivot table. It all comes down to one simple check box.

Guy Who Spent Nine Hours Getting to Bottom of Excel Worksheet is Now an Excel Guru

Guy Who Spent Nine Hours Getting to Bottom of Excel Worksheet is Now an Excel Guru »


February 13, 2018

You've likely seen the viral YouTube video of Hunter Hobbs pressing the Down arrow key in Excel for 9 hours to get to row 1048576. I saw that video and shook my head. Why would he waste nine hours when Ctrl + Down Arrow would get you there in an instant? I considered the guy hopeless.

Do All Lookups and Sum the Results

Do All Lookups and Sum the Results »


February 9, 2018

Ron wants to do a bunch of VLOOKUPs and sum the results. There is a single-formula solution to this problem.

Calendar in Excel with One Formula (Array Entered, of Course!)

Calendar in Excel with One Formula (Array Entered, of Course!) »


February 5, 2018

Create calendar in Excel with one formula by using array-entered formula.

Using Variable Ranges for Unique Counts

Using Variable Ranges for Unique Counts »


January 29, 2018

Say that you want to be able to count unique items from a list, but with a twist. Read this guest article from Bob Umlas to learn using variable ranges for unique counts.

How to Repair a Corrupt Excel File?

How to Repair a Corrupt Excel File? »


January 24, 2018

It can happen: the long-running Excel file that has all of your information suddenly won't save or won't open. Excel tells you that they are going to attempt a repair, but that repair either does not work or it removes all of your macros. What do you do?

Narrowing Down What Caused a Crash

Narrowing Down What Caused a Crash »


January 22, 2018

Sometimes Excel simply gives a message along the lines of - "Excel has stopped working. We are sorry for any inconvenience". When you get such a message, you might press Ctrl + Alt + Delete and open the workbook again (hopefully having saved whatever work you had done!), wanting to step through the code to find the offending statement. When you single-step through the code, all may work fine, but when you run it at full speed, once again it may crash. How can you find the offending statement?

Can Word Hyperlink to a Specific Sheet and Cell in Excel?

Can Word Hyperlink to a Specific Sheet and Cell in Excel? »


January 19, 2018

Can a hyperlink in Word open a specific Excel file, jump to a sheet and jump to a cell? The syntax is fairly tricky, but it seems to work.

VLOOKUP with Multiple Results

VLOOKUP with Multiple Results »


January 15, 2018

Suppose you want to produce a report from a set of data as if you filtered on a column. But what if you wanted a formula-based version of the same thing?

List all Files in a Folder in Excel Using Power Query

List all Files in a Folder in Excel Using Power Query »


January 5, 2018

Need to get a list of all of the tax invoice PDF files from a folder in to an Excel spreadsheet. This is easy to do if you are using Excel 2016 on a Windows PC using the new Get & Transform Data tools.