Course contents / Table of Contents
INTRODUCTION....................................................................................................
How To Use This Guide ............................................................................................
Objectives..................................................................................................................
Instructions ................................................................................................................
Appendices................................................................................................................
SECTION 1 ADVANCED WORKSHEET FUNCTIONS.......................................
NAMES....................................................................................................................
Defining Names..........................................................................................................
Creating Names Automatically....................................................................................
Managing Names .......................................................................................................
USING NAMES .......................................................................................................
Go To.........................................................................................................................
Names In Formulae ....................................................................................................
Applying Names .........................................................................................................
CONDITIONAL & LOGICAL FUNCTIONS...........................................................
If Statements................................................................................................................
Logical Test.................................................................................................................
Value If True / False ...................................................................................................
Nested If.....................................................................................................................
Statistical If Statements ...............................................................................................
AND, OR, NOT.........................................................................................................
And.............................................................................................................................
Or ..............................................................................................................................
Not ............................................................................................................................
LOOKUP FUNCTIONS...........................................................................................
Lookup.......................................................................................................................
Vector Lookup............................................................................................................
Hlookup......................................................................................................................
Vlookup .....................................................................................................................
Nested Lookups..........................................................................................................
OTHER USEFUL FUNCTIONS................................................................................
ISERROR....................................................................................................................
IFERROR....................................................................................................................
COUNTIF...................................................................................................................
AVERAGEIF ..............................................................................................................
AVERAGEIFS ............................................................................................................
SUMIFS.......................................................................................................................
COUNTIFS..................................................................................................................
DATA CONSOLIDATION..........................................................................................
SECTION 2 VIEWS, SCENARIOS, GOAL SEEK AND SOLVER...........................
GOAL SEEKING AND SOLVING.............................................................................
Goal Seek.....................................................................................................................
Solver ..........................................................................................................................
Solver Parameters.........................................................................................................
Constraints ...................................................................................................................
Solver And Scenario Manager.......................................................................................
Opening Scenario Manager............................................................................................
Saving Solver Solutions .................................................................................................
Reports..........................................................................................................................
VIEWS & SCENARIOS...............................................................................................
Custom Views................................................................................................................
Typical Custom View Model...........................................................................................
Defining A View..............................................................................................................
Showing A View.............................................................................................................
Scenario Manager ..........................................................................................................
Load The Scenario Manager.........................................................................................
Showing A Scenario......................................................................................................
Editing A Scenario ........................................................................................................
Deleting A Scenario ......................................................................................................
SECTION 3 USING EXCEL TO MANAGE LISTS....................................................
EXCEL LISTS,LIST TERMINOLOGY........................................................................
Row And Column Content..............................................................................................
Column Labels ...............................................................................................................
List Size And Location....................................................................................................
Miscellaneous ................................................................................................................
SORTING DATA .......................................................................................................
Custom Sorting Options ...............................................................................................
Creating A Custom Sort Order......................................................................................
ADDING SUBTOTALS TO A LIST ...........................................................................
Examining Subtotals.......................................................................................................
FILTERING A LIST.....................................................................................................
Custom Criteria .............................................................................................................
Wildcards ......................................................................................................................
Turning Off Autofilter......................................................................................................
Advanced Filtering .........................................................................................................
Set Criteria ....................................................................................................................
Entering Search Criteria .................................................................................................
Wildcards With Text Criteria .........................................................................................
Multiple Criteria ............................................................................................................
Checking The Criteria Range .........................................................................................
Calculated Criteria..........................................................................................................
Calculated Criteria Using Functions ................................................................................
Copying Filtered Data.....................................................................................................
Unique Records..............................................................................................................
LIST STATISTICS........................................................................................................
Database Functions.........................................................................................................
PIVOTTABLES.............................................................................................................
To Create A PivotChart Report From An Existing PivotTable Report ..............................
Create A Static Chart From The Data In A PivotTable Report ....................................
Delete A PivotTable Or PivotChart Report .................................................................
Create Layout For PivotTables ...................................................................................
Modifying A PivotTable ..............................................................................................
MANAGING PIVOTTABLES...................................................................................
Using Another PivotTable Report.................................................................................
Changing An Existing Report's Source Data .................................................................
Automatically Refresh Data When A Workbook Is Opened..........................................
Automatically Refresh Data At Regular Time Intervals ..................................................
Require A Password To Refresh An External Data Range ............................................
Grouping PivotTable Items...........................................................................................
Formatting A PivotTable .............................................................................................
SECTION 4 CHARTS ..............................................................................................
INTRODUCTION TO CHARTING ..............................................................................
Terminology ....................................................................................................................
Embedded Charts..........................................................................................................
Separate Chart Pages ....................................................................................................
Three Methods To Create Charts...................................................................................
Moving And Resizing Embedded Charts ........................................................................
Data Layout ..................................................................................................................
Shortcut Menu (Right Click) ..........................................................................................
Chart Types ..................................................................................................................
Default Chart Type ........................................................................................................
FORMATTING CHARTS ...........................................................................................
Design Ribbon ...............................................................................................................
Switch Rows And Columns.............................................................................................
Changing The Chart Layout ............................................................................................
Chart Styles ...................................................................................................................
Moving Chart Location...................................................................................................
Layout Ribbon ...............................................................................................................
Formatting Chart Elements..............................................................................................
Resetting Custom Formats..............................................................................................
Adding, Removing And Formatting Labels .....................................................................
Axes..............................................................................................................................
Gridlines ........................................................................................................................
Unattached Text.............................................................................................................
Format Dialog................................................................................................................
Add A Series Manually .................................................................................................
The Series Function........................................................................................................
Charting With Blocks Of Data........................................................................................
SECTION 5 TEMPLATES ........................................................................................
INTRODUCTION TO TEMPLATES ..............................................................................
Standard Templates ...........................................................................................................
Custom Templates .............................................................................................................
AutoTemplates...................................................................................................................
Opening And Editing Templates..........................................................................................
Template Properties............................................................................................................
SECTION 6 AUDITING...........................................................................................
AUDITING FEATURES .....................................................................................................
Tracers..................................................................................................................................
Comments.............................................................................................................................
SECTION 7 OTHER FORMATTING .........................................................................
FORMATTING CELLS...........................................................................................................
Themes....................................................................................................................................
Customising A Theme...............................................................................................................
Styles ......................................................................................................................................
Conditional Formatting .............................................................................................................
SECTION 8 OTHER EXCEL FEATURES.....................................................................
INSERTING, FORMATTING AND DELETING OBJECTS.................................................
Inserting A Drawing Object ......................................................................................................
SmartArt...................................................................................................................................
WordArt ..................................................................................................................................
Formatting Shapes ....................................................................................................................
QuickStyles...............................................................................................................................
Manual Formatting ....................................................................................................................
REVIEWING ...........................................................................................................................
Protecting..................................................................................................................................
Use A Shared Workbook To Collaborate...................................................................................
Share A Workbook ...................................................................................................................
PROOFING TOOLS ................................................................................................................
Spelling And Grammar ...............................................................................................................
Thesaurus..................................................................................................................................
Translation ................................................................................................................................
Show Or Hide ScreenTips ...........................................................................................................
EXCEL 2007 SPECIFICATIONS AND LIMITS ............................................................
INTRODUCTION....................................................................................................
How To Use This Guide ............................................................................................
Objectives..................................................................................................................
Instructions ................................................................................................................
Appendices................................................................................................................
SECTION 1 ADVANCED WORKSHEET FUNCTIONS.......................................
NAMES....................................................................................................................
Defining Names..........................................................................................................
Creating Names Automatically....................................................................................
Managing Names .......................................................................................................
USING NAMES .......................................................................................................
Go To.........................................................................................................................
Names In Formulae ....................................................................................................
Applying Names .........................................................................................................
CONDITIONAL & LOGICAL FUNCTIONS...........................................................
If Statements................................................................................................................
Logical Test.................................................................................................................
Value If True / False ...................................................................................................
Nested If.....................................................................................................................
Statistical If Statements ...............................................................................................
AND, OR, NOT.........................................................................................................
And.............................................................................................................................
Or ..............................................................................................................................
Not ............................................................................................................................
LOOKUP FUNCTIONS...........................................................................................
Lookup.......................................................................................................................
Vector Lookup............................................................................................................
Hlookup......................................................................................................................
Vlookup .....................................................................................................................
Nested Lookups..........................................................................................................
OTHER USEFUL FUNCTIONS................................................................................
ISERROR....................................................................................................................
IFERROR....................................................................................................................
COUNTIF...................................................................................................................
AVERAGEIF ..............................................................................................................
AVERAGEIFS ............................................................................................................
SUMIFS.......................................................................................................................
COUNTIFS..................................................................................................................
DATA CONSOLIDATION..........................................................................................
SECTION 2 VIEWS, SCENARIOS, GOAL SEEK AND SOLVER...........................
GOAL SEEKING AND SOLVING.............................................................................
Goal Seek.....................................................................................................................
Solver ..........................................................................................................................
Solver Parameters.........................................................................................................
Constraints ...................................................................................................................
Solver And Scenario Manager.......................................................................................
Opening Scenario Manager............................................................................................
Saving Solver Solutions .................................................................................................
Reports..........................................................................................................................
VIEWS & SCENARIOS...............................................................................................
Custom Views................................................................................................................
Typical Custom View Model...........................................................................................
Defining A View..............................................................................................................
Showing A View.............................................................................................................
Scenario Manager ..........................................................................................................
Load The Scenario Manager.........................................................................................
Showing A Scenario......................................................................................................
Editing A Scenario ........................................................................................................
Deleting A Scenario ......................................................................................................
SECTION 3 USING EXCEL TO MANAGE LISTS....................................................
EXCEL LISTS,LIST TERMINOLOGY........................................................................
Row And Column Content..............................................................................................
Column Labels ...............................................................................................................
List Size And Location....................................................................................................
Miscellaneous ................................................................................................................
SORTING DATA .......................................................................................................
Custom Sorting Options ...............................................................................................
Creating A Custom Sort Order......................................................................................
ADDING SUBTOTALS TO A LIST ...........................................................................
Examining Subtotals.......................................................................................................
FILTERING A LIST.....................................................................................................
Custom Criteria .............................................................................................................
Wildcards ......................................................................................................................
Turning Off Autofilter......................................................................................................
Advanced Filtering .........................................................................................................
Set Criteria ....................................................................................................................
Entering Search Criteria .................................................................................................
Wildcards With Text Criteria .........................................................................................
Multiple Criteria ............................................................................................................
Checking The Criteria Range .........................................................................................
Calculated Criteria..........................................................................................................
Calculated Criteria Using Functions ................................................................................
Copying Filtered Data.....................................................................................................
Unique Records..............................................................................................................
LIST STATISTICS........................................................................................................
Database Functions.........................................................................................................
PIVOTTABLES.............................................................................................................
To Create A PivotChart Report From An Existing PivotTable Report ..............................
Create A Static Chart From The Data In A PivotTable Report ....................................
Delete A PivotTable Or PivotChart Report .................................................................
Create Layout For PivotTables ...................................................................................
Modifying A PivotTable ..............................................................................................
MANAGING PIVOTTABLES...................................................................................
Using Another PivotTable Report.................................................................................
Changing An Existing Report's Source Data .................................................................
Automatically Refresh Data When A Workbook Is Opened..........................................
Automatically Refresh Data At Regular Time Intervals ..................................................
Require A Password To Refresh An External Data Range ............................................
Grouping PivotTable Items...........................................................................................
Formatting A PivotTable .............................................................................................
SECTION 4 CHARTS ..............................................................................................
INTRODUCTION TO CHARTING ..............................................................................
Terminology ....................................................................................................................
Embedded Charts..........................................................................................................
Separate Chart Pages ....................................................................................................
Three Methods To Create Charts...................................................................................
Moving And Resizing Embedded Charts ........................................................................
Data Layout ..................................................................................................................
Shortcut Menu (Right Click) ..........................................................................................
Chart Types ..................................................................................................................
Default Chart Type ........................................................................................................
FORMATTING CHARTS ...........................................................................................
Design Ribbon ...............................................................................................................
Switch Rows And Columns.............................................................................................
Changing The Chart Layout ............................................................................................
Chart Styles ...................................................................................................................
Moving Chart Location...................................................................................................
Layout Ribbon ...............................................................................................................
Formatting Chart Elements..............................................................................................
Resetting Custom Formats..............................................................................................
Adding, Removing And Formatting Labels .....................................................................
Axes..............................................................................................................................
Gridlines ........................................................................................................................
Unattached Text.............................................................................................................
Format Dialog................................................................................................................
Add A Series Manually .................................................................................................
The Series Function........................................................................................................
Charting With Blocks Of Data........................................................................................
SECTION 5 TEMPLATES ........................................................................................
INTRODUCTION TO TEMPLATES ..............................................................................
Standard Templates ...........................................................................................................
Custom Templates .............................................................................................................
AutoTemplates...................................................................................................................
Opening And Editing Templates..........................................................................................
Template Properties............................................................................................................
SECTION 6 AUDITING...........................................................................................
AUDITING FEATURES .....................................................................................................
Tracers..................................................................................................................................
Comments.............................................................................................................................
SECTION 7 OTHER FORMATTING .........................................................................
FORMATTING CELLS...........................................................................................................
Themes....................................................................................................................................
Customising A Theme...............................................................................................................
Styles ......................................................................................................................................
Conditional Formatting .............................................................................................................
SECTION 8 OTHER EXCEL FEATURES.....................................................................
INSERTING, FORMATTING AND DELETING OBJECTS.................................................
Inserting A Drawing Object ......................................................................................................
SmartArt...................................................................................................................................
WordArt ..................................................................................................................................
Formatting Shapes ....................................................................................................................
QuickStyles...............................................................................................................................
Manual Formatting ....................................................................................................................
REVIEWING ...........................................................................................................................
Protecting..................................................................................................................................
Use A Shared Workbook To Collaborate...................................................................................
Share A Workbook ...................................................................................................................
PROOFING TOOLS ................................................................................................................
Spelling And Grammar ...............................................................................................................
Thesaurus..................................................................................................................................
Translation ................................................................................................................................
Show Or Hide ScreenTips ...........................................................................................................
EXCEL 2007 SPECIFICATIONS AND LIMITS ............................................................
Learn Excel Online - Free Online Excel Training Course - Improve Excel Skills. 99Excel is one of the reputed institutes for Online Excel Course in India. Contact us- 9312580474
ReplyDeleteMore@ http://99excel.com/
Best Excel Training Academy in Noida, Excel Training in Noida with Lowest Cost. Compare all Academies in Noida-Price, Reviews, Address, Maps and Photos. Contact us- 9312580474
ReplyDeleteMore@ http://99excel.com/
Find the best institute in Noida for Excel Training in Noida, Lowest Cost, Free Demo, 100 Satisfaction Service. Compare all Training institute in Noida- Price, Reviews, Address, Maps and Photos. Contact us- 9312580474.
ReplyDeleteMore@ http://99excel.com/
Find the top Institute for Advanced Excel Classes in Noida, Lowest Cost, Free Demo, 100% Satisfaction Service. Compare all institutes in Noida- Price, Reviews, Address, Maps and Photos. Contact us- 9312580474
ReplyDeleteMore@ http://99excel.com/
99 Excel is one of the best Training Institutes for Ms Excel Training Institutes in Delhi Ncr With Lowest Cost, Free Demo, 100% Satisfaction Service. Get Phone Number, Address, Maps and Contact us-9312580474 http://99excel.com/
ReplyDelete