In this Excel course for experts you will learn even more useful tips and tricks for using tables. We show you how to use Add-ins, protect worksheets and work with scenarios. We also explain the date and time functions in detail. At the end we show you how to create a calendar in Excel, how to generate random numbers and use special functions such as array operations.
Course Duration: 7 - 11 hours
= free Demo = just released = already seen
Title of Film
Basic Program Settings
This video will show you how to adjust basic excel program settings such as the default saving area, the quick info, the live preview, the display of gridlines and the number of the worksheets.
This video will show you how to work with add-ins.
Converting Rows to Columns
It is possible to copy information from other Windows programs and paste them into an Excel table. Unfortunately, these copied information are usually organized in rows. This video will show you how to convert rows into columns and vice versa.
Working with Scenarios
"Best case","Worst case" and "Base case". Use scenarios to run certain calculations within different starting values and to compare the results.
This video will show you how to protect the cells in your tables for unwanted editing.
This video will show you how to create macros in order to automate a specific task.
Adding Macros to the Quick Access Toolbar
In this video you will learn how to start your macros using the quick access toolbar.
Display the Developer Tools Tab
In this video you will learn how to display the developer tools tab. This tool gives you access to macros, the VBA editor and to several control features such as check boxes, list and drop-down boxes and others.
We will show you in this video how to access external databases with Excel with the example of a Pivot Analysis. You will learn how to create a connection to a database server, define a query and add calculated fields to this query.
Working with Date and Time Formats
In this video you will learn how to create the cell formats date and time.
Date and Time Functions - Basics
In this video we will explain the calculation basics for date and time functions in Excel.
In this video you will learn more about the functions you can use for working with times: TIME, TIMEVALUE, HOUR, MINUTE, SECOND
Time Functions Calculating Times
We will show you in this video how to calculate times in Excel by using a few practical examples. Calculate times of more than 24 hours, work with negative times and calculate industrial minutes or the salary per hour.
Date Functions DATE
In this video we will explain the date functions DATE, YEAR, MONTH, DAY and DATEVALUE. Additionally we will give you some examples on how to create your own formulas with this functions and how to sort a birthday list by month.
Date Functions DATEDIF
We will show you in this video how to use the date function DATEDIF. The function DATEDIF calculates the difference between two dates.
Date Functions EDATE and EOMONTH
We will show you in this video how to use the function EDATE and EOMONTH. You can use these functions to determine payback dates and due dates.
Date functions NETWORKDAYS and WORKDAY
In this video we explain the date functions NETWORKDAYS and WORKDAY. You will see practical examples in order to see how these functions work and how to easily calculate due dates and the workdays of a whole year.
Date Functions WEEKDAY
We will show you in this video how to use the date function WEEKDAY. Additionally you will see how to display the weekday by using a simple formula.
Date Functions WEEKNUM
We will show you in this video how to use the date function WEEKNUM and how to determine the week number according to the european norm EN 28601 by using a formula.
Date Functions DAYS360
We will show you in this video how to use the date function DAYS360 and how to use a calculation formula to calculate interests.
Date Functions YEARFRAC
We will show you in this video how to use the date function YEARFRAC. You can use this function to display certain time segments as fractions of a year and this way show and determine maturities and other liabilities.
Create customizable calenders by using three simple formulas, autofil and conditional formatting.
This video will give you an introduction on random numbers. We will explain the two functions "random numbers" and "random range".
We will show you in this video how to work with array formulas in Excel. Array formulas can be used to perform various calculations at once.
We will show you in this video how to use the function VLOOKUP. With two examples we will explain the uses of approximate and exact searches. You can use the same procedure for the HLOOKUP function.
You can use the function IF in order to run a logical test and you can define what happens if a condition is either true or false. By using a nested IF function you can set various criteria.
IF-AND Function, IF-OR Function
By using the IF-AND function you will get a certain result if all conditions are met. The IF-OR function will get you a certain result if at least one of the conditions is met.
We will show you in this video how to audit, repair and fix calculations by using the formula auditing.
This video will show you how to use the SUMIF-function. By using this function, you can add up the contents of the cells if certain search criteria are met.
You can use the COUNTIF function in order to determine the amount of cells or records that match a certain criterion. The COUNTIF function can be used with various criteria as well.
Calculate the linear and degressive amortization of assets using the SLN and VDB functions.
Calculating Capital Value
In this video you will learn how to calculate the capital value of an investment by using the methods NPV (NET PRESENT VALUE) and XNPV (X NET PRESENT VALUE) of financial mathematics.
Calculating the IRR
In this film we will show you the three possible functions for the calculation of the internal rate of return.