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:
Mathematical operator | Meaning |
---|---|
+ |
Addition |
- |
Subtraction |
* |
Multiplication |
/ |
Division |
^ |
Exponentiation |
For example, to create a formula in cell C2 that multiplies the value in cell B2, you would type the following formula: =A2* B2.
Example | Instructions |
---|---|
To enter this formula in cell C2, follow these steps : 1. Click on cell C2, 2. Enter the whole formula =A2*B2 in the cell, 3. Press the Enter key. |
Some data collection tools store data related to multiple choice questions in a single cell. In this case, the data must be distributed in separate cells on the same line. The data below illustrates this situation: The waterTreatmentMethod variable combines all of the answers within a single cell.
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 ?
.
Type of error | Probable cause |
---|---|
#DIV/0! | Occurs when an element of the formula needs to be divided by the contents of a cell with a value of 0 or, most often, when that cell is empty. In mathematics, division by zero is an aberration. |
#NAME? | Occurs when the formula refers to a range name that does not exist in the workbook. This type of error occurs when you make a typo in the name or when text used in a formula is not enclosed in quotation marks, which suggests to Excel that it is a range name. |
#NULL! | Most often occurs when you insert a space, instead of the semicolon used to separate cell references used as arguments for functions. |
#NUMBER! | Occurs when Excel encounters a problem in the formula with a number, such as an erroneous type of argument in a function or a calculation that produces a number that is too large or too small to be represented in the spreadsheet. |
#REF! | Occurs when Excel encounters an invalid cell reference. Such is the case when you delete a cell used by a formula or, in some cases, when you paste cells over the cell to which the formula refers. |
#VALUE! | Occurs when you use the wrong type of argument or operator in a function, or when using a mathematical operation that refers to cells containing text. |
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.