Excel में सूत्र के साथ सशर्त स्वरूपण

सशर्त स्वरूपण अब Microsoft Excel अनुभव का एक अभिन्न अंग है। यह हमें डेटा के एक यादृच्छिक या बड़े समूह में विशेष मूल्यों की पहचान करने में मदद करता है। हम न केवल कुछ मानों की पहचान कर सकते हैं बल्कि सशर्त स्वरूपण के साथ यह भी पहचान सकते हैं कि कौन सी निश्चित सीमा से संबंधित है या नहीं, स्ट्रिंग मान, संख्यात्मक मान, त्रुटियां इत्यादि। यह, जैसा कि नाम से पता चलता है, एक्सेल एक सेल को इस आधार पर स्वरूपित करता है कि कोई शर्त सही है या नहीं। इस ट्यूटोरियल में, हम एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग पर चर्चा करेंगे।

Excel में फ़ॉर्मूला के साथ सशर्त स्वरूपण का उपयोग करने का वीडियो ट्यूटोरियल

इस वीडियो लेक्चर में:

• आप फॉर्मूला-आधारित सशर्त स्वरूपण का उपयोग करके कक्षों को प्रारूपित करना सीखेंगे।
• आप ISTEXT फ़ंक्शन के बारे में जानेंगे ।
• और आप फिर से सीखेंगे कि फ़ार्मुलों में निरपेक्ष संदर्भ का उपयोग कैसे करें।

आप प्रदर्शन के लिए उपयोग की गई कार्यपुस्तिका को नीचे दिए गए डाउनलोड लिंक से डाउनलोड कर सकते हैं।

Excel में फ़ॉर्मूला के साथ सशर्त स्वरूपण का उपयोग कैसे करें

एक्सेल में एक सूत्र के साथ सशर्त स्वरूपण का उपयोग करने का मुख्य विषय बहुत सीधा है। हमें सशर्त स्वरूपण सुविधा का चयन करने की आवश्यकता है। फिर किसी पूर्वनिर्धारित विकल्प को चुनने के बजाय, हमें एक नया नियम चुनना होगा और फिर उसे दर्ज करने के लिए सूत्र का चयन करना होगा।

ध्यान रखें कि सूत्र द्वारा, हम आमतौर पर यहां सशर्त स्वरूपण बॉक्स में एक शर्त दर्ज करते हैं। यदि शर्त सत्य है, तो प्रारूप लागू होता है और इसके विपरीत।

यहाँ Excel में सूत्र के साथ सशर्त स्वरूपण का विस्तृत प्रदर्शन दिया गया है।

चरण:

• सबसे पहले, उन कक्षों की श्रेणी का चयन करें जिन्हें आप सूत्रों के साथ प्रारूपित करना चाहते हैं।
• अब होम पर जाएं अपने रिबन पर टैब करें।
• फिर सशर्त स्वरूपण select चुनें शैलियों . से समूह अनुभाग।
• उसके बाद, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• परिणामस्वरूप, नया स्वरूपण नियम बॉक्स अब पॉप अप होगा।
• फिर आप उन मानों को प्रारूपित करें जहां यह सूत्र सत्य है:के अंतर्गत फ़ील्ड में सूत्र दर्ज कर सकते हैं:

• आप फ़ॉर्मेट . पर क्लिक करके भी प्रारूप शैली का चयन कर सकते हैं
• एक बार जब आप उपरोक्त सभी चरणों और कक्षों की स्वरूपण शैलियों के साथ कर लें, तो ठीक क्लिक करें इस बॉक्स पर।

उदाहरण के लिए, हम इन सभी चरणों का पालन करने के बाद चित्र में दिखाया गया निम्न सूत्र दर्ज करते हैं।

स्प्रैडशीट की सीमा B3:B6 . थी . यह अब इस तरह दिखेगा।

यहां, हमने शर्त दर्ज की है कि सेल मान 3 से अधिक है। इसलिए, इस चयन में जहां 3 से अधिक मान मौजूद हैं, प्रारूप लागू होते हैं।

एक्सेल में फ़ॉर्मूला के साथ सशर्त स्वरूपण का उपयोग करने के 21 उदाहरण

अब इस खंड में, हम एक्सेल में सशर्त स्वरूपण सुविधा में सूत्र का उपयोग करने के विभिन्न उदाहरण प्रदर्शित करेंगे। इस सुविधा का उपयोग करने और हर एक को दूसरे के साथ मिलाने की संभावना बहुत बड़ी है। इसलिए हमने कुछ प्रमुख फ़ार्मुलों को शामिल करने का प्रयास किया है जो हमें लगता है कि आपको जानकारीपूर्ण लग सकते हैं। यदि आपको केवल किसी विशेष की आवश्यकता है, तो आप उन्हें ऊपर दी गई तालिका में पा सकते हैं।

