Microsoft Excel Tutorial

Written by: Engr. Fakhr-e-Alam

Microsoft Excel 2003 (Tutorial) Engr. Fakhr-e-Alam B.Sc (Elec. Engg.) from Pakistan, ECD from China. D.A.E (Elect.) from Pakistan, Diploma in IT from Pakistan.

E-mail:

[email protected]

1st Edition – 2009

TRAINING & DEVELOPMENT SECTION Human Resources & Administration Department M.S Al-Suwaidi Industrial Services Co. Ltd. P.O. Box. 991 Al-Jubail 31951 Kingdom of Saudi Arabia URL: www.suwaidi.com +966 03 3407200 / +966 03 3407201

TABLE OF CONTENTS 1.

Spreadsheet Basics…………………………………………………………...… 6

1.1

Screen Layout…………………………………………………………………… 6

1.2

Title bar……………………………………………………………….. ………… 7

1.3

Menu bar………………………………………………………………………… 7

1.4

Standard Toolbar……………………………………………………………..…. 7

1.5

Other Tools………………………………………………………………...……. 8

1.6

Task Pane……………………………………………………………………..…. 9

1.7

Adding and Renaming Worksheets……………………………………………... 9

2.

Modifying Worksheets………………………………………………………... 10

2.1

Moving Through Cells…………………………………………………………. 10

2.2

Adding Worksheets, Rows, Columns, and Cells………………………………. 10

2.3

Resizing Rows and Columns……………………………………...…………… 10

2.4

Selecting Cells…………………………………………………………...…….. 11

2.5

Moving and Copying Cells………………………………………..…………… 11

2.6

Deleting Rows, Columns, and Cells…………………………………………… 12

2.7

Freeze Panes…………………………………………………………...………. 12

3.

Formatting Cells……………………………………………………....………. 13

3.1

Formatting Toolbar……………………………………………………..……… 13

3.2

Format Cells Dialog Box……………………………………………...……….. 13

3.3

Formatting Worksheet……………………………………………………...….. 14

3.4

Dates and Times………………………………………….……………………. 15

3.5

Format Painter………………………………………………………………….. 15

3.6

AutoFormat………………………………………………………………….…. 15

4.

Sorting and Filling……………………………………………………….…… 16

4.1

Basic Sorts……………………………………………………………...……… 16

4.2

Complex Sorts……………………………………………………………….… 16

4.3

Auto-fill………………………………………………………………………... 17

5.

Page Properties and Printing………………………………………………… 18

5.1

Page Breaks……………………………………………………………….……. 18

5.2

Page Setup……………………………………………………………………… 18

5.3

Margins………………………………………………………………………… 18

5.4

Header/Footer………………………………………………………………….. 19

5.5

Sheet……………………………………………………………………….…… 19

5.6

Print Preview…………………………………………………………………… 20

5.7

Print………………………………………………………………………….…. 20

6.

Formulas and Functions……………………………………………………… 21

6.1

Formulas Definition……………………………………………………………. 21

6.2

Formulas……………………………………………………………………….. 21

6.3

Formulas Bar…………………………………………………………………… 21

6.4

Linking Worksheets……………………………………………………………. 21

6.5

Relative, Absolute, and Mixed referencing……………………………………. 22

6.6

Function Definition……………………………………………………………. 22

6.7

Basic Functions………………………………………………………………… 22

6.8

Arguments……………………………………………………………………… 23

6.9

Operators……………………………………………………………………….. 23

6.10

Operator Order…………………………………………………………………. 24

6.11

Function Wizard……………………………………………………………….. 24

6.12

Entering Multiple Formulas All At Once……………………………………… 25

6.13

Editing & Deleting Formulas…………………………………………………... 25

6.14

AutoSum……………………………………………………………………….. 26

6.15

Errors in Formulas………………………………...…………………………… 26

7.

Charts……………………………………………………………... 27

7.1

Chart Wizard…………………………………………………………………… 27

7.2

Chart toolbar………………………………………………………………...…. 29

7.3

Resizing a chart………………………………………………………………… 29

7.4

Moving a chart……………………………………………...………………….. 30

7.5

Formatting Chart Objects…………………………………………….. ………... 30

7.6

Chart Types…………………………………………………………………….. 30

7.7

An Overview on Formatting Charts and Cells…………………………………. 31

8.

Commonly Used Features………………………………..……… 33

8.1

Comments…………………………………………………................................ 33

8.2

To Viewing Comments………………………………………………………… 33

8.3

Use Go To……………………………………………………………………… 33

8.4

Use Find and Replace………………………………………………………….. 33

8.5

Increase / Decrease Decimals…………………………………..……………… 34

8.6

Protecting your files (password to open)………………………………………. 35

9.

Additional Readings………………………………………...……. 36

9.1

Tab Color………………………………………………………………………. 36

9.2

Hide / Unhide……………………………………………………………..……. 36

10.

Excel Functions Overview……………………………….……….. 37

10.1

Statistical Functions Overview………………………………………………… 37

10.2

Math Functions Overview……………………………………………………... 37

10.3

Information Functions Overview………………………………………………. 38

10.4

Overview of Logical Functions……………………………………………...… 38

11.

Appendix 1: Shortcut Keys…………………………………….... 40

11.

Appendix 2: Important Formulas................................................. 44

Tutorial – I

(MS-Excel)

1. Spreadsheet Basics Microsoft Excel is a spreadsheet program that you can use to organize, analyze and attractively present data such as a budget or sales report. Each Excel file is a workbook that can hold many worksheets. The worksheet is a grid of columns, designated by letters, and rows, designated by numbers. The letters and numbers of the columns and row called labels are displayed in gray buttons across the top and left side of the worksheet. The intersection of a column and a row is called a cell. Each cell on the spreadsheet has a cell address that is the column letter and the row number. Cells can contain text, numbers, or mathematical formulas.

1.1 Screen Layout

Written By: Engr. Fakhr-e-Alam

6

Tutorial – I

(MS-Excel)

1.2 Title bar The Title bar contains the name of the program Microsoft Excel, and the default name of the workbook (Excel file) Book 1 that would change as soon as you save your file and give another name.

1.3 Menu bar The Menu bar contains menus that include all the commands you need to use to work your way through Excel such as File, Edit, View, Insert, Format, Tools, Data, Window, and Help

1.4 Standard Toolbar This toolbar is located just below the Menu bar at the top of the screen and allows you to quickly access basic Excel commands.

Note: If not all the list appears, press on the Toolbar Options button placed at the end of any toolbar, and press on Add or Remove Buttons tag, then select Standard and you will get the possible commands for this toolbar. • New: Select File > New from the Menu bar. The New Workbook pane appears on the right hand side of the screen. Under the title New select Blank workbook. An alternative is to click the New button found on the Standard toolbar to create a new workbook. • Open: Click File > Open from the Menu bar, or click the Open button toolbar to open an existing workbook.

found on the Standard

• Save: To save click on the Save button found on the Standard toolbar. Choose a directory to save the file in. Specify a filename, and then press Save. This is done only the first time you try to save a file. It is recommended that you save your changes every ten minutes. All you have to do is to press on the Save button, or simply go to File > Save. This will update your initially saved file. • Save As: To save a different copy or version: Click on the Save As option in the File menu, and save your document under a different name, or the same name but in a different location. • Print: Select File > Print from the Menu bar, or click the Print button toolbar to print a worksheet. Written By: Engr. Fakhr-e-Alam

found on the Standard

7

Tutorial – I

(MS-Excel)

• Print Preview: Select File > Print Preview from the Menu bar, or click the Print Preview button found on the Standard toolbar to preview the worksheet before it is printed. • Spelling: Use the Spelling button worksheet. • Cut, Copy, Paste, Modifying Worksheets section.

found on the Standard toolbar to correct spelling errors on the

and Format Painter

: These actions are explained in the

• Undo and Redo: Click the backward Undo arrow to cancel the last action you performed, whether it is entering data into a cell, formatting a cell, entering a function, etc. click the forward Redo arrow to cancel the Undo action. To insert a hyperlink to a Web site on the Internet, type the text into a cell • Insert Hyperlink: you want to be the link that can be clicked with the mouse. Then, click the Insert Hyperlink button found on the Standard toolbar and enter the Web address you want the text to link to and click OK. • AutoSum, Function Wizard, and Sorting: Formulas and Functions section.

These tools are discussed in detail in the

• Zoom: To change the size of the worksheet that appears on the screen, choose a different percentage from the Zoom option

found on the Standard toolbar.

1.5 Other Tools a. Formatting toolbar: used to format text, for example font type / size / alignment / color / text indentation. Also, it is used to create bulleted / numbered lists, borders, etc.

b. Drawing toolbar: contains certain commands for drawing shapes, filling colors, etc.

Note: To add or remove a toolbar select from the Menu bar, View > toolbars and then select the toolbar of your choice. A toolbar that is displayed has a check beside it. c. Scroll bars: allow you to browse through a worksheet.

Written By: Engr. Fakhr-e-Alam

8

Tutorial – I

(MS-Excel)

1.6 Task Pane The Task Pane appears each time you start Excel. To display or hide the task pane: From the Menu bar, select View > Task pane. To close it, click on the small X button at the top-left corner. The Task Pane is a dynamic tool found in the Office XP and 2003 suite applications. It allows you to perform certain actions/commands some of which are shortcuts to commands provided by the Menu bar or Standard toolbar. The task pane contains several options: • Getting started: It allows you to connect to the internet to get more information on Microsoft Excel. Moreover, you can open saved files from your local PC and create a new workbook. • Help: in case you are lost and you need some feedback. Under Search for you can directly type your keyword and Excel will provide you with information (on/offline). • Search Results: Allows you to view the result of your previous search under Help. It allows you to enter a new search at the bottom of this pane. • Clip Art: allows you to search the Clip Art Gallery using keywords. • Research: if you are doing a research Excel can provide you with online information. You can choose what type of reference books you would like Microsoft to take into consideration while searching online. • Clipboard: a list of the items you have recently cut, pasted, or copied • New Workbook: you can open a new blank workbook or select one from the existing workbooks available in your local computer, or select one of the templates saved in Excel. • Shared Workspace: you can create a document workspace if you want to share a copy of your document. A workspace also enables you to invite others, assign them tasks, and link to additional resources.

1.7 Adding and Renaming Worksheets The worksheets in a workbook are accessible by clicking the Worksheet tabs in the lower part of the screen. By default, three worksheets are included in the default workbook. To add a sheet, select Insert > Worksheet from the Menu bar. To rename the Worksheet go to Format > Sheet > Rename or right-click on the tab with the mouse and select Rename from the Shortcut menu or double click on the name of the sheet and when it is highlighted you can type in the new name. Press the Enter key after having typed in the new sheet name.

Written By: Engr. Fakhr-e-Alam

9

Tutorial – I

(MS-Excel)

