Features of Spreadsheet Applications
1. Saving. Saving enables a user to create a file for the workbook in a directory on the disk. The Save command can be accessed from the File menu. NB: The file extension of an Excel document is .xls
2. Columns and Rows. A spreadsheet is a grid consisting of columns and rows. Rows are labeled with numbers, and columns are labeled with letters, giving each cell a unique address consisting of a number and a letter.
3. Auto Complete feature. The AutoComplete can speed up data entry, especially if you have to enter a particular word or phrase repeatedly.
When you start entering data into an empty cell, directly below an existing list, Excel will automatically offer you a match from the data above it.
4. Formulas. Formulas are used to express mathematical relationships between cells. For example, C1=A1+B1 would add and display the sum of cells A1 and B1 into cell C1. It could also be a simple mathematical formula, such as
(100 * 3) / 40. Formulas are not displayed on spreadsheets. What you see in the cell is the result of the formula. Don’t forget about order of operations (BEDMAS); anything inside parenthesis happens first, followed by Exponent (^) Division (/) multiplication (*), followed by addition (+) and subtraction(-).
5. Functions. Functions are used as shortcuts when performing mathematical calculations. Functions are pre-programmed formulae that give power and flexibility to spreadsheet calculations. They prevent the user from having to continually enter long and cumbersome formula expressions. They constitute standard keywords and syntax. Some examples are SUM, AVERAGE, MAX, and MIN.
6. Cell References / Addresses. You can refer to a Cell by using the Column letter and Row number
For Example, D8 refers to a cell in Column D and Row 8. In this case, D8 is known as the Cell Reference and is also used as the default name for the cell.
A Circular reference occurs when a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).) refers back to its own cell. For example inserting the formula =A2+A3 in cell A2 creates a circular reference.
RELATIVE, ABSOLUTE and MIXED CELL REFERENCES
RELATIVE REFERENCE: a relative reference is a cell address that changes when it is copied to other cells in a spreadsheet. This is because it is based on the relative position of the cell.
Example: If in the cell C3 we have the formula =A1+B2, and we copy the formula to cell E8, then it would automatically adjust to =C6+D7.
ABSOLUTE REFERENCE: An absolute cell reference is a cell address that does not change when it is copied to other cells in the spreadsheet. Absolute references always refer to cells in a specific location. To make a cell address absolute, we insert the dollar symbol $ before the letter that identifies the column and before the number that identifies the row, example B1 becomes $B$1. If you copy the formula across rows or down columns, the absolute reference does not adjust. Example:
Example: If in the cell C3 we have the formula =$A$1+$B$2, and we copy the formula to cell E8, then it would remain the same =$A$1+$B$2.
MIXED REFERENCE: is when you have either:
an absolute column and a relative row e.g.$B1 or a relative column and an absolute row e.g. B$4.
And if the cell address is copied to other cells, the relative argument changes and the absolute part does not change.
Example: If in the cell C3 we have the formula =$A1+B$2, and we copy the formula to cell E8, then it would automatically adjust to =$A6+D$2
Note: An easy way to add the dollar signs to a cell reference is to edit a cell reference and then press the F4 key on the keyboard.
7. Ranges. A range is a combination of two or more adjacent cells in a worksheet
- You can refer to a range by using the cell reference of the top – left and bottom- right cells.
- For example, B2:D8 defines the rectangular range bound at the top left by the cell B2 and at the bottom – right by the cell D8
8. Custom names for cells and ranges. Identifying a particular cell (or range of cells) is made easier when it is given a unique name. Names are easier to remember than default cell references since they can be specific to the task being worked upon
- Names make it easier to change the structure of the sheet.
- Named cells/ranges can be referred to from other sheets easily.
- It is easy to go to a named range or cell using F5.
9. Graphs and charts. A Graph or Chart is a graphical representation of Data in a spreadsheet. These graphics, which illustrate the meaning of the numbers in the spreadsheet in different ways, can be used to stimulate interest and help make a point.
- There are several different types of graphs that can be made. Area graphs, bar and column graphs, line graphs, pie charts, scatter plot diagrams and 3-D graphs are some examples.
Other Features
- Multiple worksheets
- Inserting/deleting columns and rows
- Cell formats (Numbers, Strings, Currencies, Dates, Times)
- Cell merging (rows and columns)
- Cell locking
- Custom styles for columns, rows and column groups
- Delete text: Allows you to erase characters, words, charts, or worksheets as easily as you can cross them out on paper.
- Cut and Paste: Allows you to remove (cut) a section of text from one place in a spreadsheet and insert (paste) it somewhere else.
- Copy: Allows you to duplicate a section of text.
- Search and Replace: Allows you to direct the spreadsheet to search for a particular word or phrase. You can also direct the spreadsheet application to replace one group of characters with another everywhere that the first group appears.
- Headers, Footers, & Page Numbering: Allows you to specify customized headers and footers that the spreadsheet file will put at the top and bottom of every worksheet. The spreadsheet automatically keeps track of page numbers so that the correct number appears on each page.
- Spell Checker: A utility that allows you to check the spelling of words in a spreadsheet file.
- Print: Allows you to send a spreadsheet file to a printer to get a hardcopy or many hardcopies as you would like.
- Graphics: The spreadsheet application permits the addition of various types of graphics, such as bar charts, line charts, clip art, and photographs.
- Data Sort
- Macros – Allow a user to record or save a sequence of keystrokes or instructions that can be run later.
- Recalculation
- Zooming Worksheet view
- Alignment, Wrap Text, etc.