A large excel file slows down when analysts rely on the wrong formulas and file settings. The essential functions every analyst needs are SUMIFS, VLOOKUP, COUNTIFS, DATEDIF, and SUMPRODUCT, combined with binary file format savings and manual calculation mode to keep even a large excel file running fast.
=((New Value - Old Value) / Old Value) * 100, formatted as a percentage.=VLOOKUP(A2,Sheet2!$A:$B,2,0).=SUMPRODUCT(1/COUNTIF(range,range)) or a PivotTable for faster results on large datasets.The growth formula in Excel is the first thing most finance and business analysts learn, and most people still get it slightly wrong. The correct growth percentage formula in Excel is =((New-Old)/Old)*100. If you want Excel to display the % sign automatically, skip the *100 and just format the cell as Percentage.
For growth-degrowth analysis, wrap the formula in an IF statement: =IF(New>Old,"Growth","Degrowth")&" "&TEXT(ABS((New-Old)/Old),"0.00%"). This gives you a clean, readable output in one cell, which is exactly what a reporting dashboard needs.
The SUM shortcut key in Excel is Alt + =. Press it on any empty cell below or to the right of a data range and Excel auto-selects the range for you. This is also called Auto Sum. For larger blocks, select the entire range including the blank totals row and column, then press Alt + = once to fill every total simultaneously.
The excel summation formula =SUM(A1:A100) is basic, but SUMIF and SUMIFS are where analysts earn their pay. =SUMIFS(Sales,Region,"South",Month,"March") lets you aggregate data across two conditions in one shot, no pivot table required.
The Name Box sits at the top-left of your screen, just above column A. Type a cell address like C1048576 and press Enter to jump there instantly. In a large excel file with thousands of rows, this beats scrolling every time.
A few shortcuts worth memorising: Alt + Enter inserts a line break inside a cell. Ctrl + Shift + End jumps to the last used cell, which tells you the actual size of your dataset instantly.
VLOOKUP is still the most searched Excel function in India, according to Google Trends data for India, 2024. It is not perfect, but it works, and every analyst needs to know it cold. Understanding it well also prepares you for the kinds of questions that come up in data analyst interviews.
To use VLOOKUP across sheets, your formula looks like this: =VLOOKUP(A2,Sheet2!$A$1:$B$500,2,0). The sheet name followed by an exclamation mark tells Excel exactly where to look. If the sheet name has spaces, wrap it in single quotes: 'Sales Data'!$A$1:$B$500.
For matching across two entirely different workbooks, the syntax extends to [WorkbookName.xlsx]SheetName!$A:$B. Keep both files open while building the formula, or Excel will show a broken reference when the second file is closed.
Excel has no native COUNTDISTINCT function outside of Power Pivot. The workaround formula is =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)). This works by dividing 1 by the count of each value’s occurrences and summing the result, which always equals the number of unique entries.
If your range has blank cells, use the safer version: =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). For very large datasets, a PivotTable with the Distinct Count value field setting (available when you add data to the Data Model) is faster and less prone to errors.
COUNTIFS is COUNTIF’s more powerful sibling. =COUNTIFS(Region,B2,Status,"Active") counts only rows where both conditions are true. Analysts at Indian companies using tools like SAP, Salesforce, or Tally often export data to Excel and then use COUNTIFS to segment customer lists before handing off to management.
| Formula | Use Case | Speed on 100K Rows | Notes |
|---|---|---|---|
| SUMIF | Single-condition aggregation | Fast | One criteria range only |
| SUMIFS | Multi-condition aggregation | Fast | Up to 127 criteria pairs |
| VLOOKUP | Match and retrieve a value | Moderate | Left-to-right only; use INDEX-MATCH for flexibility |
| COUNTIFS | Count rows meeting conditions | Fast | Works across non-adjacent ranges |
| SUMPRODUCT | Array-style calculations | Slow on 500K+ rows | Replace with Power Query for big files |
Date-related errors are one of the top reasons Indian analysts waste hours re-cleaning data. When data comes from ERP systems, CRMs, or government portals, dates and times are often stored in a single cell, which breaks every downstream formula.
Excel stores date-time values as decimal numbers. The integer part is the date; the decimal part is the time. To extract just the date: =INT(A2), then format the cell as Date. To extract just the time: =A2-INT(A2), then format as Time.
Text to Columns (Data tab → Text to Columns → Delimited → Space) also works when the date and time are separated by a space character in a text-formatted cell. This is the fastest manual method for a one-time cleanup on a large excel file.
Use the DATEDIF function: =DATEDIF(BirthDate,TODAY(),"Y") returns complete years. For a full DD/MM/YYYY display of the birth date, make sure the cell is formatted as DD/MM/YYYY under Format Cells → Number → Date → select the Indian format. DATEDIF is an undocumented function in Excel, meaning it works but does not appear in autocomplete. Type it manually.
For HR teams at Indian companies running payroll or compliance checks, combining DATEDIF with conditional formatting to flag employees approaching retirement age is a genuine time-saver that takes about ten minutes to set up.
A large excel file above 50 MB starts to cause real performance problems on most office laptops. According to a 2023 survey by Spreadsheeto, 68% of Excel users report slow performance as their biggest daily frustration. The good news is that most slowdowns are fixable with a few deliberate choices.
If you are working toward a career in data, understanding these optimisation habits puts you ahead of the curve. Check out the top data science tools companies are actually using to see where Excel sits alongside Python, SQL, and Power BI in real workflows.
Saving as .xlsb (Excel Binary Workbook) instead of .xlsx can reduce file size by 40% to 75%, according to benchmarks published by Excel Campus (excelcampus.com, 2023). The trade-off is minor: xlsb files cannot be opened by some third-party tools that parse XML. For internal reporting files, it is almost always the right call.
The default file extension for Excel workbooks is .xlsx. Excel files saved in the older format use .xls. By default, a new Excel workbook contains 1 sheet (this changed from 3 sheets in Excel 2016). You can add up to 255 sheets per workbook, though performance degrades well before that limit.
Follow these steps to remove a formula from a cell while keeping its calculated result:
Knowing how to remove a formula in Excel without accidentally deleting data is a basic skill for anyone sharing reports with stakeholders who should not see your calculation logic. This is especially useful when sending files outside your organisation.
For data connected to external sources (Power Query, SQL, web), press Ctrl + Alt + F5 to refresh all connections at once. For a single query, go to Data → Refresh. If your file has no external connections, F9 recalculates all formulas manually, which is useful when automatic calculation is turned off to speed up a large excel file.
Turning off automatic calculation (Formulas → Calculation Options → Manual) is one of the best performance hacks for large workbooks. Just remember to press F9 before saving, or your file will save with stale values.
If you are thinking about building these skills into a full data career, 3.0 University has a clear guide on how to become a data scientist and what the scope of data science looks like in India right now. Excel is usually the first tool in that journey, not the last.
For professionals thinking about where this leads, the best career options in 2026 for students and professionals still list data analytics and AI among the highest-paying paths. Excel proficiency is table stakes for all of them. If you are already in data science and want to move further, read about shifting your career from data science to AI and machine learning.
Building your Excel skills is a smart first step, but it pairs best with structured learning. Explore the top online courses for high-salary jobs in 2025 to see what certifications employers actually look for. 3.0 University offers online certification programs in Cybersecurity, Ethical Hacking, AI, Blockchain, and Web3, built for students, working professionals, and career switchers who want practical, industry-ready skills they can apply from day one.
The growth percentage formula in Excel is =((New Value - Old Value) / Old Value) * 100. If you format the result cell as a Percentage, drop the *100 part: =(New-Old)/Old. Excel multiplies by 100 automatically when the Percentage format is applied. Use ABS() around the denominator if Old Value could be negative.
Reference the second sheet inside the table array argument: =VLOOKUP(A2,Sheet2!$A$1:$B$500,2,0). Replace Sheet2 with the actual sheet name. If the name has spaces, wrap it in single quotes: 'Jan Sales'!$A$1:$B$500. The rest of the VLOOKUP syntax stays identical whether you are looking up within one sheet or across many.
Use =SUMPRODUCT(1/COUNTIF(A2:A100,A2:A100)) for a quick unique count without blanks. If blanks exist, add a condition: =SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100,A2:A100&"")). For large datasets, add data to the Data Model via PivotTable and select Distinct Count as the value field setting. It is faster and handles millions of rows.
Save the large excel file in .xlsb format to cut size by up to 75%. Turn off automatic calculation under Formulas → Calculation Options → Manual and press F9 to recalculate when needed. Remove unused named ranges, delete blank rows, and replace volatile functions like NOW() or OFFSET() with static alternatives wherever possible.
Extract the date with =INT(A2) and format as Date. Extract the time with =A2-INT(A2) and format as Time. If your date-time is stored as text with a space between date and time, use Data → Text to Columns → Delimited → Space to split the cell into two columns in a few clicks.
Select the cell, press Ctrl + C to copy, then right-click and choose Paste Special → Values. The keyboard shortcut is Alt + E + S + V + Enter. This replaces the formula with its static result. The cell looks identical but no longer recalculates. Always do this before sharing a report when you do not want recipients to see your formula logic.
Last updated: September 2026. Reviewed by the 3University editorial team.
We create, review, and maintain educational content covering Cybersecurity, Ethical Hacking, Artificial Intelligence, Blockchain, Web3, and emerging technologies.
The Government of India has launched YUVA AI for ALL a 100% free AI certificate course under the IndiaAI Mission (MeitY). This 4.5-hour, self-paced online
Quick answer: The highest paid profession in India is medicine senior surgeons and anaesthesiologists can earn ₹1–3 crore a year. CEOs, investment bankers, AI/ML engineers
Quick answer: There is no rigid eligibility for cyber security course admission in most online programs. If you have completed 10+2 (any stream) or hold
Cybersecurity has become one of the fastest-growing career fields in India and across the world. As cyber threats continue to increase, organizations are actively hiring
Choosing between the OSCP and the CEH is one of the biggest decisions in any ethical hacker career. Both are respected, both open doors but
A penetration test is only as valuable as the report that follows it. While identifying vulnerabilities is a critical part of the assessment process, the
Not a member yet? Register now
Are you a member? Login now
Not a member yet? Register now
Are you a member? Login now