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:
alam091@yahoo.com
1
stEdition – 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
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
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………...……….. 307.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
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.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 found on the Standard toolbar to open an existing workbook.
• 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 found on the Standard toolbar to print a worksheet.
• 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 found on the Standard toolbar to correct spelling errors on the worksheet.
• Cut, Copy, Paste, and Format Painter : These actions are explained in the
Modifying Worksheets section.
• 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.
• Insert Hyperlink: To insert a hyperlink to a Web site on the Internet, type the text into a cell 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: These tools are discussed in detail in the
Formulas and Functions section.
• 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.
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.
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
Key stroke
One cell up Up arrow key
One cell down Down arrow key or enter
One cell left Left arrow key
One cell right Right arrow key or TAB
Top of the worksheet (cell A1) CTRL + Home End of the worksheet (last cell
containing data) CTRL + END
End of the row CTRL + right arrow key
End of the column CTRL + Down arrow key
Any cell 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. • Row: 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.
• Column: 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.
• Cells: 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.
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.
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
Mouse action
One cell click once in the cell
Entire row click the row label
Entire column click the column label
Entire worksheet click the whole sheet button (upper left corner of the labels “empty label”)
Cluster of cells drag mouse over the cells or hold down the
SHIFT key while using the arrow keys
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 on the Standard toolbar.
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.
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.
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.
• • •
• Number 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.
• Alignment tab - These options allow you to
change the position and alignment of the data with the cell.
• Font tab - Font attributes are displayed in this tab
including font name, size, style, and effects.
• Border and Pattern tabs - These tabs allow you to add borders, shading, and background colors to a
cell.
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).
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.
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.
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
auto-fill feature will automatically update the row numbers as shown below if the cells are referenced
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.
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.
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 from the Standard toolbar.
• 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.
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".
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
6.8 Arguments
An argument is the reference behind the function. The reference can be any of the following type:
Argument Types
Argument Example
Numbers 1,2,3
Text “January”
Logical Values (True or False) Cell References / Range B7 or B7:B20
6.9 Operators
Operators are mathematical symbols that are broken into four categories
Keystroke Operators
Arithmetic Explanation Example
+ Addition 2+3 - Subtraction 5-1 * Multiplication 7*3 / Division 8/2 % Percent 90% ^ Exponentiation 7^2
Comparison Explanation Example
= Equal to B1=D1
> Greater than B1>D1
< Less than B1<D1
>= Greater than or equal to B1>=D1 <= Less than or equal to B1<=D1
<> Not equal to B1<>D1
Text Explanation Example
& Adjoins text or cell references "Scott" & "Hi" produces "Scott Hi"
Reference Explanation Example
: Includes cells of a column or row
between the designated limits B3 : B20 , Separates arguments in a function (B3, B20)
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 Example
1 % Percent 2 ^ Exponentiation 3 *, / Multiplication, Division 4 +,- Addition, Subtraction 5 & Ampersand 6 >,>=,<,<=,=,<> 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.
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
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 Meaning How to Fix
#### The column is too narrow to display the result of calculation
Widen the column
# VALUE Wrong type of argument or reference
Check operands and arguments
#DIV/0! Data is attempting to divide by zero
Change the value or the cell reference so that the formula doesn't divide by zero
#NAME? Formula is referencing an invalid name
Be sure the name still exists or correct the misspelling
#REF!
Excel can't locate the referenced cells(for example, the cells were deleted)
Click Undo to restore references and then
change formula references #NULL Reference to intersection of two
areas that do not intersect
Check for typing and reference errors
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 found on the Standard toolbar to view the Chart Wizard
3. In the Chart Type dialog box: Choose the Chart type and the Chart subtype if necessary. Click
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.
• 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.
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.
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.
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).
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 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.
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.
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.
10. Excel Functions Overview
10.1 Statistical Functions Overview
Statistical functions are among the most widely used functions in Excel.
Function Function Description
AVERAGE(range) Calculates the mean (arithmetic average) of a range of cells
COUNT(range) Counts the number of values (cells containing numbers in a range)
COUNTIF(range, value) Counts the number of cells that are the same as a specified value.
MAX(range) Returns the maximum value of a data set.
MIN(range) Returns the minimum value of a data set.
MODE(range) Returns the most frequently occurring, or repetitive, value in a range of data.
STDEV(range) 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.
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.
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))
Appendix – I
Microsoft Excel S hortcut 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
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+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 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
Appendix – II
Important Formulas
SUM
Horizontal 100 200 300 600 =SUM(C4:E4) Vertical 100 200 300 600 =SUM(C7:C9) Single Cells 100 300 600 =SUM(C13,D14,E13) 200 Multiple Ranges 100 400 200 500 3000 600 4800 =SUM(C17:C19,E17:E19) Functions 100 400 200 500 300 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).
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 Sales Running Total Jan 10 10 =SUM($D$7:D7) Feb 50 60 =SUM($D$7:D8) Mar 30 90 =SUM($D$7:D9) Apr 20 110 =SUM($D$7:D10) May 110 =SUM($D$7:D11) Jun 110 =SUM($D$7:D12) Jul 110 =SUM($D$7:D13) Aug 110 =SUM($D$7:D14) Sep 110 =SUM($D$7:D15) Oct 110 =SUM($D$7:D16) Nov 110 =SUM($D$7:D17) Dec 110 =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.
The function can be tidied up to show 0 zero when there is no adjacent value by using the =IF() function.
Month Sales Running Total Jan 10 10 =SUM(IF(D7,$D$7:D7,0)) Feb 50 60 =SUM(IF(D8,$D$7:D8,0)) Mar 30 90 =SUM(IF(D9,$D$7:D9,0)) Apr 20 110 =SUM(IF(D10,$D$7:D10,0)) May 0 =SUM(IF(D11,$D$7:D11,0)) Jun 0 =SUM(IF(D12,$D$7:D12,0))
Jul 0 The =SUM() only takes place when there is data in Aug 0 column D. Otherwise the value 0 zero is entered.
Sep 0 Oct 0 Nov 0 Dec 0
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.
Jan Feb Mar
North 45 50 50 South 30 25 35 East 35 10 50 West 20 50 5 Total 130 135 140
If it does not work !
The feature may have been switched off on your computer.
SUMIF
Item Date Cost
Brakes 1-Jan-98 80 Tyres 10-May-98 25 Brakes 1-Feb-98 80 Service 1-Mar-98 150 Service 5-Jan-98 300 Window 1-Jun-98 50 Tyres 1-Apr-98 200 Tyres 1-Mar-98 100 Clutch 1-May-98 250
Total cost of all Brakes bought. 160 =SUMIF(C4:C12,"Brakes",E4:E12)
Total cost of all Tyres bought. 325 =SUMIF(C4:C12,"Tyres",E4:E12)
Total of items costing £100 or above. 1000 =SUMIF(E4:E12,">=100")
Total of item typed in following cell. service 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 Product 2 3 6 =PRODUCT(C4,D4) 5 10 50 =PRODUCT(C5:D5) 3 7 210 =PRODUCT(C6:D6,10) 6300 =PRODUCT(C4:D6)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 Sold price
Tyres 5 100
Filters 2 10
Bulbs 3 2
Total Sales Value : 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.
