Putting Excel's REPT Function To Good Use

Thu 16th February 2012

Although Excel offers a wide variety of charts and graphs to represent data, a simple bar chart can be created using the REPT function. This article shows how to create such a chart, using a repeated sans-serif upper case letter I for the bars.

One aspect of computers that has made our daily lives a lot easier is the ability to repeat processes. Data entry clerks thank their stars for the AutoComplete function, which saves them having to type the same towns and cities repeatedly when entering address information, and the ever-reliable Copy and Paste can reproduce anything, from swathes of text to detailed images, at the click of a mouse.

A less well known way of repeating data allows you to instruct Excel to repeat a character, or a series of characters, as many times as you like up to a limit of 32,000. It is a simple process and, as we shall see, one that can be put to real practical use.

To test this function, I chose to repeat the smiling face emoticon my girlfriend often uses in text messages to me:

^_^

To begin with, I decided that I wanted to repeat these characters four times, so I entered 4 into cell B2. In cell B3 (or I could have used the formula bar), I entered the following:

=REPT("*", B2)

What this is telling Excel to do is to repeat what is contained within the speech marks by the value in cell B2. So in this case, I got four asterisks in cell B3. To change those asterisks into my emoticons, I made the following alteration:

=REPT("^_^", B2)

This gave me four emoticons, but it also drew my attention to an important point. Whatever is entered within the speech marks will be reproduced exactly on the worksheet. In this case there were no spaces between the emoticons and the effect was lost. To create individual faces I needed to insert spaces either side of the emoticon, altering the formula thus:

=REPT(" ^_^ ", B2)

This gave me four separate emoticons and my cell was set up.

I changed the value in cell B2 to 10 and ten emoticons appeared. I then changed it to 2000 and the emoticons shot right off the screen. I'll take Excel's word for it that there were two thousand of them - I didn't have time to scroll along and check.

It is here that the value of the REPT function becomes apparent. To demonstrate a possible use for it, let us refer to Billy, who operates a sideline renting out films and games from his shop.

Billy has decided to keep track of his rental transactions and he enters the figures into an Excel spreadsheet at the end of each week. He uses the REPT function to give him a visual representation of how each format compares against the others. Here is a breakdown of his rentals over the past week, along with the cell he will enter that value into:

DVDs: 34 (D2)
Games: 31 (D3)
Videos: 14 (D4)

In cell E2 he enters the formula:

=REPT("|",D2)

As with the emoticon earlier, Billy is instructing Excel to display the contents between the speech marks by the number shown in cell D2. After he has entered a REPT formula for each format, this is how Billy's values appear. I have used the pipe symbol (Shift + Backslash) for this demonstration, but, as we shall see, Billy uses an upper case letter I.

DVD: ||||||||||||||||||||||||||||||||||
Games: |||||||||||||||||||||||||||||||
Videos: ||||||||||||||

What Billy has created is an easy-to-comprehend visual representation of the number of rentals in what is effectively a makeshift horizontal bar chart.

Although Excel offers many ways for information to be represented in chart form, Billy has opted for a simple, easy to update method of keeping track of rentals. Now that he has sorted out the technical side, he can set about filling in the rest of the information, and jazzing the whole thing up a bit.

The B column will be dedicated to the date, so in cell B2 Billy types the header W/E, for week ending. Below this he enters the date. In the C column, he enters the three formats: DVD in C2, Games in C3 and Videos in C4. The D and E columns are already filled as above.

To brighten the whole thing up, Billy creates a coloured background. He selects a block of cells from B2 to G4, which he colours bright yellow using the Fill function. Although no data will appear in column G at this stage, he takes the block to that length so that all of the information displayed within is comfortably housed, and there is room for longer entries in future updates. To give each bar of his chart its own colour, he adds the following formatting.

For the first bar of the chart, he right clicks on the cell E2 (the one containing the formula for DVD rentals), and selects Format Cells from the list. Selecting the Font tab, he chooses a sans-serif font - in this case Arial. He clicks on Bold in the Font Style list, and then clicks the down arrow to reveal the colour palette. He chooses a colour that will stand out well against a yellow background, and then clicks OK. With the first bar done, Billy repeats the process in cells E3 and E4, assigning a different colour to each bar (I chose dark blue, black and red). He now has a rather tidy looking, easy to understand chart to show him how well or otherwise his rentals are doing.

The next time Billy comes to enter sales figures, he simply copies the entire yellow block, skips a row to leave a space between each chart, and pastes into cell B6. After altering the date, all he has to do is enter the new values, and the three coloured bars will adjust in length accordingly.

So here we have a way of creating a genuinely useful function based around a simple formula. More importantly, however, is that this exercise shows that, although a formula may look like a slice of gobbledegook at first glance, when you take a closer look at it, you can see what it is telling Excel to do, and it becomes a lot less daunting.

Author is a freelance copywriter. For more information on excel+training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-2046-putting-excels-rept-function-good-use.html