Microsoft Excel 2013 Study Guide: Intermediate Excel            Microsoft Office Specialist (MOS): Exam 77-420 Core for Excel 2013 1. Create Worksheets and Workbooks 1.1  Create Worksheets and Workbooks Add Worksheets to a Workbook, Pg 28 Copy and Move Worksheets, Pg 25 1.2  Navigate in Worksheets and Workbooks Insert Hyperlinks, Pg 193 Change Worksheet Order, Pg 186 Navigate with the Name Box, Pg 90 1.3  Format Worksheets and Workbooks Worksheet Tab Color, Pg 187 Modify Page Setup, Pg 149 Insert and Delete Rows and Columns, Pg 33 Insert Watermarks, Pg 155 Insert Headers and Footers, Pg 159 1.4  Customize Options and Views for Worksheets and Workbooks Hide Worksheets, Pg 188 Formula Auditing: Display Formulas, Pg 44 1. Create Worksheets and Workbooks 1.5  Configure Worksheets and Workbooks to Print Or Save Print: Set Print Area, Pg 157 Save in Alternative Formats, Pg 214 Print: Individual Worksheets, Pg 167 Print: Set Print Scaling, Pg 166 Print: Repeat Headers and Footers, Pg 156 Print: Setup Workbooks to Print, Pg 148 Save to a Remote Place (SkyDrive), Pg 207 2. Create Cells and Ranges, 2.1 Insert Data in Cells and Ranges AutoFill Data, Pg 18 2.3 Order and Group Cells and Ranges Sparklines: Insert a Sparkline, Pg 178 Named Ranges: Name a Range, Pg 89 Study Guide Page 1 -Page 2

4. Apply Formulas and Functions
4.1  Apply Cell Ranges and References in Formulas and Functions

Formulas: Relative and Absolute), Pg 37
Formulas: Order of Operations, Pg 223
Formulas: Use Named Ranges in Formulas, Pg 34

4.3  Apply Conditional Logic In Functions
Conditional Logic Function: SUMIF, Pg 98
Conditional Logic Function: AVERAGEIF, Pg 104
Conditional Logic Function: COUNTIF, Pg 101

4. Apply Formulas and Functions
4.4  Format and Modify Text with Functions

TEXT Function: RIGHT. LEFT, MID, Pg 230
TEXT Function: TRIM, Pg 236
TEXT Function: UPPER and LOWER, Pg 65
TEXT Function: CONCATENATE, Pg 62

5. Create Charts and Objects
5.2   Format a Chart

Charts: Modify Chart, Pg 124