Corporate Excel Training

Best practice approach on how to quickly work with any data in Excel

• Format as table
• Freeze headlines
• Eliminate gridlines
• Navigating through tables with the keyboard
• Adding and removing rows and columns
• Sorting data alphabetically or numerically
• Basic filtering to display only the data that meets certain criteria (also with several criteria)
• Adjusting column width and row height
• Hiding columns and rows
• Validating formats

Using conditional formatting to highlight important data

• Highlight cells based on values
• Highlight duplicates
• Highlight equals

Introduction to Lists

• Creating and formatting lists
• Data validation for lists
• Creating drop-down lists
• The autofill function

Intoduction to Pivot Tables

• What are Pivot Tables, how do they work, and when to use them
• Creating a Pivot Table
• Using Pivot filters
• Updating Pivot table data
• Exercise Data Analysis
• Creating a Pivot Chart
• Tips and tricks for pivot tables

How to work with formulas and functions in Excel.

SUM, AVERAGE, MIN & MAX

CELL REFERENCES: The famous “dollar sign”

FUNCTION ASSISTANT: How to use it for function creation

IF: Performs a logical comparison between a specified value and what the user expects.

Nested IF-function: Operating with several conditions

COUNTIF: Counts the number of cells that meet a single condition.

SUMIF: Adds all numbers in a range of cells based on one criterion.

VLOOKUP: (Use case I) Searches for a value in the first column of a range and returns a value in the same row from a specified column.

VLOOKUP: (Use case II) Merging tables based on a common criterion

CONCATENATE / CONCAT: Combines two or more text strings into one string.

LEFT, RIGHT, MID: Extracts a specified number of characters from a text string

TEXT TO COLUMNS: To separate data into columns

COUNT & COUNTA: Counts the number of cells that contain numbers and non-blank cells in a range, respectively.

DATE CALCULATIONS: Calculating duration and dates. Exercise creating a team calender.

Types of Charts in Excel: Introduction to basic chart types and when best to use which type.

Creating Charts

• How to select data in Excel to create a chart.
• Overview of chart elements (title, axis, legends, data series).

Customizing Charts:

• How to switch between different chart types.
• Adjusting colours, fonts, and adding data labels
• Modifying the axis scale and changing axis titles