In-Cell Charts Using the REPT() Function

Locked lesson.

Upgrade

About this lesson

Leveraging the REPT() function and a special font in order to build charts in cells via formulas

Quick reference

In-Cell Charts Using The REPT() Function

Building in-cell charts by repeating characters x numbers of times.

When to use

When you want to build attractive progress bars using formulas rather than other methods.

Instructions

Lay the groundwork

  • Before you start, figure out how long you want your progress bar to be.  (I’ve found 10 characters to be a good number)
  • Create a cell that shows the progress to your goal, returning a value that you can easily convert to a scale of 1 to 10

Get the Symbol Needed

  • Go to Insert --> Symbol (far right) and choose a Wingdings fonts (Wingdings2 has the squares)
  • Locate the character you want and click Insert to insert it in the spreadsheet, then click Cancel
  • Your formula will be in edit mode, so enclose the character in the REPT function as follows:
    • =REPT("¢",<number_of_times>)
  • This will repeat the character the number of times specified, building a chart

Adding additional symbols

  • The trick is to get the character you need, as it often isn’t on your keyboard
  • Select another cell in the worksheet and insert your symbol
  • When you return to the worksheet, copy the required character from the formula bar
  • Modify your original format, adding another REPT() function using the & key and paste in your character
    • =REPT("¢",<number_of_times>)&REPT("£",10-<number_of_times>)

Hints & tips

  • Once you’ve inserted a character once, it will show up in your Recently Used Symbols list
  • Any character can be used that makes sense to you
  • It is a good idea to place logic in your values to ensure that you never end up with an error in your formula, or you will see some very odd results!
  • To jump to the black square, choose the WingDings 2 font and enter Character Code 162
  • To jump to the white square, choose the WingDings 2 font and enter Character Code 163
