Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

In Cell Bar Chart With REPT Function

In-cell charts are awesome.

And today, I’ll show you how to create an in-cell bar chart with rept function.

But, do they really worth your time?

Yes.

Here is the single important reason:

how to create an bar chart with rept function

They are easy to create and simple to use.

Ahead, in this post, I’ll show a step by step process to create a decent bar chart with rept function.

But Do You Know About REPT Function?

Here is a simple definition.

You can use REPT Function to repeat a specific text for a specific number of times.

Syntax

REPT(text, number_times)

The main use of REPT function is for creating in-cell charts.

Steps To Create Bar Chart With REPT Function

I have a mark sheet of students & I want to add a bar in the performance cell next to the marks.

how to create an bar chart with rept function marks table

Here are the steps.

how to create an bar chart with rept function insert option buttons

  • Name both of the buttons “Only Percentage” & “Bars”. And, link then to a cell. (I have linked then to cell A1)

how to create an bar chart with rept function link option buttons

  • Now, come back to your data table & insert the following formula into the cell.
    • =IF(A$1=1,REPT(“|”,B3)&” “&TEXT(B3/100,”0%”),B3/100)
    • Make sure to change font style to “PlayBill”.
  • Drag the formula to the entire table & your bar chart is ready.

how to create an bar chart with rept function ready table

I have used conditional formatting to highlight bar for the student whose marks are below 40.

How does this Formula work?

In this formula, I have used IF function for a conditional formula.

When you select options button “Bars” you’ll get 1 in cell A1 then rept function will show you a bar & percentage in the cell.

And, when you select “Only Percentage” it will show just percentage.

Sample File

Conclusion

By using this technique you can fire up your boring data.

If you compare both of the tables (with or without bars), you’ll notice that by using bars you can present your data in an understandable way.

Do you like this tip?

Share your view in the comment box. And, if you have any idea to create an in-cell chart then please share with me.

What’s Next

You can also try following things with rept function.



  • Dhananjay Jadhav

    I gone through the structure of the formula. I was unaware with rept function and also innovative & efficient use of radio button. This is extreamly helpful to interpret the huge data quickly and without burning much of your laptop RAM.
    Thanks Puneet Gupta for this excellent trick.