Excel Techniques Used in the Article: ARRAY functions, =TRANSPOSE(), centre across selection, cell alignment, cell borders, =REPT(), wrap text, WINDINGS font, axis alignment, chart title linked to a cell, range names, format cells

Introduction

For several years I have been demonstrating how to use the =REPT(text,number_times) function to prepare rough and ready charts/graphs that look like this:

Screen Shot 2013-12-01 at 10.06.55

In the workbook that accompanies this page (available free of charge from the link at the bottom of the page), this range starts with Bin in cell C9. The f or frequencies column contains the ARRAY function =FREQUENCY(data,C10:C24), starting in cell D10 and finishing in cell D24. Remember, to array enter this FREQUENCY function and notice the range name data, the name I have given to the raw data.

To create that FREQUENCY, array entered function, select the entire range D10:D24, type in =FREQUENCY(data_array,bins_array) and instead of just pressing Enter, press Ctrl+Shift+Enter on a Windows computer and cmd+Enter on a Mac … try it again if it doesn’t work the first time!

That will give you the results in the f or frequencies column.

The =REPT Histogram: rough and ready

In cell E10 enter =REPT(“n”,D10) but when you press enter and see nothing, don’t worry as cell D10 in this example contains 0 so there is nothing for this function to show! However, copy that formula down to cell E24 and you will now see the letter n appearing like this: nnnnnnnnnnn in cell E11, n in cell E13, nnn in cell E18 and so on. So far so what? Take the fist step to histogram magic now by selecting all of the range E10:E24 and changing the font to WINGDINGS and you see this:

Screen Shot 2013-12-01 at 10.25.24

You should now have the rough and ready histogram I showed you at the start of this article. It’s fine isn’t it!

=REPT Warning: big numbers are iffy!

The =REPT function is fine until, for example, f = 100 or 1,000 then there are 100 and 1,000 letter n in a cell when we use REPT. What we can do is to change the REPT function like this, if the results are large =REPT(“n”,D10/10) or =REPT(“n”,D10/100). Dividing D10 by 10 or 100 in this case shrinks 100 to 10 or 1 and it shrinks 1,000 to 100 or 10, when dividing by 10 and 100 respectively.

An Excel Histogram’s Supposed to Look Like This

I know what you’re thinking: that’s an amateur thing to prepare and to some extent I agree. But it’s no more or less than rough and ready and that’s all I promised. After all, an Excel Histogram should look like this:

Screen Shot 2013-12-01 at 10.10.42

The =REPT Histogram Could Look Like That and Here’s How!

What, then, if our =REPT histogram could look like this:

Screen Shot 2013-12-01 at 10.35.57

Really? Did I use VBA? Voodo? No! I just used =REPT and a bit of jiggery pokery. Here is what I did.

I prepared the original =REPT Histogram as you have seen.

Using =TRANSPOSE() I copied the =REPT Histogram results from E10:E24 to H26:V26 in my case. That gave me this:

Screen Shot 2013-12-01 at 10.42.15

Which I then transformed by selecting this range and applying Wrap Text to it which, when I changed all column widths to 2.00 and the single cell height to 150, gave me this:

Screen Shot 2013-12-01 at 10.45.44

Now, that’s pretty good I think … I have never seen REPT made to do this before but that does not mean I think I invented it. It just means that I think few people have done it!

Without dragging this out any further, what has to happen now to turn those =REPT boxes into a proper Histogram is to do this:

axes borders:

select and create a left border for the cell H26

select and create a bottom border for the cells H26:V26

vertical axis range:

type this in cell G26:

=MAX(D10:D24)&”. . . . . . . . . . . . … .. . . . . . . . . . . . . .”&MIN(D10:D24) … this is a bit of a cheat I have to admit but, hey, it worked!

horizontal axis ranges:

in H27:V27 array enter =TRANSPOSE(C10:C24)

in H28 enter  – and copy it across to I28:V28

in H29 enter  <=

in I29:V29 array enter =TRANSPOSE(C10:C23)

chart titles:

heading: enter Histogram in cell and centre across selection for the range H25:V25

horizontal axis label: in cell H30 type Class Intervals and centre across selection for the range H30:V30

vertical axis label: type frequencies in cell F26 and align it by rotating it counter clockwise 90 degrees … change the column width to 2.00

Conclusions

I wrote this page for one very simple reason: this morning I woke up and thought about the REPT function and suddenly realised that using wrap text to turn a horizontal result into a vertical result might work. Having thought of that I knew I had to try it to see if I could create the histogram you now see here.

More than that, the number of techniques I have used in this apparently simple exercise is relatively large. Not massive and not advanced but it should prove to be a good exercise for an Excel beginner/lower intermediate!

Duncan Williamson

1st December 2013 … the first in advent so wherever you are, merry Christmas 2013!

Download the Work Book: click rept_vert_histogram

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: