Excel Sparkline charts don’t have a horizontal axis. Here is a technique that creates one in the cell above or below the Sparkline. This works best for the Column Sparkline.
Category Archives: Charts
Excel Dashboards #1 Guidelines & Techniques
In this recording of a live webinar I ran in March 2023 you will learn about guidelines and technique for building dashboards in Excel.
Use the button below the video to download the materials.
This is the first session is a series of five webinars on Excel dashboards. The other four sessions are paid sessions.
In this free session we will focus on chart and dashboard guidelines plus some techniques used to create better dashboards. We will also look at creating a bullet chart which is a better alternative to a gauge or dial chart.
The session covers
- general chart guidelines
- questions to ask yourself to develop better charts
- best charts to use
- charts and formats to avoid
- using text boxes on dashboards
- how to create small charts
- chart templates
- lining up and re-sizing charts
- useful number formats for dashboard charts
- using the #N/A error with charts
- how to create a bullet chart
As always I will share a few other tips.
December 2022
Sparklines and Dates in Excel
A Sparkline chart is a single data series chart (graph) that exists in a cell. Usually, you only need to select one range to create a Sparkline. Sometimes you may need to handle missing dates.
Sparklines and Merged Cells in Excel
I am not a fan of Merged Cells but there may be a case to use them when creating a larger Sparkline chart.
One Minute to Excel #29 – Nine Names in a Minute
Shortcut to speed up name creation
In this short video I demonstrate how to create range names quickly based on labels. Range names are a powerful formula feature. I also demonstrate their use.
One Minute to Excel #28 – Eight More Charts in a Minute
Fast mouse work to the rescue
OK in the last video I cheated and used Sparkline charts to create 8 charts in a minute.
This time I set myself a real challenge to create 8 real charts in a minute.
Its close – check out the video and learn a few useful techniques.
One Minute to Excel #27 – Eight Charts in a Minute
Sparklines to the rescue
Let’s see if I can create 8 charts in a minute – one for each state and territory in Australia
I cheat a little bit and use Sparkline charts. If you haven’t seen Sparklines before check out the video.
Excel Chart Label Line Break
When adding labels to a chart sometimes you need a line break. Well it is possible.
Missing Chart Data in Excel
Another macro to the rescue
The default setting for charts in Excel is to hide the data on the chart if it is hidden on the sheet. I forgot that recently when I created a few charts using a workings area to hold the chart data. I later hid the workings with column grouping. Oops – when you hide the data in the charts go blank.
Dashboard Webinar feedback
It is always nice to receive feedback – this was for the recording of the Dashboard #1 session.
Percentage Area Chart in Excel
Conditional Format technique
You can use a pie chart to display a percentage, but it wastes a lot of space. An alternative that takes up less space is an area chart.
Excel Chart Title Hack
Linking to a range
I learned something new recently watching a recorded webinar by Jon Peltier. That’s not unusual, he is a charting legend.
Free Webinar Recording – Excel Chart Tips and Tricks
Feedback score 94.5% based on 59 responses
In November 2019 I re-ran my Excel Chart Tip sand Tricks session.
The detailed pdf manual and example file can be downloaded using the button below. Content listed below the video.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
This webinar is focused on showing you how to create and modify charts in Excel 2016 with a minimum of fuss.
The Chart interface changed in Excel 2013 and I take you through some of the changes.
You will see best practice design and formatting techniques demonstrated and explained.
See how to create dynamic charts that automatically change based on selections made or data added.
Learn about the feature added in Excel 2010 called Sparkline charts.
I also include some useful date-based functions.
Top 5 reports with PivotTable and PivotCharts
Switch the order of the Axis
When you create a top 5 sorted report with a PivotTable, the Pivot Chart isn’t always what you expect, there is an easy solution.
Free Webinar Recording – Interactive Excel Chart
Feedback score 93.7% based on 58 responses
In September 2019 I re-ran my very first webinar that was based on an August 2012 INTHEBLACK feature article. I have provided a detailed pdf manual and example files which you can download at the link below.
This session covers
- using controls with charts to make the user experience easier
- tips for using controls on spreadsheets
- flexible formula techniques to make reports more dynamic
- a technique to stop figures plotting on charts
- using range names with controls and reports
- general charting tips
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
Free Excel Webinar Recording – Financial Functions Part 1
Feedback score 93%
In June 2019 I explained and demonstrated a number of Excel’s financial functions – see below for more details.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
These functions take three or more arguments, but there are a few things you need to know to use them correctly. Most involve loans or calculations associated with the time value of money.
This session covered the following six functions
- PV – Present Value of future regular cash outflow
- PMT – periodic loan repayment calculation
- CUMIPMT – cumulative interest – great for loan schedules
- RATE – interest rate
- FV – future value of regular investment
- NPER – number of periods
The session finishes with a loan model that calculates the “missing” value for a loan scenario based on two out of three inputs. The IFERROR function is also discussed.
Related Posts
Free Excel Webinar Recording – Dashboard #1 Guildelines & Techniques
Feedback score 92%
In May 2019 I examined and demonstrated some guidelines and techniques for creating charts for dashboards.
CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.
In this session I focused on chart and dashboard guidelines plus some techniques used to create small charts for dashboards.
I also looked at creating a bullet chart, which is an alternative to gauge, speedo or dial chart.
The session covered
- general chart guidelines
- questions to ask yourself to develop better charts
- best charts to use
- charts and formats to avoid
- using text boxes on dashboards
- how to create small charts
- chart templates
- lining up and re-sizing charts
- useful number formats for dashboard charts
- using the #N/A error with charts
- how to create a bullet chart
As always I shared a few other tips.
Switching Rows and Columns in a PivotChart
Finding the hidden option
Switching between rows and columns in a normal chart is easy via a button on the Design tab. But how do you do it with a PivotChart?
Moving Images in Excel
Make the Shift
When you are moving images, charts or other objects that float above the grid you can use the Shift key to make it easier.
Bold Your Headings
Apparently this is not widely known, but you should always bold the headings in your tables.
Then when you use Format as Table (Ctrl + t) on the Home ribbon tab the header row will be correctly identified.
This also applies to the Ctrl + Shift + L shortcut to insert the filter drop downs.
It also applies to the ranges used for charts.
In general ALWAYS BOLD your headings – it is something Excel looks for.
Ctrl + b is the bold shortcut.
Creating a Timeline Chart in Excel 2016
Error Bars plus a new feature
Timeline charts are an effective way to display events over time. You can use a new Excel 2016 feature to easily create a timeline chart.
Horizontal or Vertical Progress Bar in Excel
Conditional Format or Sparkline take your pick
In a recent webinar on conditional formatting I was demonstrating how to create a horizontal progress bar using conditional formats when someone asked an interesting question about creating a vertical progress bar. It is possible and in this blog post I will explain both techniques.
Adding a Vertical Dotted Line to an Excel Line Chart
An error bar solution
When plotting Actuals and Forecasts on a single line chart you may want to use a vertical dotted line to identify where the Actuals finish and the Forecasts begin.
An Easier Step Chart
A Line chart solution
Here’s another way to create a Step Chart. This one is quicker. I wrote previously about using a scatter plot and error bars but it required a lot of chart changes. This one hacks a line chart and requires no chart changes.
Vote No for Gauge Charts
They take up too much space
Gauge charts are not a standard chart in Excel and are quite complex to create. Gauge charts should be avoided for dashboards.
TreeMap a new chart in Excel 2016
A square pie chart
The TreeMap is like a square pie chart, but it has the added ability to show a hierarchy.
Step Charts in Excel
Hacking the error bars
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch.
2018-05-15
I prefer to call them Bridge charts rather than Waterfall charts, but Waterfall is the common name.
Excel added Waterfalls in Excel 2016.
I think the name Bridge is more descriptive since a bridge takes you from one place to another which is what the chart does with values.
Waterfalls in nature only fall down, whilst a waterfall chart has measures that rise and fall.
This blog post show many examples. Most examples are not done in Excel.
2018-05-08
A good blog post on the when and how to use dotted lines in charts.
Dotted lines are great for lines you want on the chart but not featured.
Also for showing forecast values on the same chart as actuals.
Check out the winners of the Data Literacy awards.
Related Posts