Skip to main content

Data Study & Tallying Workflow – Sundar Mishra

I believe that data study and data tallying are hard because you cannot solve data issues just by overworking. You have to fix them with a clear, calm mind and a smarter approach. Once you find a data pattern and learn some Excel functions, you can easily discover ways to do calculations with your data.

Yes, one funny thing is that in the first few days, you may even see formulas, numbers, or patterns in your dreams or in daily life — but these are normal.

Some of my ideas and workflows I have documented here.

Step 0: Data Import & Backup

Description:
Before working on data, always preserve the original file. Document where the data came from, note its version (V1, V2, etc.), and keep backups. This avoids confusion when multiple files are generated or when older data is required later.

Step 1: Data Cleaning & Validation

Description:
Raw data usually contains extra spaces, inconsistent text, wrong data types, or formatting issues. Cleaning ensures the dataset is standardized and trustworthy, without changing its actual meaning. After cleaning, totals must match the original so accuracy is not lost.

  • TRIM() → Remove unwanted spaces.

  • CLEAN() → Remove invisible/non-printable characters.

  • UPPER() / LOWER() / PROPER() → Standardize text case.

  • TEXT(value, format_text) → Convert numbers/dates into consistent format.

  • VALUE() → Convert stored-as-text numbers into numeric type.

  • DATEVALUE() → Convert text dates into real date values.

  • ROUND(), ROUNDUP(), ROUNDDOWN() → Control decimals consistently.

  • ISNUMBER(), ISTEXT(), ISERROR() → Validate data type.

🔑 Check: Use SUM() before and after cleaning → totals must remain the same.

Step 2: Understanding Data & Setting Goal

Description:
After cleaning, understand the dataset: where it came from, what it contains, and what you want to achieve. Look for patterns (repeated codes, categories, unique IDs). Once the goal is clear, plan how to achieve it. If unsure, learn the method first (from seniors or AI), then solve it yourself.

  • COUNT() → Count numeric records.

  • COUNTA() → Count all non-blank cells.

  • COUNTBLANK() → Count empty cells.

  • UNIQUE() → Extract unique values (Excel 365+).

  • SORT() / SORTBY() → Arrange data to spot trends.

  • LEN() → Check for inconsistent text lengths (e.g., ID digits).

  • SEARCH() / FIND() → Locate a substring inside text.

  • LEFT(), RIGHT(), MID() → Extract parts of codes.

  • FREQUENCY() or MODE() → Detect repetition patterns.

Step 3: Tallying & Multi-way Verification

Description:
Now perform tallying: compare, sum, and reconcile. Never rely on only one method of verification — check totals, subtotals, and record-level matches. If different checks give the same result, you can be confident the data is correct.

  • SUM() → Total values.

  • SUBTOTAL() → Totals that ignore filtered/hidden rows.

  • SUMIFS() → Conditional totals.

  • COUNTIFS() → Conditional counts.

  • VLOOKUP() / HLOOKUP() → Lookup values in other reports.

  • INDEX() + MATCH() → More flexible lookup alternative.

  • XLOOKUP() (Excel 365+) → Modern lookup function.

  • IF() / IFS() → Apply conditional logic.

  • IFERROR() → Return safe output if formula errors.

  • EXACT() → Compare text values exactly (case-sensitive).

  • TEXTJOIN() → Combine multiple fields for matching.

🔑 Cross-Verification Techniques:

  • Totals vs. subtotals (SUMIFS() vs SUM()).

  • Record-to-record check (VLOOKUP() / XLOOKUP()).

  • Duplicate check (COUNTIFS() > 1).

  • Highlight mismatches → conditional formatting with formula rules.

Step 4: Finalization & Reporting

Description:
Once verified, the dataset must be finalized and reported. The report should be clean, summarized, and easy for others to read. Use pivots, charts, and formatting to present results clearly.

  • Pivot Table → Summarize large data quickly (totals, counts, %).

  • Pivot Chart → Visual representation of summaries.

  • TEXT() → Present numbers in clean formats (e.g., "1,234.00").

  • Conditional Formatting → Highlight exceptions.

  • ROUND() / INT() → For neat presentation of figures.

📌 Principle: Data is finalized only if multiple verification methods agree.

Optional: Smart Work Habits

Optional: I’d recommend that if you are working with data, you should learn some very important Excel functions. This includes formulas, Pivot Tables, printing options, and keyboard shortcuts. Yes, learning these may be a little challenging at first, but once you master them, you will never get stuck on data problems.

Nobody is perfect in their work; they simply try to improve by learning and solving new issues. If you work with the same type of data repeatedly, you shouldn’t waste time recalculating it manually every time. Instead, you can create a template: paste your cleaned data in one place, and let the template perform comparisons or calculations automatically. This approach will help you work faster and more efficiently in data analysis.

✅ End Result: Clean, reliable, verified data ready for reporting, auditing, or further analysis.

Back to top