FirstPlace
Business Associates

Advanced Excel

BEGINNER

EXCEL INTRODUCTION :

• A overview of the screen, navigation and basic spreadsheet concepts
• Understanding workbooks, worksheets, rows, columns, cells
• Various selection techniques

ENTERING DATA

• Entering, Editing and Deleting Text, Numbers, Dates
• Using Auto Lists
• Moving and Copying data
• Inserting, Deleting and Hiding Rows & Columns
• Inserting, Deleting, Moving and Copying Sheets
• Using navigation techniques

CUSTOMIZING EXCEL

• Customizing the Ribbon
• Customizing the Quick Access Toolbar
• Using and Customizing AutoCorrect
• Changing Excel’s Default Options
• Creating a Custom AutoFill List
• Creating a Custom Number Format
• Customizing Excel Review

MANAGING AND NAVIGATING LARGE WORKBOOKS

• Using Workbook Views
• Selecting and Switching Between Worksheets
• Splitting and Freezing a Window
• Creating Headers and Footers
• Hiding Rows, Columns, Worksheets, and Windows
• Working with Multiple Workbook Windows
• Creating a Template
• Managing Workbooks Review

CREATING AND EDITING FORMULAE

• Concept of Formulae
• Creating Formulae, Editing Formulae
• Bodmas : Mathematical Order
• Copying Formulae
• Using Functions - Sum, Average, Max,Min, Count, Counta
• Applying Absolute (Fixed) Referencing

REFERENCING TECHNIQUES

• Relative Reference
• Absolute Reference
• Mixed Reference
• Moving Range Reference

FORMATTING AND PROOFING

• Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
• Basic conditional formatting
• Copying and Clearing Formats
• Working with Styles
• Spell Check, Find & Replace and AutoCorrect

INTERMEDIATE

MATHEMATICAL FUNCTIONS

• SumIf, SumIfs
• CountIf, CountIfs
• AverageIf, AverageIfs
• SumProduct, Subtotal

LOOKUP FUNCTIONS

• Vlookup / HLookup
• Match
• Dynamic Two Way Lookup
• Creating Smooth User Interface Using Lookup
• Offset
• Index
• Dynamic Worksheet linking using Indirect

LOGICAL FUNCTIONS

• Nested If ( And Conditions , Or Conditions )
• Alternative Solutions for Complex IF Conditions to make work simple
• And, Or, Not

TEXT FUNCTIONS

• Upper, Lower, Proper
• Left, Mid, Right
• Trim, Len
• Concatenate
• Find, Substitute

DATE AND TIME FUNCTIONS

• Today, Now
• Day, Month, Year
• Date, DateDif, DateAdd
• EOMonth, Weekday

ROUNDING FUNCTIONS

• Round
• RoundUp
• RoundDown
• MRound

ERROR HANDLING FUNCTIONS

• isNa
• isErr
• isError

ADVANCED PASTE SPECIAL TECHNIQUES

• Paste Formulas
• Paste Formats
• Paste Validations
• Paste Conditional Formats
• Add / Subtract / Multiply / Divide
• Merging Data using Skip Blanks
• Transpose Tables

SORTING

• Sorting on Multiple Fields
• Dynamic Sorting of Fields
• Bring Back to Ground Zero after Multiple Sorts

FILTERING

• Filtering on Text, Numbers & Date
• Filtering on Colors
• Copy Paste while filter is on
• Advanced Filters
• Custom AutoFilter

PRINTING WORKBOOKS

• Working with Themes
• Setting Up Print Area
• Printing Selection
• Branding with Backgrounds
• Adding Print Titles
• Fitting the print on to a specific defined size
• Customizing Headers & Footers

ADVANCED

IMPORT & EXPORT OF INFORMATION

• From Web Page
• Exporting to XML
• Creating Dynamic Dashboards and Reports Using Data on Other Applications
• Using Text To Columns

WHAT IF ANALYSIS

• Goal Seek
• Scenario Analysis
• Data Tables

GROUPING & SUBTOTALSDATA VALIDATION

• Number, Date & Time Validation
• Text Validation
• List Validation
• Handling Invalid Inputs
• Dynamic Dropdown List Creation using Data Validation

PROTECTING EXCEL

• File Level Protection
• Workbook Level Protection
• Sheet & Cell Level Protection
• Setting Permissions for Specific Tasks
• Track changes

CONSOLIDATION

• Consolidating data with identical layouts
• Consolidating data with different layouts
• Consolidating data with different Sheets

CONDITIONAL FORMATTING

• Creating Basic Conditional Formats
• Managing Conditions Created
• Dynamic Formatting using Formulas in Conditional Formatting

PIVOT TABLES

• Creating Simple Pivot Tables
• Basic and Advanced Value Field Setting
• Sorting based on Labels and Values
• Filtering based on Labels and Values
• Grouping based on numbers and Dates
• Drill-Down of Data
• GetPivotData Function
• Calculated Field & Calculated Items

CHARTS & PIVOT CHARTS

• Bar Charts / Pie Charts / Line Charts
• Dual Axis Charts
• Dynamic Charting
• Other Advanced Charting Techniques

EXCEL DASHBOARD

• Bar Charts / Pie Charts / Line Charts
• Planning a Dashboard
• Adding Tables to Dashboard
• Adding Charts to Dashboard
• Adding Dynamic Contents to Dashboard