How to use Excel filtering to resolve file format errors

Document created by Community User on Jul 29, 2016Last modified by Community User on Jun 6, 2017
Version 102Show Document
  • View in full screen mode
Introduction
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.

Steps
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.

For errors about the heading row, see: Your roster file is missing the heading row, or the header row does not match the template.

How to filter in Excel 2007, 2010, and 2013 for Windows:
  1. Select the top row with the header.
  2. Select the Home tab to display the Home ribbon.
  3. From the Editing group on the Home ribbon, select Sort & Filter:

    User-added image
  4. From the list of options, select Filter.
  5. Select the dropdown menu arrow in the column that corresponds with the Data in Error field in your Roster File Errors table.
  6. Uncheck the boxes corresponding to the data you do not want to see.
    • Tip: Uncheck Select All and then check the box corresponding to the data in error.
 
How to filter in Excel 2011 or later for Mac:
  1. Select the top row with the header.
  2. Select the Data tab.
  3. Select Filter:

    Screenshot of Filter button in Microsoft Office for Mac
  4. Select the dropdown menu arrow in the column that corresponds with the Data in Error field in your Roster File Errors table.
  5. Uncheck the boxes corresponding to the data you do not want to see.
    • Tip: Uncheck Select All and then check the box corresponding to the data in error.
 
How to filter in Excel 2003:
  1. Select the header row.
  2. From the Data menu, select Filter.
  3. From the Filter submenu, select AutoFilter.
  4. Select the dropdown menu arrow in the column that corresponds with the Data in Error field in your Roster File Errors table.
  5. Uncheck the boxes corresponding to the data you do not want to see.
    • Tip: Uncheck Select All and then check the box corresponding to the data in error.
 
Example scenario:

Below is an example of a file format error you might see when importing your roster file:

Example Error

The Data in Error and Error Description columns indicate that the user name angelasmith123 cannot be the same for more than one instructor ID. This means that given user name is associated with more than one instructor ID in your roster.

To resolve this error, use the steps above to enable filtering. Filter the User Name column to show only the rows containing the user name in the Data in Error field.

Filter User Name

The Error Description field tells you that there is more than one instructor ID associated with this user name. When you look at the ID numbers listed in the Instructor ID column, the error should be evident:

Incorrect ID highlighted

Here you can see that the user name angelasmith123 is associated with more than one ID number: 9832493 and 983249. Determine which of the two ID numbers is correct and update the entries so that they match.

Article Number
1706

Attachments

    Outcomes