Management Decision Making Excel Functions & Features

Advanced Microsoft Excel Functions & Features For Management Decision Making

Trainer

Target Audience: Participants should know the application of the following functions: VLOOKUP; IF; COUNTIF; SUMIF; TREND; GROWTH; REPLACE; EXACT; TRIM. Advisable for participants to attend the “Advanced Microsoft Excel Tips and Techniques for Management” program first.

Functions & Features

About the Course:

Understand the more advanced functions on features of what Excel 2007 offers
Perform various statistical analysis using the statistical functions
Analyze data using some of the more advanced built-in features
Comprehend the more advanced text functions and its applications
Perform date and time calculations for a given period, or time
Incorporate multiple charts and understand the use of Histogram
Learn to use and invoke the Add-Ins
How best to apply a what-if analysis using the Solver

Training / Course Outline:

1. SUMMARIZING & ANALYZING DATA

Advanced Subtotals
Formatting the Subtotal Rows
Adding and Copying with Subtotals
Consolidating Data
Using Array Formulas
Applying Scenarios
Working with various aspects of Tables
Visualizing data using customized Data Bars, Color Scales, and Icons
Using Stop if True in Conditional Formatting
Comparing Dates with Conditional Formatting
Finding cells containing data for a given period
Highlighting specific row and every other row
Preventing formulas from being displayed
Protecting entire worksheet except selected columns or rows

2. WORKING WITH PIVOT TABLES

Creating a Pivot Table
Rearranging Fields in a Pivot Table
Explaining the Report Layout Options
Using the Report Filters Feature
Using Top 10 & Date Filters
Handling Blank Cells
Drilling Down in the Pivot Table
Sorting a Pivot Table
Formatting a Pivot Table
Creating Custom Formats
Explaining the Grouping Options
Adding Formulas to a Pivot Table
Changing a Calculation in a Pivot Table
Replicating a Pivot Table
Counting with a Pivot Table
Using Pivot Charts

3. CHARTING IN EXCEL

Using Combination Charts
Exploring Other Charts
Advanced Chart Types – Gantt Chart
Creating a Custom Layout
Generating a Histogram

4. CREATING POWERFUL FORMULAS

Applying Goal Seek in a What-if analysis
Converting Text to Numbers and vice versa
Using various Date and Time Functions
Using INDEX, MATCH, and OFFSET Functions
Using the CONVERT Function
Using the RAND and RANDBETWEEN Functions
Working with Regression Analysis
Ranking & Sorting Formulas
General purpose IFERROR function
AVERAGEIF; AVERAGEIFS Functions
COUNTIFS; SUMIFS Functions
Determining the sign of a number
Using further Text Functions
Applying Database Functions
Statistical Analysis using Statistical Functions

4.APPLYING ADD-INS IN EXCEL

Applying Add-Ins to use non-standard features and functions in Excel