Create In-Cell Visualizations: The REPT Function

Locked lesson.

Upgrade

Quick reference

Create In-Cell Visualizations: The REPT Function

Use the REPT function to create a dynamic in-cell bar. 

When to use

We can use the REPT function to create an in-cell bar whenever we want to extend the capabilities of our visualization by combining the REPT function without other functions and utilities. 

Instructions

The REPT function is a hidden gem in Excel. It allows us to repeat text a specified number of times. We can use the REPT function to create a dynamic in-cell bar. 

Using REPT to create a bar instead of using data bars is a more flexible option. Data bars can only display in the cell that contains the value and we cannot change the color of individual bars. 

In this example, we want to add a bar in the Comparison column that represents the value in the Score column. 

We can use the REPT function to do this. 

REPT has two arguments: text and number_times. 

text - this is the text that we want to repeat. 

number_times - the number of times to repeat the text. 

In this example, we are using the pipe symbol (vertical bar) as our text. Remember, text within formulas needs to be enclosed within quote marks. 

We are using the value in the score column to determine the number of times to repeat the pipe symbol. 

  • Press Enter and copy the formula down. 

  • Select the cell range. 
  • From the Home tab, in the Font group, change the font to Playbill. 

The bars are essentially a font, so if we want to change the color, we need to change the font color. 

The bars are dynamic, so if the value in the scores column changes, the bars will adjust accordingly. 

Add Conditional Formatting

We can make our bars more functional by combining REPT with Conditional Formatting. 

For example, we want to show the bar in a different color if the score is below 60. 

  • Click in cell C8. 
  • From the Home tab, in the Styles group, click Conditional Formatting. 
  • Click New Rule. 
  • Choose Use a formula to determine which cells to format. 

This formula will check if the cell is less than 60 or not. 

  • Click Format. 
  • Click the Font tab. 
  • Change the Font color. 
  • Click OK. 

  • Click OK again. 
  • Copy the formula down. 

If the value is less than 60, the bar will be green. 

Use REPT with a Symbol

We can also use the REPT function with a symbol. 

In this example, we want to add a star rating in column I to represent the employee's job rating in column H. 

  • Click in any blank cell outside of the data. 
  • From the Insert tab, in the Symbols group, click Symbol. 
  • From the Symbols tab, change the Font to Wingdings. 
  • Scroll through the gallery until you see the Star. 
  • Click Insert. 

  • Select the star. 
  • Press CTRL+C to copy. 
  • Type the REPT function in the cell. 
  • Press CTRL+V to paste the star in between quote marks. 

Notice the star is showing as a chevron. This is because we are not using the correct font. 

  • Copy the formula down. 

  • Select the cell range. 
  • From the Home tab, in the Font group, change the font to Wingdings. 
  • Change the font color if desired. 

 

