Search Results

If you follow the 5 dashboard design tips you’ll learn in this tutorial, you are guaranteed to create effective, informative dashboards.

Find out how to format the standard stacked chart to improve readability and comparison between the different categories and stacks. My tips include adding total values outside the last stack, adding percentages to the stacks, and creating a dynamic legend, putting the labels of each of the stacks on the right-hand side of the last category.

In this video tutorial, you will learn how to sum different columns based on user selection with the SUMIFS or SUMPRODUCT function. It’s a useful technique for interactive dashboards.

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.

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.

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

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

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.

Learn how to use the conditional aggregation functions: SUMIFS, AVERAGEIFS, COUNTIFS. You don’t need to waste time copying filtered ranges, you can get the value with a single formula.

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.

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.

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.