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

5. Create Charts and Objects
5.2   Format a Chart

Charts: Modify Chart, Pg 124