Starting in the 2010 version, Excel has a type of line chart called Sparklines and Sparklines are little miniature charts that actually fit into the cells of the worksheet. Now you might not always want a whole table or you might not always have room for one. So Sparklines might fit the bill for you. Well, let's check it out. First, click anywhere in the data are, it doesn't matter and Sparklines like any other chart are things that we insert. So go to the Insert tab. And over here to the right of the Chart section, we have Sparklines.
Now click Line and the Create Sparklines dialog-box comes up. First thing we have to do is define the Data Range. So delete whatever is in here and let's start with the first number here. I'm not going to select the column headers. It's just the numbers. I'm just going to drag over. Now you could drag all the way down if you want. I'm just going to press Ctrl+Shift+Down Arrow key. It's just a much easier and faster way of selecting and scroll up. So now we have the Data Range. And now click here in the Location Range. And the Location Range is where do we want the Sparklines to go? So let's start over here in H5.
Now here we have to drag down if we press Ctrl+Shift+Down Arrow. Now there's new place for it to start and let's go ahead and let's scroll back up. So click OK. And boom! It just inserts those Sparklines and just click somewhere inside the Sparklines so you can get a better look. Now when you do that you have the Sparkline Tools in the Design tab showing up on the Ribbon bar. Before we change anything there, let's just take a look at the column itself, Column H. If we want these lines to be flattened out a little bit more, you put your mouse pointer here on the right border of Column H. Your mouse pointer turns to a two headed arrow.
Now you can click and drag out to the right and those lines flatten out. If you want them more compressed, just drag them back and now the lines are a little bit more craggy. I'm just kind of drag in a little bit to the right, a happy medium there. Well, we can do some formatting here. First, let's decide what kind of thickness we want for the lines. Go over here to the Sparkline Color. Choose Weight. And yes I know that's confusing. Why is Weight in with the Color tab? I don't know. It just is. And I'll choose the heavier line so that they stand out a little bit more.
As far as Markers go, we can display High Points, Low Points, First, Last Points and so on. So let's go over here in the Show tab and maybe choose High Point and Low Point. And now we can see with each one there's a High and Low. If you want, you can go here into Marker Color. Go over here to Markers. And we can change them as well, make them a little bit more visible. If you don't care about High Point and Low Point, maybe you just want First and Last. I'll turn those off and I'll turn on First and Last Points. And we can see those also. And I'll just turn off those Markers.
It really depends on what you need. There is no right way or wrong way. Well, even though these are called Sparklines, we also have a column variety. So over here click Column. And now we can see kind of little miniature column charts. And this might be a little bit more visible of where the high numbers are and where the low numbers are. Well, speaking of low numbers, you'll notice that some of these numbers here are negative. And here's another one. Wouldn't it be nice if we could make those negative numbers really stick out like a sore thumb? When you're looking at the data here, it's kind of hard to tell.
So click somewhere in the Sparklines, and again if you need to go back to the Design tab and choose Win/Loss. Right now, it's still kind of hard to tell. But let's click on the Style dropdown. Maybe choose a dark theme and go over here to Marker Color. Click that and for negative points, let's choose a light color. I'll choose a light green. And now we can see those negative numbers really sticking out. And you can see over here Negative numbers are turned on. And if you want, you can always turn them off. And that green goes away. Turn them on, the green comes back.
So the next time you need to create some charts to display just a general overview and you don't want to fuss with the whole charting package, try using Sparklines. It can save you a lot of time.
Author
Released
6/9/2010- Copying and pasting techniques
- Working with formulas and functions
- Dealing with formula errors
- Creating lookup tables
- Naming cell ranges
- Formatting data and worksheets
- Finding and replacing data
- Creating SmartArt diagrams
- Creating charts and PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Related Courses
-
Excel 2010: Tips, Tricks, and Shortcuts
with Dennis Taylor3h 43m Intermediate -
Excel 2010: Charts
with Dennis Taylor3h 38m Intermediate
-
Introduction
-
Welcome53s
-
-
1. Getting Started
-
Touring the interface3m 38s
-
2. Worksheet Basics
-
Creating a worksheet5m 23s
-
Targeting large data groups4m 26s
-
-
3. Excel Formula Basics
-
Entering data in a worksheet3m 22s
-
Adding numbers manually5m 1s
-
Adding a whole worksheet1m 48s
-
Using IF4m 49s
-
Using SUMIF and AVERAGEIF4m 15s
-
Naming and using cell ranges3m 45s
-
4. Essential Formatting
-
Adding pictures and shapes7m 19s
-
5. Advanced Formatting
-
Inserting SmartArt6m 54s
-
Applying built-in styles3m 16s
-
Creating and sharing styles5m 33s
-
Using templates4m 9s
-
-
6. Printing Preparation
-
Making the pieces fit4m 57s
-
Printing and PDFs4m 34s
-
-
7. Large Excel Projects
-
Finding and replacing data3m 12s
-
Managing worksheets7m 23s
-
-
8. Collaborating with Others
-
Setting workbook permissions6m 44s
-
Sharing a workbook1m 25s
-
Tracking changes3m 5s
-
Saving files in shared locations10m 29s
-
9. Exploring Excel's Database Features
-
Creating lookup tables6m 3s
-
10. Analyzing Data
-
Working with Goal Seek5m 29s
-
Using scenarios in formulas5m 28s
-
11. Advanced Analysis with PivotTables
-
Discovering PivotTables2m 22s
-
Creating a basic PivotTable2m 46s
-
Modifying a PivotTable6m 57s
-
-
12. Creating and Using Charts
-
Choosing chart types1m 55s
-
Inserting Sparklines3m 54s
-
Creating a column chart3m 23s
-
Modifying a column chart5m 47s
-
-
13. Creating and Using Macros
-
Understanding macros3m 5s
-
Editing a macro6m 50s
-
-
14. Customizing Excel
-
Customizing the Ribbon bar8m 44s
-
Setting Excel options8m 19s
-
Conclusion
-
Goodbye16s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Inserting Sparklines