MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

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.

Default File Open Location

Default File Open Location »

November 21, 2017

If you always want Excel to default to a particular folder when you do File, Open... this article is for you.

Press F9 Until Close

Press F9 Until Close »

November 21, 2017

Lev is commissioner of a competitive swim league. He writes: "I am the commissioner of a swim league. There are eight teams this year. Each team hosts one meet and is the home team. A meet will have 4 or 5 teams. How to arrange the schedule so every team swims against every other team twice? In the past, when we had 5, 6, or 7 teams, I could solve it by pressing F9 until close. But this year, with 8 teams, it is not coming out."

Combine 4 Sheets

Combine 4 Sheets »

November 20, 2017

Excel combine several worksheets into a single worksheet. Each worksheet might have a different number of records from day to day, so formulas are not the way to go. Instead, a little-known tool called Power Query will let you merge the data simply and quickly.

How many Kits Available

How many Kits Available »

November 17, 2017

Today, an interesting Excel problem about bills of material. You have a lot of raw materials. Each item might be assembled into several different top-level assemblies. Based on the raw material on hand, do you have enough to fulfill an order for a certain item?

Chart Hierarchy in X-Axis

Chart Hierarchy in X-Axis »

November 16, 2017

Charts created from pivot tables have an odd x-axis where you can show a hierarchy of fields... Perhaps Quarters and Months. This article shows you how to create a chart like this.

Filter to 27 Invoices

Filter to 27 Invoices »

November 15, 2017

Excel filters are great, but what if you need to find all records that match a long list of criteria? You need to find all of the line items on 27 invoices. Rather than click click click over and over in the filter, the Advanced Filter will quickly solve the problem

Filter Subtotaled Data

Filter Subtotaled Data »

November 14, 2017

Can Excel apply a filter to data that has been subtotaled? This article will show you how.

Paste Values for External Links

Paste Values for External Links »

November 14, 2017

In Excel, can you break all external links? Imagine if you could Paste Values, but only for the links that point to another workbook.