Nov 27 2008
01: Intro
Before I forget, if you’re familiar with Excel, but are mystified by the completely new interface of Office 2007, it’s actually pretty simple. At the top of the screen are the Office Button and the ribbon. File operations and options are under the Office Button (click it). The ribbon contains all of the Excel-specific functionality (if you’re going nuts trying to figure out where something is, click the Office Button).
Okay, let’s start.
Excel is a spreadsheet application, a program designed to crunch numbers. An Excel file is called a workbook. A workbook is three dimensional: It can contain as many as 255 worksheets, like having 255 spreadsheets in one file. You navigate from worksheet to worksheet by clicking the worksheet tabs at the bottom of the screen.
A worksheet is a grid of columns and rows. The columns are denoted by letters, and the rows, by numbers. The intersection of a column and a row is known as a cell, and we refer to it by the column letter followed by the row number. A contiguous block of cells is known as a range, and is denoted by the address of the beginning cell and the end cell, separated by a colon (no spaces).
Cells may contain two types of data: Labels (text) and values (numbers). Values in turn may either be numbers, formulas, or functions.
Formulas and functions must begin with the equals sign, or they will not calculate (to show the formula or function in the cell, put an apostrophe in front of the equals sign). A formula looks like this:
=A2+C6
A function is a “canned” calculation available in Excel. To insert a function or see the available list, click Formulas on the Excel ribbon. A function looks like this:
=SUM(A2:A56)
Never forget this: In Excel, we always refer to cells and ranges in formulas and functions by their addresses or names. We never hardcode the values into formulas and functions. Never, never, never, never!
To format a cell or a range, select it, then right-click, and select format cell. Play around with it.
To insert a comment, select the cell or range, right-click, and select add comment.
You probably knew all of that. Here’s something you may not know, but should. In Excel, you can give cells or ranges names, and use those names in formulas or functions. To name a cell or range, select it, right-click, and select Name a range. We’ll get into the advantages of names later.
Before we move on, let’s discuss cell addressing. The default is relative cell addressing. Say, for example, you want to compare several hockey players’ points over eight games. You only need to put the SUM function in the first cell. You can then copy and paste it to the others, and it will adjust the cell addresses (you can also copy the contents of cells by grabbing the little square at the bottom right corner of the cell and dragging it).
There is also fixed cell addressing. You use this when you want to perform a calculation multiple times, but want one of the cell addresses to remain constant. A fixed cell address has a $ in front of the column letter and the row number.
Finally (and least frequently), sometimes, you need either the column or the row to remain fixed when you paste a calculation, but not both. This is known as mixed cell addressing. For example, say you are looking at different commission rates for your sales reps, and want to see what they would make if you changed the commission. See the screenshot:
Mixed cell addresses have only one $, in front of the element you want to remain fixed.
Note that if you give a cell or range a name, Excel treats it as fixed.
Okay. Pull up Excel and play with it. When I get up the energy to create some more screenshots (I despise Photoshop), we’ll start with functions, first the basics, then incredibly useful functions many know nothing about.
The “Snipping Tool” that came with Vista is a really easy-to-use screencap maker. I use it all of the time to capture stuff and paste into Word.