Excel for Mac: Create Custom Lists for Sorting

Last week I was delivering Excel training to a large group. Part of the training covered how to create custom lists for sorting. If that’s not something you’re familiar with, look at this screenshot… I want to sort this multi-column list by Store...

Power Query: Combine Excel files with Different Sheet Names

Last week I was running a Power Query course. I was showing the delegates how to use Power Query to combine rows from multiple Excel files into a single table in a new file. In the demo, each file had a sheet called Sheet1 and that sheet contained the data to be...

Excel: Calculate First & Last Working Day of a Month

In this week’s video I show you how to calculate the first and last working day of a month. This can be particularly useful for project planning, payroll calculations, and various administrative tasks. Here’s a list of invoices. As you can see, each invoice has...

Excel: VLOOKUP – Next Largest Value

Whilst VLOOKUP is great for many tasks, one of its limitations is that when using TRUE as the 4th argument, to indicate a non-exact match, VLOOKUP can only return the largest value that is less than the value being looked up. In the screenshot below, B3 contains the...

Excel: Display Percent Complete in Task Tracker

This video is based on a request for help that I received from Tim, one of my channel subscribers. He said…”I have a table of tasks which fall under different headings and the final column is complete/not complete. I want to create a dashboard using Pivot...