समझिए
1. Volatile functions
Volatile फ़ंक्शन वर्कबुक में कुछ भी बदलने पर दोबारा गणना होता है — और उस पर निर्भर हर फ़ॉर्मूला भी।
| Volatile | Non-volatile विकल्प |
|---|---|
OFFSET (dynamic ranges) |
Tables, INDEX(A:A,1):INDEX(A:A,n), # वाले spills, TAKE/DROP |
INDIRECT (टेक्स्ट रेफ़रेंस) |
असली रेंज पर INDEX/CHOOSE/XLOOKUP |
TODAY(), NOW() |
एक सेल inp_Today (या Power Query की refresh तारीख़) जिसे हर जगह रेफ़र करें |
RAND, RANDARRAY, RANDBETWEEN |
बनाने के बाद paste values |
CELL, INFO |
डैशबोर्ड में बचें |
एक TODAY() ठीक है; 5,000 फ़ॉर्मूले जो हर एक TODAY() या OFFSET बुलाएँ, ठीक नहीं।
2. स्पीड के दूसरे दुश्मन
- Array फ़ॉर्मूलों में पूरे कॉलम के रेफ़रेंस (SUMPRODUCT/FILTER के अंदर
A:A) — Table कॉलम इस्तेमाल करें। - दोहराई गणनाएँ — 50 जगह लिखा वही SUMIFS; CALC में एक बार गणना करके रेफ़र करें (दोहराए हिस्सों के लिए LET)।
- बिना सॉर्ट की बड़ी रेंज पर बार-बार lookups — helper कॉलम में एक बार XLOOKUP, या Data Model relationship।
- कॉपी-पेस्ट से टूटे हज़ारों conditional formatting rules (मॉड्यूल 3 लेसन 3)।
- बहुत सारी linked pictures और जटिल shapes।
3. Calculation mode
Formulas → Calculation Options:
- Automatic — सामान्य।
- Automatic except for data tables — अगर What-If data tables इस्तेमाल करते हैं।
- Manual — भारी मॉडल बनाते समय; F9 (सब गणना) या Shift + F9 (active शीट) दबाएँ। डैशबोर्ड सेव करने से पहले हमेशा Automatic पर वापस करें, वरना दर्शक पुराने नंबर देखते हैं — मोड फ़ाइल के साथ सेव होता है।
Microsoft 365: Review → Check Performance खाली सेल पर लगी फ़ॉर्मेटिंग ढूँढकर हटाता है जो फ़ाइल फुलाती है।
4. फ़ाइल का आकार घटाना
| काम | कहाँ |
|---|---|
| Used range रीसेट: डेटा के आगे की खाली रो/कॉलम हटाएँ (Ctrl + End से जाँचें), फिर सेव करें | हर शीट |
| पिवट cache: Save source data with file हटाएँ (इसकी जगह खुलने पर refresh) — या डेटा Data Model में रखें | PivotTable Options → Data |
| बड़ी टेबल शीट की जगह Data Model / connection only में लोड करें | Power Query → Load To |
| तस्वीरें compress करें (Picture Format → Compress Pictures, 150 ppi, कटे हिस्से हटाएँ) | तस्वीरें |
| बेकार नाम, styles और छिपी पुरानी शीट हटाएँ | Name Manager; Cell Styles |
| .xlsb (binary) में सेव करें — अक्सर 30–50% छोटी और जल्दी खुलती है; मैक्रो रखती है | File → Save As |
पहले और बाद में नापें: फ़ाइल का आकार, और stopwatch से गणना का समय (या VBA में Application.CalculateFull का समय)।
5. झटपट performance जाँच
- फ़ॉर्मूलों में OFFSET, INDIRECT, TODAY, NOW खोजें (Ctrl + F, Look in: Formulas)।
- Non-volatile रूप से बदलें।
- हर शीट पर Ctrl + End; used range घटाएँ।
- Conditional formatting rules साफ़ करें।
- ऊपर के अनुसार पिवट cache और Power Query loads।
- Calculation को Automatic करें, सेव करें, आकार की तुलना करें।
आम गलतियाँ
हर जगह OFFSET वाली dynamic named ranges। फ़ाइल Manual calculation में छोड़ देना। 50 MB फ़ाइलें क्योंकि 50,000 कच्ची रो दो बार सेव हैं (शीट + पिवट cache)।