Tech, politics, sports, and the overuse of ellipses...
Published on May 25, 2010 By dan_l In Blogging

This one is pretty much old as dirt and not really all that elegant, but:

  • It can be implemented now, to whatever stupid report your boss has you do - without you having to really learn anything new
  • It generally has a little bit of 'wow factor' to Excel noobs (like your boss). Even though it's a stupid trick
  • You can couple it with some static color, conditional formatting, stuff that may already be in your tool box
  • If you're totally bad at Excel, this is a great little formula to start learning with. It's not one of these byzantine messes that gives you a headache to read - but it still has some head room to learn a lil something.
  • Total time to implement is about 2 or 3 minutes for the basics, 5-10 (no more) to do something marginally more complex at the bottom of the post.

Major limitations:

  • If your data is all over the place with crazy highs and lows, it might not work. I mean it will always 'work', but it could be a mess to keep the presentation right. As such, until you've got a good handle on it, make it the polish on something - not the point of something.
  • It doesn't print well. In fact, if somebody prints it out, the trick is exposed

The Basics:

So let's just say you've got data like this:

A B C

Name Score

Bob 20

Mike 15

Steve 35

Tim 50

You can go into column C. Punch in the formula =rept("|",b2) In the quotes is a pipe or verticle bar. You'll see that the pipe has been repeated the number of times specified in b2. But let's face it, that looks like shit and is totally useless. So, you adjust the font to something that actually makes it look good. Like Brittanic at 8 points. Poof:

Divide by Something, Stupid

So, sometimes our data isn't going to be arbitarily low numbers. Sometimes it's going to be in the thousands. Add three 0's to those numbers and see what a mess you've made.

Well that sucks, right? Neat little trick blown away by high numbers. Well, you could....play with the numbers. That is, after all, what Excel is good at, right? So before our equation was this:

=rept("|",b2)

Let's divide by 1000 to get this under control. There's 3 good ways to do this:

1. In cell c2, just change the equation to =rept("|",b2/1000). This is pretty simple, but it's also going to be a bit of a pain in the ass to maintain if Bob, Mike, Steve, and Tim are able to start putting up scores in the millions. It'll work.

2. Put 1000 in cell E10. Make your equation =rept("|",b2/e$10). Copy paste as normal. Note for the n00bs: The $ delimits excel from changing the number when you drag your formula down. I'm bad at articulating this shit, so try to do it without the $ and notice: 1. It doesn't work. 2. It doesn't work because the second you drag it down, Excel assumes that you want to look at E11, E12, E13 respectively----you haven't filled the 1000 in there, so it's not going to work.

3. Name a cell "mod" or something. Fill the cell with 1000. Have your equation read: =rept("|",b2/mod) / By comparrison, this is kind of a big kid trick, but knowing how to use named ranges is, imo, breaks down one of the big barriers that keeps people Excel stupid.

2 and 3 have the added advantage of letting you adjust the divide by number on the fly helping you to more quickly make it look right.

Either way: you'll find your miserable ghetto bar charts are back where they're supposed to be - and are still proprotional lending them their value.

So what? I've got more than one series of scores. I've got like 4. Your stupid trick isn't going to help me.

Yeah. Probably not. Here's where this stuff gets a little wonky. Let's just say that the data between the 4 series is still pretty predictable. Say---between 5-50. I picked that randomly. If you're following along at home, you can use =randbetween(5,50) or you can just fill in the 20 values by hand. Here's what I've got:

So there's really 2 ways to go about solving this problem. One involves one of the afore mentioned byzantine equations. If you're new to this stuff, I'll probably lose you, so I'll explain it to you the long way around.

1. In column F, put the rept function for column b. =rept("|",b2) in column f2.

2. Drag the equation out for 3 columns. Note that you didn't use the dollar sign, so Excel adjusted accordingly.

3. Drag all for of the columns down 3 columns. You'll be left with a mess that looks like this.

Let's go over to cell E11. Put in the formula =char(10) . Nothing happened? Good. Nothing was really supposed to. You just made an ingrediant for the next step.

Go to J2. Input the forumla:

=CONCATENATE(F2,E11,G2,E11,H2,E11,I2,E11)

Seriously. It's easier than you might think. Concatenate basically lets you mash bits of text together.

=contacenate("H","E","L","L","O") would result in the cell saying "hello".

It has a few niche uses from time to time. Mainly, I use it for creating count tokens when I'm using Excel 03 and am too lazy to make a sumproduct. char(10) (the contents of e11, basically inserts a couple of spaces. So basically, =CONCATENATE(F2,E11,G2,E11,H2,E11,I2,E11) tells excel to mash up 12 bars, 10 spaces, 18 bars, 10 spaces, 39 bars, 10 spaces, 9 bars, and 10 spaces.

Well that's pretty friggn useless, right? Once you punched in the formula, you'll find that you just made a bigger trainwreck out of it. Now, repeat those 3 steps for each of the 3 other series. You can also go through and pop dollar signs between e and 11 at each occurence and drag the formula down.

Now, highlight the 4 cells you made in column J. Context menu (right click) > Alignment tab > change your alignment to 90 degrees and tick the 'wrap text' check box. With the cells still highlighted, adjust your front to Brittanic bold, and change the font to 8. Finally, hide columns F-I.

End result:

It's ok. If you go back and divide by something, you might find the results are a little less awkward. When everything is divded by 5:

If anybody wants a copy of a worksheet with this in it for the purposes of screwing around, let me know, I'll gladly share.

I should add: The above mentioned long way to go about this involves not parsing each person's set of scores into 4 different cells before hand. Rather, you make it into one gigantic formula. The end results looks the same, but the formula looks like this:

=CONCATENATE(REPT("|",B2/5),E$11,REPT("|",C2/5),E$11,REPT("|",D2/5),E$11,REPT("|",E2/5))


Comments
No one has commented on this article. Be the first!