Saturday, May 4, 2013

Excel tutorial 01: basics

This tutorial is intended as a first contact with Microsoft Excel. The prerequisites are as low as a high-school level of mathematics (functions) and a simple familiarity with computers, in particular MS Windows and knowledge of the keyboard (Tab, Enter, Ctrl, Alt).

Excel is a spreadsheet program. Spreadsheets are tables or arrays divided in rows and columns, in which you can enter text and numerical values. As we'll see, spreadsheets can also include functions or formulas which transform automatically text or numerical values, like counting the number of words or the number of letters present somewhere else in the sheet. The data can also be represented as charts or can be further processed with cross-tables or macros, but these aspects are beyond the scope of this first tutorial. They will likely be covered in a later article.

Spreadsheets are often used by individuals, associations and organizations for accounting purposes but they offer far more possibilities like data analysis, presentation, input forms, games, etc.

 Part 1: Let's get familiar with the layout of Excel

The wide strip at the top is Excel's menu. You will find there most of the functionalities that you may desire and more. The most common tools for a beginner are found under "Home", like font sizes, font color, bold, italic, underline, and various functionalities related to presentation.

Under the menu, you can see the formula bar preceded by the label "Fx". Its use will be discussed further in this article.

Then the main area. It is divided in rows and columns which define cells. This is where most of your activities will take place. The mose common way to refer to cells is to designate them by the letter of their column and the number of their row. Thus, on the screenshot above, the text "bla bla 1" is inside of cell A1 and "bla bla 2" is inside of cell C2.

You will also notice that the focus is currently on A1, as is made clear by the thick border around that cell, and by the orange highlight of column_A's header and row_1's header. The cell with the focus is called the "Active Cell" and its content appears in the formula bar.

Last on the screenshot, you see tabs which allow the user to bring different sheets into view. As with cells, you can notice that the tab for the current sheet is highlighted. Logically, the current sheet is referred to as the "Active Sheet".

Part 2: Let's use Excel and navigate between cells

Moving between cells can be done with the arrow keys of course, but if you just want to type a series of text and data, your hands will be far from these keys. Instead, you will be using Tab and Enter.

Tab moves the Active Cell to the next column, on the same row.

Enter  moves the Active Cell to the next row. It does not stay in the same column as the current Active Cell if you previously used Tab but moves back to the initial column instead. This allows you to fill a complete table row after row.

Just as above, start from Cell B2 and fill the table with letters A to P, moving around with Tab and Enter.

Part 3: functions

Recall your memories from high school: functions are the transformation of 1 or more input data into an output. Some of the most basic functions that everybody studies is of the form:
f(x) = a*x + b

In this case, we see a single variable: x. And we see 2 constant factors: a and b. It is often written as y = a*x + b for commodity because we represent the results on a chart.

Later on in high school, we studied functions that combine more than 1 variable. If you want an example, think of the probability to have a health problem based on more than 1 factor. For example you can combine age and gender. If age is our "x" and gender is our "y", then you could write this probability as f(x, y).

Excel can similarly apply transformations to your data. That's what the formula bar is for. On a new sheet, reproduce the following table. I have circled the tools, which allow you to fill cells with a color and to draw borders.

We are now going to calculate the amount paid for each type of product, which is the multiplication of the quantities bought by the unitary price. We will calculate this formula in cell D2 therefore you have to make D2 the Active Cell. A formula always starts with the symbol "=". You can either type it directly in the cell or in the formula bar. When your formula is written, just type Enter to validate it.

The first thing to notice is that your formula can refer to the content of other cells simply by mentioning those cells' coordinates (column and row).

The second thing to notice is that when you refer to other cells, these references appear in color in your formula bar while these cells are highlighted by a border of the same color. In this example, "B2" is colored blue in the formula bar and cell B2 is highlighted by a blue border. Same thing for C2 except in green color.

Once you've validated the formula, cell D2 will display the result of your formula.

Repeat this operation for the next 3 rows and you should obtain this:

We will now calculate the sum of sums. As you see (and that's one beauty spreadsheet programs), Excel can reuse the results of formulas as input for other formulas.

This time, instead of calculating D2+D3+D4+D5, we will call a built-in function: SUM.

You can notice here that the full Range from D2 to D5 is considered as one input by the formula and it is highlighted by a blue border. Excel is able to handle such groups of data as if it was an array or a series of values and apply calculations to these arrays. Such a topic will be covered in another tutorial.

You may also wonder about the function SUM, which you didn't previously know about, and other such functions. As you can expect, similar functions like AVERAGE, MIN, MAX, etc. exist in Excel. You can either look for them by clicking on fx (left of the formula bar) or wait for another tutorial to cover this subject.

The result of the SUM (note that upper-case is NOT necessary) appears in the next screenshot:


You have discovered
  • Excel's user interface with menu, sheets, formula bar and main area
  • what the Active Cell and the Active Sheet are
  • how to move around with  Tab and Enter
  • how to edit cells' content
  • that cells can contain text or numbers or formulas
  • that cells are referred to by their column and row
  • what a formula is
  • that formulas always start with the symbol "="
  • that formulas display in color the cells it refers to
  • that formulas' results can be reused by other formulas
  • that a group of contiguous cells is called a Range
  • that a Range can be used a single input for a formula

Since this tutorial has taken a long time to produce, I don't exclude the possibility of some inaccuracies to have sneaked in. So if you see anything that strikes you, don't hesitate to let me know in the comments. You're also welcome to provide any other kind of feedback.

No comments:

Post a Comment

Creative Commons License
Erik Lallemand's blog by Erik Lallemand is licensed under
a Creative Commons Attribution 3.0 Unported License.