PlumberSurplus.com Ecommerce and Entrepreneurship Blog | About | Contact | PlumberSurplus.com Store

Five Spreadsheet Formulas You Should Know and Use

Posted on July 2, 2009 by Archives

I stare at a spreadsheets most every hour at work, so there are a lot of spread sheet manipulations that take place on a daily basis. Here are five formulas that I find useful and I think you will too.

VLOOKUP()
Microsoft Office: VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Open Office: VLOOKUP(lookupvalue; datatable; columnindex; mode)
VLOOKUP() is used to find the “lookup value” in a given table of values and returns the column identified by the “column index.” This formula I find invaluable, it is used any time there is multiple data sources and I need to match up data.

CONCATENATE()
Microsoft Office: CONCATENATE (text1,text2,...)
Open Office: CONCATENATE(text1; text2; ... text30)
CONCATENATE() combines the values of the given cells. I use this often to combine data to create product names according to our format. As a hint, to concatenate a space, put the space in quotes (“ “).

LEFT()/RIGHT()
Microsoft Office: LEFT(text,num_chars)
Open Office: RIGHT(text; number)
RIGHT() and LEFT() work similar to a sub string function in programming. The formulas start at the right or left side of a cell and then grab the number of characters specified in the formula, which can adjust the data to your specifications.

SEARCH()
Microsoft Office: SEARCH(find_text,within_text,start_num)
Open Office: SEARCH(findtext; texttosearch; startposition)
SEARCH() locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string. SEARCH() can be very helpful when used in conjunction with LEFT() or RIGHT() when you want to get the contents of a cell to a certain character.

Text to Columns
Ok this one is not really a formula but it is a very useful tool. It allows you to break one column in to multiple columns by a common delimiter that you choose. Also you can choose “Fixed Width” and break up the column by width.

Differences between Open Office and Microsoft Office

For the most part the way that formulas work in Open Office and Microsoft Office work the same. There is a syntax difference in Open Office, instead of using commas (,) Open Office uses semi-colons (;) to separate the parts of the formula.

For additional information see the Open Office Formula Reference, and the Microsoft Office Formula Reference.

 

blog comments powered by Disqus