Excel Notes
This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA
Overview
Download & View Excel Notes as PDF for free.
More details
- Words: 3,162
- Pages: 16
GMM Training Pty Ltd
1.3 Microsoft Excel 2003
Section
1
1.3.1 Introduction – What is Excel? “Spreadsheets” form the basis of Excel. The spreadsheet term comes from the accounting field, when accountants recorded financial information such as employee salaries, expenses, and tax payments on potentially huge sheets of ruled paper. Today, most bookkeeping is done on a computer, and for many reasons. One is the ease at which new information can be added and old information edited. Today, Excel is used for more than just accounts. It can be also be used to create tables of information and graph results.
The main Excel window consists of a worksheet with ruled lines, which makes up “cells”. Here you can enter information to create a table. Notice that each row has a number and each cell has a letter. This is done to let you know which cell is highlighted. Click on any cell in the worksheet to highlight it.
GMM Training Pty Ltd
Excel Training Note
Page 1
When you select a cell the co-ordinates of the cell appear in a box just below the text style box. The importance of cell co-ordinates •
As an exercise, we will create a table of our daily schedule.
1.3.2 AutoFill Let’s start by entering the days of the week. You will use the Auto Fill function, which allows you to enter the first value in the series and have the PC enter the rest. 1. Move the cursor to cell A1. 2. Type Monday. 3. Click on the green tick located on the Formula bar. This will enter Monday into the cell. Clicking on the tick is similar to pressing Enter.
4. Click-drag the Fill Handle (the small square located in the lower right corner of the cell) to cell G1.
GMM Training Pty Ltd
Excel Training Note
Page 2
1.3.3 Inserting Rows and Columns You can insert or delete rows on the worksheet. Notice that we have left out a must-have title and a label for the days. You need to insert 2 rows so you can add headings to the chart. •
Right-click on the number “1” on the edge of the worksheet
•
Click on “Insert”. A new row will be inserted above row 1 Repeat to insert a 2nd row. Give your table a title Click on cell B1. Enter a title such as “My Daily Timetable”. Click on cell B2 and enter label “Days of the week.”
•
• • •
Inserting another column is similar. • •
Right-click on the letter “A” on the edge of the worksheet Click on “Insert”. A new column will be inserted to the left of the column A
Your worksheet should now look similar to the one shown here.
GMM Training Pty Ltd
Excel Training Note
Page 3
For this exercise, let’s place the time of day down column “A”. We will make it a work time-table from 9-5 o’clock, with 1 hour intervals. •
In cell A4 type in: 9:00
•
In cell A5 type in 10:00
•
AutoFill the rest of the time to 5:00
•
Add another column to the left of the time for another title
•
Type in Time in one of the cells in the new column.
Your table should now look something like this:
Notice how the text “Wednesday” is cut off on our table. 1.3.4 Adjusting Cell Sizes To see all text in a cell one option is to make the cell bigger. The width of a column can be changed, and the height of the row. •
To change the width of column D, so we can see the y in Wednesday, move the mouse pointer onto the column line between D and E. The pointer should now look like a bold line with 2 arrows coming out of it. You can now adjust the size of the column by click-drag. Resizing rows is similar. •
Point to the line between two numbers and click-drag to resize.
There are other alternatives to change the size of the cells, which will be covered later on. 1.3.5 Merging Cells Notice how the titles are not centred on the table. One way to fix this is to move the title to another cell. The second way to fix this is to merge cells. When this is performed, 2 or more cells are merged together to create 1 large cell. For our exercise we will merge the cells within the titles. •
Highlight cells B1 to E1.
•
Click the merge button
on the toolbar. It looks like an “a” with 2 arrows in a cell.
The cells will merge together to create one large cell. Repeat the process for the days of the week title. GMM Training Pty Ltd
Excel Training Note
Page 4
•
You can unmerge a cell by highlighting it and clicking the merge button again.
1.3.6 Formatting Cells Any text typed into cells can have its font changed. •
Highlight a cell.
•
Just like in Word, use the toolbar controls to quickly change the font style.
There are further format options available. •
Highlight a cell.
•
Right-click and click on “Format Cells..” on the pop-up list.
•
Click on the “Font” tab on the window that appears.
•
Try different font settings. A sample of the changes will appear in the sample window.
•
Click the OK button when you are done.
1.3.7 Cell Borders By default, when you print out the spreadsheets, Excel does not print out the ruled lines. To overcome this, you can add borders to your tables. •
For our exercise, highlight the entire table.
•
Right-click and select “Format Cells…”
•
Click the “Border” tab
GMM Training Pty Ltd
Excel Training Note
Page 5
•
Firstly, choose a “Line Style”
•
Choose a line “Color”
There are 3 methods to insert borders: 1. Use the Presets –
•
Click on the “Outline” and/or “Inside” presets to insert borders. Use “None” to delete any borders currently inserted.
2. Use the Surround buttons –
These controls allow more control of which sections of your selected cells have borders. 3. Click in sample box – Using the 2 previous methods, the sample window displays the adding of the borders. You can add borders by clicking in the sample window itself. If you click on a border already there, it deletes it. Note that if you decide to change the border, the old border must first be removed. After the old border is cleared, you can then insert the new border.
GMM Training Pty Ltd
Excel Training Note
Page 6
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. Aligning Cells 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 Entering Text 1. Move the cursor to cell A16. 2. Type Total. 3. Press Enter. The AutoSum Icon 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.
GMM Training Pty Ltd
Excel Training Note
Page 7
5. Press Enter.
Using Copy and Paste 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:
Copy and Paste Icons 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. Entering Text 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.
GMM Training Pty Ltd
Excel Training Note
Page 8
The Paste Function Icon 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. 4. 5. 6.
Click on Math & Trig in the Function Category box. Click on Sum in the Function Name box. Click on OK. The Function dialog box will open. Make sure that "B4:D4" displays in the Number 1 field. These are the fields that will be summed. 7. Click on OK. Using Copy and Paste Copy the formula you just entered in cell E4 to cells E5 to E16. 1. 2. 3. 4.
With your cursor in cell E4, click on the Copy icon on the Standard toolbar. Highlight cells E5 to E16. Click on the Paste icon. Press Esc and click anywhere on the worksheet to remove the highlighting.
Centering Across Cells 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.
GMM Training Pty Ltd
Excel Training Note
Page 9
Changing the Font and the Font Size You can change the font and the font size of individual cells.
1. 2. 3. 4.
Make sure the cursor is in A1. The title "General Widgets Sales Figures" is in cell A1. Click to open the Font drop down menu on the Formatting toolbar. Select Times New Roman. Move to the Font Size box, which is also located on the Formatting toolbar. 5. Type 16. 6. Press Enter. Inserting Columns You can use Microsoft Excel to insert or delete columns on the worksheet. You need to insert a column. 1. 2. 3. 4. 5.
Move the cursor to cell A4. Click on Insert, which is located on the Menu bar. Press the down arrow key until Columns is highlighted. Press Enter. A new column is inserted.
GMM Training Pty Ltd
Excel Training Note
Page 10
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. 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. 1.
12. Click on OK. The text now appears sideways as shown here.
GMM Training Pty Ltd
Excel Training Note
Page 11
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. 6. Using the Paste Function 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. 4. 5. 6.
Click on All in the Function Category box. Click on Average in the Function Name box. Click on OK. 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. Using Copy and Paste 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. 2. 3. 4. 5. 6. 7.
Move the cursor to cell C18. Press Ctrl-C. Pressing Ctrl-C selects the field to be copied. Press the right arrow key. You should be in cell D18. Highlight D18 to F18. Press F8. Press the right arrow key twice. Press Ctrl-V. This will paste the formula in cells D18 to F18. Press Esc. Click anywhere on the worksheet to clear the highlighting.
GMM Training Pty Ltd
Excel Training Note
Page 12
Inserting and Copying a Formula 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. Bolding 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. 5. 6. 7. 8.
Highlight cells F4 to F16. Click on the Bold icon, which is located on the Formatting toolbar. Move the cursor to cell B16. Highlight cells B16 to F16. Click on the Bold icon again.
GMM Training Pty Ltd
Excel Training Note
Page 13
Formatting Numbers You can format your numbers to make them easier to read. 1. 2. 3. 4. 5. 6. 7.
Move the cursor to cell C4. Highlight cells C4 to F18. Click on Format, which is located on the Menu bar. Press the down arrow key until Cells is highlighted. Press Enter. Click on the Number tab, if it is not in the front. 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.
GMM Training Pty Ltd
Excel Training Note
Page 14
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.
GMM Training Pty Ltd
Excel Training Note
Page 15
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. Saving Your File and Closing Microsoft Excel 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.
GMM Training Pty Ltd
Excel Training Note
Page 16
1.3 Microsoft Excel 2003
Section
1
1.3.1 Introduction – What is Excel? “Spreadsheets” form the basis of Excel. The spreadsheet term comes from the accounting field, when accountants recorded financial information such as employee salaries, expenses, and tax payments on potentially huge sheets of ruled paper. Today, most bookkeeping is done on a computer, and for many reasons. One is the ease at which new information can be added and old information edited. Today, Excel is used for more than just accounts. It can be also be used to create tables of information and graph results.
The main Excel window consists of a worksheet with ruled lines, which makes up “cells”. Here you can enter information to create a table. Notice that each row has a number and each cell has a letter. This is done to let you know which cell is highlighted. Click on any cell in the worksheet to highlight it.
GMM Training Pty Ltd
Excel Training Note
Page 1
When you select a cell the co-ordinates of the cell appear in a box just below the text style box. The importance of cell co-ordinates •
As an exercise, we will create a table of our daily schedule.
1.3.2 AutoFill Let’s start by entering the days of the week. You will use the Auto Fill function, which allows you to enter the first value in the series and have the PC enter the rest. 1. Move the cursor to cell A1. 2. Type Monday. 3. Click on the green tick located on the Formula bar. This will enter Monday into the cell. Clicking on the tick is similar to pressing Enter.
4. Click-drag the Fill Handle (the small square located in the lower right corner of the cell) to cell G1.
GMM Training Pty Ltd
Excel Training Note
Page 2
1.3.3 Inserting Rows and Columns You can insert or delete rows on the worksheet. Notice that we have left out a must-have title and a label for the days. You need to insert 2 rows so you can add headings to the chart. •
Right-click on the number “1” on the edge of the worksheet
•
Click on “Insert”. A new row will be inserted above row 1 Repeat to insert a 2nd row. Give your table a title Click on cell B1. Enter a title such as “My Daily Timetable”. Click on cell B2 and enter label “Days of the week.”
•
• • •
Inserting another column is similar. • •
Right-click on the letter “A” on the edge of the worksheet Click on “Insert”. A new column will be inserted to the left of the column A
Your worksheet should now look similar to the one shown here.
GMM Training Pty Ltd
Excel Training Note
Page 3
For this exercise, let’s place the time of day down column “A”. We will make it a work time-table from 9-5 o’clock, with 1 hour intervals. •
In cell A4 type in: 9:00
•
In cell A5 type in 10:00
•
AutoFill the rest of the time to 5:00
•
Add another column to the left of the time for another title
•
Type in Time in one of the cells in the new column.
Your table should now look something like this:
Notice how the text “Wednesday” is cut off on our table. 1.3.4 Adjusting Cell Sizes To see all text in a cell one option is to make the cell bigger. The width of a column can be changed, and the height of the row. •
To change the width of column D, so we can see the y in Wednesday, move the mouse pointer onto the column line between D and E. The pointer should now look like a bold line with 2 arrows coming out of it. You can now adjust the size of the column by click-drag. Resizing rows is similar. •
Point to the line between two numbers and click-drag to resize.
There are other alternatives to change the size of the cells, which will be covered later on. 1.3.5 Merging Cells Notice how the titles are not centred on the table. One way to fix this is to move the title to another cell. The second way to fix this is to merge cells. When this is performed, 2 or more cells are merged together to create 1 large cell. For our exercise we will merge the cells within the titles. •
Highlight cells B1 to E1.
•
Click the merge button
on the toolbar. It looks like an “a” with 2 arrows in a cell.
The cells will merge together to create one large cell. Repeat the process for the days of the week title. GMM Training Pty Ltd
Excel Training Note
Page 4
•
You can unmerge a cell by highlighting it and clicking the merge button again.
1.3.6 Formatting Cells Any text typed into cells can have its font changed. •
Highlight a cell.
•
Just like in Word, use the toolbar controls to quickly change the font style.
There are further format options available. •
Highlight a cell.
•
Right-click and click on “Format Cells..” on the pop-up list.
•
Click on the “Font” tab on the window that appears.
•
Try different font settings. A sample of the changes will appear in the sample window.
•
Click the OK button when you are done.
1.3.7 Cell Borders By default, when you print out the spreadsheets, Excel does not print out the ruled lines. To overcome this, you can add borders to your tables. •
For our exercise, highlight the entire table.
•
Right-click and select “Format Cells…”
•
Click the “Border” tab
GMM Training Pty Ltd
Excel Training Note
Page 5
•
Firstly, choose a “Line Style”
•
Choose a line “Color”
There are 3 methods to insert borders: 1. Use the Presets –
•
Click on the “Outline” and/or “Inside” presets to insert borders. Use “None” to delete any borders currently inserted.
2. Use the Surround buttons –
These controls allow more control of which sections of your selected cells have borders. 3. Click in sample box – Using the 2 previous methods, the sample window displays the adding of the borders. You can add borders by clicking in the sample window itself. If you click on a border already there, it deletes it. Note that if you decide to change the border, the old border must first be removed. After the old border is cleared, you can then insert the new border.
GMM Training Pty Ltd
Excel Training Note
Page 6
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. Aligning Cells 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 Entering Text 1. Move the cursor to cell A16. 2. Type Total. 3. Press Enter. The AutoSum Icon 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.
GMM Training Pty Ltd
Excel Training Note
Page 7
5. Press Enter.
Using Copy and Paste 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:
Copy and Paste Icons 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. Entering Text 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.
GMM Training Pty Ltd
Excel Training Note
Page 8
The Paste Function Icon 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. 4. 5. 6.
Click on Math & Trig in the Function Category box. Click on Sum in the Function Name box. Click on OK. The Function dialog box will open. Make sure that "B4:D4" displays in the Number 1 field. These are the fields that will be summed. 7. Click on OK. Using Copy and Paste Copy the formula you just entered in cell E4 to cells E5 to E16. 1. 2. 3. 4.
With your cursor in cell E4, click on the Copy icon on the Standard toolbar. Highlight cells E5 to E16. Click on the Paste icon. Press Esc and click anywhere on the worksheet to remove the highlighting.
Centering Across Cells 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.
GMM Training Pty Ltd
Excel Training Note
Page 9
Changing the Font and the Font Size You can change the font and the font size of individual cells.
1. 2. 3. 4.
Make sure the cursor is in A1. The title "General Widgets Sales Figures" is in cell A1. Click to open the Font drop down menu on the Formatting toolbar. Select Times New Roman. Move to the Font Size box, which is also located on the Formatting toolbar. 5. Type 16. 6. Press Enter. Inserting Columns You can use Microsoft Excel to insert or delete columns on the worksheet. You need to insert a column. 1. 2. 3. 4. 5.
Move the cursor to cell A4. Click on Insert, which is located on the Menu bar. Press the down arrow key until Columns is highlighted. Press Enter. A new column is inserted.
GMM Training Pty Ltd
Excel Training Note
Page 10
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. 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. 1.
12. Click on OK. The text now appears sideways as shown here.
GMM Training Pty Ltd
Excel Training Note
Page 11
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. 6. Using the Paste Function 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. 4. 5. 6.
Click on All in the Function Category box. Click on Average in the Function Name box. Click on OK. 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. Using Copy and Paste 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. 2. 3. 4. 5. 6. 7.
Move the cursor to cell C18. Press Ctrl-C. Pressing Ctrl-C selects the field to be copied. Press the right arrow key. You should be in cell D18. Highlight D18 to F18. Press F8. Press the right arrow key twice. Press Ctrl-V. This will paste the formula in cells D18 to F18. Press Esc. Click anywhere on the worksheet to clear the highlighting.
GMM Training Pty Ltd
Excel Training Note
Page 12
Inserting and Copying a Formula 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. Bolding 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. 5. 6. 7. 8.
Highlight cells F4 to F16. Click on the Bold icon, which is located on the Formatting toolbar. Move the cursor to cell B16. Highlight cells B16 to F16. Click on the Bold icon again.
GMM Training Pty Ltd
Excel Training Note
Page 13
Formatting Numbers You can format your numbers to make them easier to read. 1. 2. 3. 4. 5. 6. 7.
Move the cursor to cell C4. Highlight cells C4 to F18. Click on Format, which is located on the Menu bar. Press the down arrow key until Cells is highlighted. Press Enter. Click on the Number tab, if it is not in the front. 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.
GMM Training Pty Ltd
Excel Training Note
Page 14
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.
GMM Training Pty Ltd
Excel Training Note
Page 15
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. Saving Your File and Closing Microsoft Excel 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.
GMM Training Pty Ltd
Excel Training Note
Page 16
Related Documents
Excel Notes
December 2019 10
Excel Notes
November 2019 8
Excel Notes
June 2020 5
Ms Excel Notes
June 2020 4
Excel Tutorial Notes
December 2019 42