3

Is there a significant difference between referencing a single cell versus the entire column when using certain excel functions? For example, using a function for an entire column like TRIM(A:A) vs TRIM(A1), TRIM(A2)... etc.

They both have the same output and I recently lost points on an excel assignment for school for not having the correct syntax in the function.

3

When you refer to one cell only, then Excel performs the function on that one cell only.

When you refer to multiple cells (entire column, or any arbitrary range), you tell Excel to perform the operation on the whole range referenced.
Although Excel is clever enough and sometimes can correctly guess that it needs to consider only one cell, you need to be aware what is the correct way of referencing.

E.g. in below example both ROUND and SUM are applied on both entire column and one cell. Results are the same for ROUND, but you see it makes a difference for SUM.

enter image description here

0

When the formula is =Trim(A1), Excel operates only on cell A1 and removes the extra spaces only from cell A1.

But when your formula is =Trim(A:A), Excel operates on the entire column A.

In case of a formula like =Trim(A1:A10) or {=Trim(A1:B10)}, Excel considers only the specified data range A1 to A10 or A1 to B10.

Note: When you apply TRIM() to more than one column, then it must be entered as an array formula. Otherwise you will get a #VALUE error.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.