Technology University

Building and Constructions Engineering Department

Computers Principles-First Class

MICROSOFT EXCEL 2007

By:Lubna Zaghlul

2012

1
 Microsoft Excel 2007
Microsoft Excel is an electronic Spreadsheet program that enables you to store,
manipulate, and chart numeric data .Researchers, statistician, and businesspeople use
spread sheet to analyze and summarize mathematical, statistical , and financial data.
Excel enables you to create and modify worksheets, and chart sheets. A work sheet is
divided into vertical columns and horizontal rows. The rows are numbered and the
columns are labeled from A to Z, then AA to AZ and so on to column IV .the
intersection of a columns and a row is called a cell. each cell is given a cell address,
like a post office box number, consisting of its column letter followed by its row
number ,for example,B4,F7,C9.

◌ٍ Starting Microsoft Excel

• Start all programsMicrosoft excel
• Desktop icon
• My computer C: Program files  Microsoft Office  office 10  Microsoft
Excel

The extension of worksheet files are (*.xls) and chart sheet files are (*.cls)

2
Office button
New: it is used to create new workbook.

Open: if you have a workbook you previously saved that you would like to work on,
you must open the file first, before you can make any changes.
Opening an Existing Workbook
• Open the Office Button , and select Open, or click the open button .
The open dialog box appears.
• If the file is not located in the current folder, open the Look In drop-down list
box and select the correct drive and folder.
• Select the file you want to open in the files and folders list.
• Click Open to open the currently selected workbook.

Save: the first time you save a workbook, you must name it and specify a location
where it should be saved.
Saving workbook
• Open the Office Button and select Save, or click the save button on
the title bar. The save as dialog box appears.
• Type the name you want to give the workbook in the File Name text box.
• To save the file to a folder or drive, select a location using the Save in list.
• Click Save to save your workbook.

Save as: it is used when you want to save a copy of workbook under a different name
or different location.
Saving a Workbook under a new Name or Location
• Select the Office Button and select Save as. The save as dialog box
opens, just as if you were saving the workbook or the first time.
• To save the workbook under a new name, type the new filename over the
existing name in the File Name text box.
• To save the new file on a different drive or in a different folder, select the drive
letter or the folder from the Save In list.
• Click the Save button or press Enter.
3
Print : to print the workbook, follow these steps:
Print Workbook
• To print a portion of the worksheet, select the range of cells you want to print,
to print only a chart, click it.
• Select the Office Button and then select Print (or press Ctrl+P) .the print
dialog box appears.
• Select the options you would like to use:
1. Print range- enables to print one or more pages.
2. Print what- enables you to print the currently selected cell, the selected
worksheet, or the entire workbook.
3. Copies – enables you to print more than one copy of the selection, worksheet
or workbook.
4. Collate- enables you to print a complete copy of the selection, worksheet, or
workbook before the first page of the next copy is printed.
• Click ok

Print preview: to preview a print job, select the Office Button and then select
Print overview, then click the print preview button . The workbook appears in the
same format that it will be in when sent to the printer.

Close: you can easily close the current workbook. click Office button then select
close or Click the close (X) button in the upper right corner of the workbook.

Undo: You can undo just about any action while working in Excel, including any
changes you make to a cell's data. To undo a change, click the Undo button on the
title bar. (Or press Ctrl+Z).
Redo :You can also undo an undo. just click the Redo button on the title bar .(or
press Ctrl+Y).

4
 Home Commands

Clipboard: include
Cut: moving data is similar to copying except that the data is removed from its
original place and placed into the new application.
Move Data
• Select the cell(s) you want to move.
• Click the Cut button, or (Ctrl +X) or (select Home, Clipboard, Cut).
• Select the first cell in the area where you want to place the data, to move the
data to another worksheet, change to that worksheet.
• Click Paste , or (Ctrl +V) or ( select Home, clipboard, Paste).

Copy :when you copy data, you create a duplicate of data in a cell or range of cells.
Copy Data
• Select the cell(s) that you want to copy, you can select any range or several
ranges if you want.
• Click the Copy button or (Ctrl +C) or (select Home, Clipboard, Copy) .
The contents of the selected cell(s) are copied to the clipboard.
• Select the first cell in the area where you would like to place the copy.
• Click the Paste button or (Ctrl +V) or (select Home , Clipboard, Paste).
Excel inserts the contents of the clipboard at the location of the insertion point.

Clipboard: you can use office clipboard to store multiple items that you cut or copy
from an excel worksheet. You can then paste or move these items within Excel or to
other office application.

Using the Clipboard

• Select the Home, and then select Clipboard. The Clipboard task pane appears.
Any items that you have cut or copied appear on the clipboard.
• To paste an item that appears on the clipboard, click in a cell on the worksheet,
and then click the item on the clipboard. It is then pasted into the selected cell.

Format Painter: copy formatting from one place and apply it to another.
Double – click this button to apply the same formatting to multiple places in document.

5
Font: include
Font : font enables you to choose from several font attributes, you can control the
font, the font style, and other character attributes such as strikethrough, superscript,
and shadow.
Working in the Font
• To change the font, click the Font drop-down box and select the new font by
name.
• To change the Font Style to italic, or bold italic, make the appropriate selection
in the font style box. ,
• To change the size of the font, select the appropriate size in the size scroll box.
.use to increase the font size, and to decrease the font size.
• For underlining, click the Underline Style drop-down box and select an
underlining style.
• To change the color of the font, click the Font Color drop –down box and
select a new color, and select to change background of selection text.
• To add borders to the cells use button.
• As you make the various selections in the font dialog box, a sample of what the
text will look like appears in the preview box at the bottom of the dialog box.
After you have made all your selections in the font dialog box click ok.

Border : you can add borders to selected cells or entire cell ranges.
Adding boarders to Cells
• Select the cells around which want a border to appear.
• Select the Home, Font and chose Borders. The format cells dialog box
appears.
• Click the Border tab to see the boarder options.
• Select the desired position, style (thickness) and color for the border.
• Click ok or press enter.

6
Alignment: include
Alignment: you can change both the vertical and the horizontal alignment of data in
the cells.
Alignment text in Cells
• Select the cells or range you want to align.
• Select the Home and then select Alignments. The format cells dialog box
appears.
• Click the Alignment tab.
• Choose one of the options (horizontal, vertical, orientation, wrap text, shrink to
fit, merge cells) to set the alignment.
• Click ok.

Wrap text: make all content visible within a cell by displaying it on multiple lines.

Merge & Center: Joins the selected cells into one larger cell and center the contents
in the new cell.

7
Number: include
Number: the numeric values that you place in your Excel cells are more than just
numbers. They often represent dollar amounts, a data, or a percentage. Excel format
cells dialog ox offers a wide range of number formats and even allows you to create
custom formats.
Numeric Formatting Options
• Select the cell or range that contains the values you want to format.
• Select the Home and select Number. The format Cells dialog box appears.
• Click the Number tab. the different categories of numeric formats are
displayed in a category list.
• In the Category list, select the numeric format category you want to use.
• Click ok to assign the numeric format to the elected cells

General: choose how the value in the cell are displayed: as percentage, as a currency,
as a date and time, etc.
Accounting number format ($): choose an alternate currency format for the selected
cell.
Percent Style (%): display the value of the cell as percentage.
Comma Style (,): display the value of the cell with a thousands separators.
Increase decimal : show more precise values by show more decimal places.
Decrease decimal : show less precise values by show fewer decimal places.

8
Styles: include:
Conditional formatting: its allows to specify that certain results in the worksheet be
formatted so that they stand out from the other enters in the worksheet.
Apply Conditional formatting
• Select the cells to which you want to apply the conditional formatting.
• Select the Home, style and select Conditional Formatting; the conditional
formatting dialog box appears.
• Be sure that Cell Value is selected in the condition 1drop – down box on the
left of the dialog box.
• In the next drop down box to the right, you select the condition. The default is
between. Other conditions include equal to, greater than, less than, and other
possibilities. Use the drop – down box to select the appropriate condition.
• After selecting the condition, you must specify a cell or cells in the worksheet
that excel can use as a reference for the conditional formatting. for example if
you select less than as the condition, you must a cell in the worksheet that
contains a value that can be used for comparison with the cells that you
applying the conditional formatting to, click the shrink button on the
conditional formatting dialog box, you are returned to the worksheet, select the
reference cell for the condition.
• Click the expand button on the conditional formatting dialog box.
• Now you can set the formatting that will e applied to cells that meet your
condition. Click the Format button in the conditional formatting dialog box
and select the formatting options for your condition in the format cells dialog
box then click ok.

Format as table: quickly format a range of cells and convert it to table by choose pre
defined table style.

9
 Cell Styles: quickly format a cell by choosing from pre defined styles.

Cells: include
Insert: it is use to insert a single cell or a group of cells.
Inserting Cells
• Select the area where you want the new cells inserted. Excel inserts the same
number of cells as you select.
• Select the Home and then select Cells, choose insert. The insert dialog box
appears.
• Select Shift Cells Right or Shift Cells Down.
• Click OK. Excel inserts the cells and shifts the adjacent cells in the direction
you specify.

Rows and Columns :you might need to add rows or columns within the worksheet.

Inserting Rows and Columns

• To insert a single row or column, select a cell to the right where you want to
insert a column or below where you want to inset a row.
• To insert multiple columns or rows, select the number of columns or row you
want to insert. To insert columns, drag over the column letters at the top of the
worksheet. To insert rows, drag over the row numbers. For example, select
three column letters or row numbers to insert three rows or columns.
• Select the Home, Cells, and then select Insert Rows or Columns. Excel inserts
rows above your selection and columns to the left of your selection. the
inserted rows or columns contain the same formatting as the cells

Sheet: when you create a new workbook, it contains three work sheets. You an easily
add additional sheets to a workbook.

10
Insert sheet
• Select the worksheet that you want to be to the right of the inserted worksheet.
• Select the Home, Cells, then choose insert.
• Select sheet. Excel inserts the new sheet to the right of the previously selected
sheet.

Delete: you can remove cells and then shift surrounding cells over to take their place.
Delete data
• Select the cell or range of cells you want to remove.
• Select the Home, cells and choose Delete. The delete dialog box appears.
• Select shift cells left or shift cells up to specify how the remaining cells in the
worksheet should move to fill the gap left by the deleted cells.
• Click ok. Surrounding cells are shifted to fill the gap left by the deleted cells.

Format: change Row height or column width or protected sheet or hide sheet.
Row heights: adjusting row heights as fallow: select the row or rows and then select
the Home, Cells, Format point at Row Height .in the row height dialog box appears
type in the row height and the click OK.

Column width: adjusting column width as fallow: select the column or columns and
then select the Home, Cells, Format point at Column width .in the column width
dialog box appears type in the column width and the click OK.

Protection: it used to locking cells in a worksheet.

Hiding workbooks, worksheets, columns, and rows:

Hide Data
• To hide a row or column in a worksheet, click a row or column heading to
select it ,the right click within the row or column and select hide, from the
shortcut menu that appears. The row or column will be hidden. To unhide row
or column, right click the border between the hidden item and rows or column
that are visible, and then select unhide from the shortcut menu.
• To hide a worksheet, click its tab to select it. Then open the Home, cells,
format menu, and select hide sheet. To unhide the worksheet, select Home,
cells, format, sheet, and then unhide. Select the worksheet, to unhide n the
unhide dialog box that appears and then click ok.

11
Editing: include
AutoSum: adding a group of cells is probably one of the most often used calculations
in an Excel worksheet. Because of this fact, Excel makes it very easy to place the
SUM function into a cell. Excel provides the Auto sum button on the standard
tool bar.
Using Auto Sum
• Select the cell where you want to place the SUM function.
• Select Home, Editing, Click the Auto sum button .
• If the range of cell address that Auto sum selected is incorrect, use the mouse
to drag and select the appropriate group of cells.
• Press the enter key. Auto sum calculate the total for the selected range of cells.

Fill: to create a custom series using fill.

Entering a Custom Series
• Enter the first value in the series into a cell.
• Enter the second value in the series into the next cell. For example, you might
enter 10 into the first cell and then 20 into the second cell. This lets excel know
that the increment for the series is 10.
• Select both cells by clicking the first cell and dragging over the second cell.
• Drag the fill handle of the second cell to the other cells that will be part of the
series. excel analyzes the two cells, sees the incremental pattern, and recreates
it in subsequent cells.

Clear: you can delete only the formatting of a cell without deleting its contents. The
formatting of a cell includes the cell's color, border style, numeric format, font size,
and so on.
Clear Data
• Select the cells you want to clear.
• Open the Home, Editing, and point at Clear. The clear submenu appears.
• Select the desired Clear option:
All: Removes the cell contents and other attributes.
Formats: Removes the cell formatting only.
Contents: Removes the cell contents only
Comments: Removes the cell comments only.

Sort: you can sort a list ascending (0 to 9, A to Z), or descending (Z to A, 9 to 0)

orders.
Sorting a list
• Select a cell in the field you want sorted.
• Click sort ascending or sort descending buttons from home, Editing,
sort.

12
Filter: filtering is an excellent way to find a subset of records to work with that
match a particular range of values.
Filtering a list
• Select a cell within the active worksheet list.
• Choose Home, Editing, Filter.
• Click down arrow beside the desired field name.
• Select a value or a custom filtering option.
• Choose, Home , Editing, Filter, show all to remove the filter.

Example: work sheet below includes student name and degree, to display only
successful students execute the following:

1. select the cells which we want to filtering it

2. Select Home, Editing, Filter, combo box appears beside the field.
3. click arrow beside degree ,subtotal appears, select customize from it , dialog
box appears ,select appropriate conditions from it, in our example, the degree
is greater than 50 then click or equal 50 ,then click ok.
4. Only records contain successful students is appears.

13
Find and Select: Suppose you've entered particular label or value into the worksheet
and find that you have consistently entered it incorrectly. Great way to change
multiple occurrences of a label or value is using Excel's Replace feature, you can
locate data in the worksheet and replace it with new data.
Using the Find and Replace Feature
• Select the Home, Editing, and then select Find. The Find and Replace dialog
box appears.
• Type the text or value that you want to find into Find What text box.
• Click in the Replace With text box and type the text you want to use as
replacement text.
• Click Find Next to find the first occurrence of your specified entry.
• When an occurrence is found, it is highlighted. Click Replace to replace only
this occurrence and then click Find next to find the next occurrence.
• If you want to find all the occurrences, click Find All, you can also replace all
the occurrences of the entry with Replace All.
• Click OK.

