Spreadsheet with student names and attendance codes cleaned into neat columns

Clean Up a Messy SIS Export in 10 Minutes with Excel and Google Sheets

November 18, 2026•3 min read

If you have ever exported a roster or attendance report from your student information system, you know the feeling. Names have extra spaces. First and last names are crammed into one cell. The same student shows up twice. Dates look like text. Before you can answer a simple question like "who has missed more than five days?", you lose an hour to cleanup.

Here is a routine that works in both Excel and Google Sheets. Do it once, and you can repeat it every time you pull a new export.

Step 1: Keep the raw export untouched

Paste the export into a tab named Raw and never edit it. Do all your work on a second tab named Clean. If something goes wrong, you can start over without pulling the data again, and anyone checking your numbers can see exactly where they came from.

Step 2: Remove extra spaces with TRIM

Extra spaces are the most common reason a lookup fails. In the Clean tab, use:

=TRIM(Raw!A2)

TRIM removes spaces at the start and end of a cell and turns double spaces into single spaces. Copy it down the column, then copy and paste as values so the cleaned text stays put.

Step 3: Split combined names into columns

If the export puts "Lopez, Maria" in one cell, split it.

  • Excel (Microsoft 365): =TEXTSPLIT(A2, ", ") spills the last name and first name into two cells.

  • Google Sheets: =SPLIT(A2, ", ") does the same thing.

  • Any version: Data, then Text to Columns (Excel) or Data, then Split text to columns (Sheets).

Flash Fill is another fast option in Excel. Type the first name for the first two rows by hand, then press Ctrl+E on Windows or Command+E on a Mac. Excel fills in the rest of the column by following your pattern.

Step 4: Remove duplicate rows

Duplicates inflate counts and throw off percentages. Select your data and use Data, then Remove Duplicates in Excel, or Data, then Data cleanup, then Remove duplicates in Google Sheets. Choose the column that should be unique, such as the student ID, not the name. Two students can share a name, but they should never share an ID.

Step 5: Fix dates that are stored as text

If dates are left aligned and will not sort correctly, they are probably stored as text. Try =DATEVALUE(A2) and format the result as a date. In Sheets, Format, then Number, then Date usually does the job once the values are real dates.

Step 6: Flag the students who need attention

Now the payoff. Use a COUNTIFS formula to count absences per student:

=COUNTIFS(Attendance!A:A, A2, Attendance!C:C, "A")

Then add conditional formatting to highlight any count above your threshold. In seconds you have a list your site coordinators can act on, instead of a wall of raw data.

Make it repeatable

Write the steps on a sticky note or in a cell at the top of the Clean tab. Next month, paste the new export into Raw and follow the same steps. Once your team trusts the routine, you can automate it with Power Query in Excel, which records each cleanup step and replays it with one click.

Want your staff to learn this on your own files?

Steps To Achieve offers hands-on Excel and Google Sheets training for school and program staff, built from your real workflows with fictitious data to protect privacy. Sessions run 90 minutes to multi-day, virtual or in person, on Windows or Mac, and Pennsylvania educators can earn Act 48 hours.

See Excel and Google Sheets training or Book a free 20-minute call.

Back to Blog
Steps To Achieve logo

Steps To Achieve supports education and workforce organizations with program performance, reporting systems, and training.

Get in Touch

  • 724-744-8791

724-744-8791

Monday to Friday 8 AM to 7:30 PM | Saturday 9 AM to 12 PM | Sunday closed

Main office: 8865 Norwin Avenue Ste 27 PMB 323, North Huntingdon, PA 15642

Second office: 11232 Boyette Rd Suite 1107, Riverview, FL 33569

Steps To Achieve © 2026 | All Rights Reserved.

  • Main office: 8865 Norwin Avenue Ste 27 PMB 323, North Huntingdon, PA 15642

  • Second office: 11232 Boyette Rd Suite 1107, Riverview, FL 33569

Let's Connect

Monday to Friday 8 AM to 7:30 PM | Saturday 9 AM to 12 PM | Sunday closed

Steps To Achieve © 2026 | All Rights Reserved.