Microsoft ExcelMicrosoft Excel
I. Excel Worksheet Basic Information
A. Parts of the worksheet:
Menu bar - has a drop down menus that provide access to all excel feaures
Toolbar - buttons and selection boxes that provide shortcuts to the menue options.
Tab buttons/sheet tabs - lets you move from one worksheet to another.
Scroll bars - allow fast movement within a sheet.
Status bar - displays the status of the keys
Title bar - tells you the title of the of the file
Main grid - your work area, composed of rows and columns
columns are labeled with letters A, B
rows are labeled with numbers 1, 2
intersection forms a cell
cells are identified by combining the cell letter and row number. Eg. A1.
B. Moving around the worksheet:
To move around the worksheet you can use the ff:
· Scroll bar - scroll area one screenful at a time
· Scroll buttons for large areas
· arrows - one row/column at a time
· cursor keys to make adjacent cells active
· click on a cell to make it active
· f5 and enter a cell reference to make it active
II. Entering Data
Excel always start with a blank worksheet. Entering data is simply a matter of selecting a cell and then typing a value. You can enter number or text in any cell. It will deterimine what sort of data is being entered.
A. Data in Excel can be:
· If a value starts with a number, - or + sign, and contains only numeric characters, it is treated as a numeric value.
· If a value starts with the = symbol , Excel assumes that it is a formula.
· If a value starts with any other character, Excel assumes that it is a piece of text.
*Numeric values are always aligned at the right side of the column. Text entries are placed on the left.
B. To enter data simply:
Click on a cell to make it active
· Type the data. As you type text appears in the cell and in the formula bar.
· press enter when complete. The cursor moves down to the next cell. You can also complete an entry by pressing one of the cursor keys or clicking on another cell.
· If you start making an entry on the wrong cell press the Esc. and no change will be made.
III. Text and number Formats
If you want a numeric value to be treated as text, start by typing an apostrophe. This code will not be displayed in the cell.
A. Use apostrophe when:
· the entry looks like a number
· the entry starts with =, +, - but is not a formula.
· when you want a real apostrophe to be displayed at the start of the label ( start by typing two apostrophes)
Numbers can be
· Integers - whole numbers without decimal points.
· Decimals - shown with as many decimal places as needed, and with zero to the left of the decimal point for numbers less than one.
· Exponential - used for very large and very small numbers
· ### - indicates that the cell is too narrow to show a complete number - the number is still in the memory of the computer.
*Numbers are displayed using the General format unless chaged. Here Excel selects the most appropriate style in each individual case, using the least number of decimal places possible. This option leads to an untidy display.
IV. Editing
A. To correct mistakes simply:
· Click on the cell that contains the incorrect value. The value appears on the fomula bar.
· To replace all of the values, just type a new entry no need to delete the existing value.
· To change the value click on the fomula bar or F2 then use the delete or backspace to edit.
B. To delete the contents of a cell:
· Click on the cell
· Press the Delete Key (shortcut for edit|clear|all)
· The spacebar may also be utilized to blank a cell. However, this can cause problems in formula that refers to the cell. contents are considered as text.
V. FILE OPERATIONS:
A. Saving the worksheet
Until a file is saved, it still resides in the RAM of the PC. If you leave Excel w/o saving the file, the OS crashes, power failure, it may be lost.
Steps:
· Click on the file menu bar then on Save in the menu. A dialog box appears.
· Select a Drive/Directory
· Type a filename. Follow DOS rules max of 8 numbers and letters w/o spaces; extensions are automatically added.
· Click OK or enter
· (you can also use the toolbar shortcuts) next time you save - you need not supply a name.
· You are given an opportunity to attach a summary info to the file
· To save quickly use CTRL S
B. Loading a worksheet
· Select open from the file menu then choose directory and file
· Another option is to click on the open button on the toolbar
· Most recently used files are at the bottom of the file menu
· You can have more than one worksheet open at the same time.
VI. FORMULA
A formula is used to calculate the value of a cell from the contents of other cells; may be used to calculate totals, differences, percentages, averages, minimum and maximum values.
Starts with = sign, followed by an expressions which refer to other cells or constant values, and connected by operations.
A. Steps in writing the formula:· Click on the cell where you want the results of the fomula to be displayed
· Type the fomula; starting with =,
· Press enter. The results of the calculation is shown in the cell.
B. Steps to edit a fomula
· click on the cell conating the formula
· click on the fomula bar of F2
· make the corrections
· press enter
* when referring to numbers do not use commas to separate thousands
C. Calculation options
Excel recalculates the worksheet whenever you make a change to any item of data.
D. Operations and order of calculation
^ - exponent
* - multiplication
/ - division
+ - addition
- - subraction
- - negation (infront of a number)
% - percentage after a number
* use brackets to change the order of operation. Operations infront of a bracket are calculated first. Calculations start from the innermost pair of brackets and work outwards. Every opening bracket must have a closing bracket.
E. Range - a group of cells that form a rectangle and it is identified by the cells from the top left to the bottom right corner of the rectangle, separated by a colon. Only one range can be active at a time.
1. Identifying a range:
· Move the cells from the upper left and drag to the bottom right. The range is highligthed.
· From keyboard hold the shift key while using the cursor keys.
2. Naming a range
Names - label that the user attaches to any cell or range of cells for easy reference. Helpful in formula. Max of 255 char. Letters numbers, caps, underscore, but should start with a letter.
Steps:
· Mark the range to be named
· Select the name option from the insert menu
· Select define from the sub menu
· Enter name and enter
· Names that you create are listed in the name box.
Labels can also be used as names
· Mark the range to be named, including the labels
· Select create from the name in the insert menu
· Identify the location of the names then enter
F. FUNCTIONS - special routines that may be used insside a formula to perform pecific tasks. Followed by a pair of brackets containing one or more arguments. If there is more than one argument, these should be separated by commas.
Sum
· Click on the cell
· Start typing =SUM(
· You can drag the cells then type the closing bracket then enter.
· You can also use the autosum button.
Excel has as much as 200 functions and under different categories eg. General, finance, statistical etc.
· Type the formula up to the point where the function is required.
· Type the function name and an opening bracket
· Mark the first argument (cell or range of cells)
· If there is a second argument place ,
· After the last argument type a closing bracket and press enter
Use the function wizard (fx) Provides a dialog box where the different functions are listed.
· Type the formula to where you need a function
· Click the function wizard
· Selcet the function
· Click next to bring the next dialog box
· Click on the finish and the function will be added to the formula
· Finish typing the formula and press enter.
IF function - conditional function that returns twoo possible values, based on the result of some comparison.
Possible arguments:
· The comparison to be performed
· The value to be returned if the result of the computation is true
· The value to be returned if the result of the computation is false
Operations:
< less than
<= less than or equal
> greater than
>= greater than or equal
= equal
<> not equal
Comparison expressions can be combined with the ff:
AND True if both operations are true
OR True if either or both expressions are true
G. Copying a formula
Excel does not make an exact duplicate of the formula but it assumes that the formula will consider the cells the same relative positions.
1. Steps:
· Click on the cell to be copied
· Press CTRL + C or the Copy command in the edit menu or the copy toolbar
· Click on the cell where the copy is to appear, or drag to highlight a range of cells.
· Press enter or select edit|paste and the formula will be repeated.
To copy a range of cells the same procedure is to be utilized but must highlight the range first.
2. Relative and Absolute References
When a formula is copied to another cell the the cell references are automatically updated so that they refer to the cells on same relative position - relative cell reference
When you want the formula to refer to the same cell regardless of where it is copied this is known as absolute reference. To effect this a $ must be placed in front of the part to be retained.
When some parts of the formula is to be retained and some changed this is known as mixed cell references.
4 possible combinations for references:
a. for a relative refererence both parts are changed. eg. A1 becomes B2
b. For absolute reference nothing is changed: $A$1 stays as is whereever it is copied.
c. if the column is fixed only the row number changes $A1 to $A2
d. if the row is fixed only the column letter change A$1 to B$1
H. Moving Formula
* instead of copying cells you can also move cells to new locations.
Steps:
Click on the cell or group of cells
Pres CTRL+X or cut|edit
Click on the cell or upperleft of the cells that is to contain the cells
Press Enter
VII. NUMBER FORMATS
Numbers inputed in Excel assume the general format - format that best suits them as individual cases: as whole numbers, with limited number of decimal places
The format for a cell or a range of cells is altered using the format cells command
A. How to change the number format:
· Click on the cell/cells to be formatted
· Select cells from the format menu, then click the number tab
· Choose the category that applies to the type of data being formatted
· Click on a format Code then OK
B. Common Format options:
· Number - formats include integers, and various levels of decimal places
· Currency - formats are similar to number formats but with a currency symbol in front of the display
· Date and time - alternatives for display of date and time amounts
· Percentage -
· fraction
· Scientific formula
· Accounting
· Decimal Places
· Use the toolbar - increase decimal or use format options.
* Stored and Displayed Values - inputs are stored in the memory with 15 significant figures. Sometimes displayed values are not the same as the stored or actual values. All calculations use the stored value.
To match stored and display values choose tools|options|calculation|precision as displayed option
C. Dates and times
Excel can handle dates and times in calculations because it stores any date or time in the numeric code rather than text.
If you enter a date in general format it is displayed as dd/mm/yy. When converted to integer it will be formatted as a number that represents the date.
The earliest date for excel is 1/1/1900 and the latest is 31/12/2078.
Time is also handled by excel as numbers from 0 to 1.
VIII. FORMATTING A WORKSHEET
A. Column Width and Height
Default column width for excel is 9 characters. If the contents of the cell will not fit, the default may be adjusted.
Steps:
· Select format|column|standard width to set a new default width
· Click on a cell or mark a range then select format|column|width to change the width of a series of column.
· Select Format|Column|Auto Fit Selection to make the width of the column automatically adjust to fit the longest item of data in each case.
· The quickest way to adust the width of a column is to drag the right hand edge of the column boarder. To make a column fit the widest item of data double click on the column boarder.
· The height of the rows are also adjusted in a similar way to allow bigger fonts, multi-line text, or a bigger gap between sections of the worksheet.
· Select format|row|height. Or format|row|autofit.
· The easiest way to change the height of a row is to drag the bottom edge of the row boarder.
B. Alignment
When data is first entered:
Text is placed on the left of the cell and numbers are placed on the right.
To modify this default you can use the Format | Cells command and proceed to the alignment tab. Or use the toolbars as shortcut.
* if the text is typed in a cell that is too narrow, and the cell to the right is empty, the text will spread over both columns on the display.
* Text can be made to wrap over multiple lines within a cell using the wrap text option from the alignment tab. Any words that will not fit in one line will be carried over to the next line. And the height of the row adjusts to the number of lines.
* Text can also be justified.
* you can also center across columns by typing the text at the left hand column and higlighting the range of cells then click on the center across columns.
C. Inserting and deleting rows and columns
As much as possible you should design your layout before inputing.
You can delete or move the contents of the cells and you can also insert or delete rows or columns if needed.
New rows are inserted above the current row and new columns are inserted to the left of the current column with the use of the insert menu.
Rows and columns can be erased from the worksheet using the edit | delete command. After a deletion, all rows and columns are renumbered and all formula are revised so that they will still refer to the same cell.
D. Inserting and deleting cells
You can insert cells using the Insert | cell command. When you insert a cells all cells to the right or below move left or down to make spaces for them. The contents of the other rows and columns are not affected.
You can delete cells using the Edit | delete command. When you delete cells the other columns and rows also move to fill in the space.
E. Protecting data
You can protect data that you have inputed from alterations by choosing the protect from the tools menu. However, you must first identify the the areas of the sheet where changes are to be allowed.
Steps:
· Mark cells in which data entry is to be allowed.
· Select format | cells | protection and clear the locked box.
· Select tools | protection | protect sheet
E. Changing fonts
Font refers to the style of the text. Defined by 3 characteristics.
· Typeface - Arial, times etc.
· Pointsize - 12, 18
· Style - bold, italic, etc.
Steps:
· Select cells or range to which the new font is to be applied.
· Click on the arrow to the right of the typeface box.
· Select the point size in a similar manner.
· Click on the style button bold, etc.
IX. Multiple worksheets
Excel allows you to have several worksheets in a single file. This is referred to as a workbook. A workbook can contain different types of sheets such as:
· Worksheet - for data, formula and calculations
· Chartsheet - for the display of graphs and charts
· Macro, dialogue and visual basic sheets - for creating short programs to automate the opeations of the worksheet.
* the sheets are shown in the sheet tabs at the bottom of the window. The sheet tabs may be renamed by doing the following steps:
· Double click on the sheet tab at the bottom of the window
· Enter a new name, up to 31 chars long
· Click on the OK
A. Starting a new sheet
To start on a new worksheet simply click on the any of the sheet tabs at the bottom of the window. You will be presented with a completely blank sheet. This sheet has no connection with the other sheets in the workbook other than they being saved and loaded together.
* you can switch from one sheet to another by simply clicking on the the sheet tabs at the bottom of the window. When the tab is clicked the corresponding sheet is displayed on the window and the sheet name is higlighted on the tab.
* When you have a number of sheets you will not be able to see all the tabs. The tabs can be scrolled to the left or right by the small tab buttons. Clicking on the buttons only moves the tabs.
Multiple windows
You can display more than one worksheet from the file at a time by opening new windows onto the workbook. Select window|new window command
Changing Sheets
You can change sheets, insert new sheets, copy sheets, change the order of sheets and delete sheets.
· To insert new sheet, click on the sheet tabs and the select Insert | worksheet. The worksheet is inserted on the left of the selected sheet and becomes the active sheet.
· To delete an unwanted sheet click on the sheet tab of the sheet to be deleted and select Edit | Delete sheet.
· To move a sheet to a different position in the workbook click on the sheet tab and drag it across tabs to its new position.
· To make a copy of the sheet in the same workbook, click on the sheet tab, press and hold ctrl then drag the tab to the point where you want to insert the duplicate sheet.
Formula across Worksheets
A formula can refer to cells and ranges in other worksheets or even covering several worksheets.
· To refer to a cell in another sheet, precede the cell reference with the sheetname and an exclamation mark. eg. =Ferdi!A10;
· To refer to a range of cells in another sheet type the ff. Ferdi!A10:A20
· To refer to a 3dimensional block of cells that cover 2 or more consecutive sheets the range of sheets is placed before the exclamation mark and the range of cells after it. eg. Ferdi:Ferdi2!A10:A20
* if the sheetname uses characters other than numbers and letters the sheet reference must be enclosed in single quotes.
Chartwizard
Excel provides a wide range of options for displaying any set of data as a chart or graph -
3 broad categories of charts.
· bar - have a rectangular bar for each item of data the ht/length of the bar proportional to the value.
· Graphs - plots a sereis of points with each point being determined by a pair of coordinates. Consecutive points are joined by line.
· Pie - circle divided into segments. The values in the data set are totalled so that each value can be calculated as a percentage of the total. The sizes of the segments are proportional to these percentages.
· The other charts are combinations of these.
The chart can be placed anywhere in the worksheet.
After creating a chart any aspect of the chart can be changed.
Another chartsheet can also be created.
The chartwizard is used to make a chart.
Steps:
· Mark the range of data for which the chart is to be created - row of labels above and in the left may be included.
· Click on the chart wizard button
· A series of dialog boxes will appear depending on the version of excel; simply choose the option that suits your needs.
Editing the chart:
After the chart has been drawn it can be moved or resized.
· To move the chart - click on it and drag it to a new position
· To resize - click on the chart and drag the handles at the side and corners
· To delete - click on the chart and press delete.
* The chart is laid over the the top of the sheet, it does not affect any data on the underlying sheet.
* when you select the chart by clicking it; a small chart wizard toolbox is displayed so that you can modify any feature of the chart.
Steps:
· Double-click on the chart
· Double-click on any item: text, labels, grid lines, grida background, legend, etc.
· In the dialogue box that appears, make any changes that are needed to the appearance of the chart.
* if you change any of the values in the original data set, the chart is immediately updated accordingly.
Changing Chart Data:
You can change the range of data on a chart any time such as:
· New rows can be added or deleted
· The range can be extended or contracted to show more or less data
· The range can be changed completely so that it covers a different set of data
· Data sets can be removed from the chart.
These changes may be done by clicking on the Chartwizard button on the toolbox and repeating two of the steps.
Chart Sheets
You can create a separate chart sheet in your workbook if you want. The chartsheets behaves similarly to a workbook and is saved with the workbook.
Steps:
· Mark the range to be charted
· Select chart from the insert menu and then select as new sheet.
· Complete the chartwizard dialogue boxes.
Text boxes
Text boxes may be used to add a piece of text in the worsheet aside from those which are written in the cells. These boxes are independent of the main sheet.
Steps:
· Click on the text box button
· Mark the area to be covered by the text box
· Enter the text.
Sorting data
Rules:
· All numeric values come before text values and are sorted in order of magnitude.
· Numbers entered as text are sorted by character
· Characters other than numbers and letters are first ion ther sort order, followed by numeric characters and thern alphabetic characters.
· Upper and lower case letters are treated the same
Steps:
· Simple sorting involves marking the range and then clicking on the sort button (ascending or descending)
· For a sophisticated sort; select Sort from the Data Menu.
· Choose the columns on which the sort is to be based.
· For each column select ascending or descending order
· Specify if there is a header row.
· This sort method allows you to choose up to three sort keys.
Printing
Page Set-up
The page setup option allows you to determine how the file will be printed. It has four categories: page, margins, header/footer, sheet.
Page tab - allows the modification of page settings:
· Orientation - portrait - tall, landscape - sideways printing.
· Scaling - allows the adjustment of the size of the sheet to be printed.
Maybe fixed percentage eg. 80%.
May fit the contents of the sheet to one page.
· Paper size - allows user to change the size of paper on which the sheet is to be printed. Eg. 8.5X11 in.
· Print quality - defines the dpi etc; dependent on the printer.
· First page number - determines the page number for the first page. Auto defaults to 1.
Margin Tab - allows you to determine the blank spaces at the top, sides and bottom of the page.
· Top and bottom margins are inclusive of headers/footers.
· The center on page option allows you to center the sheet either vertically or horizontally or both.
Headers/Footers - text that appears at the top/bottom oof each page.
· Text may be printed at the top or bottom of every page. They may be printed on the left, right, or center of the page.
· Standard headers/footers may be choosen from a list box, or you can create your own by using the custom button.
Sheet settings allows you to modify the following:
· Print area - the range to be printed
· Print titles - rows and columns that will be repeated on every page if the sheet does not fit one page.
· Print options allow the printing of features such as gridlines, headers etc.
· Page order - used when the sheet is too wide/long to fit one page. Decides if printing is left to right then top to bottom, etc.
Print Preview
· The page to be printed may be viewed from the file|print preview. The page as it will appear when printed will be displayed on the screen.
· Options are available for this menu are :
· Next to see next page
· Previous to see the previous page
· Zoom to enlarge or contract the display
· Print move you to the file|print command
· Setup to load page set-up options
· Margins to enable you to change your margins
· Close to return to the worsheet.