Open Excel 2007 1. Click the Start button on the Taskbar. 2. Select All Programs > Microsoft Office > Microsoft Office> Excel 2007: Open Excel 2007 1. Click the Start button on the Taskbar. 2. Select All Programs > Microsoft Office > Microsoft Office> Excel 2007 Excel 2007
PowerPoint Presentation: Workbook A single Excel file which has a collection of worksheets contained within it. Worksheet The worksheet is made up of cells. A cell can contain a value, a formula, or a text entry. NOTE : The terms worksheet and spreadsheet are often used interchangeably.
PowerPoint Presentation: Worksheet Tabs: The worksheets are accessible by clicking the worksheet tabs , just above the status bar at the bottom of the Excel screen. By default, three worksheets are included in each workbook. These are named Sheet1, Sheet2 and Sheet3. You can add, remove, move and rename worksheets . Cell: The intersection of each row and column is a cell Cells can contain text, numbers, or mathematical formulas. Each cell on the spreadsheet has a cell address that is the column letter and the row number . Each cell in a worksheet has a unique cell address , or reference, that is a combination of a column header letter and a row header number, such as A1, B1, C25, etc. The cell address can be read from the Name Box. Row: A horizontal group of cells within a worksheet. All row headings have numbers, such as 1, 2, 3, ...1000, etc. are displayed in gray buttons across the left side of the worksheet . Column: A vertical group of cells within a worksheet. All column headings have letters, such as A , B, C, … AA, AB, etc. are displayed in gray buttons across the top of the worksheet.
PowerPoint Presentation: 5. Name Box: The Name Box displays the cell reference of the active cell. It can also show the name assigned to a cell or range of cells. 6. Range: A group of two or more cells . Ranges are often referenced for formulas, printing, and designating information to be copied or cut. Ranges can be selected by clicking and dragging over the cells. 7. Active Cell: The selected cell within the black border. The active cell (Figure 1, 7) is the cell within which you will populate when data is entered. 8. Formula: mathematical equation used to calculate a value. All formulas begin with the equal sign (=). An example of a formula may be =E1+E2 or =(A1+A5)/B13 ). Function: A preset formula. Like formulas, functions begin with the equal sign ( = ) followed by the function’s name and its arguments. The function name tells Excel what calculation to perform. The arguments are contained inside round brackets . 9. Gridlines: The horizontal and vertical lines on the worksheet. These do not print, unless set to do so in the Sheet Options group on the Page Layout tab.
Excel Facts: Excel Facts The file name of the current workbook is displayed in the title bar. The name Book1 is used temporarily until the workbook is saved with a descriptive name. Click the Office Button to display commands for opening, saving, and printing a worksheet. Select commands and perform actions using the Ribbon and the Quick Access Toolbar. Click a sheet tab to display a sheet in the workbook Columns are lettered from A to Z and the AA to XFD for a total of 16,384 columns. Rows are numbered from to 1,048,576. A cell is the intersection of a row and column. Each cell can store a single item of data A cell reference is the column letter and row number that identify a single cell. A cell reference can be thought of as a cell’s name. The selected cell is called the active cell and is displayed with a bold border. The column letter and row number corresponding to the active cell are orange. Type data to place it into the active cell. The name box displays the cell reference of the active cell. The active cell contents are displayed on the formula bar. View information about the current document in the status bar.
PowerPoint Presentation: Importing data into Excel is helpful if you want to use Excel's many analytical tools and features to view, process and analyze data. When you import data from an external source, you do not link to the external data source, but create a copy of the data at that point in time. Should the external data source get updated, your Excel file does not get updated. The two most commonly used text file import file formats are: Delimited text (.txt) and Comma separated values text (. csv ). In Excel 2007, you can import up to 1,048,576 rows and 16,384 columns of data. Importing an External Data File
PowerPoint Presentation: Open a new or existing workbook. Select the Data tab. In the Get External Data group, click From Text button . The Import Text File dialog box appears. Select the Look in drop-down list, then navigate to the import file. Select the import file and click the Import button. The Text Import Wizard– Step 1 of 3 dialog box displays . Importing an External Data File
PowerPoint Presentation: In the Text Import Wizard– Step 1 of 3 dialog box, select Delimited or Fixed Width. Note: Typically the separator in a Delimited text file (.txt) is the Tab character and in Comma separated values text files (. csv ) it is typically the comma character. Click the Next button. The Text Import Wizard– Step 2 of 3 dialog box displays. Here you can select delimiter for your file. Click the Next button . The Text Import Wizard– Step 3 of 3 dialog box displays. Here you can select column data format and set other parameters. Click the Finish button. The Import Data dialog box displays. Here you can select starting cell for your data. Default is cell A1 of the existing cell. Click the OK button to place the data in the A1 cell of the active worksheet. The imported data appears in the designated location starting in the A1 cell. Note: To place the imported data elsewhere within the active worksheet, simply click on the desired cell and then click the OK button. Importing an External Data File
Cell References: Cell References Cell references are an important part of creating formulas in Excel . Using cell references allows your formulas to update automatically if the value in a particular cell changes, but using cell references properly can also assist you in updating formulas as cells are copied or moved. There are three basic types of cell references, each serving a different purpose Relative Absolute and Mixed .
PowerPoint Presentation: Relative Cell References Relative referencing is the default in Excel. Relative references will change in relation to the new location of the formula. Relative references identify cells based on their relationship to the cell containing the reference. For example, if you copied a formula from column A to column B and the formula contained a relative cell reference to cell A12, the formula would change to refer to cell B12.
PowerPoint Presentation: Absolute Cell References References that remain the same when a formula is copied to a new location. No matter where the formula or the values in the original cell are moved, the formula will continue to refer to the same cell. For example, if a formula using an absolute cell reference to cell A12 was copied to another column , the cell reference to A12 would remain constant.
PowerPoint Presentation: Relative and absolute cell references can be used in all situations that require cell references, including cell ranges and formulas. A formula, cell range, or cell reference can have both relative and absolute components. By adding a dollar sign ($) before either the column or row location or both, that reference becomes absolute ( or anchored ). When adding dollar signs to cell references, only the portion of the reference directly following the dollar sign is absolute. To keep the entire cell reference constant, place a dollar sign before both the column and row location. For example, the reference $A$12 will anchor cell A12 in your formulas where ever it may be used.
PowerPoint Presentation: To Make a Cell Reference Absolute 1. Within the desired formula, before the part of the reference that you would like to be absolute, e.g. A12, type a dollar sign ($) OR In the Formula bar, click the cell reference you want to change, e.g. A12. Then , press the F4 key on the keyboard. The cell reference changed to $A$12. Pressing F4 enables you to toggle between being relative, absolute and two mixed cell references. When pressing F4, your cell reference will cycle through the following progression: $A$12, A$12, $A12, A12.
PowerPoint Presentation: Formatting Cells Select from Here Click this button or Right click cell
About Functions : About Functions Formulas can be made much more efficient by using worksheet functions. There are over 400 different Functions in Excel 2007. Functions can be thought of as being made up of three parts: an equal sign, a function name and a list of arguments enclosed in brackets. There are several ways you can insert your functions, but the most common methods are: Select the Insert Function button from the Formulas tab. Click the Insert Function button on the Formula bar. Press Shift + F3 on the keyboard to display the Insert function dialog box. Type the function completely from scratch.
PowerPoint Presentation: Select Formulas Tab. Here you will get complete list of Formulas available in Excel 2007. Specially See Show Formula option, it is important for IGCSE ICT practical. Click on Insert Function to check function and their description After clicking function sum , you’ll get this screen. Here you can provide argument to calculate etc. Example Answer after C alculatio n
PowerPoint Presentation: The Sum Function The most commonly used function in Excel is the SUM function. The SUM function returns the total of all the numbers in a range of cells. This formula can be typed from scratch and the cell addresses do not need to be adjacent to each other. Basic Concept: =SUM(number1, number2,number3,...) Example: =SUM(B5:B8) Example 2: =SUM(B5,C8,D12,E15) AVERAGE Function Another common calculation in Excel is the AVERAGE function. The AVERAGE function is a Statistical function which returns the arithmetic mean of a list of values. In other words, it adds up the total value of all the cells selected and divides it by the number of cells selected. Basic Concept: =AVERAGE(number1, number2, number3,…) Example: =AVERAGE(B6:F6) Important Functions
PowerPoint Presentation: COUNT NUMBERS Function The COUNT NUMBERS function counts the total number of cells that contain numbers or data. Basic Concept: =COUNT(value1, value2,...) Example: =COUNT(B6:B17) Other count functions are counta and countblank to count all the filled cells and count blanks cells in the given range respectively. Another count function is countif . It is used to Counts the number of cells within a range that meet the given criteria. Syntax for count if is countif (range, criteria). Important Functions
PowerPoint Presentation: MAX Function The MAX function returns the largest (maximum) value of all the numbers evaluated by the formula. Basic Concept: =MAX(number1, number2, number3,…) Example: =MAX(B6:B17) MIN Function The MIN function returns the smallest (minimum) value of all the numbers evaluated by the formula. Basic Concept: =MIN(number1, number2, number3,…) Example: =MIN(B6:B17) Important Functions
PowerPoint Presentation: TODAY Function The Today function is a volatile Date and Time function that returns the current date. This function will update the date each time the file is opened or saved. This function does not require any arguments, but you must still include the empty parentheses after the function name. Basic Concept/Example: =TODAY() It is probably quicker to type the TODAY function from scratch. If you do, make sure you include the open and closed parenthesis after the function name – no argument is needed for this function. NOW Function The NOW function returns the current date and time. This function will update the date each time the file is opened or saved. This function does not require any arguments, but you must still include the empty parentheses after the function name. Basic Concept/Example: =NOW() Similar to the TODAY function, it is also quicker to type the NOW function from scratch. If you do, make sure you include the open and closed parenthesis after the function name – no argument is needed for this function. Important Functions
PowerPoint Presentation: IF Function The IF function is a Logical function that is designed to return one value if a condition you specify evaluates to be TRUE and another value if it evaluates to be FALSE. In other words, the function can only give one of two possible answers. Basic Concept: =IF( logical_test , value_if_true , value_if_false ) Example: =IF(E7>F7,E7*0.1,0) Important Functions
PowerPoint Presentation: Nested IF Function A Nested function is simply a function within another function. The result returned from one function is used as the argument of another function. A nested formula can contain up to seven levels of arguments and do not need to be IF functions. Example: =IF(A2>B2, "HELLO", IF(B2>B4, "BYE", "Hi!")) Important Functions Another Condition
PowerPoint Presentation: Logical Operator In excel with IF function, you can use logical operator- AND, OR and NOT. AND- Outcome will be true if and only if the outcome of all the conditions will be true. OR- Outcome will be true if outcome of atleast one condition will be true. NOT- It will reverse the outcome i.e. true to false and false to true. Example: =if(and(condition1,condition2,….), true_exp , false_exp ) Important Functions
PowerPoint Presentation: Extract The Year, Month, or Day From A Date In cell E7, you have date as 21/12/2012. Then =YEAR(E7) “ Value returned 2012” =MONTH(E7) “ Value returned 12” =DAY(E7) “ Value returned 21 Important Functions
PowerPoint Presentation: VLOOKUP Function The VLOOKUP function is a Lookup function that searches vertically for a value in the leftmost column of a (lookup) table and returns a value in the same row from another column you specify. The values in the lookup column must be sorted in ascending order. Notice the Table_array reference is entered as absolute. It is recommended to make your Table_array reference absolute when using any lookup function so that you can easily copy the formula. You can quickly anchor a reference by pressing the F4 key on the keyboard. Basic Concept: = VLOOKUP( lookup_value , table_array , col_index_num,range_lookup ) Important Functions
PowerPoint Presentation: Defining the VLOOKUP Values Lookup_value – Think of this field as your starting point. In my example, I‟ll click cell C2 so the value is filled in the dialog. I‟m requesting Excel take the value of C2, which displays as the Pcode of “A”, and find the corresponding political party on our lookup table on the Party Codes worksheet. Table_array – This is the range for your lookup table. The range can be on your existing worksheet or another worksheet such as our “Party Codes”. When you click another tab and define the range, Excel prepends that tab name to the range such as „Party Codes‟. Col_index_num – This is the number of the column on your lookup table that has the information you need. In our example, we want column 2 from the Party Codes worksheet which has the name of the political party. Range-lookup – this field defines how close a match should exist between your Lookup_value (C2) and the value in the leftmost column on our lookup table. In our case, we want an exact match so we‟ll use “FALSE”. Important Functions
PowerPoint Presentation: CONCATENATE Joins two or more text strings into one text string. Basic Concept CONCATENATE (text1,text2,...) Text1, text2, ... are 2 to 255 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references. You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example, =A1&B1 is same as =CONCATENATE(A1,B1) Important Functions
PowerPoint Presentation: Other String/Text Function =left( text,n ): used to extract first n characters from the string =right( text,n ): used to extract last n characters from the string =mid( text,n,m ): used to extract m characters from the nth character of the string. =lower(text): convert text in lower case = len (text): tells number of characters in the string including blanks. =trim(text): removes spaces from text =upper(text): convert text to uppercase Here text means one or more characters like r, ramesh or ramesh kumar . Here n and m are positive integers including zero. Important Functions