2. Modifying Worksheets 2.1 Moving Through Cells Use the mouse to select a cell you want to begin adding data to and use the keyboard strokes listed in the table below to move through the cells of a worksheet.

Movement One cell up One cell down One cell left One cell right Top of the worksheet (cell A1) End of the worksheet (last cell containing data) End of the row End of the column Any cell

Key stroke Up arrow key Down arrow key or enter Left arrow key Right arrow key or TAB CTRL + Home CTRL + END CTRL + right arrow key CTRL + Down arrow key Edit > Go To (menu bar command)

2.2 Adding Worksheets, Rows, Columns, and Cells • Worksheets:

Add a worksheet to a workbook by selecting Insert > Worksheet from the Menu bar.

To add a row to a worksheet, select Insert > Rows from the Menu bar, or highlight the row by clicking on the row label, right-click with the mouse, and choose Insert.

• Row:

Add a column by selecting Insert > Columns from the Menu bar, or highlight the column by clicking on the column label, right-click with the mouse, and choose Insert.

• Column:

Add a cell by selecting the cells where you want to insert the new cells, Click Insert > Cells > Click an option to shift the surrounding cells to the right or down to make room for the new cells.

• Cells:

2.3 Resizing Rows and Columns There are two ways to resize rows and columns: The first way is to resize a row by dragging the line below the label of the row you would like to resize (up/down). Resize a column in a similar manner by dragging the line to the right of the label corresponding to the column you want to resize. To auto-fit text inside a cell simply double click on the separator line (separating the two columns: the one you are typing in and the one to its right). Or The second way is to click the row or column label and select Format > Row > Height or Format > Column > Width from the Menu bar to enter a numerical value for the height of the row or width of the column. Written By: Engr. Fakhr-e-Alam

10

Tutorial – I

(MS-Excel)

2.4 Selecting Cells Before a cell can be modified or formatted, it must first be selected (highlighted). Refer to the table below for selecting groups of cells.

Cells to select One cell Entire row Entire column

Mouse action click once in the cell click the row label click the column label click the whole sheet button (upper left corner of the labels “empty label”) drag mouse over the cells or hold down the SHIFT key while using the arrow keys

Entire worksheet Cluster of cells

To activate the contents of a cell or to edit it, double-click on the cell.

2.5 Moving and Copying Cells Cutting Cells To cut cells, highlight the cells the select Edit > Cut from the Menu bar or click the Cut button the Standard toolbar.

on

Copying Cells To copy the cell contents first highlight the cell then select Edit > Copy from the Menu bar or click the Copy button

on the Standard toolbar.

Pasting Cut and Copied Cells Highlight the cell into which you want to paste the content, and select Edit > Paste from the Menu bar or click the Paste button

on the Standard toolbar.

Drag and Drop You can drag and drop content between cells. I recommend you use this method if the cells are adjacent to each other. Highlight the cell you would like to move, simply drag the highlighted border of the selected cell to the destination cell with the mouse. But be aware that the Drag-and-Drop method cuts the contents the source cell and pastes it in the destination cell.

Written By: Engr. Fakhr-e-Alam

11

Tutorial – I

(MS-Excel)

2.6 Deleting Rows, Columns, and Cells Rows: select the row by clicking its number, Click Edit > Delete Columns: select the column by clicking its letter, Click Edit > Delete Cells: select the cells you want to delete, Click Edit > Delete

2.7 Freeze Panes If you have a large worksheet with column and row headings, those headings will disappear as the worksheet is scrolled. By using the Freeze Panes feature, the headings can be visible at all times.

1. Click the label of the row that is below the row that you wish to keep frozen at the top of the worksheet. 2. Select Window > Freeze Panes from the Menu bar. Note: To remove the frozen panes, select Window > Unfreeze Panes Freeze panes have been added to row 1 in the image above. Notice that the row number 1 skip as the worksheet is scrolled, row 1 will remain stationary while the remaining rows will move.

Written By: Engr. Fakhr-e-Alam

12

Tutorial – I

(MS-Excel)

3. Formatting Cells 3.1 Formatting Toolbar The contents of a highlighted cell can be formatted in many ways. Font and cell attributes can be added from shortcut buttons on the Formatting toolbar. If this toolbar is not already visible on the screen, select View > Toolbars > Formatting from the Menu bar, or right click on the toolbars area, and select the Formatting toolbar.

3.2 Format Cells Dialog Box For a complete list of formatting options, right-click on the highlighted cells and choose Format Cells from the Shortcut menu or select Format > Cells from the Menu bar. tab - The data type can be selected from the categories listed on this tab. Select General if the cell contains text and number, or another numerical category if the cell is a number that will be included in functions or formulas.

• Number

tab - These options allow you to change the position and alignment of the data with the cell.

• Alignment

tab - Font attributes are displayed in this tab including font name, size, style, and effects. • Font

• Border

and Pattern tabs - These tabs allow you to add borders, shading, and background colors to a

cell. • Protection

tab – Allow you to protect or hide a certain cell in your worksheet.

Written By: Engr. Fakhr-e-Alam

13

Tutorial – I

(MS-Excel)

3.3 Formatting Worksheet 1-Change horizontal alignment of data: a. Select the cells containing the data you want to align. b. Click one of the following: Align Left Center Align Right Justify 2- Change data color: a. Highlight the cells containing the data you want to change to a different color b. Click the Font Color button on the Formatting toolbar to choose the color you want to use. To change the color, press on the arrow on the right side of the box and then select the color you want by clicking on it. 3- Change cell color: There are two ways to change the cell color: 1. Select the cells, click on the Formatting toolbar. 2. Select the cells, click Format > Cells, the Format Cells dialog box appears. Click the Pattern tab, and choose a color. 4- Indent data: a. Select the data you want to indent in the cells. b. Click the Increase/Decrease indent button

on the Formatting toolbar

5- Change alignment of data: Excel automatically aligns data at the bottom of the cell. To change the position of data: a. Select the cell b. Click Format > Cells. Click the Alignment tab, under Vertical choose the way to align the data, click OK to confirm. Or Perform the steps above a & b and find the box labeled Orientation. Double click in the Degrees box and type the number you want your data to rotate by. 6- Add borders to cells You can add borders to cells to enhance the appearance of your worksheet in two ways: a. Click on the arrow beside the Borders icon on the Formatting toolbar then you can choose any border option from the obtained list. b. OR from the Borders list obtained (as above) click Draw Borders (Click the line style you want from the Border toolbar).

Written By: Engr. Fakhr-e-Alam

14

Tutorial – I

(MS-Excel)

3.4 Dates and Times If you enter the date "January 1, 2001" into a cell on the worksheet, Excel will automatically recognize the text as a date and change the format to "1-Jan-01". To change the date format, select the Number tab from the Format Cells dialog box. Select Date from the Category box and choose the format for the date from the Type box. If the field is a time, select Time from the Category box and select the type in the right box. Date and Time combinations are also listed. Press OK when finished.

3.5 Format Painter A handy button on the Standard toolbar for formatting text is the Format Painter. If you have formatted a cell with a certain font style, date format, border, and other formatting options, and you want to format another cell or group of cells the same way, place the cursor within the cell containing the formatting you want to copy, then click the Format Painter button found on the Standard toolbar (notice that your mouse pointer now has a paintbrush beside it). Highlight the cells which you want to re-format. To copy the formatting to many groups of cells, double-click the Format Painter button. The format painter remains active until you press the ESC key to turn it off.

3.6 AutoFormat Excel has many preset table formatting options. You can add these styles by following these steps: 1. Highlight the cells you want to format. 2. Select Format > AutoFormat from the Menu bar. 3. On the AutoFormat dialog box, click to select the format you want to apply to your highlighted table. Use the scroll bar to view all of the formats available. 4. Click the Options... button. This will open the Format to apply section at the bottom of the AutoFormat dialog box to select the elements that the formatting will apply to. 5. Click OK when finished.

Written By: Engr. Fakhr-e-Alam

15

Tutorial – I

(MS-Excel)

4. Sorting and Filling 4.1 Basic Sorts In Excel you can execute a basic descending or ascending sort based on one column. Highlight the cells that will be sorted (make sure you highlight the items with their corresponding data so that information remains intact and no item loses its corresponding data) and click the Sort Ascending (A-Z) button

or Sort Descending (Z-A) button

found on the Standard toolbar.

4.2 Complex Sort To sort by multiple columns, follow these steps: 1. Highlight the cells, rows, or columns that will be sorted. 2. Select Data > Sort from the Menu bar. 3. From the Sort dialog box, select the first column for sorting from the Sort by dropdown menu and choose either Ascending or Descending. 4. Select the second column and, if necessary, the third sort column from the drop-down menus labeled Then by. Make sure before you sort that all the cells contain text or numbers, not formulas, otherwise sorting might not function properly. If the cells you highlighted include text headings in the first row, select the option Header row under the title My data range has. Click the Options…button for special non-alphabetic or numeric sorts such as months of the year and days of the week. Click OK to execute the sort.

Written By: Engr. Fakhr-e-Alam

16

Tutorial – I

(MS-Excel)

4.3 Auto-fill The Auto-fill feature allows you to quickly fill cells with repetitive or sequential data such as chronological dates or numbers, and repeated text. If you want to auto-fill a column with cells displaying the same number or date you must enter identical data in two adjacent cells. Highlight the two cells and drag the handle of the selection with the mouse. The Auto-fill feature can also be used for alternating text or numbers. For example, to make a repeating list of the days of the week, type “Monday” into a cell in a column. Highlight the cell and drag across with the mouse. Auto-fill can also be used to copy functions. In the example below, column A and column B each contain a list of numbers and column C contains the sums of columns A and B for each row. The function in cell C2 would be "=SUM(A2:B2)". This function can then be copied to the remaining cells of column C by selecting cell C2 and dragging the handle down to fill in the remaining cells. The autofill feature will automatically update the row numbers as shown below if the cells are referenced relatively.

Written By: Engr. Fakhr-e-Alam

17

Tutorial – I

(MS-Excel)

5. Page Properties and Printings 5.1 Page Breaks To set page breaks within the worksheet, select the row you want to appear just below the page break by clicking the row's label. Then choose Insert > Page Break from the Menu bar.

5.2 Page Setup The page setup allows you to format the page, set margins, and add headers and footers. To view the Page Setup select File > Page Setup from the Menu bar. Select the Orientation under the Page tab in the Page Setup dialog box to make the page Landscape or Portrait. The size of the worksheet on the page can also be formatted under the Scaling title. To force a worksheet to be printed on one page, select Fit to 1 page(s).

5.3 Margins Change the top, bottom, left, and right margins under the Margins tab. Enter values in the Header/Footer fields to indicate how far from the edge of the page this text should appear. Check the boxes for centering Horizontally or Vertically to center the page.

Written By: Engr. Fakhr-e-Alam

18

Tutorial – I

