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.
- 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.
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.
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.
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.
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!