Excel Spreadsheet and Word Template Guidelines

Here are my personal step-by-step guidelines and tips for designing your Microsoft Word templates and Excel spreadsheets.


Step 1: Organize Templates into Folders


Create a new folder on your desktop and label it Templates. Double click Templates and inside this folder, create a new folder for each individual school you are working at (i.e. Smith Elementary; Jones Middle School). If you are only in one school, skip this step. You can also skip this step if your templates are completely the same from one school to another (mine are not). Inside each school folder, create folders for all of the disability categories that you have templates for (i.e. Specific Learning Disability, Other Health Impairment, etc.). Once you completed making the folders of disability categories for one school, simply copy and paste the disability category folders into your other school’s folder.


Next, save the templates you have already made in their respective folders of the Templates folder. You may need to save your templates under a different title given this organization method. I typically save my templates stating: gender, initial or reeval, as well as any other notable factors (i.e. out of state transfer, ELL, SLD to OHI, etc.).


Now that you’ve organized your folders and placed existing templates in these folders, you may need to enhance your templates. Try to make as many variations of templates as necessary that you feel will save you time in the long run. In my SLD folder, I have over 20 variations saved, while far fewer in less common disability categories.


Once you feel your templates are thorough and organized in folders, the last step in organizing your folders is to create a folder in the Templates folder called Assessments.


Step 2 Work on Your Assessment Templates


The next step is to determine which specific assessment templates to make Excel links and formulas for. Only do this for assessment batteries you use frequently enough to save you time in the long run. It typically takes me an hour to create all the formulas and links for a specific assessment template; however, it may take up to three hours if you’re not as quick with a computer. (Why the keyboard shortcuts are so important). As a recommendation, start with the academic or cognitive assessment template you use most often, and save your current template to the folder labeled Assessment Templates.


You may want to tweak the language of your template now that you are choosing to link data from Excel. For example, before I used Microsoft Excel, I would write sentences such as:


Firstname obtained a score of 100, placing in the 50th percentile compared to same-aged peers.


I soon noticed that when linking numbers from Excel, if the student instead scored in the 21st percentile, it would come out like “21th percentile” on my template. Instead of making a long drawn-out formula for numbers that do not end in “th,” I found an easier solution- changing the template itself:


Firstname obtained a score of 100, which ranks as well as or better than 50 percent of same-aged peers.


It conveys the same meaning and is more efficient when linking numbers from Excel. Alternatively, you could just write something as simple as “obtained a score of 100, (50%ile). Another slight change you may want to consider is improving the visuals on your template. In my tables, I like to include categories such as percentile, confidence intervals, and description ranks (Average, Below Average, etc.) in addition to scaled and standard scores. By using the links on Excel, it takes the same amount of time to display this additional information.  All the numbers are generated instantly, meaning you can add more to your template without sacrificing time.


Furthermore, you can link an Excel graph to Word based on the data you input into Excel (See Graphing/Linking tutorial). This means you instantly have a graph for each student without any additional work. Because there is no difference in the amount of time it takes to generate all the information, it’s certainly worth it to find ways to improve upon your Word templates. It will help convey the data more clearly and as an added bonus, everyone will think you’re working a lot harder than you actually are!


Step 3. Create Excel Documents for Each Assessment Template


The next step is to create an Excel document to link the data from Excel into your assessment template. I would suggest saving the Excel documents in the Assessment Templates folder as well so that everything is in one place.


See Foundations of Scoring Assistants tutorial for a visual demonstration of the following information of step 3: Now create a sample student’s scoring assistant data and copy and paste the data from the scoring assistant of that particular assessment onto the Excel spreadsheet. It is important to document for personal record the exact data you copied and which cell you pasted it into. Next, create formulas and organize the data into one section of the spreadsheet. This organized section is where you are to link the data to your assessment template. Link the data into your assessment template. Make sure to save throughout this process so that you don’t lose your work if Microsoft Word unexpectedly quits.


