The SUMPRODUCT function has been my favourite function for about 20 years. It is so flexible. Soon it will be redundant thanks to dynamic arrays.
When creating a formula sometimes you need to conditionally choose the column to perform a calculation on. The IF function can be used, but there is a trick to shorten the formula.
Formatted Tables allow you to create formulas that automatically copy down as the table expands. To create a running total in a column you have a couple of options.
Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
In a previous blog post I explained how to do a 3D SUM calculation in Excel that SUMs through sheets. You can view it here. I have seen a few posts in forums recently about performing a 3D SUMIF calculation. Whilst the SUM function can work with 3D ranges, the SUMIF function can’t.
Calculating YTD percentages in Excel isn’t always straightforward, but in some cases you can use Excel’s most flexible function to achieve the result you need.
The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.
Hiding rows and columns is a common process in spreadsheets. Excel’s grouping feature can make the process easier and make it more apparent when rows or columns have been hidden.
Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria.
The IF function is typically used to display one result or text when a condition is met and another if it is not met. There is another technique using the IF function that can reduce formula size in certain circumstances.
The SUM function is the function of choice to add up values. But when it comes to subtotalling, SUM leaves a lot to be desired. There is a specific function for subtotalling called, funnily enough, SUBTOTAL.
The SUM function can perform 3D sums. The method is useful in certain circumstances and is both efficient and flexible. See the example file link at the bottom of this blog post.
Do you know everything about Excel?
I’ve been using it for over 20 years! I’ve learned a lot in those years, but I’m still learning new things and this blog aims to share my knowledge. Of course new versions are always adding new features, so it can be hard to keep up.
I’m a CPA and this blog will discuss Excel topics that are directly applicable to Accountants and Finance staff. The content will vary from simple to complex; from functions to formatting; from Excel 2003 to Excel 2010.