E-Mail jdoherty@ccsd.edu

Microsoft Excel is an electronic spreadsheet program.  It can be used to organize, and analyze data. With Excel you can plan a budget, calculate the batting averages of your favorite baseball players, determine a student’s grade, as well as a host of other functions.

One of the most powerful uses of Excel is to create graphs and charts that display numeric relationships in a visual manner, which often allows for greater understanding of the concepts involved.

The purpose of this workshop is to introduce you to the Excel program by creating a basic spreadsheet.  You will: Create a form for a basic spreadsheet, enter data, use formulas to critically examine the data, as well as design and create charts to visually represent the data.

I hope you find this workshop both fun and informative.  

Getting Started in Excel 

When you open the Excel program the first screen you see looks like this. 

From the Menu Bar select File, Save as, assign it the file name “ Our Pets” and save it to your network drive.  If you are taking this workshop on-line, save all work on your hard drive as well as on a floppy disc.

Designing Your First Worksheet:

Use your mouse to click once on cell C4.  Type, “ Our Class Pets” and then press Enter.  Notice the cell adjusted its' size to fit the text you entered.  Notice also that when you pressed Enter the program shifted the selected cell downward to cell C5.  Double click on cell C4 again.  Place the arrow to the left of the first letter in the cell, then hold down the left mouse button as you drag across the remainder of the cell.  Change the font by using the pull down Font menus on the Formatting toolbar.  Make the font Times New Roman, size 20, Bold, and red.

Your worksheet will look like this:

Entering Data: 

Click on cell B10 and type “Total”.  Use the Tab or arrow keys to move between cells on this row.  In cell D10 type “Male”, in E10 type“ Female”.  Notice that you cannot see all the text in cell B10.  Move the pointer to the top of the line that separates column B from column C until the pointer changes to a + sign.  Double click, notice the column width changed to fit the text.

Click on cell B11and type “Dogs”.  Use the Enter key to move down and type Cats in cell B12.  Continue to fill cells as follows.  B13 “Ferrets”, B14 “Hamsters”, B15 “Rabbits”, B16 “Birds, B17 “Turtles”, B18 “Lizards”, B19 Snakes, B20 “Fish”

In C11 enter the value 12.  Finish filling column C with the following values, C12-10, C13-3, C14-4, C15-2, C16-3, C17-1, C18-2,C19-1, C20-30.  Use the Enter key to move down through the column.  Fill column D from D11 to D 18 with the following values, 8,6,2,1,0,2,1,1.  Fill cells E11 to E18 with these values, 4,4,1,3,2,0,1.  Save your Worksheet.

Your worksheet should look like this:

Moving values / text within cells, rows, columns: 

Sometimes you may want the contents of a cell, row or column to be centered for easier viewing.  To do this, click on the cell you want centered, or if you want the entire column or row centered click on the letter at the top of the column, or on the number at the end of the row.  Use the centering tool on the formatting toolbar to center the selected cell, column, or row.  You can also cut, copy, paste, click and drag the contents of cells just as you would in Word, PowerPoint, or Access.

Center columns B, C, D, E

Save your worksheet.

Your worksheet will look like this

Doing Simple Calculations:

Using Excel you can perform a wide range of mathematical calculations and functions according to what you need from your data.  To calculate sums (totals) and percentages, use the mathematical operation of adding values to get a total, (sum) and then dividing that sum by the number of values added.  Excel has features that make it very easy to perform calculations.

To find the total number of pets, select cell C22 by placing the pointer over it and clicking once.  Place the pointer over the = icon on the Formula bar and click once.  From the pull-down menu select SUM.  A dialogue box will appear.  Click OK.  The value 68 should appear in cell C22.  If you change any of the values in cells C11 through C20 the program automatically recalculates the value of C22.  Calculate sums for columns D and E.

 Save.

  Your worksheet should look like this:

Adding a new worksheet:

We are now going to use the data from the first worksheet to display other information, which will be on a second worksheet.  To create a second worksheet place the pointer over the tab at the bottom of the worksheet that says Sheet2and click.  In cell D4 on Sheet2, select Arial, size 20, Bold, Green for your font and type, “Classification”.  Your 2^nd worksheet will look like this:

Entering data:

Click on the tab for Sheet 1 to return to it.  Place the pointer over cell B10.  Click and hold the left mouse button while you drag down and to the right until all cells between B10 and C22 are selected.  Click once on the copy icon on the Standard Toolbar.  A flashing box will frame the selected cells.  This indicates they have been copied.  Click on the tab for Sheet2.  Place the pointer over cell B10 click once to select this cell.  Place the pointer over the Paste icon on the Standard Toolbar and click once.  The data from Sheet1 has been copied to sheet2.

In cell D10 type Mammals.  In E10, Birds, F10 Reptiles, and G10 Fish.

Make column C wider by placing the pointer over the line between the letters C and D at the top of the columns until the pointer becomes a +.  Click and drag the column border until the column shows a width of 14 in the formula box.

To find the total number of mammals, select cell D11 type the formula =SUM(C11:C15)  then press Enter.  You should have a value of 31 in cell D11.  Repeat this process to find the number of Birds, Reptiles and Fish.

Your sheet should look like this: 

Using Charts: 

Go to Sheet1. Select cells B11 through C20.  Click on the Chart Wizard Icon located on the Standard Toolbar.  Follow the directions to create a Column chart titled “Our Pets”.  Save chart as a new sheet.

Create charts to show how many animals are Male vs. Female.  What type of chart would show this comparison well?

 Make a chart to show how many animals are Reptiles, Birds etc….

On Sheet1 create a column G titled % of total.  Write a formula to find the percent.  For Dogs you would write =(C11/C22)  C11 is how many dogs there are.  C22 is how many pets there are in total.  Formulas ALWAYS begin with an equals sign( =) followed by brackets.  + means addition, - means subtraction, * means multiplication, / means division.

Make a pie chart showing the percentages of each animal

For our next class:

Make a new Sheet3.  On Sheet three use the information from sheets 1 and 2 to show percentages of animals that are male, female, or percentages that are in each classification group.  Use the chart wizard to make charts showing your information.

Plan a lesson to use in your classroom, keep it simple, and be prepared to share your experience with the rest of the class when we meet again.

Additional resources can be found at http://www.microsoft.com/education

Back