Set up calculations and formulas (5H)

Master the basic calculations

• Set up simple calculations (addition, subtraction, division, etc.)
• Use and understand the value of parentheses in a calculation
• Copy a calculation
• Know how to reference cells (relative A1, absolute $A$1, mixed $A1 A$1)
• Calculate percentages (%)
• Use named ranges

Discover common statistical functions

• SUM, AVERAGE, MIN, MAX, NB, NBVAL… functions
• SUMIF, AVERAGEIF, COUNTIF… functions
• SUMIFS, AVERAGEIFS, COUNTIFS… functions

Use conditional functions

• Set up a simple IS
• Nest multiple IF functions
• Nest the functions IF, AND, OR…
• IF. CONDITIONS, IF. MULTIPLE… functions

Use text functions

• TEXT function to convert a number
• STXT, LEFT, RIGHT functions to extract characters
• Convert content with LOWER, UPPER…
• CONCAT(ENER) function to gather information

Insert and format charts (2H)

Create and manage simple charts

• Why create a graphic?
• How to make the right choice of graph
• Understand how it works (X and Y axis)
• How to read a graph
• Selecting data
• Use the recommended charts
• Insert simple graphs (histograms, curves, sectors…)
• Interchange rows and columns
• Change the type of graph
• Add or remove chart elements
• Change the layout of the chart
• Change colors and styles
• Move a chart
• Customize the different parts of a graph
• Saving a graph as a template

Work efficiently on a database (3H)

Sort data

• Perform a simple sort in AZ or ZA order
• Perform multiple levels of sorting
• Sort a custom list
• Sort on cell colors or fonts
• Sort on conditional formatting icons
• Match case when sorting
• Sort left to right

Use the automatic filter

• Apply filters
• Filtering a column
• Make filters on several levels
• Use the custom filter to filter text, numbers or dates
• Filter on cell colors or fonts
• Filter on conditional formatting icons

Inserting subtotals

• Reminders on sorting
• Inserting subtotals in relation to a column
• Insert nested subtotals (multiple columns)
• Insert nested subtotals (multiple calculations)
• Replace existing subtotals
• Display subtotals below or above the data
• Create a page break between groups
• Format rows containing totals
• Delete totals

Create drop-down lists

• Set List
• Create a simple drop-down list
• Customize the input message
• Customize error alerts
• Circle invalid data
• Clear validation circles
• Clear data validation