Spreadsheet Basics: Formulas & Functions
Translated by
ScribdTranslationsSpreadsheet Basics: Formulas & Functions
Translated by
ScribdTranslationsFundamentals of the spreadsheet
1. Introduction
2. Cell content
3. Number format
4. Formulas
5. Assistance for data entry.
6. Absolute and relative references. Names.
7. Ranks
8. Functions
9. Graphs
10. Printing
11. Protect cells and the spreadsheet
1. Introduction
A spreadsheet is an application that allows, as its name indicates,
to perform calculations automatically and with great agility, but also we
allows generating graphs and presenting the data attractively within a
document.
A spreadsheet consists of cells evenly distributed in rows.
and columns. The columns are named with a letter and the rows with a
number. Thus, the cells are named indicating their column and their row. For example: A7, B9, C20, H99.
The number of columns and rows in a spreadsheet depends on the application, Open Office 3.1.
it has columns from A to AMJ, and rows from 1 to 1,048,756.
Modern spreadsheets allow grouping several sheets within the same file that are
they differentiate from each other by the name of the sheet. Some applications refer to the file as a workbook
sheets. To distinguish the cells from one sheet to another, they are named by specifying the name of the sheet.
followed by a point and the cell. For example: Sheet1.A9, Sheet3.B89, Sheet2.F3.
2. Content of the cells
When we write data, the application usually shows a different value in the cell than the one that
we have introduced taking into account the type of data, the number format and the calculations of the
formulas.
Cells can contain three types of basic data:
• Texts: they will be all those data that contain any character different from the digits
numerical, the decimal comma, the date separator slash, the colon separator
in hours, the percentage symbol. If we want any value to be considered as
a text we will use the apostrophe character ( ' ) before the value.
◦ They will serve as labels for the user to understand what the data is used for.
◦ To indicate titles.
◦ Any other text such as comments, annotations, etc.
◦ Text operations.
January, 1,245 euros, '1245.34,' =A2*B3, Result 9.
• Numbers: they will be all those data that contain only numerical digits and the comma.
decimal. Dates are treated as a special type of number and we will introduce them.
expressing the day, month, and year separated by the slash character ( / ). The hours are also a
special type of number and they are specified by separating hours, minutes, and seconds using
the character two points ( : ). Finally, the percentage character ( % ) allows us to introduce
numbers that are considered between 0 and 1.
◦ Numbers are treated specially by formulas to obtain results from
automatic form.
134,4, 56 ,10/10/2010,0,56.
• Formulas: those that always start with an equal sign ( = ). Below it is expressed
a mathematical formula where cells, operations, and functions are indicated.
◦ We will use them when the value to be displayed is normally obtained from the value of others.
cells.
◦ If the mathematical expression is poorly stated or some value cannot be obtained
we will receive an error message.
=A2,=B56,=A1*B2/2,=(A1+B2)/C2.
3. Number format
The number format allows controlling the presentation of formula results and values.
numerical. We can:
• indicate the number of decimals.
• indicate the magnitudes or units of the values.
• indicate the color of the font according to whether the result is positive or negative.
The default number formats are:
• Standard. The text is aligned to the left, the numbers and formulas to the right. It
they show the same values that were entered without any changes except in the formulas
that the result is shown.
• Number. Similar to the previous one but allows us to specify for numbers and formulas:
◦ the number of decimals
◦ the number of leading zeros
◦ show in red if it is negative
◦ show the thousand separator
• Percentage. The value is shown multiplied by 100 and with the % symbol.
• Currency. The value is displayed next to the symbol of the currency we select by default.
the one from the country.
• Date. The number is displayed as a date. The computer considers dates as
numbers, thus for Open Office the number 0 represents December 30, 1999.
We can select different formats where the dates are displayed with numbers or by
letters, day of the week, etc.
• Hour. The number is expressed as a time.
• Science. The number is expressed in scientific notation.
• Fraction. The number is expressed as an integer by a fraction.
• Logical.
• Text. The number is displayed as text without any transformation.
• Customized. It allows us to combine and modify the previous ones. For example: for a
calculated or entered value of 18.789 show two decimal places and express that they are
kilometers, 0.00 "km"; it will show 18.79 km. In quotes, we will indicate the symbols that
we want them to appear next to the numbers.
4. Formulas
Formulas always start with the character =. Next, the mathematical expression is
composed of constant values, cell references, operators, and functions. The precedence of
operators indicate the order in which the operations will be evaluated, possibly grouping operands with
parentheses to change this order.
The most commonly used operators are:
• +, sum
• , rest and sign.
• multiplication
• /, division
• ^, power
The precedence of operators indicates that the operations will be evaluated in this order:
• parenthesis
• signs
• powers
• multiplication and division
• additions and subtractions.
For example:
• 5+3*4 = 5+12 = 17
• (5+3)*4 = 8*4 = 32
• 5+(3*4) = 5+12 = 17
5. Data entry assistance. Filling cells and series.
To input data quickly, cell fill or filling is used. This consists of repeating
a value a certain number of cells (copied and pasted quickly) or based on a series
arithmetic, geometric or date series to fill the cells with successive values.
This operation can be performed through the menu or by using the mouse with the
small square in the bottom right corner of the active cell. The mouse pointer is
change to a cross and by dragging it, the filling of the cells will begin
contiguous.
6. Relative and absolute references. Names
When in a formula we refer to a cell as we have seen so far, the
the reference will be relative, that is to say that if we copy and paste the formula, or fill it in
In any direction, we will see that the letters of the columns or the numbers of the rows change.
A B C D E F
1 1 = B1 * 2 1 = $D$1 * 2 Filling down of
Fill down of
2 2 =B2*2 =$D$1*2 cell E1
3 3 = B3 * 2 =$D$1*2
Sometimes we want to refer not to the cell on the left, nor to the one above, but to
we want to reference a specific cell that should not change when we fill in the formula
other cells. To achieve this we must use absolute references.
An absolute reference is specified using the dollar sign ($) in front of the column and/or
the row. The reference to the cell will not change at any time.
To facilitate absolute references that we can make to cells in complex spreadsheets
we can give more familiar names to the cells. For example, we can use the nameTotalen
place deZ78, which will be more descriptive. The only limitations of the names are the inability to
to match with that of no predefined cell, with that of no function, nor with that of another
name already used.
7. Cell ranges
A range of cells is a set of cells. To indicate ranges, two characters are used.
specials:
• semicolon ( ; ). It is used to indicate cells or ranges that are separated or not contiguous.
• colon (:). It allows us to specify the first and last cell of a range including in
this all that is among these.
For example, if we find written
• The range contains 3 cells: A1, C2, and B3.
• The range contains 3 cells: A1, A2, and A3.
• The range contains 6 cells: A1, A2, A3, B1, B2, and B3.
• The range contains 7 cells: A1, A2, A3, B1, B2, B3, and F8.
• The range contains 9 cells: A1, A2, A3, B1, B2, B3, F8, F9, and F10.
8. Functions
Functions allow calculations to be performed on complete ranges of cells or values. A function is
identify by its name, to use it we will indicate its name and in parentheses the
parameters required separated by semicolons (some spreadsheets use a comma
in its place).
These parameters can be cells, ranges of cells, numbers, and texts expressed in quotes.
For example:
• =SUM(A1:A50) will add all the values from the 50 cells in the range. Without the SUM function.
we would have written: =A1+A2+A3+A4......., practically useless when it comes to a sheet of
calculation.
• =average(A1:A50), will calculate the mean of the values of the 50 cells.
• =arabic("XX"), it will show a 20.
Functions are grouped into categories according to their utility and the types of data they handle:
• Databases. Arithmetic operations with selection criteria.
• Date and time. Perform calculations on dates.
• Finance. Financial calculations, about loans, etc.
• Information. They obtain information about the content of the cell.
• Logical. Conditionals and boolean values.
• Mathematics. Mathematical operations such as addition, sines, cosines.
• Matrix. Operations on matrices.
• Statistics. Statistical calculations.
• Spreadsheet. Specific functions on spreadsheet information.
• Text. They transform texts, extract information from them, concatenate them, split them, etc.
• Add-in. Added to the previous ones or custom extensions on each computer.
9. Graphics
Graphs allow us to obtain a representation of the data in a way
quick. To create a chart we will follow the wizard taking into account the
fundamental steps:
• Type of chart: Columns, bars, circles, areas, lines, of
dispersion, of network or stock.
• Data range: cells from which the data to be obtained will be
numerical series, labels for values, axes and titles.
• Elementos gráficos: todos los gráficos constan de las siguientes parte pudiendo hacer que se
show or not according to our objective:
◦ Título y subtítulo. Etiquetas de texto decorativas.
◦ X axis, Y axis, and optionally choose Z depending on the type of chart. They represent the divisions.
horizontals, verticals, and depth respectively. The most important property
it is the scale of numerical representation.
◦ Grids. They are the horizontal and vertical lines that facilitate the comparison of the
data about the graph.
◦ Legend. Displays the drawing or color of each series associated with a label.
◦ Side plan of the graph or chart. It is the chart itself, on which the series appear.
data, grids, and axes.
◦ Area. It includes the entire area on which the other elements are placed, the chart.
in itself, the legend and the titles.
10. Printing
In a spreadsheet, we cannot define its dimensions, the number of rows and columns.
it is always the same, but we can define a range of cells to be printed. This range is
the print call. When the result takes up more than one page we can specify
which row and/or column do we want to appear on all pages.
11. Protect cells and the spreadsheet
The spreadsheet is a program where the work of the designer is very clearly distinguished.
spreadsheet and the spreadsheet user.
The designer will be responsible for writing the formulas, creating the graphs, and formatting the cells.
Once their work is completed, they will protect the spreadsheet to prevent the user from modifying it.
accidentally or intentionally. The latter will be able to use the sheet without the need to write
formulas, limiting themselves only to writing the data and obtaining the results in a way
snapshot.
But how does the designer select which cells the user can modify or not? The designer
it will protect those cells that the user should not modify, such as labels, formulas, empty cells,
etc.; and will unprotect those where the user has to enter the data.
When the designer has finished their work, they will protect the complete document or the sheet, remaining
only the cells that have not been protected can be modified. If we use a password the
The user will not be able to unprotect the sheet or document unless they know it.