How to Manipulate an Excel Spreadsheet

1.      Use the How to Manipulate an Excel Spreadsheet Test Sheet which is shown below.


How to Manipulate an Excel Spreadsheet Test Sheet.xlsx



To save this spreadsheet to your download folder double click on it and open it from Downloads.31413_15339_0.png
Now you can double click on the spreadsheet to open it in Excel.

2.      Because we are going to make major changes to this spreadsheet lets save as working copy and keep the original just as it is.  To do this click on 31413_11747_0.png
and fill in the name as shown below:  
31413_11823_1.png

3.      Now click on the  31413_11854_2.png button.

4.      Before we start to manipulate this spreadsheet we want to make sure the items you will need are showing in your toolbar.  Your toolbar should look like this:
31413_11911_3.png
5.      If it does not look like the above toolbar, go to   31413_11938_4.png  31413_11953_5.png and make sure these two items have a check next to them  31413_12018_7.jpg.  If they do not have a check next to them just click on them and that will add the check mark.  Now your toolbar will look like this:
31413_11911_3.png
6.      Look at the spreadsheet and you will notice that some items in row 1 don’t fully display.  We can wrap the cells so no matter how wide the column width is you can still see all of the words.
31413_15701_0.png
7.      To do this click on row 1 so the entire row is highlighted.
31413_15723_1.png
8.      Then click on 31413_15741_2.png    so this window appears 31413_15823_3.png

9.      When the Format Cells window appears click on 31413_15847_4.png  and check the box marked 31413_15857_5.png .  Then click 31413_15903_6.png .

10.     Now you will notice that row 1 displays all of the words and the row is much wider.  When done correctly it will look like this.  31413_15947_7.png

 
11.     We currently have two pages of columns that we would like to display on one page.  First we will change the page from portrait to landscape, which will give us a wider page.  To do this we will click on 31413_20008_8.png

12.     The following page will be displayed.  31413_20048_9.png

 
13.     This is where we will click on 31413_20114_10.png  instead of Portrait.  Then we will click on 31413_20122_11.png .  You will now notice instead of columns A-F displaying on the first page now columns A-I are displayed.  31413_20132_12.png

14.     Because columns J-L  31413_20226_13.png are on page two we will need to manipulate the page a bit more to get everything all on one page.  We can also change the page margins to assist with making this a single page document.  To do this click on 31413_20008_8.png


15.     Now click on the  31413_20322_15.png tab.  
Here we see the below display.
31413_20327_16.png

 
16.     We will change all margins to 0.5” like the Header and Footer so it looks like this when done:  31413_20359_17.png

17.     Now click 31413_20435_18.png .  This didn’t change the column count on page one as it is still A-I so we will also need to change the column width.

18.     We can adjust all of the column widths at the same time by highlighting all of the columns as shown below and sliding one of them so the majority of the columns are correctly resized.  31413_20451_19.png
 
19.     When done correctly you will notice that each column is exactly the same width as shown below:  31413_20511_20.png

20.     Now everything fits on the same page but it is a bit too tight so we can just move one column at a time to clean the sheet up and fit the maximum column width to the entire page.

21.     To change the column width of just one column you can click on the 31413_20537_21.png  next to each column letter  31413_20546_22.png and just slide the column to fit the information in that column.  Because column G is for State and it is a narrow column I will resize the column smaller than the other columns to maximize my page space.  31413_20557_23.png

22.     Now I will resize my other columns.  My finished single sheet will look like this:
31413_20615_24.png

23.     Even after resizing the columns I cannot see the entire address in the Street Address column.  If I wrap the text in this column I could then see it all when I print the sheet out.  To do this click on the E of Column E so it highlights the column.  It will look like this when done correctly.  31413_20637_25.png

24.     Now click 31413_21724_26.png .  
Then click the  31413_21756_27.png tab and check the  31413_21802_28.png box.  
Now click 31413_21813_29.png.

25.     The text wrapped column will now display all addresses and look like this:  31413_21849_30.png

26.     Notice that your columns have different alignment.  Some have a left alignment and others a right alignment.  To realign all data in the spreadsheet, highlight all of the columns as shown below:
31413_21912_31.png
27.     Now click on one of the icons in the tool bar.  For this example we will click the left align as that is how we want the majority of the columns align.  Click on the  31413_22001_34.png which is located next to the  31413_22011_35.png in your toolbar.  You will notice that your columns are nicely aligned and look like this:

28.      31413_22042_36.png

29.     If I wanted to center align my Grade column, I would click on Column C so the column is highlighted31413_22051_37.png  and click on the center align icon  31413_22118_38.png which is next to the left align icon.  My column will now look like this:  31413_22133_39.png

30.     Now look at column H.  You will notice that the Zip Codes are missing a starting zero even if we type a zero in.  We can fix this as well all at once.  Click on column H so it is highlighted 31413_22158_40.png .

31.     Click on  31413_22233_42.png and click on the  31413_22228_41.png tab.  


32.     Now click on  31413_22303_43.png in the Category box.   Next to this box the following should be clicked 31413_22319_44.png  in the Type Box.


33.     Then click 31413_22355_45.png .  You will notice that all numbers in column H are now five digits numbers.  31413_22411_46.png

34.     After making major changes and adjustments to my sheet I will save the changes.  To do this you can do a 31413_22500_47.png or click  31413_22506_48.png because you have already done an initial save you don’t need to use Save As.

35.     Now I want border the items in this spreadsheet so I can more easily read the information.  To do this I will highlight just the cells with words inside them.  To do this start in A1 then go across and down so your bottom corner will look like this when highlighted correctly:   31413_22550_49.png

