Content:

• Overview of Excel interface (ribbons, cells, rows, columns);
• Basic operations;
• Customizing the Excel workspace;
• Introduction to functions and formulas.

Content:

• Sum & SumProduct;
• Average Function and percentage Gain;
• Practice;
• Homework Assignment;

Content:

• VLOOKUP;
• HLOOKUP;
• Index Match;
• XLOOKUP;
• Practice;
• Homework Assignment;

Content:

• Sum & Sumifs;
• Count & Countifs Functions;
• Max and Min, large, Small;
• Frequency Analysis;
• Practice;
• Homework Assignment;

Content:

• IF & IFs;
• AND, OR, NOT;
• IFERROR;
• Practice;
• Homework Assignment;

Content:

• Split & Merge Text;
• Text Formatting: PROPER, LOWER, UPPER;
• Trim Function;
• Transpose Data;
• Practice;
• Homework Assignment;

Content:

• TODAY and TDATE
• DAY, MONTH, YEAR, HOUR, MINUTES, SECONDS
• DAYOFWEEK, WORKDAY, WEEKNUM

Content:

• DIFFDATE
• Practice
• Homework Assignment

Content:

• Why do we need pivot tables?
• Preparing data for a pivot table;
• Creating our first pivot table;
• Pivot table fields;
• The “Analyze” and “Design” tabs of a pivot table;
• How to clear, select, and move a pivot table;
• How to refresh a pivot table and change the data source of a pivot table?
• TEST: Pivot table basics and data preparation;
• PRACTICE: Pivot table basics and data preparation;

Content:

• Sorting pivot table data;
• Pivot table label filters;
• Pivot table value filters;
• How to apply multiple filters in a pivot table;
• Grouping pivot table data;
• Pivot table slicers and timelines;
• How to show all filter pages of a pivot table;
• TEST: Sorting, filtering, and grouping pivot table data
• PRACTICE: Sorting, filtering, and grouping pivot table data

Content:

• Why do we need pivot tables?
• Preparing data for a pivot table;
• Creating our first pivot table;
• Pivot table fields;
• The “Analyze” and “Design” tabs of a pivot table;
• How to clear, select, and move a pivot table;
• How to refresh a pivot table and change the data source of a pivot table?
• TEST: Pivot table basics and data preparation;
• PRACTICE: Pivot table basics and data preparation;

Content:

• Summing pivot table data;
• Additional calculations in the pivot table;
• Show data as % of Column/Row;
• Show data as % of Parent Total;
• Show data differences dynamically;
• Show data as a running total;
• How to show the rank of pivot table values;
• Creating calculated fields in a pivot table;
• Creating calculated items in a pivot table;
• TEST: Calculations in a pivot table;
• PRACTICE: Calculations in a pivot table;

Content:

• Introduction to pivot charts
• How to create a column chart;
• How to create a pie chart;
• How to create a bar chart;
• How to protect a chart from resizing;
• How to change the chart type;
• Chart style and design;
• How to move a chart to a separate sheet;
• How to use slicers and timelines;
• TEST: Pivot charts in Excel
• PRACTICE: Pivot charts in Excel

Content:

• Final Assignment
• Get Certificate

Final Project:
At the conclusion of the course, students will be required to complete a final project using a provided actuarial dataset. The project will test their ability to:

• Data Preparation: Clean, manipulate, and organize raw data for analysis.
• Excel Modeling: Apply advanced Excel functions, formulas, and PivotTables to build actuarial models.
• Reporting & Dashboards: Present results through professional reports and interactive dashboards, communicating findings clearly to stakeholders.

Content:

• What is a Power Pivot;
• How to add data to the Power Pivot;
• How to link tables together
• Counting unique values in a pivot table;
• TEST: Power Pivots;
• PRACTICE: Power pivots;