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

© 2021 AFI

Prerequisites

Windows - User or equivalent
Microsoft Excel - Basic

Objectives

  • 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

Contents

Chapter 1 - Organize workbook sheets

  • Navigate between the sheets of a workbook
  • Rename a sheet
  • Group spreadsheets
  • Manipulate workbook sheets
Chapter 2 - Perform calculations between worksheet and workbooks
  • Create formulas with references to other sheets in the workbook
  • Set links between workbooks.
  • Manage links
Chapter 3 - Use fonctions
  • Enter a function
  • Modify a function and use the wizard to use multiple functions in one function
  • Perform statistical, time-based data, logical, texts and finance calculations
Chapter 4 - Use reference names in formulas
  • Create reference names
  • Selecting a named reference
  • Using the name manager
  • Using a reference name in a formula
Chapter 5 - Create and manage a list of data
  • Explore the structure of a list of data
  • Create a list of data
  • Use validation
  • Manipulate the data list using the data table tools
  • Use external data
  • Import an external source such as Access or Web
  • Import another source through Microsoft Query and the Query Wizard
  • Refresh the linked data
  • Delete the data connection
Chapter 6 - Sort a list of data
  • Identify the items to sort
  • Sort a list of data
Chapter 7 - Filter a data list
  • Using automatic filters
  • Use advanced filters
Chapter 8 - Use information, text, statistical and search functions ìn a data table
  • Use functions to search for values in a data list
  • Use data base categories function
  • Use the lookup function
Chapter 9 – Create 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 10 - Create a PivotChart
  • Set and creating a PivotChart
  • Add fields in a PivotChart
  • Filter data in a PivotChart

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.
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.
Frédéric Paradis
Frédéric Paradis
Certified Trainer and Cloud Architect
As a certified Microsoft trainer, Frédéric describes himself as a Cloud magician who easily navigates the mythical space between technology and reality.
Be aware of trends, innovations and best practices, every month.
Confidentiality
Training center accredited by Emploi-Québec, Accreditation : 0051460
GST : 141582528 – QST : 1019557738
© 2021 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