कंडीशनल फॉर्मेटिंग अब माइक्रोसॉफ्ट एक्सेल अनुभव का अभिन्न अंग है। यह हमें डेटा के यादृच्छिक या बड़े समूह में विशेष मूल्यों की पहचान करने में मदद करता है। हम न केवल कुछ मूल्यों की पहचान कर सकते हैं बल्कि यह भी पहचान सकते हैं कि कौन से एक निश्चित सीमा के अंतर्गत आते हैं या नहीं, स्ट्रिंग मूल्य, संख्यात्मक मूल्य, त्रुटियां आदि कंडीशनल फॉर्मेटिंग के साथ। जैसा कि नाम से पता चलता है, यह एक शर्त के सत्य या असत्य होने के आधार पर एक्सेल सेल को फॉर्मेट करता है। इस ट्यूटोरियल में, हम एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग पर चर्चा करेंगे।
एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का उपयोग करने का वीडियो ट्यूटोरियल
इस वीडियो व्याख्यान में:
- आप फॉर्मूला-आधारित कंडीशनल फॉर्मेटिंग का उपयोग करके सेल्स को फॉर्मेट करना सीखेंगे।
- आप ISTEXT फ़ंक्शन के बारे में जानेंगे।
- और आप फिर से सीखेंगे कि फॉर्मूलों में निरपेक्ष संदर्भ (Absolute Referencing) का उपयोग कैसे करें।
आप नीचे दिए गए डाउनलोड लिंक से प्रदर्शन के लिए उपयोग की गई वर्कबुक डाउनलोड कर सकते हैं।
एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का उपयोग कैसे करें
एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का उपयोग करने का मुख्य विषय काफी सीधा है। हमें कंडीशनल फॉर्मेटिंग सुविधा का चयन करना होगा। फिर किसी पूर्वनिर्धारित विकल्प को चुनने के बजाय, हमें एक नया नियम चुनना होगा और फिर फॉर्मूला दर्ज करने के लिए फॉर्मूला विकल्प चुनना होगा।
ध्यान रखें कि फॉर्मूला से, हम आमतौर पर कंडीशनल फॉर्मेटिंग बॉक्स में एक शर्त दर्ज करते हैं। यदि शर्त सत्य है, तो फॉर्मेट लागू होता है और इसके विपरीत।
यहां एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का विस्तृत प्रदर्शन दिया गया है।
चरण:
- सबसे पहले, उन सेल्स की रेंज चुनें जिन्हें आप फॉर्मूलों के साथ फॉर्मेट करना चाहते हैं।
- अब अपने रिबन पर होम टैब पर जाएं।
- फिर स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- उसके बाद, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- परिणामस्वरूप, नया फॉर्मेटिंग नियम बॉक्स अब पॉप अप होगा।
- फिर आप Format values where this formula is true: के तहत फील्ड में फॉर्मूला दर्ज कर सकते हैं।
- आप Format पर क्लिक करके फॉर्मेट स्टाइल भी चुन सकते हैं।
- उपरोक्त सभी चरणों और सेल्स की फॉर्मेटिंग स्टाइल के साथ काम पूरा होने के बाद, इस बॉक्स पर OK पर क्लिक करें।
उदाहरण के लिए, हम इन सभी चरणों का पालन करने के बाद आंकड़े में दिखाए गए निम्नलिखित फॉर्मूला दर्ज करते हैं।
स्प्रेडशीट पर रेंज B3:B6 थी। अब यह इस तरह दिखाई देगी।
यहां, हमने शर्त दर्ज की कि सेल मान 3 से अधिक है। तो इस चयन में जहां 3 से अधिक मान मौजूद हैं, फॉर्मेट लागू होते हैं।
एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का उपयोग करने के 21 उदाहरण
अब इस खंड में, हम एक्सेल में कंडीशनल फॉर्मेटिंग सुविधा में फॉर्मूला का उपयोग करने के विभिन्न उदाहरणों का प्रदर्शन करेंगे। इस सुविधा का उपयोग करने और प्रत्येक को दूसरे के साथ जोड़ने की संभावना बहुत बड़ी है। इसलिए हमने कुछ प्रमुख फॉर्मूलों को कवर करने की कोशिश की है जो हमें लगता है कि आपको जानकारीपूर्ण लग सकते हैं। यदि आपको कोई विशेष चाहिए, तो आप उन्हें ऊपर दी गई तालिका में पा सकते हैं।
1. टेक्स्ट मूल्यों को फॉर्मेट करें
पहले, आइए इस डेटासेट पर विचार करें जिसमें संख्यात्मक और स्ट्रिंग दोनों मूल्य हैं।
फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग की मदद से, हम एक्सेल में स्ट्रिंग मूल्यों को आसानी से अलग कर सकते हैं। ऐसा करने के लिए, हमें ISTEXT फ़ंक्शन का उपयोग करने की आवश्यकता है। एक्सेल में कंडीशनल फॉर्मेटिंग में इस फ़ंक्शन का उपयोग करके फॉर्मेट करने का तरीका देखने के लिए इन चरणों का पालन करें।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=ISTEXT(B5)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
आप देखेंगे कि एक्सेल इस फॉर्मूले को कंडीशनल फॉर्मेटिंग में उपयोग करने के बाद डेटासेट के सभी टेक्स्ट को फॉर्मेट कर देगा।
2. उन सेल्स को हाइलाइट करें जो किसी अन्य सेल के बराबर हैं
अब मिलान मूल्यों के एक अन्य उदाहरण पर नज़र डालते हैं।
इस डेटासेट में डेटा का एक बड़ा संग्रह है। हम अब केवल उनको फॉर्मेट करने जा रहे हैं जो सेल E5 के मान से मेल खाते हैं।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=B5=$E$5
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
इस तरह हम एक्सेल में फॉर्मूला की मदद से कंडीशनल फॉर्मेटिंग का उपयोग करके उन सेल्स को हाइलाइट कर सकते हैं जो किसी अन्य सेल के बराबर हैं।
3. किसी अन्य सेल के आधार पर एक्सेल में कंडीशनल फॉर्मेटिंग
अब बराबर होने से थोड़ा हटते हैं। अब हम इस खंड में किसी अन्य सेल के मान के आधार पर डेटासेट को फॉर्मेट करेंगे कि वह बड़ा या छोटा है। और हम एक्सेल में फॉर्मूला विधि का उपयोग करके कंडीशनल फॉर्मेटिंग से उन्हें हाइलाइट करने जा रहे हैं।
हम इसे निम्नलिखित डेटासेट पर करेंगे।
हम सेल E5 के मान के आधार पर रेंज C5:C9 को फॉर्मेट करने जा रहे हैं।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=C5>$E$5
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें। यह अब डेटासेट होगा।
- इसी तरह, आप कम मूल्यों के लिए उसी प्रक्रिया को दोहरा सकते हैं और इस तरह के डेटासेट के साथ समाप्त हो सकते हैं।
इस तरह हम एक्सेल में किसी अन्य सेल के मान के आधार पर फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का उपयोग कर सकते हैं।
4. एक्सेल में IF फॉर्मूला का उपयोग करके कंडीशनल फॉर्मेटिंग
अब हम देखेंगे कि कैसे हम IF फ़ंक्शन वाले फॉर्मूलों का उपयोग एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला के रूप में कर सकते हैं। हम सीधे कंडीशनल फॉर्मेटिंग बॉक्स में फ़ंक्शन का उपयोग नहीं कर सकते। लेकिन हम फ़ंक्शन का उपयोग एक मान प्राप्त करने के लिए कर सकते हैं जिसका उपयोग हम एक्सेल में कंडीशनल फॉर्मेटिंग का उपयोग करके सेल्स को आगे फॉर्मेट करने के लिए कर सकते हैं।
आइए पहले इस उदाहरण के लिए एक डेटासेट लेते हैं। यह वह है जिस पर हम यह कार्य कर रहे हैं।
चरण:
- सबसे पहले, सेल E5 चुनें और निम्नलिखित फॉर्मूला लिखें।
=IF(D5>C5,"Profit","Loss")
- फिर अपने कीबोर्ड पर Enter दबाएं।
- अब फिल हैंडल आइकन पर क्लिक करें और खींचें ताकि कॉलम के अंत तक फॉर्मूला बाकी सेल्स के लिए दोहराया जा सके।
- अगला, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=E5="Profit"
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
यह अब डेटासेट होगा।
इस तरह हम एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला के लिए IF फ़ंक्शन का उपयोग कर सकते हैं।
5. एकाधिक शर्तों का उपयोग करके सेल्स हाइलाइट करें
अब पहले डेटासेट पर वापस चलते हैं।
अब हम उन सभी सेल्स को हाइलाइट करने जा रहे हैं जो या तो 5, 6 हैं या जिनमें "cat" टेक्स्ट है।
एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला बॉक्स में ऐसा करने के लिए, हमें OR फ़ंक्शन की आवश्यकता है।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=OR(B5=5,B5=6,B5="cat")
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
अब डेटासेट पर एक नज़र डालें।
इस तरह हम एक्सेल में एकाधिक शर्तों के लिए कंडीशनल फॉर्मेटिंग फॉर्मूला में OR फ़ंक्शन का उपयोग कर सकते हैं।
6. डुप्लिकेट पंक्तियों को हाइलाइट करें
आगे बढ़ते हुए, अब हम पूरे पंक्तियों में उन सेल्स को हाइलाइट करने जा रहे हैं जहां पूरी पंक्ति किसी अन्य से मेल खाती है। यह थोड़ा ट्रिकी है। हमें इसके लिए COUNTIF और CONCATENATE फ़ंक्शंस की आवश्यकता होगी।
प्रदर्शन के लिए एक नमूना डेटासेट लेते हैं।
यहां, हम देख सकते हैं कि तीसरी और छठी पंक्ति पूरी तरह मेल खाती है। एक्सेल में कॉलम में डुप्लिकेट मूल्यों को हाइलाइट करने के लिए इन फ़ंक्शंस का उपयोग कैसे करें, यह देखने के लिए इन चरणों का पालन करें।
चरण:
- सबसे पहले, सेल F5 चुनें और निम्नलिखित फॉर्मूला लिखें।
=CONCATENATE(B5,C5,D5,E5)
- फिर Enter दबाएं।
- अब सेल को फिर से चुनें और फिल हैंडल आइकन पर क्लिक करके खींचें ताकि बाकी सेल्स को उनके संदर्भों के लिए फॉर्मूला से भर दिया जाए।
- यह थोड़ा गड़बड़ दिखता है, लेकिन अभी इसे नज़रअंदाज़ करें। अब रेंज B5:B10 चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=COUNTIF($F$5:$F$10,$F5)>1
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें। यह अब डेटासेट होगा।
- अब कॉलम F के हेडर पर राइट-क्लिक करें और संदर्भ मेनू से Hide चुनें।
इसके बाद यह अंतिम स्प्रेडशीट दृश्य होगा।
7. फॉर्मूला वाले सेल मानों को हाइलाइट करें
अब एक ऐसे डेटासेट पर नज़र डालते हैं जिसमें फॉर्मूला हैं।
हम इन सेल्स को हाइलाइट करने के लिए फॉर्मूला का उपयोग करके कंडीशनल फॉर्मेटिंग का उपयोग करने जा रहे हैं। इस कारण से, हमें ISFORMULA फ़ंक्शन का उपयोग करने की आवश्यकता है।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=ISFORMULA(B5)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
यह अब डेटासेट होगा।
8. किसी विशेष क्षेत्र से बिक्री हाइलाइट करें
इस उदाहरण में, हम एक डेटासेट से बिक्री को हाइलाइट करने जा रहे हैं जो किसी विशेष क्षेत्र से संबंधित है। आइए निम्नलिखित डेटासेट लेते हैं।
अब मान लें कि हमें उनको हाइलाइट करना है जो एरिज़ोना से हैं। ऐसा करने के लिए, इन चरणों का पालन करें।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=$D5="Arizona"
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
इन चरणों के परिणामस्वरूप एक्सेल अब एरिज़ोना से बिक्री को चिह्नित करेगा।
9. कॉलम अंतर हाइलाइट करें
हम उन पंक्तियों को भी हाइलाइट कर सकते हैं जिनके कॉलम उनके आसन्न कॉलम से भिन्न हैं। उदाहरण के लिए, आइए निम्नलिखित उदाहरण पर नज़र डालते हैं।
एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग का उपयोग करके उन्हें हाइलाइट करने के लिए, इन चरणों का पालन करें।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=$B5<>$C5
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
यह इन चरणों का अंतिम उत्पाद होगा।
10. गायब मूल्यों को हाइलाइट करने के लिए फॉर्मूला का उपयोग करना
इस खंड में, हम एक्सेल में कंडीशनल फॉर्मेटिंग में गायब मूल्यों को हाइलाइट करने के लिए एक फॉर्मूला का उपयोग करेंगे। आइए उदाहरण के लिए निम्नलिखित डेटासेट लेते हैं।
लिस्ट 2 में कुछ गायब मूल्य हैं जो लिस्ट 1 में उपलब्ध थे। हम उन्हें हाइलाइट करने जा रहे हैं। यह देखने के लिए इन चरणों का पालन करें कि कैसे।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=COUNTIF($D$5:$D$10,$B5)=0
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
एक्सेल इस कंडीशनल फॉर्मेटिंग फॉर्मूला के लिए सेल्स को हाइलाइट करेगा।
11. सेल्स हाइलाइट करने के लिए सिंपल सर्च बॉक्स बनाना
अब एक मजेदार उदाहरण आज़माते हैं, विभिन्न विविधताओं के संयोजन के साथ। यह उसके लिए डेटासेट है।
यहां, हम सेल E4 में एक मान डालेंगे और एक्सेल उसे पिछली रेंज में हाइलाइट करेगा, सभी कंडीशनल फॉर्मेटिंग के साथ फॉर्मूला विधि के साथ। हमें इसके लिए ISNUMBER और SEARCH फ़ंक्शंस के संयोजन की आवश्यकता होगी।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=ISNUMBER(SEARCH($E$4,B5))
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
अब डेटासेट पर नज़र डालें। "game" शब्द वाले टेक्स्ट चिह्नित होंगे।
अगर हम सेल E4 में मान बदलते हैं, उदाहरण के लिए, "health" तो हाइलाइट्स बदल जाएंगे।
12. औसत से कम मूल्यों को हाइलाइट करें
अब पहले के डेटासेट में से एक पर फिर से नज़र डालते हैं।
हम यहां एक्सेल में कंडीशनल फॉर्मेटिंग के साथ फॉर्मूला का एक बुनियादी अनुप्रयोग उपयोग करने जा रहे हैं - हम कॉलम E में उन मूल्यों को हाइलाइट करने जा रहे हैं जो औसत से कम हैं। हमें इसके लिए AVERAGE फ़ंक्शन की आवश्यकता है।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=E5
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
यह डेटासेट में औसत से कम मूल्यों को हाइलाइट करेगा।
13. औसत से अधिक मूल्यों को हाइलाइट करें
पिछले वाले के समान, हम इस खंड में कंडीशनल फॉर्मेटिंग की फॉर्मूला विधि का उपयोग करके औसत से अधिक मूल्यों को हाइलाइट करने जा रहे हैं। इस कार्य के लिए भी हमें AVERAGE फ़ंक्शन का उपयोग करना होगा। डेटासेट वही होगा।
फॉर्मूला और आप इसका उपयोग कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=E5>AVERAGE($E$5:$E$10)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
इस प्रकार एक्सेल कॉलम E में उन सभी मूल्यों को हाइलाइट करेगा जो औसत से अधिक हैं।
14. सटीक मिलान सहित निकटतम मान खोजें
अब एक और अनूठा उदाहरण लेते हैं जो डेटा सफाई और विश्लेषण में व्यावहारिक है। हम एक यादृच्छिक डेटा सेट में एक संख्या के निकटतम मान को खोजने जा रहे हैं।
यह इस उदाहरण का डेटासेट है।
जैसा कि आप आंकड़े से देख सकते हैं, हम उस मान को खोजने जा रहे हैं जो सेल C13 में मान के सबसे करीब है। अगर डेटासेट में वही मान है, तो यह उस सेल को निकटतम मान के बजाय हाइलाइट करेगा। इस उद्देश्य के लिए, हम MIN, ABS, और OR फ़ंक्शंस का उपयोग करेंगे।
यह देखने के लिए इन चरणों का पालन करें कि आप यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हमें कुछ गणनाएं करने की आवश्यकता है। हमें उस डेटा सेट में इस मान से सबसे छोटा अंतर खोजने की आवश्यकता है। इसके लिए सेल C14 चुनें और निम्नलिखित फॉर्मूला लिखें।
=MIN(ABS(B5:D11-(C13)))
- फिर Enter दबाएं।
- अब हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
यह 1 के मान के निकटतम मान को हाइलाइट करेगा, एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला के साथ।
15. शीर्ष 3 मूल्य खोजें
यादृच्छिक डेटा सेट पर वापस चलते हैं।
इस बार हम एक्सेल में कंडीशनल फॉर्मेटिंग के साथ फॉर्मूला का उपयोग करके यहां शीर्ष 3 मूल्यों को खोजने जा रहे हैं। फॉर्मूला में LARGE फ़ंक्शन होता है।
यह देखने के लिए इन चरणों का पालन करें कि हम यह कैसे कर सकते हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=B5>=LARGE($B$5:$D$11,3)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
जैसा कि आप देख सकते हैं, एक्सेल इस प्रकार कंडीशनल फॉर्मेटिंग फॉर्मूला का उपयोग करके रेंज से शीर्ष 3 मूल्यों को चिह्नित करेगा।
16. निचले 3 मूल्य खोजें
फिर से, हम पहले वाले डेटासेट का उपयोग करेंगे। लेकिन इस बार हम उसका उपयोग डेटासेट से निचले 3 मूल्यों को हाइलाइट करने के लिए करेंगे। पहले की तरह ही हमें फॉर्मूला के लिए SMALL फ़ंक्शन की आवश्यकता होगी। यह देखने के लिए कि हम यह कैसे कर सकते हैं, इन चरणों का पालन करें।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=B5<=SMALL($B$5:$D$11,3)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
इस बार, कंडीशनल फॉर्मेटिंग फॉर्मूला के कारण एक्सेल निचले 3 मूल्यों को चिह्नित करेगा।
17. कलर स्केल के साथ तापमान दिखाएं
इस अगले उदाहरण में, आइए एक्सेल में फॉर्मूला और कंडीशनल फॉर्मेटिंग के एक और दिलचस्प उपयोग को आज़माते हैं। उसके लिए यहां डेटासेट है।
हमने दो कॉलम लिए हैं क्योंकि हम सेल E5 में इनपुट के आधार पर रंग बदलने के लिए एक फॉर्मूला का उपयोग करेंगे। साथ ही, डेटासेट की शुरुआत में एक खाली पंक्ति है। हमें उस फॉर्मूला का उपयोग करने के लिए इसकी आवश्यकता होगी जिसका हम उपयोग करने जा रहे हैं। फॉर्मूला में IF और AND फ़ंक्शंस होते हैं। वर्तमान तापमान के आधार पर इन तापमानों की रंग योजना को कैसे बदल सकते हैं, यह देखने के लिए इन चरणों का पालन करें।
चरण:
- सबसे पहले, सेल C6 चुनें और उसमें निम्नलिखित फॉर्मूला लिखें।
=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5
- फिर Enter दबाएं।
- अब सेल को फिर से चुनें और फिल हैंडल आइकन पर क्लिक करके खींचें ताकि कॉलम के बाकी सेल्स के लिए फॉर्मूला दोहराया जा सके।
- अगला, होम के स्टाइल्स ग्रुप से कंडीशनल फॉर्मेटिंग पर जाएं।
- फिर कलर स्केल्स पर माउस घुमाएं।
- उसके बाद, अपनी पसंदीदा कलर स्केल चुनें।
- वर्तमान तापमान के लिए फॉर्मूला पर आधारित कंडीशनल फॉर्मेटिंग का तापमान स्केल अब पूरा हो जाएगा।
- अगर हम सेल E5 में वर्तमान तापमान का मान बदलते हैं, तो तापमान स्केल तदनुसार बदल जाएगा।
इस तरह हम फॉर्मूला और कंडीशनल फॉर्मेटिंग का उपयोग करके एक्सेल में कलर स्केल के साथ तापमान दिखा सकते हैं।
18. कंडीशनल फॉर्मेटिंग के साथ वैकल्पिक पंक्तियों को हाइलाइट करें
अब यादृच्छिक संख्याओं के पिछले डेटासेट में से एक पर वापस चलते हैं।
इस डेटासेट के साथ, हम एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला का उपयोग करके एक रंग के साथ वैकल्पिक पंक्तियों को हाइलाइट करेंगे। फॉर्मूला के लिए, हमें INT और MOD फ़ंक्शंस की आवश्यकता होगी।
ऐसा करने के चरण नीचे दिए गए हैं।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=INT(MOD(ROW(),2))
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
यह एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला का उपयोग करके वैकल्पिक पंक्तियों को हाइलाइट करेगा।
19. त्रुटि वाले सेल्स को हाइलाइट करें
अगला, हम एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला का उपयोग करके त्रुटि वाले सेल्स को हाइलाइट करेंगे। यह शीट में बड़ी मात्रा में डेटा होने पर और जहां मैन्युअल रूप से त्रुटियों को ढूंढना थकाऊ होता है, विशेष रूप से सहायक होता है। प्रदर्शन के लिए, आइए निम्नलिखित डेटासेट पर नज़र डालते हैं।
डेटासेट में दो त्रुटियां हैं। हम कंडीशनल फॉर्मेटिंग फॉर्मूला में ISERROR फ़ंक्शन का उपयोग करके उनका पता लगाने जा रहे हैं। नीचे दिया गया फॉर्मूला एक्सेल में कंडीशनल फॉर्मेटिंग का उपयोग करके उनकी पहचान करने में हमारी मदद करेगा।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=ISERROR(B5)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
कंडीशनल फॉर्मेटिंग फॉर्मूला का उपयोग करने के परिणामस्वरूप एक्सेल इस प्रकार सभी त्रुटियों को हाइलाइट करेगा।
20. कंडीशनल फॉर्मेटिंग के साथ चेकलिस्ट बनाएं
अब एक्सेल में फॉर्मूला के साथ कंडीशनल फॉर्मेटिंग के एक और मजेदार अनुप्रयोग को आज़माते हैं। इस बार हम डेटा के एक सेट के बगल में एक चेकलिस्ट बनाएंगे। विकल्पों के चेक होने पर, मूल डेटा अपना फॉर्मेट बदल देगा। हम निम्नलिखित डेटासेट का उपयोग करेंगे।
यह एक मानक टू-डू सूची है। एक बार जब हम स्टेटस कॉलम में एक चेकबॉक्स चेक करते हैं, तो उसके बगल वाला कार्य अपना फॉर्मेट बदल देगा, मान लीजिए स्ट्राइकथ्रू। मूल विचार चेकबॉक्स के बूलियन मान का उपयोग करके कॉलम B में फॉर्मेट करना है। साथ ही, चेकबॉक्स डालने के लिए हमें डेवलपर टैब की आवश्यकता होगी। अगर आपके रिबन में यह नहीं है, तो अपने रिबन पर डेवलपर टैब प्रदर्शित करने के लिए यहां क्लिक करें।
विस्तार से एक्सेल कंडीशनल फॉर्मेटिंग फॉर्मूला का उपयोग करके चेकलिस्ट बनाने का तरीका देखने के लिए इन चरणों का पालन करें।
चरण:
- सबसे पहले, अपने रिबन पर डेवलपर टैब पर जाएं।
- फिर कंट्रोल्स ग्रुप सेक्शन से इन्सर्ट चुनें।
- उसके बाद, ड्रॉप-डाउन मेनू से चेक बॉक्स (फॉर्म कंट्रोल) चुनें।
- फिर बॉक्स को उसकी उचित जगह पर रखें।
- अगला, बॉक्स पर राइट-क्लिक करें और संदर्भ मेनू से फॉर्मेट कंट्रोल चुनें।
- अब बॉक्स पर कंट्रोल टैब पर जाएं और सेल C5 को इसके लिंक्ड सेल के रूप में चुनें।
- फिर OK पर क्लिक करें।
- अब आइए ऑल्ट टेक्स्ट को हटा दें और इसे सेल के बीच में रखें ताकि यह अच्छा दिखे।
- अब सभी कार्यों के लिए इस प्रक्रिया को दोहराएं।
- एक बार चेक होने पर, इस पर निर्भर करते हुए सेल्स पर TRUE/FALSE मान होगा।
- तो आइए उन्हें अदृश्य बनाने के लिए सफेद फ़ॉन्ट रंग का उपयोग करें।
- अब उस टास्क रेंज को चुनें जिसे आप फॉर्मेट करना चाहते हैं।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=C5=TRUE
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
अब एक बार जब हम किसी टास्क के बगल में एक चेक बॉक्स चेक करते हैं, तो यह उसके बगल वाले टास्क को फॉर्मेट करेगा। हमने एक्सेल में कंडीशनल फॉर्मेटिंग फॉर्मूला की मदद से यह सब किया।
21. एक सप्ताह में सप्ताहांत हाइलाइट करें
आइए एक और उदाहरण आज़माते हैं जहां हम एक्सेल में कैलेंडर सप्ताह में सप्ताहांत को हाइलाइट करने के लिए एक फॉर्मूला का उपयोग करेंगे। हम इस फॉर्मूला को बनाने के लिए OR और WEEKDAY फ़ंक्शंस का उपयोग करेंगे। डेटासेट के लिए, हम निम्नलिखित का उपयोग करेंगे।
अब एक्सेल में फॉर्मूला का उपयोग करके कंडीशनल फॉर्मेटिंग से सप्ताहांत को हाइलाइट कैसे कर सकते हैं, यह देखने के लिए इन चरणों का पालन करें।
चरण:
- सबसे पहले, हेडर्स को छोड़कर डेटासेट में सभी सेल्स चुनें।
- फिर अपने रिबन पर होम टैब पर जाएं।
- उसके बाद, स्टाइल्स ग्रुप सेक्शन से कंडीशनल फॉर्मेटिंग चुनें।
- अगला, ड्रॉप-डाउन मेनू से नया नियम चुनें।
- फॉर्मेटिंग नियम बॉक्स में, पहले Select a Rule Type के तहत Use a formula to determine which cells to format विकल्प चुनें।
- फिर Format values where this formula is true में निम्नलिखित फॉर्मूला डालें।
=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)
- अगला, अपना पसंदीदा फॉर्मेट प्रकार चुनें।
- अंत में, OK पर क्लिक करें।
इस प्रकार एक्सेल में कंडीशनल फॉर्मेटिंग की मदद से कैलेंडर सप्ताह से सप्ताहांत हाइलाइट हो जाएंगे।
निष्कर्ष
तो ये एक्सेल में कंडीशनल फॉर्मेटिंग के लिए फॉर्मूला का उपयोग करने के तरीके और विभिन्न उदाहरण थे। मुझे आशा है कि आपने इस सुविधा के उपयोग का विचार समझ लिया है और अब आप इसे तदनुसार उपयोग कर सकते हैं। उम्मीद है, आपको यह गाइड उपयोगी और जानकारीपूर्ण लगा होगा। यदि आपके कोई प्रश्न या सुझाव हैं तो नीचे कमेंट सेक्शन में हमें बताएं।
इस तरह के और गाइड के लिए Exceldemy.com पर जाएं।
