If the district student program enrollment counts do not match CALPADS, then a district must determine the cause of the difference?  Although a district may post a file - error free, the district still needs to evaluate and ensure that the information is accurate.  A file may be error free, but contain inaccurate data.  Or, there may be open records at CALPADS that are error free, but need to be closed.


Even with limited Excel skills, there are some simple steps that can easily be done in Excel to check and compare a district's data to CALPADS.  


*If any of the snapshots appear too small, please click the snapshot for a larger view.

In CALPADS:

  • Open the 1.1 Enrollment Primary Status by Subgroups Report.




  • Select the school to compare by clicking the school hyperlink.
  • Export to Excel.



  • Once in Excel, clean up the spreadsheet to remove all the CALPADS header information so that the spreadsheet will be easier to navigate.




  • Delete the extra rows that house the CALPADS logo information.  



  • Delete any columns that are unneeded. Note:  CALPADS will sometimes have multiple columns for one field -  see Student Name (below), it has columns H and I.



  • Place a filter on the header row (School Code, School Name, Primary Enrollments, Englisher Learners, etc.).




  • To simplify this example, only the English Language Learners will be compared - all other program information will be removed (columns containing program information was deleted). The spreadsheet should reflect the SSID, Student Name and Program.  In column F to the right of the program, place a "C" for every row (column  F - English Learner is using two columns, D and E).  




  • To add a "C" to column F, add a "C" and then double click the small square at the bottom of cell 2F (see diagram) to auto-populate the column.





In Aeries:

  • Query the program data in Aeries and export to Excel.  Be sure to include the student's SSID (STU.CID) and student's name (STU.NM) in the query and and add "A" to the last column.  Use the same order as the CALPADS spreadsheet.  

          For this example query STU.LF:  LIST STU CID NM LF A




  • The query extract to Excel will produce the following:




To Compare:

  • In Excel, copy the Aeries spreadsheet and paste into the CALPADS spreadsheet by aligning the columns. The SSID column must be aligned!  If all the columns are aligned, the navigation will be easier but it is not necessary.

          For this example:  We renamed column F as Source.



  • In Excel, highlight the SSID column, select Conditional Formatting, select Highlight Cells Rules, select Duplicate Values.  




  • The duplicated SSIDs will be highlighted pink.



  • Sort by Cell Color.  



  • Please note that you could receive a message the "all the merged cells need to be the same size" in your spreadsheet.  This can happen because the CALPADS extract into Excel may contain hidden columns.  To fix:  highlight the entire spreadsheet and click the Merge and Center button.  Then proceed to final step.




  • The results will provide you a list of students that are enrolled with CALPADS that not being reported in Aeries (see yellow highlight) and students enrolled with Aeries that are not being reported in CALPADS (see blue highlight).



Resolve the anomalies and insure accurate reporting in CALPADS.