Using Excel to Manage Data III
Using Excel to Manage Data III
2007 version
I.Reorganizing Data
A.Sorting Data
(a)This allows you to change the order of your data set so that it is easier for you to find a particular students name or score.
1.First, highlight all of the data by clicking in the cell in the top left-hand corner of the workbook, between the A and 1 cells.
2.Then, go to DATA and click on “sort”.
3.Using the drop down menus in the new window that appears, chose which column(s) you would like to sort by.
4.Next, chose whether you would like to sort the column ascending or descending.
5.Finally click “OK” and the data will sort.
II.Reformatting Data
A.Format Cells
(a)This allows you to change the value, alignment, font, and border of a particular cell(s), column(s) or row(s).
1.First, highlight the cell(s), column(s) or row(s) within which you would like to format data.
2.Stay on the HOME tool bar and select “format”.
3.Then, scroll down to the bottom and click “format cells”
4.Now, you can choose the value, alignment, and design you would like.
B.Conditional Formatting
(a)This allows you to highlight patterns and trends in one or more columns in your excel spread sheet.
1.For example: let’s say you wanted to know what students received a score of 3 or above on the MCA-II Reading test in the Vocabulary Expansion strand. Conditional formatting will allow you to see a better picture of this distribution.
2.First, highlight the column that contains the MCA-II Reading Vocabulary Expansion Strand (column L).
3.Stay at the HOME tool bar and click on “conditional formatting”.
4.Hover over “Highlight Cell Rules” and scroll to the bottom and choose “More Rules”. Then, underneath “Format only cells with” you want “cell value” in the first drop down arrow and in the second you want “greater than or equal to” (you will have to scroll down to find this).
5.Next, in the third blank type 3, because we want to see what students received a 3 or higher on Vocabulary Expansion Strand.
6.After that, click on “format…” and choose the filler or color you want the cells to be.
7.Finally, click the “OK” button and the conditional formatting will be applied.
*I recommend checking out , a website created by Dr. Scott McLeod previously at the University of Minnesota and now at Iowa State University. The website has short 1-2 minute Excel tutorials designed specifically for educators.