{"id":2522,"date":"2013-05-21T15:46:20","date_gmt":"2013-05-21T21:46:20","guid":{"rendered":"https:\/\/www.vertex42.com\/blog\/?p=2522"},"modified":"2024-11-06T18:16:45","modified_gmt":"2024-11-07T00:16:45","slug":"sparklines-in-excel","status":"publish","type":"post","link":"https:\/\/www.vertex42.com\/blog\/help\/excel-help\/sparklines-in-excel.html","title":{"rendered":"Sparklines in Excel – Mini Graphs that Make Data Analysis Fun"},"content":{"rendered":"

Would you rather wade through a table of data or look at charts? You'll probably say charts, maybe because they help you see data trends and interpret numbers more easily. Now let's consider the feature in Excel known as Sparklines<\/strong>.<\/p>\n

Sparklines<\/strong> are small, simple charts that are easy to make, easy to understand, and small enough to fit in a single cell alongside your data. Edward Tufte defines sparklines in his book, Beautiful Evidence<\/em>: \"Sparklines are data-intense, design-simple, word-sized graphics<\/strong>.\"<\/p>\n

Sparklines are different from other Excel charts because they are smaller, don't have labels, and show only one line of data. If you want to, you can<\/em> make a sparkline with Insert > Chart and take the time to shrink the new chart, re-size the axes, and get rid of all labels. But that takes a lot of time and can be tricky to get the graphs just the way you want them. Inserting a sparkline using the new Insert > Sparklines<\/b> feature in Excel 2010+ is much easier.<\/p>\n

Excel 2010+ provides 3 types of sparklines: Line, Column, and Win\/Loss. We'll show examples of each of these.<\/p>\n

Sparkline Type 1: Line<\/h2>\n

Line sparklines<\/strong> help you see data trends and answer questions like these: How much does my data fluctuate? Does it trend upward or downward? What are the high and low points? Here is an example with temperature data for a few cities (sample data from weatherbase.com).<\/p>\n

\n\"Line
Example of a sparkline showing monthly temperature with the high and low points marked.\n<\/div>\n

<\/p>\n

\n
Make each sparkline use the same vertical axis<\/div>\n

When you compare sparklines to each other, it is often very important to use the same vertical axis (meaning the same min and max values). To do that, select the sparklines and go to Design > Axis > Same for all Sparklines<\/strong>. If you want all the lines on the same chart, you should use a chart<\/a> instead of a sparkline.<\/p>\n<\/div>\n

Each of the sparklines in the example above use the same min and max values for the vertical axis. So, you can see right away that the temperature in Chicago varies more during a year than San Francisco.<\/p>\n

Sparkline Type 2: Column<\/h2>\n

Column sparklines<\/strong> are small column graphs that are great for comparing discrete groups of data. The table below shows the grade distribution for students' exams, with a different sparkline for each exam. Compare these sparklines to the table of data on the right. Do the sparklines help you understand the data better?<\/p>\n

\n\"Column
Example of column sparklines showing the grade distribution for each exam.<\/div>\n

Sparkline Type 3: Win\/Loss<\/h2>\n

Win\/Loss sparklines<\/strong> are similar to column sparklines except that they don't show size. They show only a bar above, a bar below, or no bar at all. All bars are the same height regardless of the value. Win\/Loss charts are great for binary data such as True\/False, OK\/Issue, or Win\/Loss. The example below shows season wins for different teams.<\/p>\n

\n\"Win\/Loss
Example of sparklines showing win\/loss records.<\/div>\n

How to Create a Sparkline in Excel 2010+<\/h2>\n

To create sparklines<\/strong>, you start with a table of numerical data, just like you would with any other chart. If you want to chart the average monthly temperature in different cities, create a table of the average temperature each month for each city. Then select just the cells with temperature data, not the cells listing the months or the cities. Go to the Insert<\/strong> tab. In the Sparklines<\/strong> group, choose one of the 3 types (Line, Column, or Win\/Loss). The Create Sparklines<\/strong> dialog box will appear, asking you to choose a location for the sparklines. Pick a range that is the same number of rows as your data. (Unless you want to chart columns instead of rows. Then the range should be the same number of columns as your data).<\/p>\n

To edit a sparkline<\/strong>, click on it and choose the Design<\/strong> tab on the ribbon. You have several customization options here. One is to mark some of the points, such as the highest and lowest values.<\/p>\n

To delete a sparkline<\/strong>, you can not<\/strong> just select it and push the delete key. Instead, once you've selected the sparkline, you'll need to go to the Design<\/strong> tab on the ribbon and click Clear<\/strong>.<\/p>\n

That's all you need to know to get started using sparklines. There are many more applications for sparklines. Be creative! They're easy enough to add that it's worth experimenting to see if a sparkline will help you analyze your<\/strong> data.<\/p>\n

References<\/h3>\n

See Vertex42's original article on Sparklines<\/a> from 2006.<\/p>\n

This Blog Article<\/a> from Microsoft Office explains how to insert and edit sparklines.<\/p>\n

About the Author<\/h2>\n

At the time of publication, Laura Cutler<\/a> was an Excel specialist and freelance writer. She is now an Assistant Teaching Professor at BYU in the Information Systems department.<\/p>\n","protected":false},"excerpt":{"rendered":"

Sparklines are little in-cell charts that can help you analyze your data. This article shows examples of the three types of sparklines available in Excel 2010 and 2013.<\/p>\n","protected":false},"author":10,"featured_media":2615,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[37],"tags":[],"class_list":{"0":"post-2522","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-excel-help"},"_links":{"self":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/2522","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/comments?post=2522"}],"version-history":[{"count":0,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/posts\/2522\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media\/2615"}],"wp:attachment":[{"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/media?parent=2522"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/categories?post=2522"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.vertex42.com\/blog\/wp-json\/wp\/v2\/tags?post=2522"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}