2 Ways to Apply Conditional Formatting to Shapes (Video Tutorial) - Excel Campus
22

2 Ways to Apply Conditional Formatting to Shapes (Video Tutorial)

Bottom line: Learn a few ways to apply conditional formatting to shapes.  This is a great technique for dashboards and interactive reports where you don’t want to be confined by the worksheet grid.

Skill level: Beginner

Create Shapes that Contain Conditional Formatting in Excel

Video Tutorial on Conditionally Formatting  Shapes

In the video below I demonstrate a few ways to create shapes that can be conditionally formatted as values change in the cells in the spreadsheet.

Additional Resources:

Download the File

Download the file I used in the video.

Conditional Format Shapes.xlsx (38.7 KB)

Can Shapes be Controlled by Conditional Formatting?

This was a great question submitted by Helen.  Unfortunately there is no direct way to apply conditional formatting to a shape in Excel.

However, there are a few simple workarounds that can get the job done.

Method #1: Copy and Paste a Linked Picture

This is a pretty simple solution that will create a picture (shape) of the cell that contains conditional formatting.  The linked picture can be moved and re-sized, just like any shape in Excel.

Create Shape with Conditional Formatting in Excel GIF

Watch the screencast in your browser

The screencast animation above shows how to create the conditionally formatted shape.  Here are the steps:

  1. Copy the cell that contains conditional formatting.  This can be a single cell or a range of cells.
  2. Paste the cell as a Linked Picture.  This option can be found on the Home tab of the Ribbon or the right-click menu.
  3. The Linked Picture creates a shape.  You can move and resize this shape just like any other shape in Excel.
  4. Any formatting changes that are made to the cell will be automatically reflected in the linked picture.

The linked picture will automatically update as changes are made to the cell.  Typically the conditional formatting is controlled by a cell’s value.  So any changes made to the value of the control cell will automatically change the cell’s formatting, and those changes will also be reflected in the shape.

Create Shapes with Icons and Data Bars Conditional Formatting

There are some limitations to this method…

Here are a few of the limitations or important things to know.  I discuss these further in the video above.

  1. The linked picture takes on all formatting of the cell, including the gridlines.  You can turn the gridlines off from the View tab on the Ribbon.  Keyboard shortcut to toggle gridlines is Alt, W, V, G
  2. The linked picture shape is a rectangle.  This cannot really be changed from the menus in Excel.  You can use the Picture Styles menu and shape properties to change some aspects of the shape.  I was able to get it to look like a circle, as you can see in the screenshot below.  The shape is still actually a rectangle, but looks like a circle/oval.
    Use the Picture Styles to Change the Shape Type for the Conditionally Formatted Shape
    You can also use a simple macro to change the shape.  One line of VBA code will do the trick.  Just make sure to select the shape before running the code.

    Selection.ShapeRange.AutoShapeType = msoShapeRightArrow

    That single line of code can be run in the Immediate Window of the VB Editor, so you don’t even have to create a macro.  Here is a link to the help page that contains all the AutoShapeType enumerations.

    Change Shape Type with VBA Immediate Window AutoShapeType

  3. If you want the shape to appear on a different sheet, copy the cell, then select the destination sheet before pasting.  If you try to copy and paste the linked picture to another sheet then the sheet reference will not be updated.  You can also update the cell reference of the shape to include the sheet name.
    Picture Linked to a Cell that Contains Conditional Formatting on Another Sheet

Method #2: The CF Shapes Add-in

My friends Brad Edgar and Ryan Wells have created another solution to this problem with the CF Shapes Add-in.

This add-in allows you to create shapes that contain conditional formatting.  The shapes fill color is updated as the value in the shape changes.  You can also reference a cell that contains a value.

Create Conditionall Formatted Shapes with the CF Shapes Add-in

This is a great solution that gives us more control over the border and properties of the shape.

Click Here to Learn More about the CF Shapes Add-in

Video Training on Conditional Formatting

If you want to learn more about conditional formatting then I highly recommend the Conditional Formatting Video Course by Dave Bruns at ExcelJet.net.

Excel conditional formatting video course

Conditional formatting can really help make your data shine, and it’s a great addition to any interactive dashboard.  This is a feature of Excel that can be a bit tricky to learn at first.

The conditional formatting rules aren’t always easy.  Each rule type contains it’s own set of options and properties, and it can take a few steps to get them setup.

So if you are new to conditional formatting I definitely recommend some structured learning like Dave’s Course.  His videos are short and to the point, and will get you up and going in no time.

Click Here to Learn More about the Conditional Formatting Course

What will you use this technique for?

Please leave a comment below with any questions.  I would be interested to learn how you will use this technique in your projects.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 22 comments
love spell - September 19, 2017

What’s up,I check your new stuff named “2 Ways to Apply Conditional Formatting to Shapes (Video Tutorial) – Excel Campus” on a regular basis.Your story-telling style is witty, keep up the good work! And you can look our website about love spell.

Reply
Francisco - August 31, 2017

Brilliant!

Reply
Doruk - August 9, 2017

Thank you very much this is very helpful!

Reply
Andrey - July 19, 2017

Wanted to warn about the issue of using above trick.
One day my excel (quite big file with a lot of data and formulas – 35MB) became significantly unresponsive/slow. I had only one linked picture and this picture was the reason of the problem! I spent several hours analyzing all possible problems. I could not imagine that this trick with linked picture can bring so significant delay. What is bad is that trouble happened already after some time of being this picture in my file.
Hope this helps someone.

Reply
Haytham - March 17, 2017

Hello Team,
will you please give me hand to the below issue.
I have an excel with one picture I would like to to have this picture automatically change the color for each part of the picture based on value of another cell.
For example if I have #3 in this cell then specific part of the picture will be changed the color.

Thank you for your hand on this ASAP.

Kind Regards
Haytham

Reply
    Jon Acampora - March 22, 2017

    Hi Haytham,
    I don’t believe it’s possible to change the color of a portion of a picture. Shapes typically have a single fill color.

    Reply
Matt Kiefer - November 22, 2016

splendid!

Reply
Susan - October 5, 2016

Jon,

I currently have created data on the worksheets in my workbook that include the conditional formatting and pasting of images on other worksheets. Thank you!!

I thought maybe you could assist with another issue.

I want to limit access/visibility to worksheets within a workbook. Ideally, I would like to have the user enter an account number on worksheet1 which will return the correlating account worksheet. I do not want users with different account numbers to be able to view data on worksheet accounts that are not theirs.

I attempted to hyperlink but found I couldn’t password protect each account sheet to limit visibility. I could only limit what they could modify and everyone could still see the data.

Any ideas? Is there a way to create a form that will recall a worksheet.. not just data? I have charts/visuals on the individual worksheets.

Reply
    Jon Acampora - October 11, 2016

    Hi Susan,

    Thanks for the feedback. Happy to hear it helped.

    The processing of hiding and unhiding the sheets can be done with a macro. Keep in mind that Excel files are not 100% secure, and someone could hack the file to see the other worksheets with free programs that can be downloaded from the internet. With that said, if you just want to hide the sheets so others can’t see the info then you can automate that process with a macro.

    If you are new to macros, checkout my free 3-part video series on getting started with macros & VBA.

    I hope that helps.

    Reply
Logan - July 6, 2016

Jon,

how did you get the cell next to the number to change color without a value in it? I am trying to get the cell to change to 1 of 3 different colors based on where the number falls within the ranges I have set . thanks

Logan

Reply
    Jon Acampora - July 18, 2016

    Hi Logan,
    You can use a formula with an IF function in it. I don’t have any specific tutorials on this yet, but you basically have to create a conditional format based on a formula. Then use the IF function to return a TRUE or FALSE based on a condition. If it returns a TRUE then the conditional format will be applied to the cell. You can reference other cells in the IF function. I hope that helps.

    Reply
Jon Acampora - April 1, 2016

Richard submitted a great suggestion about adding the Change Shape menu to the Quick Access Toolbar. This allows you to quickly change the shape type of the linked picture.

Excel Quick Access Toolbar Change Shape Menu

Reply
David Walton - March 30, 2016

Hi,

As Heather already pointed out for Excel 2007 – also the Format menu tab that comes up when you click on a Shape has an option to Crop and then Crop to Shape in Excel 2013 with a wide variety of shapes. I was quickly able to change the linked picture rectangle in the example file to a block arrow. Worked very well and opens up many more standard Excel shapes to utilise beyond just the Rectangle of a picture. Great blog Jon – I really like this simple picture link idea and the Shape CF feature! Thanks.

Reply
Pablo Baez - March 24, 2016

Hi John,
Cool technique. Using the camera icon works just like pasting as Linked Picture.
Thanks for sharing.
Pablo

Reply
Heather R - March 23, 2016

Don’t forget the “crop to shape” option in the Picture Tools (under the Crop button in 2007) – you can crop your rectangle into a wide variety of shapes with this. Once I adjusted the width of the shape, I came up with a decent heart.
I could easily see using a lightning bolt as an indicator of a danger point in a dashboard. Where you use no color when the conditional format is good (so it’s invisible), but bright yellow/orange when there’s a problem in the numbers.

Reply
    Jon Acampora - April 1, 2016

    Hi Heather,

    Sorry to not get back sooner. I’ve been out sick for almost a week.

    That is a great suggestion! Your dashboards sound pretty electrifying. 😉 Lightning strikes poor performance. That is awesome, and creative! 🙂

    Jon

    Reply
jomili - March 23, 2016

Jon,
I appreciate your detailing the picture technique. On the CF Add-in, I notice that not only is a license required ($50) to apply the CF to the shapes, but apparently your audience will have to have the Viewer add-in (free) to be able to see the results. I work in state government, and distribute reports all across the state and to the Feds; I can’t require all of my users to install special software to be able to view my reports, so that’s not really a viable option for those of us who distribute reports widely.

Reply
    Ryan Wells - March 23, 2016

    Hi Jomili,

    I certainly understand your concern about the CF Shapes Viewer add-in. I think you’ll be happy to hear we’re testing a version of CF Shapes that will no longer require the second add-in and we should be launching it shortly! I’ll certainly keep you updated when we launch the new version.

    Thank you for the feedback!

    Ryan Wells
    wellsr.com / cfshapes.com

    Reply
INDZARA - March 23, 2016

Conditionally formatting shapes would be a great feature within Excel.
The issue I face with ‘paste as linked picture’ is that it sometimes distorts the original. I use the approach for my geographical heat maps (heat maps by state) where I draw the shapes of states and put formulas in the cells to align with the shapes. I use conditional formatting on those cells. Then, I copy and paste as linked picture in a separate sheet so that the user gets to view the image without messing with all the shapes and cells with formulas.
The distortion is strange and I have not been able to find the reason. It is not an issue with simple cells, but I had to make my cells very small (row height and column width) as some states have very curved borders. If you have any suggestions, please let me know. Thanks, Jon.

Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x