Spreadsheet tip 2: Very basic formulas

In a previous post, I mentioned how easy it is to combine data from different cells in a spreadsheet. If you can master three types of punctuation, you can master this. It’s that easy.

Let’s say you have a spreadsheet that looks something like this…

You can combine the contents of different cells to arrange the information however you need it or have the contents of one cell display in many places so you only have to update it once.*

Formulas

All the cool spreadsheet magic starts with a formula, which can be big and nasty and complicated but can also be very simple. Start by typing an equals sign (=) and then the formula you want to have calculated. It can be a mathematical formula or it can be a reference to the contents of another cell, which is identified by the cell’s column and row. For example, the cell in first row of column A is cell A1.

You can combine the two forms, so you could multiply a cell by another cell, for example. You can even refer to cells on other worksheets by referring to the worksheet in single quotes (’) followed by an exclamation point (!) and a cell reference.

Concatenation

The ampersand (&) character lets you concatenate (i.e. join) the contents of two cells together.

By itself, that isn’t necessarily useful because the cell contents are stuck together without any space between them, but in conjunction with literal text, you can do some very cool things.

Literals

You can put text inside quotation marks (") to display that exact text. You can concatenate cells with literal text to organize information in as many ways as you want. Practically speaking, this means you can have one range of cells or worksheet to hold your data and other worksheets for different arrangements of that data.

It looks worse than it really is. The first example above might look intimidating but it’s really fairly simple: the contents of cell A2, space, the contents of cell B2, space and open parenthesis, the contents of cell C2, close parenthesis. It’s really just about organizing information.

Creating useful, flexible lists and reports takes a bit more thought, but just about everything else you need to do in a simple list of data is an application of these basic spreadsheet skills.

Three kinds of punctuation. That’s it. Give it a shot.

1. * You can do math too — the original purpose of spreadsheets — but I hear a lot of PR pros don’t like math. [↩]