Search Results

In this tutorial we tackle a complex lookup problem – how to return the header based on criteria in a matrix. I offer my solution using INDEX and SUMPRODUCT. We also cover alternative approaches proposed by the Excel community, including an interesting TEXTJOIN solution that is able to handle multiple match results.

Conditional formatting is great at bringing attention to specific values, whether high/low, deviations, variances. It makes interpreting the data easier. But it can also significantly slow down your workbook. There is a faster alternative. You can use Custom Number Formatting to bring attention to data. Learn how to use it, how to add color-coded up/down errors and percentage deviations.

Conditional formatting is great at bringing attention to specific values, whether high/low, deviations, variances. It makes interpreting the data easier. But it can also significantly slow down your workbook. There is a faster alternative. You can use Custom Number Formatting to bring attention to data. In part 2, learn how to add any symbol to custom number format, how to use up/down arrows for percentage variances, and how custom formatting works with thresholds and icons.

Learn how to perform a matrix lookup, i.e. when you need to return the value based on the header row and the category column, in a scenario where you have more than one header. The powerful INDEX/MATCH combo is capable of handling that a couple of ways.

Excel doesn’t have a built-in Gantt chart, but don’t worry! In this tutorial, I’ll show you a clever workaround using a stacked bar chart. You’ll learn how to create a fully functional Gantt chart complete with progress bars that display the percentage completion of tasks. Plus, I’ll teach you how to toggle between actual and planned scenarios easily. Get ready to enhance your project management skills with these Excel tricks!

Learn how to add series lines or shaded background to a stacked column chart by combining it with a stacked area chart. It’s a good way of displaying the trend for each category (stack).

In this tutorial, I share the 3 principles, along with some examples, for effective data visualization, that you should apply to your charts and dashboards in Excel. When designing a chart, keep it simple, clear and consistent.

In this tutorial, we tackle a matrix lookup scenario, where you want to return the column header based on the value inside the matrix as well as the row header. INDEX/MATCH to the rescue.

Dashboards are not only charts and graphs. Tables are ideal when you want to present the data in more detail, allowing report consumers to look up individual values and perform one-to-one comparisons. In this tutorial, you will find best practices when it comes to designing tables – how to organize and format them effectively.

In this tutorial you’ll find out to how to use Pivot Slicers in Excel and create Slicers for months based on dates. You’ll also learn how to create a dynamic Pivot Chart controlled by the slicer.

If the conditional formatting icon selection doesn’t meet your needs, you can customize your design and still make it dynamic with the help of the IF function and symbols. Explore the example of a project planning dashboard tracking overdue tasks.

Find out the difference between the WORKDAY and NETWORKDAYS functions. You can use them to calculate the end date based on the number of allocated working days or calculate the number of working days you have before a deadline. Both are useful for creating project timelines and reporting timetables.