An overview on how to effectively present quantitative data for Excel reports.

Learn best practices on how to present data tables in Excel. This lecture provides you a with good overview on the methods you can use to bring attention and focus to specific sections of your data tables and what you should avoid.

Learn best practices on how to create Excel charts with an impact. It takes you through the methods that will help your readers to understand the message of your charts faster and not waste time trying to figure it out. It also shows you what you should avoid when designing charts.

This demo provides a comprehensive overview of the chart options and features available in Excel.

In this lecture you'll learn some tips and tricks on how to become faster when creating & designing charts. You'll also learn the important shortcut keys to properly position your Excel charts in your reports.

Many people update their charts manually, never knowing that Excel can actually do the work for them! This section provides you with a good overview on what Excel is actually capable of when it comes to dynamic charts. Each of the topics mentioned here will be discussed in the next 4 sections.

This lecture introduces you to the main advanced Excel functions that will provide you with dynamic and interactive charts. These functions also go beyond charts. They can be used to create interactive dashboards, dynamic reports and advanced lookups. The next 4 sections take you through each function in more detail.

Excel Tables are definitely a must as they provide a simple way to create dynamic charts and formulas.

In some cases, you might not want or be able to turn your data into an official Excel Table. In this case there is a great formula that can come to your rescue. Find out which one, in this lecture.

Many Excel users are generally not sure how to properly use the Name Manager feature. This lecture shows you one practical use for it.

The OFFSET function is a very powerful function. A must-know for anyone looking to gain advanced Excel skills and learn advanced formulas.

INDEX & MATCH is one of my favorite functions. It's actually two functions used as one. It can do complex lookups. It can help you create dynamic dashboards and dynamic charts. I have dedicated a whole section to this to make sure I do it justice.

Learn how the Excel INDEX function works on its own. 

Learn how the Excel MATCH function works on its own.

Learn how to combine the two INDEX & MATCH functions to get a very flexible lookup function. It certainly can do a lot more than the VLOOKUP function. 

Having learnt how to use INDEX & MATCH, you learn here how to create dynamic ranges for your Excel charts. For example, how you can add a drop-down and based on the selection, the chart values & categories change. This provides an interactive-dashboard look to your reports.

How do you automatically sort your bars in ascending or descending order? Without having to manually sort? In this section you will learn one useful formula that gets the task done.

The Excel Rank function can be used to automatically sort the bars or columns in your charts. Watch this lecture to find out how.

To ensure your Rank function accounts for duplicate values, you will need to tweak the classical RANK function to get a Unique Rank. See how you get a Unique Rank in this lecture.

Avoid Excel's default legends whenever possible. For example, if you have two line series, position the series labels, right by the series and remove the default legend. This creates a leaner, more elegant and easy to interpret chart. You can also do this if you have stacked column charts. The trick is not to do this manually - which most people do by using text boxes - but instead to use some smart formulas and methods that lets Excel do the work for you.

In this lecture you will learn how to dynamically position the line series label right beside the line series. It also teaches you how you can add a correction factor to it to fully control its positioning in your Excel Chart.

When the length of your time series is changing - for example as you add data for the next months -, you need your series label to move with your line. To avoid any manual work, follow the tips and tricks shown in this section. This is the only time I like errors in my formulas!

In this lecture, you will learn how to position the legend (stacked series labels) dynamically for a stacked column chart. We will do this using formulas and smart techniques. Your readers will benefit from having the label of the stack right beside it and no longer need to look at the legend and match the color with the color of the stacks. Time-saving for everyone!

Learn how to dynamically change the color of specific bars in your column charts. Don't manually do this by clicking on a specific column and changing its color. Use this method instead!

In this lecture you will learn how to conditionally color positive & negative columns (or bars - eg. positive values should show as a green column and negative values in red) and also how to conditionally color data labels in your charts.

Whenever there is some sense of grouping of the categories in your chart, you might want to add vertical dividers to make it easier for your readers to compare grouped categories together.

First introduction to my favourite technique of using error bars in Excel charts to get the impact you need. In later lectures you will be using this method in different ways. This knowledge is definitely a must for anyone wanting to become an expert in Excel Chart design.

Sometimes it makes sense to add a comment or annotation inside your Excel chart to highlight a specific event. For example you started a marketing campaign or introduced a new product and would like to direct the reader attention to this section or use it as an explanation for your data point. This lecture not only shows you how to add annotations to your chart but also how to make these dynamic so that once your data point shifts, the annotation moves with it.

It also uses the functions we learnt in Secrets to Dynamic Charts to create a mega dashboard effect!

Excel tables are used when you'd like to show exact values and compare specific values with one another. In this section you will learn how you can get the most out of your Excel data tables and direct reader attention where it's needed most. Two techniques are shown in this section

1. Use symbols in tables
2. Use Excel's Conditional Formatting feature

In case you don't want to use the Conditional Formatting feature in your Excel tables, you can use a very simple technique to get a similar effect. Watch this lecture to see how you can use symbols in tables to get a similar effect.

This lecture shows you how to use Excel's Conditional Formatting feature to format table rows differently depending on a certain criteria. Conditional Formatting can be tricky sometimes but all you need to do is to watch out for one key setting, which is explained in this lecture.

This lecture shows you how to use Conditional Formatting to make certain parts of your table stand out so that it is easier to read and digest. The Smart formatting techniques help emphasize the sections of your report that need attention most. To ensure you do not have too much color everywhere, you can use a threshold.

This lecture shows you how to use data bars and symbols in conditional formatting to create impressive tables.

This lecture provides you with an overview of different methods used for visualizing performance comparisons, deviations, as well as future estimated performance. It introduces you to the topics and charts that you will learn in this section as well as the next two sections.

When comparing Actual to Budget, many reports show this comparison as columns. Although this is a good method, watch this lecture for two other ways to compare variables with one another that are also highly effective and take less space.

This is specially required if the range for your line series chart is based on an Excel data table that has formulas and you would like to cut the line series in the middle. Using the common "" in your formulas does not work as the line series will crash all the way down to the X-axis. You will have to use a special trick to get to "break" your line series. Watch this lecture to see how.

One really effective and neat way of showing Actual to Budget variances for different categories such as products, departments or companies is to present it as a bar chart and show the variance to budget using thinner bars. When Actual is above Budget, the difference is shown as green and when Actual is below Budget, it is shown in red. The variance amount is also shown as a percentage. Watch this lecture to see what this "professional" method is all about.

note: it does involve my favorite trick!

In this lecture you will learn another effective method to show Actual data and the Variance to Budget. The method includes two bar charts that are positioned beside each other and the bars are "connected" using subtle lines that connect the bars from the chart representing Actual data to the Variance chart. It uses three different techniques covered in this course and brings them all together creatively to get a great impact.  

In this lecture you will learn how to create a vertical bullet chart using percentage values (e.g. productivity, efficiency). Bullet charts are great when you want to compare performance not just against target but also be able to deduce if performance was within an "unacceptable", "acceptable" or "Great" range. 

If you'd like to create a vertical bullet chart using absolute values instead of percentages you need to watch this lecture. Since we use the primary and the secondary axis, we need to tweak things a bit to ensure they have the same minimum and maximum values.

Horizontal bullet charts are more difficult to make than vertical ones. Unfortunately you can't use the same techniques as with the vertical bullet chart. In this lecture you will learn a smart way of setting it up from scratch.

Don't have the time to create a horizontal bullet chart from scratch? Watch this lecture to see how you can easily transform your vertical bullet chart into a horizontal one.