Have you ever spent hours on a spreadsheet & thought “there has to be a better way”?

Then I’ve got bittersweet news. There probably was.

When I started using spreadsheets 6 years ago, I thought they were glorified Microsoft Word Tables. Except they were finicky and I could never get them to look right. ๐คท๐ปโโ๏ธ

Fact is, spreadsheets come with tons of powerful functionality. The problem is just that it all sits quietly behind the scenes. And you won’t reap the benefits unless you know how to use some of them.

Enter this post series, devoted specifically to **formulas**.

If you’ve never used a spreadsheet formula before, don’t worry! Formulas are much simpler to set up than they sound.

And these 6 in particular are gems. They basically automate the tasks that you and I would need to do manually every time – like capitalizing street names, removing extra spaces before names, and even calculating donation data.

No more individually backspacing spaces out of cells. Let’s dive in.

## First, if you’re new to formulas…

To use formulas, here’s what you need to know:

1. **All formulas begin with an equals sign (=). **This is true whether you’re using Excel or Google Sheets.

2. **Then you enter the formula and its reference.** The “reference”, in parentheses, refers to the cell/s you want the formula to run against (ex. A2, B2:B6). If you’re still confused about this, you’ll see what I mean in the screenshots below.

3. **Copy and paste the formula down a column** to quickly make changes.

**Spreadsheet Formulas, Part 1: **

6 Time-Savers

6 Time-Savers

**Formatting (easy โโโโ)**

### 1) PROPER

*changes all text to proper format*

*changes all text to proper format*

The PROPER function transforms text into proper format (where the first letter of each word is capitalized). Great anytime you need to fix data that contains street, company or person names!

p.s. You can also use the **UPPERCASE** and **LOWERCASE** formulas to transform text.

### 2) TRIM

**removes extra spaces from cells**

**removes extra spaces from cells**

The TRIM function removes extraneous spaces in cells. That includes spaces entered before, after, or in between your text.

In this example, the formula removed 2 spaces after the word “Maria”.

### 3) CONCAT

**combine multiple cell values in one**

**combine multiple cell values in one**

Use the CONCAT function to combine multiple cell entries into a single cell. Super helpful when you need to create a unique identifier for a row of data (because you’re building a database or need to de-dupe!)

You can also use **ampersands** to combine values. CONCAT is easier if you need to combine lots of cells at once. But ampersands are better when you need to include special characters in-between those cell values.

In the formula below, I added a space in between “Rosa” and “Cruz” by putting the space in between two quotation marks.

**Numerical (one step up โ****โ**โโ)

**โ**โโ)

### 4) COUNT

*counts the number of cells in a range with a (numeric) value*

*counts the number of cells in a range with a (numeric) value*

The COUNT function does what it says: it will count the number of cells in a range where there is a numerical value. (Hint: If you need to count the cells in a range where there are *text* values, use COUNTA instead. )

p.s. See that colon in between B2 and B4? This tells your file that you want to calculate every cell between B2 and B4 (including B3).

### 5) SUM

*s***ums the number of cells in a range**

**ums the number of cells in a range**

The SUM function adds all the values across a given range. Remember: your cells need to be in some sort of numerical format (not text) for this one to work!

### 6) AVERAGE

*calculates the mean of numbers in a range*

*calculates the mean of numbers in a range*

The AVERAGE function calculates the mean of all the values in a given range. Again, make sure your cells are in number format!

p.s. You can also use the **MEDIAN** and **MODE** formulas to calculate those respective values.

## Try them out for yourself!

Give these formulas a whirl. Then when you’re ready, move on to **part 2 of this series****.**

## Share your thoughts!