Create In-Cell Charts: Sparklines and Data Bars

Locked lesson.

Upgrade

Quick reference

Create In-Cell Charts: Sparklines and Data Bars

Visualize trends using in-cell charts. 

When to use

We use Data Bars and Sparklines whenever we want to visualize data based on the value in the cell or track trends in data. 

Instructions

In-cell charts are mini-charts that show in the cell. They help is visualize data at the cell level and also visualize trends. 

In this example, we will look at two types of in-cell charts: Data Bars and Sparklines. 

Data Bars

Data Bars are part of Excel's Conditional Formatting tools. They help us visualize and compare values in individual cells. 

  • Select the data. 

  • From the Home tab, in the Styles group, click Conditional Formatting. 
  • Hover the mouse over Data Bars. 

We can choose Solid Fill or Gradient Fill Data Bars. 

Modify Conditional Formatting Rules

When we add data bars, we are essentially creating a conditional formatting rule. We can make changes to the look and feel of the data bars by editing the rule. 

  • Click in any cell that contains a data bar. 
  • From the Home tab, in the Styles group, click Conditional Formatting and Manage Rules. 

  • Select the rule. 
  • Click Edit Rule. 

By default, data bars will use the minimum and maximum values in the data to determine the length of each bar in relation to the other bars. 

We can change this, by changing the type. 

For example, if we select Number from the drop-down list, we can set the minimum and maximum values we want the data bars to use. 

We also might want to only show the bar and not the value in the cell. This is useful if you are presenting confidential data to an audience. A good example would be a spreadsheet that contains people's salaries. 

  • Check the Show bar only box. 
  • Click OK. 

We can also modify the Bar Appearance. We can change the fill color to a custom color and also add a border. 

  • Click OK. 

Sparklines

Sparklines differ from Data Bars in that they show trends in data over time. There are three types of Sparkline: Line, Column, and Win/Loss. 

The Line Sparkline will show trends plotted on a line chart.

The Column Sparkline shows positive and negative values as columns.

The Win/Loss sparkline simply highlights the positive and negative values without representing the actual value. 

  • Select the data. 
  • From the Insert tab, in the Sparklines group, click Line. 

The Create Sparkline dialog box will open. If we already have our data selected, the Data Range field will be populated with the correct cell range. The Location Range is where we want to create the sparklines. 

  • Click OK. 

An example of the column sparkline option. 

An example of the win/loss sparkline option. 

Edit the Sparklines

We can make changes to the look and feel of our sparklines using the Sparkline contextual ribbon. 

Change the Sparkline Style
  • From the Sparkline tab, in the Style group, expand the gallery. 
  • Choose a style from the gallery. 

Once again, the colors we see here are controlled by the theme we are using in Excel. To choose a different color, click the Sparkline Color drop-down and select a color from the palette or choose More colors. 

Add and Modify Markers

We can add markers to our line to highlight specific data points. 

  • From the Sparkline tab, in the Show group, choose a marker option. 

We can choose to show a marker for the high point, low point, first point, or last point. To show a marker for all data points, select Markers. 

Choose a marker color from the gallery. 

We now have a marker that represents each data point (month). 

Hints & tips

  • Sparklines cannot be deleted by simply selecting the cell range and pressing Delete. We need to select the sparklines and then go to the Sparkline tab and choose Clear. 
Login to download
  • 00:04 In this lesson, we're going to take a look at another way that we
  • 00:08 can visualize data by using in-cell charts.
  • 00:11 So in-cell charts differ from regular charts in that they
  • 00:16 are contained entirely within one cell.
  • 00:19 And these little charts can be super useful for
  • 00:23 visualizing trends and time-based data.
  • 00:26 Now I'm going to show you an example of two different types of in-cell
  • 00:30 chart that we can work with in Excel.
  • 00:32 The first one is Sparklines and the second one is Data Bars.
  • 00:37 So, let's start out with Sparklines.
  • 00:39 So, as you can see on the screen here,
  • 00:42 I have a little bit of data that we want to represent in an InCell chart.
  • 00:46 So, I have some sales teams, 1 to 6.
  • 00:49 In column A, I have some months running across the top and
  • 00:53 then I have some sales figures that each of these teams have achieved.
  • 00:57 Now what I effectively want to do is in column N I want to see the trend of those
  • 01:02 sales across the different months, so I can see the peaks and the troughs,
  • 01:06 which is going to help me analyze this data a little bit better.
  • 01:10 There are a couple of different ways that you can do this.
  • 01:12 I'm going to start out by selecting all of the data.
  • 01:16 We're going to jump up to Insert and
  • 01:18 then notice that we have a Sparklines group over here.
  • 01:21 So we can choose to insert three different types of sparkline.
  • 01:25 We can insert a line, a column, or a win/loss.
  • 01:29 Now, I would say that win/loss is best if your data contains negative values.
  • 01:34 because it's going to show those in comparison to the positive values.
  • 01:38 Now I don't have any negative values in my data set, so
  • 01:42 I'm just going to choose Line.
  • 01:44 Now because I selected my cell range first in the data range field,
  • 01:48 it's already picked up the cell range that I want to use in my Sparklines.
  • 01:53 So I only need to provide the location range.
  • 01:57 So my location range is going to be these cells just here, N8 to N13.
  • 02:03 Now if I click on OK, we get a little line chart for each of the sales teams.
  • 02:08 Now currently this is just a fairly plain looking line.
  • 02:11 Of course, we can jazz this up a little bit by using some of the options on
  • 02:15 the Sparkline contextual ribbon.
  • 02:18 So from here I can change the style.
  • 02:19 So maybe I want these to be a different color, let's make them orange, why not?
  • 02:24 And if you want to get a little bit more granular you can choose a sparkline color
  • 02:28 and select a specific color from the palette.
  • 02:30 Now in the show group this is where we can add markers to our sparkline to make it
  • 02:35 a little bit easier to read.
  • 02:36 So I could choose to add a marker for the high points, the low points,
  • 02:41 the first point, the last point.
  • 02:43 And you can see it adds those little markers onto the line.
  • 02:47 Now if you want a marker for every single point, we can do that also.
  • 02:50 So let's go back to Sparkline.
  • 02:52 I'm going to remove all of these.
  • 02:54 And what I could do here, is just say markers, and it's going to put a marker,
  • 02:58 at each data point.
  • 02:59 So, for us that is at each month.
  • 03:01 And of course, we can change the marker color.
  • 03:03 So I'm going to go to markers and let's make these a bright blue, like so.
  • 03:11 So very quickly there, we've managed to create a really effective in-cell
  • 03:15 visualization of the data in our spreadsheet.
  • 03:17 And the cool thing about these is they are dynamic.
  • 03:19 So if the data changes,
  • 03:21 so let's just change this to something that's pretty wild, let's go for 10,000.
  • 03:25 You'll see that sparkline update, so everything is completely dynamic.
  • 03:30 Now if we just select the sparklines again, and go back up to the Sparkline
  • 03:34 ribbon, we could decide to change these into columns as opposed to a line.
  • 03:38 So if we go to the Type group over here, we currently have line selected, if I
  • 03:42 select Column, it's going to represent those values in a slightly different way.
  • 03:46 And once again I could mark the high point and the low point to make those stand out.
  • 03:52 I could change the marker color, so I could change the high point to,
  • 03:56 let's go for a bright yellow.
  • 03:59 And I could change the low point to, let's go for a green.
  • 04:03 So two different ways that you can display this data.
  • 04:06 And as I said, if you have negative values in your data, so let's just put
  • 04:11 a minus in here, it is actually reflected in our column chart style.
  • 04:15 But we also have a win/loss style just here as well which gives us
  • 04:19 a slightly different way of visualizing this data.
  • 04:22 Now I actually like these to be a line chart.
  • 04:26 So that is one method you can use to visualize data in one specific cell.
  • 04:30 Let's take a look at the next option that we have, which is data bars.
  • 04:34 Data bars do differ from sparklines quite a bit.
  • 04:37 Data bars are part of the newer set of visual conditional formatting rules.
  • 04:42 And what we can do here is we can select all of our data,
  • 04:46 and we can represent the value in the cell with a bar.
  • 04:50 So let's go to the Home tab, conditional formatting, data bars, and
  • 04:54 then we get a choice of gradient fill or solid fill.
  • 04:58 So if we hover our mouse over,
  • 04:59 we get a preview as to what that is going to look like.
  • 05:03 So I'm going to go, let's go for a yellow, actually, let's go for a green solid fill.
  • 05:08 And you can see those bars represent the values in the context of all of the values
  • 05:13 in the spreadsheet.
  • 05:14 Now when you add this, this is effectively a conditional formatting rule.
  • 05:17 So of course, you can then jump back into Conditional Formatting > Manage Rules,
  • 05:22 and you'll see your data bar rule just here.
  • 05:25 So we can edit this rule and we can make any changes that we need to make.
  • 05:29 For example, I can change the minimum and maximum values that the bar uses.
  • 05:33 So currently it's set to automatic,
  • 05:35 which basically means that Excel looks at the values that we have in the cells.
  • 05:40 It finds the minimum value, it finds the maximum, and
  • 05:43 it adjusts the bar length depending on those values.
  • 05:46 We can change those to something else.
  • 05:48 So maybe we want to be very specific about the number that we want as the minimum
  • 05:53 value, maybe it's going to be 1,000, so on and so forth.
  • 05:56 So you can make changes to that from here.
  • 05:59 We can also change the bar appearance from solid fill to gradient fill,
  • 06:04 and we can also choose our color.
  • 06:05 So we're not stuck with just those standard colors, we can get very granular
  • 06:10 about the colors that we're using so maybe I want to use a blue color.
  • 06:13 Let's click on OK.
  • 06:15 I could even add a solid border around the outside,
  • 06:18 so I'm going to add a dark blue border.
  • 06:21 And notice we also have an option to show the bar only, so this is particularly
  • 06:26 useful if you have sensitive data that you don't necessarily want to show the actual
  • 06:31 figures, but you just want people to see a bar representation of the value.
  • 06:35 So if we choose show bar only and click on OK, and OK again,
  • 06:40 take a look at those changes.
  • 06:42 We have a formatting, and also those values have been removed from the cells,
  • 06:47 so we can just see the bar.
  • 06:48 So again, this is another really great way of jazzing up your spreadsheets and
  • 06:54 using in-cell charts to represent values.

Lesson notes are only available for subscribers.

Gift this course