Workshop Two: Creating Cell Data, Formatting and Revising Worksheets/Workbooks

1. Workshop Two: Creating Cell Data, Formatting and Revising Worksheets/Workbooks Dr. Carolyn Rainey Professor Emeritus

2. What is Microsoft Certification? • Microsoft Office Specialist (MOS) • www.certiport.com • http://www.certiport.com/Portal/desktopdefault.aspx?page=common/pagelibrary/MOS_whatIs.html • Microsoft Office 2010 and 2013 Exam Objectives • Exam voucher • http://www.measureup.com/Microsoft-Office-Specialist-MOS-200720102013-Exam-Voucher-P3195.aspx

3. Overview of Workshops • Workshop One (10/22) - Managing the Worksheet Environment • Workshop Two (10/24) - Creating Cell Data, Formatting/Revising Worksheets/Workbooks • Workshop Three (10/29) - Applying Formulas and Functions • Workshop Four (10/31) - Presenting Data Visually • Workshop Five(11/5) - Data Analysis

4. Creating Cell Data • To participate in this live demonstration: Start a new document • Key information • Create row and column titles • Move cell contents by dragging and dropping • Use a template • Open Central Sierra (Chapter 2)http://highered.mcgraw-hill.com/sites/0073519383/student_view0/data_files.html

5. Creating Cell Data • Import text file • Use Office Clipboard • Paste Options • Transpose • Edit, replace, clear cell contents • Widen column to remove ##### • Review AutoFit

6. Formatting Cells and Worksheets • Apply and modify cell formats • Fraction/scientific formatting • Special number formats • Custom number formats (### lbs) • Apply text and number formatting • Wrap text • Apply gridlines to worksheet • Rename and color sheet tabs

7. Creating Cell Data • Use fill handle - AutoFill • Copy info • Create column titles with fill handle • Create data in a series • Create a custom list • Copy a formula by double-clicking the fill handle

8. Creating Cell Data • Data Validation: rules that help prevent data entry errors • Create a Validation rule • Values, decimals, list, date range, time, text length • Create an Input Message • Create an Error Alert • Clear a Validation rule

9. Formatting Cells and Worksheets • Apply diagonal rotation to text • Apply formatting to multiple columns • Apply • workbook theme • colors, fonts, effects • Convert text

10. Creating Cell Data • Apply and manipulate hyperlinks to • Web addresses • Cells in the workbook • E-mail addresses • Protect a worksheet, yet allow users to edit some cells • Select the cells you want to allow changes • Unlock these cells • Protect worksheet, save; close • Change username and re-open the file

11. Creating Cell Data • Conditional Formatting • Cell Rules • Filtering/sorting • Top/bottom • Value comparison (> + <) • Icon sets, color bars, color scales • Manage conditional formatting rules; set values • Create and apply cell styles

12. Managing Worksheets and Workbooks • Apply formatting to more than one worksheet at a time • Create/edit comments • Print/delete comments • Insert/delete worksheets

13. Sample CertiPrep Items Participate in live demonstration of CertiPrep simulation of exam questions.