I’ve said it before and I’ll say it again — there is a ton of power residing in those little blank cells you see in every single spreadsheet. They might not look like much on their own, but when you understand how to work with spreadsheets, you’ll be amazed at what they can do.
The key is knowing how to harness that power, which starts with learning the ins and outs of functions and formatting.
Prefer to listen instead of read? Scroll to the bottom for the audio version of this post!
Now, before we dive into this, let me just say that I get it.
Functions, formatting, and all that jazz within a spreadsheet — it’s amazing when it works, but when it doesn’t work? Well, there can be few things as stressful or frustrating as getting a value error and not being able to figure out what on earth you did wrong.
Today, we’re going to start small and simple and cover some of the most basic ways to format your spreadsheets, as well as a few simple functions and formulas to get you started!
BASIC SPREADSHEET FORMATTING
One of the great things about a spreadsheet is that they aren’t just for numbers. Sure, you can use them to add and multiply and manipulate data all you want, but you can also use them for things that require a whole lot of words, just delivered in a slightly different format than a text document allows for.
Learning how to format your cells and sheets is what takes that text from being a whole lot of words in a whole lot of little white cells to something that’s intelligible and, dare I say it, pretty!
First things first, in the same way you can manipulate the actual text in pretty much any other program — Word, Google Docs, email, etc. — you can also manipulate and format your text in a spreadsheet.
In every spreadsheet program you have the option to bold, italicize, underline, and strikethrough your text. You can change your font, font size, and font color, align it to the left, right, or center of a cell, and vertically align it to the top, middle, or bottom of a cell.
You can also wrap the text so it moves down to a new row when it reaches the edge of the cell, or you can let the text go on infinitely. If there’s nothing in the cell next to it, the text will bleed over.
If you want to get fancy, you can even tilt your text at a downward or upward angle.
All of these options are available in the navigation bar you’ll find at the top of every spreadsheet, just like you would find it in a Word Document or your email editor.
If you’re adding numbers into your spreadsheet throughout the whole thing or just a few rows or columns, number formatting can be your best friend!
You can set up individual cells or entire rows and columns to automatically format your numbers into percentages, dates, accounting, currency, time, duration, and more. There are default number formats and you can even customize them too!
In addition to formatting the text and numbers you’ll input into cells, there’s a lot you can do to the cells themselves.
First, you have the option to change the height of an entire row, as well as the width of an individual column. If you want to resize multiple rows, or columns to the same height or width at the same time, you can select all the ones you want to adjust (whether they’re right next to each other or not!) and right click to resize the selected columns or rows.
Beyond height and width, you can automatically set your cells to alternate colors (typically white and gray) or highlight individual cells whatever color you want!
Pro Tip: You can add custom colors into your text or highlight options, which is a great way to add your own branding colors into your spreadsheets!
For more definition in individual cells or groups of them, you can add borders (also in whatever color you want) to the outer edges, inner edges, top, bottom, sides… wherever you want!
Finally, if you need some cells to merge together nicely, no problem! You can merge as many cells as you want across rows or columns, so long as they’re consecutive!
BASIC SPREADSHEET FORMULAS & FUNCTIONS
Now that we’ve covered some of your basics for formatting and making your spreadsheets oh so pretty, it’s time to dive into what really give spreadsheets their power… formulas and functions!
Before we get into specifics, it’s important to define terms here.
A formula is an expression that will operate on values in a cell or a range of cells. A function performs a predefined formula.
What this means is a formula will take values (like numbers) and do something with them — add, subtract, divide, etc. — based on what you put into the formula.
A function takes some of that manual labor out of it and allows you to basically use shorthand to get the same result from a cell or range of cells!
Still a little confused?
A formula might look like this: =A1+A2+A3. The cell you put that formula into (let’s say A4) is going to give you the sum of cells A1, A2, and A3.
A function would look like this: =SUM(A1:A3). That function will give you the exact same result as the formula above and will actually save a lot of time! Why? Manually typing in three cells isn’t that big of a deal, but what if you’re summing up 50 cells? Or cells from two different columns or rows?
If you’re using a formula, you’d be typing in every single one of those individual cells, but if you use the function, it takes all that legwork off your plate!
So what are some of your basic spreadsheet functions?
This one should be fairly obvious, but the SUM function is going to take care of any addition for you! You can add consecutive cells from the same row, same column, or groups of cells from rows and columns.
If you’re adding lots of individual cells spread throughout the sheet, or groups of cells that aren’t in consecutive columns or rows, then you will need to employ the use of formulas to help things along a bit!
To use the SUM function, type =SUM in your function bar.
On the flip side of the SUM function is the MINUS function, which will take care of any subtraction for you. Unlike the SUM function, however, the MINUS function will only work between two individual cells or numbers.
To use the MINUS function, type =MINUS in your function bar.
The AVERAGE function will add any number of cells together and divide by the total number of cells to give you the average. So, for example, if you add 5 cells together, each with a value of 5, then the average will be 5.
This function is great when you’re mapping out financial goals and the like and attempting to figure out the average you need to bring in from a particular kind of service or on a month-to-month basis!
To use the AVERAGE function, type =AVERAGE in your function bar.
The COUNT function will count the number of cells, as opposed to the values within them. This means if you select 10 cells, the function will give you a result of 10. If you select 15, it will give the result of 15, and so on.
To use the COUNT function, type =COUNT in your function bar.
MAX & MIN
To round out your basic spreadsheet functions, we have MAX and MIN. As you might be able to guess, these functions will give you either the maximum number in a given dataset or the minimum number in a given dataset.
For example, if you have three cells containing values of 15, 22, and 17, the MAX function will give you a result of 22 and the MIN function will give you a result of 15.
To use the MAX function, type =MAX in your function bar and to use the MIN function, type =MIN in your function bar.
There you have it, friend! Some of your basic options for formatting your spreadsheets and getting started with functions and formulas. Happy spreadsheeting!