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 a line chart. But with Sparklines you can’t display two series in the one chart, like these budget and actual mini charts below:
And there’s no Sparkline equivalent for this variance chart below:
Excel Mini Chart Workbook Download
Enter your email address below to download the sample workbook.
Watch the Video
Inserting Excel Mini Charts
There are a few tricks to creating Excel mini charts. Especially because we want them to resize automatically with the row and column height.
Step 1: Select the chart data > Insert tab > choose your chart.
Step 2: Remove the title, axes and grid lines. Basically, you want to remove any clutter from the chart because you don’t have space to display it in a mini-chart:
Step 3: Resize the plot area so that it extends to the outer edge of the chart area…you need every millimetre of space for the mini chart:
Step 4: Hold down the ALT key while you left click and drag the outer edge of the chart to the cell you want it in. Then resize the chart to fit in the cell while holding down ALT. This will snap the chart to the cell so that if you resize the row/column the chart will also resize.
Rinse and repeat for additional cells.
Below is an animated image of the steps listed above:
The variance chart is slightly different in that it’s a single chart plotting just the variance values in column D:
Update: to set the negative variance bars to red, select the series > Format Data Series > Fill; Invert if negative. See image below.
Big thanks to Jon Peltier, Excel MVP, for the 'Invert if negative' tip!
Tip: for presentation purposes you can hide column D and just use the chart to convey the variances.
Excel Mini Chart Tips
- It doesn’t suit every chart type - while you can make any chart into a mini chart, not all charts are suitable for a small space.
- Go easy on the formatting – you’ve got a small space so don’t clutter it with unnecessary formatting.
Related Tutorials
|
Sparklines |
|
In-cell Charts |
|
Chart Formatting Tips |
Excel Multi-colored Line Charts
Fatjon
Dear, you are amazing and your tutorials are easier to understand. I made 3 dashboard thru this e tutorial. But in one of them, I have e problem when I use slicer. I have connected 2 dashboards together in one file exel, one is Sales Performance and other Brands Performance. The problem is when I do slicer in one dashboard are two charts that change format, one from Monthly in Weekly and other changes name of format in weeks, but in pivot sheet data are as not changed (as i built in beginner charts) . How this happen and what to do that this two charts to not change raports from months in weekl and from name in weeks. Data are tooken direct from server.
I would greatly appreciate your help in this matter, thank in advance Fatjon
Mynda Treacy
It’s great to hear you’re building your own dashboards! It’s difficult to troubleshoot the issues you’re experiencing without seeing your file. Perhaps you could delete the Slicers causing the problem, and insert them again. If that doesn’t fix it, please post your question and Excel file or screenshots on our forum where we can help you further.
hrieger
This is just what I was looking for – Thank you! I am wondering, though, is there a way to treat these cells with mini charts as part of a table so they sort and filter with the data adjacent to it? I have added a few to a table but they don’t sort with the table data.
Mynda Treacy
Hi Holly,
You’d be better off using Sparklines if you want the data to dynamically sort.
Mynda
René Maagdenberg
Hi Mynda,
I love the idea! Great tip.
Thanks,
René
Mynda Treacy
Thanks, René!
Amy Cheau
Hi Miranda,
thanks for the great article. I’ve tried following the steps on how to creat mini excel charts but i can’t seem to get rid of the box lines surrounding the mini chart? i’ve tried dragging out the chart so that it extends over the outer edge but i still get the box lines surrounding the mini chart? Any ideas what i’m doing wrong?!
Thanks
Amy
Mynda Treacy
Hi Amy,
All charts have a light grey border by default. You can remove the border in the chart formatting tab. Set it to ‘No border’.
Mynda
SUNNY KOW
Hi Mynda
Another great article on charts and a very relevant one in my opinion.
Mini charts are my favorite as it allows me to fit many charts into a single sheet of paper to create my dashboard. I usually combine an area and a line chart to display both actual and budget figures.
Another idea are mini bullet charts.The possibilities are endless.
Although you cannot display 2 series on a Sparkline, it can still be “faked” by super-imposing a line Sparkline above a column Sparkline (for example) using paste-link image. Great care must be taken though on the scaling of the y-axis (may need a ghost series as described in your sample workbook)
Again thanks for this excellent article.
Mynda Treacy
Thanks, Sunny! Bullet charts would also work…it’s just a shame they’re so laborious to create.