Realdolmen Education

Details

Excel Advanced

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

3 day(s)

Audience

This course is oriented towards Excel users who already thoroughly know the program, but want to gain more in-deth knowledge.

Prerequisites

Participants should have taken the Excel Introduction course or they should have acquired knowledge of the basic functions of Excel.

Objectives

An extensive view in advanced functions of Excel

Methods

Class training with individual guidance and the possibility to ask questions. The student can immediately put the possibilities of the program into practice by means of exercises. Each student disposes of a workstation.

Description

Excel is a spreadsheet with many possibilities. The core aim is of course the creation of formulas and functions, but besides that Excel has also an excellent database management system as well as many tools for statistic analysis, not to forget charts.

This course discusses the following elements :

Advanced formatting techniques.

An overview of the more specialised functions : the logical functions, the lookup and reference functions, the text functions, and the date and time functions.

The database management in Excel : Sorting and filtering data, making subtotals, using specific database functions and creating pivot tables.

We also examine some analysis tools (data table, scenarios, goal seek and solver) as well as the use of range names in Excel.

Creating and modifying charts.

Finally we take a look at the integration of Excel with other applications of the Office suite, such as Word and Access.

Contents

  • Advanced formatting techniques
    • Customized cell formatting
    • Themes and styles
    • Conditional formatting
    • The use of custom views
  • Revision of Formulas and functions
    • The difference between a formula and a function
    • Copying of formulas and functions
    • Absolute and relative cell references in formulas
  • Advanced functions of Excel
    • Logical functions (IF, AND, OR, ...)
    • Lookup- and reference functions (VLOOKUP/HLOOKUP, MATCH, INDEX, OFFSET, ...)
    • Text functions (CONCATENATE, LEFT, RIGHT, MID, TRIM, FIND, ...)
    • Date and time functions
      • What is a date/time for Excel?
      • Calculate with dates
      • Date functions (TODAY, DATE, YEAR /MONTH/DAY, WEEKNUM)
      • Calculate with time (difference between 2 days, hours over 24, round, ...)
      • Time functions (NOW, TIME, HOUR/MINUTE/SECOND, ...)
  • Manage a database
    • Create an Excel table
    • Modifying the table by using the contextual command
    • Sorting data
    • Filter
    • Subtotals
    • Data validation
    • Database functions: COUNTIF - SUMIF - COUNBLANK
  • Pivot tables
    • Creating
    • Insert/delete fields
    • Modify data from the database
    • Show/hide items
    • Modify field settings
    • Group items
    • Show/hide details
    • Calculated fields
    • Calculated items
    • Sorting the pivot table
    • Charts and pivot tables
    • The options of pivot tables
    • Conditional formatting
  • Range names
    • Create names
    • Apply names in your sheets
    • Maintenance of names
    • Named ranges and formulas
  • What if analysis
    • Data table
    • Scenario
    • Goal seek
    • Solver
  • Charts
    • Creating
    • Modifying a chart
    • Formatting a chart
    • Creating a default chart
  • Templates
    • Create a template
    • Use a template
    • Modify a template
    • Change the default template of Excel
    • Open worksheets automatically at the launch of Excel
  • Integration with other applications
    • Exporting Excel tables