Create in-cell chart with Sparklines in #Excel

🎵🎤Do you wanna build the Sparklines?

Excel tip - Incell chart with Sparklines

Sparklines is not a new feature of Excel.  It was introduced in Excel 2010.  Yes, almost a decade ago.  But I am quite sure it’s still something new to a lot of regular Excel users. Sparklines is a great tool to enrich your report and it is super easy to implement.

You may watch this short video to learn it in one minute:

If you prefer reading to watching, please continue with this post.

You may also download a Sample file to follow along.

First thing first: Where is Sparklines? 

It’s under Insert tab of the ribbon.

Excel tip - Incell chart with Sparklines1.png

There are three types of Sparklines:

  1. Line
  2. Column
  3. Win/Loss

Line and Column, by their names, are quite straightforward.  Let’s demonstrate with examples.

Line Sparklines

Excel tip - Incell chart with Sparklines2

When we click on the Line of the Sparklines, the following dialog box opens:

Excel tip - Incell chart with Sparklines3.png

  1. Input the data range from where you want your in-cell chart (Sparklines) to be plotted
  2. Where you want your Sparklines to be places (the default cell reference is your current active range)
  3. When you are ready, click OK.

In our example, the respective inputs are shown below:

Excel tip - Incell chart with Sparklines4.png

Here we go!

Excel tip - Incell chart with Sparklines5.png

We may stop here, or go further…

The Sparkline tool

When we select a cell with Sparkline, we will see a tab of Sparkline appeared on the ribbon, where we can have more controls on the appearance of the sparklines.

Excel tip - Incell chart with Sparklines6

For example, we can apply different styles or colors of the line:

Excel tip - Incell chart with SparklinesA

We may also add High/Low/First/Last Points, or apply markers for each data point.

And we can customize the color of each point too. 🙂

Excel tip - Incell chart with SparklinesB

Note: Negative Points is not demonstrated above… but you should know. 😛

Column Sparklines

Want to change your mind with Columns instead?  No problem!  It’s just one-click away.

Excel tip - Incell chart with SparklinesC

Did you notice that the colors of different data points are preserved?  And of course, we may edit it again.

Note: Sparklines are intended to show trend.  And thus, the Y-Axis is, by default, set as automatic.  

When we use Column, as a general rule, it’s recommended to set all columns starting from 0. (You will see why soon).  This is how we do it:

  1. Click the “Axis” pull-down under the Sparkline tool
  2. Set the Vertical Axis Minimum Value
  3. Set it to 0
  4. OK

Excel tip - Incell chart with Sparklines7.png

Let’s watch it in action:

Excel tip - Incell chart with SparklinesD

See the difference?

Excel tip - Incell chart with Sparklines8.png

 

Win/Loss Sparklines

What about Win/Loss?  It’s for figures with positive/negative.

For example, if we are dealing with percentage change and we want to visualize the occurrence and position of ups and downs, it may be a good idea to use Win/Loss sparklines.

Excel tip - Incell chart with Sparklines9

Please note what’s checked on “Show”.  Try it out with the sample file.

As simple as this! 🙂

To Clear Sparklines…

… is a bit tricky.  We cannot press the “DELETE” key on keyboard to remove sparklines.  In order to remove sparklines, we can use the Clear button under Sparkline tool, or Clear All button under Home tab.

Excel tip - Incell chart with Sparklines10

 

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.