Insert table

In this video, we’ll look at how to make a custom table style the default table style in new workbooks. Setting a custom table style as the default table style is a little tricky. You need to create an Excel template that contains the custom style, and then make sure Excel uses the template when it …

In this video, I’ll give a brief introduction to structured references. A structured reference is a term for using a table name in a formula instead of a normal cell reference. Structured references are optional, and can be used with formulas both inside or outside an Excel table. Let’s take a look …

In this video, we’ll set up the XLOOKUP function to return multiple values in a dynamic array. In this worksheet, we have an example we looked at previously. On the left, we have quantity-based discounts, and on the right, we have some random quantities. Let’s set up XLOOKUP to return all results in …

In this video, we’ll look at how to use the SUMIFS function with an Excel Table. On this worksheet, I have two identical sets of order data. I’m going to walk through the process of constructing a summary of sales by item for both sets of data. With the data on the left, I’ll use standard formulas …

In this video, we’ll look at shortcuts you can use in an Excel Table. Excel Tables are one of Excel’s most powerful features for working with data. To create a table, just select a cell in the data and use Control + T. New tables have filters enabled. To toggle filters, use Ctrl-Shift-L in Windows, …

Have you ever had to select the winners in a contest? It’s easy when you draw names out of a hat, but how would you do it in Excel? In this video we’ll show you a simple way to do it using the RAND function. Here’s a list of names that represent entries in a contest. Suppose we’d like to pick five …

In this video we’ll look at how to calculate and highlight expiration dates. Let’s say your company has started a membership program of some kind and your boss just sent you a set of data. She’s given you a list of 1,000 people that have renewed a membership in the last year or so, and she’s looking …

In this video, we’ll look at how to create a Pareto chart. A Pareto chart plots the distribution of data in columns by frequency, sorted in descending order. A line showing cumulative percentage is plotted on a secondary axis. Starting with Excel 2016, the Pareto chart is a built-in chart type. In …

In this video, we’ll look at a way to use basic formulas to randomly assign people to teams. Here we have a list of 36 people. Let’s say we want to randomly assign each person to a team of 4 people so that we have a total of 9 teams with 4 people in each. I’m going to solve this problem in small …

In this video, we’ll look at how to use conditional formatting to shade every other row in a table. This is sometimes called “zebra striping”. In this spreadsheet, we have a table of employees with a small amount of formatting. To get shading on every other row, we could just convert this table to a …