(MS-Excel)

5.4 Header/Footer Add preset Headers and Footers to the page by clicking the drop-down menus under the Header/Footer tab. To modify a preset Header or Footer, or to make your own, click the Custom Header or Custom Footer buttons. A new window will open allowing you to enter text in the left, center, or right on the page. Format Text – After highlighting the text click this button to change the Font, Size, and Style. Page Number - Insert the page number of each page. Total Number of Pages - Use this feature along with the page number to create strings such as "page 1 of 15". Date - Add the current date. Time - Add the current time. File Name - Add the name of the workbook file. Tab Name – Add the name of worksheet.

5.5 Sheet Click the Sheet tab and check Gridlines box under the Print section if you want the gridlines dividing the cells to appear on the page. If the worksheet is several pages long and only the first page includes titles for the columns, select Rows to repeat at top from the Print titles section to choose a title row that will be printed at the top of each page.

Written By: Engr. Fakhr-e-Alam

19

Tutorial – I

(MS-Excel)

5.6 Print Preview Select File > Print Preview from the Menu bar to view how the worksheet will be printed or press the Print Preview button from the Standard toolbar. Click the Next and Previous buttons at the top of the window to display the next pages and click the Zoom button to view the pages closer. Make page layout modifications needed by clicking the Page Setup button. Click Close to return to the worksheet or Print to continue printing. After a print preview, dotted lines appear on the document to show the borders of the document for printing. These lines are virtual and do not appear on the printed copy.

5.7 Print To print the worksheet, select File > Print from the Menu bar or click on the Print button toolbar.

from the Standard

• Print range –Select either All pages or a range of Page(s) to print. • Print what –Select Selection of cells highlighted on the worksheet, the Active sheet(s), or all the worksheets in the Entire workbook. • Copies - Choose the number of copies that should be printed. Check the Collate box if the pages should remain in order.

Click OK to print.

****************************

Written By: Engr. Fakhr-e-Alam

20

Tutorial – I

(MS-Excel)

6. Formulas and Functions The unique feature of a spreadsheet program such as Excel is that it allows you to create mathematical formulas and execute functions. Otherwise, it is not much more than a large table for displaying text. This page will show you how to create these calculations.

6.1 Formula Definition A formula allows you to calculate and analyze data in your worksheet. Formulas perform calculations such as addition or multiplication; formulas can also combine values.

6.2 Formulas Formulas are entered in the worksheet cell and must begin with an equal sign "=". The formula then includes the addresses of the cells whose values will be manipulated with appropriate operators placed in between. After the formula is typed into the cell, the calculation executes immediately and the formula itself is visible in the Formula Bar. See the example to the right to view the formula for calculating the subtotal for a number of textbooks. The formula multiplies the quantity and price of each textbook and adds the subtotal for each book.

6.3 Formula Bar The Formula bar is an area located at the top of the worksheet window that is used to enter or edit values or formulas in cells or charts. The Formula bar displays the constant value or formula in the active cell. To display or hide the Formula bar; select from the Menu bar, View > Formula.

6.4 Linking Worksheets When working with formulas, you may want to use a cell from a worksheet other than your current worksheet. For example, the value of cell A1 in the current worksheet and cell A2 in the second worksheet can be added using the format "sheet name! Cell-address". The formula for this example would be "=A1+Sheet2! A2" where the value of cell A1 in the current worksheet (since current worksheet means the active worksheet then there is no need to specify the name of this sheet) is added to the value of cell A2 in the worksheet named "Sheet2".

Written By: Engr. Fakhr-e-Alam

21

Tutorial – I

(MS-Excel)

6.5 Relative, Absolute, and Mixed Referencing Calling cells by just their column and row labels (such as "A1") is called relative referencing. When a formula contains relative referencing and it is copied from one cell to another, Excel does not create an exact copy of the formula. It will change cell addresses relative to the row and column they are moved to. For example, if a simple addition formula in cell C1 "= (A1+B1)" is copied to cell C2, the formula would change to "= (A2+B2)" to reflect the new row. To prevent this change, cells must be called by absolute referencing and this is accomplished by placing dollar signs "$" within the cell addresses in the formula. Continuing the previous example, the formula in cell C1 would read "= ($A$1+$B$1)" if the value of cell C2 should be the sum of cells A1 and B1. Both the column and row of both cells are absolute and will not change when copied. Mixed referencing can also be used where only the row or column is fixed. For example, in the formula "= (A$1+$B2)", the row of cell A1 is fixed and the column of cell B2 is fixed ($ appears before row number however it doesn’t appear before column name row is fixed and column isn’t).

6.6 Function Definition A function in Excel is a built-in formula that performs a mathematical operation or returns information specified by the formula. As with every formula created in Excel, each function starts with an equal (=) sign.

6.7 Basic Functions Functions can be a more efficient way of performing mathematical operations than formulas. For example, if you wanted to add the values of cells D1 through D10, you would type the formula "=D1+D2+D3+D4+D5+D6+D7+D8+D9+D10". A shorter way would be to use the SUM function and simply type "=SUM (D1:D10)". Several other functions and examples are given in the table below. Function

Example

Description

SUM

=SUM(A1:A100)

finds the sum of cells A1 through A100

AVERAGE

=AVERAGE(B1:B10)

finds the average of cells B1 through B10

MAX

=MAX(C1:C100)

returns the highest number from cells C1 through C100

MIN

=MIN(D1:D100)

returns the lowest number from cells D1 through D100

SQRT

=SQRT(D10)

finds the square root of the value in cell D10

TODAY

=TODAY()

returns the current date (leave the parentheses empty)

Written By: Engr. Fakhr-e-Alam

22

Tutorial – I

(MS-Excel)

6.8 Arguments An argument is the reference behind the function. The reference can be any of the following type:

Argument Types Argument Numbers Text Logical Values Cell References / Range

Example 1,2,3 “January” (True or False) B7 or B7:B20

6.9 Operators Operators are mathematical symbols that are broken into four categories Keystroke Operators Arithmetic Explanation + Addition Subtraction * Multiplication / Division % Percent ^ Exponentiation Comparison Explanation = Equal to > Greater than < Less than >= Greater than or equal to <= Less than or equal to <> Not equal to Text Explanation & Reference : ,

Adjoins text or cell references

Example 2+3 5-1 7*3 8/2 90% 7^2 Example B1=D1 B1>D1 B1=D1 B1<=D1 B1<>D1 Example "Scott" & "Hi" produces "Scott Hi" Example

Explanation Includes cells of a column or row B3 : B20 between the designated limits Separates arguments in a function (B3, B20)

Written By: Engr. Fakhr-e-Alam

23

Tutorial – I

(MS-Excel)

6.10 Operator Order Formulas are calculated left to right, using order of precedence, the parentheses have high order of precedence, i.e.: every thing inside them is evaluated first. Excel performs operations in the order shown in the following table.

Excel's Operator Order Arithmetic Operator Precedence 1 % 2 ^ 3 *, / 4 +,5 & 6 >,>=,<,<=,=,<>

Example Percent Exponentiation Multiplication, Division Addition, Subtraction Ampersand Comparisons

Notice that percent has the highest precedence, multiplication and division have same order of precedence, also addition and subtraction have same order of precedence. Excel performs all operations within sets of parentheses first, and you can use this to get exactly the order of operations you want. If multiple operations are encased in multiple sets of parentheses, the operations are performed from inside to outside, then follow the order of operations, and then left to right.

6.11 Function Wizard The function wizard is designed to help provide the necessary arguments and descriptions for the various Excel functions. 1. Select the cell in which you want the results of the function to display. 2. Click the Insert Function button on the Formula toolbar or select Function from the Insert menu. 3. From the Insert Function dialog box, browse through the functions by selecting a Function category from the drop-down menu, and select the function from the list below. As each function name is highlighted a description and example is provided below the two boxes. 4. Click OK to select a function

Written By: Engr. Fakhr-e-Alam

24

Tutorial – I

(MS-Excel)

5. The next window allows you to choose the cells that contain the arguments of the function. In this example, cell B2 and C2 are selected to compute their sum. The values of the cells B2, and C2 are respectively 2, and 3. Excel identifies the range of the cells in the function to (B2:C2). In the lower part of the Function Argument dialogue box you can see the Formula result. 6. Click the OK button.

6.12 Entering Multiple Formulas All At Once To enter the same formula in several cells at once, follow these steps: 1. Select all the cells you want to enter the formula in. 2. Create your formula, but don’t press ENTER when finished. 3. When the formula is complete, press CTRL + ENTER. The formula is entered in all the selected cells simultaneously.

6.13 Editing & Deleting Formulas You can also edit or delete any formula. To delete a formula, click on the cell that contains the formula, and press the DELETE key on your keyboard. If you need to alter the formula, follow these steps: 1. Click on the cell that contains the formula. 2. Click on the Formula Bar and make changes to your function.

If the formula uses a built-in Excel function: 1. Click on the cell that contains the function to select it. 2. Click on the Insert Function button on the formula bar to edit the function arguments. Change the appropriate argument(s) and click OK

Written By: Engr. Fakhr-e-Alam

25

Tutorial – I

(MS-Excel)

6.14 AutoSum Use the AutoSum functions

to add the contents of a cluster of adjacent cells.

1. Select the cell where you want the sum to appear. This cell should be outside the cluster of cells that you will select. Cell C2 was used in this example. 2. Click the AutoSum button (Greek letter sigma) on the Standard toolbar. 3. By default, the group of cells that will be summed will be highlighted, in this example cells A2 through B2. Press the ENTER key on the keyboard or click the green check mark button on the Formula Bar.

6.15 Errors in Formulas When a formula is prevented to run normally, Excel will notify you with an error message. Each error message helps users identify the problem they are facing. The following table lists common Excel errors that you might face.

Error Values Error #### # VALUE

Meaning The column is too narrow to display the result of calculation Wrong type of argument or reference

#DIV/0!

Data is attempting to divide by zero

#NAME?

Formula is referencing an invalid name

#REF! #NULL

Excel can't locate the referenced cells(for example, the cells were deleted) Reference to intersection of two areas that do not intersect

Written By: Engr. Fakhr-e-Alam

How to Fix Widen the column Check operands and arguments Change the value or the cell reference so that the formula doesn't divide by zero Be sure the name still exists or correct the misspelling Click Undo to restore references and then change formula references Check for typing and reference errors

26

Tutorial – I

(MS-Excel)

7. Charts A chart allows you to visually display your data. Charts help users compare data and identify trends. This section explains how you can create simple charts from the data selection you have in a worksheet. Before you can create your chart you must enter data into a worksheet, and then highlight the data to specify to Excel which information to use as part of the chart, then do the following: 1. Insert > Chart, the Chart Wizard appears or 2. Click the Chart Wizard button on the Standard toolbar, the Chart Wizard appears or 3. Press F11 on your keyboard. The chart will be created using the data you selected.

