<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=158001101211973&amp;ev=PageView&amp;noscript=1">

5 Excel Formulas Every Church Worker Will Love

Jan 25, 2016 9:00:00 AM

5 Excel Formulas Every Church Worker Will Love

I have, as some people call it, an unhealthy obsession with Microsoft Excel. I get excited about anything that allows me to be more efficient. With Excel, I keep finding new and better ways to do just that.

While much of Excel revolves around numbers (all right, most of Excel revolves around numbers), there are some pretty fantastic formulas and functions that allow you to quickly manipulate text. So even if you are more focused on Bible studies and mail merges than income statements, Excel can help you.

The following are my favorite five formulas that have nothing to do with numbers. If you’re new to using formulas in Excel, check out Microsoft’s Excel help page.

1. CONCATENATE

I put this as #1 because it can be used in so many situations, especially once you export data into a single row CSV or XLSX document. CONCATENATE allows you to merge separate values together into a single cell. You can merge cells like contact values, or you can add your own text to the formula using double quotes.

To start this Excel formula, choose an empty cell. Type in an equal sign, then the formula word in all caps. After the word, type an open parenthesis with no space after the formula word. Separate values with commas, and close the parenthesis when you’ve come to the end. Keep in mind that you have to put spaces in the formula if you want them in the end result!

In the example below, I wanted to take each family name and turn it into The             Household (which is how I might address a mailing label). So Abbott becomes The Abbott Household with the formula =CONCATENATE("The ",A2," Household"). To continue using this formula on the other names, just click and drag the bottom right corner of the cell where you typed the formula (see below).

As another example, if I had first names in column A and last names in column B, my formula to combine them would be =CONCATENATE(A2," ",B2).

CONCATENATE.gif 

2. RIGHT / LEFT

These two position formulas are pretty straightforward. The RIGHT / LEFT formulas allow you to extract a certain number of characters from the right or left side of a cell. One way I often use it is to find the ZIP code of an address. Then I can sort or filter by the data in that column.

In the example below, I wanted to extract the last five characters of the cells in column A. The formula read =RIGHT(A2,5) with A2 being the field with data and 5 being the numbers of characters. To continue using this formula on the other addresses, I clicked and dragged the bottom right corner of the cell where I typed the formula.

RIGHT.gif

3. PROPER / UPPER / LOWER

Have you ever exported data and found that it exported in all caps, when you wanted proper case? What about the reverse? The PROPER / UPPER / LOWER formulas allow you to quickly and easily change the case of any text string.

The formula for these in incredibly easy, as the only thing to put between the parethesis is the cell with the data: =PROPER(A2).

PROPER.gif

4. TRIM

This one is easily missed due to how simple it is. If you have a text string with extra spaces in front of or after the text or numbers, the TRIM formula will remove the spaces at the beginning or end of the string.

Just like above, this formula is simple, with data cell being the only information between the parethesis: =TRIM(A2).

TRIM.gif

5. TEXT

Ok, this one is related to numbers a little bit. Excel comes with a variety of formats for things like date, time, or currency, and the formula TEXT allows you to set the format for the value. The example below shows you how easy it is to convert a date to full text.

To change the date form the MM/DD/YYYY format to the full text, I used this formula: =TEXT(A2,"MMMM DD, YYYY"). If I had changed the format to "MM DD YY", it would have shown the first date as May 16 74, the second as Oct 15 77, and so on. Microsoft has provided full list of TEXT function formats for you to review.

TEXT.gif


 

While there are plenty more options to choose from, I am going to limit myself to five options today. What are your favorite Excel formulas and how do you use them in the church?

Want to receive notifications about more content like this? Subscribe to our blog, Technology & Your Ministry!

Subscribe to the CTS Blog Technology & Your Ministry

 

Peter Frank

Written by Peter Frank

Peter Frank serves at Concordia Publishing House as a Senior Marketing Manager for Church Supplies, including Concordia Technology Solutions (CTS), the church management software division. A graduate of Concordia University Wisconsin, his background in theology, business, and computers gives him a unique perspective on technology in the church. Married and the father of two children, he is frequently humbled when his young children teach him something new about technology.

Lists by Topic

see all