Duration
2 days
Introduction
Some may wonder if it is possible to maintain data integrity in Excel, but experts say that the Excel data problem can be solved with a few tips. This two days training introduces you to Microsoft Excel 2013 features in exploiting financial analysis. Participants will learn how to finding solutions with data table, gold seek and scenarios and resource optimization with solver as well as PivotTable.
Audience
This course is intended for data analysts, intermediate Excel users
Prerequisites
Before attending this course, students should have experience:
- A basic working knowledge of Microsoft Office Excel
Course Outlines
Analysing Data Dynamically by Using PivotTables
- Rearranging the Layout
- Adding New Field
- Renaming a Field
- Formatting Numbers
- Changing Sorting Order
- Showing Top 10
- Refreshing Data
Filtering, Showing, and Hiding PivotTable Data
- Grouping Items
- Displaying Subtotals
- Hiding/Showing Grand Totals
Formatting PivotTable
- Using Slicer & Formatting Slicer
- Editing PivotTables
- Rename and reconfigure a PivotTable; create a formula that references PivotTable data
Formatting PivotTables
- Apply a number format, PivotTable style, banded rows, and conditional formatting; create a custom PivotTable style
Creating PivotTables from External Data
- Create a PivotTable based on data you import from a text file
- Creating Second Data Field
- Changing the Summary Function
- Displaying Numerical Value as a Percentage of the Total
- Creating a Custom Calculation
- More Function in Custom Calculation
Analyzing data and alternative data sets
- Examining data by using the Quick Analysis Lens
- Defining an alternative data set
- Defining multiple alternative data sets
- Analyzing data by using data tables
- Varying your data to get a specific result by using Goal Seek
- Finding optimal solutions by using Solver
- Analyzing data by using descriptive statistics
- Key points
Working with Scenarios
- Creating Scenarios
- Loading Scenarios
- Merging Scenarios
- Editing Scenarios
- Creating a Scenario Summary Report
- Deleting Scenarios
Using Solver
- Setting up the Worksheet
- Running Solver
- Generating Reports and Scenarios with Solver
- Modifying Constraints
- Setting Solver Options
Using Data Tables
- Creating One Input Data Tables
- Creating Two Input Data Tables
Programme scope, delivery arrangements and suitability are confirmed during enquiry.