Excel Baba

आज इस Topic में आप Excel के Conditional Formatting Features को जानेंगे। Conditional Formatting, Excel Spreadsheet के सबसे सरल लेकिन शक्तिशाली विशेषताओं में से एक है। जैसा कि नाम से पता चलता है, आप Excel में Conditional Formatting का उपयोग उन Selected Cells को Formatting करने के लिए कर सकतें हैं, जो किसी निर्दिष्ट Condition को पूरा करते हैं। यह आपको अपने Datasets पर Visual Analysis Layer को जल्दी से जोड़ने की क्षमता देता है। Excel में Conditional Formatting कि सहायता से आप Heat Maps, Increasing / Decreasing Icons, Harvey Ball, आदि बना सकते हैं।

Excel में Conditional Formatting को आप Excel Ribbon के माध्यम से या Excel Conditional Formatting Shortcut के माध्यम से Access कर सकते हैं और अपने Data को आकर्षक बना सकतें हैं। यह Excel के Home Tab के Styles Group में उपलब्ध रहता है।

Highlight Cells Rules

आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग उन Cells को जल्दी से Highlight करने के लिए कर सकते हैं जिनका Value दिए गए Conditions (Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values) के अनुसार हो।

Greater Than/Less Than

आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग उन Cells को जल्दी से Highlight करने के लिए कर सकते हैं जिनका Value इंगित Value से Greater Than या Less Than (अधिक या कम ) हो। ऐसा करने के लिए नीचे Steps दिए गए हैं:

• सबसे पहले आप संपूर्ण Dataset को Select करें।
• Home ⇒ Conditional Formatting (Styles Group) ⇒ Highlight Cells Rules⇒ Greater Than/Less Than

आपके द्वारा चुने गए विकल्प (Greater Than या Less Than) के आधार पर, एक Dialog Box खुलेगा। मैंने यहाँ Highlight Cells Rules का Greater Than विकल्प चुना है। आप Greater Than Dialog Box में Value Enter करें जिस Value से अधिक सभी Values को आपको Highlight करना है, उसके बाद उन Cells पर लागू होने वाले Formatting को Select करें। आप उपलब्ध Default Formatting के आलावा, Excel में उपलब्ध Formatting को Custom Formatting विकल्प की सहायता से Apply कर सकतें हैं। Formatting Select करने के बाद आप OK बटन पर Click करें।

आपका Dataset कुछ इस प्रकार दिखेगा, जो भी Values इंगित Value “70” से अधिक है वे सभी Values Highlighted हो जाएंगे।

Between

आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग उन Cells को जल्दी से Highlight करने के लिए कर सकते हैं जिनका Value दो इंगित Values के बीच में होगा। इसके लिए आपको Between विकल्प चुनना होगा। आपके द्वारा चुने गए Between विकल्प के आधार पर, Between Dialog Box खुलेगा, दो Value Enter करें, उसके बाद उन Cells पर लागू होने वाले Formatting को Select करें। आप उपलब्ध Default Formatting के आलावा, Excel में उपलब्ध Formatting को Custom Formatting विकल्प की सहायता से Apply कर सकतें हैं।Formatting Select करने के बाद आप OK बटन पर Click करें।

आपका Dataset कुछ इस प्रकार दिखेगा, जो भी Values “32” और “75” के बीच में उपलब्ध होगी, वे सभी Values Highlighted हो जाएंगे।

Equal To

आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग उन Cells को जल्दी से Highlight करने के लिए कर सकते हैं जिनका Value इंगित Value के बराबर या समान हैं। इसके लिए आपको Equal To विकल्प चुनना होगा। आपके द्वारा चुने गए Equal To विकल्प के आधार पर, Equal To Dialog Box खुलेगा, Value Enter करें, उसके बाद उन Cells पर लागू होने वाले Formatting को Select करें। आप उपलब्ध Default Formatting के आलावा, Excel में उपलब्ध Formatting को Custom Formatting विकल्प की सहायता से Apply कर सकतें हैं।Formatting Select करने के बाद आप OK बटन पर Click करें।

आपका Dataset कुछ इस प्रकार दिखेगा, जो भी Values इंगित Value “60” के बराबर या समान है वे Values Highlighted हो जाएंगे।

