Step-by-step Spreadsheets for Absolute Beginners

Meet your spreadsheet! Start up a new spreadsheet, and let's have look around.

There are some important terms you need to know before we move forward:

See the grid on your spreadsheet? You probably noticed the letters along the top, and the number along the side. Those letters identify our vertical columns, and the number identify the horizontal rows. I'll be talking about columns and rows a lot in this course, so make sure you know which is which.

Those individual boxes in the grid are called cells. Each cell is identified by the name of the column and the name of the row. So a cell in column E and on row 5 is called "E5".

Pretty easy, right? In fact, it's just like playing BATTLESHIP!

One more detail...

Each spreadsheet workbook can actually contain more than one sheet. Typically, you can switch between sheets using tabs along the bottom left hand side. You can also use these tabs to add new sheets to your workbook.

BONUS: Check out this fun TV commercial from 1975 for the Milton Bradley game "Battleship": You Sank My Battleship!

Battleships (also known as Sea Battle or Battleship) is a strategic guessing game for two players. It's a game that dates from World War I. It was published by various companies as a pad-and-pencil game in the 1930s, and released as a plastic board game by Milton Bradley in 1967.

What does this have to do with spreadsheets? Because if you know how to play Battleships, you already know how to identify cells in a spreadsheet!

Download and play this pen-and-paper version of the game with a friend whenever you need a break from working on your spreadsheet. Think of it as "spreadsheet cell identification practice"!

Let's add some stuff to our spreadsheet!

Since we're creating a budget, let's put an appropriate title at the top of our sheet. We won't worry about formatting for the time being.

We'll need to list all of our EXPENSES as well as all of our INCOME, so let's add those headings too.

Next, let's do a brain dump of every expense and income item we can think of. (Don't worry about the numbers for now.)

Wondering how to insert a new row in a spreadsheet? Just right-click on the row number BELOW where you want to insert a row, and select the "insert row" option.

Watch the video to learn a handy trick to quickly resize the width of a column.

BONUS: Check out this video to find out what a theremin is!

Before you add any numbers to your spreadsheet, take a moment to think about how you might want to use this information in the future. This will help you build a better, more flexible spreadsheet.

In our case, we might want to have the freedom to adjust quantities and item costs, and have the spreadsheet do the calculations for us. So we'll create separate columns to contain all of this information, rather than just enter final totals.

So let's go ahead and enter the quantities and item costs for all of our expenses and income items. (Don't worry too much about accuracy - remember, this is a spreadsheet course, not a budgeting course! Go ahead and have some fun with your numbers.)

Watch the video to learn how to format individual numbers and columns of numbers to display as currency.

Let's put our spreadsheet to work now! I don't want to do any math in my head, and nor should we have to. That's what spreadsheets are for!

Computers are great at doing calculations - the trick is know how to tell the computer what to do. When you're using a spreadsheet, you need to enter a "=" symbol first in order to tell it you want it to do a calculation. (Also, don't use any spaces!)

Use the following symbols to write your calculation:

to add: +

to subtract: -

to multiply: *

to divide: /

Notice the difference between what shows up in the input line vs. what shows up in the cell. The input line will display the calculation instructions you've given to the spreadsheet, and the cell will display the results of that calculation.

Go ahead and practice!

Remember when we learned about how to identify cells? Those skills will come in handy now!

Now that we know all about calculations, we can write formulas.

Spreadsheet formulas are just calculations that use cell names instead of numbers. So when we type the following formula:

=B4*C4

...we're just telling the spreadsheet to take the number inside cell B4 and multiply it by C4. Magic!

Let's learn about some shortcuts for writing formulas.

You don't have to type out the name of a cell when you're writing a formula - instead, try clicking once on the cell you want. Boom!

Watch the video to learn an awesome trick for quickly copying the same formula across several rows.

Now that we know how to write a formula, let's learn about functions!

When adding up a column of numbers, you could write something like:

=B1+B2+B3+B4

...but that's not a very elegant solution. What if you had hundreds of rows to add up? Not very practical!

This is where functions come in handy.

Functions also start with the "=" symbol, and they have a name. Some common function names are SUM, MAX, and AVERAGE. The basic syntax for functions looks like this:

=NAME(????)

In the example above, "NAME" is just the name of the function. The ????? part might be a number, a cell name, a range of cells, or a series of values separated by commas.

HINT: Don't put any spaces before or after the NAME part!

A commonly used function is the SUM function. It just "sums up" all the numbers inside a range. So the following function:

=SUM(B1:B5)

...will add up ALL the numbers between B1 and B5 inclusive. Super handy, right?

In fact, the SUM function is so handy that most spreadsheet programs have a shortcut button for it. Look for a button with the Σ symbol on it (this letter is called "Sigma" - it's the eighteenth letter of the Greek alphabet).

Let's use our spreadsheet to identify problems and find solutions for us.

First of all - are we going to break even on our project? Let's find out!

We can use a formula to figure out our profit. Go ahead and write a formula that calculates INCOME minus EXPENSES.

Oh no! Our project doesn't seem to break even. We're going to have to adjust some numbers to make sure we don't lose money on this project.

Because we built a great spreadsheet, we can easily change some of the numbers to find ways to break even on our project.

So go ahead and change a few numbers, and watch how your entire spreadsheet updates automatically for you. Cool!

BONUS ROUND: Let's split the proceeds!

In this lecture, let's figure out how much money each band member will make if we split the proceeds evenly. How would you write this formula?

Watch the video to find out!

How do we choose the right file format to save our spreadsheet?

We have a few different options available to us, including the following:

Microsoft Excel 2007/2010 XML (*.xlsx) is one of the most commonly used formats for spreadsheets. This format will allow you to share your spreadsheet with anyone else using a recent version of Microsoft Excel.

Microsoft Excel 97/2000/XP/2003 (*.xls) is another commonly used spreadsheet format. This is an older version of the Excel format. Using this format will allow you to share your spreadsheet with someone who's using an older version of Microsoft Excel.

ODF Spreadsheet (*.ods) is the default spreadsheet format for open-source programs like LibreOffice and OpenOffice.org. If you're only using LibreOffice and you're not planning to share your spreadsheet with anyone else, it's fine to use this format. Otherwise, you'll probably want to use one of the Excel formats to make it more convenient for other people who might be using Excel.

Text CSV (*.csv) is useful when you just want to transfer raw data from one application to another. This kind of file format doesn't save formulas or even formatting for that matter - it just save the raw numerical data in text format. If you're just a beginner, you probably won't use it very much. But it's handy to know about. "CSV" stands for "Comma Separated Values" and that's exactly what these files look like if you open them up in a plain text editor - just a list of values separated by commas.

Portable Document Format (*.pdf) is useful if you want to share or publish a non-editable PDF version of your spreadsheet.

Now that you know the basics of spreadsheets, what should you learn next?

WOW, YOU DID IT! Congratulations!!

Thank you so much for taking this course. I really enjoy putting together these courses, and I hope you had fun learning about spreadsheets.

I love hearing from students, so please reach out to me and say hi!

If you found this course valuable, please consider leaving a course review. Not only does it help me, but it also helps other learners by making this course easier to find in the Udemy marketplace.

Thanks again for joining me on this course, and good luck in your ongoing learning journey!

PS You can also download a practice copy of our sample project budget if you like. I've provided both the .xlsx and the .xls versions in the "Downloadable Resources" section.