7.1 Chart Wizard The chart wizard brings you through the process of creating a chart by displaying a series of dialog boxes. 1. Enter the data into the worksheet and highlight all the cells that will be included in the chart including headers. 2. Click the Chart Wizard button toolbar to view the Chart Wizard

found on the Standard

3. In the Chart Type dialog box: Choose the Chart type and the Chart subtype if necessary. Click Next.

Written By: Engr. Fakhr-e-Alam

27

Tutorial – I

(MS-Excel)

4. In the Chart Source Data dialog box: Select the data range in case different from the area highlighted in step 1 and click Next.

5. In the Chart Options dialog box: Enter the title of the chart and titles for the X- and Y-axes. Other options for the axes, Grid Lines, Legend, Data Labels, and Data Table can be changed by clicking on the tabs. Click Next to move to the next set of options.

Written By: Engr. Fakhr-e-Alam

28

Tutorial – I

(MS-Excel)

•

Chart title: is the title associated with the chart.

•

Category (x) axis: is the axis (usually a vertical axis) that shows a scale of values by which the data series are measured.

•

Category (y) axis: is the axis (usually a horizontal axis) that displays a category labels for all the data series.

•

The Gridlines tab: You can add gridlines (both vertically and horizontally) to your chart. This is important when you have a chart with values close to each other.

•

The Legend tab: A color, text, or graphics “Key” identifying each series in the chart.

•

The Data Labels tab: Text or values displayed at Data Points (an individually plotted value associated with a specific category).

•

The Data Table tab: Enables you to place a table below the x-axis. This feature aligns the numeric data under the corresponding category.

6. The Chart Location dialog box: Click As new sheet if the chart should be placed on a new worksheet or select As object in if the chart should be embedded in an existing sheet and select the worksheet from the drop-down menu. 7. Click Finish to create the chart

7.2 Chart Toolbar To get the Chart toolbar, right click on the toolbar and click on Chart.

7.3 Resizing a Chart To resize the chart, click on its border and drag any of the eight black handles to change the size. Handles on the corners will resize the chart proportionally while handles along the lines will stretch the chart. • The two vertical middle handles can be used to change the height of the chart. • The two horizontal middle handles can be used to change the width of the chart. • The four handles at the corners can be used to change both the height and width of the chart.

Written By: Engr. Fakhr-e-Alam

29

Tutorial – I

(MS-Excel)

7.4 Moving a Chart Select the border of the chart, hold down the left mouse button and drag the chart to a new location. Elements within the chart such as the title and labels may also be moved within the chart. Click on the element to activate it, and use the mouse to drag it.

7.5 Formatting Chart Objects To select a chart object to format, click on the chart object or select the object from the Chart Objects List and click the Format button. A window will then appear containing the properties of that object. You can change these properties as you like. •

Chart Type: Click the arrowhead on the Chart Type button to select a different type of chart.

•

Legend Toggle: Show or hide the Chart Legend by clicking this toggle button.

•

Data Table view: Display the data table instead of the chart by clicking the Data Table toggle button.

•

Display Data by Column or Row: Displays the data by columns or rows according to the data sheet.

Angle Text - Select the category or value axis and click the Angle Clockwise or Angle Counterclockwise button to angle the selected text by +/- 45 degrees.

7.6 Chart Types Excel offers different kinds of charts. We will look at two types: 1. Column Charts: A column chart has vertical bars and plots as separate points over time. Column charts are good for showing value amounts and quantities over time. 2. Pie Charts: Pie charts are used for showing a percentage of the whole. One of the great features Excel offers with pie charts is that you can select the data point and drag it away from the whole pie, thereby exploding the slice to highlight certain data point.

Written By: Engr. Fakhr-e-Alam

30

Tutorial – I

(MS-Excel)

7.7 An Overview of Formatting Charts and Cells 1. Formatting Chart Titles: Right-click the chart title, and choose Format Chart Title from the Format Chart Title dialog box. Select the font settings, effects, and alignment you want. Click OK. 2. Formatting Legends: Right-click the legend, and choose Format Legend from the Format Legend dialog box. Select the font settings, effects, and alignment you want. Click OK. 3. Changing the Background Color: Right click on the chart area (the white area), and choose Format Chart Area from the pop up menu. Under the Pattern tab select the color that you want. You can also select Chart Area option from the Chart toolbar. 4. Formatting Axis Title: Right-click the axis title and choose Format Axis Title from the pop up menu. Select the font settings, effects, and alignment you want from the Format Axis Title dialog box. 5. Adding Picture/ClipArt Image: You can add a picture or a clipart image to your chart or worksheet by: a. Insert > Picture > ClipArt (To insert an image from ClipArt Gallery). Or b. Insert > Picture > From File (To insert an image stored in your local disk). 6. Rotating Text on Chart: a. Click the text you want to rotate. b. Click on the following buttons of the Chart toolbar: Rotate Text downward by 45 degrees Rotate Text upward by 45 degrees. 7. Wrapping Text in Cells: You can display a long line of text within a cell by wrapping the text. a. Click the cell containing the text you want to wrap b. Click Format > Cells > Alignment tab, and under the Text Control section check the option Wrap text, Click OK.

Written By: Engr. Fakhr-e-Alam

31

Tutorial – I

(MS-Excel)

8. Printing the Chart: You can print the chart with or without worksheet data: a. If you highlight the chart with the worksheet data, and click File > Page Setup you will get a dialog box with Page, Margins, Header/Footer, Sheet tabs. b. If you highlight the chart without the worksheet data, and click File > Page Setup you will get a dialog box also with four tabs but with Page, Margins, Header/Footer and Chart tabs.

Here you have the option of a full page scale to fit the page or custom. You can also determine the printing quality. Now click on Print Preview to preview your worksheet (then close).

Written By: Engr. Fakhr-e-Alam

32

Tutorial – I

(MS-Excel)

8. Commonly Used Features 8.1 Comments A comment is a note that you attach to a cell, separate from other cell contents. Comments are useful as a reminder, such as noting how a complex formula works, or to provide feedback to other users. To add a comment to a cell: 1. Click the cell you want to add a comment to. 2. On the Insert menu, click Comment. 3. In the box that appears, type the comment you want to add. If you don't want your name in the comment, highlight it and delete the name. 4. When you finish typing the text, click outside the comment box.

8.2 To viewing comments: Excel provides several ways to view comments. Cells with comments have red indicator triangles in their upper-right corners. If you rest the pointer over a cell that has an indicator, its comment will appears. You can also make your comments be continuously displayed — either individual comments or all the comments on the worksheet. You can also print out the comments, either in their place on the worksheet or as a list at the end of the printout.

8.3 Use Go To The Go To option takes you to a specific cell without scrolling down to it. From the Edit menu, select Go To. Enter a grid reference in the Reference box (for example type A400 in the Reference box) then click OK, and you go to cell A400.

8.4 Use Find and Replace Use Find and Replace to locate specific text or numbers on a worksheet to review or edit. You can automatically replace data you find by using the replace option. You can choose to select all cells containing the same type of data, formulas, or cells which content does not match the selected cell. To find text or numbers in a worksheet or workbook select the range of cells on the worksheet that you want to search. Note: If you want to search the entire worksheet, click in any cell. From the Edit menu, select Find. The Find and Replace dialog box is displayed. In the Find what field, enter the text or numbers that you want to search for. To perform a simple search, click Find Next. The active cell is moved to the first occurrence of the text you are searching for. Click Find Next again to continue to find matching cells. Written By: Engr. Fakhr-e-Alam

33

Tutorial – I

(MS-Excel)

Click the Options button to be able to specify further details of your search. To specify find options: •

The Within box: either search within the sheet or the workbook

•

The Search box: select whether you want to search by rows or columns

•

The Look in box: select the type of information you want to search for (formulas, values or comments)

•

The Match case check box: If you want to distinguish between upper and lowercase characters in your search

•

The Match entire cell contents check box: to search for an exact or complete match of a cell’s content

•

The Format button: you can identify the format of the text you’re searching for, choose between specifying the format you are looking for or selecting a cell which contains the format you want to find

•

Click Find Next

To find all cells containing the specified text: Enter the find criteria and options as normal and click on Find All. A panel is displayed at the bottom of the dialog box listing the cells where the search text is found. Note: To cancel a search, press the ESC key from the keyboard.

8.5 Increase/Decrease Decimal To increase/decrease the number of digits after the decimal point of a number, click on the Increase Decimal or Decrease Decimal buttons of the Formatting toolbar. You can also select Format > Cells from the menu bar. In the Number tag, choose Number from the Category menu. Change the number of decimal places from the Decimal places menu. Click on OK.

Written By: Engr. Fakhr-e-Alam

34

Tutorial – I

(MS-Excel)

8.6 Protecting your files (password to open)

•

When you first try to save your work, or when you select File > Save As, the Save as dialog box appears.

•

Click on the Tools option and choose General Options.

•

Enter the password to open and the password to modify

•

Click OK.

•

The Confirm Password dialog box that appears, re-enter your Password to open so that you confirm that you typed it correctly. Do the same for Password to modify.

Save your worksheet under any name you want. Whenever anyone attempts to open this worksheet, he will be asked for a verification password.

******************************

Written By: Engr. Fakhr-e-Alam

35

Tutorial – I

(MS-Excel)

9. Additional Readings 9.1 Tab Color To change the tab color, right click on the tab you want, and select Tab Color. From the color list that appears choose the color you want.

9.2 Hide / Unhide •

To hide a column or a row, you have to highlight the whole column / row by right clicking on its Letter/ Number, and choose Hide.

•

To unhide a column or a row, highlight the columns / rows next to the hidden one, right click on them, and choose Unhide.

Written By: Engr. Fakhr-e-Alam

36

Tutorial – I

(MS-Excel)

10. Excel Functions Overview 10.1 Statistical Functions Overview Statistical functions are among the most widely used functions in Excel.

Function AVERAGE(range) COUNT(range) COUNTIF(range, value) MAX(range) MIN(range) MODE(range) STDEV(range)

Function Description Calculates the mean (arithmetic average) of a range of cells Counts the number of values (cells containing numbers in a range) Counts the number of cells that are the same as a specified value. Returns the maximum value of a data set. Returns the minimum value of a data set. Returns the most frequently occurring, or repetitive, value in a range of data. Calculates the standard deviation of a sample.

Given that: • Range: Represents the set of values (number1, number2…) • Value: The criteria upon which you want to evaluate; it can be a number (14), a cell reference (G5), an expression (E5>7), or text (“Victor”).

10.2 Math Functions Overview Math functions in Excel can be used to perform calculations as stand-alone functions or combined to create complex formulas. Excel has a great number of Math functions but the most commonly used ones are: 1. Sum 2. Round 3. Ceiling 4. Floor You can use the Round ( ), Ceiling ( ), or Floor ( ) function to round a number to any number of digits you want. 1. Sum Function • Adds the numbers in a range or multiple ranges of cells. • Written as: Sum (number1, number2…) • Number1, Number2..: are written as a number, a reference to a cell, or to a range of cells for which you want the total value or sum.

