When creating ranges of formulas that you want to copy down, you sometimes have a trade off in the use of fixed and relative references. If you need to create a relative reference that acts like a fixed reference you can use a trick.
Formatted Tables are great but there is an issue when it comes to copying formula that use the table names (Structured References). There are two techniques that cope with this limitation.
When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
We’ve all heard the term “A month of Sundays” to describe a long time. Well what if you wanted to count how many Sundays between two dates?
If you are using date-based headings in your reporting models please consider using dates in the headings rather than text. I’ll explain why.
The NETWORKDAYS.INTL function was added in Excel 2010. It allows to calculate how may work days between two dates using non-standard weekends. Some countries don’t have Saturday/Sunday weekends.
I had a question during one of my Date and Times Webinars in February. It was about months and fortnights. I couldn’t answer it during the webinar, but I did follow up with an email with the solution. The answer follows.
Most people are unaware that the SUMIFS function has a serious limitation when it comes to codes with leading zeroes. This post shows you how to perform calculations involving codes with leading zeroes. This issue also affects SUMIF, COUNTIF and COUNTIFS.
I had a question on another post on how to convert Nov 21, 2014 into a date Excel recognises. The solution involves six functions working together.
If you need to convert a number into a text number within a formula there are a couple of ways to achieve this, but one way is a lot easier.
Let’s say you have a table of codes and every month there are a few you want to check out. You could use a VLOOKUP to extract all the details for each code, but let’s say you want to view the codes in the table.
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.