
Clean Up a Messy SIS Export in 10 Minutes with Excel and Google Sheets
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.

