In this article, you will learn how to use the REPT function in Excel to create simple and effective in-cell charts. The REPT function repeats a text string a specified number of times, and can be used to display data visually by using characters such as bars, dots, or stars. You will also learn the basic theory behind the REPT function, the steps to create a REPT chart, and some alternative methods to achieve the same result.
The REPT function has the following syntax:
= REPT (text, number_times)
where:
- text is the text string that you want to repeat, such as a single character or a combination of characters.
- number_times is the number of times that you want to repeat the text string. It can be a positive integer, a cell reference, or a formula that returns a positive integer.
The REPT function returns a text value that contains the repeated text string. For example, =REPT(“x”,5) returns “xxxxx”.
The REPT function can be used to create in-cell charts by using characters that represent different values or categories of data. For example, you can use the pipe character “|” to create a simple bar chart, or the star character “*” to create a star rating chart. The number_times argument can be based on the data that you want to display, such as sales, ratings, or percentages.
Steps to Create a REPT Chart
To create a REPT chart in Excel, you need to follow these steps:
- Prepare your data in a table, with one column for the labels and one column for the values. For example, you can use the following table to create a bar chart of sales by region:
| Region | Sales |
|---|---|
| North | 120 |
| South | 150 |
| East | 100 |
| West | 80 |
- In a new column, enter the REPT function with the text string and the number_times arguments. For example, to create a bar chart with the pipe character “|”, you can use the following formula in cell C2 and copy it down:
= REPT (“|”, B2/10)
This formula divides the sales value by 10 to scale the bars, and repeats the pipe character according to the result. You can adjust the scaling factor to suit your data range and desired chart size.
- Format the cells with the REPT function as text, and adjust the column width and alignment as needed. You can also apply conditional formatting to change the color of the bars based on the values. For example, you can use the following settings to create a green-red color scale:
- Select the cells with the REPT function (C2:C5 in this example).
- On the Home tab, click Conditional Formatting > Color Scales > More Rules.
- In the New Formatting Rule dialog box, select Format all cells based on their values.
- Under Format Style, select 2-Color Scale.
- Under Minimum, select Type: Number and Value: 0. Click the color box and choose green.
- Under Maximum, select Type: Number and Value: 150. Click the color box and choose red.
- Click OK to apply the rule.
- You can optionally add a chart title and axis labels by using text boxes or cells. For example, you can use cell A1 to enter the chart title “Sales by Region”, and use cells B1 and C1 to enter the axis labels “Region” and “Sales”.
Result
The result of the above steps is a REPT chart that looks like this:
| Sales by Region | ||
|---|---|---|
| Region | Sales | Chart |
| North | 120 | <span style=“color:green”> |
| South | 150 | <span style=“color:red”> |
| East | 100 | <span style=“color:green”> |
| West | 80 | <span style=“color:green”> |
Alternative Methods
There are some other ways to create in-cell charts in Excel, such as:
- Using the SPARKLINE function, which creates mini charts in a single cell. You can choose from different chart types, such as line, column, or win-loss. For example, to create a column sparkline based on the sales data, you can use the following formula in cell D2 and copy it down:
= SPARKLINE (B2, {“charttype”,“column”})
- Using the CHAR function, which returns a character based on a numeric code. You can use this function to create custom symbols or icons for your charts. For example, to create a star rating chart based on the ratings data, you can use the following formula in cell C2 and copy it down:
= REPT (CHAR (9733), B2) & REPT (CHAR (9734), 5-B2)
This formula repeats the solid star character (code 9733) according to the rating value, and repeats the empty star character (code 9734) according to the remaining value. The result is a text string that looks like a star rating, such as “★★★☆☆”.
