Excel Charts

Creating an Excel five star rating chart is easy with Conditional Formatting. They’re not limited to stars, you can also create column, pie and waffle rating charts. Constructing an Excel Five Star Rating Chart Step 1: In the top left cell of your table, in my case cell C5, insert the formula below that calculates …

The options in the Pivot Chart library are limited, which you’ll know if you’ve ever tried to create a Scatter chart. Last week we looked at a workaround to create a scatter chart from a PivotTable, which is great if you want to use Slicers. However, we can also create an Excel Power Query Pivot …

Ok, that title is a lie. There’s no such thing as an Excel Scatter Pivot Chart. It’s like mixing water and oil. They just don’t go. However, in this tutorial I’m going to show you a way you can defy Excel and trick it into building a scatter chart from a PivotTable. And not only …

A common chart mistake is to use a line chart for the wrong type of data. An example of that is the chart below that has categories* along the horizontal axis: *This chart displays the degree of difficulty each sport requires for the categories above, as judged by a panel of 8 experts made up …

Excel Chart titles are often a wasted opportunity to connect with your audience and drive home your message. So, let’s look at some ideas for how we can leverage the chart title to help you and your audience get the most from the chart. Watch the Video Ideas for Excel Chart Titles Keep in mind …

Understanding your data and creating Excel charts has never been easier with Excel's Analyze Data (previously known as Excel Ideas and before that, Insights!). Excel Analyze Data is an AI powered service that automatically identifies patterns in your data and provides you with charts you can insert into your workbook. You can also use ask …

Excel Waterfall charts are now available to Office 365 users via the Insert > Charts menu. It makes light work of building what was a laborious chart to create in earlier versions of Excel. However, it has some limitations, so you may prefer the manual approach required by users of earlier Excel versions anyway. I’ll …

Last week I looked at getting real time data into Power BI using PubNub. This week I want to take the same code and modify it slightly so we can get real time data directly into our Excel sheet. This will require Excel 2016 or Excel 365. I've previously written about creating add-ins for Excel …

Excel mini charts are a great alternative to Sparklines. Mini charts are just regular charts made really small. You might consider using them instead of Sparklines when you want something other than column, line or win/loss charts. For example, the table below contains mini area charts: Ok, so an area chart isn’t much different to …

Excel multi-colored line charts are a little gimmicky, after all we don’t need color to tell if a line is going up or down. However, it can be useful for encoding data that is outside a threshold and can also speed up interpretation, especially if you use familiar traffic light color encoding. We have a …

Excel 3D Maps in 2016, or Power Map as it’s known in Excel 2013, enable you to visualise data in, you guessed it, 3D! No surprises there, however you may be surprised to discover insights you didn’t anticipate. Because unlike 2D maps or tables, with 3D maps we can quickly identify hot spots, anomalies, clusters …

Excel infographics, like the one below, are easy to build and there are loads of tools you can use to add some sparkle. Infographics are suited to one off brochure style reporting designed to catch your eye and be easily digestible and memorable. They’re often found in newspapers or other print publications because they draw …

The Excel Forecast Sheet and charts are a new feature available in Excel 2016. In just a few clicks you can get a forecast done for you, including a chart like the one below: This new forecasting tool creates a copy of your data and inserts it on a new sheet in your file, along …

Plotting data over time can reveal patterns and trends, but often blips in the data require further explanation. We can help our user by labelling events in Excel charts to highlight key points in time that may explain those blips or patterns revealed in the data. For example, the chart below monitors Phil’s average running …

Format your dashboards and reports fast with these pro Excel chart formatting tips. Watch the Video Pro Tip 1 – Select Multiple: Hold the SHIFT or CTRL key to select/de-select multiple charts or objects. Pro Tip 2 – Select All: Select one chart then press CTRL+A to select all. Note: This will select all Objects …

Dumbbells and Lollipop Charts Excel Dot Plots, dumbbells and lollipop charts are good for comparing one, two or three points of data. For example, year on year, before or after or A vs B. They make a nice change from a column or bar chart (like the one below) and are less cluttered: Column and …

When you think of visualizing parts to a whole in Excel charts the first thing that’s likely to come to mind is the pie chart, or if you’re Homer Simpson then you might think of doughnut charts! Being outnumbered 3:1 by football (soccer) fans in my household, I get to see my fair share of …

Highlighting the minimum and maximum in an Excel chart can help focus your readers’ attention. We can manually select points or columns and change the color, and that’s fine for static charts, but if your chart gets updated then you’ll want to automate the process. The approach is slightly different for line charts and column …

The default Excel chart legends can be awkward and time consuming to read when you have more than 2 series in your chart. As your eye flits back and forth from legend to chart any ability to quickly interpret the data dwindles away. Just try it with the example below: Presenting charts like this does …

Sometimes a secondary axis is a necessary evil. After all, most of the time you can’t plot big numbers and little numbers in the same chart without the little numbers getting lost in the scale. For example, let’s say we want to plot our annual sales and profit margins in one chart. Here’s the data: …

One of the cool features of Power BI is the ability to cross filter and highlight charts. With a simple click on a column or line in one chart we can instantly highlight the selected item in all other charts and tables in our report. In the animated image below you can see that when …

New in Excel 2016 (for Office 365 subscription users only*) are Map Charts. *If you have Office 365 and can’t see the Map Charts then it’s likely you’re on the Deferred Channel, which hasn’t received them yet. You can check information about all channel releases for Office 365 client applications here. Download the workbook Enter …

Survey data can be tricky to display in a chart so in this post I’m going to give you some options. Let’s start by looking at what you shouldn’t do, and that is a 100% stacked bar chart like this: The above chart plots the 15 questions on the vertical axis and the responses colour …

Excel Heat maps can speed up interpretation of data and focus the reader’s attention to problem or key areas. And we can create them easily using Conditional Formatting. To be fair it’s less of a map and more a heat table, but nonetheless they’re referred to as heat maps. Let’s take this example of student …

Slopegraphs were first introduced by Edward Tufte in his book, The visual Display of Quantitative Information (1983) where he referred to them as Table Graphics. The more catchy term, “Slopegraphs”, came much later. Slopegraphs are useful for comparing change over time. For example, the Slopegraph below shows the change in long-term health conditions from 2001 …

I was reading a health magazine last week and the chart below caught my eye. click image to enlarge (sorry about the quality, it's a photo of the magazine and photography is not my 'thing' 😉 ) × There’s something fundamentally wrong with this chart. Can you see what it is? Overall the layout is …

PivotTables are a great way to summarise your data, but PivotCharts can be a pain in the, um, neck. PivotChart Restrictions They’re not as customisable as a regular Excel chart They only play nice with data from one PivotTable PivotCharts aren't available for all chart types e.g. Sunburst, Scatter, Histograms, Waterfall and more. In this …