Create In-Cell Charts with the REPT Function in Excel

[@faisalkb]

Applied this technique in my today's MIS, it's awesome, Was not able to download Playbill font due to system restrictions. But found Nyala font which will also give similar bar. It's a preloaded windows font. Thanks for the video ...

[@Computergaga]

Awesome!! Thank you for the suggestion. A few people have commented about other possible fonts.

[@colsoyeti1]

Thanks for the how to use the REPT function. just what I needed to help break away from the traditional bar charts.

[@Computergaga]

My pleasure.

[@jmathew6988]

This is an ingegenious trick. It solved my problem. Saw here a couple of other tutorials who have copied this idea as their own. I know yours is original because it's the oldest one. Thank you.

[@Computergaga]

Thank you 😊

[@wayneedmondson1065]

Hi Alan.. this is a super tip.. love it! Thanks for the reference to Playbill font.. I've locked that one into my brain. Also, the conditional formatting trick is a great idea. I used MIN and MAX to produce the highlights. On the star chart.. here is a tip I discovered: =UNICHAR(9733) will show a black star regardless of the font designation of the cell. You can't use UNICHAR() in REPT, but you can F9+ENTER it into a cell and then copy and paste it between quotes in REPT as in: =REPT("★",ROUND(H5,0)). In this way, no font change is needed to display the star. Other UNICHAR codes could be used, as well. Thanks again.. really love these tips.. super useful and will be using them right away in my work. Thumbs up!!

[@Computergaga]

Thank you Wayne. And great tips, thank you.

[@christopherlawes6081]

I found I could use unichar in rept "=REPT(UNICHAR(9733),REF)". Maybe version specific? I'm using Excel 2016. Thanks

[@wayneedmondson1065]

@@christopherlawes6081 Hi Christopher.. you are indeed correct. I think when I tried I forgot to input the number_times argument of REPT(text, number_times) and so got an error. Nice to know another way to do it. Thanks for the tip. Thumbs up!

[@josephcoon5809]

7:00 I would set the REPT() text to a highlighted cell that is absolutely referenced so that I can change the character used by changing what’s in the referenced cell, rather than editing the function itself. If you want to take it a bit further, make a list of characters and reference it for a drop down list above the chart column.

[@rickkeev4919]

Great Vid again! For my numbers I like dividing by a certain number so the pseudo chart fits into the cell column width. Like =REPT("|",B2/7). The bigger the divisor the smaller the pseudo chart

[@Computergaga]

Thank you RickK. Sounds like a good idea especially for large values.

[@brightdaysaheadofUs]

Whats the letter inside the quotation mark?

[@deanontheinterwebs]

@@brightdaysaheadofUs The "pipe" symbol, which is mentioned too in the video, plus what font (Playbill) allows display with super tight kerning so it looks like a solid bar.

[@georgemensah2292]

Whoa! I feel like I can do so much more with this visual. Thanks so much for sharing.

[@Computergaga]

You're very welcome. Thank you George.

[@Lomaxient]

Wow, that was awesome! Wasn't even looking at Excel today, just stumbled over this.

[@ten5039]

Lol, same.

[@Computergaga]

Thank you Lomax.

[@suyashG1x]

I m blown away by this...this was very amazing and useful. This is so simple but yet I couldn't have ever imagined this was possible in excel

[@Computergaga]

Awesome!! Thank you Suyash.

[@mohitjain5632]

This is very useful to standout in making daily office worksheets. The content of your videos are understandable. I appreciate your efforts.

[@Computergaga]

Thank you Mohit.

[@sakibrahman6019]

This is called creativity. Loved it !

[@Computergaga]

Thank you very much Sakib.

[@hafizgeain4170]

Thanks! Much easier and customizable options compared to existing conditional formatting..

[@Computergaga]

Absolutely Hafiz.

[@Sandhu_051]

Sir, superb technique 👍🏻💐 But, after open inverted commas how to type this symbol

[@Computergaga]

Thank you! I copied the star symbol from cell J3 into the quotes. Because of the font, it is displayed as

[@samuelkodjoe1645]

that was powerful!!!....thank you very.....going to make some improvement right away!!!

[@Computergaga]

Thank you very much Samuel.

[@abdelhafidassadiki2124]

Trucs very utils & exclusif s.. Bravoooo teacher

[@EricaDyson]

Really useful. Thanks a bundle!

[@Computergaga]

My pleasure Erica. Thank you.

[@njvanheerden2157]

Very nice. Thanks for sharing

[@Computergaga]

You're very welcome.

[@ICIVALMI]

It is very relaxing and educational listening to you. Never thought such a combination would even exist. :)