Login to download
  • 00:05 In this module, I'm gonna show you a super cool technique to build a progress
  • 00:09 meter right in a cell using formulas.
  • 00:12 And this has worked forever, really, in Excel, and it's super cool.
  • 00:16 So let's go take a look.
  • 00:17 The first thing we need to know is, how close are we to budget anyway?
  • 00:22 Well, this is most easily figured out by using a simple little rounding function,
  • 00:26 where we're going to round our total revenue divided by total budget,
  • 00:31 and we're gonna round that off to one decimal place.
  • 00:35 When we hit Enter on this, it tells us that we're 0.9 or
  • 00:38 90% of the way towards budget.
  • 00:40 So that part is pretty easy.
  • 00:42 Now, I wanna use this number with a little bit of math to
  • 00:46 repeat a certain character nine times.
  • 00:49 This is the basis of this in-cell chart, but which character?
  • 00:54 Well, this is where things get kinda neat.
  • 00:56 So what we're gonna do is we're gonna go to the Insert tab and
  • 00:58 we're gonna go all way across to the right hand side.
  • 01:01 Now, my ribbon's a little bit collapsed, but on the furthest right side of yours,
  • 01:05 you'll find this Symbol icon.
  • 01:07 So when I click on that,
  • 01:09 it takes me into this dialog you've probably never visited before.
  • 01:13 Now, you'll notice that we are in a font of normal text.
  • 01:16 I'd like to change this.
  • 01:17 Somewhere way down near the bottom here,
  • 01:20 there's a specific font that I'm looking for which is called Wingdings 2.
  • 01:25 And on the Wingdings 2 font, you'll find all kinds of super useful things that
  • 01:30 are relevant to your business today, like a 1970s-style telephone, or
  • 01:35 a CD, or tapes.
  • 01:36 I mean, man, it's been a long time since we've seen those.
  • 01:39 No, I'm kidding,
  • 01:40 actually there is some really useful stuff in here that is very useful.
  • 01:42 If you scroll down a little bit,
  • 01:44 you'll find these two guys here, the black square and the white square.
  • 01:47 They may be in a different column for you, but they're gonna be there somewhere.
  • 01:51 What I'd like you to do is click on the black square.
  • 01:54 You'll notice that this is character code 162.
  • 01:56 So if you can't find it, just change to the Wingdings 2 font and
  • 02:00 change to character code 162, and then click Insert and Close.
  • 02:06 What you'll see is in your formula area here in the cell,
  • 02:10 you'll see the black square, which is cool.
  • 02:12 But in the formula bar, you'll see the cent character.
  • 02:16 And the reason we go and get it from the symbols areas is because I don't really
  • 02:19 have a cent character on my keyboard.
  • 02:22 So now that I have that,
  • 02:23 what I'm gonna do is I'm gonna edit in the formula bar up top and
  • 02:27 I'm gonna say right in front of the cent character =REPT, and open parenthesis.
  • 02:34 This function allows me here to repeat a piece of text a certain number of times.
  • 02:39 Of course, it's text, so it needs to be wrapped in quotes, and
  • 02:43 then I get to tell it, how many times would you like to repeat it?
  • 02:46 Well, what I'm gonna do is I'm gonna choose T7 times 10.
  • 02:53 So we'll repeat it 9 times.
  • 02:55 And then we'll close the parenthesis to balance out the formula.
  • 02:58 Of course, before we commit it, we should really take a second to appreciate
  • 03:01 the kind of coding language that we're writing in.
  • 03:03 It's always best when somebody's standing over your shoulder watching you do that,
  • 03:06 cuz this looks really impressive.
  • 03:08 And now, what we can do is we can say Enter.
  • 03:12 Now, if it returns the characters here, it's not a big deal.
  • 03:16 All we need to do is go back and say, hey, let's just change the font here as well
  • 03:21 to Wingdings 2,
  • 03:25 which will be in your font list, and it will now represent as these squares.
  • 03:29 That generally only happens if a cell has been formatted with a font beforehand.
  • 03:32 But we've now got something that shows us a pretty cool little progress meter with
  • 03:36 nine black squares.
  • 03:39 Now, if I'm trying to get to budget though,
  • 03:41 I really want it to get to all ten squares.
  • 03:43 So it would be even better if I could add one more character to the end
  • 03:46 to show the part that hasn't been done.
  • 03:49 So here's what I'm gonna do.
  • 03:50 I'm gonna select a different cell, go to Insert,
  • 03:54 go to Symbols, and now on my Wingdings 2,
  • 03:57 I'm gonna find the square that was next to it, the white square.
  • 04:03 Now, you'll notice also after you've done this a couple of times, you don't really
  • 04:06 have to hunt for them because they do end up in your recently used symbols list.
  • 04:09 So if I were to double click on this guy, or click on him rather, and say Insert,
  • 04:15 you'll notice that it immediately adds it to the most recently used symbols list.
  • 04:18 So once you've done this once, it's very easy to pick them off.
  • 04:21 Now we can say Close.
  • 04:22 And you'll notice that the character is here, and
  • 04:24 in the formula bar it's listed as the British pound.
  • 04:28 Now, I'm gonna hit Ctrl+X on this guy to copy it, or cut it, and then Escape.
  • 04:35 I'm now gonna go back to my original formula and
  • 04:38 I'm gonna come up to the end and I'm gonna append some more text to it.
  • 04:41 We are gonna say and REPT, open brackets,
  • 04:45 open quotes, Ctrl+V to paste the British pound symbol.
  • 04:49 How many times do we wanna repeat this one?
  • 04:52 Well it's gonna be 10 minus T7 times 10.
  • 04:59 So we're gonna repeat it whatever the difference is, and we'll now say Enter.
  • 05:05 And you'll notice that we get a little white square.
  • 05:07 But there's one small problem here.
  • 05:09 While it works fine for Tarps and Groundsheets and
  • 05:12 Tents, when we hit Accessories things go sideways.
  • 05:16 And the reason being is because we've exceeded budget by 20% here.
  • 05:21 We need to find out what happened.
  • 05:23 All we need to do is change to any other font and
  • 05:25 you can see that it returns a value Error.
  • 05:28 Now, that's a challenge, but it's easily solved by coming back and saying,
  • 05:33 let's wrap this guy here with minimum of the actual percentage or 1.
  • 05:40 When we do that, it now caps it with a percent target at 100%,
  • 05:43 and our progress chart will not blow up on us anymore.

Lesson notes are only available for subscribers.

Gift this course