I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
The LEFT and RIGHT functions are great for extracting leading or trailing characters from a text string. Did you know their default setting is handy too?
I was looking at a calendar and noticed it used alternately shaded cells, like a checkerboard, for all the dates and thought Excel could do that.
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.
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.
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.
Here is the problem – you have a list of values which includes sub-totals calculated using SUM functions. You only want add up the values and ignore the SUM function cells.
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible?
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.
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.
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.
Excel doesn’t have a MINIF or a MAXIF function and many advanced users create an array formula to provide that functionality. If you have Excel 2010 or later there is a non-array solution.