MAXIF functions
Learn how to create a function in Excel that will allow you to find the maximum value with either one or two criteria.
Internal rate of return
Gain an understanding of the internal rate of return function in Excel to compare multiple cash flows over time and improve you efficiency in worksheets.
Net present value
This Excel formula tutorial will show you how to use the net present value function to analyse multiple cash flows over time and use the function to calculate regular and irregular intervals.
Switching first and last name
Get help with Excel formulas to find out how you can convert the order of a name in a cell. With this tutorial you will learn to easily swap from first name, last name to last name, first name.
TRUE and FALSE in validation checks
When you need to check if something balances you can use the Excel formula =A1=B1. This will display TRUE if A1 equals B1 or FALSE if they don’t balance. Read the full tutorial for more information.
Range name rules and recommendations
With this tutorial, learn how to use "range name rules" to simplify formula creation and help you understand and maintain your formulas.
Avoid #N/A errors
If you have used VLOOKUP or MATCH functions you may have encountered the #N/A error. This error means that the item being looked up or searched for, is not in the range involved in the function. There is an Excel formula that can help.
Working with dates
Excel views dates as numbers, this means you can do calculations using dates and number of days. This tutorial shows you how to use formulas to use to do this.
Text functions
Text functions allow you to extract text from longer text strings. The two easiest text functions to use are LEFT and RIGHT. These extract a number of characters from the left or right. This tutorial will show you how.
3D SUM
If you need to add up the same cell across multiple sheets you can use the SUM function to do it easily. Let’s say you need to add all the A4 cells in 12 sheets.
Converting a date to a week commencing date
It you like to summarise your data into weeks it can be handy to convert a date to a week commencing date. A helpful Excel formula makes this possible.
TRIM those spaces
Ever wondered why your VLOOKUP or SUMIF functions don't work with downloaded data? Read this Excel tutorial to find out how to get around this issue.
Week numbers
If you use week numbers and you need to know which week in the calendar year a specific date is in, you can use the WEEKNUM function.
Days passed in the financial year
As a follow up to the tip on calculating the number of days passed in the calendar year, this tutorial provides a formula to calculate the number of days passed in the 30 June financial year.
Days passed in a year
Ever needed to find how may days have passed in a year? For any date in A1 this Excel formula will tell you how many days have passed.
Adding months to a date
Do you need to increment dates by whole months? For example, do you need to add one month to 15/5/2009 to arrive at 15/6/2009? You could use the DATE function to do this, but there is an easier function, EDATE, that is designed for this task. The Excel formulas you need for this are in the tutorial.
Phantom links
Have you ever opened an Excel file and had the question pop up: Do you want to update links? You say: There shouldn't be any links to other sheets in the workbook. Help is at hand with an Excel formula.
See range names
If you frequently use range names, then seeing where they are on the sheet can be helpful. Use the Excel tutorial to learn how to do this.
Extracting characters
If you want to extract all but the last two characters, you can do this by using two text functions. The function in this Excel tutorial will work on any length text string. It is extracting the text from cell A1.
Using functions for the first time
When you first start to use a new function it can take a while to get used to the syntax (layout) of the function. This Excel tutorial will make using a new formula easier.
Fixing trailing minus signs
Some systems download negative values into Excel with the minus sign on the right of the number instead of the left – learn which Excel formulas can help.
Formula auditing
If you need to analyse formula and track the links to and from a cell, a good tip is to use Excel’s Formula Auditing Toolbar. Use this Excel tutorial to find out how to do this.
Working with working days
If you do calculations to work out completion dates with start dates and working days you should check out the WORKDAY function, another useful Excel formula, which ignores weekends and can also ignore public holidays if you use a list of holiday dates in the function.
Linking content
Text boxes are useful because you can easily place them anywhere on your spreadsheet. You can link the contents of a text box to a cell.
Using dates in headings
Excel stores dates as numbers and when you try to use a date in a heading with the "&" symbol Excel will display the number, not the date. The Excel formula for this is explained in the tutorial.
Formula bar extended
A feature was added to Excel 2007 to allow you to change the width of the name box to display long-range names.
Calculate part of a formula
Sometimes when creating a spreadsheet you need to see where errors are coming from. In longer formula this can be difficult, however, one technique can make it easier.
Name box extension
New in Excel 2007 is a feature which allows you to change the width of the name box to display long-range names.
ABS
No, Excel doesn't have a safe breaking system like your car – but it does have a function that allows you to remove negatives from numbers easily. Learn about the formula in this Excel tutorial.
Use the N() function to add a note to a formula
Sometimes a formula can get quite complex, and it can be worthwhile inserting a note into the formula to explain and document it.
