My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
For many people when they think of a chart that shows parts to a whole, a pie chart is the first choice. And if you’ve known me for any length of time you’ll know I pretty much despise data in a pie. Don’t get me wrong, I love pies. It would be un-Australian not to …
Using interactive Excel charts in your dashboards and reports allows the user to pick and choose what they want to see. This means you only have to build the report once and then the user can easily create their own view instead of you having to create multiple permutations of the same report, customised for …
I get the fanfare around infographics. They’re used to make information more appealing, grab attention, awaken interest, lessen boredom and make that information memorable and easier to understand. However sometimes they don’t deliver on all points and often miss the most important point, which is to quickly and clearly communicate your message. Just take the …
In an ideal world our data will be in one table so we can easily analyse it in a PivotTable and PivotChart. However sometimes the data we want to display in a chart is split across multiple tables, and this is a PivotChart showstopper. Remember Pivot Charts are monogamous in that they only display data …
That’s a rubbish title, I know. I couldn’t think of a succinct way to describe what I’m about to show you. I’ve combined a few techniques to create this interactive PivotTable and PivotChart using Slicers: Probably the easiest way to understand how I’ve put this together is to download the file, which you can do …
With Office 2016 officially launching on September 22, 2015 I thought I’d give you a sneak peek at a few of the new charts in Excel 2016. Treemap Chart A Treemap chart is an area based visualisation that displays your data in a hierarchical pattern. The tree branches are represented by rectangles, with sub-branches displayed …
Put some spark in your chart! Excel Sparklines were introduced in Excel 2010 and are great for displaying the trend of data over time and making sense of a sea of numbers. Just take this before and after example: Before A sea of numbers that would take a long time to analyse and compare: After …
In this tutorial we’re going to look at how we can twist Excel’s arm into putting text labels on the vertical axis of a chart. This was inspired by a question I received a while back from Juan Gabriel Aguero, who asked if we could re-create this chart in Excel: While the answer is yes, …
In my previous life as an accountant one of my main tasks was to analyse budget vs actual variances and then report these variances to department heads who were less than enthusiastic about finance, despite my repeated ‘Finance is Fun’ chants! Let’s see how we can present variances in a chart that’s quick and easy …
A new feature in Excel 2013 is called Quick Analysis – it’s data analysis made easy, so easy my 6 year old could use it, you just point and click and Excel creates live previews and insights into your data using: Conditional Formatting Charts and Sparklines Summarise with Totals Tables and PivotTables Just add data. …
In this tutorial we're going to look at how we can skip dates in the Excel chart axis for those dates that have no data. When you plot data in a chart that has a time axis Excel is clever enough to recognise you’re using dates and will automatically arrange the data in date order. …
Using Excel custom chart labels is a great way to create a more insightful chart without having to show a whole other series. Just take this chart below with custom labels showing the year on year % change: Enter your email address below to download the sample workbook. Get Workbook By submitting your email address …
Getting the formatting in your chart right can make the difference between clear, concise information and just a pretty picture. Take a look at this stacked column chart using some of the built in Excel chart formats: What's your initial reaction? Here are some of mine: Striking use of colour It looks nice But then …
Below is a set of Panel Charts. In this case they’re actually 4 separate charts aligned close together. Comparing one chart to the next isn’t easy since each chart uses a different vertical axis scale: If we could put all charts on the same axis then we could remove the 3 superfluous axes and ensure …
Bullet graphs were developed by data visualisation expert Stephen Few to address the need for visually rich displays of data in small spaces which are typical of a dashboard report. They enable you to compare your actual measure to a target/budget and also a qualitative range as denoted by the background fill. While Excel is …
If you don’t have Excel 2013 then this post will be a bit like window shopping with an empty wallet. You get to try stuff on and test drive it, you just don’t get to take it home. Window shopping is still fun though, right? Excel 2013 has gone all iPad on us and now …
Charts are great at revealing trends, patterns and relationships in your data. We love how they enable us to glean information in the blink of an eye, but beware of the line chart because it can lie. Watch the Video Download the Excel File Enter your email address below to download the sample workbook. Get …
If you find yourself repeatedly spending a few minutes customising your chart colours, formatting the title, legend, axis labels etc. every time you insert a new chart, then you might consider setting up your own chart templates. Think of them like man’s best friend, the dog; always at your side. Chart templates are on hand …
Last week I wrote about how I used Excel to design a Minecraft cake. The response to that post was bigger than I expected and it was nice to know that many of you use Excel for extraordinary things too. One of the emails I received was from my friend and Excel extraordinaire, Roberto Mensa …
Last week we looked at some different ways to plot the same data, with each chart enabling the reader to glean different information. A by-product of that post was the observation that one series, the White, dwarfed the others, which made it difficult to get a feel for the trend and significance of the change …
Often when you’re charting data it can be tricky to find the best chart for the job. If you get it wrong at best it can be a waste of everyone’s time, and at worst it can be misleading. That’s why you need to know the motivation for the chart up front. What is the …
I see the same innocent mistakes with Excel charts and data presentation over and over again. And a lot of them are easily avoided. So, I'm going to share with you 30 easy to action tips on how to create better charts and dashboard reports in Excel, and the common pitfalls to avoid. Download the …
One of my Excel Dashboard course members, Mark Maher, emailed me wanting to know if a chart that Stephen Few had mocked up in Adobe Illustrator could be done in Excel. The answer is yes (see below), but it’s not native to Excel and it took a bit of jiggery-pokery to get it to look …
Today I’d like to share with you a video tutorial by Isaac Gottlieb on supply-chain decision making from his book Next Generation Excel. Isaac teaches over 2,000 MBA students a year at Columbia, NYU and Temple U and over 3,000 undergrads on-line. See how he uses the Solver to find out the ideal location for …
Gantt charts are handy for planning and managing project tasks over time. They give a visual representation of the whole project, displaying progress to date and work to come. In this tutorial you'll learn how to build an Excel Gantt chart using Conditional Formatting. We’ll also look at how we can highlight tasks that are …
Gantt charts are commonly used in project management and while Excel doesn’t have a native Gantt chart in its chart library we can easily create one. Don’t get too excited, it’s a very basic one. If your job involves complex project management then you probably need a more sophisticated tool like Microsoft Project. However, for …
I recently came back from a family skiing vacation in Whistler, Canada, which by the way was fantastic. Here we are at a lookout on a snowmobile trip we took. Anyway I digress…. one of the key pieces of information you want to know as a skier (or snowboarder) is how much snow is on …
As fun as this chart is, it actually makes a valid point and that is; pie charts are (typically) not very useful. Why? They’re slow to interpret because they usually have too many slices with teeny weeny labels on stalks. You know the ones….like this: Nine times out of ten there are better charts to …
There's a handful of mistakes almost everyone makes when they first use Excel. I know I did. In fact some of us are still making these mistakes. Are you? The good news is they're easily fixed. Mistake 1. Create reports that take a long time to update. I’ve done it before. I’ve I imported the …
Did you know you can analyse data from an Access database in an Excel Pivot Table without importing the data first? If you use Access then this is a great productivity booster because once you connect to your Access query you simply refresh the data connection and your Pivot Table updates, as do any charts …