[@Computergaga]

You're very kind. Thank you.

[@mandalorian66]

Thanks @Computergaga, Question: how do i change the color of the stars based on the value

[@Computergaga]

You can use Conditional Formatting like I do with the bars at 03:28 but with the criteria you desire for the stars.

[@SiddharthNagwekar]

Amazing technique. Something new! Thank you for sharing this technique with us.

[@Computergaga]

You're welcome. Thank you Siddharth.

[@NikolaKralj]

Great video! but can you make that 3.5 represents 3 and a half of a star??

[@Computergaga]

Thank you. If you can find the icon/symbol/emoji then you can show anything for any criteria.

[@marif7623]

such a good explanation, thank you so much, definitely you are an excellent teacher

[@Computergaga]

Thank you very much 👍

[@GeorgeAJululian]

thank you it was very helpful

[@Computergaga]

Great to hear 👍 You're welcome, George.

[@spuneet156]

New for me, thanks I will use it in my future reports and resume

[@Computergaga]

You're welcome.

[@88888cross]

This is great, is the bar graph target automated to be 💯 with this method ? Please advise, thank

[@markviana4960]

Thanks for sharing. I appreciate your time in this video.

[@Computergaga]

My pleasure Mark.

[@solarkitty9298]

Thank you so much! Your video saved me so much!

[@Computergaga]

Excellent! You're welcome Shad.

[@drkash81]

Wow, awesome technique. Thanks a lot for sharing this

[@Computergaga]

You're very welcome.

[@hudaanwer1303]

It is really great, Thank you

[@Computergaga]

You're welcome. Thank you.

[@anandsharma4627]

Wow...this is absolutely brilliant....sir...you are amazing. Thank you so much for the video

[@Computergaga]

You're very welcome Anand. Thank you.

[@214Wildbill]

Absolutely fantastic!!! I wasn't able to use playfill I didn't have that. I used min+10&cell for the fill so I could see the end result There is suppose to be a site I can download it, Thanks computer Gaga

[@Computergaga]

Thank you. Yes there are sites where you can download fonts. The Stencil font works also.

[@chinthakadiwelwatta27]

Thank you very much

[@Computergaga]

You're welcome. Thank you for watching.

[@JamesBond-qg5gx]

Very simple and very useful, thanks a lot 👍

[@Computergaga]

You're very welcome. Thank you James.

[@gourav2711]

You can do the first one by simply using data bars from conditional formatting

[@Computergaga]

You sure can, but you cannot add the Conditional Formatting extra that we did in the video.

[@rina1592]

Sir, this will be very useful for me.......................thanks in tons.

[@Computergaga]

You're welcome Munaf. Thank you.

[@majidsiddique8227]

Excellent

[@Computergaga]

Thank you Majid.

[@PaulaWeb3andCrypto]

Very creative Alan, nice one 😉

[@Computergaga]

Thank you Paula.

[@sircorn4248]

Excellent video. Many thanks

[@Computergaga]

Thank you Sir Corn.

[@user-lp1gy6wp8z]

For the star, you can use use the star in the unicode instead

[@Computergaga]

Excellent. Thank you.

[@macrofitmealprep1502]

Very Informative!! Thank you, sir!

[@Computergaga]

You're very welcome.

[@sasavienne]

Respect. Wow wow wow.. 🌟 🌟 That is powerful.. 🌟 🌟 🌟 Thanks Alan. 👍

[@Computergaga]

You're very welcome Salim.

[@Rubbernecker]

Excellent video and very informative.

[@Computergaga]

Thank you Scott.

[@k.chriscaldwell4141]

Just superb! Thanks!

[@Computergaga]

Thank you Chris.

[@stefandunker6276]

Thank you, very helpful.

[@Computergaga]

Thank you Stefan.

[@m.raedallulu4166]

Useful as usual, and I still live your accent !

[@Computergaga]

Thank you.

[@a.m.fahimahmedth53]

When I changed the font to "playbill" the bar doesn't look as a solid bar rather gap is visible in between the "I". Any suggestions plz? It is really fruitful

[@gregoryunderwood4121]

Brilliant!!! Thank you!!💚

[@Computergaga]

You're welcome. Thank you.

[@nicolaeb3942]

Very cool! Thank you

[@Computergaga]

You're very welcome Nicolae. Thank you.

[@rupomExcel]

Awesome!

[@Computergaga]

Thank you 😊

[@sidsawant]

Wow Sir Great

[@Computergaga]

Thank you very much.

[@minhvuongluu7644]

Such a cool idea

[@Computergaga]

