What are the basics of MS-Excel?

Answer 1

That is a big question. This answer is therefore long, but even then there is so much more to a spreadsheet. There are many other questions where some of this kind of information can be found and many other things about Excel, if you look at some of the answered questions.

A Spreadsheet is a computer application used for numerical analysis and manipulation. It was invented by a Harvard business student, Dan Bricklin, and a programmer, Bob Frankston. They launched the first Spreadsheet program, Visicalc, in 1979 on an Apple II Computer. The two men founded their own company, Software Arts. There are now many other spreadsheets available, such as Microsoft Excel, Lotus 1-2-3 and Quattro Pro. Most of these programs contain many new features.

A Spreadsheet is split into Columns and Rows based on an accountant's worksheet.

Columns are referenced by letters.

Rows are referenced by numbers.

Where a Column and a Row intersect a Cell is formed.

A Cell is referenced by its Column letter and Row Number.

A Range or Block is a rectangular group of cells referenced by the top left and bottom right cells.

A Spreadsheet can have thousands of columns and thousands of rows and therefore millions of cells. It will depend on which version of Excel you have as to how many columns and rows there are in a sheet. The Column after Column Z is known as Column AA. Then comes AB, AC, AD etc. The width of a Column and less commonly, the height of a Row can also be set in the Spreadsheet.

A number of kinds of data can be entered into cells in a Spreadsheet. These include:

Numeric: Quantity or values that may be subject to arithmetic operations that may be positive or negative. A number of different formats of numeric data, such as currency, percentage or scientific notation can also be used and are often treated as separate data types in their own right.

Text: Any readable character including digits. Used mainly for headings, labels, codes, names etc.

Date: Dates in different formats.

Time: Clock times in various formats such as 12 or 24 hour based

Logical: Logical data represents either one of two values, TRUE or FALSE. It is used for the result of comparing two values using relational operators. eg

3 = 4 is FALSE. 3 < 4 is TRUE.

Logical data can be entered directly by users in a Spreadsheet, by typing TRUE or FALSE into a cell. However they are usually only used as an integral part of formulae containing relational operators. For example making comparisons between values will result in a TRUE or FALSE. Based on the outcome subsequent calculations can be made like deciding what rate of tax to charge based on earnings compared to tax bands.

The format of cells or the way they display the information in a cell can be set by the user. A wide range of formatting options are available, depending on the data type. For example if you had 5.5 in a cell, among other formats, it could be displayed as:

Currency: €5.50 Percentage: 550% Decimal (2 places): 5.50

Text can be shown as Left Justified, Right Justified or Centred. It can also be shown as Bold or Italic and in different fonts and sizes.

Dates can be shown in different formats:

12-Jan-2009

12/01/09

12-Jan
Depending on your computer's settings, your dates may show the day and month in a different order, as different parts of the world use different formats for their dates. In Europe, the dates are as above, but in America 12/01/09 would be December the 1st rather than the 12th of January.

One of the most powerful features of a Spreadsheet is the ability to use formulas for calculations. Formulas should always be used in preference to manually working out results of calculations. All formulas in Excel begin with the = sign. This may differ in other Spreadsheet packages. It is possible to perform calculations using the values of different cells together to produce a result. For example in a cell you could have =3 + 6 which will give the result 9. Using values in this way is not normally done.

Cell and/or Range references can be used in formulas as the alternative to direct values. If for example the value 10 is in cell C11 and the value 23 is in cell C12, a formula could be entered in cell C14 to multiply one by the other. The formula could be =C11 * C12. The answer it returns will be displayed in cell C14. . If either of the two values are changed the formula will automatically recalculate the result. Formulas usually use references in preference to using values, although values may form part of the elements of a formula like having something like =D20*10.

CELL CONTENTS are what is contained in a cell such as a value or a Formula.

CELL DISPLAY is what the cell shows like the format of a value or the result of a Formula.

For example if you have a cell formatted to percentage and you enter the value .25:

Cell Contents = .25 Cell Display = 25%

If you have a formula in cell B13 to add A12 containing the value 6 to A13 containing the value 4 and the cell B13 is formatted to currency with two decimal places:

Cell Contents : =A12+A13

Cell Display : €10.00

Functions are pre-written formulas that come supplied with the Spreadsheet to enable the user to carry out specialised or common tasks. There are functions to do a wide range of tasks. The functions are usually classified under various headings including Mathematical, Logical, Special, Text, Date/Time, Financial, Statistical and Database Functions.

Among the most common functions are: SUM, AVERAGE, COUNT, MAX &MIN.

SUM allows you to total a range of cells.

=SUM(A1:A15) will total all values from cell A1 to cell A15 inclusive and display the answer in the cell in which the function is entered. This is much quicker and effective than typing the equivalent formula which would be:

