Excel: Better Variance Charts – Dynamic Columns, Lines & Arrows

Dynamic column and bar charts are great for showing variances. In this tutorial, you will find 4 methods of visualizing variances in Excel: with conditional formatting – by inverting fill color for negative values, with the help of error bars, and by adding conditionally formatted arrows on top of or under the chart.

How to Use the Excel INDIRECT Function

The INDIRECT() function comes very handy when need to feed different ranges to certain formulas, especially when you refer to ranges in other sheets. This tutorial explains how the INDIRECT() function works and how to use in a real-life scenario, gathering data from different tabs.

Dependent Drop Down Lists in Excel

Learn how to create dependent dropdown list in Excel lists using the INDEX, OFFSET, CHOOSE and INDIRECT functions.

How to Use INDEX and MATCH in Excel

Learn how the INDEX and MATCH functions work individually and how putting them together allows you to conduct flexible lookups.

Lookup Within Boundaries

This tutorial takes you through three powerful Excel formulas that lookup values in ranges that include lower bound and upper bound values. They are SUMIFS, SUMPRODUCT and INDEX/MATCH. Find the one that works best for your scenario.

Excel SUMIFS Function

Learn how to use the SUMIFS function in Excel to sum values based on multiple conditions. Free practice file included.

Excel Index Dynamic Range to find First Empty Cell

Learn how to create a description column that includes the number of sub-items within a main item and also provides the description of the main item. You can achieve that in Excel with a formula combining the COUNTA, INDEX and MATCH functions.

Excel Arrays: Count Unique Items with MMULT, INDIRECT & ROW Functions (Part 3/3)

Consider a scenario, where you need to return a unique count across rows rather than column. You could use the TRANSPOSE function, but in older versions of Excel, TRANSPOSE requires pressing Control Shift Enter (CSE). If you’d rather avoid it, but keep the solution dynamic, you need to look for an alternative. In this tutorial, you will find 3 different solutions based around the ROW function – in combination with INDIRECT, INDIRECT & ADDRESS, and INDEX.

Excel Companion Charts

With this tutorial, you will improve your chart design. I share a few different methods used for visualizing performance comparisons, deviations, as well as future estimated performance.

Excel Arrays: Count Unique Items with MMULT Function (Part 2/3)

Learn how to get the unique count across rows using an array formula based on the MMULT and SUMPRODUCT functions.

8 Formulas to get Unique Count Across Rows in Excel (Part 1/3)

Learn how to get the unique count across rows using an array formula based on the SUMPRODUCT and FREQUENCY functions.

Excel OFFSET Function for Dynamic Calculations

Think of the OFFSET function as a GPS for your spreadsheet. You give it a starting point and then you tell it how many rows to go down, how many columns to move across, and what range you want returned. Master OFFSET with the help of this tutorial, which looks at calculating the average for the last six months in a dynamic way.

Free Guide!

The ONE Critical Excel Skill You Need

This free Excel guide will help you to:

  • Pinpoint the Excel skill you should work on now
  • Understand how this skill can help you
  • Find the optimal learning path to master this skill