MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Tips

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.

Lookup Row & Sheet

Lookup Row & Sheet »

November 13, 2017

How to write an Excel formula that will lookup a value on a different sheet based on which product is selected. How to pull data from a different worksheet for each product.

Sort Slicers

Sort Slicers »

November 13, 2017

Slicers in Excel tend to be arranged alphebetically. This article will show you how to arrange them in another sequence.

VLOOKUP from Outline Data

VLOOKUP from Outline Data »

November 10, 2017

Someone taking an Excel certification test missed a question. They were trying to overthink the question. It doesn't require an insane array formula. A simple IFNA will solve it. While there are many ways to solve everything in Excel, sometimes the simplest ways go overlooked.