Microsoft Office Excel 2010 PDF

Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali Microsoft Office Excel Introduction: Microsoft office Excel 2010 is one of t...

Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali Microsoft Office Excel Introduction: Microsoft office Excel 2010 is one of the office pack programs, its application program provides mathematical/ logical / statistical working environments. As it provides a network of cells with dimensions so huge that each cell retains one value with the possibility to apply the functions of ready-build or manually built functions. The program also provides filtering (capability to show only specific values)/ sorting / search (to detect the existence of specific value and perform the required operation on it). The Main Interface: To run Microsoft office Excel 2010, click on> start> all program> Microsoft office Excel 2010. The main interface of the program is as follows: Default work file title: automatically, the new file will be named Book1. If the file is stored with another name the new name will appear here. Command bar: is a bar that contains many commands for easy access, such as save, back, print preview and more). Ribbons: classified tools lists, each ribbon contains tools classified under the title of that bar. Tools, workspace: a hug network of cells with invisible borders called working sheet. When printing each cell can contain a value (numeric, symbol, date...etc.) each cell have tittle 1 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali representing the column litter followed by a number representing the line (i.e D4, B12....etc.). Each new worksheet contains three pages automatically. Build up table:  To enter a value in a cell, simply click on the cell and type the desired data.  To move to another cell, use the arrows in the keyboard, or press the pointer on the new cell.  If we write longer text than the length of the cell, we will notice that the text will hide under the adjacent cell and to enlarge the length in the cell proportional to the length of the text, click on the borderline and drag to the right as shown: If we write a numeric number longer than the length of the cell, the number will automatically be converted to Scientific Exponential for example Form 998877665555 to 9.98878E+11 and to return no. to its original formula:  To merge cell click on home> click on  To select entire column or entire row or entire work page 2 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali  To format text: Click on >home to add underlined or make the text italic or make bold text. change font type (Andalus ،Times New Roman ، Arial,..) font size, font color, cell Color, text color, Vertical and horizontal Alignment respectively, text direction in cell, copy, past, and cut.  To add boarders to cell > select cells > home> and then:  For more formats on cells: select cell> right click> formats cells> border> change specification according to below figure: 3 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali  To add column or row to the work sheet > select the liter of the column that placed after the column that wound to be add (or on number for row)> click on insert.  To delete column or row select column (by pressing on liter of column)> right click>delete (for row pressing on number of row and do the same).  To delete sheet or change the name, make copy, hide, and add sheet to work file> put pointer on sheet title > right click> choose the desired edit:  Change the address direction (make the page from right to left)> click on page layout> click on icon > address directions will be reversed. 4 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali Series: If we want to fill a line of cells with numbers from 1 to 100, it is not logical to fill each cell separately, where Microsoft office excel provides a way to fill a row of cell with relational values (serial no., multiples of 5, days of week, months of year, etc.) example to write numbers from 1 to 7 first select cell > write 1 and on the other cell 2 > select both cells> put the pointer on the edge and drag. In the same way we could create any serial 1. (2,4 , 6,8...) we write1 and 2 then drag with pressing. 2. (5, 10, 15, 20,...) write 5and 10 and do the same. 3. (Sun, Mon.,Tue.,Wed.,...) write Sun and Mon then drag with pressing. Function: The program provides the possibility to create a function so that changing any of the input of this function will update the output function without having to rebuild it. The program provides two ways to create a function: 1. Create a function manually Add new column under the name of sum 5 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali To create sum function manually: a. Click the pointer on the cell to which you want to write the result (i.e cell F3in this ex.) b. Write the formula of the combination in terms of cell addresses stating with a sign = which we write: E3+ D3+ C3> enter the result will appear for first students. Then select and drag: c. If any of the grades change, the total values will be updated automatically. In the same way we can add new column to calculate average, where the equation will be = (C3+ D3 +E3)/3, (inside G3 cell). 2. Using ready- made functions: The program provides a hundreds of mathematical, statistical and logical functions such as sum, average, max, min, conditional if statement. To use it click on cell > click on formulas> math & trig > choice wanted function (ex. Sum): Then new window will appear> click on red arrow> the new window will pressed > select cells from C3 to E3> press enter key twice: 6 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali Select on F3 > press on the black edge and drag to F5 to full the other cells with sum functions: To print or save file do the same as word document. When print excel page the border of cell will not appear so that if we want to print a specific area of work page, we must first define the desired boundary by clicking on view> select page break preview then blue borders appear on the work page as shown: Then drag the blue border with the mouse and select the area to be printed, as shown: 7 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali To return to normal case click on view> select normal.  Format cells to change the cell content format, select the cell (or range of cell) right click> format cells> number and select the required formulas below:  Find to search for thing in sheet click on home> icon > find:  Replace click on home > > replace : 8 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali  Sort to arrange a list of values (either alphabetically for words or ascending/ descending order for number), select cells > home> > choose A→Z or Z→ A.  Filter for example assume there is a table representing ten employees in a company (sequence, names, department, and service months). To arrange table use filter click on the column of title name > home> > click on filter arrow: 9 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali To nominate employees who have two criteria, work in HR department and have twenty months service or more. Click on month of service filter> choice > number filter> greater than or equal to as shown below:  Freeze panes When dealing with huge tables, we will encounter the problem of missing the address line as we move within the table, we use the aspect fix feature to resolve this problem. Can fix row, column, or both. 11 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali For example, if you want to install the address line in previous table click on the line below that wont to be fixed > view> freeze panes> the address line will be installed no matter what moved through the work sheet Statistical chart The program provides the translation of table data into a statistical chart in an easy way, so that if any values in the table modify the statistical chart will modify. To draw chart represent student degrees with days select cell > click on insert> chose shape 11 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali To change the color of one column click on it > home> select the new fill color and can click on the text and change the font size and color. To add values above the column right click on the column to which values are to be added> add data labels: To draw chart for student names with sum first select chart from > an empty chart is added to page > right click on chart> select data as shown below: 12 Refinery eng. 1st year Computer lect# 3 Assist lect. Sara Ali Click on ok the chart will appear: 13