36.     Now click on the Border 32013_65858_0.pngicon in your toolbar.  It should look like this but if it doesn’t click on the 32013_65924_1.png  next to the 32013_65858_0.pngand the below box will be displayed:    
32013_70018_4.png
Here you can select which border you want to use.  For this exercise we will use the  32013_70046_5.png border.

37.     My bordered sheet will now look like this:
32013_70249_6.png
38.     For this sheet I want to make my header row bold, italic, and change the color to blue.  To do this I will highlight Row 1 so it looks like this:
32013_70312_7.png
39.     Then I will click on the  32013_70330_8.png in the Toolbar.
32013_70338_9.png
40.     Because it changed some of my header text alignment I will change my mind and just do Italic so to shut off the Bold, I will click on the 32013_70414_10.png  and it will now look like this:
32013_70424_11.png

41.     Now to change the color I will go to the 32013_70449_12.pngand click on the 32013_70504_13.pngnext to the 32013_70521_14.pngand the following color box will be displayed:
32013_70536_15.png
Here I will click on the 32013_70607_16.png  color I would like the words in my header to be changed to.  When finished it would look like this:
32013_70751_17.png

42.     If I wanted to change the text and size of the words in my header row, I would highlight the row and go to the Font and Font Size box in the Toolbar 32013_70808_18.png .  Here I would click on the 32013_70827_19.png  to select a different font and size.  For this exercise we will change the font to 32013_70857_20.pngand the font size to 32013_71136_21.png .  Your finished header will now look like this:
32013_71201_22.png

 
43.     You may need to realign a cell or two so that the header words are not split up.  Just click on the 31413_20537_21.png  next to each column letter and side it to the position you need.

44.     Now I want to sort my sheet so the students are in alphabetical order.  To do this I can highlight the entire page by holding down my Apple/Command key next to the space bar and press the letter A quickly once.  You will now notice that the entire page is highlighted.
32013_71247_24.png
You could also click on the columns to highlight them all.  It is very important that the entire page be highlighted when performing a sort.  

45.     Now click 32013_71326_25.png  so the Sort Box will be displayed.
32013_71345_26.png
   For this exercise I want to make sure that it shows My list has a Header row.  

46.     Then I want to sort my Last Name first and First Name second.  When done properly, it will look like this:
32013_71500_27.png

47.     Now click 32013_71538_28.png .  Your newly sorted sheet will now look like this:
32013_71550_29.png
48.     Notice that the Day children are in alphabetical order by last name and then by first name.   If we had not click on First Name as the second sort they would not be in alphabetical order.

49.     Now lets say we have this sheet completed and ready to turn in but a new student just showed up in your class today and they need to be added to the sheet.  The new student’s name is Ralph Emery.  To add Ralph to the sheet click and highlight the row where you want to add Ralph.  For this example we will add him to row 9 so it is alphabetical.  32013_71615_30.png

50.     Now click 32013_71644_31.png .  
When done correctly it will look like this:
32013_71706_32.png
51.     Now enter Ralph’s information.  It would look something like this:
32013_71723_33.png
52.     Maybe I would want to sort the Day kids by school instead of by first name.  To do this I would just highlight the rows that I wanted to sort leaving the other rows alone.  For this example I would sort rows 4-7 by just highlighting those rows as shown below:
32013_71742_34.png
53.     Then I would click  32013_71805_35.png so this box would be displayed:
32013_71820_36.png

54.     My list this time does not have a Header Row so I would want  32013_71854_37.png this time.  I would this time sort by the column letter.  For this exercise I want to sort by school first which is Column D and Grade next which is Column C so my proper sort box would look like this:
32013_71924_38.png

55.     Then I would click  32013_72002_39.png so my sheet would look like this:
32013_72014_40.png
56.     If I wanted the two Day kids at SVHS to be sorted by which student was graduating first I would sort this way in my sort box.
32013_72033_41.png



57.     Notice that I have clicked Descending order next to Column C so the student in 12th grade will show up before the student in 11th grade.  My finished column would now look like this:
32013_72105_42.png
58.     What if I wanted to get the average score of both the Reading and Math Tests.  I might type the word Average in I35 as shown here:
32013_72123_43.png
59.     Then I would click in Box J35  32013_72144_44.png and click the    32013_72149_45.pngnext to the  32013_72156_46.png button in my Toolbar and click32013_72202_47.png  .

60.     Now the following formula will display 32013_72317_48.png .  
Press the enter/return key and the average Reading score will display.
32013_72332_49.png

 
61.     Now I want to do the same thing in cells K35 and L35.  I can repeat steps 58-60 or I can highlight I35 and J35 32013_72420_50.png , click 32013_72426_51.png ,
then click cell K35 32013_72458_52.png  
and click 32013_72512_53.png

62.     This is a shortcut to copy the word Average to cell K35 and the formula to L35. When done properly it will look like this:  
32013_72558_55.png

63.     Now lets sort the Reading scores in descending order first and student name next.  To do this highlight just rows 1-33.  Make sure you don’t highlight rows 34-36 as we don’t want to sort those rows.  

64.     Now click 32013_72625_56.png  and change the sort box so it reads like this:

32013_72642_57.png

65.     Notice that the Reading Test Score is in Descending order and the name boxes are in Ascending order.  For this sort we have a Header row.  Now click 32013_72719_58.png .

66.     The finished sort will look like this:
32013_72734_59.png
67.     Notice that the Reading scores are listed from highest to lowest with the student’s names in alphabetical order.  Sorting is a very powerful tool but it needs to be carefully done.