Dive deeper into formatting numbers to learn common date errors and how to format numbers correctly for the graduates file.
This session is intended for secondary schools active with StudentTracker.
Resources
- Sample Graduates File (XLS)
- Submit a Graduates File Guide (DocX)
- Formatting and Submitting a Graduates File
- Common Graduates File Submission Errors
Transcript
You need to format the social security numbers, student IDs and high school ACT codes to ensure leading zeros are not dropped. Let's format numbers as text elements by looking specifically at column J the student ID. This same logic can be applied to Social Security numbers and ACT codes in columns B and O respectively.
In column J, some values are formatted differently. Those aligned left are text. Numbers are aligned right. Student ID must be stored as text because we want to keep the leading zero so we want them to be left aligned. If you select cells J2, 5, 7, 9, 10 or 11, you will notice that they are formatted as general. If you change the formatting to number, it will create decimals which we do not need. Format as text and they left align.
Look at cell's J4 and J8 which are formatted as general but still aligned left. If you click these cells, you'll see that the formula bar contains an apostrophe which tells Excel to read the data as text.
In this example, student IDs should have a set number of digits, four digits. Notice that cells J10 and J11 only have three. Why? Because they're missing the leading zero. Make sure they're formatted as text and then add the leading zero.
Be careful when clicking and dragging your ACT code when you work because you could inadvertently cause each subsequent cell to increase by one. For instance, clicking and dragging the ACT code will cause each student to have a different ACT code. One way to address this is to copy and paste one ACT code into three consecutive cells, highlight those three cells and then click and drag. This will ensure every student has the same ACT code.
There's another way to fill all detail rows with a single ACT code. Let's say you want to enter the same ACT code into rows 2 through 11 in column o. Start by selecting cell O2, then in the name box which is located in the upper left corner of the file change the value to O2 : O11 and press enter. Type the ACT code, hold down control and press enter. The same value has been entered into rows 2 through 11 for column o.
This concludes formatting numbers as text in Excel.