Create in-cell chart with Conditional Formatting in #Excel

Have you ever seen an Excel sheet with in-cell bars to visualize the data like the ones below, and wonder how they are made?

Believe it or not, you can do it in one less than a minute with Conditional Formatting.

Let’s watch it in action:

 

Please continue to read this post if you prefer reading to watching.

You may download a Sample File to follow along.

Here’s the step by step instruction.

  1. select the range of data of interest
  2. Home tab –> Conditional Formatting
  3. Data Bars
  4. Select any one of the options you prefer (Tip: hover your mouse to see preview)

Excel Tip - Create in-cell bar with conditional formatting

DONE!  This is the result.

Excel Tip - Create in-cell bar with conditional formatting_0

Can’t believe it’s so simple?!  😁

As you see from the above result, the bar will inevitably overlay on (some) numbers as they share the same cell.  To avoid that, we may place the bar onto the next column, like this:

Excel Tip - Create in-cell bar with conditional formatting_8

To achieve this, we need to duplicate (link) the values to the cells next to it.

In D3, input the formula

=C3 'Copy down

Excel Tip - Create in-cell bar with conditional formatting_2

On the “duplicated” values, apply the same steps before

Excel Tip - Create in-cell bar with conditional formatting_3

Apply extra steps to make the values invisible

Select the range of data with the data bar.

  1. Go to Home tab –> Conditional Formatting
  2. Mange Rules…

Excel Tip - Create in-cell bar with conditional formatting_4

When the Conditional Formatting Rules Manager shows up,

  1. Edit Rule…
  2. OK

Excel Tip - Create in-cell bar with conditional formatting_5

Then,

  1. Check the “Show Bar Only”
  2. OK

Excel Tip - Create in-cell bar with conditional formatting_6

And OK.

Excel Tip - Create in-cell bar with conditional formatting_6.1

And here we go:

Excel Tip - Create in-cell bar with conditional formatting_8

As simple as this. 🙂

Tip:

We may have more options on the filled colors of the bar on the step “Edit Formatting Rule”.  Try explore it.

Excel Tip - Create in-cell bar with conditional formatting_7

Extra tip: Adjust the column width for different bar length.

Conditional Formatting is a cool feature in Excel to visualize data in an easy way.  When used properly, your data stand out.  However when overused, it may create noise rather than insight.  Use it wisely. ;p

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.