Text That Contains

आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग उन Cells को जल्दी से Highlight करने के लिए कर सकते हैं जिनका Value, Cells मे उपलब्ध Text के अनुसार हो, उदहारण के लिए Marksheet में “Pass/Fail” को Format करने के लिए, इसके लिए आपको Text That Contains विकल्प चुनना होगा। आपके द्वारा चुने गए Text That Contains विकल्प के आधार पर, Text That Contains Dialog Box खुलेगा, Text Enter करें, उसके बाद उन Cells पर लागू होने वाले Formatting को Select करें। आप उपलब्ध Default Formatting के आलावा, Excel में उपलब्ध Formatting को Custom Formatting विकल्प की सहायता से Apply कर सकतें हैं।Formatting Select करने के बाद आप OK बटन पर Click करें।

A Date Occurring

आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग उन Cells को जल्दी से Highlight करने के लिए कर सकते हैं जिनका Value, Cells मे उपलब्ध Date के अनुसार हो, इसके लिए आपको A Date Occurring विकल्प चुनना होगा। आपके द्वारा चुने गए A Date Occurring विकल्प के आधार पर, A Date Occurring Dialog Box खुलेगा, Date Enter करें, उसके बाद उन Cells पर लागू होने वाले Formatting को Select करें। आप उपलब्ध Default Formatting के आलावा, Excel में उपलब्ध Formatting को Custom Formatting विकल्प की सहायता से Apply कर सकतें हैं।Formatting Select करने के बाद आप OK बटन पर Click करें।

A Date Occurring Dialog Box

आपका Dataset कुछ इस प्रकार दिखेगा ।

Duplicate Values

आपको Excel में अगर एक बड़े Dataset पर काम करना हो तो आप ये कभी नहीं चाहेंगे कि उस Dataset में किसी भी प्रकार कि त्रुटि हो, खास कर आप ये नहीं चाहेंगे कि उस Dataset में आप कोई Data दुबारा Enter कर दें। यह आपके लिए एक बड़ी समस्या खड़ी कर सकता है और आपके द्वारा किये गए काम की उपयोगिता तथा आपके काम करने कि क्षमता को भी कम कर सकता है। आप Excel में Conditional Formatting के Highlight Cells Rules का उपयोग Duplicate Values को जल्दी से Highlight करने के लिए कर सकते हैं।

इसे विकल्प ध्यान से पढ़ें क्योंकि यह न केवल आपकी समस्या को हल करेगा बल्कि Interview के समय भी आपकी सहायता करेगा, क्योंकि अकसर जब आप किसी Data Entry या MIS के Interview में जाते हैं तो कई बार Excel में Duplicate Values Highlight करने या उसे हटाने कि प्रक्रिया के बारे में आपसे पूछा जाता हैं।

Excel में Duplicate Values कैसे Highlight करें?

सबसे पहले हम एक Dataset लेंगे, उसके बाद उस Dataset को Select करेंगे, उसके बाद हम Home Tab के Styles Group से Conditional Formatting Option पर Click करेंगे, उसके बाद Highlight Cells Rules Option पर Click करेंगे, उसके बाद Duplicate Values Option पर Click करेंगे।

जैसे ही हम Duplicate Values Option पर Click करेंगे Duplicate Values Dialog Box खुलेगा जो कुछ इस प्रकार का होगा।

Duplicate Values Dialog Box में हम अपने अनुसार Duplicate Values को Highlight करने के लिए Formatting को Select करें। आप उपलब्ध Default Formatting के आलावा, Excel में उपलब्ध Formatting को Custom Formatting विकल्प की सहायता से Apply कर सकतें हैं।Formatting Select करने के बाद आप OK बटन पर Click करें।

Excel में Duplicate Values कैसे हटाएँ?

अब हम Duplicate Values को हटाने कि प्रक्रिया को जानतें हैं, सबसे पहले हम एक Dataset लेंगे, उसके बाद उस Dataset को Select करेंगे, उसके बाद हम Data Tab के Data Tool Group से Remove Duplicates Icon पर Click करेंगे।

