Constants and Variables

Before getting started on formulas, it’s important to discuss two things: constants and variables.  Most people learned about this in Algebra class, but that was a long time ago, so here’s a refresher.

If it’s a number in a formula, it’s a constant.  1, 5, 37, and 9.3 are all constants.

If it’s in a formula and it’s not a constant, it’s a variable.  Most spreadsheets have two kinds of variables: cell references and named variables.

Cell references are just what they sound like:  they refer to the value in some other cell.  For example, using the variable “a2” would be the same as saying “whatever the value is in cell A2.”

Named variables are also just what they sound like: references to some other cell that has been given a name for user convenience.  For example, “A2” doesn’t tell you much about what the value in the cell represents, but “JulyIncome” gives a much clearer idea, even if it’s the same as far as the computer is concerned.  When creating generic examples I’ll usually use the names “Foo” and “Bar”.
Continue reading Constants and Variables

Basic terms

There are a few things that every spreadsheet program will have, in one way or another.

  • Every spreadsheet will have cells.  (Or cels with a single L, but my spell-checker hates that, and I don’t remember where I learned it, so…)  That’s a single box that holds one piece of data.  In Excel, a cell is identified by the row and column that it is in.
  • Every spreadsheet will have rows.  That’s a horizontal line of cells.  In Excel, the rows are identified by numbers.
  • Every spreadsheet will have columns.  That a vertical line of cells.  In Excel, the columns are identified by letters (A, B,…Z, AA, AB…AZ,BA,…).
  • Every spreadsheet will have a way to view the complete contents of cells.  Most (possibly all) will have it in a long box near the top of the screen, similar to the address bar in a browser.  I’ll be calling this the formula bar.
  • The rows and columns all reside on a single page, called a worksheet.  Generally if you see several tabs near the bottom of your screen, they lead to the different worksheets.  Each worksheet  has a different name.  When you open an Excel file it will have three blank worksheets, named Sheet 1, Sheet2, and Sheet3.
  • All the worksheets in a file are collectively referred to as a workbook.

In the cells, you can put text or formulas.  Before you have flashbacks to math class and decide to flee in terror, the formulas aren’t necessarily horrible.  = 1 is a formula that tells the program that the value in that cell is equal to 1.  I’ll explore formulas more in later posts.

It you put text in a cell, it won’t do anything.  In most cases the only reason to put text in a spreadsheet is so a human viewing that spreadsheet knows what they’re looking at.  Therefore, even though it doesn’t help the spreadsheet at all, I strongly recommend adding text so that if you come back to your spreadsheet in a year or so you’ll still be able to make sense of it!

Welcome to Swiss Army Spreadsheets!

Most people have access to a spreadsheet program.  Microsoft bundles Excel into its Office suite, OpenOffice has Calc, Google Docs has “spreadsheets”, and so on.  For a great many people, however, the spreadsheet program is one they never open.  Many people, even if they do use spreadsheets, consider them to be some sort of infinitely large table and not much good for anything else.

Despite popular opinion, though, spreadsheets can be used for a wide variety of purposes.  They’re the Swiss Army knife of the software world.  People sometimes either buy an expensive, specialized program or simply do without when all they really need is a spreadsheet and a little creativity.  As examples, I have in the past made spreadsheets that graphically illustrated available space on library shelves, calculated a variety of monthly statistics quickly and easily, extracted data from emails, verified data validity, and even written computer code!

When I talk to people about this sort of thing I get one of two reactions — either “Is that really possible?” or “Yes!  I love doing that!”  Since the first reaction outnumbers the second reaction by something like four to one, I thought I would make this blog to help show people the possibilities and explain how to do some of them.

I’ll do my best to explain how to do everything in four different programs: OpenOffice, Google Docs, Excel 97, and Excel 2007.  I’m choosing these four programs because they’re what I have easy access to, and most people will have easy access to at least one of them.  Excel 97 is, obviously, old, but I want to emphasize that the things I demonstrate here are not only available with the newest program and the latest bells and whistles.

So, welcome!  I hope this blog is helpful to you.  If you have any questions or want me to address particular topics or ideas, please leave a comment!