सबसे पहले, आइए इस डेटासेट पर विचार करें जिसमें संख्यात्मक और स्ट्रिंग दोनों मान हों।

सूत्र के साथ सशर्त स्वरूपण की सहायता से, हम Excel में स्ट्रिंग मानों को आसानी से एकल कर सकते हैं। ऐसा करने के लिए, हमें ISTEXT फ़ंक्शन . का उपयोग करने की आवश्यकता है . यह देखने के लिए इन चरणों का पालन करें कि हम Excel में सशर्त स्वरूपण में इस फ़ंक्शन का उपयोग करके कैसे प्रारूपित कर सकते हैं।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=ISTEXT(B5)

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

आप देखेंगे कि सशर्त स्वरूपण में इस सूत्र का उपयोग करने के बाद एक्सेल डेटासेट के सभी पाठों को प्रारूपित करेगा।

अब आइए मिलते-जुलते मानों का एक और उदाहरण देखें।

इस डेटासेट में डेटा का एक बड़ा संग्रह है। अब हम उन्हें प्रारूपित करने जा रहे हैं जो केवल E5 . के सेल मान से मेल खाते हैं ।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=B5=$E$5

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

इस प्रकार हम Excel में सूत्र की सहायता से सशर्त स्वरूपण का उपयोग करके किसी अन्य कक्ष के बराबर कक्षों को हाइलाइट कर सकते हैं।

आइए अब बराबर होने से थोड़ा हटें। अब हम इस खंड में किसी अन्य सेल के मान से बड़ा या छोटा होने के आधार पर एक डेटासेट को प्रारूपित करेंगे। और हम एक्सेल में फॉर्मूला विधि का उपयोग करके सशर्त स्वरूपण का उपयोग करके उन्हें हाइलाइट करने जा रहे हैं।

हम इसे निम्नलिखित डेटासेट पर निष्पादित करेंगे।

हम श्रेणी को प्रारूपित करने जा रहे हैं C5:C9 सेल E5 . के मान के आधार पर ।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=C5>$E$5

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें . यह अब डेटासेट होगा।

• इसी तरह, आप निम्न मानों के लिए भी यही प्रक्रिया दोहरा सकते हैं और इस तरह के डेटासेट के साथ समाप्त हो सकते हैं।

इस प्रकार हम Excel में किसी अन्य कक्ष के मान के आधार पर सूत्र के साथ सशर्त स्वरूपण का उपयोग कर सकते हैं।

अब हम यह देखने जा रहे हैं कि हम IF फ़ंक्शन . वाले सूत्रों का उपयोग कैसे कर सकते हैं? एक्सेल में सशर्त स्वरूपण सूत्र के रूप में। हम सशर्त स्वरूपण बॉक्स में सीधे फ़ंक्शन का उपयोग नहीं कर सकते हैं। लेकिन हम फ़ंक्शन का उपयोग एक मान प्राप्त करने के लिए कर सकते हैं जिसका उपयोग हम एक्सेल में सशर्त स्वरूपण का उपयोग करके कोशिकाओं को आगे प्रारूपित करने के लिए कर सकते हैं।

आइए पहले इस उदाहरण के लिए एक डेटासेट लें। यह वही है जिस पर हम यह कार्य कर रहे हैं।

चरण:

• सबसे पहले, सेल चुनें E5 और निम्न सूत्र लिखिए।

=IF(D5>C5,"Profit","Loss")

• फिर दर्ज करें press दबाएं अपने कीबोर्ड पर।

• अब क्लिक करें और फिल हैंडल आइकॉन को कॉलम के अंत तक खींचें और बाकी सेल्स के लिए फॉर्मूला दोहराने के लिए।

• अगला, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=E5="Profit"

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

यह अब डेटासेट होगा।

इस प्रकार हम IF . का उपयोग कर सकते हैं एक्सेल में सशर्त स्वरूपण सूत्र के लिए कार्य।

5. एकाधिक स्थितियों का उपयोग करने वाले कक्षों को हाइलाइट करें

अब पहले डेटासेट पर वापस चलते हैं।

अब हम उन सभी कक्षों को हाइलाइट करने जा रहे हैं जो या तो 5,6 हैं या जिनमें "कैट" टेक्स्ट है।

