Microsoft Excel Logo

Goals


- Know the main Excel tools

- Be able to perform calculations and use formulas

- Know how to handle graphics and pivot tables

Program

Presentation of Excel and the other tools of the Office suite
Examples of realization with Excel

Description of the interface (Ribbon, Quick Access Toolbar, etc.)
Structure of a file (workbooks, sheets and cells)
Create a workbook

Adding a sheet to the workbook
Rename a sheet
Move a sheet (on the same workbook or in another)
Identify a sheet using a tab color

The different ways to select cells
Insert rows and columns
Change the height and width of rows / columns

Format the content of a cell (font, colors …)
Apply borders
Manage alignment in a cell
Merge several cells
Apply a cell format

Workshop: Making a simple painting

Creating tables
Automatic formatting
Creating a custom style

Workshop: Creation of a personalized style and application on a board

Insert pictures (local or online)
Add and edit shapes
Insert organization chart

Define the application area
Use the predefined rules
Modify an existing rule
Create a new rule
Apply to an entire table

The formula bar
Addition, subtraction, multiplication, division
The value of parentheses
Applying a calculation to an entire column

Apply a sort on one or more columns
Modify sorting options
Apply filters on a table
Use advanced filters

Manage the page layout (margins, header and footer, etc.)
Scale your table
Manually manage page breaks
Define a print area
Define print options then print

Available display modes
Show / hide cells
Zoom
Freeze panes

Use of a referent cell ($)
Reminder on the basic formulas (SUM, AVERAGE, MAX …)
Formulas with condition (IF, NB.SI, SUM.SI …)
Date and time formulas ( TODAY, WEEKDAY …)
Text formulas (SMALL, LEFT, CONCATENATE …)
Search formulas ( VLOOKUP, HLOOKUP )

Workshop: Realization of a summary document with the main Excel formulas

Create named areas and integrate them into formulas
Find antecedents and dependents to formulas
Check formulas for errors
Change the way your workbook is calculated

Select the data to integrate into the graph
Choose a type of graph according to needs (histogram, curve, sector in 2D or 3D)
Define a quick layout for its graph
Add additional information to the graph (axis title, data labels, curve trend …)
Choose the visual aspect of your graphic via the styles

Apply table updates to associated charts
Change the chart type
Move a chart within its workbook
Customize the style of its chart
Integrate a chart into an external document (Word, PowerPoint)

Workshop: Generate a graph according to your needs

The principle of a pivot table
Define which calculation and label fields will be
Select the source of its pivot table
Place the fields to analyze
Apply a style to the pivot table

Integrate multiple calculations
Define grouping and sorting criteria
Insert segments
Integrate custom calculation fields

Workshop: Highlight the desired statistics from your table

Integrate subtotals into a table
Convert and consolidate
Data validation
Scenario analysis

Adding and modifying comments
Enable tracking of changes
Protect your workbook and sheets

Passing of the certification (if foreseen in the financing).

Duration

4 days

Price

£ 2495

Audience

All

Prerequisites

Knowledge of the PC environment

Reference

BUR1240-F