User FAQs

If you have a question, please send it to us.

1) What is the quickest way to add up a column of numbers ?
*) AutoSum - clicking on this command will add the SUM worksheet function below the current region
*) SUM - using this worksheet function manually.
*) AutoCalculate - highlight the cells and look in the status bar for the quick total.

2) What is the difference between the COUNT function and the COUNTA function ?
COUNT - Returns the number of non blank cells in a cell range.
COUNTA - Returns the number of non blank cells in a cell range, including logical values and text.

3) Using the NOW function inserts the current date and time. Is there a way to enter this information so it does not automatically update ?
Yes. Use these shortcut keys instead:
(Ctrl + ;) - inserts the current date
(Ctrl + Shift + ;) - inserts the current time

4) What is the difference between the SUBSTITUTE function and the REPLACE function ?
SUBSTITUTE - returns the text string with a substring substituted for another substring.
REPLACE - this should be used when you want to replace text that occurs in a specific location in a text string.

SUBSTITUTE(text, old_text, new_text [,instance_num]) 
REPLACE(old_text, start_num, num_chars, new_text) 

5) What is the difference between the SEARCH function and the FIND function ?
SEARCH - returns the starting position of a substring within a larger text string (not case sensitive).
FIND - returns the position of a substring within a larger text string (case sensitive).

SEARCH(find_text, within_text [,start_num]) 
FIND(find_text, within_text [,start_num]) 

The SEARCH function is not case sensitive and supports wildcards. ("aa" = "AA")
The FIND function is case sensitive and does not support wildcards. ("aa" <> "AA")

6) Are there any built-in worksheet functions that are volatile ?
Yes, for example: INDIRECT, NOW, OFFSET, RAND, RANDARRAY, RANDBETWEEN, TODAY
CELL and INFO are also volatile but it depends on their arguments.

7) How would you sum all the values in column A where column B is greater than 15 ?

{=SUM(A1:A10*(B1:B10>=15))} 
=SUMIF(B1:B10,">=15",A1:A10) 
=SUMIFS() 

8) When would you use the VLOOKUP function ?
When you want to match a value in the first column of a table and return a specific item in that row.

9) What are the four arguments passed to the VLOOKUP function ?

VLOOKUP(lookup_value, table_array, colum_index [,range_lookup]) 
lookup value - the value to be found in the first column 
table_array - the cell range containing the table of data 
column_index - the column number for the value you want returned 
range_lookup - (optional) whether to find an exact match 

10) Can you describe some of the common problems that people experience when using the VLOOKUP function ?
This function defaults to approximate match (not exact match)
The value you are looking up must be in the first column of your data table.
The column index number will be broken is you insert or delete columns in your data table.
Forgetting to make the table_array absolute references (using $) before dragging down a formula.

11) What function would you use if you want to match a value in the first row of a table and return a specific item in that column ?
HLOOKUP - returns the value in a row that matches a value in the top row of a table.

12) When would you use a combination of the INDEX and MATCH functions ?
If you wanted to lookup a value that was not in the first column.

INDEX('col_return', MATCH('match_item', 'col_data',0), 1)  

13) When would you use a combination of the OFFSET and MATCH functions ?
You should try not to use this combination because OFFSET is a volatile function and using it is very inefficient.
Always use INDEX and MATCH instead.

OFFSET('col_header', MATCH('col_data','table',0) -1, col_offset, 1, 1) 

14) Should I use the SUMIF function ?
No. In Excel 2007 this function was replaced with the SUMIFS function.
You should replace this function, remembering to change the order of the arguments.

SUMIF(range, criteria, sum_range) 
SUMIFS(sum_range, range, criteria) 

15) Should I use the AVERAGEIF function ?
No. In Excel 2007 this function was replaced with the AVERAGEIFS function.
You should replace this function, remembering to change the order of the arguments.

AVERAGEIF(range, criteria, sum_range) 
AVERAGEIFS(sum_range, range, criteria) 

16) Should I use the COUNTIF function ?
No. In Excel 2007 this function was replaced with the COUNTIFS function.
You should replace this function, remembering to change the order of the arguments.

COUNTIF(range, criteria, sum_range) 
COUNTIFS(sum_range, range, criteria) 

17) Be careful when you use the ROUND function.
The sum of the rounded numbers does not always equal the rounded sum of the unrounded numbers.