14
 Go to: you can also quickly go to a specific cell address in a worksheet using the go
to feature. Press ctrl+G or select Home, Editing, Go To. Type the cell address you
want to go to into the reference box, and then click ok button.

Insert Commands

Tables: create a table to manage and analyze related data

Illustrations: it is used to insert picture or clipart or shapes or smart art into the
worksheet.

Charts: charts enable you to create a graphical representation of data in a worksheet

.you can use charts to make data more understandable to people who view your
printed worksheets. The following are the major chart types and their purposes:
Column – similar to the bar chart, use this chart type to emphasizes the difference
between items.
Line – use this chart type to emphasize tends and the change of values over time.
Pie –use this chart type to show the relationship among parts of a whole.
Bar - use this chart type to compare values at a given point in time.
Area – similar to the line chart, use this chart type to emphasize the amount of change
in values over time.
Scatter – similar to a line chart, use this chart type to emphasize the difference
between two sets of values.

15
 Chart-Design Commands

Type: to change chart type:

Data: include:
Switch row/column: By default Excel assumes that your different data series are
stored in rows. You can change this to columns if necessary by clicking the Switch
row/column option.

Select Data: next, Excel asks whether the selected range is correct (you can select the
data you want to chart before click chart wizard). You can correct the range by typing
a new range or by clicking the shrink button and selecting the range you want to use.

16
Chart layouts: to change options for the chart.
• Titles: you can add a chart title on the titles tab.
• Axis: display major and minor tick in x-axis and y- axis.
• Gridline: display horizontal and vertical lines on the chart.
• Legend: you can delete the legend by clicking the legend tab and deselecting show
legend.
• Data Label: add data labels (labels that display the actual value being represented
by each bar, line, and so on) by clicking the data labels tab.
• Data Table: to add data table to the chart.

Chart styles: to choose chart style.

Chart location: finally, Excel asks whether you want to embed the chart (as an
object) in the current worksheet (or any other existing worksheet in the workbook) or
if you want to create a new worksheet for it. Make your selection and click the finish
button. Your completed chart appears.

17
 Chart - Layout Commands

Current selection: include

Chart elements: select chart element so that you can formatted.
Format selection: lunch the format dialog box to fine tune formatting for the
selected chart element.
Reset to match style: clear the custom formatting of the selected chart elements back
to the overall visual style applied to the chart.

Insert: include:
Picture: insert a picture from file.
Shape: insert readymade shapes such as rectangles, circles, arrows, lines, flowchart
symbols and callouts.
Text box: A text box is a movable, resizable container for text or graphics. You can
use text boxes position several blocks of text on a page or to give text a different
orientation from other text in the document.

Labels: include:
• Chart Titles: add, remove or position the chart title.
• Axis title: Add, remove or position the text use to label axis.
• Legend: Add, remove or position the chart legend.
• Data Label: Add, remove or position the data label.
• Data Table: add data table to the chart.

Axes: include:
Axes: change the formatting and layout of each axis.
Gridlines: turn gridlines on or off.

Background: include
Plot area: turn the plot area on or off.
Chart wall: format the chart wall.
Chart floor: format the chart floor.
3-D rotation: change the 3-D viewpoint of the chart.

Analysis: include:
Trend line: add a trend line to the chart.
Lines: add other lines to the chart, such as drop lines or high –low lines.
Up/down bars: add up /down bars to the chart.
Error bars: add errors bars to the chart.

Properties: give the chart friendly name to help you to refer to it when ordering
objects on the sheet or writing VBA code.

18
 Chart-format Commands

Current selection: include

Chart elements: select chart element so that you can formatted.
Format selection: lunch the format dialog box to fine tune formatting for the
selected chart element.
Reset to match style: clear the custom formatting of the selected chart elements back
to the overall visual style applied to the chart.

Shape styles: include:

Shape Fill: fill the selected shape with solid color, gradient, picture, or texture.
Shape Outline: specify the color, width, and line style, for the outline of the selected
shape.
Shape Effects: apply visual effects to the selected shape. Such as shadow, glow,
reflection, or 3-D rotation.

Word art styles: include:

Text Fill: fill the text with solid color, gradient, picture, or texture.
Text Outline: specify the color, width, and line style, for the outline of the text.
Text Effects: apply visual effects to the text. Such as shadow, glow, reflection, or 3-D
rotation.

Arrange: include:
Bring to front: bring the selected object in front of all other objects so that no part of
it is hidden behind another object.
Send to back: send the selected object behind all other objects.
Selection pane: show the selection pane to help select individual object and to change
their orders and visibility.
Align: align the edges of multiple selected objects.
Group: group objects together so that they can be treated as a single object.
Rotate: rotate or flip the selected object.

Size: include:
Shape height: change the height of the shape or picture.
Shape width: change the width of the shape or picture.