जैसे ही हम Remove Duplicates Icon पर Click करेंगे, Remove Duplicates Dialog Box खुलेगा जो कुछ इस प्रकार का होगा।

यहाँ दिए गए Options को जानते हैं, अभी केवल एक Column "F " में Data है इसलिए यहाँ एक Column दिख रहा है अगर आपके पास एक से अधिक Columns हो तो आप उन सब को Select या Unselect कर सकतें हैं, यदि आपके Dataset में Header या शीर्षक हो तो आप "My data has headers" Check Box को Select कर सकते हैं, अंत में OK बटन पर Click करें।

जैसे ही आप OK बटन पर Click करेंगे एक Message दिखेगा, जिसमें हमारे Data के बारे में Details Mentioned रहेगा।

Top/Bottom Rules

आप Excel में Conditional Formatting के Top/Bottom Rules विकल्प की सहायता से Dataset के Top 10 / Bottom 10 या Top 10% / Bottom 10% को Highlight कर सकते हैं। यह उन स्थितियों में मददगार हो सकता है, जहां आप Top के 10 Items या Top 10% को Highlight कर सकते हैं। इसी तरह, आप किसी Dataset में Bottom के 10 Items या Bottom 10% को Highlight कर सकते हैं। ऐसा करने के लिए नीचे Steps दिए गए हैं:

• सबसे पहले संपूर्ण Dataset का चयन करें।
• Home ⇒ Conditional Formatting (Styles Group) ⇒ Top/Bottom Rules ⇒ Top 10/Bottom 10 or Top 10% /Bottom 10%

इसी प्रकार आप Excel में Conditional Formatting के Top/Bottom Rules विकल्प से उन दिए हुए संख्याओं को Highlight कर सकते हैं जिनका मान Average Value से अधिक या Average Value से कम हो, तो इसकेलिए आपको Top/Bottom Rules विकल्प के Above Average या Below Average विकल्प का उपयोग करना होगा। नीचे दिखाए गए Dataset का औसत "8" है।

Data Bars

आप Excel में Conditional Formatting के Data Bars विकल्प की सहायता से आप Dataset में Value को दर्शाने के लिए रंगीन Data Bars जोड़ सकते हैं। Values जितना अधिक होगा, Data Bar उतना लंबा होगा। ऐसा करने के लिए नीचे Steps दिए गए हैं:

• सबसे पहले संपूर्ण Dataset का चयन करें।
• Home ⇒ Conditional Formatting (Styles Group) ⇒ Data Bars ⇒ Gradient Fill /Solid Fill

Color Scales या Heat Map

आप Excel में Conditional Formatting के Color Scales विकल्प की सहायता से Dataset में Values को दर्शाने के लिए Colour Scales या Heat Map बना सकते हैं, जहां रंग एक Cell में Values का प्रतिनिधित्व करता है। रंग इंगित करता है कि प्रत्येक Cell का मान उस सीमा के भीतर कहां आता है। उदाहरण के लिए, आप एक Color Scales बना सकते हैं, जहां उच्चतम Values वाला Cells हरे रंग का होता है, Middle (मध्य) Values वाला Cells पीले रंग का, और कम Values वाला Cells लाल रंग का होता है। ऐसा करने के लिए नीचे Steps दिए गए हैं:

• सबसे पहले संपूर्ण Dataset का चयन करें।
• Home ⇒ Conditional Formatting (Styles Group) ⇒ Color Scales

Icon Sets

आप Excel में Conditional Formatting के Icon Sets की सहायता से Dataset में Values को दर्शाने के लिए आप Icon Sets का उपयोग कर सकतें हैं। Values जैसा होगा Icons भी उसी अनुसार होगा। ऐसा करने के लिए नीचे Steps दिए गए हैं:

• सबसे पहले संपूर्ण Dataset का चयन करें।
• Home ⇒ Conditional Formatting (Styles Group) ⇒ Icon Sets

New Rule

