How to use Excel filtering to resolve file format errors

Document created by Community User on Jul 29, 2016Last modified by Community User on Sep 11, 2020
Version 104Show Document
  • View in full screen mode
File format errors occur if your roster contains invalid data, is missing required fields, or a piece of data is inconsistent (for example, a teacher is listed with two different email addresses). These errors require that the source file be updated and imported again. You can use the filtering options in Excel to help identify the error without manually searching through the entire file. The Roster File Errors table will show the data that is causing an error. You can use this in conjunction with Excel's filtering features to find the incorrect data.

Note: NWEA released an optional new template version in April 2017, so the column letters in the screenshots below might be different if you are using a version of the template downloaded prior to April 2017.

These steps will walk you through how to set up filtering and analyze the error message. As an example, we will use the error "User Name cannot be the same for more than one Instructor ID". Modify these steps to match the error you are troubleshooting.

Here is a screenshot of our example error:
Error message with the following data: Data Type = User Name; Data in Error = angelasmith123; # Row of First Occurrence = 2; Error Description = User Name cannot be the same for more than one Instructor ID

In this error, the Data Type is User Name, the Data in Error is the username angelasmith123, the Row of First Occurrence is 2, and the Error Description is "User Name cannot be the same for more than one Instructor ID". This error is telling us that the username angelasmith123, which first appears in row 2, has more than one instructor ID associated with it. So to resolve the error, we will use Excel filtering to show us only the rows with this username. Then we will look for the instructor ID that is incorrect.

To troubleshoot this error using filtering:
  1. In the error message, locate the Data Type and #Row of First Occurrence fields. In our example, the Data Type is User Name, and the #Row of First Occurrence is 2.
  2. Open your roster file in Excel, and, locate the column matching the data type given and scroll to the specified row. You should see that the data in this cell matches the Data in Error field on the error. In our example, we find the User Name in column I, and we see that cell I2 matches the Data in Error found in our error message.
    A portion of an Excel spreadsheet showing column I with the User Name header, and the value angelasmith123 in cell I2.
  3. Right-click (or Control-click on a Mac) on the cell containing the data in error.
  4. On Windows, select Filter > Filter by Selected Cell's Value. On a Mac, select Filter > By Cell Value. In Numbers, select Filter Table by [Cell Value].

    You should now see only rows matching this specific piece of data.
    A contextual menu in Excel showing the Filter sub-menu with Filter by Selected Cell's Value selected.
  5. Check the Error Description field in your error. This field should specify where the data is mismatched. In our example, the error description "User Name cannot be the same for more than one Instructor ID" tells us that a particular user name is associated with multiple instructor IDs, so we should check the Instructor ID column.
  6. You should find that there is at least one value that is incorrect. To resolve the error, update the values so they are all correct and matching. In our example, we can see that this user has two instructor IDs listed: 9832493 and 983249.
    Instructor ID column in Excel, showing that one of the Instructor ID values does not match the others.
  7. To remove the filtering and see your entire roster file, right-click (or Control-click on a Mac) the column header for the data type we are filtering by, and select Filter > Clear Filter. In Numbers, right-click or Control-click the cell you were filtering by and choose Don't Filter Table by [Cell Value].

Article Number