Excel

1. Excel Lesson 3

2. Functions • Function is a built-in formula that performs calculations automatically. • Examples: • SUM (Adds a series of cells) • COUNT (Counts non-blank cells in a range) • MAX (Finds the highest value in a range) • MIN (Finds the lowest value in a range)

3. Function Wizard • The Paste Function icon fx is located on the Standard Toolbar

4. Open the worksheet Payroll • In cell A12 add the label TOTAL • Position your cursor in cell D12 • Click on the Function Wizard • We want to find the total hours worked by all employees, so we will need to use the SUM feature • Click on SUM, click OK • A dialog box will appear, select cells D6:D10 and click OK • The total will appear in cell D12 and the formula will appear in the formula bar =SUM(D6:D10)

5. Copy the formula across through cell J12 • In cell A13 enter the label NO. OF EMPLOYEES • In cell C13 use the Function Wizard to find how many employees are on the payroll • Hint: Count cells C6:C10 • In cell A14 enter the label AVG. GROSS SALARY • In cell C14 use the Function Wizard to find the average gross salary • Hint: Average cells in column E • Save the changes you have made to Payroll

6. Create a Series • You can use the Fill Handle to quickly enter sequential values in a range of cells. • These values may be numbers, dates or times • Select cells A6:A10 and delete the data • Position your cursor in cell A6 and type ‘101 and press Enter • Move your cursor back to cell A6, click on the fill handle and drag down to cell A10 • You should now have the numeric labels 101-105

7. Insert Columns • To insert a new column, position your mouse in the column to the right of where you would like the new column to appear • Click on Insert, Column • Position your cursor in column C, click on Insert Column • Column C becomes D and a blank column C should appear

8. Auto Complete Auto Complete allows you to enter labels automatically after making repetitive entries. • In cell C5 enter the label Department • In cell C6 type Assembly • In cell C7 type Accounting • In cell C8 right click your mouse and select Pick From List from the menu options, select Accounting • In cell C9, right click, Pick from List, select Assembly (repeat in cell C10)

9. Comments • Excel allows you to attach text or audio comments to a cell that will display when your cursor is active in the cell. • Position your cursor in cell G6, click on Insert, Comment • A ivory box will appear for your type your note in • Type Federal Income Tax calculated at 20% and click off the cell • Note: There is now a red triangle in the upper right corner of that cell, when you click on that cell the comment will appear

10. Page Setup • Page Tab • Allows you to change orientation, scaling (Fit to One Page), paper size, first page number • Margins Tab • Allows you to change margins and to center the worksheet on the page horizontally  and vertically 

11. Page Setup cont. • Sheet Tab • Print Area - only define if you always want to print the same range of cells • Print Titles - you set the worksheet to print specified columns or row on each page • Example: You could print the labels in column A on each page of the worksheet so that you would not have to re-enter them on each page.

12. Page Breaks • Position your mouse in the cell where you would like to insert the break and click on Insert, Page Break • To remove a page break: click on Insert, Remove Page Break

13. Headers and Footers • Codes - you may insert codes into the header and footer to automatically print the current date, current time, page number and/or filename. The icons for these codes are located on the header/footer toolbar.