Auditexcel.co.za offers a fair amount of free Excel
Training and Tutorials covering many features and functions of Excel.
Most of these are provided through screen videos, so you will
need Windows
Media Player to see and use these Excel training videos. Also take a
look at their page of Vital
Tips in Using Microsoft Excel.
Free
Excel Training Videos
Excel
Database Video Tutorial
Learn how to use
all the
functions
that start with a D, for example: DSUM and DCOUNT.
Microsoft
Excel Function Tutorials
Financial
Functions in Excel
Learn from these Excel
tutorials
how to use
the financial calculations.
Rate (2 min)
Calculate the interest RATE charged on a constant annuity cash flow. Excel Help File Description: Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Internal Rate of Return / IRR (2 min)
Calculate the Internal Rate of Return of a series of cash flows. Excel Help File Description: Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.
Net Present Value/ NPV (3 min)
Calculate the Net Present Value of a series of cash flows. Excel Help File Description: Calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values).
Calculate the Compound Annual Growth Rate (CAGR) using Excel (2 min)
Use the RATE function to calculate the CAGR
PMT (2 min)
The PMT function calculates the required repayment amount to amortise (pay off) a debt or loan based on the original capital amount, interest charged and term of the loanExcel Help File Description: Calculates the payment for a loan based on constant payments and a constant interest rate
CUMIPMT & CUMPRINC (4 min)
The CUMIPMT and CUMPRINC functions allow you calculate the total interest or capital (principal) paid between any two periods without having to build an amortization table. Excel Help File Description: Returns the cumulative interest paid (or principal paid) on a loan between start period and end period. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in (follow this link to learn how).
Date Funtion Tutorials for Excel
Here are Excel tutorials showing you how to use the Date functions in Microsoft Excel.
DATE, DAY, MONTH, and YEAR (4 min)
These functions allow you easily extract various components of a date for further manipulations, or to combine bits of a date into a format that Excel can understand.
EOMONTH (4 min)
The EOMONTH (end of month) function allows you to generate a month end date no matter what the original date (even taking into account leap years). Extremely useful when you want to report on standardized dates. Excel Help File Description: Returns the serial number for the last day of the month that is the indicated number of months before or after start date. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in (follow this link to learn how).
EDATE (4 min)
The EDATE function generates a date a specified number of months before or after a reference date. Excel Help File Description: Returns the serial number that represents the date that is the indicated number of months before or after a specified date (the start_date). Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. (follow this link to learn how)
Excel Tutorials for Statistics Functions
Learn how to perform statistical analysis using Excel, such as calculating the rank of a cell in a list, or the minimum or maximum numbers in a list.
Rank (5 min)
The Rank function allows you to rank a selection of cells in ascending or descending order automatically. Easier than using complex IF functions to achieve the same goal. Once you have learnt this, go to Tips and Tricks, to see how to automatically move cells around depending on there order. Excel Help File Description: Returns the rank of a number in a list of numbers. The rank of a number is its size relative to other values in a list. (If you were to sort the list, the rank of the number would be its position.)
Max & Min (2 min)
Max and Min allows you to specify the maximum or minimum number that can appear in a cell. Excel Help File Description: Returns the largest (or smallest) value in a set of values.
Logical Functions in Excel
These Excel tutorials show you how to use the logical operators IF, AND, and OR in your functions.
IF
The IF function allows you to perform alternate actions depending on certain criteria. Excel Help File Description: Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. Use IF to conduct conditional tests on values and formulas.
AND & OR
These functions allow you to check multiple criteria that they either all meet the criteria (AND) or at least some of them do (OR). Excel Help File Description: AND- Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE. OR- Returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Learn Text Excel Functions
Learn how to use several the text manipulation functions.
FIND
The FIND function allows you to search for a particular character within some text which is especially useful when trying to extract parts of the text. Excel Help File Description: FIND finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text, from the first character of within_text. You can also use SEARCH to find one text string within another, but unlike SEARCH, FIND is case sensitive and doesn’t allow wildcard characters.
LEN
The LEN function tells you how many characters make up a cell which is particularly useful when trying the manipulate the text within the cell. Excel Help File Description: LEN returns the number of characters in a text string.
Concatenate or &
The Concatenate (or &) can be used to join up the contents of multiple cells into one cell. This text function is extremely useful for generating unique keywords or preparing intelligent reports. Excel Help File Description: Joins several text strings into one text string
Lower, Upper & Proper
These text functions give you the ability to change the capitalization (case) of cells to being all lower, or all UPPER, or Proper. Excel Help File Description: Lower and Upper converts all uppercase (lowercase) letters in a text string to lowercase (uppercase). Proper capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters
Left, Right & Mid
These text functions give you the ability to to extract certain segments of a cell (normally text). Useful if only part of a cell’s text contains the information you want.. Excel Help File Description: LEFT (RIGHT) returns the first (last) character or characters in a text string, based on the number of characters you specify. MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.
Information Functions in Excel
Find out about functions and formulas that will provide you with added information about a cell, worksheet or workbook in Excel.
ISERROR
The ISERROR function allows you to test whether a cell has generated an error message. This is particularly useful for division by zero (#DIV/0!) errors and is often combined with an IF statement to provide an alternate action if an error is generated. Excel Help File Description: Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
Math and Trig Excel Function Tutorials
This Excel tutorial demonstrates useful mathematical and trigonometry functions.
SUMIF and COUNTIF
The Sumif and Countif commands allow you to sum or count cells depending on whether they meet the stated criteria. Excel Help File Description: Counts or sums the number of cells within a range that meet the given criteria
Tips and Tricks in using Excel
Show the underlying formulas in all cells (1 min)
A shortcut which allows you to show either the results in all cells or the underlying formula in the cells.
Filling in the blank cells quickly and easily (3 min)
There are occasions when you would like to fill in all blank cells with a formula. This is a quick and easy way to achieve this. Use is made of the GoTo Special command so it may be worthwhile learning about it.
Cleaning up data using the Data Autofilter (7 min)
Excel is often used as a means of cleaning up data and produces reports. Often the most time consuming aspect is the data cleanup. Using the Data AutoFilter it is possible to achieve more in less time.
Changing Multiple Worksheets at the same time (4 min)
In Excel it is possible to change a number of worksheets at the same time. Extremely useful but don’t forget to switch it off once you are done!
Automatic sorting of a list (5 min)
Rank a list of numbers and then automatically (without macros) have the numbers sort themselves into your preferred order (and pull other information with it if necessary). Can be used to generate sport league tables e.g. English Premiership log or the World Cup Soccer logs. You need to know the workings of the VLOOKUP and RANK functions.
Absolute Musts to Use Microsoft Excel
Function Wizard (3 min)
The function wizard allows you to use any of Excels functions in a very user friendly way. You therefore do not need to remember the complex syntax of any of the commands.
Auditing Toolbar (5 min)
The Auditing Toolbar is one if Excel’s best kept secrets. It allows you to visualise the cells used in any formulae. In particular is allows you to view what cells are being used by a particular formulae, what other cells are relying on the cell you have specified, follow links to other spreadsheets and more recent versions of Excel include some error checking capabilities.
Good Spreadsheet Design Techniques (15 min)
The are some basic aspects of spreadsheets which should be considered. When developing your spreadsheets some upfront thought can be vital. After you have viewed the free material on How Errors Happen, this will further enforce some good design tips. PLEASE NOTE THAT THIS IS A BIG FILE. GIVE IT SOME TIME TO DOWNLOAD
Loading the freely available Add Ins (2 min)
With a standard installation of Excel, you are provided with a number of add ins that you can activate. These add significant functionality to Excel and should be loaded.
Running Sensitivities and What If Analysis in Excel
Goalseek (3 min)
The Goalseek feature allows you to specify the result you require and which input can change. Then the computer will calculate the required number. Very useful for break even analysis. This clip also shows you how to perform a goalseek straight off a graph.
Data Tables (8 min)
Learn how to use Excel’s Data Tables feature to automatically run multiple scenarios through a model, based on either one or two variables.
Multiple Scenarios (4 min)
The scenario feature allows you to quickly change a number of input cells based on some pre defined situations e.g. Low case, High Case, Realistic Case. You can then switch between the various scenarios.
Watch Window (3 min)
The watch window allows you to continuously view cells that interest you (no matter where they are on the spreadsheet) while making changes to inputs in the model.
Lookup and Reference Excel Tutorials
These Excel tutorials will teach you how to use the lookup and reference functions, useful when using tables of information. For learning vlookup, see tutorials immediately below.
Transpose (3 min)
Two methods to quickly and easily move information from row format into a column format, or a column into a row. Excel Help File Description: Converts a vertical range of cells to a horizontal range, or vica versa.
CHOOSE (3 min)
The Choose function allows you to refer to different cells depending on the results of a cell. Often useful as an alternate to the IF function (especially if you are tempted to use nested IF functions). Excel Help File Description: Uses index num to return a value from the list of value arguments. Use CHOOSE to select one of up to 29 values based on the index number. For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index num.
OFFSET (3 min)
Offset allows you to refer to cells above, below, left or right of a reference cell. Very useful for cash flow forecasts where the receipt or payment of money can move between periods. Excel Help File Description: Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.
Using and Understanding Microsoft Excel’s VLOOKUP function
VLOOKUP Basics (7 min)
Learn how to use the VLOOKUP function in Microsoft Excel which allows you to request Excel to reference certain information and pull through some related information. This can either be done based on an exact match of the criteria or an approximate match. Excel Help File Description: Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default the table must be sorted in ascending order. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data you want to find. The V in VLOOKUP stands for “Vertical.”
Handling Large VLOOKUP tables (6 min)
Once you have mastered VLOOKUP you may find that you use it for large datasets. The problem is that when you make changes to your spreadsheet you sometimes need to go through all the VLOOKUP cells and change the column reference. Learn how to avoid this!
Typical Errors made when using VLOOKUP (5 min)
As useful as the VLOOKUP function is, there is a significant risk of errors. Learn what you must watch for.
Automatic Sorting of lists (5 min)
By combining the VLOOKUP and RANK functions you can have Excel automatically sort a list of data into a specific order.
Popularity: 22% [?]
Related Posts:
Tags for this post>> Excel