Practical How-To Guides
48 articles
- Jun 14, 2026
The 15 Excel Formulas Every Data Cleaner Needs (Explained in Plain English)
Every data quality problem in Excel has a formula solution. These 15 cover the situations you'll encounter most often — whitespace, casing, concatenation, lookup, duplication, and extraction. Each one is explained with a real example.
- Jun 14, 2026
How to Remove Duplicates in Google Sheets (3 Methods, No Formulas)
Fastest method: Select your data → Data → Data cleanup → Remove duplicates → choose columns → Remove duplicates. Done in under 30 seconds. This works for exact duplicates on clean, consistent data.
- Jun 14, 2026
How to Combine CSVs with Different Columns (Schema Mismatch Fixes)
The core problem: When two CSVs have different column names, structure, or order, a simple `copy *.csv merged.csv` or `cat` command produces a broken file — headers from multiple files appear mid-data, mismatched columns create null-filled rows, and different column names for the same data produce…
- Jun 14, 2026
Excel Converted My Numbers to Scientific Notation: How to Fix It
Immediate fix: Select the cells showing scientific notation → right-click → Format Cells → Number (or Custom: `0`) → OK. The full number reappears.
- Jun 14, 2026
TRIM, CLEAN, and the Invisible Characters Breaking Your VLOOKUPs
The most common cause of #N/A in VLOOKUPs: trailing spaces. "Acme Corp" and "Acme Corp " are different strings to Excel — even though they look identical in a cell. The second string has a space character after the text that you can't see.
- Jun 14, 2026
CSV to SQL: Generating INSERT Statements Safely (Escaping, Types, NULLs)
The quick method: Use Sohovi's CSV to SQL generator — upload your CSV, select your database (MySQL, PostgreSQL, or SQLite), and download ready-to-run INSERT statements. All processing is in your browser.
- Jun 14, 2026
Excel Turned My Gene Names / Product Codes into Dates: How to Fix It
What happened: Excel's auto-detection treats text that looks like a date as a date. "DEC1" becomes December 1st. "SEPT7" becomes September 7th. "1-3" becomes January 3rd. The original text is replaced and cannot be recovered — Excel stored a serial date number, not your original text.
- Jun 14, 2026
How to Bulk-Import a CSV into MySQL, PostgreSQL, and SQLite
The fast path by database: - MySQL: `LOAD DATA INFILE 'file.csv' INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;`
- Jun 14, 2026
Data Validation in Google Sheets: Dropdowns, Rules, and Protected Ranges
The fastest way to prevent bad data in a shared sheet: Add a dropdown list to any column by selecting the column → Data → Data validation → Criteria: List of items → enter your valid values. Collaborators can only enter values from the list.
- Jun 14, 2026
UTF-8 Characters Look Broken in Excel (é, ’): The Encoding Fix
The diagnosis: When you see `é` instead of `é`, or `’` instead of `'`, Excel is reading a UTF-8 encoded file as if it were Windows-1252 (Latin-1) encoded. Each multi-byte UTF-8 character is being interpreted as separate Latin-1 characters — producing garbled output.
- Jun 14, 2026
How to Find Duplicates Across Two CSV Files (4 Methods)
The fastest approach for non-technical users: Open both CSVs in Excel or Google Sheets, combine them into one sheet, then run VLOOKUP (or COUNTIF) to find records that appear in both files. For fuzzy matches across files — "Jon Smith" in file 1 matching "John Smith" in file 2 — you need a fuzzy…
- Jun 14, 2026
How to Convert JSON to Excel Without Coding (3 Methods)
The fastest method for a simple JSON array: Use Power Query in Excel — Data → Get Data → From File → From JSON → select your file → Load to worksheet. This works natively in Excel 365 and Excel 2016+ without any add-ins.
- Jun 14, 2026
What Does This Excel Formula Do? Decoding Nested IFs, VLOOKUP, and INDEX/MATCH
The fastest approach for any formula you don't understand: Paste it into Sohovi's Excel Formula Explainer — it gives you a plain-English breakdown of what each part does.
- Jun 14, 2026
CSV to JSON: Arrays, Objects, and Which Format APIs Actually Expect
The core confusion: Converting CSV to JSON seems simple, but JSON has multiple valid structures for tabular data. Most APIs expect one specific format, and sending the wrong one causes a 400 error even though your data is correct. Here's exactly which format to use and how to produce it.
- Jun 14, 2026
Conditional Formatting Recipes for Spotting Bad Data Instantly
Conditional formatting turns a spreadsheet into a live quality audit. Instead of writing formulas column by column, you define a rule once and every problem glows red. Here are the 6 most useful recipes for data quality work.
- Jun 14, 2026
Test Data vs Production Data: Why You Should Never Test with Real PII
The short answer: Using real customer data in development, testing, or staging environments creates privacy risk (data breaches in less-secure environments), legal risk (GDPR requires appropriate security for personal data at all times), and compliance risk (many auditors will cite…
- Jun 14, 2026
How to Put a CSV Table into GitHub README or Notion
Fastest method: Use Sohovi's CSV to Markdown converter — paste your CSV or upload the file, and get GitHub-compatible Markdown table syntax in seconds. Copy and paste into your README or Notion page.
- Jun 14, 2026
Excel's 1,048,576 Row Limit: How to Work with Bigger Files
The limit: Excel can display a maximum of 1,048,576 rows (2^20) per sheet. Files with more rows open, but Excel silently truncates them — you see no warning, just missing data. For any file that exceeds this limit, you need a different approach.
- Jun 14, 2026
How to Generate Realistic Fake Customer Data for Testing
The fastest method: Use Sohovi's test data generator — choose your fields (name, email, phone, address, company, date, etc.), set the row count (up to 100k), and download a CSV. No code, no account required.
- Jun 14, 2026
How to Reorder, Rename, and Drop CSV Columns in Bulk
Fastest method: Use Sohovi's CSV column picker — upload your file, drag columns to reorder, rename headers, check or uncheck to include/exclude columns, and download the modified CSV. No Excel, no code.
- Jun 14, 2026
How to Split One Huge CSV into Smaller Files (Without Excel)
The fastest method for Mac/Linux: `split -l 10000 data.csv chunk_ && for f in chunk_*; do (head -1 data.csv && cat $f) > $f.csv; done`
- Jun 14, 2026
Seed Data for Demos: Generating 100k Realistic Rows in Seconds
For demos without code: Use Sohovi's test data generator — choose your fields, enter 100,000 as the row count, click Generate. Your CSV is ready to download in seconds.
- Jun 14, 2026
Why 'Remove Duplicates' in Excel Misses Most Real-World Duplicates
The answer in one sentence: Excel's Remove Duplicates uses exact string matching — two cells must be byte-for-byte identical to be considered duplicates. Real-world data is almost never that clean.
- Jun 12, 2026
How to Anonymize a CSV Before Sharing It (So It Can't Be Re-Identified)
The counterintuitive fact: Deleting the name column is not anonymization. A dataset with ZIP code, age, and gender is enough to uniquely identify most individuals — researchers have shown that three quasi-identifiers can re-identify 87% of Americans. True anonymization requires systematically…
- Jun 12, 2026
CSV Dates Are Wrong in Excel: Why 03/04 Becomes April 3rd and How to Fix It
The diagnosis: Excel applies your computer's regional date format when interpreting CSV date strings. If your file was created on a system using DD/MM/YYYY but your computer is set to MM/DD/YYYY (common on US Windows machines), Excel reads "03/04/2026" as April 3rd — not March 4th. The same string,…
- Jun 12, 2026
Excel Keeps Removing Leading Zeros (ZIP Codes, Phone Numbers, IDs): The Permanent Fix
The immediate fix: Don't double-click the CSV file. Instead, open Excel, go to Data → From Text/CSV, browse to your file, and when the import wizard opens, click on the problematic column and change the data type from "General" to Text. Click Load. Your leading zeros survive.
- Jun 12, 2026
Data Cleaning: The Complete 8-Step Process (With a Free Checklist)
Data cleaning is the process of detecting and correcting errors, inconsistencies, and inaccuracies in a dataset so it can be reliably used for analysis, operations, or reporting. The process has eight distinct steps. Skip any one of them and the problems you miss in that step will show up as errors…
- Jun 12, 2026
Free Data Quality Audit Template (Run a Self-Audit in One Afternoon)
This template gives you a structured, one-afternoon process to audit the data quality of any dataset — a CRM export, a product catalog, a customer list, or an operational database. You'll end up with: a quality score per dimension, a prioritized issues list, and a clear picture of what to fix first.
- Jun 12, 2026
Data Quality for AI: Why Your RAG Pipeline Gives Wrong Answers (and the Fix Is Boring)
The uncomfortable truth: Most "hallucination" complaints about RAG-based assistants are not hallucinations. They're the model accurately retrieving and summarizing dirty source data. Your product catalog has two conflicting prices for the same SKU. Your knowledge base has three versions of the same…
- Jun 12, 2026
Fuzzy Matching Explained: How to Find 'John Smith' vs 'Jon Smyth' in Your Customer Data
The short answer: Fuzzy matching compares records by similarity rather than exact equality. Instead of requiring "John Smith" and "John Smith" to be identical, it scores how similar they are — "John Smith" and "Jon Smyth" might score 85% similar and get flagged as a likely duplicate. You set the…
- Jun 12, 2026
Handling Missing Values Without Python: A Business User's Guide
You don't need code to handle missing values. You need a decision process. Every missing value has exactly four treatment options: delete the row, fill with a default, fill with a derived value, or flag it and leave it. The right choice depends on WHY the value is missing — not on what's…
- Jun 12, 2026
How to Merge Multiple CSV Files into One (Without Excel Crashing)
Fastest method on Windows: Open Command Prompt in the folder containing your CSVs and run `copy *.csv merged.csv`. On Mac/Linux: `cat *.csv > merged.csv`. This takes under 10 seconds. Warning: it concatenates headers too — fix that in the next section.
- Jun 12, 2026
How to Remove Duplicate Rows in Excel: 3 Methods (and What Each One Misses)
The fastest fix: Select your data → Data tab → Remove Duplicates → pick which columns to check → click OK. Excel removes exact duplicate rows and tells you how many were removed. This works for clean, consistent data and takes under 30 seconds.
- Jun 12, 2026
How to Standardize Phone Numbers in a Spreadsheet (Without Breaking Them)
The target format: E.164 — `+[country code][number]` with no spaces, dashes, or parentheses. `+14155552671`, `+447911123456`, `+919840123456`. This is the international standard accepted by all telecoms, SMS APIs, and CRMs without ambiguity.
- May 21, 2026
How to Build a Data Quality Checklist for Your Business
A data quality checklist is a structured list of checks you run on a dataset before using it — designed to catch completeness gaps, duplicate records, format errors, and consistency issues before they damage your reports, campaigns, or decisions.
- May 21, 2026
How to Train Your Team to Maintain Data Quality Standards
You can train your team to maintain data quality standards by making the standards explicit, building them into workflows rather than relying on memory, showing people the consequences of bad data, and creating accountability without blame.
- May 21, 2026
How to Fix the Most Common Data Quality Problems
You can fix the most common data quality problems — duplicate records, missing values, wrong formats, and inconsistent fields — by following a structured cleanup process that addresses both the existing errors and the upstream processes that created them.
- May 21, 2026
How to Test Data Quality Before Importing Into Your System
You can test data quality before importing by running five checks on your file before the import begins: structural validation (correct columns and format), completeness (required fields populated), uniqueness (no duplicates on identifier fields), validity (values match expected formats), and…
- May 21, 2026
How to Track Data Quality Trends Over Time
You can track data quality trends over time by running the same quality checks on a regular schedule, recording the results in a simple tracker, and reviewing those results periodically to see whether quality is improving, stable, or degrading — and why.
- May 21, 2026
How to Set Data Quality Thresholds That Actually Make Sense
A data quality threshold is the minimum acceptable level of quality for a specific field or dataset — the point at which quality is good enough for its intended use, below which action is required.
- May 21, 2026
How to Write a Data Quality Policy in 5 Steps
You can write a data quality policy in 5 steps: define what datasets the policy covers, set measurable quality standards for each, assign clear ownership, specify how quality will be monitored and measured, and document what happens when standards aren't met.
- May 21, 2026
How to Audit Your Data Quality in 5 Steps
You can audit your data quality in 5 steps: define the scope and standards, profile the dataset across all quality dimensions, score and prioritize the findings, document the results, and identify root causes for each issue found.
- May 21, 2026
How to Prioritize Data Quality Issues When Resources Are Limited
You can prioritize data quality issues when resources are limited by scoring each problem on two dimensions: how much damage it causes if left unfixed, and how easy it is to fix. Fix high-damage, easy-fix problems first — and defer or accept low-damage problems that are hard to fix.
- May 21, 2026
How to Run Your First Data Quality Audit (Step-by-Step)
A data quality audit is the process of systematically evaluating a dataset to identify specific quality problems — completeness gaps, duplicate records, format errors, and consistency issues — so you can fix them before they damage your operations, decisions, or customer relationships.
- May 21, 2026
How to Set Up Data Quality Monitoring Without an Engineer
You can set up data quality monitoring without an engineer by choosing lightweight tools that run checks automatically, defining the thresholds that matter for your business, and setting up alerts that notify the right person when something falls below standard — all without writing a single line…
- May 21, 2026
How to Create Custom Data Validation Rules for Your Business
Custom data validation rules are conditions that define what valid data looks like for your specific business — checking whether a value is in the right format, falls within an acceptable range, or matches an approved list of values.
- May 21, 2026
How to Conduct a Data Quality Assessment in One Day
You can conduct a meaningful data quality assessment in one day by focusing on a single dataset, working through five quality dimensions systematically, and producing a prioritized list of findings by end of day — ready to act on the next morning.
- May 21, 2026
How to Automate Your Data Quality Checks
You can automate your data quality checks by using tools that run validation rules on a schedule or trigger, alerting the right person when results fall below threshold — replacing manual review with a system that works even when no one is watching.