Written By: Engr. Fakhr-e-Alam

37

Tutorial – I

(MS-Excel)

2. Round Function • Rounds a number to a specified number of digits. • Written as: Round (number, num_digits) • Number: The value you want to round. • Num_digits: The number of decimal places you want to round. 3. Ceiling Function • Returns number rounded up, away from zero, to the nearest multiple you specify. • Written as: Ceiling (number, significance) • Number: The value you want to round • Significance: The multiple you want to round to 4. Floor Function • Rounds number down, toward zero, to the nearest multiple you specify. • Written as: Floor (number, significance) • Number: The value you want to round. • Significance: The multiple you want to round to

10.3 Information Functions Overview Information functions are generally made up of logical results and can be used in many business situations. Combined with other functions, the information functions can manage lists of data and provide feedback based on a logical result. The most useful function is: Is Number Function • Returns true if the value is a number. • Written as: ISNUMBER (value) • Value: this is the cell or range you want tested.

10.4 Overview of Logical Functions Logical functions test cells & ranges and can return only: True or False. Commonly used logical functions are: 1. AND 2. OR 3. NOT 4. IF 1. AND Function • Returns True if all the logical arguments are true. • Returns False if one or more arguments is false. • Written as: AND (logical1, logical2 …) • Logical Value 1, 2 … : The test results in a logical TRUE or FALSE return. Up to 30 conditions can be tested together.

Written By: Engr. Fakhr-e-Alam

38

Tutorial – I

(MS-Excel)

2. OR Function • Returns False if all the logical arguments are false • Returns True if one or more arguments is true • Written as: OR (logical1, logical2…) • Logical Value 1, 2 …: These are the conditions to be met to test a logical true or false result. You can use up to 30 conditions within the formula. 3. NOT Function • Returns the opposite of the logical value • Written as: NOT (logical) Logical : This is the value that can be evaluated with a True or False Condition. If True, NOT returns False, if False, NOT returns True. 4. IF Function • The IF ( ) function decides the contents of a cell on a spreadsheet based on whether a test condition is true or false. • It returns a value if one condition is True, and returns another value or result if the condition is False. • Written as: IF (logical_test, value_if: true, value_if_false) Logical_test : Is any value or expression that can be evaluated to True or False. VALUE IF TRUE : Is the value returned if the logical_test is True. VALUE IF FALSE : Is the value returned if the logical_test is False

The operators in the logical_test of the IF function may be:

= <> > >= < <=

Equal to Not equal to Greater than Greater than or equal to Less than Less than or equal to

