Excel Intermediate: Functions and data lists

Expand your Excel skills with this training. Master data list management and the use of statistical, logical, text and financial functions.
Microsoft Partner

Upcoming sessions

Show all sessions
Guaranteed to run
  • Duration: 2 days
  • Regular price: $290
  • Preferential price: $250

Course outline

Please note that the most represented version in the group will be installed in computer workstations. However, the training addresses the differences between versions.

Duration : 2 days

© AFI Expertise inc.


Windows - User or equivalent
Microsoft Excel - Basic


  • Organize and work with a list of data
  • Illustrate the data in a data list using PivotTable and PivotChart
  • Create formulas using a data list
  • Integrate external data into Excel

Teaching method

Presentations - Demos - Facilitated and individual exercises


Chapter 1 - Use reference names

  • Create reference names
  • Reach a Named Item
  • Use Name Manager
  • Use a reference name in a formula
Chapter 2 - Use functions
  • Enter a function
  • Edit a function
  • Perform statistical calculations
  • Calculate time-based data
  • Use logical functions
Chapter 3 - Organize workbook sheets
  • Navigate between the sheets of a workbook
  • Rename a sheet
  • Group spreadsheets
  • Manipulate workbook sheets
Chapter 4 - Set and maintain link formulas between sheets and between workbooks
  • Create formulas with references to other sheets in the workbook
  • Set links between workbooks
  • Manage links
Chapter 5 - Outline data
  • Outline a spreadsheet
  • Create an outline
  • Act on summary data
Chapter 6 - Verify workbook formulas
  • Select cells containing formulas
  • Visually check spreadsheets
Chapter 7 - Create and manage a list of data
  • Explore the structure of a list of data
  • Create a list of data
  • Manipulate the list of data using data table functions
Chapter 8 - Sort a list of data
  • Identify the items to sort
  • Sort a list of data
Chapter 9 – Generate subtotals in a list of data
  • Prepare a list of data to add subtotals
  • Create subtotals
  • Obtain multiple subtotals
Chapter 10 - Create and manipulate PivotTables
  • Set a PivotTable
  • Create a PivotTable
  • Change display options and layout of a PivotTable
  • Manipulate fields
  • Apply a PivotTable style
  • Update data in a PivotTable
Chapter 11– Create a PivotChart
  • Set and creating a PivotChart
  • Add fields in a PivotChart
Chapter 12 - Sort a list of data
  • Use automatic filters
  • Use elaborate filters
Chapter 13 - Use functions specific to lists of data
  • Use functions for a list of data
Chapter 14 - Use search functions
  • Use functions to search for values in a list
  • Use the VLOOKUP function
  • Use the HLOOKUP function
Chapter 15 - Import text file data
  • Identify text file features
  • Open a .txt file with Excel
  • Create a connection with a .txt file
  • Distribute text on columns

Surround yourself with the best

Pierre-Edouard Brondel
Pierre-Edouard Brondel
Trainer and Desktop Application Consultant
Renowned as an educational expert in the IT and office technology field who has accumulated more than 25 years of experience, Pierre-Édouard is first and foremost passionate about human capital.
Leif Martin Bédard
Leif Martin Bédard
Trainer and Desktop Application Consultant
Specialized in the Microsoft suite and with a background in accelerated learning, Leif is a trainer whose communication approach and adaptability are always fully appreciated
Marc Maisonneuve
Marc Maisonneuve
Trainer and Professional Efficiency Consultant
Ghislain Parent
Ghislain Parent
Trainer and Professional Efficiency Consultant
With more than 25 years of experience, Ghislain thrives on the challenge of improving your work team’s efficiency.
Be aware of trends, innovations and best practices, every month.
Training center accredited by Emploi-Québec, Accreditation : 0051460
GST : 141582528 – QST : 1019557738
© 2020 AFI
AFI Expertise

The AFI experience brought to you by Edgenda

At AFI, our focus is to offer you relevant training and impactful learning experiences in line with your transformation approach. Do you need support for your approach? Check out Edgenda’s services: Edgenda.com