# The End of SUMPRODUCT

### Dynamic arrays will carry it off in to the sunset

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.

# Conditionally Selecting a Column in Excel

### The IF function can return a range

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.

# Running Total in a Formatted Table

### Using the SUM function

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.

# Status Bar Calculations in Excel

### Quick and easy calculations

Excel has had an AutoCalculation feature for many versions. This means you can see the result of common functions without typing a single formula.

# Summing Coloured Cells in Excel

### A SUBTOTAL trick worth learning

In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.

# Excel 3D SUMIF

### You can achieve it

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.

# Handling Text in Calculation Cells in Excel

If you need to do mathematical calculations on cells that may contain numbers or text, then you can use the IFERROR function to ignore the text.

# YTD Percentages in Excel

### Getting percentages right

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.

# FORMULATEXT Function in Excel 2013

### New function - great for training

The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.

# Quickly and easily hiding and unhiding rows and columns in Excel

### Excel's Grouping technique

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 in Excel functions

### Handling missing characters

Using wildcard characters allows you to create flexible calculations. When used with the SUMIF and SUMIFS functions you can include quite complex criteria.

# Using Excel’s IF function differently

### A Shorter IF

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.

# SUBTOTAL Function- better than SUM

### Summing visible cells only

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.

# 3D SUM – SUM through spreadsheets

### No glasses required

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.

# Welcome to the Excel Yourself Blog

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.