Microsoft Excel Reporting Course

EXCEL INTRODUCTION :

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

ENTERING DATA

1. Entering, Editing and Deleting Text, Numbers, Dates
2. Using Auto Lists
3. Moving and Copying data
4. Inserting, Deleting and Hiding Rows & Columns
5. Inserting, Deleting, Moving and Copying Sheets
6. Using navigation techniques

CUSTOMIZING EXCEL

1. Customizing the Ribbon
2. Customizing the Quick Access Toolbar
3. Using and Customizing AutoCorrect
4. Changing Excel’s Default Options
5. Creating a Custom AutoFill List
6. Creating a Custom Number Format
7. Customizing Excel Review

MANAGING AND NAVIGATING LARGE WORKBOOKS

1. Using Workbook Views
2. Selecting and Switching Between Worksheets
3. Splitting and Freezing a Window
4. Creating Headers and Footers
5. Hiding Rows, Columns, Worksheets, and Windows
6. Working with Multiple Workbook Windows
7. Creating a Template
8. Managing Workbooks Review

CREATING AND EDITING FORMULAE

1. Concept of Formulae
2. Creating Formulae, Editing Formulae
3. Bodmas : Mathematical Order
4. Copying Formulae
5. Using Functions - Sum, Average, Max,Min, Count, Counta
6. Applying Absolute (Fixed) Referencing

REFERENCING TECHNIQUES

1. Relative Reference
2. Absolute Reference
3. Mixed Reference
4. Moving Range Reference

FORMATTING AND PROOFING

1. Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
2. Basic conditional formatting
3. Copying and Clearing Formats
4. Working with Styles
5. Spell Check, Find & Replace and AutoCorrect

MATHEMATICAL FUNCTIONS

1. SumIf, SumIfs
2. CountIf, CountIfs
3. AverageIf, AverageIfs
4. SumProduct, Subtotal

LOOKUP FUNCTIONS

1. Vlookup / HLookup
2. Match
3. Dynamic Two Way Lookup
4. Creating Smooth User Interface Using Lookup
5. Offset
6. Index
7. Dynamic Worksheet linking using Indirect

LOGICAL FUNCTIONS

1. Nested If ( And Conditions , Or Conditions )
2. Alternative Solutions for Complex IF Conditions to make work simple
3. And, Or, Not

TEXT FUNCTIONS

1. Upper, Lower, Proper
2. Left, Mid, Right
3. Trim, Len
4. Concatenate
5. Find, Substitute

DATE AND TIME FUNCTIONS

1. Today, Now
2. Day, Month, Year
3. Date, DateDif, DateAdd
4. EOMonth, Weekday

ROUNDING FUNCTIONS

1. Round
2. RoundUp
3. RoundDown
4. MRound

ERROR HANDLING FUNCTIONS

1. isNa
2. isErr
3. isError

ADVANCED PASTE SPECIAL TECHNIQUES

1. Paste Formulas
2. Paste Formats
3. Paste Validations
4. Paste Conditional Formats
5. Add / Subtract / Multiply / Divide
6. Merging Data using Skip Blanks
7. Transpose Tables

SORTING

1. Sorting on Multiple Fields
2. Dynamic Sorting of Fields
3. Bring Back to Ground Zero after Multiple Sorts

FILTERING

1. Filtering on Text, Numbers & Date
2. Filtering on Colors
3. Copy Paste while filter is on
4. Advanced Filters
5. Custom AutoFilter

PRINTING WORKBOOKS

1. Working with Themes
2. Setting Up Print Area
3. Printing Selection
4. Branding with Backgrounds
5. Adding Print Titles
6. Fitting the print on to a specific defined size
7. Customizing Headers & Footers

IMPORT & EXPORT OF INFORMATION

1. From Web Page
2. Exporting to XML
3. Creating Dynamic Dashboards and Reports Using Data on Other Applications
4. Using Text To Columns

WHAT IF ANALYSIS

1. Goal Seek
2. Scenario Analysis
3. Data Tables

GROUPING & SUBTOTALS

DATA VALIDATION

1. Number, Date & Time Validation
2. Text Validation
3. List Validation
4. Handling Invalid Inputs
5. Dynamic Dropdown List Creation using Data Validation

PROTECTING EXCEL

1. File Level Protection
2. Workbook Level Protection
3. Sheet & Cell Level Protection
4. Setting Permissions for Specific Tasks
5. Track changes

CONSOLIDATION

1. Consolidating data with identical layouts
2. Consolidating data with different layouts
3. Consolidating data with different Sheets

CONDITIONAL FORMATTING

1. Creating Basic Conditional Formats
2. Managing Conditions Created
3. Dynamic Formatting using Formulas in Conditional Formatting

PIVOT TABLES

1. Creating Simple Pivot Tables
2. Basic and Advanced Value Field Setting
3. Sorting based on Labels and Values
4. Filtering based on Labels and Values
5. Grouping based on numbers and Dates
6. Drill-Down of Data
7. GetPivotData Function
8. Calculated Field & Calculated Items

CHARTS & PIVOT CHARTS

1. Bar Charts / Pie Charts / Line Charts
2. Dual Axis Charts
3. Dynamic Charting
4. Other Advanced Charting Techniques

EXCEL DASHBOARD

1. Bar Charts / Pie Charts / Line Charts
2. Planning a Dashboard
3. Adding Tables to Dashboard
4. Adding Charts to Dashboard
5. Adding Dynamic Contents to Dashboard