California Department of Education, Special Education Division’s special project, California Services for Technical Assistance and Training (CalSTAT) is funded through a contract with the Napa County Office of Education. CalSTAT is partially funded from federal funds, State Grants #H027A080116A. Additional federal funds are provided from a federal competitively awarded State Personnel Development Grant to California (#H323A070011) provided from the U.S. Department of Education Part D of the Individuals with Disabilities Education act (IDEA). Opinions expressed herein are those of the authors and do not necessarily represent the position of the U. S. Department of Education.
Introduction
Reading the Chart
Finding the Data
Making the Chart
Advanced Charting
Contact Information
This chart was designed to illustrate California's Standards Test proficiency levels in a way that can be easily understood by teachers, parents, and administrators.
Most of us are more familiar with seeing the CST scores on the California Department of Education website in the form of a table. Seeing these same test scores in a chart like this, gives us a much more direct experience of what those numbers are saying.
For schools and districts that might like to create a chart of their CST scores, we designed an Excel file with an intuitive interface. You can enter data, press a button, and it will generate a chart for your school or district.
This chart and charting program were developed by the evaluation team of California's State Improvement Grant. A part of our mission is to make data accessible and useful. For more information go to www.calstat.org/CSTProficiencyCharting.html, where you can also find a copy of the Excel file for this free charting program.
To walk you through the different features of this chart and program we've created a series of brief movies. You can click on any movie in any order to learn what you need to get started.
Click on one of the movie buttons below to learn how to get started using this simple but powerful charting tool.
The default charts generated by the CST charting program show the California Standards Test, English Language Arts scores for students in California in 2005. Students are scored according to their level of proficiency, and we've used colors in this chart to show the different levels of proficiency.
This chart shows scores for 4th, 7th and 10th graders. For each grade level, there are two bars showing proficiency: one for all students, and one for students with disabilities. Each of these bars is divided to represent the percentage of students falling into each proficiency level, and are color-coded correspondingly. The number at the top of the column shows the total percentage of students who are Proficient or above, including Proficient (yellow) and Advanced (purple). Down at the bottom of the column, the figure there shows the percentage of students that are less than Proficient, which includes Far Below Basic (red), Below Basic (blue), and Basic (green) categories. We show these figures for all students and students with disabilities.
It's a sobering reality that the majority of students are less than proficient.
To get a sense of the progress being made toward increasing proficiency we can compare the 2005 numbers with the 2004 numbers. These are both collected above the chart, with 2005 figures arranged above 2004 figures. In 2004, 39% of 4th grade students in California were proficient, in 2005, 47%. In 2004, 16% of students with disabilities in 4th grade were proficient, in 2005, 19%. So, an additional 8% of all 4th grade students and an additional 3% of 4th grade students with disabilities reached proficiency. You can see a similar pattern among 7th graders, where 7% of all students (36% increasing to 43%) and an additional 3% of students with disabilities (7% increasing to 10%) reached proficiency. But among 10th graders, there was virtually no change (35 to 36% among all students, 5% to 5% among students with disabilities).
Comparing the chart of your school or district to the state chart or the charts of similar schools is very useful as well. It's easy to see differences in the amount of red there is in one chart as compared to another, or the amount of yellow or purple, when you look at the charts side by side.
Since students with disabilities make up approximately 10% of all students in California, it follows that 10% of the test scores on the bars showing all students, are those of students with disabilities. To ascertain how that 10% is broken down by level of proficiency, look to the bars showing the test scores of students with disabilities. Then you simply shift the decimal point. For example, looking at the English Language Arts test scores of 4th grade students with disabilities, we find the following figures:
7% Advanced, becomes .7%,
12% Proficient, become 1.2%,
24% Basic, becomes 2.4%,
24% Below Basic becomes 2.4%,
and Far Below Basic 32%, becomes 3.2.
These numbers, .7, 1.2, 2.4, 2.4, 3.2, add up to 9.9%. With a rounding error, this is the 10% of all students which is expected.
This also allows us to examine the 4th grade proficiency levels for all students factoring in the scoring of students with disabilities, and students without disabilities. For example, if 24% of students with disabilities and 30% of all students at Basic, we can reason that 2.4% of these are students with disabilities and 27.6% are students without disabilities. The 14% of students who are Below Basic is made up of 2.4% students with disabilities, and 11.6% students without disabilities.
What the data in the chart doesn't tell us is the relationship of proficiency to either specific disability categories or how it might be impacted by students being English Language Learners or socioeconomically disadvantaged. Nor does it factor in the percentage of students testing out of grade level. Nevertheless, this chart provides an excellent place to begin in closely examining proficiency levels and initiating focused discussion.
Now that you know how to read the chart, you are ready to learn how to find the data or if you already know where to find the data, then move onto making the chart.
To find the California Standards Test scores for your district or school you begin at the California Department of Education website, www.cde.ca.gov. Then come down to the Standardized Testing and Reporting (STAR) link and click on in.
On this page, scroll down to Test Results Reporting Site. And click on that link.
This is the main page on which you can get STAR test information including STAR test results for 2005 (http://star.cde.ca.gov). Scroll down the page for 2004 and earlier years. For now lets focus on STAR 2005 Test Results for school, district, county and statewide summary results. Click on the link.
On this page go to the upper right hand corner, click on the link for Test Results.
This is the main interface from which you can get the test results for your county, district, or school. So, just go up to the county combo box, and we'll select Contra Costa, and then we'll select Antioch Unified from the district combo box. Now, to see a report for all students in Antioch Unified School District, click on View Report.
This report will show the Antioch Unified School District test scores for all students. And if you scroll down the page, you will see there will be English Language Arts scores, as well as Mathematics, Social Science, History, Life Sciences, Chemistry, and Physics. To print out the report, click on this Print Report button. I would suggest you go under preference and select landscape as the orientation so that all the test scores will fit across the page, and then print.
You'll also need to print the test scores for students with disabilities. Go up to the Group combo box and select disabilities, and then under the Subgroup combo box select Students with Disability. Then click View Report and Print Report.
Now you might also want to print out test scores for a school in Antioch Unified. So come up here to school, and we will select Antioch Middle School. We still have the group Students with Disabilities; click view report. Now when you scroll down and look at the English Language Arts scores for students with disabilities. What you will notice is that it only reports on the percent of students that are proficient or above at each of the three grade levels. It doesn't break it down into all the different levels of proficiency. And the reason for this is there are only 40 or 50 students at each grade level, so if you broke it down into the different categories you might be able to identify an individual student. So in order to respect student privacy, only the percent proficient or above have been put on this chart. And we made an accommodation for this in the charting program. You will want to click on print this report and then finally you'll want to go and print the all students report for the school.
Now, another thing I want to show you is that if you click on this Group drop down menu, there are a number of different groups for which you can get reports. For example, if you want to look at English-Language Fluency, you can click on that group and then be sure to look at the different choices for sub-group to make sure you get the one that's most appropriate, and then you can look at that report.
And finally, you want to go back to this page (http://star.cde.ca.gov) where you can access all the different years of STAR test results. And scroll down the page until you see Prior Years STAR Data and Additional Information, and click on STAR 2004.
And then again up here for Test Results.
And you will then get that familiar interface. But, this time you'll be able to access the 2004 STAR test results. It's worth doing this because then you'll be able to compare the scores from your school or district in 2004 and 2005.
Now that you've got the data, you're ready to learn how to make the chart.
Now you're ready to learn how to work with the Excel file we've created, and generate a chart for your school or district.
The first step is to open Microsoft Excel. And under the Tools pull down menu, to find Macro, and then Security from the Macro side menu. When the options window opens, you'll see that there are three levels of security. To be able to use the macros in this program we've designed, you need to set the security level to Medium. What this means is that if you open an Excel file that has macros in it, it will ask you whether you want to enable the macros or not. So, you still have that level of safety, but if you open a file which has macros you will want to use, like the CST Charting program, Excel won't automatically disable them. Once you've set the security level, it will remain that way, and you won't have to do this each time. Click, OK.
Now you are ready to work with the Excel file that we've created for generating charts. And there are a couple of different ways you can get a hold of the file. One is to download it from www.calstat.org/CSTProficiencyCharting.htm, under CST Proficiency Charting. The other is you can request a CD-Rom of the file from cst.charting@sonoma.edu. When you have a copy of the file, you open it like you would any other Excel file, you just click on the file.
Now, here's where you get your chance to enable macros so that this charting program will work. And again, for this file I would encourage you to enable macros, but for other files use your discretion. Click the Enable Macros button.
When the file opens, if it is not the right size for your computer monitor, just use the zoom control to find a size that works for your computer, and then once you've saved the file it will open at that size. If the zoom control isn't on your toolbar, it is also located on the View pull-down menu.
Your next step is to choose which CST test you want to chart, and you'll see a pull down menu in the top-center of the page. English-Language Arts is the default, but we have all of the different CST tests listed. For now, lets use English Language Arts. Once you've selected the test it's a good time to save the file with a new file name. And go to File, Save As. This is CST Charting 2005; lets say our district is Crocodile Unified School District, so, we'll say CUSD; and it's the English Language Arts, so we'll say ELA. Thus, we get a filename CSTCharting05CUSDELA. Click Save.
There are three different sections, one for Schools, another for Districts, and a third for the State of California. To get you started, we filled in the data for the State of California for 2005. And to see what that looks like, just click on the Data clipboard under the State of California.
What we have done here is just taken the data that we got off the CDE website and we've entered the proficiency levels for All Students and Students with Disabilities, for 4th graders, 7th graders, and 10th graders. And then you can also see as we scroll down the page, that we entered for 3rd, 4th , and 5th graders, 6th, 7th, and 8th graders, and 9th, 10th, and 11th graders. So now, we'll be able to generate charts for all of these different groupings of grade levels. And to do that, just go up to this Go To Main Page banner, click on it.
And it will take you back to the main page. And then under the State of California, click on the Charts icon, and it will take you to a chart of the data.
And here's your chart. Now you can't see the whole page from this view, but if you go up to File, go Print Preview, you'll be able to see the whole chart in the frame. From here, you can print normally or if you have Adobe Acrobat, you can print a PDF. And to do that, still click on the Print button. For normal printing you would normally just click OK, but to make a PDF, you go to the menu with the name of the printer and Adobe PDF will be one of the choices, then you click OK. It will open a save dialogue to allow you to put the new PDF wherever you want; I'm just going to put this on my Desktop for the moment. And save the file as a PDF. With a PDF you can easily share the chart with colleges as an email attachment or you can put it on a website and be able to have access and share this.
Now you might recall, on the data page we had entered data on several different grade level groupings. And if you look in this drop down menu of charts on the upper-left, you'll see that those different options are available to us. So if you want to look at a chart of the Statewide 3rd, 4th, and 5th graders, you just click on that, it will shift the chart. If you want to look at 6th, 7th, and 8th graders, you can generate the chart, same for 9th, 10th, and 11th graders.
So now let's go back and look at the main menu by clicking the link at the top of the page.
Now it's time to learn how to generate a chart for your district or school. The first step is to enter data from your district. So under the district section, click on the Data clipboard, and it will take you to the data page.
So on this page we are going to fill in the yellow cells with data that we got from the CDE website. Our first step is to give your chart a name, replacing the text "zDistrict001" in the far-left column. We'll type in Crocodile USD. Next, we want to decide which grade levels to use for this chart. And so that we can compare them with the state chart lets go with 4th, 7th, and 10th. Now you can see on the left hand side we've listed Advanced through Far Below Basic, all the different proficiency levels in descending order. So, the next step is to enter district scores for 4th, 7th, and 10th graders. And you'll see there is a column at each grade level for All Students and a column for Students with Disabilities. Now we'll enter that data for our Crocodile Unified School District.
It is important to type in just the number, rather than the number and a percent sign. For example, Advanced, 4th grade is 37 instead of 37%. Now if you'd also like to do a chart for the middle school in your district you just want to name another chart, so we'll type Crocodile USD 6-8, replacing "zDistrict002." And, now we've already got the 7th graders typed in, so we can just Copy, and Paste this into the next data block. Now I'll just take a moment to type in the 6th and 8th grade scores. So, now that we have our data typed in, we're ready to generate charts. To get to the charts page, you can click on the banner in the upper right hand corner, which will take you to the main page, and then you can click on the charts icon. Or, there is another way to navigate your way around. If you look in the upper left, there are two go to boxes: Go to Data, and Go to Charts. If we click on Go to Charts, you can go directly to district charts.
And here is a chart of the data we just entered for Crocodile USD. We entered two different sets of data. If you want to look at the other chart, you just look at the pull down menu in the upper-left of the page and you can go Crocodile USD 6th through 8th and it will produce that chart. And again, if you want to get a good look at it, you can just go to print preview, and print. It's as simple as that to create a chart of your district's data.
Now you're ready to learn how to make charts for a school. From the main menu, go to data under school. And you'll see that the school data page looks very similar to the state and district data pages. And you want to go about it the same way, you want to type in the name of the school that your are doing a chart for and decide which grade levels you are going to look at, then type in the data for all students and students with disabilities. The only difference here is the students with disabilities column: rather than having yellow cells for each of the proficiency levels it only has them down at the bottom, for Advanced and Proficient, and Below Basic, Far Below Basic, and Basic. And the reason for that is that on the CDE website at the school level they will only put the percentage of students that are proficient or above, which is essentially the Advanced and Proficient levels. The reason for this is that there are usually so few students with disabilities at a school, that once you break it down by grade level and then by proficiency level you might be able to identify individual students. In order to protect student privacy they will only report on the Advanced and Proficient in combination.
So what you want to do for the students with disabilities is just type in the percentage that you get off the CDE website for Proficient or above, and subtract that from 100 and then whatever is left over would be the figure that goes in the second yellow cell for Basic, Below Basic, and Far Below Basic. And one little trick to remember for this part is that if you end up needing to type in a 0% or a 100%, it freaks out the program a little bit, so just type in .1 or 99.9 instead of zero or 100 into the these students with disabilities cells. And then the program itself will round it out and the charts will come out perfectly.
So, I'm going to take a moment and enter the data for a couple of schools. And here you can see I've entered in data for Bonita Middle School and Dolphin Elementary School. Now there is room enough in this program that you can enter in data on up to 100 different schools or combinations of different grade levels at schools. So, that should be plenty of room to enable you to generate as many charts as you would like. To get a look at the charts for Bonita Middle School and Dolphin Elementary, we'll go up to the upper-left drop down menu Go to Charts, and then school chart.
And here is the chart for Bonita Middle School, and again you go to this pull down menu and there's Dolphin Elementary. Now lets get a look at them in print preview. These look a little bit different than the district or state charts, because again for the students with disabilities we had to only report on the percentage of students above the proficiency line and below the proficiency line. Consequently there are only two colors in the students with disabilities bar, orange for proficient and above and teal for less than proficient.
Now if you're at a school, you have for your own internal use, details on all the different proficiency levels percentages for students with disabilities at your school, and you want to generate a chart that shows that. What you should do is use the district section of this program, which does the all students and students with disabilities; it will do the chart for all the different proficiency levels. And just enter the school data into the district section and give it the school title so that you are sure it is a school and you can generate a chart that will work, in that way.
Now that you know the basics on how to use this, program to make charts. You might take a few minutes to look at the advanced charting movie. It just gives you a few additional tips and tricks for using some of the advanced features we put into the program.
For those of you who are now familiar with how to use this charting program, I wanted to show you some advanced features that we've built in so that you can have more versatility in what you do and what you chart. Be sure and watch the end of this movie because we've recently built in the capability of creating comparison charts from one year to another or of different subgroups. So here are descriptions of the advanced features and how to use them.
The first feature is useful for duplicating the Excel file for additional years. We wanted you to be able to use this program for multiple years and we currently have on the www.calstat.org website a 2005 version and a 2004 version. But say you wanted to go back and do a 2003 version for example. The first thing you should do is save it as another file. For example, we'll go to Save As and instead of 05 we'll change it to 03. Then find on the front page of the program a yellow square labeled Enter School Year For Label on Charts; you just double click in there and you can change that to the year you want. We are changing it from 2005 to say 2003. Now when you view a chart you will find a 2003 label.
However, the data on these charts is now wrong, because it reflects 2005 data despite the 2003 label. The next step is to delete these erroneous figures before replacing them with 2003 information.
Now you go into the data pages and find the delete all data button in the upper-right hand corner of the page (you may have to scroll right to find it), which we've put a big red "X" over because of course you usually don't want to wipe out your data. But, in this case you do. So go ahead and click the button, and it will give you a warning so that in case you click on it by mistake and you realize "well wait I forgot to save it as a different year" then you can click no. But in this case we do want to clear data, and click yes. Another menu pops up with a few choices: first you can delete all the test score data, the names of the schools, and the grade levels; second you can delete just the test score data; or third, you can still back out and cancel.
If you want to delete all of the data, including the names and the grade levels and just wipe it out and start fresh, then you can press this button and you it will take that back to the beginning.
If you press on delete data only, it will clear all of these data, but it will keep all of your names of all the types of charts that you wanted statewide: 3rd, 4th, 5th and 6th, 7th, 8th. Or you might have a list a whole bunch of different districts and you wanted to save the names of those districts so you only have to re-enter data. Then just pressing the Delete Data Only button will take care of that. If you were pleased with your 2005 charts and wanted to generate different versions of exactly the same thing for a different year, this is the option you want, leaving you only with the job of matching your new data to the right data fields.
Now you are going to have to delete data at the state, district, and school level. And go back to the main page. And then you'll want to go and clear the schools data, using the same process. And then be sure to resave. Data all tends to look alike and now that the data in your fields is labeled with the wrong year, it is best to delete it as soon as possible in order to avoid confusion later on.
Now the other tip I wanted to give you has to do with managing when you have a whole bunch of different data. For example, lets say we are looking at schools. And we are under data, and so lets say we had the data for Antioch Middle School, but then say we also had a Dolphin Elementary and we also had Miller Middle School, and we also had Adams Elementary. Now, lets say you are a school district and you were doing these charts for all the different schools in your school district, the list could get fairly long. We've built this program so you could list 100 different schools in this Excel file. So once you've entered all the different school names what you can do is go to this ABC Order button in the upper-left of the page (you may have to scroll left), and press it. And it will alphabetize the schools for you. So that way when go into the school charting and you have this pull down menu then things will be alphabetized and it will just make it easier for you to be able to see what your choices are. And be able to find the charts that you want to generate.
Another feature that we added is the ability to generate charts for different CST tests. So if you look at this drop down menu in the top-middle of the main menu, you'll see that in addition to English Language Arts there are tests for mathematics, geometry, etc. Now, before selecting one of those I would suggest you do a File, Save As and we'll go with math. Once you've done that you can select mathematics, and then what will come up is the option for clearing all the existing data, which is important because now you've changed the file, you need whole different test scores. So I would say yes, for the same reasons as when changing years. Again you have an opportunity to erase all of the data, including the names of the schools and the grade levels, or you can chose to just delete all of the test score data only (which is what I would suggest). Now mathematics proficiency has been substituted for English Language Arts in all charts, and you'll need to go to the data pages and enter the state level, district level, and school level data on mathematics proficiency.
Next, we'll show you how to create comparison charts. Now looking back at the Statewide English Language Arts data for a minute, I want to show you the most exciting new feature of this program. There are some green cells at the top of the columns for All Students and Students with Disabilities. We've made it now so that you can edit those column titles, which will enable you to create comparison charts. For example, if you wanted to look at All Students in comparison with English language learners then you just click in this cell and replace "Students with Disabilities" with "ELL." And then put in the data for English language learners. And once you've entered the English language learner data you can go to charts and it will show a state chart comparing All Students and English language learners. We can go to print preview to get a better look at it.
And finally, if you wanted to look statewide, at all students, comparing 2004 and 2005 scores, you can do that by changing the column headings to 2004 and 2005. What you want to do is the all students 4th grade data and put it under 2005, and then go ahead and fill in the 2004, 4th grade data in its column. So here, I've filled in all of the 2004 and 2005 comparison data, so that we can generate a chart. The chart that is generated compares years instead of comparing students with disabilities to all students. Having this sort of chart enables you to look side by side at all students or any subgroup you want to look at from one year to another. And it helps to more easily visualize the change that is actually occurring.
Now on the CD ROM that contains these instructional movies, there is also a file called CST Charting demo version. And if you look in that file under districts section, you'll find that if you click on the charts pull-down menu, there are a variety of different kinds of charts we've created examples of. As well, there are, templates of all the headings, and layouts for those charts, that you can copy, paste, and modify as you wish to generate these kinds of charts for your school or district. Specifically you can see in the list that in addition to the regular layout, and the layout for year-to-year comparisons by grade, which you have already seen, there are charts for making year comparisons across several years, for tracking a cohort, and for looking at subgroups in different ways.
The year comparisons chart enables you to look at forth graders in several different years. And since 4th grade is one of the grade levels that is looked at for tracking statewide, it gives you a chance to see how 4th graders are over multiple years. The chart for tracking a cohort enables you to follow a group of students as they move through your school or district. So, students that are 4th graders in 2004 are 5th graders in 2005, and 6th graders in 2006. And you can see their progress. The chart for comparing subgroups enables you to look at a single grade level, for a single year, like 4th graders in 2005. But look at a wide variety of subgroups of students in 4th grade. And you can choose, whatever subgroups you want, just by changing the titles, which I will show you how to do in a moment. And there is a similar chart for looking at a single grade level, in a single year, where it is broken down by ethnicity.
And finally, there is a chart for looking at subgroups that allow you to make year-to-year comparisons. So, you can choose three different subgroups and compare their 2004 and their 2005 scores.
Now if you shift to the district data page, you can see the data that is used to generate these charts and for the most part it is the same as the regular layout you've been using all along. The two places where it is different are in the green cells, where we've put in different column headings, for looking at several years or looking at different subgroups of students. And in the other, one is in the line right under the green cells called Grade Level or Your Choice. Where again we just modified it according to the different types of charts we are using. And if you continue to scroll down the data page you will see blank templates that have all of the chart titles, column headings, but don't have the students' data. And you can copy and paste these and use them to enter your school or districts data to generate these different kinds of charts. And of course you can modify them to create whatever types of charts you want.
It is our hope that that you'll be able to use this charting program, to get a really good idea of what's going on with the state and the students you are serving and supporting. And that you'll be able to use it to watch them make magnificent gains in proficiency over the years.
This chart and charting program, and this demonstration come to you from the SIG evaluation team, which is comprised of Cheryl "Li" Walter, and Kelly Bucy. SIG stands for the State Improvement Grant, which is a grant received by the State of California Department of Education, Special Education Division from the Federal Office of Special Education programs to support system change and improving the outcomes for students with disabilities.
This database was developed by the California SIG Evaluation Team and Karen Walter
Funds for this project come in part from federal funds awarded as a State Program Improvement Grant to California (CFDA 84.323A) allowed In Part D of Public Law 108-446, the Individuals with Disabilities Education Act (IDEA), as amended in 2004. These dollars are considered local assistance funds and will assist individuals serving children birth to 22 years of age and their families.
For questions contact:
Kelly Bucy
CalSTAT/CIHS/Sonoma State University
cst.charting@sonoma.edu
707-849-2268