=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10+A11+A12+A13+A14+A15

AVERAGE totals the cells and divides the result by the amount of cells into which a value has been entered not by the amount of cells in the range

=AVERAGE(A1:A15) will give the average of the values in cells A1 to A15. For example if in the range A1 to A15 there were 6 cells which were totally blank, having no value, not even 0, then the total of the cells would be divided by 9 and not 15. This means that AVERAGE gives more accurate results.

COUNT will count the amount of cells in which any data of any type containing numbers, like values or dates, have been entered. It should not be confused with SUM which totals values in cells.

=COUNT(A1:A15) . The given example could therefore give any value from 0 to 15, depending on the amount of cells which have entries with numbers or numbers as part of the data.

COUNTA is a similar function that counts cells with any values in them. If a cell contains no data it is not counted. However if you have a cell in which you have accidentally hit the space bar, a space being a piece of data will be counted. The cell will of course appear to be blank

MAX gives the highest value in a range of cells or from a list of values.

=MAX(A1:A15). This will give the highest value among the range selected.

MIN gives the lowest value in a range of cells or from a list of values.

=MIN(A1:A15). This will give the lowest value among the range selected.

More than one range or a combination of ranges, cells and values can also be used by separating the groupings by commas in any of the above functions:

=SUM(A1:A10, B14:B17, B23, C23, 45)

=AVERAGE(A10, B13, C6:C12, 38)

=COUNT(A1, A2:D6)

=MAX(10, C20, D15:D20)

=MIN(A1, A3, A5, A7, A9, A11, 20)

Relative Addresses: Formulas work by what is known as Relative Addressing. This means that when a formula refers to certain cells it thinks of their position or address relative to the cell in which the formula is written rather than the actual cell reference. In the earlier example, the formula in cell C14 multiplied the contents of cell C11 by the contents of cell C12. The computer will think of the formula as multiplying the contents of the cell three cells above the current cell by the cell that is two cells above the current cell. This characteristic of Formulas is most useful when it comes to copying Formulas. Placing this formula anywhere will result in the same process.

If you had values in cells D11 and D12 that you also wanted to multiply, it could be done by copying the formula in cell C14 to cell D14. The formula would again multiply the contents of the cell three cells above the current cell by the cell which is two cells above the current cell. Therefore it would multiply D11 by D12 and put the result in D14. This Formula could be copied into cells E14, F14, G14, H14, I14 etc. and it would do the same in all cells.

Absolute Addresses: Sometimes you will want a formula to use a constant value as one part of the calculation. For example you may want to multiply each of a set of values by 10 or by a tax rate. This constant could be entered direct in the formula but it is better to have it in a single cell that can be referenced no matter where the formula is used. With Relative Addresses the cell reference will change when a formula is copied. To make a cell referred to in a formula constant, it must be given an Absolute Address. This can be done by putting a dollar sign ($) before the column letter and row number.

If in a formula we wanted to multiply one cell value by a value that was in cell A1 and then copy it so that other cells are also multiplied by A1, we would make the A1 cell an absolute address in the formula as follows:

=C11 * $A$1

If this formula was in C14 and we copied it to H14 for example, the Formula would become:

=H11* $A$1

Note that the C11 has changed to H11 because it was a relative address but that the A1 is still constant. Without the making the A1 absolute the cell address would have changed to F1

By using Absolute and Relative addressing carefully it is possible to make Spreadsheets work much more efficiently and also make them easier to amend saving yourself some work. If A1 contained a tax rate which changed occasionally, it would be possible to change its value and therefore all the formulae referring to it instantly. Otherwise each individual formula would have to be changed to accommodate the new tax rate or the tax rate would have had to be entered repetitively in a number of cells corresponding to the formula. A change in the tax rate would mean all these cells would have to be changed, resulting in more work and the possibility that a cell may not be changed resulting in inconsistencies in the Spreadsheet.

It is also possible to make just the Column or the Row an absolute reference by putting the dollar sign before either:

$B11 : In this example the Column B is an absolute reference whereas the Row 11 is a relative reference.

F$16 : In this example the Row 16 is an absolute reference whereas the Column F is a relative reference.

If either of these formulas are copied the relative value can change and the absolute reference will remain permanent. The following rules apply for deciding what needs to be made absolute:

If a formula is being copied down, then only the row reference needs to be absolute.

If a formula is being copied across only the column reference needs to be made absolute.

If a formula is being copied down and across, into a block, both the row and column references need to be made absolute.

Most formulas are copied either down or across so usually only the row or column needs to be made absolute.

To gain the full benefit of Relative and Absolute addressing it is important that values are laid out in a structured manner on the Spreadsheet.

Answer 2

Rows, Column, Worksheet, Cell , Workbook, Range, and Cell content,