Login to download
  • 00:04 Another method that we can use for creating really cool in-cell
  • 00:08 visualizations is by using the REPT function in Excel.
  • 00:12 And I find that the REPT function is one that's not so familiar to people.
  • 00:17 So we're going to take a look at a couple of examples because this can be really
  • 00:21 cool if you want to create little in-cell chats.
  • 00:24 And the cool thing about the REPT function is that it is dynamic,
  • 00:27 we can combine it with other formulas to make it a lot more functional.
  • 00:30 So let's take a look at a basic example and then we'll build from there.
  • 00:34 So this first example here, I've got a list of employee names and
  • 00:39 I've got their review scores out of 100.
  • 00:42 So I want to show these review scores represented as a bar in column C.
  • 00:49 Then you might be thinking to yourself, well, can't you just select them and
  • 00:54 maybe go to Conditional Formatting and add Data Bars to show that bar representation.
  • 00:58 Well, yes, we could do this, but when you use Data Bars,
  • 01:03 the bar will always be in the same cell as the value.
  • 01:06 Now of course, you could fiddle around with this and
  • 01:09 get the bars to output to a different column, but
  • 01:12 the REPT function allows us to place our bar in any cell really easily.
  • 01:15 Another advantage that the REPT function has is that we can combine it with other
  • 01:20 formulas and things like Conditional Formatting, so that the bar is dynamic and
  • 01:24 it changes color depending on what values we have in these cells.
  • 01:28 So let's see how the REPT function works.
  • 01:31 We're going to type in, equals REPT.
  • 01:34 Now the REPT function only has two arguments, text and number of times.
  • 01:40 Now text, is the text that you want to display.
  • 01:44 Now this is going to look a little bit weird to you but
  • 01:46 bear with me it will all become clear.
  • 01:48 Now remember, when you're adding text into a formula it needs to go in quote marks.
  • 01:53 And what we're going to add here is the pipe symbol.
  • 01:56 And basically REPT stands for repeat.
  • 01:59 So what we're saying here is repeat whatever the text that we've specified is
  • 02:04 and then we can choose the number of times we want to repeat it.
  • 02:07 So my next argument, number of times, is going to be whatever the score is in here.
  • 02:13 So, what I would expect when I press Enter on this formula,
  • 02:16 is to have that pipe symbol repeated 86 times in my spreadsheet,
  • 02:20 which is going to look a little bit strange at first.
  • 02:23 So let's hit Enter.
  • 02:25 Now that's exactly what I get.
  • 02:27 I can double-click to copy this formula down, so
  • 02:30 I now get that pipe symbol whatever number of times is specified in the score column.
  • 02:35 Now once we have this pipe symbol,
  • 02:37 what we can do is press Ctrl+Shift+arrow down to select all of them,
  • 02:42 and then jump up to the Home ribbon and change the font up here to Playbill.
  • 02:48 Let's hit Enter.
  • 02:49 Check it out.
  • 02:50 We now have a bar instead of those pipe symbols.
  • 02:54 And of course, we can change the color of this bar.
  • 02:56 So remember this is effectively a font.
  • 02:58 So if I want to change the color, I can press Ctrl+Shift+arrow down to select them
  • 03:03 all, and I can change the color of these bars by just changing the font.
  • 03:07 So maybe I want these to be a light orange color.
  • 03:11 And these are dynamic, so if this changes to let's say 65,
  • 03:14 that bar is also going to change.
  • 03:17 Now we can take this a stage further.
  • 03:19 So maybe what I want to happen here is that if the score,
  • 03:23 the review score is less than 60, I want the bar to be a different color.
  • 03:29 So what we need to do here is we're going to select the first cell.
  • 03:33 We're going to jump up to Conditional Formatting and New Rule.
  • 03:37 Now we're going to use a formula to determine which cells to format.
  • 03:41 And the formula is a very straightforward one, we're going to say equals,
  • 03:45 if the value in this cell, and
  • 03:46 we don't want that to be absolute because we're going to copy it down.
  • 03:50 So I'm going to press F4 three times to make that relative.
  • 03:54 If cell B8 is less than 60, we want to change the font color,
  • 03:59 remember, this is effectively a font.
  • 04:02 So we're going to go to Format, to the Font tab, and
  • 04:05 we're going to make these a light green color.
  • 04:08 Let's click on OK, and OK again.
  • 04:11 And then we can simply copy this down, and check it out.
  • 04:16 Every time the value in this cell is less than 60, that bar is showing as green.
  • 04:22 And of course this is also dynamic, so if we change one of these down here to 55,
  • 04:27 we'll find that that bar will turn green as well.
  • 04:30 So the REPT function is amazing for creating dynamic in cell charts.
  • 04:34 Now another cool example of how we can use the REPT function is by using a symbol.
  • 04:39 So in this second example,
  • 04:40 again, you can see I have some employees, I have their Job Rating and
  • 04:45 I basically want to give them a Star Rating based on their job rating.
  • 04:49 So the first thing we need to do here is we need to insert a symbol of a star
  • 04:53 somewhere into the spreadsheet.
  • 04:55 So we're going to go to Insert, we're going to go to Symbols, and
  • 04:59 we're going to choose Wingdings in the top, mine's already there.
  • 05:03 And if you scroll through this list you should find that there is a star
  • 05:07 just here.
  • 05:08 Now I have it in my recently used symbols, so I'm going to select it and
  • 05:12 insert that in.
  • 05:13 So now what we need to do here is we need to copy,
  • 05:16 Ctrl+C this symbol and then we can construct our REPT formula.
  • 05:21 So our text is going to be the star, Ctrl+V.
  • 05:25 Now, notice that it's showing as chevrons and I'm going to tell you why in a moment.
  • 05:30 Let's choose number of times, like so.
  • 05:33 Close the bracket and hit Enter.
  • 05:35 Now you'll notice that we are getting chevrons as opposed to a star, and
  • 05:40 that is simply because we're not using the Wingdings font.
  • 05:43 So remember this star is effectively a Wingding symbol, so
  • 05:46 we need to change the font that we're using.
  • 05:49 So let's click the drop down, and we want to go to Wingdings.
  • 05:53 There it is, and it will change all of those to stars.
  • 05:57 And of course, once again,
  • 05:58 I can change these to a gold color to make them look more realistic.
  • 06:01 So those are two really cool ways that you can use the REPT
  • 06:06 function to produce more engaging in-cell visualizations.

Lesson notes are only available for subscribers.

Gift this course