SUM across multiple sheets in Excel

If you have a report in Excel with multiple identical sheets, for example one sheet per month, that you want to add together in a separate sheet, there are a few different ways to do it.

The most common way is to simply type an equal sign, click on B2 in the ‘Jan’ sheet, type a plus sign, click on B2 in the ‘Feb’ sheet, and so on. Then copy down and across. This takes a long time, and it’s easy to make mistakes when you write formulas like that.

A better way to do it is to use this formula:

READ MORE

Make SUMIFS and COUNTIFS in Excel more flexible with a Wildcard

How to use a wildcard

In the example below I have used the COUNTIFS function to count the number of members who fulfill the two criteria in the report, and the SUMIFS function to calculate the total amount paid if both criteria are met.

=COUNTIFS(B2:B11,G2,D2:D11,G3)
=SUMIFS(C2:C11,B2:B11,G2,D2:D11,G3)

READ MORE

The easiest way to reduce file size in Excel: Excel Binary Workbook

Sometimes an Excel document becomes too large to email or share via your filesharing service. Or it just takes forever to calculate. How can you speed up your spreadsheet or reduce the size of it?

Obviously, you should always look at the structure of the file and see if you can minimize the use of volatile functions such as OFFSET, INDIRECT, RAND, TODAY etc. The volatile functions recalculate every time you make a change in the workbook. It is also a good idea to be careful with Conditional Formatting, which is also a volatile feature.

But the easiest way to reduce the size and increase the speed of you file is to save it as an Excel Binary Workbook:

READ MORE

An easier way to calculate compound interest in Excel

Do you want to calculate how much your current savings might be worth in 10 years or in 30 years? It’s an easy calculation that doesn’t require any specific function in Excel. Some simple multiplication, addition and exponentiation is all you need.

Still, the answer you will most likely get if you search for “compound interest in Excel” on Google is the FV function. The FV function is difficult to use, and it actually calculates compound interest based on a monthly rate rather than a yearly rate, which gives a slightly different result.

READ MORE

How to find cells that contain a formula in Excel

It’s a good idea to check if there are actually formulas in all the cells where you expect them to be. It happens all the time that someone accidentally has overwritten a formula with a hard-coded value, and it can be difficult to spot errors like that. Fortunately, there is a very easy way to locate all the formulas in an Excel report: The Go To Special feature.

This is what a sales report might look like. We expect to find formulas in some of the columns, but it’s almost impossible to go through the report manually cell by cell:

READ MORE