I am working on a series of blog posts about the various spreadsheets I use in my hobby crafting business. I found myself explaining the same spreadsheet topics more than once… so I figured it deserved its own post. 🙂

This will cover the basics of cells, ranges, functions, and formulas that I use in both Google Sheets and Microsoft Excel. If you have any questions, please let me know and I’ll do my best to answer them!

How Spreadsheets Name Things

Spreadsheets use cells, ranges, and tabs/sheets to organize their information. The formulas and functions look at these types of data to perform their calculations.

Cells

A cell is a single rectangle on the spreadsheet. It is the most common source of data that I will be using in the spreadsheets.

The spreadsheets designate the cell by its column (letter) and row (number). B2 would pull the value of the cell in column B, row 2.

Ranges

A range is a group of touching cells on a spreadsheet. It can either be a selection of cells or entire rows or columns.

The spreadsheets designate a range (or section) of the spreadsheet by listing the top leftmost cell, a colon, and the bottom rightmost cell : B2:D15

If the range contains entire columns (letters) or entire rows (numbers), it only includes that side: B:C or 2:3

Tabs/Sheets

Tabs are ways of dividing the data visually so it’s easier for humans to use. It’s 100% possible to do everything that I show you all on one tab… but there’s no reason to! 🙂

The formulas use the name of the tab and an exclamation point in front of the cell or range to signify that we will be looking to that tab for the information : Sheet2!B3 or Sheet3!B:E

You can make new tabs in Google Sheets by clicking on the plus in the bottom left of the screen.

You can make new tabs in Microsoft Excel by clicking on the icon with the star to the right of the existing tabs (or by right-clicking a tab and choosing ‘Insert’.)

Formulas

Formulas are where a spreadsheet does math. They are not the same thing as functions, which allow you do to more complex tasks.

If you type =2+3 into a cell and hit enter, the value in the cell will change to 5, but when you click on the cell it will show you the formula.

The power of spreadsheets comes from the ability to use cell names instead of numbers in these formulas. This allows you to pull numbers from anywhere, even other tabs of the spreadsheet!

Note: this only works with individual cells, if you try to use a formula in a function it will give you a #VALUE! error.

If you need to make changes to a formula, it will normally be to change the cell or range that the formula is looking at. A common problem is to have the right formula, but using the wrong cells.

Example: you are trying to check a value from row 5, but the formula is looking at a cell from row 3. To fix this, you would need to change the cell name to point to the right place.

=IF(F3="Y",(E5*0.9),E5) would change to =IF(F5="Y",(E5*0.9),E5)

Functions

A function is something the spreadsheet does for you, like checking to see if a cell is blank, or looking up a specific item in a list. I will be covering only the functions that I use, there are lots more to play with.

You can often call functions from within functions, so you can make some very fancy stuff! 🙂

SUM

This is a very basic function that returns the sum of the numbers within the range that you give it. You can either give it numbers from a single row or column or numbers from a range.

VLOOKUP

This is a function that finds information from a range. It needs four things to work: The value you want to match, the range of cells you are looking in, the column to return the value from, and if you want an exact match.

Almost every time I use vlookup, I use it to pull information from one tab to another. An example of this would look like:

=vlookup(B255,Materials!B:I,8,FALSE)

This example will take the value in cell B255 from the current tab and try to match it against a value in column B in tab Materials.

If it finds a match, it will return the value from the 8th column, counting from B  which is I (Cost Per).

The FALSE tells it that we want an exact match for the value, not an approximate one. (You will almost never want to pick TRUE here. Approximate matches are only useful in a very small set of situations.)

The range can be larger than the data you are looking for, so it could be B:Z and it wouldn’t hurt anything. If the range is smaller, like B:D, then it will return a #REF! error.

ISBLANK

This function checks to see if a cell is completely empty and returns TRUE or FALSE.

I use this so the calculations won’t cause errors if nothing has been entered. As you can see below, a space (or several spaces) doesn’t count as empty!

ISNA

This function checks to see if a cell has the error code #N/A that is returned when vlookup can’t find a match and returns TRUE or FALSE.

I use this so I can make errors look a little prettier.

IF

This function checks to see if a thing is true. If it’s true it will do one thing and if it’s false it does another. I use this to do all of the more complicated tasks!

=if(F2="Y",(E2*0.9),E2)

In the example above, the IF is checking F2=”Y”, which checks if there is a ‘Y’ in the 10% column. If there is, displays (E2*0.9), which is 90% of the Units value. If there isn’t it displays the whole E2 value.

I get pretty complicated with the IFs in the spreadsheet! Here is a more complex example from the product costing spreadsheet:

=if(ISBLANK(B255),"",if(ISNA(vlookup(B255,Materials!B:I,8,FALSE)),"Missing",vlookup(B255,Materials!B:I,8,FALSE)))

• This checks to see if the Material cell is blank for this row.
  • If it is, it shows a blank in the Cost Per cell.
  • If it isn’t then it tries to look up the material in column B of the 
    Materials tab.
    • If it gets an #N/A error from the vlookup, then is shows “Missing”in the cell.
    • If it finds the material, then it shows the value in column position 8 in the range B:I from the Materials tab for that item.

Whew!

ROUNDUP

This function will round up a given number to a given number of decimal places. Because I am working with currency, I will often round things up to the nearest penny as I calculate costs.

The example above shows what will happen if you roundup the number from 0 to 6 decimal places. In Sheets, you don’t need to specify if you want to round to 0 decimal places, but Excel will kick and error if you don’t.

You can also use negative decimal positions with roundup if you want to round to 10’s, or 100’s, etc.

Toubleshooting

Whenever I am writing a complicated formula that contains functions, I will break it out to make sure that each part is calculating correctly.

Using the complicated example above, I would have tested each of the following:

• ISBLANK(B255)
• if(ISBLANK(B255),”blank”,”not blank”)
• vlookup(B255,Materials!B:I,8,FALSE)
• ISNA(vlookup(B255,Materials!B:I,8,FALSE)
• if(ISNA(vlookup(B255,Materials!B:I,8,FALSE)),”Missing”,”Not Missing”)

Breaking it down like this can help you confirm what output you are getting from each step. Sometimes it’s something simple like the wrong column or swapping the True and False options on an IF.

Other Free Training & Tutorials

• Get started with Sheets – Google.com
• How to use Google Sheets: A Beginner’s Guide – Ben Collins
• Google Sheets 101: The Beginner’s Guide to Online Spreadsheets – zapier
• Excel for Windows training – Office.com
• Excel Courses – edX
• Excel 2016 Basics – GCFLearnFree.org
• The Beginner’s Guide to Microsoft Excel Online – zapier
• Excel Easy