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

  • AlwaysDescription:
    Before saveworking aon rawdata, copyalways beforepreserve starting.

    the
    • original
  • file.

    Document sourcewhere details:the filedata name,came reportfrom, name,note extractionits date,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

CleanDescription:
Raw anddata standardizeusually contains extra spaces, inconsistent text, wrong data types, or formatting issues. Cleaning ensures the dataset is standardized and trustworthy, without alteringchanging 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) → FormatConvert valuesnumbers/dates (e.g.,into dates,consistent numbers).format.

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

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

  • ROUND(), ROUNDUP(), ROUNDDOWN() → EnsureControl consistentdecimals decimal places.consistently.

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

🔑 Checks:Check:

    Use

  • TotalsSUM() before &and after cleaning → totals must matchremain (SUM()the check).same.

  • Define correct data types: Date, Number, Text.

Step 2: Understanding Data & Setting Goal

    Description:
    After

  • cleaning,

    Verifyunderstand datathe sourcedataset: where it came from, what it contains, and reliability.

    what
    • you
  • want

    Identifyto achieve. Look for data patternpatterns (repeated fields,codes, keys,categories, categories)unique IDs).

    • Once
  • the

    Define goal ofis tallyclear, (e.g.,plan reconciling totals, validating balances).

  • If needed, ask senior/AI tools how to solveachieve it. If unsure, learn the method first (from seniors or AI), notthen to dosolve it for you.yourself.

🔧 Helpful Formulas for Exploration:Formulas:

  • COUNT(), → Count numeric records.

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

  • COUNTBLANK() → Count records.empty cells.

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

  • SORT() / SORTBY() → DetectArrange orderingdata &to patterns.spot trends.

  • LEN() → SpotCheck for inconsistent text lengths (e.g., ID codes)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

PerformDescription:
Now tallyperform tallying: compare, sum, and checkreconcile. inNever morerely thanon only one way.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() → DynamicTotals totalsthat (ignoresignore filtered/hidden rows).rows.

  • SUMIFS() → Conditional summing.totals.

  • COUNTIFS() → Conditional counting.counts.

  • VLOOKUP(), / HLOOKUP() → CompareLookup values acrossin other reports.

  • INDEX() + MATCH() → FlexibleMore lookups.flexible lookup alternative.

  • XLOOKUP() (ifExcel available)365+) → Modern lookup alternative.function.

  • IF() / IFS() → ConditionalApply checks.conditional logic.

  • IFERROR() → HandleReturn mismatchessafe gracefully.output if formula errors.

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

  • TEXTJOIN() → ConcatenateCombine multiple fields for pattern matching.

🔑 Cross-verificationVerification techniques:Techniques:

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

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

  • Duplicate detectioncheck (COUNTIFS() > 1).

  • MismatchHighlight highlightingmismatches (→ conditional formatting with formulas).formula rules.

Step 4: Finalization & Reporting

    Description:
    Once

  • verified,

    Confirmthe datadataset ismust error-freebe finalized and consistent.reported. The report should be clean, summarized, and easy for others to read. Use pivots, charts, and formatting to present results clearly.

  • Formulas

    Prepare summary output (Excel Pivot, PDF, or Dashboard).

🔧 Useful Reporting& Tools:

  • Pivot Table â†’ Summarize large data quickly (withtotals, SUM, COUNT,counts, % of Total)).

  • ChartsPivot forChart visualization.→ Visual representation of summaries.

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

    report
    • presentation.

  • Conditional Formatting → Highlight exceptions.

  • ROUND() / INT() â†’ For neat presentation of figures.

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

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 result.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: Cleaned,Clean, reliable, and thoroughly verified data ready for reporting, auditing, or deeperfurther analysis.

Back to top