annual_school_census_workbook_user_manual
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
annual_school_census_workbook_user_manual [2020/03/23 06:03] – [Version] ghachey | annual_school_census_workbook_user_manual [2022/05/04 19:31] (current) – [Rollover] ghachey | ||
---|---|---|---|
Line 12: | Line 12: | ||
The workbook main objective is to improve the quality of data collected in a challenging environment where many do not have reliable Internet connection while building on tools that people know. It is designed to be updated by managing authorities twice a year (a) in September/ | The workbook main objective is to improve the quality of data collected in a challenging environment where many do not have reliable Internet connection while building on tools that people know. It is designed to be updated by managing authorities twice a year (a) in September/ | ||
+ | ==== Aims of this document ==== | ||
- | ===== Aims of this document ===== | ||
This is not a step-by-step how to document. Most Data and IT staff are comfortable working in excel, understand the schools data model and make rapid transition across to the workbook. This document therefore will aim to explain the key differences and tools. | This is not a step-by-step how to document. Most Data and IT staff are comfortable working in excel, understand the schools data model and make rapid transition across to the workbook. This document therefore will aim to explain the key differences and tools. | ||
- | + | ==== Main features of the workbook ==== | |
- | ===== Main features of the workbook | + | |
The workbook main objective is to improve the quality of data collected while still collecting granular student data in an challenging Pacific environment where many locations and schools still have no Internet connectivity. | The workbook main objective is to improve the quality of data collected while still collecting granular student data in an challenging Pacific environment where many locations and schools still have no Internet connectivity. | ||
Line 26: | Line 25: | ||
* **Merge**: a facility to merge workbooks to import one or more secondary workbooks. This merge feature can be used in a number of ways including merging whole workbook data into a newer master workbook that contains new features and bug fixes, merged individually completed workbook part (e.g. a single schools workbook) into a large workbook (e.g. a district or national workbooks) | * **Merge**: a facility to merge workbooks to import one or more secondary workbooks. This merge feature can be used in a number of ways including merging whole workbook data into a newer master workbook that contains new features and bug fixes, merged individually completed workbook part (e.g. a single schools workbook) into a large workbook (e.g. a district or national workbooks) | ||
- | ===== Version | + | |
+ | ==== Workbook Content | ||
+ | |||
+ | ^ Worksheet Name ^ Description/ | ||
+ | | Schools | ||
+ | | Students | ||
+ | | Student Summary | ||
+ | | Staff | This is where you enter all staff (teaching and non-teaching staff for each school.) | Visible | ||
+ | | Staff Summary | ||
+ | | WASH | Unicef Water and Sanitation/ | ||
+ | | WASH Summary | ||
+ | | SchoolsList | ||
+ | | SchoolByState | ||
+ | | Merge | Use this worksheet when you want to merge from another workbook into this workbook. | Hidden | ||
+ | | Rollover | ||
+ | | Settings | ||
+ | | Lists | All the validation lists are kept here. You may need to make temporary changes to this list from time to time, but discuss this with NDOE. | Hidden | ||
+ | |||
+ | ==== Hidden vs Visible Sheets ==== | ||
+ | |||
+ | Some sheet will be hidden by default as they should only be used by people with more experience with Excel and this workbook in particular. If you need to access one of the hidden worksheets go the Home menu then Format, Hide & Unhide, Unhide Sheet and then select the Sheet you wish to unhide. OR – Right click on any worksheet tab and select Unhide, then select the sheet to show. To Hide a sheet, right click on the tab for the worksheet and click Hide. | ||
+ | |||
+ | ==== Protected Sheets ==== | ||
+ | |||
+ | The worksheets are all protected by default which prevents the user from accidentally modifying (corrupting) formulas and other data quality mechanisms. However, this protection also prevents some desirable operations like MS Excel’s autofill tool (dragging a value to copy it to a range of cells). You may need to do this if, for example, you are entering new ECE enrolments and need to drag/copy a value such as the school name. To use autofill you will need to unprotect the worksheet. Do this by | ||
+ | |||
+ | - selecting the worksheet | ||
+ | - select Review from the menu and click on the unprotect worksheet button (see image below). | ||
+ | |||
+ | {{: | ||
+ | |||
+ | <note warning> | ||
+ | |||
+ | ==== Workbook Version | ||
Has the workbook constantly undergoes improvements it is important to make sure you are always using the latest and greatest version. Currently the best way to do this is to contact the people of the project you are working with. You can also check the version in the Settings sheet of the workbook as shown below. | Has the workbook constantly undergoes improvements it is important to make sure you are always using the latest and greatest version. Currently the best way to do this is to contact the people of the project you are working with. You can also check the version in the Settings sheet of the workbook as shown below. | ||
{{: | {{: | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ===== Data Validation ===== | ||
+ | |||
+ | The workbook helps improve data quality with some basic data validation. There are various types of validations which will be introduced below. | ||
+ | |||
+ | ==== Non-Conditional Required Data ==== | ||
+ | |||
+ | In **non-conditional cases**, certain cells always require a value. In the example below there are three crucial pieces of data missing: a Student ID, a Student First Name and a Student Last Name. The spreadsheet will highlight cells (by turning them red) if they are missing required data. | ||
+ | |||
+ | {{: | ||
+ | |||
+ | ==== Conditional Required Data ==== | ||
+ | |||
+ | In **conditional cases**, cells only require a value depending on the value in a different cell. In the example below the '' | ||
+ | |||
+ | {{: | ||
+ | |||
+ | ==== Dropdown List Validation ==== | ||
+ | |||
+ | It is very important to use the dropdown list data and not copy paste or write new data when there is a dropdown list. The reason for this is to improve the quality of data by only allowing specify choices that are standardized and pulled from the EMIS and loaded for use in the workbook. | ||
+ | |||
+ | Let's use school names as an example. The EMIS holds the official list of school names, and this list is loaded to the Excel data Workbook through a database connection. This is done automatically, | ||
+ | |||
+ | {{: | ||
+ | |||
+ | In the example below the workbook does not recognize '' | ||
+ | |||
+ | {{: | ||
+ | |||
+ | < | ||
+ | |||
+ | |||
+ | |||
===== Rollover ===== | ===== Rollover ===== | ||
- | <note warning> | + | <note warning>The rollover done in the workbook still work but is not supported and is no longer the recommended way to do a rollover. The rollover should be done directly within the EMIS online. Refer to [[https:// |
+ | |||
+ | <note important>Always use some test copies of your data before you perform this on your live data. You need to do a number of practice runs to familiarise yourself with how this works.</ | ||
The rollover function allows you to anticipate enrolments for a new school year. The function is based around the Completed and Outcome columns (Outcomes view) and the ‘From’ column in the Enrolment. | The rollover function allows you to anticipate enrolments for a new school year. The function is based around the Completed and Outcome columns (Outcomes view) and the ‘From’ column in the Enrolment. | ||
Line 128: | Line 208: | ||
FIXME | FIXME | ||
+ | |||
+ | |||
+ | ===== Advanced ===== | ||
+ | |||
+ | ==== Refresh Data Connection ==== | ||
+ | |||
+ | To improve data quality the workbook makes use of data connection to the EMIS database. It does that to retrieve the exact school name and various dropdown values. It can be useful to know how to refresh those data connection if any dropdown is updated in the EMIS for example. This is shown below. | ||
+ | |||
+ | {{ : | ||
+ |
annual_school_census_workbook_user_manual.1584943418.txt.gz · Last modified: 2021/02/02 02:10 (external edit)