X-Y Chart on a Mac – Step 2: Chart Construction

last time we downloaded a spreadsheet from schooldigger calm that was the the website i was using to get data on schools for the entire United States I could go to other sources but I found that one to be consistently robust and has a very similar format for all states so what I have here and what you would have and what state you’re looking at would be very similar i’m looking at oregon i have moved feels over into column a and column B that I want to plot in a chart Colome being free and reduced meal percentage of kids in schools and be the y axis is going to be average scores that those kids or those schools generated it’s important to know that column a is always thought by Excel to be the default x value so and the why is column be you could put data into column C as well if you’re going to make a three-dimensional chart which I’m not not going to do here in either minute the first thing I like to do is make sure that after I’ve selected these two feels make sure the data is numerical and I do that by doing a right click format cells I click numerical right here sometimes it’s highlighted as general its a mix of text and other things when i click numerical i make sure it has the right number of characters after the decimal zero to one or two and say okay so now it if I sort it if I plot it Excel does not have trouble because it’s all numbers I sometimes sorted by school district to so that I know if I’m going to plot are going to look at a particular school streak I select I can i can find that districts schools very quickly so i select the whole data sheet i go up the data sort and i have highlighted my list has headers so i can pick one of those headers and in this case i’ll sort by district and bingo it all comes out by district and you can see right here is the district starting with a’s going down to you know beaverton salem all those it goes through the complete set of oregon schools but let’s go back to plotting i’m going to make this chart now from columns a and B and i just select those two columns a lot of data here like 1300 different schools in the state some states have even more than that i’m going to just select some of them and then go down to the bottom drag my elevator down here to the bottom and i go over to the B column the bottom right corner that I’m trying to select hold down the shift key and click it and it selects that whole double column A&B now that I’ve selected the fields x-axis and y-axis I can go up to chart and use the Excel 2011 for a Mac chart feature might click it it gives me options I want this one without lines between my various data points you could pick others but that’s what I found most valuable and I get this dummy chart all of a sudden I in its embedded inside of your data spreadsheet and that doesn’t be much good you could print print it you can manipulate it it’s easiest to right click on it and to say move chart and i’ll call it to dimension to the oregon schools and when i click ok it creates a new tab down here called 2d oregon schools and it’s got its own its own page there’s a few things that i don’t light so i’m going to have to fix them first off it doesn’t have a least squares line through it they haven’t averaged all these data points and to do that it used to be really tough to do this is simple you just click this

little box as a line through it and it changes the whole layout of the the chart and it puts in a lease or best fit line through it and you can see there are there’s a legend over here i don’t really need that it’s kind of occupying real estate so i’ll click one of the fields and i delete it then i click the other field and delete it it opens up the chart spreads it across the page so i can read it a little bit better the axes right now are okay this is 200 and that’s the maximum score that you could get and one hundred percent is the maximum if you need to change those you do that on the next tab over it says chart layout and under chart layout first i can add a title it has no title on this chart right now i add one it puts in a dummy title and i’ll put in all organ schools i always like to add more detail because there are educators that want to know where you got your data you know what is it you’re plotting and i would say that this is to begin with combined reading i’ll just put our and math they call it an Oaks test I don’t know what Oaks stands for but the educators would Oaks combined reading math oaks for the fourth the eighth and tenth grades and these are average scores so I’ve got combined average reading and math oaks for the fourth age and that’s just my y-axis on the x-axis I’m going to have percent eligible for and you can you can clean this up free and reduced meal there are folks that absolutely will want to know what this data is and so I try to find a way to maybe reduce the size and fit it all in there sometimes it’s a little messy to read but it solves a lot of quick questions if you will and then I identify the two different axes here this is combined test scores and you can you can add more to that or less it’s up to you correct spelling all that stuff I’ll do that later and this is a percent free and reduced meal if i want to change these light like I’ve got 200.0 well it’s only 200 so again you go up here and you’ll find something called axis and you can look at the vertical axis the one I was just messing with go to the bottom that says you know other options and I can change all kinds of things with that how big it is the we’ll all kinds of different now let me go back and do that again here’s the horizontal axis axis title options I’m in axis titles what I need to do is go over here to the axis itself they look just the same and I’m going to go down here to access options and that’s what i was looking for right here it says number and i can first uncheck the link to the source data and then i can manipulate this down to zero where there’s nothing after the decimal place and down here on the percents it now looks like a percent there’s no decimal point after say 100 or after 90 i can

