Advanced Excel Functions & Features, Analyzing Data, Pivot Tables, Charting & Formulas

Advanced Excel Functions & Features, Analyzing Data, Pivot Tables, Charting & Formulas

MS Excel In-House & Online Individual, Group Training Programs

Advanced Excel Functions & Features, Analyzing Data, Pivot Tables, Charting & Formulas

Trainer

Excel Dashboard

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.

Excel Dashboard for Reporting

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 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 TABLE

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. CREATING POWERFUL FORMULA

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. CHART IN EXCEL

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

5. APPLYING ADD-INS EXCEL

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

Contact us : https://www.malaysia-training.com/contact-us