Up next in 10
Creating Text-Based Histograms with the REPT Function in Excel
2K views
Nov 7, 2024
Discover how to easily create simple histograms directly in Excel using the REPT function. This approach allows you to visualize your data in a clear, text-based format without needing charts. It’s a straightforward way to see how often certain values occur and understand your data better.
View Video Transcript
0:00
hello everyone in this video I'll be
0:02
showing you how to create a text
0:03
histogram in Excel without using any
0:05
charts we'll visualize budget versus
0:08
spining using simple text characters
0:10
let's get started first let's talk about
0:12
the re function which stands for repeat
0:15
this function allows us to repeat a
0:17
given character or string a specified
0:19
number of times the syntax is quite
0:21
simple you start by entering the text
0:23
you want to repeat as the first
0:25
parameter and in the second parameter
0:27
you specify the number of times you want
0:28
that text to be repeated as you can see
0:31
the RP function is versatile and can be
0:33
used to create simple visual
0:34
representations using text characters
0:37
now that we understand the RP function
0:39
let's enter our sample data in column A
0:42
I'll list the months in column B I'll
0:44
enter the budgeted amounts and in column
0:46
C I'll input the actual amount spent
0:49
next we'll calculate the percentage
0:51
difference between the budget and the
0:52
amount spent in column D to start we'll
0:55
enter our formula and sell D2 this
0:57
formula calculates the percentage of the
0:59
amount spent relative to the budget
1:01
we'll first determine how much we are
1:02
over or under budget in percentage terms
1:05
by using the formula that compares the
1:06
difference between the budget and the
1:08
spin amount to ensure Clarity and
1:10
improve readability we use the round
1:12
function to eliminate any decimal points
1:14
in our final result once we have the
1:16
formula set up I'll simply drag the fill
1:18
handle down to apply it to all the rows
1:20
and column D giving us a complete
1:22
overview of the percentage differences
1:24
for each month now let's set up our
1:26
columns for the histograms in column H
1:29
I'll write the heading under budget in
1:30
column I I'll write month and in column
1:33
J I'll write over budget in column H
1:36
I'll use the formula shown on the screen
1:37
to create a histogram for under budget
1:39
months applying it from H2 to h13 for
1:42
all months this formula uses the IF
1:45
function to check if the value of
1:46
percentage is positive indicating that
1:48
the spending is under budget if the
1:50
condition is met the formula executes
1:52
the re function which repeats the
1:54
asteris character as many times as the
1:56
value in percentage cell this visually
1:59
represents how much under budget you are
2:01
if the condition is not met the formula
2:03
returns an empty string which means
2:05
nothing will be displayed this formula
2:07
creates a text histogram for under
2:09
budget months using asterisks to show
2:11
spending below the budget next I'll
2:13
select the range H2 to h13 and align it
2:16
to the right for better visibility in
2:18
columi I'll enter the month names from
2:20
January to December and Center align
2:22
them for better presentation in column J
2:25
I'll use a similar formula this formula
2:27
checks if the percentage value is
2:28
negative indic ating that spending has
2:30
exceeded the budget after entering the
2:32
formula I'll drag it down to j13 however
2:36
we encounter a value error in cells
2:38
where the condition is met to resolve
2:40
this I'll modify the formula to use the
2:42
ABS function which Returns the absolute
2:45
value of a number effectively removing
2:47
any negative sign now we have our two
2:49
histograms set up one displaying
2:51
positive values for under budget months
2:53
and the other showing negative values
2:55
for over budget months next to enhance
2:58
the visual appeal I'll color the under
3:00
budget column green and the over budget
3:01
column red now I'll replace the asterisk
3:04
in the formulas with the character L for
3:06
a more refined bar chart look this gives
3:08
us a cleaner bar representation lastly
3:11
I'll replace the L with the black Square
3:13
to give it a solid appearance we've
3:15
successfully created a text histogram
3:17
without inserting any charts in Excel
3:19
this method not only helps us visualize
3:21
our budget versus spending but also
3:23
allows for easy customization if you
3:25
found this tutorial helpful please give
3:27
it a thumbs up and subscribe for more
3:29
Excel tip and tricks thank you for
3:31
watching and see you in the next
3:32
tutorial
#Business & Industrial
#Financial Planning & Management