Note: Nested IF ( ) Function If you wish to test more than one condition, you can nest an if () function within another =if (logical_test, result_if_true, if (logical_test, result_if_true, if (logical_test, result_if_true, result if false)) Example: If (I am married, If (I get a child, I will send him/her to AUB))

****************************** Written By: Engr. Fakhr-e-Alam

39

Tutorial – I

(MS-Excel)

Appendix – I Microsoft Excel Shortcut Keys Ctrl+A

Select All

None

Ctrl+B

Bold

Format, Cells, Font, Font Style, Bold

Ctrl+C

Copy

Edit, Copy

Ctrl+D

Fill Down

Edit, Fill, Down

Ctrl+F

Find

Edit, Find

Ctrl+G

Goto

Edit, Goto

Ctrl+H

Replace

Edit, Replace

Ctrl+I

Italic

Format, Cells, Font, Font Style, Italic

Ctrl+K

Insert Hyperlink

Insert, Hyperlink

Ctrl+N

New Workbook

File, New

Ctrl+O

Open

File, Open

Ctrl+P

Print

File, Print

Ctrl+R

Fill Right

Edit, Fill Right

Ctrl+S

Save

File, Save

Ctrl+U

Underline

Format, Cells, Font, Underline, Single

Ctrl+V

Paste

Edit, Paste

Ctrl W

Close

File, Close

Ctrl+X

Cut

Edit, Cut

Ctrl+Y

Repeat

Edit, Repeat

Ctrl+Z

Undo

Edit, Undo

F1

Help

Help, Contents and Index

F2

Edit

None

F3

Paste Name

Insert, Name, Paste

F4

Repeat last action

Edit, Repeat. Works while not in Edit mode.

F4

While typing a formula, switch between absolute/relative refs

None

F5

Goto

Edit, Goto

F6

Next Pane

None

F7

Spell check

Tools, Spelling

F8

Extend mode

None

F9

Recalculate all workbooks

Tools, Options, Calculation, Calc,Now

F10

Activate Menubar

N/A

F11

New Chart

Insert, Chart

F12

Save As

File, Save As

Written By: Engr. Fakhr-e-Alam

40

Tutorial – I

(MS-Excel)

Ctrl+:

Insert Current Time

None

Ctrl+;

Insert Current Date

None

Ctrl+"

Copy Value from Cell Above

Edit, Paste Special, Value

Ctrl+’

Copy Formula from Cell Above

Edit, Copy

Shift

Hold down shift for additional functions in Excel’s menu

none

Shift+F1

What’s This?

Help, What’s This?

Shift+F2

Edit cell comment

Insert, Edit Comments

Shift+F3

Paste function into formula

Insert, Function

Shift+F4

Find Next

Edit, Find, Find Next

Shift+F5

Find

Edit, Find, Find Next

Shift+F6

Previous Pane

None

Shift+F8

Add to selection

None

Shift+F9

Calculate active worksheet

Calc Sheet

Shift+F10

Display shortcut menu

None

Shift+F11

New worksheet

Insert, Worksheet

Shift+F12

Save

File, Save

Ctrl+F3

Define name

Insert, Names, Define

Ctrl+F4

Close

File, Close

Ctrl+F5

XL, Restore window size

Restore

Ctrl+F6

Next workbook window

Window, ...

Shift+Ctrl+F6

Previous workbook window

Window, ...

Ctrl+F7

Move window

XL, Move

Ctrl+F8

Resize window

XL, Size

Ctrl+F9

Minimize workbook

XL, Minimize

Ctrl+F10

Maximize or restore window

XL, Maximize

Ctrl+F11

Inset 4.0 Macro sheet

None in Excel 97. In versions prior to 97 - Insert, Macro, 4.0 Macro

Ctrl+F12

File Open

File, Open

Alt+F1

Insert Chart

Insert, Chart...

Alt+F2

Save As

File, Save As

Alt+F4

Exit

File, Exit

Alt+F8

Macro dialog box

Tools, Macro, Macros in Excel 97 Tools,Macros - in earlier versions

Alt+F11

Visual Basic Editor

Tools, Macro, Visual Basic Editor

Ctrl+Shift+F3

Create name by using names of row and column labels

Insert, Name, Create

Ctrl+Shift+F6

Previous Window

Window, ...

Ctrl+Shift+F12

Print

File, Print

Alt+Shift+F1

New worksheet

Insert, Worksheet

Written By: Engr. Fakhr-e-Alam

41

Tutorial – I

(MS-Excel)

Alt+Shift+F2

Save

File, Save

Alt+=

AutoSum

No direct equivalent

Ctrl+`

Toggle Value/Formula display

Tools, Options, View, Formulas

Ctrl+Shift+A

Insert argument names into formula

No direct equivalent

Alt+Down arrow

Display AutoComplete list

None

Alt+’

Format Style dialog box

Format, Style

Ctrl+Shift+~

General format

Format, Cells, Number, Category, General

Ctrl+Shift+!

Comma format

Format, Cells, Number, Category, Number

Ctrl+Shift+@

Time format

Format, Cells, Number, Category, Time

Ctrl+Shift+#

Date format

Format, Cells, Number, Category, Date

Ctrl+Shift+$

Currency format

Format, Cells, Number, Category, Currency

Ctrl+Shift+%

Percent format

Format, Cells, Number, Category, Percentage

Ctrl+Shift+^

Exponential format

Format, Cells, Number, Category,

Ctrl+Shift+&

Place outline border around selected cells

Format, Cells, Border

Ctrl+Shift+_

Remove outline border

Format, Cells, Border

Ctrl+Shift+*

Select current region

Edit, Goto, Special, Current Region

Ctrl++

Insert

Insert, (Rows, Columns, or Cells) Depends on selection

Ctrl+-

Delete

Delete, (Rows, Columns, or Cells) Depends on selection

Ctrl+1

Format cells dialog box

Format, Cells

Ctrl+2

Bold

Format, Cells, Font, Font Style, Bold

Ctrl+3

Italic

Format, Cells, Font, Font Style, Italic

Ctrl+4

Underline

Format, Cells, Font, Font Style, Underline

Ctrl+5

Strikethrough

Format, Cells, Font, Effects, Strikethrough

Ctrl+6

Show/Hide objects

Tools, Options, View, Objects, Show All/Hide

Ctrl+7

Show/Hide Standard toolbar

View, Toolbars, Standard

Ctrl+8

Toggle Outline symbols

None

Ctrl+9

Hide rows

Format, Row, Hide

Ctrl+0

Hide columns

Format, Column, Hide

Ctrl+Shift+(

Unhide rows

Format, Row, Unhide

Written By: Engr. Fakhr-e-Alam

42

Tutorial – I

(MS-Excel)

Ctrl+Shift+)

Unhide columns

Format, Column, Unhide

Alt or F10

Activate the menu

None

Ctrl+Tab

In toolbar: next toolbar

None

Shift+Ctrl+Tab

In toolbar: previous toolbar

None

Ctrl+Tab

In a workbook: activate next workbook

None

Shift+Ctrl+Tab

In a workbook: activate previous workbook

None

Tab

Next tool

None

Shift+Tab

Previous tool

None

Enter

Do the command

None

Shift+Ctrl+F

Font Drop Down List

Format, Cells, Font

Shift+Ctrl+F+F

Font tab of Format Cell Dialog box

Format, Cells, Font

Shift+Ctrl+P

Point size Drop Down List

Format, Cells, Font

*****************************

Written By: Engr. Fakhr-e-Alam

43

Tutorial – I

(MS-Excel)

Appendix – II Important Formulas

SUM Horizontal 100

200

300

Vertical 100 200 300 600

=SUM(C7:C9)

Single Cells 100

300

600

=SUM(C4:E4)

600

=SUM(C13,D14,E13)

200 Multiple Ranges 100 200 3000

Functions 100 200 300

400 500 600 4800

=SUM(C17:C19,E17:E19)

400 500 600 800

=SUM(AVERAGE(C23:C25),MAX(E23:E25))

What Does It Do ? This function creates a total from a list of numbers. It can be used either horizontally or vertically. The numbers can be in single cells, ranges are from other functions. Syntax : =SUM(Range1,Range2,Range3... through to Range30).

Written By: Engr. Fakhr-e-Alam

44

Tutorial – I

(MS-Excel)

Note : Many people use the =SUM() function incorrectly. This example shows how the SUM has been combined with plus + symbols. The formula is actually doing more work than needed. It should have been entered as either =C48+C49+C50 or =SUM(C48:C50). 100 200 300 600

=SUM(C48+C49+C50)

Wrong!

=SUM(C48:C50)

Correct

=C48+C49+C50

Correct

SUM (Running Total) Using =SUM() For A Running Total

Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Sales 10 50 30 20

Running Total 10 60 90 110 110 110 110 110 110 110 110 110

=SUM($D$7:D7) =SUM($D$7:D8) =SUM($D$7:D9) =SUM($D$7:D10) =SUM($D$7:D11) =SUM($D$7:D12) =SUM($D$7:D13) =SUM($D$7:D14) =SUM($D$7:D15) =SUM($D$7:D16) =SUM($D$7:D17) =SUM($D$7:D18)

Type the formula =SUM($D$7:D7) in cell E7 and then copy down the table. It works because the first reference uses dollar symbols $ to keep $D$7 static as the formula is copied down. Each occurrence of the =SUM() then adds all the numbers from the first cell down.

Written By: Engr. Fakhr-e-Alam

45

Tutorial – I

(MS-Excel)

The function can be tidied up to show 0 zero when there is no adjacent value by using the =IF() function.

Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Sales 10 50 30 20

Running Total 10 60 90 110 0 0 0 0 0 0 0 0

=SUM(IF(D7,$D$7:D7,0)) =SUM(IF(D8,$D$7:D8,0)) =SUM(IF(D9,$D$7:D9,0)) =SUM(IF(D10,$D$7:D10,0)) =SUM(IF(D11,$D$7:D11,0)) =SUM(IF(D12,$D$7:D12,0)) The =SUM() only takes place when there is data in column D. Otherwise the value 0 zero is entered.

SUM using names You can use the names typed at the top of columns or side of rows in calculations simply by typing the name into the formula. Try this example: Go to cell C16 and then enter the formula =SUM(jan) The result will show. This formula can be copied to D16 and E16, and the names change to Feb and Mar.

North South East West Total

Jan 45 30 35 20 130

Feb 50 25 10 50 135

Mar 50 35 50 5 140

If it does not work ! The feature may have been switched off on your computer. You can switch it on by using Tools, Options, Calculation, Accept Labels in Formula.

Written By: Engr. Fakhr-e-Alam

46

Tutorial – I

(MS-Excel)

SUMIF Item Brakes Tyres Brakes Service Service Window Tyres Tyres Clutch

Date 1-Jan-98 10-May-98 1-Feb-98 1-Mar-98 5-Jan-98 1-Jun-98 1-Apr-98 1-Mar-98 1-May-98

Cost 80 25 80 150 300 50 200 100 250

Total cost of all Brakes bought. Total cost of all Tyres bought. Total of items costing £100 or above. Total of item typed in following cell.

service

160 325 1000

=SUMIF(C4:C12,"Brakes",E4:E12) =SUMIF(C4:C12,"Tyres",E4:E12) =SUMIF(E4:E12,">=100")

450

=SUMIF(C4:C12,E18,E4:E12)

What Does It Do ? This function adds the value of items which match criteria set by the user. Syntax : =SUMIF(RangeOfThingsToBeExamined,CriteriaToBeMatched,RangeOfValuesToTotal) =SUMIF(C4:C12,"Brakes",E4:E12)

This examines the names of products in C4:C12. It then identifies the entries for Brakes. It then totals the respective figures in E4:E12

=SUMIF(E4:E12,">=100")

This examines the values in E4:E12. If the value is >=100 the value is added to the total.

PRODUCT Numbers 2 3 5 10 3 7

Written By: Engr. Fakhr-e-Alam

Product 6 50 210 6300

=PRODUCT(C4,D4) =PRODUCT(C5:D5) =PRODUCT(C6:D6,10) =PRODUCT(C4:D6)

47

Tutorial – I

(MS-Excel)

What Does It Do ? This function multiples a group of numbers together. It is the same as using 2*3*5*10*3*7, which results in 6300. Syntax : =PRODUCT(Number1,Number2,Number3... through to Number30) or =PRODUCT(RangeOfNumbers) or =PRODUCT(Number1,Range,Number2...)

SUMPRODUCT Item Tyres Filters Bulbs

Sold 5 2 3 Total Sales Value :

price 100 10 2 526

=SUMPRODUCT(D4:D6,E4:E6)

What Does It Do ? This function uses at least two columns of values. The values in the first column are multipled with the corresponding value in the second column. The total of all the values is the result of the calculation. Syntax : =SUMPRODUCT(Range1, Range, Range3 through to Range30) Example : The following table was used by a drinks merchant to keep track of stock. The merchant needed to know the total purchase value of the stock, and the potential value of the stock when it is sold, takinging into account the markup percentage. The =SUMPRODUCT() function is used to multiply the Cases In Stock with the Case Price to calculate what the merchant spent in buying the stock. The =SUMPRODUCT() function is used to multiply the Cases In Stock with the Bottles In Case and the Bottle Setting Price, to calculate the potential value of the stock if it is all sold. Written By: Engr. Fakhr-e-Alam

48

Tutorial – I

(MS-Excel)

Cases Product In Stock Red Wine 10 White Wine 8 Champagne 5 Beer 50 Lager 100

Case Price £120 £130 £200 £24 £30

Bottles In Case 10 10 6 12 12

Bottle Cost £12.00 £13.00 £33.33 £2.00 £2.50

Markup 25% 25% 80% 20% 25%

=D39/E39

Total Value Of Stock : Total Selling Price Of Stock : Profit :

Bottle Selling Price £15.00 £16.25 £60.00 £2.40 £3.13 =F39+F39*G39

£7,440 =SUMPRODUCT(C35:C39,D35:D39) =SUMPRODUCT(C35:C39,E35:E39, £9,790 H35:H39) £2,350

=E44-E43

Brackets in formula Sometimes you will need to use brackets, (also known as 'braces'), in formula. This is to ensure that the calculations are performed in the order that you need. The need for brackets occurs when you mix plus or minus with divide or multiply. Mathematically speaking the * and / are more important than + and - . The * and / operations will be calculated before + and - . Example 1 : The wrong answer ! 10 20 2 50 =C12+C13*C14 You may expect that 10 + 20 would equal 30 And then 30 * 2 would equal 60 But because the * is calculated first Excel sees the calculation as 20 * 2 resulting in 40 And then 10 + 40 resulting in 50

Written By: Engr. Fakhr-e-Alam

49

Tutorial – I

(MS-Excel)

Example 2 : The correct answer. 10 20 2 60 =(C27+C28)*C29 By placing brackets around (10+20) Excel performs this part of the calculation first, resulting in 30 Then the 30 is multiplied by 2 resulting in 60

LCM Numbers 6 20 12 18 34 96

Least Common Multiple 60 36 1632

=LCM(C4,D4) =LCM(C5,D5) =LCM(C6,D6)

What Does It Do ? This function calculate the Least Common Multiple, which is the smallest number that can be divided by each of the given numbers. Syntax : =LCM(Number1,Number2,Number3... through to Number29)

FACT Number Factorial 3 6 =FACT(C4) 3.5 6 =FACT(C5) 5 120 =FACT(C6) 10 3,628,800 =FACT(C7) 20 2,432,902,008,176,640,000 =FACT(C8)

Written By: Engr. Fakhr-e-Alam

50

Tutorial – I

(MS-Excel)

What Does It Do ? This function calculates the factorial of a number. The factorial is calculated as 1*2*3*4..etc. The factorial of 5 is calculated as 1*2*3*4*5, which results in 120. Decimal fractions of the number are ignored. Syntax =FACT(Number)

Time Sheet Week Mon beginning 05-Jan-98

Day Mon 05 Tue 06 Wed 07 Thu 08 Fri 09

Arrive 8:00 8:45 9:00 8:30 8:00

Normal Hours Lunch Lunch Out In 13:00 14:00 12:30 13:30 13:00 14:00 13:00 14:00 12:00 13:00

Depart 17:00 17:00 18:00 17:00 17:00 Total Hours

Under worked by Over worked by

37:30

Total 8:00 7:15 8:00 7:30 8:00

=(F6-C6)-(E6-D6)

38:45

=SUM(G6:G10)

1:15

=IF(G3-G11>0,G3-G11, "-") =IF(G3-G11<0,ABS(G3-G11),"-")

This is simple example of a timesheet : Instructions : Use the format dd/mm/yy, the name of the day will appear automatically. The date is then passed down to the Day column. Type the amount of hours you are expected to work in G3, the Normal Hours. This is used later to calculate if have worked over or under the required hours. Type the times you arrive and leave work in the appropriate columns. Use the format of hh:mm. Note : If the [hh]:mm format had not been used the Total Hours would show as : If the [hh]:mm format does not show in the cell format dialog box on your computer, it can be created using Format, Cells, Number, Custom. Written By: Engr. Fakhr-e-Alam

14:45

51

Tutorial – I

(MS-Excel)

Split Forename and Surname The following formula are useful when you have one cell containing text which needs to be split up. One of the most common examples of this is when a persons Forename and Surname are entered in full into a cell. The formula use various text functions to accomplish the task. Each of the techniques uses the space between the names to identify where to split.

Finding the First Name : Full Name Hussain Yousif Adil Ahmed Hassan Mahdi

First Name Hussain Adil Hassan

=LEFT(C14,FIND(" ",C14,1)) =LEFT(C15,FIND(" ",C15,1)) =LEFT(C16,FIND(" ",C16,1))

Last Name Yousif Ahmed Mahdi

=RIGHT(C22,LEN(C22)-FIND(" ",C22)) =RIGHT(C23,LEN(C23)-FIND(" ",C23)) =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last Name : Full Name Hussain Yousif Adil Ahmed Hassan Mahdi

Finding the Last name when a Middle name is present : The formula above cannot handle any more than two names. If there is also a middle name, the last name formula will be incorrect. To solve the problem you have to use a much longer calculation. Full Name Hussain Yousif Al-Shaikh Adil Ahmed Al-Zahrani Hassan Mahdi Al- Ashwor

Written By: Engr. Fakhr-e-Alam

Last Name Al-Shaikh Al-Zahrani Al- Ashwor =RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)LEN(SUBSTITUTE(C37," ","")))))

52

Tutorial – I

(MS-Excel)

Finding the Middle name : Middle Name Yousif Ahmed Mahdi =LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND (" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

Full Name Hussain Yousif Al-Shaikh Adil Ahmed Al-Zahrani Hassan Mahdi Al- Ashwor

Percentages There are no specific functions for calculating percentages. You have to use the skills you were taught in your maths class at school! Finding a percentage of a value : Initial value % to find Percentage value

120 25% 30

=D8*D9

Example 1 A company is about to give its staff a pay rise. The wages department need to calculate the increases. Staff on different grades get different pay rises. Grade A B C

% Rise 10% 15% 20%

Name Alam Sara Ali Hussain Khalifa Usman

Grade A B C B C A

Old Salary SR 10,000 SR 20,000 SR 30,000 SR 25,000 SR 32,000 SR 12,000

Increase SR 1,000 SR 3,000 SR 6,000 SR 3,750 SR 6,400 SR 1,200

=E23*LOOKUP(D23,C18:C20,D18:D20) =E24*LOOKUP(D24,C18:C20,D18:D20) =E25*LOOKUP(D25,C18:C20,D18:D20) =E26*LOOKUP(D26,C18:C20,D18:D20) =E27*LOOKUP(D27,C18:C20,D18:D20) =E28*LOOKUP(D28,C18:C20,D18:D20)

Finding a percentage increase : Initial value % increase Increased value

120 25% 150

Written By: Engr. Fakhr-e-Alam

=D33*D34+D33 53

Tutorial – I

(MS-Excel)

Example 2 A company is about to give its staff a pay rise. The wages department need to calculate the new salary including the % increase. Staff on different grades get different pay rises. Grade A B C Name Alam Sara Ali Hussain Khalifa Usman

% Rise 10% 15% 20% Grade A B C B C A

Old Salary SR 10,000 SR 20,000 SR 30,000 SR 25,000 SR 32,000 SR 12,000

Increase SR 11,000 SR 23,000 SR 36,000 SR 28,750 SR 38,400 SR 13,200

=E48*LOOKUP(D48,C18:C20,D18:D20)+E48 =E49*LOOKUP(D49,C18:C20,D18:D20)+E49 =E50*LOOKUP(D50,C18:C20,D18:D20)+E50 =E51*LOOKUP(D51,C18:C20,D18:D20)+E51 =E52*LOOKUP(D52,C18:C20,D18:D20)+E52 =E53*LOOKUP(D53,C18:C20,D18:D20)+E53

Finding one value as percentage of another : Value A Value B A as % of B

120 60 50% =D59/D58

You will need to format the result as % by using the % button on the toolbar.

Example 3 An manager has been asked to submit budget requirements for next year. The manger needs to specify what will be required each quarter. The manager knows what has been spent by each region in the previous year. By analyzing the past years spending, the manager hopes to predict what will need to be spent in the next year.

Last years figures Region Q1 North 9,000 South 7,000 East 2,000 West 8,000 Total 26,000

Q2 2,000 4,000 8,000 9,000 23,000

Written By: Engr. Fakhr-e-Alam

Q3 9,000 9,000 7,000 6,000 31,000

Q4 7,000 5,000 3,000 5,000 20,000

Total 100,000

54

Tutorial – I

(MS-Excel)

Last years Quarters as % of last years Total Region Q1 Q2 Q3 North 9% 2% 9% South 7% 4% 9% East 2% 8% 7% West 8% 9% 6% Total 26% 23% 31%

Q4 7% 5% 3% 5% 20%

=G74/H78 =G75/H78 =G76/H78 =G77/H78 =G78/H78

Next years budget 150,000 Next years estimated budget requirements Region Q1 Q2 Q3 North 13,500 3,000 13,500 South 10,500 6,000 13,500 East 3,000 12,000 10,500 West 12,000 13,500 9,000 Total 39,000 34,500 46,500

Q4 10,500 7,500 4,500 7,500 30,000

=G82*E88 =G83*E 88 =G84*E 88 Total 150,000

Finding an original value after an increase has been applied Increased value % increase Original value

150 25% 120 =D100/(100%+D101)

Example 4 An employ has to submit an expenses claim for traveling and accommodation. The claim needs to show the VAT tax portion of each receipt. Unfortunately the receipts held by the employee only show the total amount. The employee needs to split this total to show the original value and the VAT amount. VAT rate

Receipt Petrol Hotel Petrol

17.50%

Total SR 10.0 SR 235.0 SR 117.5

Actual Value Vat Value SR 8.51 SR 1.49 SR 200.00 SR 35.00 SR 100.0 0 SR 17.50 =D115/(100%+D110)

Written By: Engr. Fakhr-e-Alam

=D113-D113/(100%+D110)

55

Tutorial – I

(MS-Excel)

Age Calculation You can calculate a persons age based on their birthday and todays date. The calculation uses the DATEDIF() function. The DATEDIF() is not documented in Excel 5, 7 or 97, but it is in 2000. (Makes you wonder what else Microsoft forgot to tell us!) Birth date :

1-Jan-60

Years lived : and the months : and the days :

49

=DATEDIF(C8,TODAY(),"y")

4 3

=DATEDIF(C8,TODAY(),"ym") =DATEDIF(C8,TODAY(),"md")

You can put this all together in one calculation, which creates a text version. Age is 49 Years, 4 Months and 3 Days ="Age is "&DATEDIF(C8,TODAY(),"y")&" Years, "&DATEDIF(C8,TODAY(),"ym")&" Months and "&DATEDIF(C8,TODAY(),"md")&" Days"

Another way to calculate age : This method gives you an age which may potentially have decimal places representing the months. If the age is 20.5, the .5 represents 6 months. Birth date :

1-Jan-60

Age is :

49.34

=(TODAY()-C23)/365.25

AND Items To Test 500 500 25

800 25 500 12

Result TRUE FALSE FALSE TRUE

=AND(C4>=100,D4>=100) =AND(C5>=100,D5>=100) =AND(C6>=100,D6>=100) =AND(D7>=1,D7<=52)

What Does It Do? : This function tests two or more conditions to see if they are all true. It can be used to test that a series of numbers meet certain conditions. It can be used to test that a number or a date falls between an upper and lower limit. Normally the AND() function would be used in conjunction with a function such as =IF(). Written By: Engr. Fakhr-e-Alam

56

Tutorial – I

(MS-Excel)

Syntax : =AND(Test1,Test2) Note that there can be up to 30 possible tests. Formatting : When used by itself it will show TRUE or FALSE. Example The following example shows a list of examination results. The teacher wants to find the pupils who scored above average in all three exams. The =AND() function has been used to test that each score is above the average. The result of TRUE is shown for pupils who have scored above average in all three exams. Name Alan Bob Carol David Eric Fred Gail Harry Ian Janice

Maths 80 50 60 90 20 40 10 80 30 10

Averages

47

English Physics 75 85 30 40 70 50 85 95 30 Absent 60 80 90 80 70 60 10 20 20 30

54

Passed TRUE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE =AND(C38>=AVERAGE($C$29:$C$38),D38>= AVERAGE($D$29:$D$38),E38>=AVERAGE ($E$29:$E$38))

60

OR Order No. AB001 AB002 AB003 AB004

Cost 1000 1000 2000 5000

Payment Handling Type Charge Cash SR Visa SR 5 Cheque SR Delta SR 5

=IF(OR(E4="Visa",E4="Delta"),5,0) =IF(OR(E5="Visa",E5="Delta"),5,0) =IF(OR(E6="Visa",E6="Delta"),5,0) =IF(OR(E7="Visa",E7="Delta"),5,0)

What Does It Do? This function tests two or more conditions to see if any of them are true. It can be used to test that at least one of a series of numbers meets certain conditions. Normally the OR() function would be used in conjunction with a function such as =IF().

Written By: Engr. Fakhr-e-Alam

57

Tutorial – I

(MS-Excel)

Syntax : =OR(Test1,Test2) Note that there can be up to 30 possible tests. Formatting : When used by itself it will show TRUE or FALSE. Example : The following table shows a list of orders taken by a company. A handling charge of £5 is made on all orders paid by Visa or Delta cards. The =OR() function has been used to determine whether the charge needs to be applied. Order No. AB001 AB002 AB003 AB004

Cost 1000 1000 2000 5000

Payment Handling Type Charge Cash SR Visa SR 5 Cheque SR Delta SR 5

=IF(OR(E27="Visa",E27="Delta"),5,0)

NOT Cells To Test 10 20 10 20 10 20 1-Jan-98 1-Feb-98 Hello Goodbye Hello Hello

Result TRUE TRUE FALSE TRUE TRUE FALSE

=NOT(C4>D4) =NOT(C5=D5) =NOT(C6D7) =NOT(C8=D8) =NOT(C9=D9)

What Does It Do ? This function performs a test to see if the test fails. (A type of reverse logic). If the test fails, the result is TRUE. If the test is met, then the result is FALSE. Syntax : =NOT(TestToPerform) The TestToPerform can be reference to cells or another calculation.

Written By: Engr. Fakhr-e-Alam

58

Tutorial – I

(MS-Excel)

Example : The following table was used by a library to track books borrowed. The date the book was Taken out is entered. The period of the Loan is entered. The date the book was returned is entered. The =NOT() function has been used to calculate whether the book was returned within the correct time, by adding the Loan value to the Taken date. If the book was not returned on time the result Overdue is shown, otherwise OK is shown. Taken 1-Jan-98 1-Jan-98 1-Jan-98

Loan 14 14 14

Returned 5-Jan-98 15-Jan-98 20-Jan-98

Status OK =IF(NOT(D33<=B33+C33),"Overdue","OK") OK =IF(NOT(D34<=B34+C34),"Overdue","OK") Overdue =IF(NOT(D35<=B35+C35),"Overdue","OK")

IF Name Alan Bob Carol

Sales 1000 6000 2000

Target 5000 5000 4000

Result Not Achieved Achieved Not Achieved

=IF(C4>=D4,"Achieved","Not Achieved") =IF(C5>=D5,"Achieved","Not Achieved") =IF(C6>=D6,"Achieved","Not Achieved")

What Does It Do? This function tests a condition. If the condition is met it is considered to be TRUE. If the condition is not met it is considered as FALSE. Depending upon the result, one of two actions will be carried out. Syntax : =IF(Condition,ActionIfTrue,ActionIfFalse) The Condition is usually a test of two cells, such as A1=A2. The ActionIfTrue and ActionIfFalse can be numbers, text or calculations. Formatting : No special formatting is required. Example : The following table shows the Sales figures and Targets for sales reps. Each has their own target which they must reach. The =IF() function is used to compare the Sales with the Target. If the Sales are greater than or equal to the Target the result of Achieved is shown. If the Sales do not reach the target the result of Not Achieved is shown. Note that the text used in the =IF() function needs to be placed in double quotes "Achieved". Written By: Engr. Fakhr-e-Alam

59

Tutorial – I

Name Alan Bob Carol

(MS-Excel)

Sales 1000 6000 2000

Target 5000 5000 4000

Result Not Achieved Achieved Not Achieved

=IF(C31>=D31,"Achieved","Not Achieved") =IF(C32>=D32,"Achieved","Not Achieved") =IF(C33>=D33,"Achieved","Not Achieved")

Example 2 : The following table is similar to that in Example 1. This time the Commission to be paid to the sales rep is calculated. If the Sales are greater than or equal to the Target, the Commission is 10% of Sales. If the Sales do not reach Target, the Commission is only 5% of Sales. Name Alan Bob Carol

Sales 1000 6000 2000

Target 5000 5000 4000

Commission 50 600 100

=IF(C43>=D43,C43*10%,C43*5%) =IF(C44>=D44,C44*10%,C44*5%) =IF(C45>=D45,C45*10%,C45*5%)

Example 3 : This example uses the =AND() within the =IF() function. A builders merchant gives 10% discount on certain product lines. The discount is only given on products which are on Special Offer, when the Order Value is £1000 or above. The =AND() function is used with the =IF() to check that the product is on offer and that the value of the order is above £1000.

Product

Special Offer

Wood Glass

Yes No

Cement

Yes

Turf

Yes

Order Value SR 2,000 SR 2,000 SR 500 SR 3,000

Written By: Engr. Fakhr-e-Alam

Discount

Total

SR SR

200 -

SR

-

SR 1,800 SR 2,000 SR

500

SR 300 SR 2,700 =IF(AND(C61="Yes",D61>=1000),D61*10%,0)

60

Tutorial – I

(MS-Excel)

AVERAGE Temp Rain

Mon 30 0

Tue 31 0

Wed 32 0

Thu 29 4

Fri 26 6

Sat 28 3

Sun Average 27 29 1 2

=AVERAGE(D4:J4) =AVERAGE(D5:J5)

Mon 30 0

Tue

Temp Rain

Wed 32 0

Thu 29 4

Fri 26 6

Sat 28 3

Sun Average 27 28.667 1 2.3333

=AVERAGE(D8:J8) =AVERAGE(D9:J9)

Temp Rain

Mon 30 0

Tue No Reading

Wed 32 0

Thu 29 4

Fri 26 6

Sat 28 3

Sun Average 27 28.667 1 2.3333

=AVERAGE(D12:J12) =AVERAGE(D13:J13)

What Does It Do? This function calculates the average from a list of numbers. If the cell is blank or contains text, the cell will not be used in the average calculation. If the cell contains zero 0, the cell will be included in the average calculation. Syntax : =AVERAGE(Range1,Range2,Range3... through to Range30) Formatting : No special formatting is needed. Note : To calculate the average of cells which contain text or blanks use =SUM() to get the total and then divide by the count of the entries using =COUNTA(). Mon

Tue

Wed

Thu

Fri

Sat

Sun Average

Temp

30

No

32

29

26

28

27

24.571

Rain

0

Reading

0

4

6

3

1

2

Mon

Tue

Wed

Thu

Fri

Sat

Sun Average

Temp

30

32

29

26

28

27

28.667

Rain

0

0

4

6

3

1

2.3333

Written By: Engr. Fakhr-e-Alam

=SUM(D31:J31)/ COUNTA(D31:J31) =SUM(D32:J32)/ COUNTAD32:J32)

=SUM(D35:J35)/ COUNTA(D35:J35) =SUM(D36:J36)/ COUNTA(D36:J36)

61

Tutorial – I

(MS-Excel)

DAVERAGE Life Hours 3000 2000

Product Wattage Brand Bulb 200 Horizon Neon 100 Horizon Spot 60 Other 10 8000 Sunbeam Bulb 80 1000 Horizon Spot 100 unknown Horizon Spot 200 3000 Horizon Other 25 unknown Sunbeam Bulb 200 3000 Sunbeam Neon 100 2000 Sunbeam Bulb 100 unknown Sunbeam Bulb 10 800 Horizon Bulb 60 1000 Sunbeam Bulb 80 1000 Sunbeam Bulb 100 2000 Horizon Bulb 40 1000 Horizon

Unit Cost 4.50 2.00

Box Quantity 4 15

0.80 0.20 1.25 2.50 0.50 5.00 1.80 0.25 0.20 0.15 0.20 0.80 0.10

25 40 10 15 10 3 20 10 25 25 30 10 20

This is the Database range. Boxes In Value Of Stock Stock 3 54.00 2 60.00 0.00 6 120.00 3 24.00 4 50.00 0 0.00 3 15.00 2 30.00 5 180.00 5 12.50 2 10.00 0 0.00 2 12.00 5 40.00 5 10.00

To calculate the Average cost of a particular Brand of bulb. These two cells are the Criteria range. Brand Type the brand name : sunbeam The Average cost of sunbeam is :

£1.24

=DAVERAGE(B3:I19,F3,E23:E24)

What Does It Do ? This function examines a list of information and produces and average. Syntax : =DAVERAGE(DatabaseRange,FieldName,CriteriaRange) The DatabaseRange is the entire list of information you need to examine, including the field names at the top of the columns. The FieldName is the name, or cell, of the values to be averaged, such as "Unit Cost" or F3. The CriteriaRange is made up of two types of information. The first set of information is the name, or names, of the Fields(s) to be used as the basis for selecting the records, such as the category Brand or Wattage. The second set of information is the actual record, or records, which are to be selected, such as Horizon as a brand name, or 100 as the wattage.

Written By: Engr. Fakhr-e-Alam

62

Tutorial – I

(MS-Excel)

Examples : The average Unit Cost of a particular Product of a particular Brand. Product Bulb The average of Horizon Bulb is :

£1.16

Brand Horizon =DAVERAGE(B3:I19,F3,E49:F50)

This is the same calculation but using the actual name "Unit Cost" instead of the cell address. £1.16

=DAVERAGE (B3:I19,"Unit Cost",E49:F50)

The average Unit Cost of a Bulb equal to a particular Wattage. Product Bulb Average of Bulb 100 is :

£0.53

Wattage 100 =DAVERAGE (B3:I19,"Unit Cost",E60:F61)

The average Unit Cost of a Bulb less then a particular Wattage. Product Bulb Average of Bulb <100 is :

£0.17

Wattage <100 =DAVERAGE (B3:I19,"Unit Cost",E67:F68)

CHOOSE Index Value 1 3 2 3 1 2

Result Alan Carol Bob 18% 10% 15%

Written By: Engr. Fakhr-e-Alam

=CHOOSE(C4,"Alan","Bob","Carol") =CHOOSE(C5,"Alan","Bob","Carol") =CHOOSE(C6,"Alan","Bob","Carol") =CHOOSE(C7,10%,15%,18%) =CHOOSE(C8,10%,15%,18%) =CHOOSE(C9,10%,15%,18%)

63

Tutorial – I

(MS-Excel)

What Does It Do? This function picks from a list of options based upon an Index value given to by the user. Syntax : =CHOOSE(UserValue, Item1, Item2, Item3 through to Item29) Example The following table was used to calculate the medals for athletes taking part in a race. The Time for each athlete is entered. The =RANK() function calculates the finishing position of each athlete. The =CHOOSE() then allocates the correct medal. The =IF() has been used to filter out any positions above 3, as this would cause the error of #VALUE to appear, due to the fact the =CHOOSE() has only three items in it. Name

Time

Alan

1:30

Bob

1:15

Carol

2:45

David

1:05

Eric

1:20

Position

Medal

=IF(D30<=3,CHOOSE (D30,"Gold","Silver","Bronze"),"unplaced") =IF(D31<=3,CHOOSE 4 unplaced (D31,"Gold","Silver","Bronze"),"unplaced") =IF(D32<=3,CHOOSE 1 Gold (D32,"Gold","Silver","Bronze"),"unplaced") =IF(D33<=3,CHOOSE 5 unplaced (D33,"Gold","Silver","Bronze"),"unplaced") =IF(D34<=3,CHOOSE 3 Bronze (D34,"Gold","Silver","Bronze"),"unplaced") =RANK(C34,C30:C34) 2

Silver

CONVERT Amount To Convert 1 1 1

Converting From in ft yd

Converting To cm m m

1 1 1.5 0.5

yr day hr mn

day hr mn sec

Written By: Engr. Fakhr-e-Alam

=CONVERT(C4,D4,E4) =CONVERT(C5,D5,E5) =CONVERT(C6,D6,E6) =CONVERT(C8,D8,E8) =CONVERT(C9,D9,E9) =CONVERT(C10,D10,E10) =CONVERT(C11,D11,E11)

64

Tutorial – I

(MS-Excel)

What Does It Do ? This function converts a value measure in one type of unit, to the same value expressed in a different type of unit, such as Inches to Centimetres. Syntax : =CONVERT(AmountToConvert,UnitToConvertFrom,UnitToConvertTo) Example The following table was used by an Import / Exporting company to convert the weight and size of packages from old style UK measuring system to European system.

Weight

Pounds 5

Ounces 3

Kilograms 2.35301 =CONVERT(D28,"lbm","kg")+ CONVERT(E28,"ozm","kg")

Height Length Width

Feet 12 8 5

Inches 6 3 2

Metres 3.81 2.5146 1.5748 =CONVERT(D34,"ft","m")+ CONVERT(E34,"in","m")

Abbreviations This is a list of all the possible abbreviations which can be used to denote measuring systems. Weight & Mass Gram Kilogram

g kg

Slug

sg

Pound mass U (atomic mass) Ounce mass

lbm

ozm

Time Year Day Hour Minute

yr day hr mn

Second

sec

u

Written By: Engr. Fakhr-e-Alam

Distance Meter Statute mile Nautical mile Inch Foot Yard Angstrom Pica (1/72 in.) Pressure Pascal Atmosphere mm of Mercury

m mi Nmi in ft yd ang Pica

Pa atm mmHg

65

Tutorial – I

(MS-Excel)

Temperature Degree Celsius Degree Fahrenheit Degree Kelvin Force Newton Dyne Pound force Energy Joule Erg Thermodynamic calorie IT calorie Electron volt Horsepowerhour Watt-hour Foot-pound BTU

C

Liquid Teaspoon

tsp

F

Tablespoon

tbs

K

N dyn lbf

Fluid ounce Cup Pint Quart Gallon Liter

oz cup pt qt gal l

J e

Power Horsepower Watt

HP W

Magnetism Tesla

T

Gauss

ga

c cal eV HPh Wh flb BTU

These characters can be used as a prefix to access further units of measure. Using "c" as a prefix to meters "m" will allow centimetres "cm" to be calculated.

Prefix exa peta tera giga mega kilo hecto dekao

Multiplier 1.00E+18 1.00E+15 1.00E+12 1.00E+09 1.00E+06 1.00E+03 1.00E+02 1.00E+01

Abbreviation E P T G M k h e

Prefix deci centi milli micro nano pico femto atto

Multiplier 1.00E-01 1.00E-02 1.00E-03 1.00E-06 1.00E-09 1.00E-12 1.00E-15 1.00E-18

Abbreviation d c m u n p f a

******************************

Written By: Engr. Fakhr-e-Alam

66