Excel में सशर्त स्वरूपण सूत्र बॉक्स में ऐसा करने के लिए, हमें OR फ़ंक्शन . की आवश्यकता है ।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=OR(B5=5,B5=6,B5="cat")

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

अब डेटासेट पर एक नज़र डालें।

इस प्रकार हम या . का उपयोग कर सकते हैं एक्सेल में कई स्थितियों के लिए सशर्त स्वरूपण सूत्र में कार्य करता है।

आगे बढ़ते हुए, हम अब पूरी पंक्तियों में कोशिकाओं को हाइलाइट करने जा रहे हैं जहाँ पूरी पंक्ति एक दूसरे के साथ मेल खाती है। यह थोड़ा पेचीदा है। हमें COUNTIF . की आवश्यकता होगी और CONCATENATE इसके लिए कार्य करता है।

आइए प्रदर्शन के लिए एक नमूना डेटासेट लें।

यहां, हम तीसरी और छठी पंक्ति को पूरी तरह से मेल खाते हुए देख सकते हैं। एक्सेल में एक कॉलम में डुप्लिकेट मानों को हाइलाइट करने के लिए हम इन कार्यों का उपयोग कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, सेल चुनें F5 और निम्न सूत्र लिखिए।

=CONCATENATE(B5,C5,D5,E5)

• फिर Enter दबाएं ।

• अब फिर से सेल का चयन करें और फिल हैंडल आइकन पर क्लिक करें और बाकी सेल्स को उनके संदर्भ के लिए फॉर्मूला से भरने के लिए खींचें।

• यह थोड़ा गन्दा लग रहा है, लेकिन अभी के लिए इसे अनदेखा करें। अब श्रेणी चुनें B5:B10 ।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=COUNTIF($F$5:$F$10,$F5)>1

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें . यह अब डेटासेट होगा।

• अब F . के कॉलम हेडर पर राइट-क्लिक करें और छिपाएं . चुनें संदर्भ मेनू से।

उसके बाद यह अंतिम स्प्रैडशीट दृश्य होगा।

आइए अब एक ऐसे डेटासेट पर एक नज़र डालते हैं जिसमें पूरा करने के लिए फ़ॉर्मूला होता है।

हम इन कक्षों को हाइलाइट करने के लिए एक सूत्र का उपयोग करके सशर्त स्वरूपण का उपयोग करने जा रहे हैं। इस कारण से, हमें ISFORMULA फ़ंक्शन का उपयोग करने की आवश्यकता है ।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=ISFORMULA(B5)

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

यह अब डेटासेट होगा।

8. किसी विशेष क्षेत्र से बिक्री हाइलाइट करें

इस उदाहरण में, हम किसी विशेष क्षेत्र से संबंधित डेटासेट से बिक्री को हाइलाइट करने जा रहे हैं। आइए निम्नलिखित डेटासेट लें।

अब मान लें कि हमें एरिज़ोना के लोगों को उजागर करने की आवश्यकता है। ऐसा करने के लिए, इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=$D5="Arizona"

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

एक्सेल अब इन चरणों के परिणामस्वरूप एरिज़ोना से बिक्री को चिह्नित करेगा।

9. कॉलम के अंतर को हाइलाइट करें

हम उन पंक्तियों को भी हाइलाइट कर सकते हैं जिनमें उनके आसन्न कॉलम की तुलना में अलग-अलग कॉलम हैं। उदाहरण के लिए, आइए निम्नलिखित उदाहरण पर एक नज़र डालें।

Excel में एक सूत्र के साथ सशर्त स्वरूपण का उपयोग करके उन्हें हाइलाइट करने के लिए, इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=$B5<>$C5

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

यह इन चरणों का अंतिम उत्पाद होगा।

इस खंड में, हम एक्सेल में सशर्त स्वरूपण में लापता मूल्यों को उजागर करने के लिए एक सूत्र का उपयोग करेंगे। उदाहरण के लिए निम्नलिखित डेटासेट लेते हैं।

सूची 2 में कुछ अनुपलब्ध मान हैं जो सूची 1 में उपलब्ध थे। हम उन्हें उजागर करने जा रहे हैं। कैसे देखें के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=COUNTIF($D$5:$D$10,$B5)=0

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

Excel इस सशर्त स्वरूपण सूत्र के लिए कक्षों को हाइलाइट करेगा।

11. कोशिकाओं को हाइलाइट करने के लिए सरल खोज बॉक्स बनाना

आइए अब विभिन्न विविधताओं के संयोजन के साथ एक मजेदार प्रयास करें। यह उसके लिए डेटासेट है।

