Sometimes data that comes into Excel with code numbers formatted as text. This can stop VLOOKUP functions from working and return the dreaded #N/A error. With a couple of tweaks you can lookup both real numbers and text numbers in the one formula.
The TRANSPOSE function is one of only a few functions that must be entered as an the array using keyboard entry Ctrl + Shift + Enter (CSE). It allows you to switch a range from going across the sheet, to go down the sheet and vice versa.
Always Refer to Cell A1
If you need to ALWAYS refer to cell A1, regardless of whether row or columns are inserted or deleted, then use the following formula.
This will always display the entry in cell A1 on the current sheet.
Another formula that always refers to cell A1 on the current sheet is
Excel has a few issues with dates. Mainly they relate to the dates that are imported into Excel. Different systems present different problems. This post solves an issue with dates that include times.
Most people think that the IF function has to return a result. This leads to doing whole calculations in the true and false sections of the IF function. There is a way to create shorter functions.
If you need a formula to identify the last used cell in a column you don’t have to use an array formula. The AGGREGATE function can calculate it for you.
Days in Month Formula
If you need to calculate how many days in a month, you can use two functions together.
Assuming cell A1 has a date within the month, you can use
Hyperlinks are a great way to navigate around large spreadsheets. Unfortunately they each take a few clicks to create and can be easily broken. You can use a function to easily create multiple, flexible hyperlinks.
The ROUND function rounds values to decimal places on either side of the decimal point. It is useful and popular. The MROUND function is meant to allow you more flexibility in your rounding calculations. Let’s say you want round to closest 0.05. The MROUND is meant to handle this calculation but unfortunately it provides inconsistent results.
Excel has had a week number function for many versions, but Excel 2013 added a new week number function that complies with the ISO 8601 standard week number.
When working with loans or leases, it is common to have to add a number of years to a start date to determine the end date. An Excel function can automate that process.
Do you remember factorials in maths? That’s where you have, say four numbers, and you want to find out how many four number combinations you can make with those four numbers. In Maths you write it as 4!. The answer is 4 x 3 x 2 x 1 = 24.
If you have two lists of numbers and you need to ensure they are identical there is a simple formula that can confirm they match.
Let’s say you have a list of values that has a set sequence. In the list which starts in row 2 you want to add up every second entry. So you want to add up the entries in row 3, row 5, row 7 etc going down the sheet. The SUMPRODUCT function to the rescue yet again.
If you want to place ordinals (st, nd, rd and th) after a number in a date. You can use the CHOOSE function to do this.
In Excel dates are stored as numbers.
To generate random dates you can use the RANDBETWEEN function with a start date and an end date.
Cell C2 has been formatted as a date.
This formula is dynamic. Each time Excel calculates it will update and most likely change.
You can use Copy > Paste Values to capture the date(s).
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
Counting Errors in a Range in Excel
If you need to ensure that a range has no errors you can count the errors and compare the result to zero to ensure the range is error free.
The formula in cell C1 which counts errors in a range is
If you want to display TRUE for no errors and FALSE for error(s) you can use
You can also use an IF function to display the text Error if any errors are found.
When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task.
Removing Line Feeds in Excel
Sometimes when text data is imported from other systems it can contain line feeds.
A line feed wraps the text onto a new line – it is not associated with the Wrap Text format.
Cell A1 below shows an example. Cell B1 has the formula that removes line feeds ans replaces it with a space.
The formula in B1 is