Advance Excel

Introduction

    1. How To Use This Guide
    2. Objectives
    3. Instructions
    4. Appendices

(SECTION 1) : ADVANCED WORKSHEET FUNCTIONS

    1. Defining Names
    2. Creating Names Automatically
    3. Managing Names
  1. USING NAMES

    1. Go To
    2. Names In Formulae
    3. Applying Names
  2. CONDITIONAL & LOGICAL FUNCTIONS

    1. If Statements
    2. Logical Test
    3. Value If True / False
    4. Nested If
    5. Statistical If Statements
  3. AND, OR, NOT

    1. And
    2. OR
    3. NOT
  4. LOOKUP FUNCTIONS

    1. Lookup
    2. Vector Lookup
    3. Hlookup
    4. Vlookup
    5. Nested Lookups
  5. OTHER USEFUL FUNCTIONS

    1. ISERROR
    2. IFERROR
    3. COUNTIF
    4. AVERAGEIF
    5. SUMIFS
    6. COUNTIFS

(SECTION 2) : VIEWS, SCENARIOS, GOAL SEEK AND SOLVER

    GOAL SEEKING AND SOLVING

    1. Goal Seek
    2. Solver
    3. Solver Parameters
    4. Constraints
    5. Solver And Scenario Manager
    6. Opening Scenario Manager
    7. Saving Solver Solutions
    8. Reports
  1. VIEWS & SCENARIOS

    1. Custom Views
    2. Typical Custom View Model
    3. Defining A View
    4. Showing A View
    5. Scenario Manager
    6. Load The Scenario Manager
    7. Showing A Scenario
    8. Editing A Scenario
    9. Deleting A Scenario

(SECTION 3) : USING EXCEL TO MANAGE LISTS

    EXCEL LISTS,LIST TERMINOLOGY

    1. Row And Column Content
    2. Column Labels
    3. Solver Parameters
    4. List Size And Location
    5. Miscellaneous
  1. SORTING DATA

    1. Custom Sorting Options
    2. Creating A Custom Sort Order
  2. ADDING SUBTOTALS TO A LIST

    1. Examining Subtotals
  3. FILTERING A LIST

    1. Custom Criteria
    2. Wildcards
    3. Turning Off Autofilter
    4. Advanced Filtering
    5. Set Criteria
    6. Entering Search Criteria
    7. Wildcards With Text Criteria
    8. Multiple Criteria
    9. Checking The Criteria Range
    10. Calculated Criteria
    11. Calculated Criteria Using Functions
    12. Copying Filtered Data
    13. Unique Records
  4. LIST STATISTICS

    1. Database Functions
  5. PIVOT TABLES

    1. To Create A PivotChart Report From An Existing PivotTable Report
    2. Create A Static Chart From The Data In A PivotTable Report
    3. Delete A PivotTable Or PivotChart Report
    4. Create Layout For PivotTables
    5. Modifying A PivotTable
  6. MANAGING PIVOT TABLES

    1. Using Another PivotTable Report
    2. Changing An Existing Report's Source Data
    3. Automatically Refresh Data When A Workbook Is Opened
    4. Automatically Refresh Data At Regular Time Intervals
    5. Require A Password To Refresh An External Data Range
    6. Grouping PivotTable Items
    7. Formatting A PivotTable

(SECTION 4) : CHARTS

    INTRODUCTION TO CHARTING

    1. Terminology
    2. Embedded Charts
    3. Separate Chart Pages
    4. Three Methods To Create Charts
    5. Moving And Resizing Embedded Charts
    6. Data Layout
    7. Shortcut Menu (Right Click)
    8. Chart Types
    9. Default Chart Type
  1. FORMATTING CHARTS

    1. Design Ribbon
    2. Switch Rows And Columns
    3. Changing The Chart Layout
    4. Chart Styles
    5. Moving Chart Location
    6. Layout Ribbon
    7. Formatting Chart Elements
    8. Resetting Custom Formats
    9. Adding, Removing And Formatting Labels
    10. Axes
    11. Gridlines
    12. Unattached Text
    13. Format Dialog
    14. Add A Series Manually
    15. The Series Function
    16. Charting With Blocks Of Data

(SECTION 5) : TEMPLATES

    INTRODUCTION TO TEMPLATES

    1. Standard Templates
    2. Custom Templates
    3. Auto Templates
    4. Opening And Editing Templates
    5. Template Properties

(SECTION 6) : AUDITING

    AUDITING FEATURES

    1. Tracers
    2. Comments

(SECTION 7) : OTHER FORMATTING

    FORMATTING CELLS

    1. Themes
    2. Customising A Theme
    3. Styles
    4. Conditional Formatting

(SECTION 8) : OTHER EXCEL FEATURES

    INSERTING, FORMATTING AND DELETING OBJECTS

    1. Inserting A Drawing Object
    2. SmartArt
    3. WordArt
    4. Formatting Shapes
    5. QuickStyles
    6. Manual Formatting
  1. REVIEWING

    1. Protecting
    2. Use A Shared Workbook To Collaborate
    3. Share A Workbook
  2. PROOFING TOOLS

    1. Spelling And Grammar
    2. Thesaurus
    3. Translation
    4. Show Or Hide ScreenTips