When working in Excel you may need to calculate a future date. This may be for example, when somebody’s work probation period ends, or the date a rented car should be returned.
In this article we explore a few different techniques for calculating the future date. These include how to exclude certain dates such as weekends and holidays.
Tuesday, 17 June 2014
Saturday, 7 June 2014
Return the Cell Reference instead of the Value from a Lookup
In Excel, Lookup functions such as Vlookup or Index and Match can be used to find a record and return a value. But what if you want to return the cell reference, or address, instead of the value.
In this example we will look for a Customer using its ID and return the address of the cell that contains the customer’s city.
In this example we will look for a Customer using its ID and return the address of the cell that contains the customer’s city.
Monday, 2 June 2014
Fixing Formula Errors
Working with formulas is the building block of creating powerful and dynamic Excel spreadsheets. However when there is a problem, it is not always that easy to diagnose what the problem with your formulas is.
When trying to trace the cause of a formula error, the Trace Precedents and Trace Dependents buttons will illustrate the cells that the formula is dependent on (precedents), and those affected by the formula result (dependents).
Displaying Formulas
By default Excel displays the value (or formula result) in a cell and the formula in the Formula Bar. This means that you need to select a cell to view the underlying formula.
If you are trying to diagnose a large formula that references other cells (which may also contain formulas), this can get irritating.
When a formula is quite large you may have trouble locating where the error is occurring.
Good news is that Excel will show you the calculation steps of the formula. This means that it will run the formula and stop at the point just prior to the error occurring. It even underlines the problem part.
This example highlights that the issue was that a cell contained text. A basic example but it demonstrated using these features to find the fault.
Fortunately there a few tools provided with Excel to assist you in fixing formula errors on your spreadsheet.
Formula Error Messages
When an error occurs you will be shown an error message.
Formula error messages begin with a number sign (#) and are then followed by some text that gives an indication as to the type of problem you encountered.
Understanding these error messages can help you solve your formula problems.
Some common formula error messages include;
- #NAME? – Excel does not recognise the text you entered as a reference to a range, table or function.
- #REF! – Excel cannot find the cell that you referenced within the formula.
- #VALUE! – Normally a formatting issue. For example, a cell your formula is referencing may contain text instead of a number.
- #DIV/0! – Occurs when you try and divide by 0 or an empty cell.
- #NUM! – Normally occurs when you try and pass invalid data to an argument like text rather than a number.
- ######## - Either the number is too large for the cell that is displaying it, or it contains a date before 01/01/1900.
Tracing Precedents and Dependents
When trying to trace the cause of a formula error, the Trace Precedents and Trace Dependents buttons will illustrate the cells that the formula is dependent on (precedents), and those affected by the formula result (dependents).
These buttons can be found on the Formulas tab of the Ribbon.
The image below shows the Trace Precedents button identifying the cells that the formula in B17 is dependent upon. It highlights that the issue is caused by the contents of cell B5.
Displaying Formulas
By default Excel displays the value (or formula result) in a cell and the formula in the Formula Bar. This means that you need to select a cell to view the underlying formula.
Fortunately you can toggle the display of formulas on and off. Making them visible in the cell can make them easier to troubleshoot.
Click the Formulas tab on the Ribbon, and then Show Formulas in the Formula Auditing group.
Show Calculation Steps and Error Checking
When a formula is quite large you may have trouble locating where the error is occurring.
Click the smart tag icon next to the cell containing the error, and select Show Calculation Steps from the menu.
Excel steps through the formula, displaying the values used and stops when it cannot go any further without producing the error.
This example highlights that the issue was that a cell contained text. A basic example but it demonstrated using these features to find the fault.
Let’s look at a stronger example. There is an error in the formula below.
Click the Error Checking button on the Formulas tab. This provides an explanation of the suspected problem. It mentions that a cell may have moved, or that a function is returning a reference error.
Click the Show Calculation Steps button to step into the formula. The first Vlookup function is underlined highlighting the problem function.
You now have the source of the problem from a formula that used 4 different functions. Click the Evaluate button to see the error occur.
The actual problem with this formula is that we have asked to return data from column 11, but the table array for the Vlookup is only 10 columns wide.
Boost your Excel formula skills to superhero status, sign up for our Excel Superhero course.More Tips for Troubleshooting Formula Errors
Saturday, 3 May 2014
5 Awesome Excel 2013 Flash Fill Examples
Flash Fill is one of the biggest new features to be released with Excel 2013. In fact let me re-phrase that. Flash Fill is one of the biggest new features to ever be released with Excel.
It can do so much with your data, the first time I saw its true power it nearly knocked me off my chair.
It can be used to extract text and values from a cell, concatenate data, change its case and even reverse data.
How to use Flash Fill in Excel 2013
Probably the best thing about this powerful new tool is how easy it is to use.
To use Flash Fill;
- Click in a cell and type in an example of how you want your data to look.
- Press the Ctrl + E keys, or select the range of cells including the example and press the Flash Fill button on the Data tab.
Tuesday, 22 April 2014
Understanding Section Breaks in Microsoft Word
Section breaks are one of the most useful features of Microsoft Word, especially when working with long documents.
They enable you to apply page formatting to parts of a page, or to specific pages of a document.
This article will look at two awesome uses of section breaks in Microsoft Word.
First we look at using them to insert a landscape page in the middle of a portrait orientated document. We then look at using them to start page numbering in a document from a specific page.
They enable you to apply page formatting to parts of a page, or to specific pages of a document.
This article will look at two awesome uses of section breaks in Microsoft Word.
First we look at using them to insert a landscape page in the middle of a portrait orientated document. We then look at using them to start page numbering in a document from a specific page.
Subscribe to:
Posts (Atom)