Example: Enter the following data

19
 • Draw the relation between Product and Quantity.
• Draw the relation between Product and Price.
• Draw the relation between Price and Quantity.
• Draw the relation between Product and Quantity and Price.

Example: Enter the following data, and then draw the expenses over three months.

Example: Enter the following data Powers=(50,100,………1000) and Cubes Side

=(10,12………48) then calculate Reaction=Power/Cube Area and draw the relation
between Power and Reaction.

Example: enter the following data : X=( 0,45,90,135,180,225,270,315,360) then

calculate Sin(X),Cos(X),Tan(X).then draw Chart illustrate the relation between all
variables.

Example: enter the following data: X= (5, 10 ….50) and calculate X2, X3 then draw
the relation between variables.

20
Links: create a link to a web page, a picture, e-mail address, or a program.

Text: include
Text box: A text box is a movable, resizable container for text or graphics. You can
use text boxes position several blocks of text on a page or to give text a different
orientation from other text in the document.
Header / Footer: the contents of a header (at the op of the page) or footer (at the
bottom of a page) repeat automatically for each page that is printed.
Word art: insert decorative text to the document.
Signature line: insert a signature line that specifies the individual who must sign.
Object: insert an embedded object.
Symbol: Special characters and symbols are characters that can't be found on
keyboard and that are not part of what is considered to be the standard character set.

21
 Page Layout Commands

Themes: include:
Themes: change the overall design of the entire document include, colors, fonts, and
effects
Colors: change the colors for the current theme.
Fonts: change the fonts for the current theme.
Effects: change the effects for the current theme.
Page setup: include
Margins- the margins tab allows to select the top, button, left, and right page
margins, and to center the worksheet both horizontally and vertically on a page.
Orientation: Print orientations (prostrate or landscape).
Size: use to specify paper size.
Print area: you don’t always have to print an entire worksheet; instead, you can
easily tell excel what part of the worksheet you want to pint by selecting the point
area.
Breaks: a page break is the point at which one page ends and another begins.
Background: choose an image to display as background of the sheet.
Print Titles: specify rows and columns to repeated at each printed page.

Scale to fit: include:

Width: shrink the width of printed output to fit a maximum number of pages.
Height: shrink the height of printed output to fit a maximum number of pages.
Scale: stretch or shrink the printed output to percentage of its actual size.
22
Sheet options:
Sheet right to left: switch the sheet direction so that the first column is on the right
side.
Gridlines: show lines between rows and columns in the sheet to make editing and
reading easer.
Headings: show rows and columns headings.

Arrange: include:
Bring to front: bring the selected object in front of all other objects so that no part of
it is hidden behind another object.
Send to back: send the selected object behind all other objects.
Selection pane: show the selection pane to help select individual object and to change
their orders and visibility.
Align: align the edges of multiple selected objects.
Group: group objects together so that they can be treated as a single object.
Rotate: rotate or flip the selected object.

Entering Formula: you can enter formulas in one of two type: by typing the enter
formula, including the cell address, or by typing the formula operators and selecting
the cell reference.
Type Formula
• Select the cell where you will place the formula.
• Type an equal sign"=" into the cell to begin the formula.
• Enter the appropriate cell references and operators for the formula the formula
also appears in the formula bar as you type it. The cells that you specify in the
formula are highlighted with a color board.
• press enter when you have finished the formula, and Excel calculate the results.

Enter formula by selecting cell address

• Click in the cell where you will place the formula.
• Type the equal sign"=" to begin the formula.
• Click the cell whose address you want to appear first in the formula. You can
also click a cell in a different worksheet or workbook. The cell address appears
in the cell and in the formula bar.
• Type a mathematical operator after the value to indicate the next operation you
want to perform. The operator appears in the cell and in the formula bar.
• Continue clicking cells and typing operators until the formula is complete.
• Press enter to accept the formula and have Excel place its results into the cell.
Some of operators that you can use and how you would use them in simple formula.
Order of operators: the natural order of mathematic operators as follows:
1. Exponent (^) and calculations with parenthesis.
2. Multiplication (*) and division (/).
3. Addition (+) and subtraction (-).
You can force the precedence of an operation by using parentheses.

23
 Operator Simple formula
3
A1 =A1^3
A1+B1 = A1+B1
A1-B1 = A1-B1
A1×B1 =A1*B1
A1◌ِ /B1 =A1/B1
A2+B2×C2 =A2+B2*C2
(A2+B2)×C2 =(A2+B2)*C2
A1+B1 =(A1+B1)/(C1-D1)
C1-D1
A1+B1-D1 =A1+B1/C1-D1
C1
A1×B1/2 =A1*B1/2
A1×B1 =A1*B1/(C1*D1)
C1×D1
A1+B1+C1 =A1+B1+C1/3
3
A1+B1+C1 =(A1+B1+C1)/3
3
B1 (1+C2) = B1*(1+C2)
B1x1+C2 =B1*1+C2
A12+B12+C12+D12 =A1^2+B1^2+C1^2+D1^2
(A1+B1+C1+D1)2 =(A1+B1+C1+D1)^2
A1+B1 =(A1+B1)/C1
C1
‫׀‬A1‫׀‬ =ABS(A1),example: abs(8)=8,abs(-9)=9
Integer (B1) =INT(B1),example: int(7.1)=7, int(7.9)=7
Integer (C1) =INT(C1),int(-9.2)=-10,int(-11.1)=-12
D1 =D1^0.5 or D1^(1/2) or SQRT(D1)