Thank you Minh Vuong Luu.

[@basharibrahim6795]

Excellent function.. and a smart tip.. how can it be applied to a time cell?

[@Computergaga]

Thank you Bashar. I don't know how, or why you want it applied to a time cell. But the time is a fraction of a day so 12:00 is 0.5. We could multiple this value by 10 or 100 to create a bar or icon significant enough to chart.

[@abuzdream]

Thank you for sharing sir, Appreciated

[@Computergaga]

My pleasure. Thank you Abu.

[@taryn4080]

5:58 for star part

[@muralipk1964]

Thank You!

[@Computergaga]

You're welcome.

[@generalflights3241]

Perfect and thanks again

[@Computergaga]

You're very welcome.

[@tahirhanif9669]

Fantastic, thanks for this 👍

[@Computergaga]

You're very welcome Tahir.

[@jonesdinale2432]

Afternoon. Kindly assist Plainbill does not change the to chart.

[@Computergaga]

Not sure why this is. Try experimenting with other fonts, but the font is the font, it should work the same.

[@zxcvbnmmasdfghjkl]

How can you use this for example monthly trending ? So for the score example where you have month 1 score and then month 2 scores

[@laithhameedalazawy]

Great video thanks

[@Computergaga]

You're welcome Laith.

[@chandrasegaran353]

Very informative 👍

[@Computergaga]

Thank you very much Chandra.

[@ranieri9409]

Super! Grazie mille! But what about Microsoft Excell Online? It has very few fonts 😥😥

[@Computergaga]

Thank you. Ah we may need a different technique with the online with the fonts don't render correctly. There may be an alternative though.

[@ranieri9409]

@@Computergaga I menaged to did it. Just clicked "open with excell in desktop" and than... ta-daaa LOL thank you!

[@afzalzamiransari]

Excellent mind blowing Sir. Great

[@Computergaga]

Thank you Afzal.

[@igorristovski1309]

Nice one. You can do it also with conditional formating with Bar option. Much faster and simple.

[@Computergaga]

Thank you Igor. The cool thing here is that we can add the automatic colour change of the bars on any criteria we want. The Conditional Formatting data bars do not offer that functionality.

[@igorristovski1309]

@@Computergaga I realized that and that's why I liked this one. Very useful especially with reasonable numbers. Thank you 👌

[@Dev_Bartwal]

very use ful video thanks

[@Computergaga]

My pleasure.

[@MarcSchreiberRealtor]

Awesome. Is there a way to use the IF statement and join other symbols (“”&””) to add a half star or quarter etc to make the visual more accurate?

[@Computergaga]

Sure. If you can find symbols for these, you can use any symbol and formula to construct the visual.

[@himanshupatel1601]

Hi, Rept function for in cell chart giving error if there is involvement of negative values...

[@Computergaga]

Yes, the REPT function only handles positive values.

[@robertreed4337]

I wish I'd have found this site earlier in my life instead of having to carry around that unwieldy Abacus

[@Computergaga]

😂 Happy to help.

[@SaniGarba]

Thank you.

[@Computergaga]

You're very welcome Sani.

[@klikinfotech7731]

cool trick, thanks for this

[@Computergaga]

You're welcome. Thank you.

[@victoreduardoplataolivares7092]

Hello, thank you for sharing, I found this very useful, I was trying to use the function but I found out that I do not have the same font that you are using, playbill, i believe is the one. I tried to download it from the web but it seems its not working on my mac fonts. Is there any other font that I can use or other way to make it work? Thanks

[@Computergaga]

Thank you. There is the Stencil font too.

[@pravatlife115]

Is the 3.55 ,exactly can show 3.5 stars ,is it possible without rounding to 4 stars

[@Computergaga]

For sure, but you would need a symbol that is half a star. In this video, I'm using full-star symbols, so no. But with half-star or quarter-start symbols, you can adapt the formula to show the appropriate symbol/star. It definitely could be done.

[@Luciano_mp]

Nice, thanks.

[@Computergaga]

Thank you Luciano.

[@HeartAbyss]

What if the score has negative values?

[@wonderfulnepalwow]

Just Great video

[@Computergaga]

Thank you.

[@sitifairuz206]

How do i make change to the bar value? my biggest value is 5 . but the bar look so small

[@Computergaga]

With the REPT function, multiply the number by a large number i.e. 10. So the value is still 5, but the bar charts 50. Do it for each bar.

[@Robert8455]

Nice tip

[@Computergaga]

Thank you Robert.

[@markvalenzuela7010]

