When you are creating an Excel Dashboard and are limited by space and do not want to insert a chart, you can easily create an in-cell bar chart using the RPT (repeat) function.

The RPT function uses the vertical bar character | as the first argument: text and references the value cell for the second argument: number_times

So it enters the vertical bar character by the amount of times of the value cell, looking something like this:


Here is how it is done in just a few steps:


STEP 1: Enter the REPT function in a column next to your values


STEP 2: Enter the vertical bar keyboard character in the first argument =REPT(“|”)


STEP 3: Reference the value cell for the second argument =REPT(“|”a1)

STEP 4: Highlight the formula column and insert the Stencil font from the Home menu and choose a font color

STEP 5: If your value cells are high, the bar will go out of your screen.  To fix this, you need to enter a divisor in the second argument of your formula which will reduce the length eg =REPT(“|”,a1/5)


REPT Function Chart



If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Stacked Column Chart: Compare Contributions The Stacked Column chart is used in Excel to compare the contribution of a value to a Total.For example, if you want to show the North & South sales for the last 5 years then this is the chart for you as I explain below.NB: If you are using Years as your first column ...
Summarize Data With Dynamic Subtotals What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...
Vlookup in an Excel Table What does it do?Searches for a value in the first column of a table array and returns a value in the same row from another column (to the right) in the table array.Formula breakdown:=VLOOKUP(lookup_value, table_array, col_index_num, )What it means:=VLOOKUP(thi...
Advanced SUMPRODUCT Function: Conditional Date If you want to find out the total sales for a particular month, then the SUMPRODUCT function is your answer.  You can create a criteria for a specific date range, a particular month or a year.In the example below I show you how to use the SUMPRODUCT function to sum up the tot...