Of all the charting features in Excel, Sparklines are my absolute favorite. These bite-sized graphs can fit in a cell and show powerful insights. Edward Tufte coined the term sparkline and defined it as,
intense, simple, word-sized graphics
Edward Tufte
Sparklines (often called as micro-charts) add rich visualization capability to tabular data without taking too much space. This page provides a complete tutorial on Excel sparklines along with 5 secret tips.
What is a sparkline?
A Sparkline is a small chart that is aligned with rows of some tabular data and usually shows trend information.
Here is an example of sparklines in a project team status report.
Excel Sparklines Tutorial – 3 steps
Creating sparklines in Excel is very easy. You follow 3 very simple steps to get beautiful sparklines in an instant.
- Select the data from which you want to make a sparkline.
- Go to Insert > Sparkline and select the type of sparkline (you have 3 options – line, column and win-loss chart)
- Specify a target cell where you want the sparkline to be placed
- Optional: Format the sparkline if you want.
Here is a short screen-cast showing you how a sparkline is created.
Types of Sparklines in Excel:
There are 3 basic types of sparklines in Excel 2010. They are,
- Line chart
- Column chart
- Win-loss chart (useful for showing a bunch of wins & losses denoted by 1s and -1s)
Sparkline Formatting and Options – Explored
Whenever you select a cell with sparkline in it, you will find a new ribbon called as “Sparklines – Design” ribbon. This is where all the formatting options for sparklines are included. Some of the key formatting / customization options available are,
- Change the sparkline type – between line, column and win/loss
- Change the source data / target cells of sparkline
- Set different colors for first point, last point, highest & lowest points (applicable for column and line chart types)
- Set axis options (show / hide axis, set min and max value for vertical axis, set axis type to date axis etc.)
- Group / un-group a bunch of sparklines (you can change formatting options, axis settings en-masse when you group sparklines)
- Remove sparklines
Sparklines & Missing Data – How does it work?
- Non-numeric data: If the sparkline source data contains non-numeric data, they are neglected while plotting the sparklines.
- Errors & #NA values: If data has some #NA values, they are neglected
- Blanks: sparkline show blanks as gaps
- Zeros: If data has zeros, zero value is plotted
- Data in hidden rows / columns: If data has some hidden rows / columns, the values are neglected (unless you enable “Show data in hidden cells” option)
Sparklines in Tables & Pivot Tables
You can add sparklines to tables and pivot tables too. Adding them to pivot tables is a bit tricky but adding sparklines to tables is fairly straightforward and scales nicely.
5 Tips to use Sparklines better
Here is a bunch of quick tips & tricks for those of you starting on sparklines.
- You can auto-fill sparklines. Select the first set of values and add a sparkline. Now copy and past sparklines to auto-fill them based on data in adjacent cells.
- Change their size: When you adjust row-height or column-width of the cell containing sparkline, the size of sparkline changes too.
- Juxtapose sparklines with conditional formatting icons to create stunning charts and dashboards.
- If you want to copy a sparkline over to a ppt or document, you can use “copy as picture” option.
- Enable high / low points to highlight important values
Sparklines & Compatibility
Sparklines are available since Excel 2010. They work in desktop and web versions of Excel.
What happens when someone opens a file with sparklines in Excel 2007?
Sparklines don’t show up in if you open the file in older version of Excel (say Excel 2007).
How does Sparklines compare with other alternatives?
Sparklines vs. In-cell Charts
In-cell charts are a powerful and lightweight way to create bite-sized visualizations. The main technique is to use REPT formula to repetitively show a bunch of symbols (usually | symbol) to create a small chart. The advantage of this approach is that they work in any version of Excel. But the dis-advantage is that we can make only few types of charts (bar charts, column charts by rotating cell text, dot plots). Also, incell charts require some knowledge of excel formulas and creativity.
This is where Excel Sparklines shine, as they are very easy to create and maintain.
Sparklines vs. Conditional Formatting
In Excel 2007, MS introduced a bunch of useful Conditional Formatting options like icons, heat maps that effectively create small visualizations of underlying data. These features are further improved in Excel 2010, 2013 and 2016. While conditional formatting based visualizations are easy to implement and scale very well, there are only few options (a bunch of traffic lights, data bars etc.). This could leave you high and dry if you are looking for rich visualization options. these new features require the actual data to be present in underlying cells (which is a head-ache).
Again, sparklines shine as a simpler and easier alternative.
Sparklines vs. Shrinking an actual chart
We can take an actual chart, strip it of all the clothing (remove gridlines, axis, legend, titles, labels etc.) and resize it so that it fits nicely in a cell [example]. This is the easiest and cleanest way to get sparklines in earlier versions of excel. However this approach has one problem. It doesn’t scale. (ie if you want to get 2 sparklines, you need to do twice the work). Of course, we can write some macros to take care of that, but if you are open to macros, you might as well use SfE and save a lot of trouble. But this approach of shrinking a real chart is better as it gives you full power to customize the underlying chart (add multiple series etc.) which is not available in excel sparklines.
Download Excel Sparklines Tutorial Workbook
Click here to download Excel sparklines tutorial workbook. It shows all three kinds of sparklines in a simple dashboard format. Use the data to create your own sparklines to learn more.
Conclusions on Sparklines
The sparklines in Excel is certainly a great step forward in the world of data visualization. It brings ease and consistency to most users who want better visualizations but do not know how to create them. That said, Microsoft hasn’t really introduced any new types of sparklines since 2010. This is disappointing. Ideally few more types of sparklines such as these can help with dataviz.
On a lighter note, Kudos to Office Team at MS for not adding any 3D capabilities to these sparklines. That would have unleashed a fresh dose of chart monsters.
I use sparklines in most of my dashboards and business reports.
What about you? What are your thoughts on sparklines? Have you used them? What is your experience like? Please share your ideas, impressions and tips thru comments.
Additional examples on Sparklines:
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)