CREATING A WORKSHEET

1. CREATING A WORKSHEET By Dr. Ennis - Cole

2. OBJECTIVES • Plan, build, test, document, preview, and print a worksheet • Enter labels, values, and formulas • Calculate a total using the AutoSum button • Copy formulas using the fill handle and Clipboard

3. OBJECTIVES • Learn about relative, absolute, and mixed references • Use the AVERAGE, MAX, and MIN functions to calculate values in the worksheet • Spell check the Worksheet • Insert a row

4. OBJECTIVES • Reverse an action using the Undo button • Move a range of cells • Format the worksheet using AutoFormat • Center printouts on a page • Customize worksheet headers

5. DEVELOPING WORKSHEETS • Determine the purpose • Enter the data • Test • Edit • Document • Improve the appearance • Save and Print

6. PLANNING THE WORKSHEET • What is the goal? • What is the desired result? • What data is needed to calculate the results? • What calculations are needed to produce the desired output?

7. BUILDING THE WORKSHEET • Start Excel, maximize the window • Click A1 cell to make it active • Type the heading, press Enter key • Enter labels for the column headings • Enter the values • Click the AutoSum button to calculate total • Enter the formula

8. COPYING A FORMULA • Using Fill Handle • Click the cell or range of cells to copy • Click and drag the fill handle to the place where you want the copies to appear, release the mouse • Using Relative References • Click the cell having any formula • Click and drag the contents to the place to be copied • Formulas in the new cells are automatically adjusted for new locations

9. COPYING A FORMULA • Absolute Reference • Use it when you want a cell reference to point to the same location in the worksheet when you copy it • Insert a dollar ($) sign before the column and row of the cell reference • Example, $B$8 is an absolute cell reference • B8 is a relative reference • $B8 is a mixed reference • Use the F4 key to change the type of cell reference

10. COPYING AND PASTING A CELL OR CELL RANGE • Select the cell or range of cells to be copied • Click the Copy button on the Standard toolbar • Select the range you want to copy • Click the paste button • Press the Enter key

11. RENAME AND SAVE • Select the sheet to rename • Double click and type the new name • Click File, Save As • Type the name • Click Save • Refer to Fig. 2-15

12. EXCEL FUNCTIONS • A Function performs a predefined operation • SUM function adds the values in a range of cells • Excel provides 100’s of functions • Syntax: • FUNCTION NAME(argument 1, argument 2,…..) • Refer to fig2-16 for a list of selected Excel functions

13. AVERAGE function • The Arithmetic mean • Syntax: • AVERAGE(number1, number2,…) where number is the range of cells • Refer to Fig. 2-17 to calculate the AVERAGE by using the Paste Function button

14. MAX and MIN functions • MAX finds the largest number • Syntax • MAX(number1, number2,…) • Type =MAX(B4:B7), press the Enter key • MIN finds the smallest number • Syntax • MIN(number1, number2,…) • Type =MIN(C4:C7), press the Enter key

15. TESTING and SPELL CHECKING • Enter the test values • Enter the extreme values • Click cell A1and begin checking the spelling from the top of the worksheet • Click the spelling button on the Standard toolbar • Change the spelling/ignore the suggestions

16. CHANGING THE COLUMN WIDTH & INSERTING A ROW • Click the column width you want to change • Click Format, Column, Width • Enter the new column width • Click any cell in the row above which you want to insert the new row • Click Insert and then click Rows • Excel inserts one row for every row selected

17. Undo and Move • Click the Undo button to restore the original worksheet • Select the range of cells to move • Place the mouse pointer until it changes to an arrow • Click and drag the range to the new location • Release the mouse

18. AutoFormat • Select the cells • Click Format, AutoFormat • Select a Format Style • Click OK • Refer to figures 2-24 and 2-25

19. Centering and Print Preview • Click the Print Preview button, click Zoom • Click Setup button to open the Page Setup dialog box • Click the Margins tab • Click the Horizontally check box • Click the Vertically check box • Click Ok

20. Headers and Footers • Click Print Preview, Setup, Header/Footer • Click the Custom Header • Click the Filename button • Click the Right Section box • Click the Date button, click OK • Click Custom Footer, Center Section • Type your name, Click Ok, Close.

21. Documenting & Adding Cell Comments • One source of internal documentation would be a worksheet placed as the first • Another source is the Property dialog box • Select the cell in which you add the comment • Click Insert, Comment • Type your comment, click anywhere to store the comment

22. Displaying and Printing Worksheet formulas • Click Tools, Options • Click View, Formulas • Click Ok to return to the worksheet • Click File, Page Setup • Click Sheet, Row and Column Headings • Click Gridlines • Click Page, Landscape option button

23. To Display the Worksheet values • Click the Fit to option button • Click the Print Preview button • Click the Print button • Press Ctrl+` to display the worksheet values • Close the workbook without saving • Exit Excel.