Advanced MS Excel 2010

Course details

Advanced Excel
SECTION 1
ADVANCED WORKSHEET FUNCTIONS.
CONDITIONAL & LOGICAL FUNCTIONS
• If Statements 
• Logical Test Value 
• If True / False 
• Nested If
COUNTING AND TOTALLING CELLS CONDITIONALLY
• Statistical If Statements
• Sumif 
• Countif
• Averageif 
• Averageifs
• Sumifs 
• Countifs 
• AND, OR, NOT 
• Iserror 
• Iferror 
• LOOKUP FUNCTIONS
• Lookup 
• Vector Lookup
• Hlookup 
• Vlookup 
• Nested Lookups
SECTION 2
USING EXCEL TO MANAGE LISTS
• EXCEL LISTS, 
• LIST TERMINOLOGY
• Row and Column Column Labels 
• List Size And Location
• SORTING DATA 
• Quick Sort 
• Multi Level Sort 
• Custom Sorting Options 
• Creating a Custom Sort Order SUBTOTALS
• Organizing the List for Subtotals 
• Create Subtotals 
• Summarizing a Subtotaled 
• List
• Show and Hide by Level Remove Subtotals 
• FILTERING A LIST 
• Auto filters 
• Search 
• Criteria - Custom Criteria and – Or Wildcards 
• Turning off Auto filter
ADVANCED FILTERING
• Set Criteria in Advanced Filter 
• CRITERIA 
• MULTIPLE CRITERIA 
• Using Multiple Rows in the Criteria 
• Range
• CALCULATED CRITERIA 
• Basic Calculation 
• Calculated Criteria Using Functions
• Copying Filtered Data 
• Unique Records
• Database Functions 
• PIVOTTABLES 
• Styles 
• SLICERS
SECTION 3
CHARTS
• CHARTING
• CREATING CHARTS 
• Embedded 
• Data Layout 
• Shortcut Menu (Right 
• Chart Types 
• Default Chart Type 
• FORMATTING 
• Formatting Chart Elements
• Resetting Custom Formats 
• Format 
• SPARKLINES 
• What are Spark lines?
• Create Spark lines 
• Customize Spark lines
• Axis options
SECTION 4 TEMPLATES
INTRODUCTION TO TEMPLATES
• Template Types 
• Normal Template 
• Sample 
• Create Custom Templates 
• To Use Custom Templates 
• Opening and Editing Templates
• Template Properties 
• Auto templates
SECTION 5
DRAWING AND FORMATTING
• INSERTING, FORMATTING AND DELETING OBJECTS
• Inserting a Drawing Object 
• Smart Art 
• Quick Styles 
• 2D And 3D 
• The Design Ribbon 
• WordArt 
• Formatting Shapes 
• Manual Formatting 
• Themes
• Customizing a Theme 
• Cell Styles Conditional Formatting
SECTION 6
EXCEL TOOLS
• Comments 
• Protecting 
• Tracking 
• Use a Shared Workbook To Collaborate 
• Share a Workbook 
• Links 
• Working With a Shared Workbook
• Conflicts 
• Stop Sharing 
• AUDITING 
• Tool Information 
• Go To Special 
• Error Checking
• Correct an Error Value 
• Watch Window
• Dependants and Precedents 
• PROOFING TOOLS 
• Spelling and Grammar 
• Thesaurus 
• Translation 
• Show or Hide Updated on 09 May, 2018