Create Template in Excel

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Create Template in Excel

Post by BOOBALAN.V »

Dear Community,

Good Day !

We are trying to create template for project delay analysis in Gantt chart. But we don't know how to give conditional format for Period columns. Kindly request you to help us. We tried to set name manager and conditional formats from other net sources. But its not working for our cases (We don't know the basics of that source functionality). It is very difficult for us. Please help us. Here, we have attached the sample workbook with manually highlighted cells. Please find the attachment. Thank you for your kind help in advance.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Template in Excel

Post by HansV »

See the attached version. You can view the conditional formatting rules in Conditional Formatting > Manage Rules...

Conditional format for template.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Create Template in Excel

Post by BOOBALAN.V »

Dear Sir,

Thank you so much for your kind help. Its working fine. We have two doubts sir. Please help us for our learning purpose.

1. When using conditional formats, highlighted color is mingled with existing rules color. Why its showing like this sir?
2. Here, we have attached free template which is downloaded from net source and we used this model for our needs. In this period highlighting working fine without bottom border and its not hiding the existing conditions colors. But if we use the same way in our sheet. its not working sir. Please help us sir. It will be helpful for us in future in some case.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Template in Excel

Post by HansV »

I will look at this later.
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Create Template in Excel

Post by BOOBALAN.V »

Sure sir. Thank you :thankyou:

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Template in Excel

Post by HansV »

That template is much more complicated. Why don't you use that?
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Create Template in Excel

Post by BOOBALAN.V »

Good Morning Sir.

Yes sir. self tried to understand that how they have created this template. But it was very very difficult to understand, why they are finding the median value and multiplying with other values. But, the design of this template is suit for our requirements sir. That's why we select this template.

Your solution is very very simple and easy to understand sir. Thank you so much. We have only one doubt sir. Even if we change the order of conditions, its not showing the color properly. But, in their sheet, that is showing clearly. Please see the screenshot. the color showing is different. Its not looking professionally. That's why we asked in previous post sir. Please tell us what is the reason sir. How to avoid this. For the time being we removed color banding for alternative columns. But, Please help us.

Thank you so much for your time.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Template in Excel

Post by HansV »

Is this better? I set the fill color of the rules that have a pattern to while instead of no fill color.

Planning and Analysis of RFQ to PO.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Create Template in Excel

Post by BOOBALAN.V »

Dear Sir,

Thank you so much sir. Its pretty good sir :smile: How to set fill color rule sir. we couldn't see any additional conditional formats. How to do it sir. Please tell us sir.

User avatar
HansV
Administrator
Posts: 78233
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Create Template in Excel

Post by HansV »

Take this rule for example:

S0007.png

Click Format... and activate the Fill tab:

S0008.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

BOOBALAN.V
5StarLounger
Posts: 737
Joined: 24 Aug 2017, 07:43

Re: Create Template in Excel

Post by BOOBALAN.V »

Good Morning Sir,

Thank you so much sir sir. Now its working fine. Once again Thank you so much for your timely help as usual :smile: :thankyou: