Lesson 4: Creating Your First Worksheet

To begin this lesson, open Microsoft Excel. You are now ready to start building your first worksheet. During this lesson you will review some things you have already learned and learn a few new things. The order in which you will complete the worksheet is not the most efficient. The exercises have been ordered to provide an opportunity to learn new skills and practice skills already learned. The lesson must be completed in the sequence presented. When you have completed all of the exercises, your worksheet will look like the one shown here. 

Creating a Series

Let's start by entering the months of the year. You will use the Series function, which allows you to enter the first value in the series and have the computer enter the rest. 

1. Move the cursor to cell A1.
2. Type January.
3. Click on the checkmark located on the Formula bar. This will enter January into the cell. Clicking on the checkmark is similar to pressing Enter.

4. Drag the Fill Handle (the small square located in the lower right corner of the cell) to cell A12. Point to the Fill Handle and hold down the left mouse button while you move the cursor down to cell A12. The months February through December should fill the cells.

Entering Numbers

Pressing the Num Lock key can make data entry easier. If you have a numeric keypad, Num Lock enables you to enter numbers as if you were using a calculator. You can also use the Enter key located on the numeric keypad. If you highlight the rows and columns into which you are going to enter data, the cursor will automatically move up and down those columns. 

Enter the following values: 
 

1. Highlight the area B1 to D12. Move the cursor to cell B1. Press F8. Press the down arrow key until you are in cell B12. Press the right arrow key until B1 to D12 are highlighted.
2. Make sure Num Lock is on.
3. Do NOT remove the highlighting.
4. Type 789. Press Enter. You can use the Enter key located on the numeric keypad.
5. Type 736. Press Enter.
6. Type 785. Press Enter.
7. Type 799. Press Enter.
8. Type 723. Press Enter.
9. Type 2086. Press Enter.
10. Type 1744. Press Enter.
11. Type 1143. Press Enter.
12. Type 854. Press Enter.
13. Type 753. Press Enter
14. Type 1747. Press Enter.
15. Type 1760. Press Enter. If the data area is still highlighted, the cursor will automatically move to cell C1.
16. Type 460. Press Enter.
17. Type 1230. Press Enter.
18. Continue entering the data until you have entered it all.
19. Press Esc when you have completed entering your data. Then click anywhere on the worksheet to remove the highlighting.

You can use Microsoft Excel to insert or delete rows on the worksheet. You need to insert three rows so you can add headings to the chart. 

1. Highlight cells A1 to A3. Move the cursor to cell A1. Press F8. Press the down arrow key twice.

2. Click on Insert, which is located on the Menu bar.
3. Press the down arrow key until Rows is highlighted.
4. Press Enter. Three new rows should be inserted.

Creating a Series

You need to put headings on our columns of data. Use the Series function. 

1. Move the cursor to cell B3.
2. Type Region 1.
3. Click on the checkmark located on the Formula bar.
4. Grab the Fill Handle and move the cursor right to cell D3. "Region 2" and "Region 3" should appear in cells C3 and D3, respectively.

Now right-align cells B3 to D3. 

1. Highlight cells B3 to D3.
2. Click on the Align Right icon on the Formatting toolbar.

3. Note the change in the text alignment

1. Move the cursor to cell A16.
2. Type Total.
3. Press Enter.

The AutoSum icon on the Standard toolbar automatically creates a SUM function. The following illustrates using the SUM function to total the Region 1 sales: 

1. Press F5.
2. Type B16.
3. Press Enter.
4. Click on the AutoSum button, which is located on the Standard toolbar. 

B4 to B15 should now be highlighted. 






5. Press Enter.

In Lesson Three you learned that you can copy and paste information. To copy the formula in cell B16 and paste it in cells C16 to D16, follow these steps: 

1. Move to cell B16.
2. Click on the Copy icon. Rotating dotted lines will appear around the cell. The rotating dotted lines designate the area to be copied.
3. Highlight cells C16 to D16.
4. Click on the Paste icon, which is located on the Standard toolbar. The formula in cell B16 is copied to cells C16 and D16.
5. Press Esc.

Enter the word "Total" in cell E3 and right-align the cell. 

1. Move to cell E3.
2. Type Total.
3. Click on the checkmark located on the Formula bar.
4. Click on the Align Right icon on the Formatting toolbar.

There is a Paste Function icon located on the Standard toolbar. You can use this icon to add a function to your worksheet. To sum the January sales figures: 

1. Move to cell E4.
2. Click on the Paste Function icon on the Standard toolbar. The Paste Function dialog box opens. 

3. Click on Math & Trig in the Function Category box.
4. Click on Sum in the Function Name box.
5. Click on OK. The Function dialog box will open.
6. Make sure that "B4:D4" displays in the Number 1 field.  These are the fields that will be summed.
7. Click on OK.

Copy the formula you just entered in cell E4 to cells E5 to E16. 

1. With your cursor in cell E4, click on the Copy icon on the Standard toolbar.
2. Highlight cells E5 to E16.
3. Click on the Paste icon.
4. Press Esc and click anywhere on the worksheet to remove the highlighting.

Previously you learned how to center data within a cell. You can also center the data across several cells. The following illustrates: 

1. Move to cell A1.
2. Type General Widgets Sales Figures.
3. Press the Tab key.
4. Highlight cells A1 to E1.
5. Click on the Merge and Center icon.

Changing the Font and the Font Size

You can change the font and the font size of individual cells. 

1. Make sure the cursor is in A1. The title "General Widgets Sales Figures" is in cell A1.
2. Click to open the Font drop down menu on the Formatting toolbar.
3. Select Times New Roman.
4. Move to the Font Size box, which is also located on the Formatting toolbar.
5. Type 16.
6. Press Enter.

You can use Microsoft Excel to insert or delete columns on the worksheet. You need to insert a column. 

1. Move the cursor to cell A4.
2. Click on Insert, which is located on the Menu bar.
3. Press the down arrow key until Columns is highlighted.
4. Press Enter.
5. A new column is inserted.

Aligning Text Sideways

You can also align text sideways. After doing the following exercise, your worksheet should look like the illustration at the end of this exercise. 

1. Type Year 2000 in cell A4.
2. Press Enter.
3. Highlight A4 to A15.
4. Click on Format, which is located on the Menu bar.
5. Press the down arrow key until Cells is highlighted.
6. Press Enter.
7. Click on the Alignment tab, if it is not in the front.
8. Select Center in the Horizontal field. This will center the text in the cell horizontally.
9. Select Center in the Vertical field. This will center the text in the cell vertically.
10. Click on the word "Text" in the Orientation frame. Set the orientation to 90 degrees. This will turn the text sideways.
11. Select Merge Cells.

12. Click on OK. The text now appears sideways as shown here.

Adding Text

Add the following text to your worksheet: 

1. Move the cursor to cell B18.
2. Type Average.
3. Press Enter.
4. Type % of Total in cell B19.
5. Press Enter.

Earlier in this lesson you learned about the Paste Function icon and how to use it to add a function to the worksheet. You can also use the Paste Function icon in the Point mode. When you are in the Point mode you can use your arrow keys or your mouse to select cells. 

The following illustrates: 

1. Move the cursor to cell C18. You are going to enter a formula to calculate average regional sales for Region 1 in cell C18.
2. Click on the Paste Function icon on the Standard toolbar.  The paste function dialog box will open.

3. Click on All in the Function Category box.
4. Click on Average in the Function Name box.
5. Click on OK.
6. Move the Paste Function dialog box to the lower right corner of the screen so you can see the cells on the worksheet you will be working with.
7. Highlight C4 to C15. Note that the lower right corner of the status bar reads "Point."  This indicates that you are now in the point mode.
8. Click on OK,  in the Paste Function dialog box.  The average sales for Region 1 should now appear in cell C18.

Copy the formula you just entered in cell C18 to cells D18 to F18. This will calculate the average sales for regions two and three and it will also calculate the average total sales. 

1. Move the cursor to cell C18.
2. Press Ctrl-C. Pressing Ctrl-C selects the field to be copied.
3. Press the right arrow key. You should be in cell D18.
4. Highlight D18 to F18. Press F8. Press the right arrow key twice.
5. Press Ctrl-V. This will paste the formula in cells D18 to F18.
6. Press Esc.
7. Click anywhere on the worksheet to clear the highlighting.

In this exercise you are going to enter a formula to calculate the regional sales as a percent of total sales and copy the resulting formula to cells D19 to E19. You will use the Point mode. 

1. Move the cursor to cell C19.
2. Click on the Edit Formula icon (the equals sign) on the Formula bar. A dialog box will appear.

3. Move the dialog box so that you can see your column headings. While holding down the left mouse button, drag the dialog box out of the way.
4. Click in cell C16. C16, the numerator, will appear in cell C19.
5. Press the slash.
6. Click in cell F16. F16, the denominator, will appear in cell C19.
7. Press F4 to make the cell address absolute.
8. Click on the checkmark located on the Formula bar.
9. Press Ctrl-C to copy the formula you just entered.
10. Highlight D19 to E19. Move to cell D19. Press F8 anchor the cursor. Press the right arrow key.
11. Press Ctrl-V to paste the formula in cells D19 and E19.
12. Press Esc.  You have finished copying.

Let�s bold the region names and the totals. 

1. Move the cursor to cell C3.
2. Highlight cells C3 to F3.
3. Click on the Bold icon, which is located on the Formatting toolbar.

4. Highlight cells F4 to F16.
5. Click on the Bold icon, which is located on the Formatting toolbar.
6. Move the cursor to cell B16.
7. Highlight cells B16 to F16.
8. Click on the Bold icon again.

You can  format your numbers to make them easier to read.

1. Move the cursor to cell C4.
2. Highlight cells C4 to F18.
3. Click on Format, which is located on the Menu bar.
4. Press the down arrow key until Cells is highlighted.
5. Press Enter.
6. Click on the Number tab, if it is not in the front.
7. Click on Number in the Category box.
8. Type 2 in the Decimal Places field. This will cause the number to display with two decimal places.
9. Place a checkmark in the Use 1000 Separator box. This will cause thousands to be separated with a comma.
10. Click on OK to close the dialog box.

Your worksheet should look similar to the one shown here.

Creating a Border

You can use the Border icon to place borders around a cell. You have several options on the type of border to use and where to place the borders. Borders can be placed above, below, and/or on the sides of cells. The following illustrates using borders: 

1. Place the cursor in cell C16.
2. Highlight cells C16 to F16.
3. Click on the down arrow next to the Borders icon to open the Borders palette.

4. Click on Top and Double Bottom Border (farthest right in the middle row).

Formatting as a Percent

In Lesson Three you learned how to format a number as a percent by using the icon on the toolbar. You can also format a number as a percent by using the menu. The following illustrates: 

1. Highlight cells C19 to E19.
2. Click on Format, which is located on the Menu bar.
3. Press the down arrow until Cells is highlighted.
4. Press Enter.
5. Click on the Number tab, if it is not in the front.
6. Click on Percent in the Category box.
7. Type 0 in the Decimal Places field. This will cause the number to display with no decimal places.
8. Click on OK.

Your worksheet is complete.  It should look similar to the one shown here.

Printing a Worksheet

You have completed your first worksheet. You are now ready to print it. First, look at the worksheet in the Print Preview screen. 

1. Click on File, which is located on the Menu bar.
2. Press the down arrow key until Print Preview is highlighted.
3. Press Enter. The worksheet as it will appear when printed should display.
4. Click on Setup.
5. Select Portrait on the Page tab.
6. Click on the Margin tab.
7. Place a checkmark in the Center on Page Horizontally box.
8. Click on OK.
9. Click on Print. A dialog box will appear.
10. Check the setting in the dialog box.
11. Click on OK.

This is the end of Lesson Four. Save your file and close Microsoft Excel. 

1. Click on File, which is located on the Menu bar.
2. Press the down arrow key until Save is highlighted.
3. Press Enter.
4. Type lesson4.xls in the filename field.
5. Click on Save.
6. Click on File, which is located on the Menu bar.
7. Press the down arrow key until Exit is highlighted.
8. Press Enter.