Excel Advanced (conducted in English) kursus

  • Hjem
  • Office
  • Excel
  • Excel Advanced (conducted in English) kursus

You will learn

This course in Excel begins where we left off on our Excel experienced course and is made for you who needs to be able to make dynamic calculations, data analysis and user-friendly work sheet solutions.

After the course you will be able to:

  • Build complicated formulas by using dynamic ranges
  • Import, adjust, compare, merge and make calculations from pretty much all data sources using Power Queries
  • Finding desired data using the advanced filter
  • Building worksheet models or templates by using user friendly controls
  • Automate actions using recorded macros
In the section ”Course Details” on this page, you will find more details about the content of this course in Excel.

We will teach you a number of advanced functions for managing dynamic ranges, for example, if you have some lookup values and have formulas to find the desired values in one or more ranges.

In addition, we look at Power Queries, which are a powerful tool for importing data from virtually any data source. You can manipulate your data in many ways, for example by splitting, linking or merging them.

You will learn the Data table tool and Scenarios for data analysis as well as the Solver, which allows Excel to calculate a wide range of values based on given criteria. The Solver can make thousands of calculations to find a possible solution.

We look at tools using advanced criteria ranges, fx some Database Functions for calculating totals or averages and the Advanced filter. The Advanced Filter is unknown to most people, but is an intelligent way of filtering a list based on many criteria. And the criteria can be saved.

If you want to build spreadsheet models with multiple users, you can with advantage inserting Controls to allow users to choose from predefined buttons than manually entering values.

We also look at recording Macros for automating routine tasks.

You get

Before the course
  • The opportunity to speak with one of our instructors about finding the right course for you.
During the course
  • Teaching by the most experienced instructor team in our cozy and fully updated classrooms located in the center of Copenhagen.
  • A course that consists of a both theory and practical exercises. We know the importance of having practical exercises and hands-on is always in focus during the courses.
  • A course material you can use as an encyclopedia after the course and downloads of course exercises.
  • All meals, which includes breakfast, fresh coffee, tea, fruit, soft drinks, lunch in an Italian restaurant on Gråbrødretorv, cake, candy and off cause Wi-Fi to your devices.
  • A course certificate as a proof of your new skills.
After the course
  • Access to our free hotline service, which means that you up to a year after the completing of the course can contact us if you have questions regarding subjects from the course.
  • We have a unique satisfaction guarantee in order to ensure you the complete benefit from the course.

Get the best benefit from the course

This Excel course is a part of 4D's unique collection of English conducted courses and we recommend that you have participated in our Excel Experienced or have the knowledge equivalent to the content covered in the above-mentioned course.

Course Details

Functions
Build-in functions ease and automate the task of performing advanced calculations. These functions are used for solving a wide range of problems in Excel. This module gives you a reference compendium for looking up many of the build-in Excel functions. We look at formulas with functions in functions and the advantages using the function guide.

The following will be covered:
  • Functions in functions
  • Use of the Function guide
  • Tips & tricks regarding the building of functions

Get and transform data
In this module you’ll learn about the Power Query tool, to import data from various data sources. And you’ll learn to transform and manipulate columns, creating calculated columns etc.. With Power Query you have a guide to manipulate all your data.

The following will be covered:
  • Importing data from various data sources - eg. Excel, text files, SQL server or other databases
  • Cleanup of redundant data
  • Connection manager - your queries are always up to date
  • Merge content from multiple tables - add more lists into one
  • Split and merge columns
  • Creating calculated columns - create your own formulas
  • Conversion between numbers and text
  • Replacement of values

Advanced formulas and functions
This module familiarizes you with a range of special functionalities in Excel. You learn how a data range can be expanded automatically when the range is filled in with more data. You will be introduced to special types of formulas in Excel, called array formulas. Here are the subjects of this module:

The following will be covered:
  • Dealing with calculations and named ranges
  • Creating dynamic lists – lists that automatically expands
  • Automatic offset of a named range
  • Using the function Indirect – a smart reference to named ranges and sheets
  • Analyze data using Scenarios and the Data Table tool
  • Advanced calculations using array formulas
  • Analyze advanced problems using solver – where you set up criteria’s and Excel calculates several unknown variables

Controls
In this module, you will learn how to add automation to a worksheet using controls. Controls are also used to make worksheets easier for others to use.

The following will be covered:
  • Combo box – a drop box with your predefined options
  • Spinner and Scrollbar, Option buttons and Check boxes – a fast way of choosing a value
  • Command buttons – to activate your recorded macros

Macros
In this module, you learn to add automated features to worksheets by using macros. You learn what macros do and when to use them. You learn to create simple macros and create shortcuts for them.

The following will be covered:
  • Recording and running a macro
  • Where are your macros stored?
  • Relative- and absolute recording – how is your code recorded
  • Macro security

Database tools
Here we look at two effective tools for filtering and calculation of data in large datasets. It is the Advanced Filter, where you can set up many criteria’s for what you want to extract. The second tool makes statistic calculations using D-functions where you also use a range with criteria’s for what you want to calculate.

The following will be covered:
  • Advanced filter - criteria-based data extraction
  • Database functions, fx. DSUM and DCOUNT – effective statistical calculations using multiple criteria’s

Shortcut keys
This module gives you a logical overview of all the Excel shortcuts.

The following will be covered:
  • Compendium of Excel shortcuts

Hold dig opdateret med vores nyhedsbrev

Modtag nyheder, gode tilbud, tips og tricks med vores månedlige nyhedsbrev.

Vi har både et Office/Adobe nyhedsbrev og et nyhedsbrev for vores tekniske Microsoft kurser.

Her bor vi

Hovedindgang
Amagertorv 21
1160 København K
Kursusindgang
Læderstræde 22-26
1201 København K
Åbningstider
Mandag: 08.00 - 16.30 (Indgang for kursister i Læderstræde åbner 8.30) 
Tirsdag: 08.30 - 16.30
Onsdag: 08.30 - 16.30
Torsdag: 08.30 - 16.30
Fredag: 08.30 - 16.30

 

Kontaktoplysninger

 
Amagertorv 21
1160 København K