Clean Up a Messy Headcount Roster Export with Gemini in Sheets

What This Does

An HRIS roster export rarely comes out clean. Two divisions merging produce duplicate rows, one manager writes "Sr. Manager" and another writes "Senior Manager," and a few rows are missing a field entirely. Gemini in Sheets can scan the export, flag likely duplicates and inconsistent formatting, and suggest fixes you apply in bulk instead of scrolling row by row before a planning session.

Before You Start

• You have a Business Standard plan with Gemini enabled ($14/user/month). Check for the sparkle icon near Share in the top-right corner of Sheets
• Your roster export is pasted into a sheet with consistent column headers (Name, Title, Department, Manager, Employee ID)
• You have a few minutes to spot-check whatever Gemini flags. Treat its output as a first pass, not a final answer

Steps

1. Find the AI feature

Click the sparkle icon in the top-right corner of the toolbar, or check Extensions in the top menu for Gemini, to open the side panel.

2. Tell it what you need

With your roster tab active, ask something specific: "Look at this roster export and flag any rows that look like duplicates of the same employee, and flag any inconsistent title formatting, like 'Sr. Manager' versus 'Senior Manager' for what should be the same title." Gemini reads the active sheet as context.

3. Review and use the result

Gemini returns a list of likely duplicates and formatting inconsistencies, sometimes with an Insert option that adds a flag column directly in the sheet. Go through each flagged row yourself. A duplicate flag might actually be two employees who share a name, and a title flag might reflect a real difference in level rather than just spelling. Once you've confirmed which flags are real, use Sheets' own Data menu to remove confirmed duplicate rows, and do a find-and-replace to standardize title wording across the sheet.

Real Example

Scenario: Two divisions merged last quarter, and the combined roster export has 340 rows where the standalone exports had roughly 320 combined, a sign of overlap. Titles are a mix of two different naming conventions.

What you do: Ask Gemini to flag likely duplicates and inconsistent titles across the merged export.

What you get: A dozen flagged rows, most genuine duplicates from employees who appeared in both source systems during the transition, plus a note that "Sr. Manager," "Senior Manager," and "Sr Mgr" all appear for what looks like the same level. You confirm the duplicates against employee ID, remove them, and standardize the title column before the roster goes into your next planning session.

Tips

• Gemini's read on "duplicate" is a starting hypothesis, not a verdict. Always confirm against employee ID before deleting anything
• Standardize title wording before you build anything on top of the roster (headcount counts by level get wrong fast if "Sr. Manager" and "Senior Manager" are being counted as different titles)
• Keep a backup copy of the raw export before you start deleting or editing rows, in case a flagged "duplicate" turns out to be real

A word on the data itself: a roster export is personnel data (names, titles, sometimes employee IDs) sitting in a sheet an AI feature can read. Before this becomes a routine habit, confirm with your HRIS or IT team that Google Workspace's Gemini features are covered under your company's data agreement for personnel information. If they aren't, work from a version with employee IDs swapped for a temporary label instead of real names while you clean formatting, and only restore identities once the sheet is back in an approved system.

Tool interfaces change. If a button has moved, look for similar AI/magic/smart options in the same menu area.