यहां, हम सेल E4 . में एक मान डालेंगे और एक्सेल उन्हें पिछली श्रेणी में हाइलाइट करेगा, सभी फॉर्मूला विधि के साथ सशर्त स्वरूपण के साथ। हमें ISNUMBER . के संयोजन की आवश्यकता होगी और खोज इस विधि के लिए कार्य करता है।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=ISNUMBER(SEARCH($E$4,B5))

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

अब, डेटासेट को देखें। गेम शब्द वाले टेक्स्ट को चिह्नित किया जाएगा।

अगर हम सेल E4 . में मान बदलते हैं , उदाहरण के लिए, "स्वास्थ्य" हाइलाइट बदल जाएगा।

आइए अब पहले के किसी एक डेटासेट पर दोबारा गौर करें।

हम यहां एक्सेल में एक सूत्र के साथ सशर्त स्वरूपण के मूल अनुप्रयोग का उपयोग करने जा रहे हैं- हम यहां उन मानों को हाइलाइट करने जा रहे हैं जो कॉलम E में औसत से कम हैं। . हमें औसत फ़ंक्शन की आवश्यकता है इस उद्देश्य के लिए।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• स्वरूपण नियम . में बॉक्स में, पहले यह निर्धारित करने के लिए कि कौन से कक्षों को प्रारूपित करना है, एक सूत्र का उपयोग करें . चुनें नियम प्रकार चुनें . के अंतर्गत विकल्प ।
• फिर निम्न सूत्र को उन मानों को स्वरूपित करें जहां यह सूत्र सत्य है . में सम्मिलित करें ।

=E5<AVERAGE($E$5:$E$10)

• अगला, अपना पसंदीदा प्रारूप प्रकार चुनें।

• आखिरकार, ठीक . पर क्लिक करें ।

यह उन मानों को हाइलाइट करेगा जो डेटासेट में औसत से कम हैं।

13. उन मानों को हाइलाइट करें जो औसत से अधिक हैं

पिछले एक के समान, हम एक्सेल में सशर्त स्वरूपण की सूत्र विधि का उपयोग करके इस खंड में औसत से अधिक मूल्यों को उजागर करने जा रहे हैं। हमें औसत फ़ंक्शन का उपयोग करना होगा इस कार्य के लिए भी। डेटासेट वही होगा।

फ़ॉर्मूला देखने के लिए इन चरणों का पालन करें और आप इसका उपयोग कैसे कर सकते हैं।

चरण:

• सबसे पहले, हेडर को छोड़कर डेटासेट में सभी सेल चुनें।
• फिर होम पर जाएं अपने रिबन पर टैब करें।
• उसके बाद, सशर्त स्वरूपण . चुनें शैलियों . से समूह अनुभाग।
• अगला, नया नियम चुनें ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=E5>AVERAGE($E$5:$E$10)

• Next, select your preferred format type.

• Finally, click on OK ।

Thus Excel will highlight all the values that are higher than average in column E ।

Now let’s take another unique example that is practical in data cleaning and analysis. We are going to find the nearest value to a number in a random set of data.

This is the dataset of this example.

As you can see from the figure, we are going to find the value that is closest to the one in cell C13 . If there is the same value in the dataset, it will highlight that cell instead of the closest value. For this purpose, we will utilize the MIN , ABS , and OR functions.

Follow these steps to see how you can do that.

चरण:

• First, we need to perform some calculations. We need to find the smallest difference from this value in that set of data. For that select cell C14 and write down the following formula.

=MIN(ABS(B5:D11-(C13)))

• Then press Enter ।

• Now select all the cells in the dataset excluding headers.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)

• Next, select your preferred format type.

• Finally, click on OK ।

This will highlight the value that is closest to the value of 1, with conditional formatting formula in Excel.

Let’s go back to the random set of data.

This time we are going to find the top 3 values here using conditional formatting with a formula in Excel. The formula consists of the LARGE function ।

हम यह कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।

चरण:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=B5>=LARGE($B$5:$D$11,3)

• Next, select your preferred format type.

• Finally, click on OK ।

As you can see Excel will thus mark the top 3 values from the range using conditional formatting formula.

16. Find Bottom 3 Values

Again, we will use the same dataset as before. But this time we will use that to highlight the bottom 3 values from the dataset. Just like before we are gonna need the SMALL function for the formula. To see how we can do that, follow these steps.

चरण:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=B5<=SMALL($B$5:$D$11,3)

• Next, select your preferred format type.

• Finally, click on OK ।

This time, Excel will mark the bottom 3 values because of the conditional formatting formula.

17. Show Temperature with Color Scale

