Advanced Microsoft Excel tutorials

Extract Emails from Outlook to Excel using Power Query

Step-by-step tutorial on using Power Query in Excel to extract and format email addresses from Outlook.

Excel - Combine Multiple Tables - Relationships (PivotTables) and XLOOKUP

You can set up a relationship with two or more tables in Excel to create a PivotTable. XLOOKUP and VLOOOKUP also work, but you can skip those great functions and set up Relationships. For example, one Excel table contains the Employee ID and name.

Excel PivotTable - Table VS. Range as source - Which is better?

In Excel, a PivotTable can be based on a Range or on a Table. I encourage you to base your PivotTable on a Table. Initially, both a range and table will be correct.

Excel - IF Function using AND and OR

One of my favorite functions is the IF function. The IF function allows you to use true and false conditions. For example, if the value in cell B2 is over $5,000, give them a 2% discount.

Styles in Tables and PivotTables and Grand Total Calculation - Excel

When you create a Table or PivotTable in Excel, you can work with Styles. There are three style groups - Light, Medium, and Dark. Within each group are multiple styles.

Excel - Getting started with Data Validation List

Creating a Data Validation List is a great way to make sure you have "good" data. For example, I may type Human Resources as a department, but later in my data, I see HR. I know that HR and Human Resources are the same, but Excel doesn't.

Excel - Financial Functions for a House or a Car

Excel has a lot of Financial Functions. We will look at several functions for a home purchase or a car purchase.

Breakeven Analysis - Breakeven in Units and Breakeven in Sales

The Contribution Margin is a company's revenue minus its variable cost. You can use the Contribution Margin to find the Breakeven Point.

Excel - Combine the SORT, UNIQUE, and COUNT functions

Excel has dynamic array functions. Two of the dynamic array function are SORT and UNIQUE. I will combine SORT and UNIQUE and also combine them with COUNT and the COUNTA functions. This came from a user that asked how many unique zip codes were in a spreadsheet.

Excel Import Data from the Web

Excel allows you to pull data on a webpage into Excel. This is a great time-saving feature. Getting data from a webpage is done in Excel on the Data Tab using Get and Transform Data group, From Web command.

Excel - Turn on Multiple Filters in a PivotTable

Filter in PivotTables only allows one filter by default. To filter by more than one field, you need to turn on the PivotTable Option - Totals and Filters tab and check Allow multiple filters per field.

PivotTables - 3 Annoying Things FIXED!

PivotTables are one of the best features in Excel. PivotTables allow you to summarize, analyze and chart complex data. There are 3 annoying things in PivotTables that users always ask me about.