2.2 Creating your spreadsheet

TABLE OF CONTENTS

• 2.2.1 Getting started with spreadsheets
• 2.2.2 The different types of data
• 2.2.3 Managing spreadsheet columns and rows
• 2.2.4 Using simple formulas

---

2.2.1 Getting started with spreadsheets

To begin with, here are some general recommendations:

• As far as possible, organize your data into a single database with adjacent columns and rows. Start a new database in the upper left corner of the spreadsheet and complete it by descending towards to the bottom of the sheet, rather than across it.
• When building your database, don’t skip rows or columns just to “space out” the information.
• Reserve an empty column to the left of the database for column headers.
• Reserve an empty row at the top of the table for column headers.

2.2.2 The different types of data

Excel classifies the database observations entered into the spreadsheet into 3 categories: Text, Value, or Formula.

• Formula: The software calculates it immediately and then displays the result in the cell.
• Text: This type of data is automatically left-aligned. If the text is longer than the cell, it overflows onto the cell(s) to the right, as long as the cells are empty. In practice, most text entries (also called headings) are combinations of letters and punctuations, or letters and numbers
• Value: A value is always right-aligned. If it is too large to fit in the cell, Excel automatically converts it to scientific notation.

2.2.3 Managing spreadsheet columns and rows

You can add columns or rows to your existing database. For example, to add a column after the first column in your database, simply place the cursor in the column to the right of the column you want to add, click in one of the cells in that column, and then select Insert, followed by Entire column. The new column is added to the left.

To delete a column (or row) from the database, simply right-click on one of the cells belonging to the column (row) you want to delete, then select Delete, then select Entire column (Entire row).

2.2.4 Using simple formulas

When the formula has been correctly formulated, it returns the desired result as soon as it is validated. Subsequently, it will react in real time to all of the changes made to the cells on which it depends.

By starting with the = ( equals ) sign, you are letting Excel know that what you are entering is a formula – and not text or value. It precedes all formulas, even the most elementary ones, such as SUM or MEAN.

See Section 4 on data analysis.

Other simple formulas are based on a series of values or cell references containing values. These are separated by one or more of the following mathematical operators:

For example, to create a formula in cell C2 that multiplies the value in cell B2, you would type the following formula: =A2* B2.

After validating the formula =A2*B2 in cell C2, Excel displays the result according to the values currently contained in cells A2 and B2. The great strength of the spreadsheet lies in the possibility of recalculating the formula immediately and automatically, as soon as one of the cells dependent on a formula is changed.

Many of the formulas you create will perform more than one mathematical operation. Excel executes them from left to right in strict hierarchical order, which is actually the natural order of arithmetic operations. Multiplication and division have priority over addition and subtraction and, therefore, are performed first, even if these operations are not at the top of the formula (when reading from left to right).

The following is an example:

= A2+B2*\C2

If cell A2 contains the number 10, cell B2 contains the number 100 and cell C2 contains the number 2, Excel calculates the formula as follows:

= 10+100 * 2

Here Excel starts by multiplying 100 by 2, which gives 200, then adds 10, which gives 210. If you want Excel to perform the addition between the values of cells A2 and B2 before multiplying the result by the value of cell C2, you must put the addition in parentheses, as follows:

= (A2+ B2) * C2

Formula errors

Sometimes, a formula may not work the way you expect it to, when instead of seeing the expected result appear, the cell displays a strange message in capital letters preceded by a pound sign # and ended by an exclamation mark ! or a question mark ?.

Automatic data capture

To help you enter data within the spreadsheet, you can use AutoComplete: this is a form of memory that anticipates words depending on what you have started typing and works only with text (not with numbers or formulas). When you enter text, AutoComplete checks to see if text that starts with the same letters already exists elsewhere in the column. If so, it completes your entry immediately, avoiding you having to type the rest.

Auto Copy

Having to create, within the database, a series of numbers or dates that follow one another, is quite a common occurrence. For instance, entering all 12 months of the year in a workbook or creating a list of beneficiaries from 1 to 100. Simply type the first two values in the series in two cells and then drag the fill handle (in the shape of a black cross) to the right to increment in a row, or downwards to increment in a column.

Automatically calculate the sum

The Sum (∑) button in the Editing group on the Home tab of the Ribbon lets you quickly calculate the sum, average, count, maximum and minimum values of a list while selecting the corresponding range of cells. Simply click on the cell where you want the sum to be displayed and Excel will automatically select the range of cells to use. If necessary, it is possible to correct this selection by dragging the cell cursor over the range to be selected.