Excel tip: Instant in-cell graphs with worksheet formulas

Excel In Cell Charts aren’t actually charts at all, insofar as you won’t find them in the Charts menu in Excel.

They’re actually a formula that you can insert in any empty cell, like you see in column C below:

Example file : In-Cell-Graphs-Examples.xls

In-Cell Bar Charts

Excel in cell chart

Yes, that is a formula.

The secret to an In Cell Chart is the REPT function. The following formula is in cell C4:

=REPT("|",B4)

When you format the pipe symbol (|) the right way you get a nice smooth bar.

I like to use Script font, Bold and 9pt, but you can play around with different font types to get different effects.

Scaling In Cell Charts

If you find your bar is too big to fit in the cell you can change the scale of your values by dividing them in half, or by 100, or 1000 etc. depending on their size.

=REPT(“|”,INT(B4/100))

Note: The INT function rounds the value down to the nearest integer.

For example, the data below is in the thousands but the bar still fits nicely in column C. See the formula in the formula bar.

Excel in cell chart

Likewise, you can increase the scale by multiplying the value in column B by 2, or 10, or 100 etc.

How to handle #N/A error values to make charts look more presentable

In-Cell Dot Plots

In-Cell-Graphs

The configuration required to make these in-cell dot plots are as follows:

Dot Plot 1 (Column C)

Formula: =REPT(" ",B2-1)&"o"
Font: Courier New

The formula means “Repeat the space character as many times as the value in B2 minus one, then the letter ‘o'”.
Enter the formula in cell C2 and fill it down to C9.

Dot Plot 2 (Column D)

Formula: =REPT(" ",B2-1)&"l"
Font: Wingdings

The formula means “Repeat the space character as many times as the value in B2 minus one, then the letter ‘l’ (or in Wingdings, ‘l‘).
Enter the formula in cell D2 and fill it down to D9.

You can also use a variety of ASCII characters to create some interesting bar types. Simply replace the “|” pipe symbol with an ASCII code. This is accomplished by holding the ALT key and typing in the applicable code number, i.e. ALT-254 (make sure to place this code in double quotes just like the pipe symbol.) Using a standard font like Arial or Calibri tends to work best.

EXAMPLES:

ALT-177 ▒ ▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒▒
ALT-178 ▓ ▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓▓
ALT-219 █ ███████████████
ALT-220 ▄ ▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄▄
ALT-221 ▌ ▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌▌
ALT-222 ▐ ▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐▐
ALT-223 ▀ ▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀▀
ALT-240 ≡ ≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡≡
ALT-247 ≈ ≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈≈
ALT-251 √ √√√√√√√√√√√√√√√√√√√√√√
ALT-254 ■ ■■■■■■■■■■■■■■■■■■■■■■■■

BONUS: If you really want to make the bars change colors based on their lengths (ex: short bars RED, medium bars YELLOW, long bars GREEN), apply a conditional format logic to the bars.

EXAMPLE:

Suppose all the data is between 0 and 10 and you wish to apply the following color designations (first bar resides in cell A2):

Numbers less than or equal to 3 = RED
Numbers greater than 3 but less than 7 = YELLOW
Numbers greater than or equal to 7 = GREEN

Set the Conditional Format to “Formula Is” (2003 or earlier)
or
Set the Conditional Format to “Use a formula to determine which cells to format” (2007 or later)

“=A2< =3″ (set to RED)
“=AND(A2>3,A2<7)” (set to YELLOW)
“=A2>7″ (set to GREEN)

In Cell Charts are a handy tool to use in Excel Dashboard reports as they assist the reader in quickly understanding your data and you can format them quite small.

10 Simple Tips To Make Your Excel Charts Sexier

Conditionally Formatted In-Cell Charts

In-Cell-Graph_Conditional_Formatting

You can use worksheet conditional formatting to highlight certain values in different colors. For example, in column C, suppose you want values less than 10 to appear red, and those 20 and greater to appear green. Select C2:C17, then choose Conditional Formatting from the Format menu. For Condition 1, select Formula Is in the dropdown, and enter the formula =$B2<10. Then click the Format button, and on the Font tab, change the color to red. Click the Add>> button, and for Condition 2, select Formula Is in the dropdown, and enter the formula =$B2>=20. Then click the Format button, and on the Font tab, change the color to blue. Press Enter, and you’ve got a conditional chart.

You can produce a conditional chart with different markers with an expanded formula. The dot plot in column C can be changed to show “x” for numbers less than 10 and “+” for numbers at least 20 by using this formula in cell C2 and filling it down

=REPT(" ",B2-1)&IF(B2<10,"x",IF(B2>=20,"+","o"))

Naturally you could combine both approaches.

Example file : In-Cell-Graphs-Examples.xls

Also read


Courtesy: myonlinetraininghub.com


1

2 Responses

Show all responses
  1. AbdelRaouf
    October 29, 2016
    • Raghu R
      October 31, 2016

Leave a Reply