Tutorial to learn EXCEL for data analysis


Télécharger Tutorial to learn EXCEL for data analysis

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


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



2