Formation Microsoft Excel – Anglais

Introduction to Microsoft Excel (1H) 

• Discover the Excel interface : 

• What is Excel used for? 
• Finding your way around the interface 
• Open a new workbook or search for online templates 
• Locate the Ribbon, the Quick Access toolbar, the workspace… 
• Customize the ribbon 

• Customize the Quick Access Toolbar 
• Save a workbook 
• Know how to undo or redo an action 
• Become familiar with the most common keyboard shortcuts 
• Manipulating cells, rows and columns : 

• Locate cells, rows and columns 
• Entering content (text, numbers, dates, etc.) 
• Insert or delete rows and columns 
• Hide or show rows and columns 
• Know how to select cells, rows and columns 

• Move, copy, cut and paste cells, rows and columns 
• Use special pasting 
• Enlarge or reduce the size of rows and columns 
• Manage spreadsheets : 
• Insert or delete sheets 

• Rename a sheet 
• Select sheets 
• Move or duplicate sheets in the current workbook 
• Move or duplicate sheets in a new workbook 
• Change the colour of the tab 

Create and format a table (4H) 

• Using increments : 
• Use the fill handle 
• Increment months, days, quarters… 
• Increment numbers 

• Increment dates 
• Explore incremental copy options (the tag) 
• Use Flash Fill 
• Create and save a custom list 
• Explore formatting options : 

• Modify the font: size, color, underline… 
• Apply filling in cells 
• Change the orientation of cells 
• Merge and center content 
• Wrap text 

• Customize alignments 
• Use cell styles 
• Reproduce or erase a formatting 
• Use automatic formatting : 
• Why use automatic formatting ? 

• Apply automatic formatting 
• Modify style 
• Customize table style options 
• Show the total row 
• Know how to resize the table 

• Insert and customize a segment 
• Create a new table style 
• Convert to normal range 
• Customize cell formats : 
• What is cell format in Excel? 

• Change the format of a cell 
• Use numerical formats (Currency, date, etc.) 
• Use text format 
• Use conditional formatting : 
• Use cell highlighting rules 

• Use high/low range value rules 
• Apply and customize data bars 
• Apply color shades 
• Apply and customize icon sets 
• Delete / Remove rules 

Manage the display, layout and printing (1H) 

• Manage the workbook views in Excel : 
• Switch between the 4 workbook views and see the difference between them 
• Insert headers and footers 
• Activate or deactivate the grid of the sheet 

• Zoom in and out 
• Stack windows 
• Split a window 
• Know how to freeze the panes 
• Define layout options and print : 

• Customize the margins 
• Choose the orientation of the sheet 
• Repeat row/column titles when printing 
• Define a print area 
• Modify the background of the sheet 

• Define printing options 
• Define printer properties 
• Print on paper or in PDF 

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 

Master complex formulas (3H) 

• Automate searches between tables : 

• Set up VLOOKUP and HLOOKUP functions 
• Use the LOOKUP function 
• Compare the VLOOKUP and H functions to the XLOOKUP 
• Use nested INDEX and MATCH functions 
• Using date functions : 

• TODAY, NOW functions 
• DAY, MONTH, YEAR functions 
• DATEDIF function to calculate the difference between two dates 
• WORK.DAY, WORK.DAY.INTL functions 
• Conditional formatting with formulas : 

• Format an entire row based on a criterion 
• Create a calendar and automate the color of the ranges 
• Search and assign colors 
• Create a gantt project planner 

Using pivot tables (4H) 

• Learn about pivot tables : 
• Ensure the reliability of your database 
• Create a pivot table (TCD) 
• Discover the list of fields 
• Become familiar with the 4 zones of the pivot table 

• Populate the pivot table 
• Update the data in the pivot table 
• Duplicate fields 
• Summarize the values by SUM, AVERAGE… 
• Displaying values as a percentage 

• Using the number format 
• Group and ungroup data 
• Show or hide information 
• Sort and filter in a pivot table 
• Breakdown information on a new sheet (extraction) 

• Formatting the pivot table (layout, empty lines, styles…) 

• Learn more about data analysis: 
• Renaming pivot tables 
• Use visual filters – segments or timelines 

• Create connections between segments or timelines 
• Create calculated fields 
• Create calculated items 
• Display the list of formulas 
• Create a DCT consolidation 

• Create relationships (data models) 
• Navigate through the DCT parameters to customize the display 

Create complex graphics (2H) 

• Create and manage more complex charts: 
• Create a graph with conditional colors (MIN, MAX, RANK functions…) 

• Combine histograms and curves 
• Combine histograms and areas 
• Toggle series on the secondary axis 
• Create a cascade chart 
• Create a population pyramid chart 

• Creating a Gauge Graph 
• Using geographic data and creating map charts 
• Overview of other useful chart types 

Record macros (3H) 

• Getting started with macros : 

• Display the « Developer » tab 
• Understand the purpose of a macro and its limits 
• Discover the macro recorder 
• Prepare to record a macro (pitfalls to avoid) 
• Differentiate between a relative and absolute macro 

• Use keyboard shortcuts for selections 
• Recording macros and managing current actions : 
• Record a formatting macro 
• Record a sorting macro 
• Record a filter macro 

• Choose a keyboard shortcut to run a macro 
• Run a macro using the macro list 
• Use an additional macro 
• Using approved folders 
• Assign a macro to a button or image 

• Assign a macro to an icon on the quick access toolbar 
• Use the personal macro workbook and discover its features 
• Save the Excel file that supports macros