Excel में conditional formatting इस्तेमाल करने के लिए, अपने cells select करें, Home → Conditional Formatting पर जाएँ, और कोई rule चुनें, जैसे Highlight Cells Rules → Greater Than, Data Bars या Color Scales। जो चीज़ें menu में नहीं हैं, जैसे पूरी row highlight करना या दो columns की तुलना करना, उनके लिए New Rule → Use a formula to determine which cells to format चुनें और ऐसा formula टाइप करें जो उन cells के लिए TRUE लौटाए जिन्हें format करना है।
ज़्यादातर समस्याएँ एक ही बारीकी से आती हैं: formula वाले rule में $ चिह्न तय करते हैं कि formula किन cells को देखता है। उन्हें सही रखें (आम तौर पर $C2, यानी column locked और row खुली) तो rule हर row पर काम करता है। ग़लत रखें तो Excel ग़लत cells highlight करता है, या कोई भी नहीं।
इस article में Excel के menu और button के नाम English version के हिसाब से दिए गए हैं। Formulas में Excel के English function नाम (जैसे AND, OR, TODAY, MOD, TRIM) इस्तेमाल हुए हैं।
| आप क्या चाहते हैं | कहाँ मिलेगा | उदाहरण |
|---|---|---|
| किसी संख्या से ऊपर, नीचे या बीच के values highlight करना | Highlight Cells Rules | 5,000 से बड़ी राशियाँ |
| ख़ास text या तारीख़ें highlight करना | Highlight Cells Rules → Text that Contains / A Date Occurring | "Overdue", या पिछले 7 दिन की तारीख़ें |
| दोहराई गई entries ढूँढना | Highlight Cells Rules → Duplicate Values | दोहराए गए email addresses |
| सबसे ऊँचे या सबसे नीचे values highlight करना | Top/Bottom Rules | शीर्ष 10 items, औसत से ऊपर |
| आकार या रुझान एक नज़र में दिखाना | Data Bars, Color Scales, Icon Sets | sales cells के अंदर bars |
| पूरी row highlight करना या cells की तुलना करना | New Rule → Use a formula… | =$C2="Overdue" |
Built-in conditional formatting rules इस्तेमाल करें
ये steps Windows पर Excel for Microsoft 365, 2024, 2021 और 2019 में एक जैसे हैं।
- जिन cells को format करना है उन्हें select करें, जैसे Amount column (D2:D10), header के बिना।
- Home → Conditional Formatting पर जाएँ। Windows पर आप
Alt,H,Lको एक के बाद एक भी दबा सकते हैं (यह sequence English Excel के लिए है)। - Highlight Cells Rules पर pointer ले जाएँ और कोई rule चुनें:
- संख्याओं के लिए Greater Than, Less Than, Between या Equal To
- "Overdue" जैसे शब्दों के लिए Text that Contains
- तारीख़ों के लिए A Date Occurring, जैसे Yesterday, In the last 7 days, Next week या This month
- दोहराव चिह्नित करने के लिए Duplicate Values (या drop-down को Unique पर बदलें)
- Value टाइप करें, with सूची से कोई format चुनें (जैसे Light Red Fill with Dark Red Text) या Custom Format।
- OK पर click करें।
Menu का बाकी हिस्सा भी इसी तरह काम करता है:
- Top/Bottom Rules Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, या Above Average और Below Average values को highlight करते हैं। आप 10 को किसी भी संख्या में बदल सकते हैं।
- Data Bars हर cell के अंदर एक bar खींचते हैं, बड़े values के लिए लंबा (Gradient Fill या Solid Fill)।
- Color Scales cells को एक रंग से दूसरे रंग तक shade करते हैं, जैसे कम के लिए लाल और ज़्यादा के लिए हरा।
- Icon Sets हर value के range में स्थान के आधार पर arrows, traffic lights या ratings जोड़ते हैं।
Duplicates ढूँढने या हटाने के लिए Excel में duplicates हटाने की guide देखें।
किसी cell की value के आधार पर पूरी row highlight करें
Built-in rules सिर्फ़ उन्हीं cells को format करते हैं जो शर्त पूरी करते हैं। जब Status में "Overdue" लिखा हो तब पूरी row का रंग बदलने के लिए आपको formula वाला rule चाहिए। मान लें आपका data A1:D100 में है: A में Client, B में Due date, C में Status और D में Amount।
- Header row के बिना पूरी data range select करें, ऊपर-बाएँ cell से शुरू करके: A2 पर click करें और D100 तक drag करें।
- Home → Conditional Formatting → New Rule पर जाएँ।
- Select a Rule Type के नीचे Use a formula to determine which cells to format चुनें।
- Format values where this formula is true में टाइप करें:
=$C2="Overdue" - Format पर click करें, Fill tab खोलें, हल्का लाल रंग चुनें और OK पर click करें।
- जाँचें कि Preview में आपका format दिख रहा है, फिर OK पर click करें।
अब हर वह row जिसके column C में "Overdue" लिखा है, A से D तक लाल हो जाती है, और status बदलने पर रंग भी साथ बदलता है। यह तुलना case-sensitive नहीं है। अगर लोग status हाथ से टाइप करते हैं, तो drop-down list वर्तनी को एक जैसा रखती है ताकि rule कभी कोई row न चूके। (Formula में text वही होना चाहिए जो आपकी sheet में लिखा है; आपकी sheet में status हिंदी में है तो formula में भी वही शब्द लिखें।)
$ चिह्न क्यों मायने रखता है (सबसे आम ग़लती)
Excel आपका formula active cell के लिए लिखता है, यानी आपके selection का पहला cell (ऊपर के उदाहरण में A2), फिर उसे बाकी हर cell के लिए ठीक वैसे ही adjust करता है जैसे formula को नीचे और बग़ल में copy करने पर होता है।
| Reference | Rule जब range में आगे बढ़ता है | किसके लिए इस्तेमाल करें |
|---|---|---|
$C2 | Column C ही रहता है, row बदलती है | पूरी row के rules (आम पसंद) |
C2 | Column और row दोनों बदलते हैं | किसी cell को उसके बग़ल वाले cell के आधार पर format करना |
$C$2 | कभी नहीं बदलता | एक अकेला threshold या input cell |
C$1 | Column बदलता है, row 1 ही रहती है | हर column की तुलना row 1 की value से करना |
=C2="Overdue" के साथ, cell A2, C2 को जाँचता है लेकिन B2, D2 को जाँचता है, इसलिए row का सिर्फ़ एक हिस्सा highlight होता है। =$C$2="Overdue" के साथ हर cell C2 को जाँचता है, इसलिए पूरी range या तो पूरी लाल होती है या बिल्कुल नहीं।
ध्यान रखें: अगर आप formula टाइप करते समय किसी cell पर click करते हैं, तो Excel $C$2 जैसा absolute reference डाल देता है। $C$2, C$2, $C2 और C2 में घूमने के लिए F4 दबाएँ (Mac पर Cmd+T या Fn+F4) जब तक वह न मिल जाए जो चाहिए।
और formula उदाहरण
इन formulas के साथ भी वही steps अपनाएँ। सब मानकर चलते हैं कि आपका selection row 2 से शुरू होता है।
किसी दूसरे cell के आधार पर cell format करें। किसी sales sheet में, जहाँ column D में Actual और column E में Target है, D2:D100 select करें और यह इस्तेमाल करें:
=$D2<$E2
अपने target से नीचे का हर actual लाल हो जाता है। पूरी row highlight करने के लिए इसकी जगह A2:E100 select करें।
एक input cell से तुलना करें। H1 में एक threshold रखें, D2:D100 select करें और =$D2>$H$1 इस्तेमाल करें। $H$1 पूरी तरह absolute है, इसलिए हर row उसी cell को देखती है।
बीती तारीख़ें highlight करें। A2:D100 select करें और यह इस्तेमाल करें:
=$B2<TODAY()
खाली cells शून्य गिने जाते हैं (1900 की एक तारीख़), इसलिए खाली due dates भी highlight हो जाती हैं। खाली cells और चुकाए गए invoices को छोड़ने के लिए =AND($B2<>"",$B2<TODAY(),$C2<>"Paid") इस्तेमाल करें।
हर दूसरी row shade करें। अपना data select करें और यह इस्तेमाल करें:
=MOD(ROW(),2)=0
यह सम (even) rows को shade करता है (विषम rows के लिए =1)। Home → Format as Table में Banded Rows बिना rule के यही कर देता है।
शर्तें मिलाएँ। AND या OR इस्तेमाल करें, जैसे बड़े pending invoices के लिए =AND($C2="Pending",$D2>5000)। आपको IF की ज़रूरत नहीं है: formula को बस TRUE या FALSE लौटाना होता है।
Rules, rule का क्रम और Stop If True संभालें
Conditional Formatting Rules Manager खोलने के लिए Home → Conditional Formatting → Manage Rules पर जाएँ।
- Show formatting rules for में sheet के हर rule को देखने के लिए This Worksheet चुनें।
- Edit Rule या Delete Rule इस्तेमाल करें, या Applies to में किसी rule की range ठीक करें (जैसे
=$A$2:$D$100) और Apply पर click करें। - Rules का क्रम बदलने के लिए Move Up और Move Down arrows इस्तेमाल करें। सूची में ऊपर के rules जीतते हैं।
अगर दो rules एक ही चीज़ अलग-अलग सेट करें, जैसे दो fill colors, तो ऊपर वाला rule जीतता है। अगर वे टकराते नहीं (एक font को bold करता है, दूसरा cell को लाल भरता है), तो दोनों लागू होते हैं। Rule की formatting आपके हाथ से की गई formatting को भी ओवरराइड कर देती है।
Stop If True पर tick लगाने से, उस rule के true होते ही Excel नीचे के rules जाँचना बंद कर देता है। यह "अगर Status Paid है तो और कुछ मत दिखाओ" जैसे अपवादों के लिए काम का है। यह data bar, color scale या icon set rules के लिए उपलब्ध नहीं है।
Formatting हटाने के लिए Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells या Clear Rules from Entire Sheet इस्तेमाल करें।
Conditional formatting को दूसरे cells में copy करें
- Format Painter: किसी formatted cell को select करें, Home → Format Painter पर click करें, फिर नए cells पर drag करें। कई ranges पर लगाने के लिए उस पर double-click करें; रोकने के लिए
Escदबाएँ। - Paste Special: cell को copy करें, लक्ष्य select करें, फिर Home → Paste → Paste Special → Formats।
- इसकी जगह rule को बढ़ाएँ: Manage Rules में Applies to range बदलें। इससे कई copies की जगह एक साफ़ rule रहता है।
Excel for Mac और Excel for the web
Excel for Mac में वही Home → Conditional Formatting menu है। Formula वाले rule के लिए New Rule चुनें, Style को Classic पर बदलें, उसके नीचे वाले box में Use a formula to determine which cells to format चुनें, formula टाइप करें, और Format with को Custom Format पर सेट करें।
Excel for the web भी Home → Conditional Formatting इस्तेमाल करता है, लेकिन rules dialog की जगह Conditional Formatting pane में खुलते हैं। Formula वाले rule के लिए New Rule चुनें और rule type को Formula पर सेट करें। Desktop पर बनाए गए rules browser में काम करते हैं और इसका उलटा भी सच है।
Conditional formatting काम क्यों नहीं कर रही
यह ग़लत rows highlight करती है (एक row का फ़र्क)। अगर आपका selection A2 से शुरू होता है, तो formula में row 2 का reference होना चाहिए। यह आम तौर पर तब होता है जब आप header row शामिल कर लेते हैं या नीचे से ऊपर की ओर select करते हैं।
सिर्फ़ एक column highlight होता है, या सब कुछ। Dollar signs जाँचें। पूरी row के rules के लिए C2 या $C$2 की जगह $C2 इस्तेमाल करें।
संख्याएँ text के रूप में सेव हैं। Import किए values संख्या जैसे दिख सकते हैं पर होते text हैं (अक्सर कोने में छोटे हरे त्रिकोण के साथ), इसलिए Greater Than जैसे rules ग़लत व्यवहार करते हैं। Cells select करें, चेतावनी वाले icon पर click करें और Convert to Number चुनें।
Text मेल नहीं खाता। अंत में एक space ("Overdue ") या अलग वर्तनी "Overdue" के बराबर नहीं होगी। Data को TRIM से साफ़ करें, या Equal To की जगह Text that Contains इस्तेमाल करें।
Rules एक-दूसरे पर चढ़ते हैं या टकराते हैं। Manage Rules खोलें, This Worksheet दिखाएँ, और क्रम व Applies to ranges जाँचें। Copy, paste और rows जोड़ने से अक्सर एक rule कई टुकड़ों में बँट जाता है।
Workbook धीमा हो गया। सैकड़ों बँटे हुए rules, पूरे columns पर rules (=$A:$D) और TODAY() या INDIRECT जैसे volatile functions, सब recalculation का काम बढ़ाते हैं। दोहराव हटाएँ और Applies to को उन्हीं rows तक सीमित रखें जो आप इस्तेमाल करते हैं।
अक्सर पूछे जाने वाले सवाल
Excel में किसी cell की value के आधार पर row कैसे highlight करें? Data को headers के बिना select करें, Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format चुनें, =$C2="Overdue" डालें, और कोई fill चुनें। Column अक्षर से पहले का $ पूरी row का रंग बदल देता है।
किसी दूसरे cell के आधार पर conditional formatting कैसे लगाएँ? उस cell की ओर इशारा करने वाला formula rule इस्तेमाल करें, जैसे target से नीचे के values highlight करने के लिए =$D2<$E2, या हर row की तुलना एक input cell से करने के लिए =$D2>$H$1।
क्या conditional formatting किसी दूसरी sheet का हवाला दे सकती है? हाँ, जैसे =$D2>Settings!$B$1। दूसरे workbooks के हवाले की अनुमति नहीं है।
क्या मैं conditional formatting के रंग से sort या filter कर सकता हूँ? हाँ। Data → Filter चालू करें, column के filter arrow को खोलें और Filter by Color या Sort by Color चुनें। यह rules से लगाए गए रंगों के साथ भी काम करता है।
Data हटाए बिना conditional formatting कैसे हटाएँ? Cells select करें और Home → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells चुनें। आपके values जैसे हैं वैसे रहते हैं।
AI से पूछें कि क्या highlight करना चाहिए
Conditional formatting वही दिखाती है जो आपने पहले से तय किया है कि ढूँढना है। CoreGPT for Excel उलटे तरीके से काम करता है: पूछें "Q3 sales में क्या अलग दिख रहा है?" और वह आपका data पढ़कर बेहतर प्रदर्शन करने वालों, असामान्य गिरावटों और रुझानों की ओर इशारा करता है, ताकि आपको पता चले कि कौन-से rules बनाने लायक हैं। यह शुरू करने के लिए free है और Excel for Windows, Mac व web में चलता है।
CoreGPTमुख्य बातें:
- $184K के साथ यूरोप सबसे आगे, टारगेट से 23% ऊपर
- 6 में से 4 क्षेत्रों ने टारगेट पार किया
- उत्तर अमेरिका टारगेट से $7K पीछे है
आज़माएँ: CoreGPT for Excel
और पढ़ें:
