Recruiters export ATS data when they need a report the system does not already produce: a hiring funnel by outcome, a source comparison for a hiring manager, or a vacancy-level summary for a weekly catch-up. The CSV arrives quickly. The analysis usually does not.
Raw ATS CSV exports are often hard to trust. Stage names appear in several spellings. Candidate sources splinter into near-duplicates. Dates arrive in mixed formats. A duplicated application row can inflate every metric that follows. Those problems are not cosmetic. They change conversion rates, source performance, and the story you take into a hiring meeting.
This guide shows how to clean an ATS CSV export, standardize recruitment stages and sources, calculate a simplified hiring funnel report, and chart the results. All practical steps use WPS Spreadsheets in WPS Office 12.1 on Windows. The workflow uses free spreadsheet features: CSV import, filters, Find and Replace, formulas, PivotTables, and charts.
The worked example uses a fictional 85-application export. The numbers demonstrate the method. They are not industry benchmarks.
What Is an ATS CSV Export?
An ATS CSV export is a flat file of recruitment records downloaded from an applicant tracking system. Recruiters use exports for custom reporting, audits, stakeholder packs, and analysis that sits outside the ATS dashboard.
Column names vary by provider. A typical application-level export may include fields such as:
|
Example field |
What it usually represents |
|
Application ID |
Unique ID for one application to one vacancy |
|
Candidate ID |
Person-level identifier |
|
Candidate Name |
Candidate display name |
|
Job Title |
Vacancy applied for |
|
Department |
Hiring department |
|
Application Date |
Date the application was submitted |
|
Candidate Source |
Channel or source label |
|
Recruitment Stage |
Current or final stage/status |
|
Recruiter |
Owner of the record |
|
Location |
Hiring location |
|
Interview / Offer / Hire Date |
Milestone dates, when populated |
|
Application Status |
Active, hired, rejected, withdrawn, or similar |
Two distinctions matter for ATS data analysis:
· Candidate vs application: One candidate can submit more than one application.
· Current status vs stage history: Many exports show only the latest stage. That supports an outcome report. It does not automatically support a full historical stage-transition funnel.
Do not assume every ATS uses the same labels or the same meaning for “Screening,” “Interview,” or “Hired.”
ONREC’s coverage of ATS selection and reporting—such as what to look for in an applicant tracking system—focuses on platform capabilities. This tutorial starts after the export button: how to make that file usable in a recruitment spreadsheet.
Why Recruiters Need to Clean ATS Data Before Reporting
Cleaning is part of recruitment reporting, not an optional admin chore.
Examples of distortion:
· Duplicate application rows count the same application twice in every total and rate.
· Stage label drift (Phone Screen, phone screening, Phone Interview) splits one stage into several buckets.
· Source label drift (LinkedIn, Linkedin, LinkedIn Job Post) understates or overstates channel performance.
· Mixed date formats break time-to-hire calculations or sort order.
· Extra spaces create false “new” categories in PivotTables.
· Blank optional fields are normal; blank required identifiers are not.
If you compare sources by application volume alone, a noisy LinkedIn label group can look dominant while Referral quietly produces more hires. ONREC’s recruitment analytics pieces already warn that volume can mislead; cleaning is how you stop the spreadsheet from amplifying that problem.
Choose Free Spreadsheet Software for ATS Data Analysis
Recruitment teams often need Free spreadsheet software that opens CSV exports offline, supports cleaning, and can produce charts for a hiring meeting.
|
Application |
Free access |
CSV import |
Cleaning / filters |
Formulas |
Pivot / summary |
Charts |
Limitations |
|
WPS Spreadsheets |
Free desktop install; ads may appear |
Yes |
Strong |
Yes |
PivotTable |
Yes |
Some PDF/AI extras are premium |
|
LibreOffice Calc |
Fully free, offline |
Yes |
Strong |
Yes |
Pivot Table |
Yes |
Different dialog-driven UI |
|
Google Sheets |
Free with Google account |
Yes |
Strong |
Yes |
Pivot table |
Yes |
Browser-first; account required |
Selected application for this tutorial: WPS Spreadsheets (WPS Office 12.1.0.29162, Windows).
It was selected because CSV import, Find and Replace, filters, PivotTables, Average/Count summaries, and charts are available in the free desktop workflow, and the ribbon layout is familiar for many recruiters who already work with .xlsx files. LibreOffice Calc and Google Sheets can complete a similar analysis; their menus are not mixed into the steps below.
Students of Excel-compatible spreadsheet workflows can also review the official Excel download page for WPS Spreadsheets as a free desktop option that opens common ATS exports.
Prepare and Import Your ATS CSV Export
1. Download the CSV from your ATS for a defined reporting period and vacancy set.
2. Save an untouched copy named something like ats-export-raw-original.csv.
3. Keep personal data access limited to people who need it.
4. Open WPS Spreadsheets.
5. Use File > Open and select the CSV, or drag the file into WPS Spreadsheets.
6. Confirm the delimiter split columns correctly (comma-separated ATS exports are common; some systems use semicolon).
7. Check that headers occupy one row and that Application ID values look unique at a glance.
8. Save a working copy as .xlsx, for example ats-export-working.xlsx.
For this article, the sample files are:
· onrec-ats-raw-export.csv / .xlsx (messy fictional export: 88 rows including duplicates)
· onrec-ats-cleaned-data.csv / .xlsx (cleaned working file: 85 applications)
How to Clean an ATS CSV Export Step by Step
Use the raw sample and keep a cleaning log. Do not overwrite the original export.
1. Identify duplicate application records
· Select the Application ID column.
· Use sort or conditional highlight to spot repeated IDs.
· Remove extra rows that share the same Application ID.
· Keep different Application IDs that share a Candidate ID. Those are usually separate applications.
In the sample, three duplicate Application IDs were removed: APP-2005, APP-2020, and APP-2042. Records fell from 88 to 85.
2. Standardize recruitment stage names
Create a mapping table, then use Find and Replace or a helper column:
|
Raw label examples |
Standardized stage |
|
New Application, applied |
Applied |
|
Phone Screen, phone screening, Phone Interview |
Screening |
|
1st Interview, Interview Stage |
Interview |
|
Offer Extended |
Offer |
|
Hire, hired |
Hired |
|
Reject, Not Successful |
Rejected |
|
Candidate Withdrew |
Withdrawn |
Inconsistent stage labels break hiring funnel analysis before any formula is written.
3. Standardize candidate source labels
Map variants such as:
|
Raw label examples |
Standardized source |
|
Linkedin, LinkedIn Job Post |
|
|
Website, Company website |
Company Website |
|
referral, Employee Referral |
Referral |
|
JobBoard, Indeed / Job Board |
Job Board |
|
Recruitment Agency, agency |
Agency |
4. Correct date formats
Convert Application Date values to one format. In this tutorial, cleaned dates use ISO YYYY-MM-DD.
Be careful with day/month ambiguity. If your ATS mixes regional formats, document the rule you used. In the sample, slash dates were treated as day-first (DD/MM/YYYY) and normalized to ISO.
5. Identify blank fields
· Required for application-level reporting: Application ID, Candidate ID, Job Title, Application Date, Recruitment Stage, Candidate Source.
· Optional: Interview Date, Offer Date, Hire Date, Location.
Do not invent hire dates. Leave blanks blank.
6. Trim inconsistent spacing
Trim leading and trailing spaces from Candidate Name, stage, and source fields. Extra spaces create false PivotTable categories.
7. Validate Application and Candidate IDs
· Application IDs must be unique after deduplication.
· Candidate IDs can repeat across applications.
· Spot-check that Hired rows have Hire Date values when your process requires them.
8. Save the cleaned file separately
Save as onrec-ats-cleaned-data.xlsx. Keep the raw file unchanged.
Fictional before/after cleaning view: standardize stages and sources, normalize dates, and remove duplicate Application IDs.
How to Build a Hiring Funnel Report in a Spreadsheet
Reporting model used in this tutorial
· Unit of analysis: one row per application
· Stage field: current or final outcome
· Report type: simplified application outcome funnel
Because the export does not include stage-history events, the stage counts below are outcome volumes, not proof of how many applications ever entered each historical stage.
Outcome funnel for the fictional sample (n = 85)
|
Stage / Outcome |
Applications |
|
Applied |
18 |
|
Screening |
16 |
|
Interview |
14 |
|
Offer |
6 |
|
Hired |
12 |
|
Rejected |
14 |
|
Withdrawn |
5 |
Active pipeline applications (Applied + Screening + Interview + Offer) = 54.
Core recruitment metrics
Use these formulas on application counts:
· Application-to-hire rate = Hires ÷ Total applications × 100
Sample: 12 ÷ 85 × 100 = 14.12%
· Rejection rate = Rejected ÷ Total applications × 100
Sample: 14 ÷ 85 × 100 = 16.47%
· Withdrawal rate = Withdrawn ÷ Total applications × 100
Sample: 5 ÷ 85 × 100 = 5.88%
Build the summary in WPS Spreadsheets
9. Open the cleaned workbook.
10. Select the data range including headers.
11. Go to Insert > PivotTable.
12. Place the PivotTable on a new worksheet.
13. Put Recruitment Stage in ROWS.
14. Put Application ID in VALUES and set the summary to Count via Value Field Settings if needed.
15. On a metrics sheet, calculate the three rates with formulas such as:
=HiredCount / TotalApplications Format as percentage.
16. Add a Definitions note that states the outcome-funnel limitation.
Time-to-hire (only where dates exist)
For Hired rows with both Application Date and Hire Date:
Time-to-hire (calendar days) = Hire Date − Application Date
In the sample, all 12 hires had valid dates:
· Average: 31.5 days
· Minimum: 28 days
· Maximum: 36 days
Do not calculate time-to-hire for applications without a hire date. Do not treat missing dates as zero.
Outcome volumes from the cleaned sample. This is a labeled stage-volume chart, not a claim of native funnel-chart software.
Analyze Recruitment Sources and Job Performance
Source summary (sample)
|
Candidate Source |
Applications |
Hires |
Source-to-Hire Rate % |
|
Referral |
21 |
6 |
28.57 |
|
|
24 |
2 |
8.33 |
|
Job Board |
19 |
2 |
10.53 |
|
Company Website |
15 |
1 |
6.67 |
|
Agency |
6 |
1 |
16.67 |
LinkedIn produced the most applications. Referral produced the most hires and the highest source-to-hire rate in this small fictional set. That is exactly why applicant tracking system reporting should separate volume from conversion.
Agency’s 16.67% rate looks strong, but it is based on only six applications. Do not treat tiny samples as strategy proof.
Job title summary (sample)
|
Job Title |
Applications |
Hires |
Application-to-Hire Rate % |
|
Customer Support Associate |
17 |
3 |
17.65 |
|
Marketing Executive |
17 |
3 |
17.65 |
|
Junior Software Engineer |
17 |
2 |
11.76 |
|
People Operations Coordinator |
17 |
2 |
11.76 |
|
Sales Development Representative |
17 |
2 |
11.76 |
Create these tables with PivotTables:
· Rows: Candidate Source or Job Title
· Values: Count of Application ID
· Add a second value or calculated column for Count of hires (Recruitment Stage = Hired), or filter a helper column.
ONREC’s article on data analytics in recruitment stresses source quality over vanity volume. The spreadsheet steps above are one practical way to check that claim against your own export.
Create Charts for Your Hiring Funnel Report
In WPS Spreadsheets:
17. Select a summary table (stage volumes, source counts, or job counts).
18. Go to Insert and choose a chart type, or create a chart from a PivotTable via the PivotChart / chart tools available in your build.
19. Prefer:
- Horizontal bar for outcome volumes - Clustered column for applications vs hires by source - Clustered column for applications vs hires by job title
20. Give each chart a precise title, such as Applications vs Hires by Candidate Source (cleaned export).
21. Label axes with units (Applications, not “Values”).
22. Start the value axis at zero for count comparisons.
23. If your build has no native funnel chart, use a horizontal bar chart and say so. Do not relabel a bar chart as a funnel chart feature.
Source chart from the verified sample: high application volume and high hire conversion are not the same thing.
For slide packs, export charts or summary tables into a presentation workflow. A Powerpoint download path is useful when the hiring funnel report needs to move from spreadsheet analysis into a short stakeholder update—without rebuilding the numbers by hand.
How to Interpret the Hiring Funnel Report
A short interpretation of this fictional sample:
In the cleaned 85-application export, 12 applications ended as Hired (14.12% application-to-hire). Fourteen were Rejected (16.47%) and five Withdrawn (5.88%). Fifty-four applications remained in active pipeline stages. Referral converted to hire at 28.57% in this sample, while LinkedIn led application volume. Average time-to-hire for the 12 hires was 31.5 calendar days. These figures describe outcomes in the demo file. They do not prove why candidates withdrew or why a source converted better.
Use the report to ask better questions:
· Where are applications concentrated right now?
· Which sources produce hires, not just volume?
· Which vacancies need attention this week?
· Are stage labels still drifting in the next export?
A spreadsheet identifies patterns. It does not automatically explain candidate quality, recruiter performance, or process fairness. For visualization-oriented stakeholder storytelling, ONREC’s archive piece on how data visualization tools enhance recruitment reporting discusses dashboard communication; this tutorial stays with export-to-spreadsheet mechanics.
Common ATS Data Cleaning and Reporting Mistakes
· Deleting every repeated Candidate Name or Candidate ID and losing legitimate multi-job applications
· Mixing candidate counts with application counts in the same KPI
· Treating current statuses as historical stage-entry counts
· Changing denominators mid-report
· Ignoring missing hire dates in time-to-hire averages
· Ranking sources on five or six applications
· Treating Rejected and Withdrawn as interchangeable without saying so
· Publishing charts without titles, units, or sample size
· Circulating identifiable candidate-level files more widely than necessary
Best Practices for Reliable Recruitment Reporting
· Keep an unchanged raw export.
· Document every cleaning rule in a short log.
· Agree stage definitions with the hiring team before reporting.
· Define the reporting period and vacancy set on the cover sheet.
· Use Application ID for application-level metrics.
· Separate candidate-level and application-level questions.
· Recalculate after each new export.
· Limit access to personal candidate fields.
· Follow your organization’s privacy and retention rules.
· Avoid sweeping claims from small samples.
If your team later needs a broader free office suite for documents, sheets, and PDF packs around the same hiring process, WPS publishes a free microsoft office alternative comparison. The reporting method in this article still depends on clean application data, not on the brand of spreadsheet.
Conclusion
Reliable ATS data analysis follows a simple sequence: export, protect the raw file, clean and standardize, calculate application-level metrics, chart the summary, and interpret with caution. When stage history is missing, label the report as a simplified outcome funnel and keep conversion claims inside what the file can support.
A cleaned recruitment spreadsheet will not replace an ATS. It will stop messy CSV exports from quietly rewriting your hiring story.






