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
Formatting (easy ●○○○)
1) PROPER
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
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
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
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
sums 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
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!