Today we are going to discuss about Conditional formatting in shape.
We can apply conditional formatting in cell to change the color based on condition.
If we know how to tweak this a bit, we can dynamically change the color in shape.
Lot to learn, let's dive in.
Here, Service level metric value is in D3 cell. We are going to use a helper cell which will have the conditional formatting. Select any cell away from the dashboard view, in our case we picked Y3 cell.
Formula in Y3 cell is =D3, we simply referred that cell.
Change color of cell and Font in Y3 cell to green color by selecting the Fill color option in Home tab as shown below.
In Home tab -> Conditional Formatting Drop down -> select Manage Rules
Add below 2 rules by clicking New Rule.
Make sure position of rules as same as here.
Click OK.
Copy Y3 cell and paste as linked picture as shown below.
Expand the size of pasted picture based on your preference,
In menu bar, Contextual tab namely 'Picture Format' is getting activated when we select the picture. In that tab, at right most corner drop down is there in 'Crop' option.
Set crop aspect ratio to 1:1 as shown below.
Then, click drop down in 'Crop to Shape' and select oval shape.
Expand the size of oval by dragging lower right corner as shown below.
You can see that, there is a cut at top of the circle.
Select picture and click Crop Aspect ratio again to correct straightness at top,
Expand the outer edge of rectangle by dragging and move rectangle to fit in full circle.
Insert Rounded rectangle shape and send it to backward position of circle and adjust to fit center as shown below.
Insert Text box at the center of circle shape and in the formula bar enter '=' and then refer D3 cell. Now you can see the percentage value in text box, this will reflect D3 cell value in dynamic way. Align the text box at center of circle shape and set the fill color and border color to 'No Color' Option.
If we change the service level percentage, then color of the shape will change based on applied condition and the value in text box will also change. value in text box will act like a label. Now, we can enjoy dynamic traffic light visual in Excel.
Comments