FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

The Excel Addict - Help with Excel 2013, 2010, 2007, 2003
February 25, 2016  
Greetings from The Excel Addict
Hi fellow Excel Addict,

T
hanks for joining me for another 'Excel in Minutes' tip. Today I'm going to introduce you to sparklines. Sparklines is an awesome feature that has been available since Excel 2010 but I recently realized that I had not covered it in this newsletter. If you have watched Mynda's Dashboard Webinars, you will have seen how sparklines are great for using dashboard reports.

If you happened to miss Tuesday's 'Excel in Seconds' newsletter, in it I showed how easy it is to 'Drill Down to See the Details for Any Pivot Table Subtotal'. You can read that newsletter here.


If you haven't paid any attention to Excel's sparklines before today, I think you are going to be very impressed with how easily you can jazz up your boring financial reports and look like an 'Excel expert'.

Also, note that TODAY (8pm PST) is your last chance to enroll Mynda's Excel Dashboard Course. You can still view her free webinar videos here.

Please feel free to share my newsletter with anyone you think could use a little Excel help.


Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
Email:  fhayes[AT]TheExcelAddict.com


Francis Hayes (TheExcelAddict.com)


Free Excel Dashboard Webinar


If you missed my last newsletter, you can click here to view it online.
 

 
TheExcelAddict.com Quote of the Day

"Do not limit your challenges, challenge your limits"
-- Anonymous --


Thanks to Kathleen M. for suggesting today's quote
 
If you have a favourite quote, send it to me and I may post it in my newsletter.


Today's Microsoft Excel Tip

Awesome Sparklines Will Quickly Spot Trends in Your Data

Summary:
If you haven't paid any attention to Excel's sparklines before today, I think you are going to be very impressed with how easily you can jazz up your boring financial reports and look like an 'Excel expert'.

Details:
Sparklines, introduced in Excel 2010, are miniature charts contained within a single worksheet cell. They provide a visual representation of your numeric data. Sparklines, usually displayed in cells adjacent to the data, allow end-users to quickly spot patterns that otherwise may not be obvious from looking at just the numbers.

In the following example, you can see the sparklines in cells H5 to H8. Each of these sparklines uses the data from columns C to G in its own row.

Excel Sparklines In Cell Chart_in_Microsoft_Excel_2010_2013_2016_365
Thanks to these sparklines, the trend for the production of each of the products in this example is immediately obvious. Without the sparklines, the numbers would not tell the same story so clearly.

So, why should you be using sparklines? Because sparklines are super easy to create, so you have no excuse not to use them.

DOWNLOAD PRACTICE FILE HERE

Creating sparklines from the Insert tab

1) Select the data which you will be using to create your sparklines (do not include row or column headings);

2) Click the Insert tab and look for the Sparklines group toward the middle of the Ribbon;

3) Select the type of sparkline you want to create (Line, Column, or Win/Loss);

4) The Create Sparklines dialog opens with the Data Range in the first box and a blinking cursor in the Location Range box. Since there will be a sparkline created for each row of your data, you will typically select the cells in the column next to the data;

5) Click OK and the sparklines appear in the cells;

Create Sparklines_in_Microsoft_Excel_2010_2013_2016_365
Creating Sparklines Using the Quick Analysis Tool

Another option for creating sparklines is the Quick Analysis Tool. Starting with Excel 2013, a new Quick Analysis Tool was added that appears when you select a range of cells containing data. This tool makes it even easier to add sparklines to your data.

Create Sparklines Quickly With Quick Analysis Tool_in_Microsoft_Excel_2013_2016_365

1) First, select the data you want to use to create your sparklines;

2) Click the Quick Analysis Tool icon that appears to the bottom right of the cells you have selected;

3) Click Sparklines to see the types of sparklines available;

4) In the Quick Analysis Tool, as you point to each of the sparkline types, a preview will be shown in your worksheet in the column immediately to the right of cells you have selected. To add the sparklines to your worksheet, skimpy click your desired sparkline type.

Sparklines Location
The Quick Analysis Tool places sparklines in the column to the right of your Data Range. If you want to put your sparklines in a different location, you will need to create them using the Insert tab method and choose the Location Range.

Create Sparklines To Left Above Or Below Data_in_Microsoft_Excel_2010_2013_2016_365
Grouped Sparklines
When you create sparklines, Excel automatically treats them as a group. This makes it easier for you to work with all of them and make consistent modifications to them. If you want, you can ungroup sparklines by clicking 
Design tab, Ungroup or you can right-click on the sparkline cells and choose Sparklines, Ungroup.


Formatting and Editing Sparklines

Sparkline Tools Design Tab On Ribbon_in_Microsoft_Excel_2010_2013_2016_365

When the cells containing the sparklines are selected, you will see a Sparkline Tools/Design tab on the Ribbon. You can use the tools on this tab to make modifications to your sparklines. For example, you may want to select the Markers options to display a marker for each data point on the sparkline. Or maybe you want to show just the High and Low points.

You can use the options in the Design tab to change the sparkline to a different type or change its style

From the Edit Data command on the left of the Ribbon (Design tab), you can edit the Data Range and Location Range either for individual cells (Edit Single Sparkline's Data...) or for the whole group (Edit Group Location & Data...). The Hidden & Empty Cells... option allows you to control how the sparklines display hidden and empty cells in the data range.

Sparklines Edit Options_in_Microsoft_Excel_2010_2013_2016_365
Resizing Your Sparklines
If you adjust the height of the row or width of the column containing the sparklines, the sparkline
will adjust to the size of the cell.

Resize Sparklines_in_Microsoft_Excel_2010_2013_2016_365

Deleting Sparklines
A
 sparkline isn't an object like a typical chart. So you can't simply select it and press Delete. To delete them, you must select the sparklines and, from the Design tab, choose Clear (to clear sparklines from just the cells you have selected) or Clear Selected Sparkline Groups (to clear the entire 'related' group). Alternatively, you can right-click and choose Sparklines, Clear Selected Sparklines (selected cells only) or Clear Sparklines Group.


Conclusion
Sparklines are a quick and easy solution for summarizing and instantly providing a visual analysis of your data. They can be used to build clear, concise and at-a-glance dashboard reports that will be used by others or you can simply use them as a temporary, ad hoc data analysis tool. Sparklines can be an alternative to placing large charts in your reports. If your Excel charting skills are lacking, sparklines are like magic. Anyone who can use Excel can use sparklines to bring new understanding to the numeric data that so many people love to hate.


FREE Excel Tips Newsletter from TheExcelAddict.com
Subscribe here to get more tips like this every week

Thanks for supporting this newsletter and website

Disclosure: Some of the resources I recommend on my website and in my newsletter pay me a small referral commission if you purchase from them through links on my website or using my referral code. This helps offset the costs of my website. I've worked long and hard to build up my reputation online over the past 10 years as someone who provides exceptional value to my readers. So I'm not willing to risk that for a few dollars. As you know, I don’t just recommend anything. It has to be of outstanding quality and value. If you are ever not completely satisfied with anything I recommend, please let me know and I will get your money back for you...GUARANTEED. You can't lose.
"Spreadsheets Tips From An Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.

8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630