How to Clean ATS CSV Exports and Build a Hiring Funnel Report in a Spreadsheet

How to Clean ATS CSV Exports and Build a Hiring Funnel Report in a Spreadsheet

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

LinkedIn

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.

Before-and-after illustration of messy ATS stage and source labels versus standardized cleaned fields

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.

Horizontal bar chart of application outcome volumes from the cleaned fictional ATS sample

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

LinkedIn

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.

Clustered column chart comparing applications and hires by candidate source

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.

 

Explore ATS and recruitment platform suppliers

Related reading

Explore this subject in more depth.

Recruitment Spotlight: the people shaping recruitment. Read the interviews.The Onrec Awards: a night for the remarkable. March 2027. Explore the Awards.

Community poll

When should employers first share salary information with candidates?