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!
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.
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.
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.
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.
hey Jon, thanks for the video. I am looking to change a excel sheet with conditional formatting where the colors are changing. but I want to do something a little different. for instance I want to change a number to a pie. Example if the number is 1 the pic in that cell would be a fourth of a sphere. if it is 2 it would show half a pie. if it is 3 3/4 of a pie and then for would show the whole pie. is that formatting available in excel if so what is it called. and how can I set it up for my spread sheet with our having a separate chart. I would like each individual cell to be a chart per say. I hope I made sense here. thanks for your help in advance!
Hi Jon I have one query here. I have one data where we have to meet the accuracy of 93% every month. So i used to send audit projections on the basis of number of audits on a daily to basis to meet the accuracy of 93%. Is there any way so that i can change the color of a cell which has 93% to green using conditional formatting, otherewise i am selecting color manually to each and every box. Please help.
Hi Ravi, Yes, this is definitely possible. My friend Dave at ExcelJet has a great article on how to do conditional formatting with formulas. Here is the link. exceljet.net/conditional-formatting-with-formulas
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.
+NIRANJAN K M Hi Niranjan, Here is the link to the page where you can download the sample file. There are no macros needed for this solution. www.excelcampus.com/tips/conditional-formatting-shapes/ Thanks!
The "Paste Linked Picture" cannot work on a different tab. I copied a cell from one tab, then went to another tab to paste - but the option for "Linked Picture" did not pop up. Is there a workaround?
Sorry to not reply sooner. There are two ways to solve this. 1. After pasting the picture to the new sheet, change the reference in the formula bar to include the original sheet name that the cells are on. 2. When originally pasting the Linked Picture, paste it on a different sheet instead of the same sheet that the cell is on. That will also create the reference in the formula bar for the shape back to the original sheet. Once you have that reference to the original sheet on another sheet, you can then copy/paste the shape to other sheets. I hope that helps. Thanks again and have a nice day! 🙂
This works well for things like graphically showing construction progress. I use it for showing the completion of sewer, water and paving. For testing progress. I simply overlay the copied cells reformatted as long narrow lines or squares. As progress is made the conditional formatting on the master sheet is automatically updated on the second sheet. I have a plan view as the background and the formatted shapes clearly show the progress. I usually use gray as the original placeholder. Yellow as Installed, blue as scheduled testing, green or red for passed or failed. The same concept is used for construction of buildings, such as apartments and dormitories.
Thanks, but what about a CIRCLE? Nobody seems to know this: if I insert a circle that's 24x24" (perfect circle), HOW do I determine how to fit 8,964 cells within that area? It there an auto script to do this? Please help me with details if you can. Thanks
fit 8,964 square-shaped cells within a circle with a radius of 12". 1) Calculate how many cells for circle diameter A = 8,964 A = pi * r¨2 r = sqrt(A/pi) r= sqrt (8964 / 3.14159) = 53.4165 so the radius will be 54 cells long. The circle will have a diameter of 54*2 = 108 cells. 2) Insert consecutive numbers in the first column and the first row, 1 to 108. 3) Select all rows and columns and resize the row height to fit your screen. Also resize the column width to the same as the row height. Select your grid 108 cells wide * 108 cells high and activate all borders. 4) Now insert an ellipse, right click it and select "Size and properties" and scale it to exactly 24 inches high and 24 inches wide. Also within Properties, check the box "Move, but not change size with cells". 5) Select all cells and columns and repeat step 3 and 4 if needed until the grid fits your circle.
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
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!
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.
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.
Brilliant. Thank you!
Great! This is exactly what I'm looking for! Thank you!
You are so amazing sir,, thanks a lot for the tip
Great video. Thank you
Thank you, genius. 😄
Great tip with the format shape option.
Your videos really help full. Please advise, how to change a specific shap outline color based on some farmula or condition or custom formatting
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?
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.
Just tested it out - interesting! Thanks!
HELLO JOHN,THANKS FOR ALL THE HELPFULL VEDIOS.CAN WE DO THE SAME IN GOOGLE SHEETS ?PLEASE HELP ME OUT.REGARDS,AVINASH
Beautiful thank you
You're welcome! 😀
Great!!! Thx
thank you! that helps a lot
Hey Jon, could you please help for how did you create the shape and what is link picture
Hi Jon, I noticed the selector is green color , can you help me how set up like that ? Thanks.
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.
This is great! Thanks!
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.
Awesome conditional formatting :)
Thanks!
simple but effective.. thanks
+Derek Lowry Thanks Derek! :-)
Thank you so much!
hey Jon, thanks for the video. I am looking to change a excel sheet with conditional formatting where the colors are changing. but I want to do something a little different. for instance I want to change a number to a pie. Example if the number is 1 the pic in that cell would be a fourth of a sphere. if it is 2 it would show half a pie. if it is 3 3/4 of a pie and then for would show the whole pie. is that formatting available in excel if so what is it called. and how can I set it up for my spread sheet with our having a separate chart. I would like each individual cell to be a chart per say. I hope I made sense here. thanks for your help in advance!
How did you apply formatting to a shape/cell with the number in a different cell?
Could anyone help me out. I can't get to change the shape with VBA.
Hi Jon I have one query here.
I have one data where we have to meet the accuracy of 93% every month. So i used to send audit projections on the basis of number of audits on a daily to basis to meet the accuracy of 93%. Is there any way so that i can change the color of a cell which has 93% to green using conditional formatting, otherewise i am selecting color manually to each and every box.
Please help.
Hi Ravi,
Yes, this is definitely possible. My friend Dave at ExcelJet has a great article on how to do conditional formatting with formulas. Here is the link. exceljet.net/conditional-formatting-with-formulas
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.
How to avoid the shape size changing, when I copy to new tab
Hey Jon - That was a great and an Informative data.
Can you help me with the links on Macro if there are any.
+NIRANJAN K M Hi Niranjan, Here is the link to the page where you can download the sample file. There are no macros needed for this solution. www.excelcampus.com/tips/conditional-formatting-shapes/
Thanks!
A workaround to create false shapes is to apply "group " to several images. so you can have "shapes" without VBA.
The "Paste Linked Picture" cannot work on a different tab. I copied a cell from one tab, then went to another tab to paste - but the option for "Linked Picture" did not pop up. Is there a workaround?
Sorry to not reply sooner. There are two ways to solve this.
1. After pasting the picture to the new sheet, change the reference in the formula bar to include the original sheet name that the cells are on.
2. When originally pasting the Linked Picture, paste it on a different sheet instead of the same sheet that the cell is on. That will also create the reference in the formula bar for the shape back to the original sheet.
Once you have that reference to the original sheet on another sheet, you can then copy/paste the shape to other sheets.
I hope that helps. Thanks again and have a nice day! 🙂
This works well for things like graphically showing construction progress. I use it for showing the completion of sewer, water and paving. For testing progress. I simply overlay the copied cells reformatted as long narrow lines or squares. As progress is made the conditional formatting on the master sheet is automatically updated on the second sheet.
I have a plan view as the background and the formatted shapes clearly show the progress. I usually use gray as the original placeholder. Yellow as Installed, blue as scheduled testing, green or red for passed or failed. The same concept is used for construction of buildings, such as apartments and dormitories.
My Excel 2007 is not showing Format Tab. Please guide me.
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
Thanks, but what about a CIRCLE? Nobody seems to know this: if I insert a circle that's 24x24" (perfect circle), HOW do I determine how to fit 8,964 cells within that area? It there an auto script to do this? Please help me with details if you can. Thanks
fit 8,964 square-shaped cells within a circle with a radius of 12".
1) Calculate how many cells for circle diameter
A = 8,964
A = pi * r¨2
r = sqrt(A/pi)
r= sqrt (8964 / 3.14159) = 53.4165
so the radius will be 54 cells long.
The circle will have a diameter of 54*2 = 108 cells.
2) Insert consecutive numbers in the first column and the first row, 1 to 108.
3) Select all rows and columns and resize the row height to fit your screen.
Also resize the column width to the same as the row height.
Select your grid 108 cells wide * 108 cells high and activate all borders.
4) Now insert an ellipse, right click it and select "Size and properties" and scale it to exactly 24 inches high and 24 inches wide. Also within Properties, check the box "Move, but not change size with cells".
5) Select all cells and columns and repeat step 3 and 4 if needed until the grid fits your circle.
how to change color with Number change ?? plz share!
Format your cells with roules :)
Check out Conditional Formatting.
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
doesn't show anything, just the end result, would be useful to see the actual conditional formatting dialog and settings