Sometimes you receive an Excel report or an export from another system with a lot of empty cells in a category column:
If you want to analyze the data, either with a Pivot table or with formulas such as SUMIFS and VLOOKUP, you need to populate all the empty cells in the category (dimension) columns, in this example column A.
For example, if you want the Total Sales for Northeast, the formula =SUMIFS(E:E,A:A,”Northeast”) will only work if you have the right region in every cell in column A .
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.
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.
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: