Use a Spreadsheet and Statistics to Better Understand Your Data

If you have ever studied Statistics, you may recall the importance of summarizing data and looking at how values are distributed. This tutorial covers a handful of spreadsheet formulas that will allow you to perform basic Statistics functions. Along the way, I use real-life numbers and address why the formulas and functions used are meaningful.

Continue reading

How to Use Basic Math Functions in a Spreadsheet

This tutorial covers how to perform basic math operations and is aimed at beginning-level users of Excel and other similar spreadsheet programs such as LibreOffice Calc and Google Sheets.

Personally, I prefer using a spreadsheet over a calculator. The reason is simple. Spreadsheets allow me to scan for and correct typos much easier than do most calculators. This allows me to enter long series of numbers if needed.

Continue reading

The Mystery of Dollar Signs in your Spreadsheet, or How Do Cells Find Each Other?

Spreadsheets allow us to point to other cells when entering formulas. This is called a reference. A reference can point to a single cell (C1) or to a range of cells (C1:C10). References can use relative location (one cell down and two to the right) or absolute (C1). This post provides an overview and examples of these two methods for specifying cell location.

Cell references are relative references by default. That is, they identify other cells based on distance in both horizontal and vertical directions. This boils down to something like two cells to the right and one cell down from my current location.

The cell A1 has a formula that references the cell C2.

The cell A1 has a formula that references the cell C2.

Continue reading

Use Advanced Filter to get rid of duplicates in your spreadsheet

Having duplicate rows or entries in your spreadsheet can drive you up the wall. It could be that you need to clean up sloppy contact info from your address book, or maybe you need to consolidate several tables into one. There are a lot of possible scenarios where the Advanced Filter tool is the solution for getting rid of duplicates in your spreadsheet.

Advanced Filter

Here is a sample problem.

Each column has duplicate entries.

The first column shows row numbers. The second column shows names. The third column shows email addresses. The fourth column contains dates. The fifth column contains text.

  A B C D
1 Name Email Date Issue
2 Walter blysky@gmail.com 10/1/2012 Health Care Reform
3 Tyrion theimp@gmail.com 10/1/2012 Health Care Reform
4 Dan dandority@gmail.com 10/1/2012 Health Care Reform
5 Glenn go-to-town@gmail.com 10/1/2012 Health Care Reform
6 Tyrion theimp@gmail.com 9/24/2012 Foreclosures
7 Glenn go-to-town@gmail.com 9/24/2012 Foreclosures
8 Kalinda investigator@gmail.com 9/24/2012 Foreclosures
9 Walter blysky@gmail.com 9/24/2012 Foreclosures
10 Glenn go-to-town@gmail.com 9/17/2012 Schools
11 Dan dandority@gmail.com 9/17/2012 Schools
12 Tyrion theimp@gmail.com 9/17/2012 Schools
13 Tyrion theimp@gmail.com 9/10/2012 Immigration Reform
14 Tyrion theimp@gmail.com 9/3/2012 Fracking

Continue reading

Merging data in a spreadsheet with multiple columns

This post builds upon “How to merge data sets using an Excel or Calc spreadsheet.” A reader, Tina, asked “What if you have more than one column to lookup to get the match value?” This post tries to answer that question.

The key thing about merging data sets is that they MUST share at least one column in common. If this is not the case, then you will have to make it so!

Here is an example of two related data sets.

There is a list of names and street addresses on the left side of this table. On the right side is a list of names and phone numbers. The problem is that we can not merge the two lists, because the name-related columns are not the same.

The first column shows row numbers. The second column shows first names. The third column shows last names. The fourth column contains addresses. The fifth, sixth, and seventh columns are purposely left blank and represent the separation between the two data sets. The eighth column contains names. The ninth column contains phone numbers.

  A B C D E F G H
1 First Name Last Name Address       Name Phone
2 Sly Stone 123 Main St       George Clinton 555-555-6969
3 George Clinton 1 P-Funk Ln       Sly Stone 555-555-1212
4 Stevie Wonder 22 Talking Book Ln       Stevie Wonder 555-555-2323

Continue reading

How to merge data sets using an Excel or Calc spreadsheet

The scenario

Let us imagine two related data sets in Excel or LibreOffice’s Calc. One contains a list of names and corresponding phone numbers, and the other is a list of names and email addresses. The two data sets have a unique id in common (like a Social Security number). We want to merge the two data sets together into a single table. This is a common problem with an easy solution.

Here is an example of two related data sets.

More about this table

There is a list of names and phone numbers on the left side of this table. On the right side is a list of names and email addresses. Our goal is to merge the two lists into one.

The first column shows row numbers. The second column shows ID numbers. The third column shows telephone numbers. The fourth column shows names. The fifth and sixth columns are purposely left blank and represent the separation between the two data sets. The seventh column contains ID numbers. The eighth column contains names. The ninth column contains email addresses.

  A B C D E F G H
1 ID Tel Number Name     ID Name Email
2 01 555-1313 Dennis Osorio     06 Jesse Pinkman capncook@heisenberg.com
3 02 555-4242 Carrie Mathison     09 Tom Haverford tom@entertainment720.com
4 03 555-2323 Saul Berenson     05 Walter White blue@heisenberg.com
5 04 555-5353 King Joffrey     01 Dennis Osorio dposorio@fake.huh
6 05 555-3636 Walter White     10 Andy Dwyer just-the-tip@pawnee.gov
7 06 555-6464 Jesse Pinkman     07 Hank Schrader hank@thefuzz.gov
8 07 555-4747 Hank Schrader     02 Carrie Mathison carrie.mathison@the-cia.gov
9 08 555-7373 Saul Goodman     08 Saul Goodman saul@bettercallsaul.com
10 09 555-5858 Tom Haverford     02 Saul Berenson saul@notevenlefthanded.net
11 10 555-8484 Andy Dwyer     04 King Joffrey no-pain-no-gain@thelannisters.com

Continue reading