आप Excel में Conditional Formatting के New Rule विकल्प की सहायता से आप अपने Dataset को Formula के माध्यम से Format कर सकते हैं, इसका मतलब यह है कि Formula सभी Cells की जाँच करेगा और यदि Formula True Output लौटाता है, तो Conditional Formatting उन सभी Cells पर लागू होगा। आइए हम इसे एक उदहारण से समझते हैं, अगर आप अपने Dataset के Alternate Odd Rows को Highlight करना चाहतें हैं तो आप इसे दो तरीकों से कर सकतें हैं। पहला और सबसे तेज़ तरीका यह है कि आप अपने Selected Tabular Data को Excel Table में बदल दें (Ctrl+T)। यह स्वचालित रूप से Alternate Rows को Highlight कर देगा। लेकिन, इसे हम दूसरे तरीके Conditional Formatting कि सहायता से करेंगे, आइए उसे जानतें हैं। मान लीजिए कि आपके पास नीचे दिखाए अनुसार एक Dataset है:

नीचे Excel में Conditional Formatting का उपयोग करके Alternate Rows को Highlight करने के Steps दिए गए हैं।

• सबसे पहले Dataset को Select करें।
• Home ⇒ Conditional Formatting (Styles Group) ⇒ New Rule

Formulas के साथ Conditional Formatting

जैसे ही आप New Rule पर Click करेंगे New Formatting Rule Dialog Box खुलेगा, उस Dialog Box में आपको Select a Rule Type से "Use a formula to determine which cells to format” को Select करना है।

• “Edit the Rule Description Section” से “Format Values where this Formula is true” में निम्नलिखित Formula दर्ज करें:

• जैसे ही आप “Format“ पर Click करेंगे FormatCellsDialog Box खुलेगा, उस Dialog Box में उपलब्ध Formatting Options से आप कोई भी Format Select करें , इस उदहारण के लिए मैंने Fill Option से हरे Colour को चुना है।FormattingSelect करने के बाद OK बटन पर Click करें।

• जैसे ही आप “OK“ बटन पर Click करेंगे आप पुनः "New Formatting Rule Dialog Box पर आ जाएंगे, उस Dialog Box में आपको आपके द्वारा लगाए गए Formatting का Preview दिखेगा जो कि आपके Condition के अनुरूप Select किये गए Dataset पर लागु होगा, अब बस आपको OK बटन पर Click करना है।

आप कई मामलों में अपने अनुसार Formulas उपयोग कर सकते हैं। आपको केवल Conditional Formatting में प्रासंगिक Formula का उपयोग करने की आवश्यकता है। यहाँ कुछ उदाहरण हैं:

• Alternate Even Rows को Highlight करें : ISEVEN(ROW())
• हर तीसरी पंक्ति को Highlight करें : MOD(ROW(), 3) = 0

Excel में Conditional Formatting कैसे हटाएं?

एक बार लागू होने के बाद, Conditional Formatting तब तक बना रहता है जब तक आप इसे Manual रूप से नहीं हटातें । एक सर्वोत्तम अभ्यास के रूप में, Conditional Formatting को केवल उन Cells पर लागू करें जहाँ आपको इसकी आवश्यकता है। Conditional Formatting को हटाने के लिए उन Cells का चयन करें जिनसे आप Conditional Formatting को हटाना चाहते हैं।

Home ⇒ Conditional Formatting (Styles Group) ⇒ Clear Rules ⇒ Clear Rules from Selected Cells / Clear Rules from Entire Sheet

यदि आप संपूर्ण Worksheet से Conditional Formatting हटाना चाहते हैं, तो “Clear Rules from Entire Sheet” Option का चयन करें।

Excel में Conditional Formatting के बारे में जानने के लिए महत्वपूर्ण बातें!

• Conditional Formatting आपके Worksheet को Slow कर देता है इसलिए जरूरत पड़ने पर ही इसका इस्तेमाल करें।
• जब आप किसी Cell को Copy करते हैं जिसमें Conditional Formatting होता है और उसे कहीं Paste करते हैं तो, तो Conditional Formatting भी Paste हो जाता है।
• यदि आप Cells के एक ही Data पर कई नियम लागू करते हैं, तो सभी नियम सक्रिय रहते हैं। किसी भी Overlap के मामले में, पिछले लागू नियम को वरीयता दी जाती है। हालाँकि, आप Conditional Formatting Rules Manager कि सहायता से Formatting Order बदल सकते हैं।