One of the visuals I show in my FinancialViz courses is a multiple diverging bar chart that can show variance between actual and budget and actual and last year. This can also be used for actual and relative variances as well. I create it using a stacked bar chart and scatter with straight lines charts.…
Category: Excel Charts
Excel Chart Tip: Add a goal or target line to a bar chart
When you are showing ranked data in a bar chart you may want to add a goal or target line to easily identify those with performance above the goal and those below the goal. Instead of drawing the line on top of the chart, build the target or goal line as part of the chart…
Excel Chart Tip: Add a CAGR line callout to a column chart
When you are reporting sales, shipments, clients served, or any measure where you want to show the growth rate over the period of time along with the values in each period, a column chart with a CAGR line callout can be a good visual to use. Instead of drawing the line on the chart and…
Use the INDIRECT function to create similar charts in Excel quickly and easily
Recently I’ve been sharing a technique in my client training that increases the payoff for making the effort to create a meaningful chart in Excel. Often we have results for different locations, regions, products, or scenarios to show. We don’t have to compare them to each other, but, for example, each region needs to report…
Excel Tip: Color conditional formatting of directional indicator symbols in cells
A financial professional in one of my courses asked if it was possible to conditionally format the color of a symbol to indicate good or poor performance. The indicator direction and color needed to be set based on the value of the cell. This video shows you how to achieve this by combining formulas and…
Force a column, bar, or line chart in Excel to start at zero
Excel changes the starting value for the measurement axis of column, bar, and line charts based on the values being graphed. This can cause a chart to be misleading just because the data got updated. In this video I show you how to add a second invisible data series that forces Excel to always start…
The strategic use of color in Excel charts
A few dataviz experts tweeted about a great new article by Lisa Charlotte Muth called “10 ways to use fewer colors in your data visualizations”. I highly recommend you read it because it is very well written and has great ideas. If you create data visuals in Excel like many business professionals do, let me…
Conditional formatting of category in column chart; Issue #470 August 4, 2020
One of my most popular Excel Chart Tips videos is the one on conditional formatting of a column chart based on the values being categorized into ranges (video is here). Another option for conditional formatting is to visually focus the audience on one (or more) column based on a condition or a decision you make.…
Filter the data series shown on a graph; Issue #463 April 28, 2020
On a recent AFP webinar discussing what CEOs and Boards are expecting from the Finance department during these turbulent times, cash flow forecasting was clearly the biggest request. It was clear that finance teams are having to generate multiple future scenarios based on various assumptions. These forecasts get updated daily, if not more frequently. When…
Using superscript numbers to add footnotes to financial reports and graphs in Excel
It is common to use footnotes when reporting financial results. Footnotes allow you to be transparent with the viewer and disclose important assumptions or clarifications so that they can more accurately interpret the results. By using superscript numbers, we can make adding footnotes to financial reports and charts in Excel easier. To create a superscript…