CPE

Sparklines part 1: Line and column sparklines

chart drawn on a piece of paper

Suppose you work for a large bank with 1,000 branches. Each week you need to summarize the daily customer counts for each branch. You could create a line chart with 1,000 lines (one for each branch), but that would be cluttered beyond belief. Fortunately, beginning with Excel 2010, Excel allows you to create sparklines, which summarize a row or column of data with a chart in a single cell. The workbook Sparklines.xlsx illustrates the use of sparklines.

Figure 1 (see the worksheet “Line”) shows an example of sparklines.

A picture containing hanging, people, wooden, group

Description automatically generated

Figure 1: Line sparklines

In rows 8-14, columns D-H show the daily customer count for 7 bank branches. The line sparkline charts in I8:I14 summarize the daily customer counts for each branch. You can see that for each branch, Friday is the busiest day and either Tuesday or Wednesday is the least busy day of the week. To create these line sparklines proceed as follows:

1. Select the range I8:I14 and from the insert tab select line from the sparklines group.

2. Select the data range for the sparklines by filling in the create sparklines dialog box as shown in Figure 2.

A screenshot of a cell phone

Description automatically generated

Figure 2: Creating line sparklines.

3. When your cursor is in any cell containing a sparkline, the sparkline option will appear on the ribbon. Selecting sparkline from the ribbon brings up the menu shown in figure 3.

Figure 3: Sparkline menu

From this menu, we checked the high Point and low Point to ensure that for each row the busiest and least busy day were highlighted. You can also change the color of the sparklines and/or the color of the high and low point markers.

Column sparklines

In the worksheet “Column,” we created Column sparklines (see Figure 4) and highlighted the high and low Points. To create the column sparklines choose I9:I14 and then choose column from the sparkline group and then choose the high and low points to be marked.

A screenshot of a social media post with text and people in the background

Description automatically generated

Figure 4: Column sparklines 

Watch your scaling!

In the worksheet “Wrong Scaling” (see Figure 5) we reduced the number of customers visiting the New York branch. Our column sparklines don’t reflect the reduction. For example, the New York sparkline indicates that on Friday, New York has roughly the same number of customers as the other branches, and this is not the case.

A screenshot of a cell phone

Description automatically generated

Figure 5: New York customers reduced.

The problem is that the default setting for sparklines is that each sparkline is scaled against only the data used to create that sparkline. We want each sparkline to be scaled against all the data. To accomplish this goal, from the sparkline menu (see figure 6) select axis and change the vertical axis minimum and maximum value settings to same for all sparklines.

A screenshot of a cell phone

Description automatically generated

Figure 6: Settings to scale all sparklines the same

As shown in worksheet “Right Scaling” (see figure 7), our sparklines now correctly show that New York is not nearly as busy as the other branches.

A screenshot of a cell phone

Description automatically generated

Figure 7: Correctly scaled sparklines

Read as we discuss win loss sparklines, which are a great way to summarize how to compare your organization’s actual and targeted performances.

Share

FacebookLinkedinXEmail
CPE FREE COURSE
Sidebar CTA
Browse our CPE Offerings

About the author

Dr. Wayne Winston is a Professor Emeritus of Decision Sciences at the Kelley School of Business at Indiana University. He holds a B.S. in Mathematics from M.I.T. and a PhD in operations research from Yale. He won more than 40 teaching awards at Indiana University. He has written more than a dozen books including "Marketing Analytics", "Data Analysis and Decision Making", "Operations Research", "Practical Management Science", "Office 365 Data Analysis and Business Modeling", and "Mathletics." Dr. Winston has taught classes and consulted for many leading global organizations. He is also a two-time Jeopardy! champion and has consulted for the NBA’s Dallas Mavericks and New York Knicks. His Linkedin class on Forecasting has been completed by over 250,000 learners. He has also published 30 research articles.

Featured

Accounting Tips: Schedule K-1 Form 1065
Accounting Tips: Schedule K-1 Form 1065
Read More
How a CPA should respond to a known or suspected NOCLAR
How a CPA should respond to a known or suspected NOCLAR
Read More
5 best practices when performing a remote audit
5 best practices when performing a remote audit
Read More

Now Leaving Becker.com

You are leaving the Becker.com website. Once you click “continue,” you will be brought to a third-party website. Please be aware, the privacy policy may differ on the third-party website. Adtalem Global Education is not responsible for the security, contents and accuracy of any information provided on the third-party website. Note that the website may still be a third-party website even the format is similar to the Becker.com website.

Continue