then do the same thing under axis under the vertical axis axis options unlink the data make it 0 and i get rid of the point 200.0 point 1 whatever i’m going to save it right here just to be careful I may lose it on the next couple steps the next one is to change the data point color what you see here is a blue little diamonds the diamonds are okay but I don’t like the the blue is a little heavy and I want to be able to see more of that center section that is kind of has a lot of schools with the same data points so I just click any one of the items and this is you got to be careful at first I click out here outside so I select the chart then I just go inside and click one data point item and it ends up selecting a whole bunch of them actually all of them and when I’ve selected one I can then do a right click and it says select data or it says format data in this case I’m going to format the data series and one of the choices is marker fill and it’s an automatic that is Excel will use this blue diamond as a default I can change that to a different color I can say no Phil and in fact no field looks pretty good because as you can see I can start seeing the heavy data points in the middle there so I’m going to leave it at that I can also change the size or what they actually are they they’re diamonds and there at nine size of nine and I think I’ll leave that for right now so that looks much better again I’m going to save it because next week I’m thinking of visiting one of the schools hypothetically and one of the school districts and it would be nice to be able to show that particular district so again I’ll click outside the spreadsheet out in this gray area and I’ll go in and I’ll select one piece of data and it highlights all of them in that data series and I’ll then do a right-click on the surface and I’ll do select data now it has in this dialog box it is brought up series one this is the one you have clicked all the data points so far are called series 1 i’m going to add a new data point which includes some of the data that I’ve already plotted but I want to highlight specific pieces of data so I’ll add a new series and this series I’ll call beaverton because that’s the school district I’m going to visit so I add the title beaverton so I’ll know what it is now this gets a little tricky I on the x-axis I click here and I have to go back then once this little select data source comes up I have to go back to the original spreadsheet that I’ve been working from and I have to find the school district within this data that I want to identify or have it stand out and here the beaverton school district is located right here on row 37 that’s where it starts so I’ll highlight the X values only that’s Colome for the beaverton school district and i’ll drag this down until I no longer in column G see the word beaverton and there it is and Beaverton ends in 94 so I will select all the way down to 94 you can’t see how it is selected because it’s just kind of a blinking line there I can see it that you probably can on your computer monitor then I go up here and click the icon that says that’s the X values now I want to select the Y values

so I click this and I repeat the process and only this time I think I’ll do reverse I’m already sitting at 94 so I’ll select 94 beaverton y value in the in the B column and go up to where beaverton starts and that was at 37 so I’ve highlighted that and I click it and now I’m done when I click OK you will now see the beaverton school district schools that are identified with an X and a why marker let me click one of them because if i select just one of them and then do a right-click and do format I can actually change the fill I can change them to a different color in this case i’ll make them black and i can also change the marker style i go down here quick style it’s automatic i’m going to change it to this little X in the middle and you can start to see how these data points are changing down here as I do this and I’m going to make a little smaller too so there they don’t stand out quite so much and now you can see the district you’re going to visit you can see exactly where all of their schools like compared to the whole state of Oregon and I’m going to change the title now so that I know this is the school district that I’m dealing with and and they could actually hang this chart on the wall they could frame it and say these are our schools and how we’re doing and which ones we need to work on and in in terms of performance you can already see that these are doing quite well compared to others that are having that degree of poverty these obviously need some work and so you know specifically what schools you can talk to them about which ones that are below or perhaps they should be I’m going to save it again because the next step sometimes you got to be very careful because it will blow up your Excel spreadsheet you have to restart it what I’m going to try and do is add labels and I’m using a product from application professionals on the web you can do a search for chart labeler and this XY chart labeler is a add-in usually a free app that goes into Excel and there’s explanation how to add it into your excel on the web they provide that for you when you download it I’m again going to identify one of the beaverton school data points then I’m going to go up here to tools and i’ve added in XY chart labeler down here at the bottom it shows it under tools and i’m going to add chart labels and this will be the beaverton school district and i need to select where the data is and it again brings up a similar little icon on dialog box I when I click this thing I can then go over to the original data and I need to select the name of the schools and they are right in this area names of the schools here is beaverton school district here is the the column with schools in it and I’ll start again with 37 and if I miss that if I get out of alignment see I get 36 row 36 instead of 37 the thing will report an air and then it will not start again for me i’ll have to go back and restart everything I’ve done and again the bottom and last school is number 94 it’s row number 94 so I click that it also says where do you want to put the label to the right to laugh up down I’m going to say to the right just make a guess I can move them around little bit that’s a lot of work it’s better to try to get it right and I

click OK and bingo the names all start to appear and they’re messy because there are big names I can shorten the names make it a little bit easier to read I can blow this chart up make it larger I can click on one of them and move the label around a bit so you can read it but i’ll let you decide that if you’re visiting a district with just five or six schools you know it’s no problem they’ll come up almost perfect for the first time but a larger district like this you get a clump and it’ll take you a little while to figure out how to move them how to eliminate them how to change the names i could actually go into the original data source and do a mass change and let’s say eliminate the word school with a search and replace eliminate the word elementary and just put it in the L so I could make these names shorter and they would then be easier to read on the chart and the graph so those are just some suggestions I can then once i get my chart i can do a let’s assume i’ve cleaned it up i can go up here and i can do a print and I could send it somewhere by PDF down here in the bottom I can make a PDF file give it a name this is all Oregon schools and it’s be written so I save that on the desktop I can put this away and you can see it on the desktop here that I have just made a chart very messy cuz I haven’t cleaned up the titles but you get the general idea I hope that saves you some time and helps you in in your work and your process of studying school rules