In this next example, let’s try another interesting usage of formula and conditional formatting in Excel. Here is the dataset for that.

We have taken two columns because we will use a formula to change the color based on the input in cell E5 . Also, there is a blank row at the start of the dataset. We will need that to utilize the formula we are going to use. The formula consists of IF and AND functions. To see how you can change the color scheme of these temperatures based on the current temperature, follow these steps.

चरण:

• First, select cell C6 and write down the following formula in it.

=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))

• Then press Enter ।

• Now select the cell again and click and drag the fill handle icon to replicate the formula for the rest of the cells in the column.

• Next, go to Conditional Formatting from the Styles group of the Home
• Then hover your mouse over the Color Scales ।
• After that, select your preferred color scale.

• The temperature scale for conditional formatting based on the formula for current temperature will now be complete.

• If we change the value of the current temperature in cell E5 , the temperature scale will change accordingly.

This is how we can show the temperature with color scale utilizing formula and conditional formatting in Excel.

18. Highlight Alternate Rows with Conditional Formatting

Now let’s go back to one of the previous datasets of random numbers.

With this dataset, we will highlight alternate rows with a color using the conditional formatting formula in Excel. For the formula, we will need the INT and MOD functions.

The steps to do that are below.

चरण:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=INT(MOD(ROW(),2))

• Next, select your preferred format type.

• Finally, click on OK ।

This will highlight the alternate rows using the conditional formatting formula in Excel.

19. Highlight Cells with Error

Next up, we will be using the conditional formatting formula in Excel to highlight cells with errors. This is particularly helpful with a large number of data in a sheet and where finding errors manually is tiresome. For demonstration, let’s take a look at the following dataset.

There contains two errors in the dataset. We are going to use the ISERROR function in the conditional formatting formula to detect them. The formula used below will help us identify them using conditional formatting in Excel.

चरण:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=ISERROR(B5)

• Next, select your preferred format type.

• Finally, click on OK ।

Excel will thus highlight all the errors as a result of using the conditional formatting formula.

20. Create Checklist with Conditional Formatting

Now let’s try another fun application of conditional formatting with a formula in Excel. This time we will make a checklist beside a set of data. With the options checked, the original data will change its format. We will use the following dataset.

This is a standard to-do list. Once we check a checkbox in the status column, the task beside it will change the format, let’s say a strikethrough. The basic idea is to use the boolean value of the checkbox to format in column B . Also, we will need the Developer tab to insert checkboxes. If you don’t have one in your ribbon, click here to display the Developer tab on your ribbon ।

Follow these steps to see how we can use the Excel conditional formatting formula to create a checklist in detail.

चरण:

• First, go to the Developer अपने रिबन पर टैब करें।
• Then select Insert from the Controls group section.

• After that, select the Check box (Form Control) ड्रॉप-डाउन मेनू से।
• Then place the box in its appropriate place.

• Next, right-click on the box and select Format Control संदर्भ मेनू से।

• Now go to the Control tab on the box and select cell C5 as its linked cell.

• फिर ठीक . पर क्लिक करें ।
• Now let’s remove the alt text and place it in the middle of the cell to make it look good.

• Now repeat the process for all of the tasks.

• Once checked, there will be a TRUE/FALSE value on the cells depending on it.

• So let’s use a white font color to make them invisible.

• Now select the task range you want to format.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=C5=TRUE

• Next, select your preferred format type.

• Finally, click on OK ।

Now once we check a check box beside a task, it will format the task beside it. We did all of that using the help of the conditional formatting formula in Excel.

21. Highlight Weekends in a Week

Let’s try another example where we will use a formula to highlight weekends in calender week in Excel. We will use the OR and WEEKDAY functions to create this formula. As for the dataset, we will use the following.

Now follow these steps to see how we can highlight weekends in Excel using conditional formatting using the formula.

चरण:

• First, select all the cells in the dataset excluding headers.
• Then go to the Home अपने रिबन पर टैब करें।
• After that, select Conditional Formatting from the Styles group section.
• Next, select New Rule ड्रॉप-डाउन मेनू से।

• In the Formatting Rule box, first, select the Use a formula to determine which cells to format option under Select a Rule Type ।
• Then insert the following formula in the Format values where this formula is true ।

=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)

• Next, select your preferred format type.

• Finally, click on OK ।

Thus it will highlight the weekends from the calender week, all with the help of conditional formatting in Excel.

निष्कर्ष

So these were the methods and different examples of using a formula for conditional formatting in Excel. I hope you have grasped the idea of the usage of the feature and can now use it accordingly. Hopefully, you have found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

For more guides like this, visit Exceldemy.com ।