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
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:
- The CF Shapes Add-in allows you to create custom shapes that contain conditional formatting.
- The Conditional Formatting Video Training Course from ExcelJet will help make you a conditional formatting pro.
Download the File
Download the file I used in the video.
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.
Watch the screencast in your browser
The screencast animation above shows how to create the conditionally formatted shape. Here are the steps:
- Copy the cell that contains conditional formatting. This can be a single cell or a range of cells.
- Paste the cell as a Linked Picture. This option can be found on the Home tab of the Ribbon or the right-click menu.
- The Linked Picture creates a shape. You can move and resize this shape just like any other shape in Excel.
- 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.
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.
- 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
- 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.
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.
- 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.
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.
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.
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!
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.
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.
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
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.
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
Hi John,
Cool technique. Using the camera icon works just like pasting as Linked Picture.
Thanks for sharing.
Pablo
Great suggestion! Thanks Pablo!
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.
Thanks David! The possibilities are endless with Excel… 🙂
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.
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
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.
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.
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.
splendid!
Thanks Matt! 🙂
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
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.
Great technique.
Enhancement to get multicolored cells and linked copy images:
you’re original cell uses a multi color gradient as the background image then the linked picture copy will also have the same gradient…
This might also work for a pattern or an actual image ( although I haven’t tried that last one)
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.
Thank you very much this is very helpful!
Brilliant!
If we use the CF Shapes add-on to create a file and send it to someone that does not have CF Shapes will the formatting still show up for them and will they be able to edit it in a similar fashion (enter a value and the shape will be shaded appropriately)?
Adam
I am trying to use the VBA code to create the PieWedge Shape. 2 issues – it is not working. I created the code in the Immediate Window and nothing happened. Second issue is I need to repeat this 4 times based on 4 different copies.
Awesome! Needed to be able to hide a second graph when the slicer limited my displayed locations from 2 to 1. Put a box over it: clear (no color) box when filter=”(All)”, turn it white with any other value. Priceless.
Thank you!
Jon, Hi is there any way to to update a cell if the colour of a shape is changed?
Basically I have a requirement for a traffic light type system for particular devices. I need to be able to have a quick visual check i.e. RED = Fail, I select the shape change its colour and it updates an other detailed sheet with FAILED – is this possible.
I am able to do it the other way around but this does not fit my requirements as there are 100’s of devices/items
TIA