− b + b2 − 4 * a * c =(- B1 + sqrt (B1^2-4*A1*C1))/(2*A1)

2*a
− b − b2 − 4 * a * c =(- B1- sqrt (B1^2-4*A1*C1))/(2*A1)
2*a
eA1 =exp(A1)
1- ea1/1+ea1 = (1-exp(A1))/(1+exp(A1))
ln(A1) =log (A1)
log(A1) =log(A1)/log(10)
Sin(A1) =Sin(A1*3.141592/180)
Cos(B2) =Cos(B2*3.141592/180)
Tan (C3) =tan(C3*3.141592/180)
Sec(A1) =1/cos(A1*3.141592/180)
Csc(B2) =1/Sin(B2*3.141592/180)
Tan -1(B1) =1/tan(B1*3.141592/180)
Log B1 –Sin A1 +C1 x D1 =Log(B1)/Log(10)- Sin (A1*3.14/180) +C1*D1
24
Ln D1 – Log D12 = Log(D1)- Log(D1^2)/Log(10)

Sin(C4) =sin ( C4*3.14/180)

Cos (A12) = Cos( (A1*3.141592/180)^2)
Cos2(A1) =Cos (A1*3.141592/180)^2
Sin (A1)+Cos(B1)/Tan(C1) =(Sin(A1*3.14/180)+Cos(B1*3.14/180))/Tan(C1*3.14/180)
Signature(A1) =sign(A1), =1 if a is positive or -1 if a is negative
Signature(D1) =sign(D1),example: sign(7)=1,sign(-7)= -1
random (A1) =RAND(1) ,its return random value between
0,1,example:0.87965431
A + A −3 =log(((A1)+abs(A1-3))/(2*A1+5))
Ln
2A + 5
A 2 + 1 − 2A =(Sqrt(A1^2+1) - 2*A1)/abs(A1)
A
A.TanA =A1*tan(A1*3.14/180)/(A1+tan(A1*3.14/180)
2
( A + TanA ) ^2
1 =(log(A1)/log(10)+1/A1-A1^3)/(10-A1)
LogA + − A3
A
10 − A
A+ A−3 =log((A1+abs(A1-3))/(2*A1+5))
Ln
2A + 5
A3 =sqrt(cos(A1^3/(5*(A1-1))*3.141592/180))
Cos
5(A − 1)
( A − B − 1 / 2) 2 =(abs(A1-B1)-1/2)^2/(A1+B1)
A+B
A + 1 − 3A =(sqrt(A1+1)-sqrt(3*a1))/(A1^2-2)
A2 − 2
SinA 3 =sqrt(sin(A1*3.14/180)/(3*(A1-1)))
3(A − 1)

2 − x2 =sqrt((2-X1^2)/sqrt(a1+(B1^2-1)^5))
Y=
a + (b 2 − 1) 5

ln 3e x − 9 =log(abs(3*exp(X1)-9))/sqrt(3*X1^2)
Y=
3x 2
 1 − e − 2x  =log((1-exp(-2X1))/(1+x1))/log(10)
Y = log 
 1+ x 
 
Y = x 2 + a b − c2 − ex =X1^2+A1*abs(b1-c1^2)-Exp(X1)
Sin2(X) + Cos(X3) =sin(x1*3.14/180)^2+cos((X1*3.14/180)^3)

25
 Formula Commands

Insert Function: Functions are ready made formulas that perform a series of
operations on specified range of values .every function consists of the following three
elements:
1. The "=" sign, which indicates that what follows is a function (formula).
2. The function name, such as sum, that indicates which operation will be
performed.
3. A list of cell address, such as (A1:H1), which are to be acted upon by the
function. Some functions can include by commas such as (A1, B1, and H1).

Some of Excel functions that probably used most often in worksheets:

Functions Example
Average =Average(B4:B9)
Count =Count(A3:A7)
If =if ( A3>1000,"bonus",no bonus")
Max =Max(B4:B10)
Min =Min(B4:B10)
Sum =Sum(A1:A10)

Using the status bar Auto Calculate feature.

Using feature that Excel calls, you can view the sum of the column of cells simply by
selecting the cells and looking at the status bar. the values in the selected cells are
added. You can also right – click the auto calculate area of the status bar and choose
different formulas, such as average, minimum, maximum, and count.

26
Using function feature: the insert function feature leads you to through the process
of inserting a function and specifying the appropriate cell address in the function.
Use function feature
• Click in the cell where you want to pace the function.
• Click the arrow button next the auto sum button and select more functions.
The insert function dialog box appears.

• To search for a particular function, type a brief description of what you wan to
do in the search for a Function box. then click Go to conduct the search. you
also can select a function category ,such as financial or statistical ,using the
select a category drop – down box. in either case a list of functions is provided
in the select function dialog box.
• From the functions list, select the function you want to insert. then click ok. the
function Arguments dialog box appears. this dialog box allows you to specify
the range of cells that the function acts upon .

• Next you must enter the range of cells that will be acted upon by the function.
Click the collapse button on the far right of the Number1 text box in the
Function arguments dialog box, this return you to the worksheet.
• Use the mouse to select cells that you want to place in the function. Then click
the expand button on the right of the function arguments dialog box.
• Click ok. Excel inserts the function and cell addresses for the function into he
selected cell and displays the result.
27
Example: Enter the following data:

• Calculate sum of columns B, C, D, and E.

• Calculate the Average of columns B, C, D, and E.
• Calculate the Max and Min value in each row.
• Calculate the sum and average of each row.
• In each columns, If the values >=100.print "Over", else print "Below".
• Count the number of values in each row.

Example: Enter the following data:

• Calculate sum of columns Rice, Tea, Sugar.

• Calculate the Max and Min value in each column.
• Calculate the Average of columns Rice, Tea, Sugar.
• Count the number of values in each column.
Example: Enter the following data:

• Calculate sum of column grade.

• Calculate the Average of column grade.
• Calculate the Max and Min value in grade.
• In column grade, If the values >=50.print pass, else print poor.
• Count the number of values in grade.
28
Matrix Multiply: the condition to multiply matrices is number of columns of first
matrix equal to the number of rows in second matrix.
Matrices Multiply
• Select the cells you want the result display on write "=" in first cell.
• Click Insert Function from Formula, choose MMULT.
• The dialog box appear, select the range of matrix by drag the mouse ,and
select the range of second matrix in the same way.
• Click Ctrl + Shift + Enter, the result display in selected cells.

Example: to calculate the result of multiply two matrices, first size (2×2) and the
second size (2×3).

Matrices Inverse: to calculate matrix inverse fallow these steps:

Matrices Inverse
• Select the cells you want the result display on write "=" in first cell.
• Click Insert Function from formula , choose MINVERSE
• The dialog box appears; select the range of matrix by drag the mouse.
• Click Ctrl + Shift + Enter, the result display in selected cells.

Example: calculate the matrix inverse for the matrix size (2×2).

Function Library: include:

Auto sum: display the sum of the selected cells directly after the selected cells.
Recently used: brows and select from list of recently used function.
Financial: brows and select from list of financial functions.
Logical: brows and select from list of logical functions.
Text: brows and select from list of text functions.
Date &time: brows and select from list of date & time functions.
Lookup &references: brows and select from list of lookup &references functions.
Math & trig: brows and select from list of math &trigonometry functions.
More functions: brows and select from list of statistical, engineering, cube, information
functions.

Define names: Create, edit, delete, and find all the names used in the workbook.

29
Formula Auditing Checking the formula:
• Trace precedents: we select the result, and want to know from where it is,
rows appear to cells that caused the result.
• Trace dependents: we select the cells that special perform formula on it and
Excel display row to cell contain this formula result.
• Remove all rows: to remove all rows that appears.
• Show formulas
• Error checking: formula errors caused error results, There is some tools help
in define error such:
####: column is thin
#value: can’t convert string to value.
#div/0: divided by zero.
#name: names cannot define to it.
#N/A: invalid formula.
#ref: invalid reference.
• Evaluate formulas: lunch the evaluate formula dialog box to debug a formula
by evaluating each part of formula individually.

Example: to know the reference of the value (30) we select A5and select trace
precedents, arrow appears to four values caused the result.
.

Calculation: specify when formulas are calculated.

Data Commands

Get external date: import data from Microsoft access data base or from web pages or
from text files, or from other data source.

Connections: display all data connections for the workbook.

Sort & filter: sort & filter a list of data.

Data tools: include:

Text to columns: separate the contents of one excel cell to into separate columns.

Remove duplicate: remove duplicate rows from the sheet.

30
Data Validation: it is used to check correction of data. For example ,we want enter
data values between 0 and 100 ,when enter number out of data ,Excel display
message , "the number you entered is not valid", that is the number is refused .or
we want only positive data ,when enter negative number it s refused.

Consolidate: where you need to combine revenues from several regions or calculate
productivity static's several departments. Excel consolidation tools allow you to
better manage, organize, and present your information.

Goal Seek: also called backward solving, lets you begin at the finish line and then
work backward to solve a problem.
Using Goal Seek
• Select Tools, Goal Seek.
• In the Set Cell text box, enter the cell containing the outcome formula.
• In the To Value text box, enter the desired target value.
• In the By Changing cell text box, enter the input cell that Excel may change to
achieve the target value.
• Click ok.
Example: to change the summation of four values from 20 to 30, In the set cells text
enter the cell A5, in the to value text box, enter 30, in the by changing cell text box,
enter A1.after using goal seek the value of A1 change to 15, the value of A5 change
to 30.

31
Out line: include:
Group: tie a range of cells together so that they can be collapsed or expanded.

Sub group: ungroup a range of cells that were previously grouped.

Subtotal: subtotaling describes the process of grouping records together in a

worksheet list and inserting new rows for computing average ,sums, minimums, and
maximums.
Create Subtotals
• Sort the list by the field for which you want to calculate subtotals.
• Select any cell in the worksheet list.
• Select data, outline, and then subtotals.
• Specify the sort field that contains the groupings, the deseed summery
function to calculate, and the fields you want to subtotal.

Example: to create subtotals to sporting sales ,the first step is sorting the data rows
by sport type ,then select subtotal ,dialog box appears , select appropriate function
from it, the summation of columns which contain sports sales ,summation each type
alone, then all summation.

32
 Review Commands

Proofing: include:
Spelling :Microsoft office provides several ways to check spelling and grammar.
Checking the spelling
• Click the Spelling button (or select Review, Proofing, Spelling), the
spelling dialog box appears. Excel finds the first misspelled word and displays
it at the top of the spelling dialog box, a suggested correction appears in the
suggestions box.
• To accept the suggestion in the suggestion box, click change, or click change
all, to change all occurrences of the misspelled word.
• Click ok to confirm that the spelling check is finished.

Research: open the research task pane to search through reference materials, such as
dictionaries, encyclopedias, and translations services.

Thesaurus: Suggestions other words with a similar meaning to the word you have
selected.

Translate: translate the selected text into a different language.

Comment: you can add comments to particular cells although the comments are not
really considered cell content these comments allows you to associate information
with a cell ,information that does not appear with the workbook when sent to the
printer.
Adding Comments to Cells
• Click the cell in which you want to place the comment.
• Select Review, Comments, new comment. A comment box appears next to the
cell.
• Type your information into the comment box.
• Click anywhere else in the worksheet to close the comment box.

Changes: include:
Protected sheet: prevent unwanted changes to the data in a sheet by specifying what
information can be change.

Protected workbook: restrict access to the workbook by preventing new sheet from
being created or by granting access only to specific people.

Share work book: allow multiple people to work in a workbook at the same time.

33
 View Commands

Workbook views: include:

Normal: In normal view ,page ,boundaries, headers and footers, backgrounds,
drawing objects, and pictures that do not have the "in line with text" wrapping style
do not appear.

Page Layout: view the document as it will appear in printed page.

Page break Preview: a page break is the point at which one page ends and another
begins.

Custom views: save a set of display and print setting as a custom view.

Full screen: view the document in full screen mode.

Show /hide:
Ruler: show or hide ruler.
Grid lines: show or hide grid lines.
Message bar: show or hide message bar.
Formula Bar: show or hide formula bar.
Headings: show or hide headings.

Zoom: to enlarge or reduce the view of the current worksheet, use the zoom feature.
Simply click the zoom button on the standard toolbar or click View, Zoom and select
the zoom percentage. You want to use from the following: 25%, 50%, 75%, or 200%.

Window: open a new window containing view of the current document.

Micros: record a macro or access to other macro options.

Acrobat Commands

Create adobe PDF: Create adobe PDF from current document.

Create and Email: Create adobe PDF and attach to new email.
Review and Comment: Create adobe PDF and imitate a shared review.

34
Examples
Example: Enter the following data:

• Calculate sum of columns A,B,C,D.

• Calculate the Average of columns A,B,C,D.
• Calculate the Max and Min value in each row.
• Calculate the sum and average of each row.
• In each columns, If the values >=50.print pass, else print poor.
• Count the number of values in each row.

Example: Enter the following data

• Display employee record they work in seines collage.

• Display the female employee only.
• Display all records.
• Display the two maximum salary.
• Display the five minimum salary.
• Display employee record they work before 2000.
• Display employee record in code 1,2.
• Display employee record they work between 2000 and 2003.

Example: Enter the following data

• Calculate the total of each product.(cost * ordered)

• Calculate the adjusted total of each product.(total – discount)
• Summation of adjusted total.

.‫ﺍﺗﻤﻨﻰ ﺍﻥ ﻻ ﻳﻨﺘﺞ ﺍﻻﺻﺪﺍﺭ ﺍﻟﻼﺣﻖ ﻗﺒﻞ ﺍﻻﻧﺘﻬﺎء ﻣﻦ ﻛﺘﺎﺑﺔ ﻩ ﺫﻩ ﺍﻟﻤﺤﺎﺿﺮﺍﺕ‬

35