The REPT function in Excel is used to repeat a text or a character a given number of times. For example, =REPT("x",5) returns xxxxx. This function can be useful for creating simple in-cell charts or histograms to visualize data. In this article, we will explain how to use the REPT function to show data in graphic mode, and provide a detailed example with real data.
The basic syntax of the REPT function is:
=REPT(text, number_times)
where text is the text or character to repeat, and number_times is the number of times to repeat it. The result of the REPT function is always a text value, even if the input is a number. The result cannot be longer than 32,767 characters, or the function will return #VALUE! error.
To use the REPT function to show data in graphic mode, we need to choose a suitable character to represent the data values, such as | or *. Then, we need to scale the data values to fit the desired length of the chart. For example, if we want to show the values from 0 to 100 in a 10-character chart, we can divide the values by 10 and use the REPT function to repeat the character accordingly. For example, =REPT("|",A1/10) will create a chart like this:
| Value | Chart |
|---|---|
| 0 | |
| 10 | | |
| 20 | || |
| 30 | ||| |
| 40 | ||||| |
| 50 | |||||| |
| 60 | ||||||| |
| 70 | |||||||| |
| 80 | ||||||||| |
| 90 | |||||||||| |
| 100 | ||||||||||| |
Procedures
To use the REPT function to show data in graphic mode, follow these steps:
- Select a character to represent the data values, such as
|or*. - Determine the maximum value and the desired length of the chart.
- Scale the data values by dividing them by the maximum value and multiplying by the desired length.
- Use the REPT function to repeat the character according to the scaled values.
- Format the cells as text to display the charts correctly.
Example
Let’s say we have a table of sales data for four products in different regions, as shown below:
| Product | Region | Sales |
|---|---|---|
| A | North | 120 |
| A | South | 80 |
| A | East | 100 |
| A | West | 90 |
| B | North | 150 |
| B | South | 110 |
| B | East | 130 |
| B | West | 140 |
| C | North | 180 |
| C | South | 160 |
| C | East | 170 |
| C | West | 190 |
| D | North | 200 |
| D | South | 210 |
| D | East | 230 |
| D | West | 220 |
We want to create a simple in-cell chart to compare the sales of each product in each region. We can use the following steps:
- Choose the character
*to represent the sales values. - Find the maximum sales value, which is 230 in this case, and the desired length of the chart, which is 20 in this case.
- Scale the sales values by dividing them by 230 and multiplying by 20. For example, for the first value of 120, the scaled value is
=120/230*20, which is 10.43. - Use the REPT function to repeat the character
*according to the scaled values. For example, for the first value of 10.43, the REPT function is=REPT("*",10.43), which returns**********. - Format the cells as text to display the charts correctly. The final result is shown below:
| Product | Region | Sales | Chart |
|---|---|---|---|
| A | North | 120 | ********** |
| A | South | 80 | ******** |
| A | East | 100 | ********* |
| A | West | 90 | ********* |
| B | North | 150 | ************* |
| B | South | 110 | *********** |
| B | East | 130 | ************ |
| B | West | 140 | ************* |
| C | North | 180 | **************** |
| C | South | 160 | **************** |
| C | East | 170 | **************** |
| C | West | 190 | ***************** |
| D | North | 200 | ****************** |
| D | South | 210 | ******************* |
| D | East | 230 | ******************** |
| D | West | 220 | ******************* |
Other approaches
There are other ways to use the REPT function to show data in graphic mode, such as:
- Using conditional formatting to change the color of the characters based on the data values.
- Using different characters for different categories of data, such as
+for positive values and-for negative values. - Using the CHAR function to create custom characters, such as
=CHAR(219)for a solid block or=CHAR(176)for a shaded block. - Using the SPARKLINE function to create mini charts in a single cell.