When working on your Excel spreadsheet, it is important to make your Excel spreadsheets as organized as possible. As you’ll see in my tutorials, my Excel spreadsheets are so thoroughly detailed and organized that a school psychologist who’s never seen my Excel spreadsheets before could easily interpret what the data mean. In general, highlighting and labeling numbers are the organization tools I utilize the most. When everything is organized, it makes it easier for you to ensure that the links are correctly placed.


The last and final steps are to double-check that your links were placed properly and proofread your templates. One easy way to double-check your links is by checking that your assessment template numbers match the numbers on the scoring software. Continue checking your links until you are absolutely certain that every link was placed correctly. Next, proofread your template from start to finish to make sure the links make sense and flow naturally throughout the template.


Step 4: Putting the Templates into Practice


Once you’ve completed this method for one student, you’ve completed it for all. When starting to write a report on a new student, open your Excel document first. Input the data from the scoring assistant into Excel. Then open the assessment template. The template will ask you if you want to update the links. Click yes. All of the data should now be instantly generated into your assessment template.


Now copy and paste the report into your full report template (found in one of the disability category folders). Then break the links and Save As. Then save and close your assessment template and Excel document.


Regardless of how thorough your assessment templates are, it is essential to personalize your reports for each student. You’ll see in the generating sentences tutorial how to further personalize your report efficiently. While this is a helpful tool, it may not be enough to fully describe a student’s abilities. In this case, I recommend continuing to personalize the write-up for that particular student simply by typing the additional content into your report. If you notice that you’re writing similar sentences for many students, you may want to consider adding it to your dropdown lists.


Once you have added in all of the assessment templates into your new student’s report, the next step is to personalize and expand upon the student’s report. Add in all the necessary sections outside of the assessment results (i.e. background history, classroom observations, etc.). One great way to enhance the efficiency is by placing many drop-down menus throughout the report. To give a very basic example, in a Specific Learning Disability template, you may want to make a sentence such as:


His academic deficits are evident in the areas of (insert dropdown list here).


The dropdown list could possibly display the following options: reading; math; writing; reading and math; reading and writing; reading, math, and writing.


The next step is to proofread your full report. This really goes without saying, but I can’t emphasize enough how important it is. Reading the report on a student from start to finish every time is inefficient. There are going to be sentences that you use for every student no matter what, especially when using a highly specific template. There will also be sentences where the entire sentence will remain the same for each student except for one word. In that case, use either a dropdown list, or if there are too many options, simply use the text highlighter for that one word. It is strongly recommended that you use the text highlighter, and highlight yellow the words/sentences that you possibly need to change. If you do this for every template from start to finish, you’ll know exactly where to proofread every time.


Step-by-Step Summary of the Tips:


  1. Create a folder on your desktop called Templates
  2. Organize folders within Templates and place your current templates in their respective folder
  3. Also in Templates, create a new folder called Assessments
  4. Determine which assessments templates to create Excel links for
  5. Save these templates to Assessments folder
  6. Create Excel document for each assessment template in Assessment folder
  7. Save these Excel documents in Assessment folder
  8. Copy and paste scoring assistant data onto Excel spreadsheet
  9. Thoroughly organize the data in these spreadsheets
  10. Make links from Excel to assessment template
  11. Save often in case something goes wrong
  12. Double check the placement of your links initially after creating them
  13. Proofread your template to make sure the links flow smoothly
  14. Upon reaching this step, you can use the template for all new students
  15. Enter a new student’s data into your Excel spreadsheet
  16. Open assessment template specific to the Excel spreadsheet
  17. Upon opening template, it will ask if you want the links to be updated, click yes
  18. Highlight all written material on assessment template, and copy and paste it onto your disability category template.
  19. Break the links on your disability category template and Save As the student’s report
  20. Personalize and expand upon your student’s report
  21. Proofread each and every report.