My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
One of the best uses for Python in Excel is creating the amazing charts available through Python libraries like Seaborn and Matplotlib. These libraries open up a huge array of charts we simply cannot make using the built-in Excel charts. Get started with Python in Excel here. Your browser does not support the video …
Progress charts are a powerful way to track milestones and showcase achievements. However, there’s no built-in way to create Excel progress circle charts, but with some clever uses of doughnut charts we can whip them up in no time. In this tutorial we’ll look at how to quickly create them and link them to Slicers …
Charts provide a visual representation of data, making it easier to understand and interpret large amounts of information quickly. At least that’s the objective, but too often poor choice of chart formatting is more of a hindrance than a help. In this tutorial we’ll look at the types of Excel chart formatting that is an …
Dashboards have limited space which is why an Excel scroll and sort table is super handy. Scroll and sort tables allow you to embed an extract of data from a larger table in your Dashboard. Users can scroll to see all the records and toggle which column to sort by and set the sort order, …
When building reports, it's important that people not only understand what you're saying but also remember it. Picture fill Excel charts are a simple way to make your data visualizations more captivating and memorable. They’re suitable for infographics and fun reports, but I wouldn’t necessarily use them in corporate style reports. You can create them …
Excel Speedometer charts or gauge charts as they’re also known, have a worse reputation in data visualization circles than the disdained pie chart. It’s primarily because they take up a huge amount of space and ink relative to the amount of information they convey. In this tutorial we’re going to learn how to build Excel …
Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. If you’re familiar with Agile Project Management, then you’ll know that these charts are useful for monitoring the progress of a project against expectations. While the terminology is specific to Agile Project Management, the concept is relevant to managing …
Excel WeePeople font charts enable you to build interesting variations on waffle, bar and column charts, to name a few. They’re great for visualising demographic data, survey data and more. The free WeePeople font, courtesy of ProPublica, consists of 20 different people silhouettes hand drawn by Alberto Cairo. The last 6 letters of the alphabet …
One of the things that makes Excel stand apart from other reporting tools is its immense flexibility. With some tinkering you can make charts and graphics that don’t look anything like Excel. In this post I’m going to take you through the steps to create Excel dot map charts. And while there’s no built in …
S-curve charts are a common tool used in project management to track the cumulative costs, hours, sales etc. over time. The chart forms the shape of an S because typically progress starts slow and ends slow, with acceleration in the middle periods. For example, in the s-curve chart below you can see progress is slow …
When working with small multiple charts or panel charts as they’re also known, it’s handy to be able to switch the chart axes between the same axis for each chart and their own axis. Using the same axis enables you to compare the data between the charts because they all use the same scale. Whereas …
You can create more interest and give your charts infographic styling when you create Excel charts with Shapes. There’s no limit to the type of shapes you work with, but there are some techniques required to ensure they don’t get distorted when working with data of different values. Watch the Video Download Workbook Enter your …
Excel pyramid charts are easy to build using bar charts or Conditional Formatting, but there are a few rules you must follow depending on the approach you choose. Before we look at the steps, keep in mind that these charts aren’t limited to visualising gender splits. They’re also handy for any type of demographic data …
Shading or highlighting periods in Excel charts can help users more quickly interpret them and identify patterns. In the chart below I’ve highlighted every second month to give a quick visual indication of each period, which allows the user to focus on the line instead of having to refer back and forth to the horizontal …
Last week we looked at building Excel waffle charts using Conditional Formatting. This week we’re looking at building Stacked Bar Excel waffle charts. Some might say this technique is easier, although it may appear daunting when you look at the number of steps. Don’t let that put you off though, it’s super easy. The other …
Excel Waffle Charts are a popular way to visually display parts to a whole. You can think of them as an alternative to pie and doughnut charts. Waffle charts are better at displaying small segments that often get lost in a pie chart. However, that doesn’t mean you should get carried away with the number …
When you have data that spans a long period of time that you want to plot in a chart, the dates in the horizontal axis in a line or scatter chart can get very cluttered. With this clever workaround that Jim Fitch shared with me we can trick Excel into showing data correctly spaced over …
If you use a scatter plot for a dataset that has discrete values in one dimension, for example your x-axis shows the days of the week, you can get points overlapping when you plot the data. To make the chart easier to interpret you can introduce jitter to the data points. This means moving the …
When you plot multiple series in a chart the labels can end up overlapping other data. A solution to this is to use custom Excel chart label positions assigned to a ghost series. For example, in the Actual vs Target chart below, only the Actual columns have labels and it doesn’t matter whether they’re aligned …
We can use a data validation list or a Slicer to lookup pictures in Excel. This ability to lookup pictures is handy for returning images of products, employees, logos etc. The only limit is your imagination. In this example I’m going to lookup pictures of the Tour de France stage profiles*. *Source 2013 Tour de …
One of the cool features in Power BI is the ability to cross highlight from one chart to another by clicking on a column in one chart and instantly highlight the selected item in another, as shown below: We can also cross highlight Excel charts in almost the same way and without the need for …
If you need to plot data that has one variable where values can be the same or very similar, for example the age of a group of people, you'll likely end up with data points that are plotted over the top of each other. To make this type of plot easier to read and allow …
Reduce clutter in busy line charts by plotting your data in small multiple charts, or panel charts as they’re sometimes known. I’ve used a few tricks for building small multiples in the past, but the Excel small multiple charts I’m going to cover in this tutorial are dead easy. It’s all in the layout of …
Labeling charts is useful if the reader needs to know the exact value for each point. We can position labels left, right, above, below or overlaid (centred) on the line. However, when you overlay line chart labels it typically looks messy, as you can see below: This is easily solved with some formatting to achieve …
Often, you’ll find Actual vs Target charts based on categorical data in the form of a column chart, however they’re slow to read. Thankfully we can make a big improvement in how quick and easy they are to read with a few simple changes. Watch the Video Download Workbook Enter your email address below …
Charting survey data can be tricky, and when you want a segmented survey data chart it adds another layer of complexity. Bart sent me the data below and asked how I would go about visualising the data to include the size of the segments (students, lecturers, staff). Segmented Survey Data Chart – Option 1 You …
Year on year charts or data over time is one of the most common data visualisations you’ll see. However, the message conveyed depends on the chart you choose. Watch the Video Download Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can …
In this post I’m going to show you a way to create an Excel bar chart with a vertical line. The inspiration was taken from this Tableau chart by Emily Tesoriero: Unfortunately, adding a vertical line to a bar chart isn’t a simple feat in Excel, but I’ll step you through a workaround that’s relatively …
If you work with PivotTables, then you’ve probably found that you can’t include grand totals in Pivot Charts, or subtotals for that matter. And if you’ve ever created a stacked column chart then you’ll have likely wanted to include grand totals as column labels, like this: And then remembered you can’t. One workaround is to …
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 …
