How To Apply Conditional Formatting To Shapes In Excel

Learn 2 ways to apply conditional formatting to shapes in Excel.

This technique is great for dashboards and interactive reports where you don't want to be confined to the grid layout of the cells.

There is no way to actually apply conditional formatting to shapes in Excel, so both these methods are workaround solutions.

Here is the link to the article and page where you can download the sample Excel file:

Method #1: A simple solution is to copy and past e the cell that contains conditional formatting as a Linked Picture. The paste special linked picture command will create a shape that displays the formatting and values of the cell. As the cell's value and formatting changes, the linked picture will also update to reflect the changes.

You can use this technique for a single cell or a range of cells. The picture is resizable so you can make the shape bigger for your dashboard.

You cannot change the shape type directly in the Excel menus or ribbon. However, you can use VBA to change the shape type.

The following line of code will change the shape type of the selected shape with VBA.

Selection.ShapeRange.AutoShapeType = msoShapeRightArrow

Method #2: The CF Shapes Add-in allows you to create custom shapes with conditional formatting for the shapes fill color.

Click the following link to learn more about the CF Shapes Add-in:

00:00 Introduction
00:21 Linked Picture
01:32 Other Examples
02:30 Pictures Styles
03:14 Range Styles
04:05 CF Shapes

[MrElectrifyer]

This is a great method for having a cell's content displayed in the spreadsheet view, but not printed, as you're able to exclude an object from being printed. Thanks very much for shearing!

[mattjeon]

Great! This is exactly what I'm looking for! Thank you!

[CaseyBlase]

This was a fantastic lesson. The one thing I would add that I had to learn after the fact. The default is to save without the worksheet in the formula, so if you want to copy and paste it across workbooks, be sure to add it.

[levinmorgan]

Great tip, Jon! I used the picture shape of a rounded rectangle frame to imitate a flow chart rectangle. Works fine, and the conditional formatting is awesome.

[DougHExcel]

Great tip with the format shape option.

[susanto]

You are so amazing sir, , thanks a lot for the tip

[iamskk]

Your videos really help full. Please advise, how to change a specific shap outline color based on some farmula or condition or custom formatting

[juarezr]

I discovered that you can apply a few more "shapes" by applying the Crop to Shape under the Picture formate tab. Picture Format->Size->Crop->Crop to Shape-> Select the shape from menu.

[truglis]

Thanks! Gonna use it in a controlpanel as a overlay on a picture of my flat, with macroes that send webhooks. An use this as a status indicator.

[chaitanyaavinashthiparani]

HELLO JOHN, THANKS FOR ALL THE HELPFULL VEDIOS.CAN WE DO THE SAME IN GOOGLE SHEETS ?PLEASE HELP ME OUT.REGARDS, AVINASH

[dirtymike]

I do this to my excel sheet out of boredom and to see what the customer thinks when we send surveys and how they look to a standard

[Joikie]

I want to create a dropdown list in a cell and based on which selection it will change the outline color of the shape. Any advice?

[vijaykumarc.a.]

I thought it worked for me, but it can be painful to work with the paste as picture if the data is very advanced level and is worked on a shared folder or one drive sharing as the picture loses the link even if stored in the same WB or behaves odd very often...so i had to take out this method...didnt work for me!! sadly...although this could be very nice for simple workbooks...tks

[m.n.]

Thank you sir for nice and clear video, I have a question pleas. I make a table that contains in culomn A doctors names and the headers in row1 is the month date. Inside the table all the cells has data validation with departments in hospital to make a monthly rota shift for the doctors. The list of the data valedation contains (main department, long shift, short shift, emergency, operation room, DAY OFF).
I want to make a conditional FORMATING THAT IF I CHOOSE "long shift" from the data validation automatically gives this doctor "DAY OFF" for the next day and color the cell of "DAY OFF" in red.
How can I do that.( For example if I choose in C5 "long shift" automatically mak D5 "DAY OFF" with red background in B5.) thank you.

[nhanetjean]

A workaround to create false shapes is to apply "group " to several images. so you can have "shapes" without VBA.

[chsaeman]

Hi Jon, I noticed the selector is green color, can you help me how set up like that ? Thanks.

[OriginalOutdoorplayer]

How did you apply formatting to a shape/cell with the number in a different cell?

[niranjankunamallikarjun]

Hey Jon - That was a great and an Informative data.
Can you help me with the links on Macro if there are any.

[FarooqKhan-uftu]

hi Mr jon can you tell me how i create cf shape options in excel. i see this video you have more options in excel book. please tell me how it possible. thanks.

[govugovshw]

How to avoid the shape size changing, when I copy to new tab