Hi! I'm Trying to get the Nearest Sum from the Target using Excel Solver, For Example, i have Shipments A to D that holds a value, what i want to get is the combination of all the shipments based on the target Value which is 200, in this case, the constraint is not met because there are no way to get the value of 200 if we sum the shipments, what i want to get is the Nearest sum from the target, in this case. 155, the result will be (Shipments - A,B,D), is there a way we can get the nearest sum of combination using Excel solver? A - 50 B - 50 C - 50 D - 55 Thank you so much!

[@BiancaAguglia]

Creative and useful. 😊 One question, please: is there a way to get partial stars for the second chart (i.e. rounding 3.55 to 3.6 instead of 4)?

[@Computergaga]

Thank you Bianca. Not sure on the partial stars unless there are emojis showing that detail.

[@christopherlawes6081]

Seems to be a limitation. One way round it is to multiply data to whole numbers and plot them e.g 3, 3.55, 3.8 becomes (x100) 300, 355, 380.

[@BiancaAguglia]

@@christopherlawes6081 That's a creative solution. 😊

[@romeshkirpalani848]

How!!! wonderful

[@Computergaga]

Thank you Romesh.

[@kapilg89]

Can't we use sparkline today to do this?

[@Computergaga]

Some of it, yes you are right. They cannot be customised in the way that we added the conditional colouring to the bars though.

[@StephenNu9]

Very good. Someone is burning the midnight oil with Excel.

[@Computergaga]

Living the dream 😊

[@respectedraja]

Can we use half star as well for 1.5 or 2.5

[@Computergaga]

Sure, if you can find a half star icon to use in the REPT function.

[@mohamadfawaz1928]

keep it up

[@Computergaga]

Will do. Thank you Mohamad.

[@hariprasanthh2600]

Thanks sir

[@Computergaga]

You're welcome Hariprasanth.

[@tanujsharma2812]

I want to graph the change in a cell value , please help

[@Computergaga]

Hi Tanuj, can you be more specific? You can reach out to me via email.

[@aman5909]

What formula can use pl. Reply In row 1,,, digit is 783 now what formula can use for this ..... 7+8+3=18 now if total is 12 or 6 then ture and if not 12 or 6 than false

[@ayyazbutt1937]

its not working i apply =rept(''|'', and the cel num but its not wrking

[@a2bt456]

you have got an amazing brain🧠🧠🧠

[@Computergaga]

Thank you very much 😊

[@a2bt456]

@@Computergaga its my pleasure sir

[@robertmaluka2763]

Is there another font I can use. I would like to do this in google drive. I have been looking for something like this.

[@Computergaga]

I'm not sure Robert. Will need to pay around with some other fonts and see what looks good. See if there are alternatives to Playbill that look good. Ensure the font is bold also.

[@christopherlawes6081]

Best I could find was install "Zilla Slab Highlight" and using text such as "-" (hyphen)

[@rishom1441]

Under wich excel version does that works

[@Computergaga]

This will work under any version.

[@easyinvesting3730]

I do not find the sign "l" after rept...please help

[@Computergaga]

The sign is called the pipe. On my keyboard it is above the \. It may be somewhere else on yours.

[@tonnynak]

Pls mention excel version too so i can practice

[@Computergaga]

Hi Saptarshi, this video is done using Excel 365 Pro, but the techniques shown can be done on any version.

[@AzwanGP]

NICE ! but what if the other guys don't have that playbill font?

[@Computergaga]

Thank you. Other fonts can also be used. You can play around with this.

[@faisalkb]

You can use Nyala instead. It's a preloaded font

[@stephenstephen8660]

This apparently only works for small numbers. I tried for numbers greater than 50,000 and i got #VALUE error messages for all of them. When I changed to the small numbers in this video, it worked.

[@Computergaga]

Sure. A solution to this could be to reduce the number for the visual by dividing them by a number. For example, =REPT("|",B2/1000)

[@stephenstephen8660]

Computergaga Thanks!

[@Shampoolegend]

love the accent

[@Computergaga]

Thank you Ibrahem.

[@jagadeeshd4737]

Where is the pipe in the keyboard, please help im not finding here

[@Computergaga]

On a UK keyboard it is on the same key as the backslash. Different language keyboards can position characters in different positions though.

[@syedmoiz9330]

This table represents what values?

[@Computergaga]

Whatever you want them to represent.

[@compscript7973]

You are an Excel version of Bob Ross.

[@knwrpl]

How can I label those bars which we create

[@Computergaga]

You can use something like =REPT("|",B2)&" "&B2 to add the value on the end as a label. You can use any label you want by referencing the cell value.

[@christopherlawes6081]

Or just simply put the column with the labels next to the column with the "bars"