You are here: Home : Spreadsheet Tips : All Spreadsheet Tips

2

The formulas should read (A2:D2) instead of (A2:C2). Sorry for the confusion, but an edit function would be nice. :)

Posted at 10:07 am ET on February 12, 2011

by Nicholas Cammarata

by Nicholas Cammarata

3

Regarding pivot tables, I recommend that you always select empty cells to show zeros. The following example will illustrate why:

First, consider the case where empty cells are left blank.

(A) (B) (C) (D)

(1) Jan Feb Mar Apr

(2) 24 (blank) (blank) 24

Using this pivot table, the formula =average(A2:C2) would return the value "24." It ignores the blank cells.

Now, let's contrast that with the case where empty cells contain zeros.

(A) (B) (C) (D)

(1) Jan Feb Mar Apr

(2) 24 0 0 24

Now, the formula =average(A2:C2) would return the value "12."

If the numbers above represent revenue $, for example, the lack of revenues in February and March should not be overlooked in calculations. Thus, I would expect calculations built on the latter settings to be the correct ones for most users.

Finally, this issue is not exclusive to the =average formula. Others, such as =min and =mode, may also be thrown off by blank cells in a pivot table.

Posted at 10:02 am ET on February 12, 2011

by Nicholas Cammarata

by Nicholas Cammarata

4

There are a few people who know about the function Subtotal(Function_num,Range), but even only a few of these know about how powerful it can be when adding columns in a table in conjunction with Data\Filter\Autofilter.

This is a tip worth sharing.

Posted at 11:43 am ET on January 13, 2011

by Alan Brown

by Alan Brown

5

Multiple Sumif formula

For multiple sum if, first you need to open brackets as follows: {=sum(if(....="x",if(...="y"..,range of values,0),0))}. Second, you need to type {} at the beginning and at the end of the formula by pressing Ctrl, Upper Case, Enter keys at the same time while the cursor in on the formula.

Posted at 12:47 pm ET on October 20, 2010

by Sameh Kirollos

by Sameh Kirollos

6

When creating a row index it is better to use the formula =row(). Then when a row is inserted, one does not have to recopy the formula A1+1, for example, for the entire list. The formula has to only be added to the inserted row.

Posted at 11:56 am ET on October 7, 2010

by Matthew Saxon

by Matthew Saxon

7

Here is an oldie but a goodie that helps when you are working in a heavy-formulated Excel file. To review and understand the formulas without going into each cell, hit ALT and the (~). This will display all live formulas in the sheet. Hit the key combination again to return to the number value.

Posted at 9:36 am ET on September 9, 2010

by Karlo Bustos

by Karlo Bustos

8

When creating a lookup table with VLOOKUP, index your rows - but don't use hardcoded sequential numbers (e.g. 1, 2, 3, 4, etc.), because this will cause problems if you later insert a row in the middle of the data set. Instead, put a "1" in the first row and then use the formula A1 + 1 and then copy that down the column so that the indexing is dynamic and automatically incorporates any added rows.

Posted at 3:41 pm ET on September 8, 2010

by George Austin

by George Austin

9

This is a short-lived tip for those who run into backward compatability problems with Office (as I do when trying to open spreadsheets from work on my older home computer). If you can't open a .xlsx file, one way to view and even work with the contents is to open it in Google spreadsheets. I would only do this for viewing and limited operations, though, not for making major changes. Google spreadsheets are still somewhat buggy.

Posted at 9:34 am ET on September 7, 2010

by Tim Reason

by Tim Reason

123

by Amanda Funderburke