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
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:
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:
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:
The =REPT Histogram Could Look Like That and Here’s How!
What, then, if our =REPT histogram could look like this:
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:
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:
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:
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)
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
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!
1st December 2013 … the first in advent so wherever you are, merry Christmas 2013!
Download the Work Book: click rept_vert_histogram