Bonding thru words

Excel

Excel

 

Excel’s filtering capabilities and its formulas have often reduced to a great extent my workload. Here’s a few examples (NB: please note Excel formulas have been localized and vary from one lang to another). You can find a spreadsheet with a few examples on the Freebie tab.

Data>Filter>Autofilter: this will activate filters in Excel. You can filter in any column by existing values, blank or non blank cells or create your custom filters (contains, does not contain, equals, does not equal).

LEN: measures the number of characters in a string (e.g. =LEN(B1) will return the length of the string in B1). Handy for translations with space limitations.

IF: runs a logical test and returns TRUE or FALSE (or other custom values) depending on the result (e.g. =IF(LEN(B1)>LEN(A1), ok, error) will check if translation is longer than source and then return an error.

OR: allows to run several logical tests at the same time. If any of the tests is true, it will return the value TRUE; otherwise, it will return FALSE. E.g. OR(A1=””, B1″”) will return TRUE if any of the two conditions is right.

COUNTIF: will count 1 or more if a given condition is met, or 0 otherwise (e.g. COUNTIF=B1:B100, A1 will count how many times the value in A1 is present in range B1 to B100). Very useful when working with a large number of files to spot the ones missing.

VLOOKUP: will search the first column of a range of cells, and then return a value from any cell on the same row of the range. It can be useful for comparing or aligning two lists of source and translations.