FIX THE PITCH

×

Fix the Pitch

How to craft compelling, dazzling pitchbooks. Thoughts, ideas, and inspiration to help construct advanced financial analysis, build stunning data visualizations and tips for mastering client meetings.

Fix your pitchbook


Join the Fix the Pitch newsletter

Popular tags


How to treat data outliers in pitchbook charts

Eric RattnerEric Rattner

Outliers are a pain.

If you’ve had to chart data that includes extreme values, it’s probably been a frustrating experience. Excel’s charting platform offers no easy way to deal with outliers; “broken” axes are not supported, and only the most rudimentary controls over axis scales are provided. Even worse, after the dull fine-tuning required to produce a viable graphic, the standard output is underwhelming.

Given a raw set of data that looks like this when charted:


bar chart showing raw data with outliers

This is the typical treatment 99% of the time.


bar chart showing outliers using broken axes

Not the best. And it’s not the banker’s fault. By and large, finance practitioners are not given robust (or any) training in data visualization science (yes, it’s a science, you can get your PhD in it). I understand it doesn’t make sense for finance institutions to provide this training when bankers are already expected to warehouse an abundance of skills inside their brains (financial statement analysis, discounted cash flow modeling, Microsoft Office keyboard shortcuts, spreading comps, at least a dozen unique corporate login passwords).

As a result, bankers spend a large amount of time awkwardly tinkering in spreadsheets: rescaling data to be plottable in an Excel chart; drawing and placing the shapes representing the breaks; and manually overwriting the axis and data labels to produce something suboptimal. It’s not the best use of a banker’s time and is just one of the reasons why investment bank pitchbook production is such a burden and energy drain.

So when it comes to addressing the issue of outliers, bankers are generally left to their own devices. For time series charts, more often than not, no outlier treatment is received at all—the problematic data is simply cut off or excluded.

It’s time to move beyond this approach and luckily I’m here to help (marginally) with some Excel alternatives (if you’re going to spend the time hacking Excel charts to handle outliers, at least do it in a more visually robust way). Alternatively, if you have no patience for any of this and want best-in-class chart design AND a painless experience, then check out the Pellucid content platform where all of this has been institutionalized, with an average cost per chart of less than a dollar.

The “Paneled Canvas” approach


Bar chart showing outliers using paneled canvas

How it works

Considered best practice from a visualization science standpoint, this technique deals with outliers by exhibiting the data in two ways. An unadjusted axis presents outliers in their full glory, towering over the rest of the puny data. A chopped-off value axis allows for examination of the rest of the data set on a normalized scale.

Best used for

It’s a great way to comprehensively display a data set with outliers, but it’s not always practically optimal because it requires extra space, and this can be difficult to handle if it needs to be in alignment with other charts on a page. This makes it great for things like dashboards but not necessarily pitchbooks.

How to make it

This is probably the easiest of these techniques to implement is Excel, since it only requires stacking two standard charts.

The “Outlier Panel” approach


Bar chart showing outliers using an outlier panel approach

How it works

This is my preferred approach in most cases. Outlier panels adjust the chart’s coordinate system by distorting the scale at the upper or lower edge of a value axis. As such, the bulk of the data is displayed along an axis with normal “proportions”, and data above or below certain outlier thresholds are plotted in the associated panel whose value axis scale is squashed.

Best used for

I love this technique because it is universally applicable to any chart with a value axis. Additionally, it plots all of the data without exclusion or breaks, albeit with a bit of transformation, so it is perfect for time series or scatters where it’s difficult to break an axis without breaking the chart.


Scatter chart showing treatment of outliers

Admittedly, the rescaling of the axis reduces the visual impact of the outliers vs. paneled canvases, but in most cases, the outliers are not the “point” of an investment banking pitchbook chart. In fact, they are by definition the exceptions. So unless their magnitude is the focus of the analysis, using a scaled panel is an encouraged method, in my opinion.

How to make it

To make them in Excel, copy-paste a chart, format them with the two different axis scales, resize their plot areas so the outlier panel chart is shrunken proportionately, and align them into a cohesive visualization.

The “Outlier Indicator” approach


Bar chart showing outliers using indicator approach

How it works

As the name suggests, this approach indicates the existence of an outlier above or below a value axis’s maximum or minimum using a visual marker without visually addressing its magnitude.

Best used for

It’s a minimalist approach and useful when the outlier data is so extreme that it’s essentially meaningless (such that it would just look silly with any other treatment) or where real estate on the page is a scarce commodity.

How to make it

Basically, all this technique requires is some visual cue attached to a data marker that traverses a value axis edge. For bars, I prefer the stacked lines above (representative of the “dot-dot-dot” punctuation), though arrowheads or other indicators could be substituted. For lines, I favor arrows where the line crosses an axis boundary, and for marker-only plots, I go with directional triangles or arrows.


Outlier indicator chart using lines*

Relative to the clunky broken axes polluting today’s pitchbook content, I suggest trying your hand at one of these variants to differentiate your charts. The first two enhancements give a better view of the data and are much more widely applicable to different chart types than your current standby. And outlier indicators are a good fallback as well when space is tight.

Let me know if you need some tips for getting this done in Excel. Alternatively, just email me at eric@pellucid.com if you’d like to see what this looks like using Pellucid.

Pellucid blends technology and design to create beautiful, client-ready charts. Find out more at www.pellucid.com.

Eric Rattner
Author

Eric Rattner

Investment banking lifer and native New Yorker. Broadway and movie buff and aspiring soccer star. Building innovative, beautiful charts filled with really smart data analysis.

Comments