Reference guide and training examples for Excel
Reference guide and training examples for Excel.
What is a Spreadsheet?
A spreadsheet package is a computer database program created specifically to help in
organising and processing tabular information. The spreadsheet stores information in rows (across the screen) and columns (down the screen), forming a worksheet (the Excel term for a spreadsheet).
Spreadsheets are most commonly used to manipulate figures. They can be used for accounting, cash flows, budgeting, forecasts, etc. Any job that involves the use of numbers can be done on a spreadsheet. However, they are also useful for storing large amounts of textual information which can be sorted and filtered to retrieve specific data more easily.
The biggest advantage that a spreadsheet has over other methods of manipulating data is its ability to constantly update figures without the user having to do any calculations. Once a spreadsheet is set up, its calculations will always be correct and any changes in data are automatically updated.
Spreadsheets can also take raw data and present it in an attractive way, with
formatted tables and graphs.
Basic screen layout.
On starting, Excel displays a blank workbook. A workbook is a file that can contain many worksheets but usually has 3 as a default.
The Title Bar is the top line of the Excel screen. It shows the application
and the name of the workbook that is on the screen.
The second line is the Menu Bar, containing menus from File to Help.
The next line is called the Toolbar. The left side is the Standard Toolbar
and the right side the Formatting Toolbar. These have buttons to click on
to quickly select an action or basic feature.
Note: many buttons are hidden, click on the chevrons to display them. Point the mouse away from the chevrons to any button on the toolbar to hide the extra buttons again.
The Status Bar runs along the bottom of the window. This displays
messages as tasks are performed. Check that the current message is Ready.
Formatting.
To Format is to change the way cells look in order to improve the overall
appearance of a worksheet. The Format Menu is used to achieve the full range
of formatting. However, the Formatting Toolbar provides buttons to format
more quickly.
Formatting can change the style, size, colour, alignment and number format of
text and numbers, the border style, colour and pattern of cells.
A useful tool is wrapping text to fit a cell, found in the alignment folder.
Numbers can be displayed in various styles. This can be important in making information appear clearer on screen.
Select Format / Cells, and then the Number tab – look through the options.
Note: in the section Negative numbers there are options to display any negative values in red.
There are also buttons on the toolbar to Increase Decimal places and Decrease Decimal places, by one place for each click.
Example:
Select the cell B5, type in the number 5.72 and click the Decrease Decimal button. The number is now displayed with one decimal place.
After applying number formats, cells may display #######. This means that
the number is too big for the cell. The column must be widened. .
To widen a column to fit to the largest entry, place
the cursor between A and B in the column heading as before and double
click. The column on the left is automatically adjusted to the widest entry
in that column.
Percentages.
A percentage is just a fraction displayed differently. Percent means per
hundred. 20% is 20/100 or 0.2. Percentages are easy using a computer
because the computer performs the calculation.
There is a Percent Style button, that changes a decimal to a percentage.
Filling A Range
A range is a rectangular selection of cells. Just as a single cell is identified by a
cell reference, ranges are identified by the cells of their outer limits, e.g. the four
cells B2, B3, C2 and C3 is the range B2:C3.
Ranges are selected by clicking the mouse button and dragging to highlight a
range of cells (known as Click and Drag).
Selected ranges can be increased and decreased from the first cell in the
range. Hold down the key and select cell E7. The range is
increased. Select cell C2 while holding down and the range is
decreased.
An entire row or column can be highlighted for data entry by clicking on the column letter (top ), or row number (left margin)
Ranges can be quickly filled with data using the Fill Series command.
Example:
On a blank worksheet enter a number in B2 then select the range B2:F2.
Select Edit | Fill then select Right.
The number will now fill the range B2:F2.
Select the range C2:C6 and select Edit | Fill | Down. This range is now
filled with the number.
Enter 3 in B9. Select the range B9:F9. Select Edit | Fill | Series. The
Series dialog box is displayed.
Ensure the Step value is 1 and the Type is Linear. Click on OK. The
range is filled with a series of numbers.
Select the range B9:B16 then select Edit | Fill | Series.
Select the Growth option from Type and in the Step value enter 2.
Click on OK. The range is filled with a series doubling from cell to cell.
Dates can also be filled using this command.
The Fill Handle ( the small square in the bottom right of the highlighted cell) quickly copies or increments data to a range of cells. If the
data is in the form of days, dates, time, months or text with a number then the
Fill Handle will increment as it fills, otherwise the data will be copied.
It is only possible to drag in one direction, i.e. across a row or down a column.
Select two adjacent cells (with sequential numbers entered in them), then drag to fill the range, and the sequence will be continued.
Creating basic formula in worksheets.
Spreadsheets help in the processing of numbers. They store information in
rows (across the screen) and columns (down the screen). A cell is the
intersection of a row and column. All the cells form the worksheet .
Example:
On a blank worksheet, move the mouse pointer to cell B3 and click. The Current or Active cell is now B3. It has a dark border. Each cell is identified by the column letter
and row number, which form the intersection, e.g. the cell formed where
column D and row 8 meet is known as cell D8. Look for the active cell reference, which is shown in the Formula Bar. When entering information into a cell, notice that the text appears in the formula bar aswell as in the cell.
Formula
A calculation in Excel is called a Formula.
All formulas begin with an equals = sign, followed by the calculation. The
calculation consists of cell references or numbers separated by a mathematical
symbol (+ add, - subtract, * multiply, / divide), e.g. =A1+A2
Formulas are used to calculate answers from numbers that are entered on to a
sheet. Changes made within referenced cells will cause the formulas to be
recalculated.
Good practice: when writing/selecting a formula, use the green tick (enter box) to complete your work in the cell, rather than just moving to another cell – this may change the information in the formula. Equally, if incorrect information is typed, press the ‘x’ button to cancel the action, and the worksheet will revert to any existing information in the cell rather than blanking out the cell by moving out of, and back into the cell.
Example:
Start a new worksheet, move the cell pointer to B2 and type in 66. Move to
cell B3 and type 34.
Move to cell B4 and enter the formula to add the contents of cells B2 and
B3 by typing in =B2+B3 .
Click in cell B4 and note the cell display of 100 and the formula in the Formula bar.
Start a new worksheet, or delete the information above.
In cell B3 type 6, in cell D3 type 8, in cell B5 type 10 and in cell D5 enter the formula =B3+B5-D3. Click on the green tick.
Double click on cell D5 to check the formula.
This shows that unconnected cells can be added together/included in a formula. Indeed, cells from different worksheets in a workbook can included in a formula by double-clicking a cell whilst completing a formula in the formula bar.
When more than one symbol is used in a formula, then the order becomes
important, e.g. A1+A2/A3. Excel performs calculations in this order: Brackets
over Division, Multiplication, Addition and finally Subtraction (the BODMAS
theory)
Example:
. Using the information in the table below, set up a worksheet.
| |A |B |
|1 | | |
|2 |Sell price |10 |
|3 |Buy price |6 |
|4 |Sold |4 |
|5 |Profit | |
To calculate the profit, click on cell B5 and type the formula =B2-B3*B4
and click on the green tick to complete the formula.
The answer is given as -14, this is because the multiplication is carried out
before the subtraction, due to the BODMAS theory.
Click on cell B5 and re-enter the formula, this time add the brackets
around the subtraction part of the formula, the old formula is replaced by
the new.
Check the answer displayed. Profit per item 10-6, which is 4, multiplied
by the number sold, 4, giving 16.
Close the worksheet without saving the changes.
Brackets are added to force Excel to perform calculations in a different order to
normal, as it will perform the calculation in the brackets first no matter what
mathematical operation is used.
Autosum
The most common formula is addition. This calculation has been simplified by
the use of a Function called AutoSum, on the Standard Toolbar.
AutoSum adds the contents of cells automatically.
Example:
Start a new workbook.
In the following cells, enter the following numbers:
| |A |B |C |D |
|1 | | | | |
|2 | |2 |1 |3 |
|3 | |3 |5 |2 |
|4 | |4 |2 |1 |
|5 | | | | |
Click in cell B5 and click on the AutoSum button, on the Standard Toolbar.
Press or the green tick to complete the entry. The answer should be 9.
Repeat this in cells C5 and D5.
AutoSum also adds cells across. Click on cell E2 and click the AutoSum
button. Press to complete the entry.
Functions.
Functions are specialised formulas that make a calculation easier. Just as
AutoSum uses Sum to total a range of cells, other functions such as Average,
Min, Max and Count can be used to simplify calculations.
Functions can be typed directly into a cell, e.g. =SUM(A1:B6) or Paste
Function can be used to insert the formula structure e.g. =SUM(), prior to
selecting a range of cells to complete the formula.
Example:
On a clear sheet, enter a column of 10 numbers, starting in B3.
Add the numbers by typing =Sum(B3:B12) in cell B13.
Enter numbers into the cells D2, D3, D4 and D5. Click on cell D7.
Click on the Paste Function button, to display the Paste Function
dialog box. Click on each of the Categories to see all available functions.
Click on the category Math & Trig and then in Function name select
Ave. An explanation of the function is given.
Click on the OK button and a prompt for a range to be summed appears.
Click and drag the range D2:D5. Move the dialog box if necessary.
Click on OK. The function is entered into the worksheet and the result is
displayed.
Sorting
Select a table by clicking the box in the top left hand corner. Choose Data and then Sort from the menu.
From the Sort box decide which column to sort and whether to sort in ascending or descending order. Click on the OK button.
Alternatively, the sort ascending and descending icons can be used for speed.
When there is a huge list of data you may wish to locate records that you know or suspect are there.
For this use the Edit and then Find button. The * can be used as a wild card. For example *smith will find all ‘smiths’ (goldsmiths, blacksmiths, etc).
Filters.
Used to filter information in a worksheet, to show on screen only the data you have requested to see from everything included in the sheet.
To set up a filter, select the relevant cell (usually in the header row), select
Data / Filter / Autofilter. To then select a group from within a column, click on the arrow at the top of the column of your choice, and select from the options in the drop-down box revealed. This will reduce the table to show your selection only.
Selecting Ranges – it is possible in numerical fields to select data within a certain range rather than just a specific number. For example in a census database, select all the men aged between 21 and 60. First use the autofilter to select men from the sex column, then click on the down arrow next to Age. Click on custom… in the custom box and select the greater than sign [>] from the first box, and type 21 in the text box. In the second box select the less than sign [ ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- reference guide and training examples for excel
- introduction to excel formulae and functions
- excel webjunction
- capacity plan template
- using excel for calculation activities ks1 2
- excel functions
- advanced excel statistical functions formulae
- excel chapter 2 formulas and functions performing
- microsoft excel tutorial 1 courses
- exercise 1 understanding regression output
Related searches
- sba quick reference guide 2019
- hospice pocket reference guide pdf
- excel reference guide free pdf
- vba language reference guide pdf
- python reference guide pdf
- python quick reference guide pdf
- reference examples for research paper
- cause and effect examples for kids
- quick reference guide template word
- sql reference guide pdf
- strengths and weaknesses examples for reviews answers
- reference letter examples for coworkers