Tutorial to learn EXCEL for data analysis
MS Excel 2007
INDEX
Getting started with Excel 2007 you will notice that there are many similar features to previous versions. You will also notice that there are many new features that you’ll be able to utilize. There are three features that you should remember as you work within Excel 2007: the Microsoft Office Button, the Quick Access Toolbar, and the Ribbon. The function of these features will be more fully explored below.
Spreadsheets
A spreadsheet is an electronic document that stores various types of data. There are vertical columns and horizontal rows. A cell is where the column and row intersect. A cell can contain data and can be used in calculations of data within the spreadsheet. An Excel spreadsheet can contain workbooks and worksheets. The workbook is the holder for related worksheets.
Microsoft Office Button
The Microsoft Office Button performs many of the functions that were located in the File menu of older versions of Excel. This button allows you to create a new workbook, Open an existing workbook, save and save as, print, send, or close.
Ribbon
The ribbon is the panel at the top portion of the document It has seven tabs: Home, Insert, Page Layouts, Formulas, Data, Review, and View. Each tab is divided into groups. The groups are logical collections of features designed to perform function that you will utilize in developing or editing your Excel spreadsheets.
Commonly utilized features are displayed on the Ribbon. To view additional features within each group, click the arrow at the bottom right corner of each group.
Home: Clipboard, Fonts, Alignment, Number, Styles, Cells, Editing
Insert: Tables, Illustrations, Charts, Links, Text
Page Layouts: Themes, Page Setup, Scale to Fit, Sheet Options, Arrange
Formulas: Function Library, Defined Names, Formula Auditing, Calculation
Data: Get External Data, Connections, Sort & Filter, Data Tools, Outline
Review: Proofing, Comments, Changes
View: Workbook Views, Show/Hide, Zoom, Window, Macros
Quick Access Toolbar
The quick access toolbar is a customizable toolbar that contains commands that you may want to use. You can place the quick access toolbar above or below the ribbon. To change the location of the quick access toolbar, click on the error at the end of the toolbar and click Show Below the Ribbon.
You can also add items to the quick access toolbar. Right click on any item in the Office Button or the Ribbon and click Add to Quick Access Toolbar and a shortcut will be added.
Mini Toolbar
A new feature in Office 2007 is the Mini Toolbar. This is a floating toolbar that is displayed when you select text or right-click text. It displays common formatting tools, such as Bold, Italics, Fonts, Font Size and Font Color.
Excel 2007 offers a wide range of customizable options that allow you to make Excel work the best for you. To access these customizable options:
§ Click the Office Button
§ Click Excel Options
Popular
These features allow you to personalize your work environment with the mini toolbar, color schemes, default options for new workbooks, customize sort and fill sequences user name and allow you to access the Live Preview feature. The Live Preview feature allows you to preview the results of applying design and formatting changes without actually applying it.
Formulas
This feature allows you to modify calculation options, working with formulas, error checking, and error checking rules.
Proofing
This feature allows you personalize how word corrects and formats your text. You can customize auto correction settings and have word ignore certain words or errors in a document through the Custom Dictionaries.
Save
This feature allows you personalize how your workbook is saved. You can specify how often you want auto save to run and where you want the workbooks saved.
Advanced
This feature allows you to specify options for editing, copying, pasting, printing, displaying, formulas, calculations, and other general settings.
Customize
Customize allows you to add features to the Quick Access Toolbar. If there are tools that you are utilizing frequently, you may want to add these to the Quick Access Toolbar.
Create a Workbook
To create a new Workbook:
§ Click the Microsoft Office Toolbar
§ Click New
§ Choose Blank Document
If you want to create a new document from a template, explore the templates and choose one that fits your needs.
Save a Workbook
When you save a workbook, you have two choices: Save or Save As. To save a document:
§ Click the Microsoft Office Button
§ Click Save
You may need to use the Save As feature when you need to save a workbook under a different name or to save it for earlier versions of Excel. Remember that older versions of Excel will not be able to open an Excel 2007 worksheet unless you save it as an Excel 972003 Format. To use the Save As feature:
§ Click the Microsoft Office Button
§ Click Save As
§ Type in the name for the Workbook
§ In the Save as Type box, choose Excel 97-2003 Workbook
Open a Workbook
To open an existing workbook:
§ Click the Microsoft Office Button
§ Click Open
§ Browse to the workbook
§ Click the title of the workbook ? Click Open
Entering Data
There are different ways to enter data in Excel: in an active cell or in the formula bar. To enter data in an active cell:
§ Click in the cell where you want the data
§ Begin typing
To enter data into the formula bar
§ Click the cell where you would like the data
§ Place the cursor in the Formula Bar ? Type in the data
Excel allows you to move, copy, and paste cells and cell content through cutting and pasting and copying and pasting.
Select Data
To select a cell or data to be copied or cut:
§ Click the cell
§ Click and drag the cursor to select many cells in a range
Select a Row or Column
To select a row or column click on the row or column header.
Copy and Paste
To copy and paste data:
§ Select the cell(s) that you wish to copy
§ On the Clipboard group of the Home tab, click Copy
§ Select the cell(s) where you would like to copy the data ? On the Clipboard group of the Home tab, click Paste
Cut and Paste
To cut and paste data:
§ Select the cell(s) that you wish to copy
§ On the Clipboard group of the Home tab, click Cut
§ Select the cell(s) where you would like to copy the data
§ On the Clipboard group of the Home tab, click Paste
Undo and Redo
To undo or redo your most recent actions:
? On the Quick Access Toolbar ? Click Undo or Redo
Auto Fill
The Auto Fill feature fills cell data or series of data in a worksheet into a selected range of cells. If you want the same data copied into the other cells, you only need to complete one cell. If you want to have a series of data (for example, days of the week) fill in the first two cells in the series and then use the auto fill feature. To use the Auto Fill feature:
§ Click the Fill Handle
§ Drag the Fill Handle to complete the cells
Insert Cells, Rows, and Columns
To insert cells, rows, and columns in Excel:
§ Place the cursor in the row below where you want the new row, or in the column to the left of where you want the new column
§ Click the Insert button on the Cells group of the Home tab
§ Click the appropriate choice: Cell, Row, or Column
Delete Cells, Rows and Columns
To delete cells, rows, and columns:
§ Place the cursor in the cell, row, or column that you want to delete
§ Click the Delete button on the Cells group of the Home tab ? Click the appropriate choice: Cell, Row, or Column
Find and Replace
To find data or find and replace data:
§ Click the Find & Select button on the Editing group of the Home tab
§ Choose Find or Replace
§ Complete the Find What text box
§ Click on Options for more search options
Go To Command
The Go To command takes you to a specific cell either by cell reference (the Column Letter and the Row Number) or cell name.
? Click the Find & Select button on the Editing group of the Home tab ? Click Go To
Spell Check
To check the spelling:
? On the Review tab click the Spelling button
Excel Formulas
A formula is a set of mathematical instructions that can be used in Excel to perform calculations. Formals are started in the formula box with an = sign.
There are many elements to and excel formula.
References: The cell or range of cells that you want to use in your calculation
Operators: Symbols (+, -, *, /, etc.) that specify the calculation to be performed
Constants: Numbers or text values that do not change Functions: Predefined formulas in Excel
To create a basic formula in Excel:
§ Select the cell for the formula
§ Type = (the equal sign) and the formula
§ Click Enter
Calculate with Functions
A function is a built in formula in Excel. A function has a name and arguments (the mathematical function) in parentheses. Common functions in Excel:
Sum: Adds all cells in the argument
Average: Calculates the average of the cells in the argument
Min: Finds the minimum value
Max: Finds the maximum value
Count: Finds the number of cells that contain a numerical value within a range of the argument
To calculate a function:
§ Click the cell where you want the function applied
§ Click the Insert Function button
§ Choose the function ? Click OK
§ Complete the Number 1 box with the first cell in the range that you want calculated ? Complete the Number 2 box with the last cell in the range that you want calculated
Function Library
The function library is a large group of functions on the Formula Tab of the Ribbon. These functions include:
AutoSum: Easily calculates the sum of a range
Recently Used: All recently used functions
Financial: Accrued interest, cash flow return rates and additional financial functions Logical: And, If, True, False, etc.
Text: Text based functions
Date & Time: Functions calculated on date and time
Math & Trig: Mathematical Functions
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 fixed. For example, in the formula "=(A$1+$B2)", the row of cell A1 is fixed and the column of cell B2 is fixed.
Consider the following example. Suppose we need to calculate HRA for each employee. HRA is based on a condition i.e., For east region it is 50% of Basic Salary for others 48%.
Emp Code | First Name | Last Name | Region | Dept | DOJ | Qualification | Basic Salary | HRA |
1 | Amit | Sharma | East | Admin | 2/28/2005 | Graduate | 18000 | |
2 | sumit | verma | West | HR | 7/16/2007 | Post Graduate | 22000 | |
3 | anuRadha | gupta | West | Finance | 7/16/2007 | Post Graduate | 22000 | |
4 | rosy | Fernandise | West | Finance | 12/4/2007 | Post Graduate | 22000 |
IF function can be used in such cases, when we have to return one value if a condition evaluates to TRUE and another value if it evaluates to FALSE.
Syntax
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 that is returned if logical_test is TRUE.
Value_if_false is the value that is returned if logical_test is FALSE.
How to use If for the above example
Below HRA heading type the following formula.
=IF(D2=”east”, H2 * 50%, H2 * 48%)
Logical Test
Consider the following example. Suppose we need to calculate HRA for each employee. HRA is different for different region i.e., East region it is 50% of Basic Salary, West region it is 48%, North region 35% and South region 55%.
=IF(D2=”east”, H2 * 50%, IF(D2=”west” , H2 * 48% ,IF(D2=”north” , H2 *55%)))
Remarks
• Up to seven IF functions can be nested as value_if_true and value_if_false arguments to construct more elaborate tests.
• When the value_if_true and value_if_false arguments are evaluated, IF returns the value returned by those statements.
Consider the following example. Suppose we need to calculate HRA for each employee. HRA is based on multiple conditions and if all the conditions are satisfied then only employee will get that particular percentage i.e., For east region of Admin department, it is 50% of Basic Salary for others 48%.
Emp Code | First Name | Last Name | Region | Dept | DOJ | Qualification | Basic Salary | HRA |
1 | Amit | Sharma | East | Admin | 2/28/2005 | Graduate | 18000 | |
2 | sumit | verma | West | HR | 7/16/2007 | Post Graduate | 22000 | |
3 | anuRadha | gupta | West | Finance | 7/16/2007 | Post Graduate | 22000 | |
4 | rosy | Fernandise | West | Finance | 12/4/2007 | Post Graduate | 22000 |
AND function can be used in such cases.
Syntax
AND(logical1,logical2, ) Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
Logical1, logical2, are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
How to use AND for the above requirement
Below HRA heading type the following formula
=IF(AND(D2=”east” , E2=”admin”), H2 * 50%, H2 * 48%)
Logical Test for IF
Consider the following example. Suppose we need to calculate HRA for each employee. HRA is based on multiple conditions and if any one condition is satisfied employee will get that particular percentage i.e., For Admin, HR department, it is 50% of Basic Salary for others 48%.
Emp Code | First Name | Last Name | Region | Dept | DOJ | Qualification | Basic Salary | HRA |
1 | Amit | Sharma | East | Admin | 2/28/2005 | Graduate | 18000 | |
2 | sumit | verma | West | HR | 7/16/2007 | Post Graduate | 22000 | |
3 | anuRadha | gupta | West | Finance | 7/16/2007 | Post Graduate | 22000 | |
4 | rosy | Fernandise | West | Finance | 12/4/2007 | Post Graduate | 22000 |
OR function can be used in such cases. OR returns TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
Syntax OR(logical1,logical2, )
Logical1, logical2, are 1 to 30 conditions you want to test that can be either TRUE or FALSE.
How to use OR for the above requirement
Below HRA heading type the following formula
=IF(OR(E2=”Admin” , E2=”HR”), H2 * 50%, H2 * 48%)
Logical Test for IF
Consider the following example. Suppose we need to add the salaries of east region
Emp First Name Last Name Region Dept DOJ Qualification Basic
Code Salary
1 Amit Sharma East Admin 2/28/2005 Graduate 18000
2 sumit verma West HR 7/16/2007 Post Graduate 22000
3 anuRadha gupta West Finance 7/16/2007 Post Graduate 22000
4 rosy Fernandise West Finance 12/4/2007 Post Graduate 22000
5 Range to evaluate theRamesh Kelkar East Finance Range to add the values1/1/2007 Post Graduate 22000
6 conditionAmit Gujar East HR 7/16/2007 Graduate 15500
7 Dhanashree Namdeo East Finance 1/2/2006 Post Graduate 22000
8 Beena Roy North HR 12/4/2006 Post Graduate 21500
9 Vimal Kelkar North IT 7/16/2007 Post Graduate 22000
10 Pinki Bijore South Admin 1/2/2006 Post Graduate 21500 SUMIF function can be used in such cases.It adds the cells specified by a given criteria.
Syntax SUMIF(range, criteria, sum_range)
Range is the range of cells you want evaluated.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples".
Sum_range are the actual cells to sum.
How to use SUMIF for the above requirement
=SUMIF(D2 : D11 , “east” , H2 : H10 )
Range to evaluate the condition Range to add the values
Consider the following example. Suppose we need to count employees working in east region.
Emp First Name Last Name Region Dept DOJ Qualification Basic
Code Salary
1 Amit Sharma East Admin 2/28/2005 Graduate 18000
2 sumit verma West HR 7/16/2007 Post Graduate 22000
3 anuRadha gupta West Finance 7/16/2007 Post Graduate 22000
4Range to evaluate the r sy Fernandise West Finance 12/4/2007 Post Graduate 22000
5condition to countRamesh Kelkar East Finance 1/1/2007 Post Graduate 22000
6 Amit Gujar East HR 7/16/2007 Graduate 15500
7 Dhanashree Namdeo East Finance 1/2/2006 Post Graduate 22000
8 Beena Roy North HR 12/4/2006 Post Graduate 21500
9 Vimal Kelkar North IT 7/16/2007 Post Graduate 22000
10 Pinki Bijore South Admin 1/2/2006 Post Graduate 21500
COUNTIF function can be used in such cases.
Counts the number of cells within a range that meet the given criteria.
Syntax COUNTIF(range,criteria)
Range is the range of cells from which you want to count cells.
Criteria is the criteria in the form of a number, expression, or text that defines which cells will be counted.
How to use COUNTIF for the above requirement
=COUNTIF(D2 : D11 , “east” )
Range to evaluate the Criteria to check condition to count count
Searches for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify in the table. The V in VLOOKUP stands for "Vertical."
Syntax VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.
Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.
• If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: , -2, -1, 0, 1, 2, , A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
• You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.
• The values in the first column of table_array can be text, numbers, or logical values.
• Uppercase and lowercase text are equivalent.
Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.
Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
Remarks
• If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
• If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.
• If VLOOKUP can't find lookup_value, and range_lookup is FALSE, VLOOKUP returns the #N/A value
Example 1 A B C Density Viscosity Temperature
1 0.457 3.55 500 2 0.525 3.25 400 3 0.616 2.93 300 4 0.675 2.75 250 5 0.746 2.57 200 6 0.835 2.38 150
7 0.946 2.17 100
8 1.09 1.95 50
9 1.29 1.71 0
Formula Description (Result)
=VLOOKUP(1,A2:C10,2) Looks up 1 in column A, and returns the value from column B in the same row (2.17)
Looks up 1 in column A, and returns the value from
=VLOOKUP(1,A2:C10,3,TRUE) column C in the same row (100)
=VLOOKUP(.7,A2:C10,3,FALSE) Looks up 0.746 in column A. Because there is no exact match in column A, an error is returned
(#N/A)
=VLOOKUP(0.1,A2:C10,2,TRUE) Looks up 0.1 in column A. Because 0.1 is less than
the smallest value in column A, an error is returned
(#N/A)
Looks up 2 in column A, and returns the value from
=VLOOKUP(2,A2:C10,2,TRUE)
column B in the same row (1.71)
Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUP when your comparison values are located in a column to the left of the data you want to find. The H in HLOOKUP stands for "Horizontal."
Syntax HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value is the value to be found in the first row of the table. Lookup_value can be a value, a reference, or a text string.
Table_array is a table of information in which data is looked up. Use a reference to a range or a range name.
The values in the first row of table_array can be text, numbers, or logical values.
• If range_lookup is TRUE, the values in the first row of table_array must be placed in ascending order: -2, -1, 0, 1, 2, , A-Z, FALSE, TRUE; otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.
• Uppercase and lowercase text are equivalent.
• You can put values in ascending order, left to right, by selecting the values and then clicking Sort on the Data menu. Click Options, click Sort left to right, and then click OK. Under Sort by, click the row in the list, and then click Ascending.
Row_index_num is the row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array, a row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! error value; if row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error value.
Range_lookup is a logical value that specifies whether you want HLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.
Remarks
If HLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.
If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value. Example
A B C
1 Axles Bearings Bolts
2 4 4 9 3 5 7 10 5 6 8 11
Formula
• =HLOOKUP("Axles",A1:C4,2,TRUE) Looks up Axles in row 1, and returns the value from row 2 that's in the same column. (4)
• =HLOOKUP("Bearings",A1:C4,3,FALSE) Looks up Bearings in row 1, and returns the value from row 3 that's in the same column. (7)
• =HLOOKUP("B",A1:C4,3,TRUE) Looks up B in row 1, and returns the value from row 3 that's in the same column. Because B is not an exact match, the next largest value that is less than B is used: Axles. (5)
• =HLOOKUP("Bolts",A1:C4,4) Looks up Bolts in row 1, and returns the value from row 4 that's in the same column. (11)
• =HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE) Looks up 3 in the first row of the array constant, and returns the value from row 2 in same column. (c)
ISERROR Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
=IF(ISERROR(VLOOKUP(A2,Incentives!$A$1:$B$10,2,0)),0,H2* VLOOKUP(A2,Incentives!$A$1:$B$10,2,0))
Linking Worksheets
You may want to use the value from a cell in another worksheet within the same workbook in a formula. For example, the value of cell A1 in the current worksheet and cell A2 in the second worksheet can be added using the format "sheetname!celladdress". The formula for this example would be "=A1+Sheet2!A2" where the value of cell A1 in the current worksheet is added to the value of cell A2 in the worksheet named "Sheet2".
Sorting and Filtering allow you to manipulate data in a worksheet based on given set of criteria.
Basic Sorts
To execute a basic descending or ascending sort based on one column:
§ Highlight the cells that will be sorted
§ Click the Sort & Filter button on the Home tab
§ Click the Sort Ascending (A-Z) button or Sort Descending (Z-A) button
Custom Sorts
To sort on the basis of more than one column:
§ Click the Sort & Filter button on the Home tab
§ Choose which column you want to sort by first
§ Click Add Level
§ Choose the next column you want to sort ? Click OK
Filtering
Filtering allows you to display only data that meets certain criteria. To filter:
§ Click the column or columns that contain the data you wish to filter
§ On the Home tab, click on Sort & Filter
§ Click Filter button
§ Click the Arrow at the bottom of the first cell
§ Click the Text Filter
§ Click the Words you wish to Filter
§ To clear the filter click the Sort & Filter button
§ Click Clear
The Advanced Filter command on the Data tab lets you use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.) to filter a range, but it works differently from the AutoFilter command in several important ways.
1 Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure there is at least one blank row between the criteria values and the range.
2 On the Data menu, point to Filter, and then click Advanced Filter.
Adding a Picture
To add a picture:
§ Click the Insert tab
§ Click the Picture button
§ Browse to the picture from your files
§ Click the name of the picture
§ Click Insert
§ To move the graphic, click it and drag it to where you want it
Adding Clip Art
To add Clip Art:
§ Click the Insert tab
§ Click the Clip Art button
§ Search for the clip art using the search Clip Art dialog box
§ Click the clip art
§ To move the graphic, click it and drag it to where you want it
Editing Pictures and Clip Art
When you add a graphic to the worksheet, an additional tab appears on the Ribbon. The Format tab allows you to format the pictures and graphics. This tab has four groups:
Adjust: Controls the picture brightness, contrast, and colors
Picture Style: Allows you to place a frame or border around the picture and add effects
Arrange: Controls the alignment and rotation of the picture
Size: Cropping and size of graphic
Adding Shapes
To add Shape:
§ Click the Insert tab
§ Click the Shapes button
§ Click the shape you choose
§ Click the Worksheet
§ Drag the cursor to expand the Shape
To format the shapes:
§ Click the Shape
§ Click the Format tab
Adding SmartArt
SmartArt is a feature in Office 2007 that allows you to choose from a variety of graphics, including flow charts, lists, cycles, and processes. To add SmartArt:
§ Click the Insert tab
§ Click the SmartArt button
§ Click the SmartArt you choose
§ Select the Smart Art
§ Drag it to the desired location in the worksheet
To format the SmartArt:
§ Select the SmartArt
§ Click either the Design or the Format tab
§ Click the SmartArt to add text and pictures.
Charts allow you to present information contained in the worksheet in a graphic format. Excel offers many types of charts including: Column, Line, Pie, Bar, Area, Scatter and more. To view the charts available click the Insert Tab on the Ribbon.
Create a Chart
To create a chart:
§ Select the cells that contain the data you want to use in the chart
§ Click the Insert tab on the Ribbon
§ Click the type of Chart you want to create
Modify a Chart
Once you have created a chart you can do several things to modify the chart.
To move the chart:
§ Click the Chart and Drag it another location on the same worksheet, or
§ Click the Move Chart button on the Design tab
§ Choose the desired location (either a new sheet or a current sheet in the workbook)
To change the data included in the chart:
§ Click the Chart
§ Click the Select Data button on the Design tab
To reverse which data are displayed in the rows and columns:
§ Click the Chart
§ Click the Switch Row/Column button on the Design tab
To modify the labels and titles:
§ Click the Chart
§ On the Layout tab, click the Chart Title or the Data Labels button
§ Change the Title and click Enter
Chart Tools
The Chart Tools appear on the Ribbon when you click on the chart. The tools are located on three tabs: Design, Layout, and Format.
Within the Design tab you can control the chart type, layout, styles, and location.
Within the Layout tab you can control inserting pictures, shapes and text boxes, labels, axes, background, and analysis.
Within the Format tab you can modify shape styles, word styles and size of the chart.
§ Select the chart
§ Click Copy on the Home tab
§ Go to the Word document where you want the chart located
§ Click Paste on the Home tab
Convert Text to Columns
Sometimes you will want to split data in one cell into two or more cells. You can do this easily by utilizing the Convert Text to Columns Wizard.
§ Highlight the column in which you wish to split the data
§ Click the Text to Columns button on the Data tab
§ Click Delimited if you have a comma or tab separating the data, or click fixed widths to set the data separation at a specific size.
Modify Fonts
Modifying fonts in Excel will allow you to emphasize titles and headings. To modify a font:
§ Select the cell or cells that you would like the font applied
§ On the Font group on the Home tab, choose the font type, size, bold, italics, underline, or color
Format Cells Dialog Box
In Excel, you can also apply specific formatting to a cell. To apply formatting to a cell or group of cells:
§ Select the cell or cells that will have the formatting
§ Click the Dialog Box arrow on the Alignment group of the Home tab
There are several tabs on this dialog box that allow you to modify properties of the cell or cells.
Number: Allows for the display of different number types and decimal places
Alignment: Allows for the horizontal and vertical alignment of text, wrap text, shrink text, merge cells and the direction of the text.
Font: Allows for control of font, font style, size, color, and additional features
Border: Border styles and colors Fill: Cell fill colors and styles
Add Borders and Colors to Cells
Borders and colors can be added to cells manually or through the use of styles. To add borders manually:
§ Click the Borders drop down menu on the Font group of the Home tab ? Choose the appropriate border
To apply colors manually:
§ Click the Fill drop down menu on the Font group of the Home tab ? Choose the appropriate color
To apply borders and colors using styles:
§ Click Cell Styles on the Home tab
§ Choose a style or click New Cell Style
Change Column Width and Row Height
To change the width of a column or the height of a row:
§ Click the Format button on the Cells group of the Home tab
§ Manually adjust the height and width by clicking Row Height or Column Width
§ To use AutoFit click AutoFit Row Height or AutoFit Column Width
Hide or Unhide Rows or Columns
To hide or unhide rows or columns:
§ Select the row or column you wish to hide or unhide
§ Click the Format button on the Cells group of the Home tab
§ Click Hide & Unhide
Merge Cells
To merge cells select the cells you want to merge and click the Merge & Center button on the Alignment group of the Home tab. The four choices for merging cells are:
Merge & Center: Combines the cells and centers the contents in the new, larger cell
Merge Across: Combines the cells across columns without centering data
Merge Cells: Combines the cells in a range without centering
Unmerge Cells: Splits the cell that has been merged
Align Cell Contents
To align cell contents, click the cell or cells you want to align and click on the options within the Alignment group on the Home tab. There are several options for alignment of cell contents:
Top Align: Aligns text to the top of the cell
Middle Align: Aligns text between the top and bottom of the cell
Bottom Align: Aligns text to the bottom of the cell
Align Text Left: Aligns text to the left of the cell
Center: Centers the text from left to right in the cell
Align Text Right: Aligns text to the right of the cell
Decrease Indent: Decreases the indent between the left border and the text
Increase Indent: Increase the indent between the left border and the text
Orientation: Rotate the text diagonally or vertically
Format Worksheet Tab
You can rename a worksheet or change the color of the tabs to meet your needs. To rename a worksheet:
§ Open the sheet to be renamed
§ Click the Format button on the Home tab
§ Click Rename sheet
§ Type in a new name ? Press Enter
To change the color of a worksheet tab:
§ Open the sheet to be renamed
§ Click the Format button on the Home tab
§ Click Tab Color ? Click the color
Reposition Worksheets in a Workbook
To move worksheets in a workbook:
§ Open the workbook that contains the sheets you want to rearrange
§ Click and hold the worksheet tab that will be moved until an arrow appears in the left corner of the sheet
§ Drag the worksheet to the desired location
Insert and Delete Worksheets
To insert a worksheet
§ Open the workbook
§ Click the Insert button on the Cells group of the Home tab ? Click Insert Sheet
§ Open the workbook
§ Click the Delete button on the Cells group of the Home tab
§ Click Delete Sheet
Copy and Paste Worksheets:
To copy and paste a worksheet:
§ Click the tab of the worksheet to be copied
§ Right click and choose Move or Copy
§ Choose the desired position of the sheet
§ Click the check box next to Create a Copy
§ Click OK
Set Print Titles
The print titles function allows you to repeat the column and row headings at the beginning of each new page to make reading a multiple page sheet easier to read when printed. To Print Titles:
§ Click the Page Layout tab on the Ribbon
§ Click the Print Titles button
§ In the Print Titles section, click the box to select the rows/columns to be repeated
§ Select the row or column
§ Click the Select Row/Column Button
§ Click OK
Create a Header or Footer
To create a header or footer:
Click the Header & Footer button on the Insert tab
This will display the Header & Footer Design Tools Tab
§ To switch between the Header and Footer, click the Go to Header or Go to Footer button
§ To insert text, enter the text in the header or footer
§ To enter preprogrammed data such as page numbers, date, time, file name or sheet name, click the appropriate button
§ To change the location of data, click the desired cell
Set Page Margins
To set the page margins:
§ Click the Margins button on the Page Layout tab ? Select one of the give choices, or
§ Click Custom Margins
§ Complete the boxes to set margins
§ Click Ok
Change Page Orientation
To change the page orientation from portrait to landscape:
Click the Orientation button on the Page Layout tab
Choose Portrait or Landscape
Set Page Breaks
You can manually set up page breaks in a worksheet for ease of reading when the sheet is printed. To set a page break:
? Click the Breaks button on the Page Layout tab ? Click Insert Page Break
Print a Range
There may be times when you only want to print a portion of a worksheet. This is easily done through the Print Range function. To print a range:
§ Select the area to be printed
§ Click the Print Area button on the Page Layout tab
§ Click Select Print Area
Split a Worksheet
You can split a worksheet into multiple resizable panes for easier viewing of parts of a worksheet. To split a worksheet:
§ Select any cell in center of the worksheet you want to split
§ Click the Split button on the View tab
§ Notice the split in the screen, you can manipulate each part separately
Freeze Rows and Columns
You can select a particular portion of a worksheet to stay static while you work on other parts of the sheet. This is accomplished through the Freeze Rows and Columns Function. To Freeze a row or column:
§ Click the Freeze Panes button on the View tab
§ Either select a section to be frozen or click the defaults of top row or left column
§ To unfreeze, click the Freeze Panes button
§ Click Unfreeze
Hide Worksheets
To hide a worksheet:
§ Select the tab of the sheet you wish to hide
Right-click on the tab
Click Hide
To unhide a worksheet:
§ Right-click on any worksheet tab
§ Click Unhide
§ Choose the worksheet to unhide
A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data.
Data appropriate for a pivot table
A pivot table requires that your data is in the form of a rectangular database. You can store the database in either a worksheet range (which can be a table or just a normal range) or an external database file. Although Excel can generate a pivot table from any database, not all databases benefit.Generally speaking, fields in a database table consist of two types:
Data: Contains a value or data to be summarized. For the bank account example, the Amount field is a data field.
Category: Describes the data. For the bank account data, the Date, AcctType, OpenedBy, Branch, and Customer fields are category fields because they describe the data in the Amount field.A single database table can have any number of data fields and category fields. When you create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the values in the category fields appear in the pivot table as rows, columns, or filters.
1. Select Insert tab from the Ribvbon
2. Select a table or range is already selected and the Table/Range field shows the range of the selected data. New Worksheet is also selected by default as the place where the report will be placed.
o .
3. Click Existing Worksheet and select a worksheet, if you do not want the pivot table to appear in a new worksheet.
4. Click OK.
When you create a pivot table, each column label in your data becomes a field that can be used in the report. The Field List appears on the right side of the report, while the layout area appears on the left.
1. Select the check box next to the Salesperson field in the PivotTable Field List. The field will appear in the drag and drop area at the bottom of the field list and in the layout area. The order amount data appears on the right. This is a default setting in Excel – data with numbers will always appear on the right.
2. Select the check box next to the Order Amount field in the PivotTable Field List. The field will appear in the drag and drop area at the bottom of the field list and in the layout area. All of the salesperson data appears on the left side as rows.
• Click and drag a field from one area to another.
• Release the mouse button to drop the field in the new area. In this example, we move Region from Report Filter to Column Label. The pivot table report will change.
OR
• Right-click one of the rows. A menu will appear.
• Select Move and then select a move option.
• Select the Pivot Chart command from the Options tab. The Insert Chart dialog box appears.
• Select the chart you’d like to insert.
• Click OK. The chart will now appear on the same sheet as the Pivot Table.
•
Imagine you have a spreadsheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw data. Excel gives us several tools that will make this task easier. One of these tools is called conditional formatting. With conditional formatting, you can apply formatting to one or more cells based on the value of the cell.
You have many conditional formatting rules, or options, that you can apply to cells in your spreadsheet. Each rule will affect selected cells differently. Before you choose a formatting rule, you need to identify what questions you are trying to answer.
For example, in a sales spreadsheet, you might want to identify the salespeople with lower than average sales. To do this, you need to choose a conditional formatting rule that will show you this answer. Not all of the options will provide you with this information.
Some of the Conditional Formatting Options Include:
• Highlight Cell Rules: This rule highlights specific cells based on your option choice. For example, you can choose for Excel to highlight cells that are greater than, less than, or equal to a number, and between two numbers. Also, you can choose for Excel to highlight cells that contain specific text, including a specific date. If you choose this option, a dialog box will appear, and you will have to specify the cells to highlight, and the color you would like to highlight the cells.
• Top/Bottom Rules: This conditional formatting option highlights cell values that meet specific criteria, such as top or bottom 10%, above average, and below average. If you choose this option, a dialog box will appear, and you will have to specify the cells to highlight, and the color you would like to highlight the cells.
• Data Bars: This is an interesting option that formats the selected cells with colored bars. The length of the data bar represents the value in the cell. The longer the bar, the higher the value.
• Color Scales: This option applies a two or three color gradient to the cells. Different shades and colors represent specific values.
1. Select the cells you would like to format.
2. Select the Home tab.
3. Locate the Styles group.
4. Click the Conditional Formatting command. A menu will appear with your formatting options.
Select one of the options to apply it to the selected cells. A cascading menu will appear.
An additional dialog box may appear, depending on the option you choose. If so, make the necessary choices, and click OK.
Click the Conditional Formatting command.
Select Clear Rules. A cascading menu appears.
Choose to clear rules from the entire worksheet or the selected cells.
Click the Conditional Formatting command.
Select Manage Rules from the menu. The Conditional Formatting Rules Manager dialog box will appear.
From here you can edit a rule, delete a rule, or change the order of rules.
The real power in Excel comes in its ability to perform multiple
mathematical calculations for you. One of the tools in Excel that you can use to perform these calculations is a Data tool called What-If Analysis. What-If analysis allows you to see the effect that different values have in formulas.
You need a loan to buy a new car. You know how much money you want to borrow, how long of a period you want to take to pay off the loan (the term), and what payment you can afford to make each month. But, what you need to know is what interest rate you to qualify for to make the payment $400 a month. In the image below, you can see that if you didn’t have interest and just divided this $20,000 into 60 monthly payments, you would pay $333.33 a month. The What-If Analysis tool will allow you to easily calculate the interest rate.
There are three What-If analysis tools that you can use.
To access these, select the Data tab, and locate the What-If Analysis command. If you click this command, a menu with three options appears.
is useful if you know the needed result, but need to find the input value that will give you the desired result. In this example, we know the desired result (a $400 monthly payment), and are seeking the input value (the interest rate).
Goal Seek
1. To Use Goal Seek to Determine an Interest Rate:
2. Select the Data tab.
3. Locate the Data Tools group.
4. Click the What-If Analysis command. A list of three options appears.
5. Select Goal Seek. A small dialog box appears.
6. Select the cell that you what to set to a specific value. In this example, we want to set B5, the Payment cell.
7.
8. Insert the cursor in the next field.
9. Enter a value in the value field. In this example, type -$400. Since we’re making a payment that will be subtracted from our loan amount, we have to enter the payment as a negative number.
10. Insert the cursor in the next field.
11. Select the cell that you want to change. This will be the cell that tries various input values.
In this example, select cell B4, which is the interest rate.
12. Click OK.
13. Then, click OK again. The interest rate appears in the cell. This indicates that a 7% interest rate will give us a $400 a month payment on a $20,000 loan that is paid off over 5 years, or 60 months.
Scenario Manager lets you create and save sets of different input values that produce different calculated results as named scenarios (such as Best Case, Worst Case, and Most Likely Case). The key to creating the various scenarios for a table is to identify the various cells in the data whose values can vary in each scenario. You then select these cells (known as changing cells) in the worksheet before you open the Scenario Manager dialog box.
The example uses three scenarios based on the following sets of values for the three changing cells:
• Most Likely Case, where the Sales_Growth percentage is 5%, COGS is 20%, and Expenses is 25%
• Best Case, where the Sales_Growth percentage is 8%, COGS is 18%, and Expenses is 20%
• Worst Case, where the Sales_Growth percentage is 2%, COGS is 25%, and Expenses is
35%
Use the Scenario Manager to add and switch to different scenarios in your worksheet.
Follow these steps to use the Scenario Manager:
1. On the Data tab of the Ribbon, choose What-If Analysis?Scenario Manager in the Data Tools group.
The Scenario Manager dialog box appears.
2. To create a scenario, click the Add button.
The Add Scenario dialog box appears.
3. Type the name of the scenario (Most Likely Case, in this example) in the Scenario Name text box, specify the Changing Cells (if they weren’t previously selected), and click OK.
Create a scenario in the Edit Scenario dialog box.
Excel displays the Scenario Values dialog box.
4. Enter the values for each of the changing cells in the text boxes.
In this example, you would enter the following values for the Most Likely Case scenario:
• 0.05 in the Sales_Growth text box
• 0.20 in COGS text box
• 0.25 in the Expenses text box 5. Click the Add button.
Excel redisplays the Add Scenario dialog box.
6. Repeat Steps 3 through 5 to enter the other scenarios. When you finish entering values for the final scenario, click OK instead of Add.
The Scenario Manager dialog box makes another appearance, this time displaying the names of all scenarios in its Scenarios list box.
7. To have Excel plug the changing values from any scenario into the table, click the scenario name in the Scenarios list box and then click Show.
8. Click the Close button when you’re finished with the Scenario Manager.
After adding the various scenarios for a table in your worksheet, don’t forget to save the workbook. That way, you’ll have access to the various scenarios each time you open the workbook in Excel by opening the Scenario Manager, selecting the scenario name, and clicking the Show button.
After using Scenario Manager to add scenarios to a table in a worksheet, you can have Excel 2007 produce a summary report. This report displays the changing and resulting values not only for all the scenarios you’ve defined but also the current values that are entered into the changing cells in the worksheet table at the time you generate the report.
1. Open the workbook that contains the scenarios you want to summarize.
You create scenarios from the Data tabby choosing What-If Analysis?Scenario Manager in the Data Tools group.
2. Choose What-If Analysis?Scenario Manager in the Data Tools group of the Data tab.
The Scenario Manager dialogboxappears.
3. Click the Summary button.
The Scenario Summary dialog box gives you a choice between creating a (static) Scenario Summary (the default) and a (dynamic) Scenario PivotTable Report. You can also modify the range of cells in the table that are included in the Results Cells section of the summary report by adjusting the cell range in the Result Cells text box.
4. Click OK to generate the report.
Excel creates the summary report for the changing values in all the scenarios (and the current worksheet) along with the calculated values in the Results Cells on a new worksheet.
You can rename and reposition the Scenario Summary worksheet before you save it as part of the
A data table is a range of cells that shows how changing certain values in your formulas affects the results of the formulas. Data tables provide a shortcut for calculating multiple versions in one operation and a way to view and compare the results of all of the different variations together on your worksheet.
One-variable data tables
For example, use a one-variable data table if you want to see how different interest rates affect a monthly mortgage payment. In the following example, cell D2 contains the payment formula, =PMT(B3/12,B4,-B5), which refers to the input cell B3.
How to create table
1 Select the range B6 To C9
2 On the Data tab , in What if analysis, click Datable
3 In Column Input cell select B2.
4 Click OK.
Two-variable data tables
A two-variable data table can show how different interest rates and loan terms will affect the mortgage payment. In the following example, cell C2 contains the payment formula, =PMT(B3/12,B4,-B5), which uses two input cells, B3 and B4.
Excel 2007 includes a Protect Workbook command that prevents others from making changes to the layout of the worksheets in a workbook. You can assign a password when you protect a workbook so that only those who know the password can unprotect the workbook and make changes to the structure and layout of the worksheets.
Protecting a workbook does not prevent others from making changes to the contents of cells. To protect cell contents, you must use the Protect Sheet command button on the Review tab.
Follow these steps to protect an Excel 2007 workbook:
1. Click the Protect Workbook command button in the Changes group on the Review tab. Excel opens the Protect Structure and Windows dialog box, where the Structure check box is selected by default. With the Structure check box selected, Excel won’t let anyone mess around with the sheets in the workbook (by deleting them or rearranging them).
You can protect the structure and windows in a workbook.
2. (Optional) If you want to protect any windows that you set up, select the Windows check box.
When selected, this setting keeps the workbook windows in the same size and position each time you open the workbook.
3. To assign a password that must be supplied before you can remove the protection from the worksheet, type the password in the Password (optional) text box.
4. Click OK.
If you typed a password in the Password (optional) text box, Excel opens the Confirm Password dialog box. Re-enter the password in the Reenter Password to Proceed text box exactly as you typed it Step 3, and then click OK.
To remove protection from the current workbook, follow these steps:
1. Click the Unprotect Workbook command button in the Changes group on the Review tab.
2. If you assigned a password when protecting the workbook, type the password in the Password text box and click OK.
Protecting data from change in Excel is a two step process.
1. Step one involves locking/unlocking specific cells in your spreadsheet.
2. Step two involves applying the Protect Sheet option. Until step 2 is completed, all data is vulnerable to change.
Step 1
Locking/Unlocking Cells
By default, all cells in an Excel spreadsheet are locked. This makes it very easy to protect all data in a single worksheet or in a workbook simply by applying the Protect Sheet or Protect Workbook option.
1. Select the cells which you want to lock/unlock
2. Click on the Home tab.
3. Choose the Format option on the ribbon to open the drop down list.
4. Click on Lock Cell option at the bottom of the list.
5. The Lock Cell option works like an ON/OFF button. Since all cells are initially locked in the worksheet, clicking on the option has the affect of Unlocking the highlighted cells A1 and B1.
Step 2
1. Click on the Home tab.
2. Choose the Format option on the ribbon to open the drop down list.
3. Click on Protect Sheet option at the bottom of the list to open the Protect Sheet dialog box.
4. This dialog box contains a number of options when protecting the worksheet.
5. The first option is to add a password to prevent worksheet protection from being turned off. This password does not stop users from opening the worksheet and viewing the contents.
6. Next there a number of options that can be turned on or off with check boxes. The first two allow a user to drag select locked and unlocked cells. If these two are turned off, users will not be able to make any changes to a worksheet - even if it contains unlocked cells.
7. The remaining options allow users to carry out specific tasks on a protected worksheet, such as formatting cells and sorting data.
8. These options, however, do not all work the same. For instance, if the format cells option is checked off when a sheet is protected, all cells can be formatted. The sort option, on the other hand, works only on those cells that have been unlocked before the sheet was protected.
9. When you have selected the appropriate options, click OK.
Excel 2007 provides a straightforward method to prevent invalid data entry in a worksheet. You're allowed to specify the allowed data type and range checking among other options. As you decide the type of the validation appropriate for different areas of your Excel 2007 worksheet, follow these steps to set up the data validation. To set up data validation in your Excel 2007 sheet, follow the steps below:
1. Select the range of cells that you wish to validate.
2. Click Data Tools group > Data Tab > Data Validation.
3. Click the Settings tab in the Data Validation dialog box and specify the type of validation.
4. Check or uncheck the Ignore Blank checkbox to specify what you want to do with the null values.
5. Optionally, arrange for an input message when the cell is clicked (Input Message) 6. Specify Excel’s response to the invalid data.(Error Alert)
7. Finally, test the data validation to make sure it works.
There is a minor issue with data validation, and it happens when data is copy-pasted, since the system fails to identify the invalid entries. In order to prevent users from copy-pasting data by cell drag-and-drop, do the following:
Click the Microsoft Office Button > Excel Options > Advanced category. In the Editing options, clear the Enable fill handle and cell drag-and-drop checkbox. Click OK.
1 Select the cell to validate
2 On the Data menu, click Validation, and then click the Settings tab. 3 In the Allow box, click List.
Note: Cell references have to be to cells on the same worksheet. If you have to refer to a different worksheet or a different workbook, use a defined name and make sure the workbook is already open.
1 Select the cell to validate
2 On the Data menu, click Validation, and then click the Settings tab.
3 In the Allow box, click Custom
4 In the Formula box, enter a formula that calculates a logical value (TRUE for valid entries or FALSE for invalid). For example, to allow only text and of 5 letters, you could enter =AND(IsText(C2),Len(c2)=5) for the custom formula.
Use Trace Precedents
Choose a formula and click Trace Precedents. It is on the Formulas ribbon in Excel 2007 and the Tools - Formula Auditing menu in Excel 97-2003. Excel will draw blue arrows to show all the cells that flow into this cell.
Precedent cells: Precedent cells are referred to by a formula in another cell. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.
Dependent cells. Dependent cells are cells that contains formula that refer to other cells. For example, if cell D10 contains the formula =B5, cell B5 is a precedent to cell D10.
Evaluate Formula – learn how to evaluate your formulas in slow motion
Tracing Precedents: Trace cells that provide data to a formula (precedents)
Tracing Dependents: Trace formulas that reference a particular cell (dependents)
Functions | Description | Example |
Date | Adds day, month, year | =DATE(2004,1,14) |
Today | Current date | =TODAY() |
Now | Current date and time | =NOW() |
Day | Extracts day from Current date | =DAY(TODAY()) |
Month | Extracts month from Current date | =MONTH(TODAY()) |
Year | Extracts year from Curent date | =YEAR(TODAY()) |
Time | Adds Hr, min, second | =TIME(12,30,45) |
Hour | Extracts Hours from given time | =HOUR(C44) |
Minute | Extracts Minutes from given time | =MINUTE(C44) |
Second | Extracts Seconds from given time | =SECOND(C44) |
Weekday | Extracts day from Current date | =TEXT(WEEKDAY(TODAY()),"dddd") |
Function | Description | Example |
Upper | Converts a text into uppercase | =UPPER(A1) |
Lower | Converts a text into lowercase | =LOWER(A1) |
Proper | Converts a text into First character as capital | =PROPER(A1) |
Left | Extracts characters from left | =LEFT(A1,3) |
Right | Extracts characters from right | =RIGHT(A1,3) |
Mid | Extracts caracters from middle | =MID(A1,3,3) |
Concatenate | Join text | =CONCATENATE("Hello ",A1,B1) |
Len | Find the length of string | =LEN(A1) |
Exact | Checks for two text are same or not | =EXACT(A1,A1) |
Search | Search for position of text | =SEARCH("is","India is my country",1) |
Replace | Changes the text | =REPLACE("India is my country",C15,2,"was") |
Text | Changes value to text format | =TEXT(C1,"$###.00") |
DSUM Adds the numbers in a column of a list or database that match conditions you specify.
Syntax
DSUM(database,field,criteria)
• Database is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.
• Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.
• Criteria is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.
A | B | C | D | E | F | |
Tree | Height | Age | Yield | Profit | Height | |
Apple | >10 | <16 | ||||
Pear | ||||||
Tree | Height | Age | Yield | Profit | ||
Apple | 18 | 20 | 14 | 105.00 | ||
Pear | 12 | 12 | 10 | 96.00 | ||
Cherry | 13 | 14 | 9 | 105.00 | ||
Apple | 14 | 15 | 10 | 75.00 | ||
Pear | 9 | 8 | 8 | 76.80 | ||
Apple | 8 | 9 | 6 | 45.00 | ||
Formula | Description (Result) | |||||
=DCOUNT(A4:E10,"Age",A1:F2) | This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers. (1) | |||||
=DCOUNTA(A4:E10,"Profit",A1:F2) | This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank. (1) | |||||
=DMAX(A4:E10,"Profit",A1:A3) | The maximum profit of apple and pear trees. (105) | |||||
=DMIN(A4:E10,"Profit",A1:B2) | The minimum profit of apple trees over 10 in height. (75) | |||||
=DSUM(A4:E10,"Profit",A1:A2) | The total profit from apple trees. (225) | |||||
=DSUM(A4:E10,"Profit",A1:F2) | The total profit from apple trees with a height between 10 and 16. (75) | |||||
=DPRODUCT(A4:E10,"Yield",A1:B2) | The product of the yields from apple trees with a height | |||||
greater than 10. (140) | ||||||
=DAVERAGE(A4:E10,"Yield",A1:B2) | The average yield of apple trees over 10 feet in height. (12) |
PMT Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax PMT(rate,nper,pv,fv,type)
For a more complete description of the arguments in PMT, see the PV function.
Rate is the interest rate for the loan.
Nper is the total number of payments for the loan.
Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0. Type is the number 0 (zero) or 1 and indicates when payments are due.
• 0 or omitted At the end of the period
• 1 At the beginning of the period
•
A | B |
Data | Description |
8% | Annual interest rate |
10 | Number of months of payments |
10000 | Amount of loan |
Formula | Description (Result) |
=PMT(A2/12, A3, A4) | Monthly payment for a loan with the above terms (-1,037.03) |
=PMT(A2/12, A3, A4, 0, 1) | Monthly payment for a loan with the above terms, except payments are due at the beginning of the period (-1,030.16) |
IPMT
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. For a more complete description of the arguments in IPMT and for more information about annuity functions, see PV.
Syntax IPMT(rate,per,nper,pv,fv,type)
Rate is the interest rate per period.
Per is the period for which you want to find the interest and must be in the range 1 to nper.
Nper is the total number of payment periods in an annuity.
Pv is the present value, or the lump-sum amount that a series of future payments is worth right now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0).
Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.
• 0 At the end of the period
• 1 At the beginning of the period
A | B |
Data | Description |
10% | Annual interest |
1 | Period for which you want to find the interest |
3 | Years of loan |
8000 | Present value of loan |
Formula | Description (Result) |
=IPMT(A2/12, A3*3, A4, A5) | Interest due in the first month for a loan with the terms above (-22.41) |
=IPMT(A2, 3, A4, A5) | Interest due in the last year for a loan with the terms above, where payments are made yearly (-292.45) |
RATE
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions. If the successive results of RATE do not converge to within 0.0000001 after 20 iterations, RATE returns the #NUM! error value.
Syntax RATE(nper,pmt,pv,fv,type,guess)
For a complete description of the arguments nper, pmt, pv, fv, and type, see PV.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period and cannot change over the life of the annuity. Typically, pmt includes principal and interest but no other fees or taxes. If pmt is omitted, you must include the fv argument.
Pv is the present value— the total amount that a series of future payments is worth now.
Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). Type is the number 0 or 1 and indicates when payments are due.
• 0 or omitted At the end of the period
• 1 At the beginning of the period
A | B |
Data | Description |
4 | Years of the loan |
-200 | Monthly payment |
8000 | Amount of the loan |
Formula | Description (Result) |
=RATE(A2*12, A3, A4) | Monthly rate of the loan with the above terms (1%) |
=RATE(A2*12, A3, A4)*12 | Annual rate of the loan with the above terms (0.09241767 or 9.24%) |
What is Macro
An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.
If you plan to work with VBA macros, you’ll want to make sure that the Developer tab is present in Excel. To display this tab:
1. Choose Office ? Excel Options.
2. In the Excel Options dialog box, select Personalize.
3. Place a check mark next to Show Developer tab in the Ribbon.
4. Click OK to return to Excel.
1 On the Developer tab menu, click Record New Macro.
2 In the Macro name box, enter a name for the macro .
Notes
• The first character of the macro name must be a letter. Other characters can be letters, numbers, or underscore characters. Spaces are not allowed in a macro name; an underscore character works well as a word separator.
• Do not use a macro name that is also a cell reference or you can get an error message that the macro name is not valid.
3 In the Store macro in box, click the location where you want to store the macro.
4 If you want to include a description of the macro, type it in the Description box.
5 Click OK.
94