M S Excel by Priya Murad

Microsoft Excel Level 2

Pragati Software Pvt. Ltd. 207, Lok Center, Marol-Maroshi Road Marol, Andheri (East), Mumbai 400 059. Tel: +91-22-2920 1583, 2920 4130 022 â&#x20AC;&#x201C; 3091 00 00 (100 Lines) www.pragatisoftware.com

164.01.06

Table of Contents Changing Default setting............................................................................................................................... 1 Customizing Toolbars and Menus............................................................................................................. 1 Changing default settings ......................................................................................................................... 1 Cell Reference and Range Name................................................................................................................... 3 Using relative references .......................................................................................................................... 3 Absolute references.................................................................................................................................. 3 Mixed references ...................................................................................................................................... 3 Working with Range Name ....................................................................................................................... 4 Working with Formulas and Function........................................................................................................... 6 Using Formulas in a Worksheet ................................................................................................................ 6 Using array formulas................................................................................................................................. 6 Using functions ......................................................................................................................................... 6 IF function ................................................................................................................................................. 7 Nesting Function using IF.......................................................................................................................... 8 Using AND function................................................................................................................................... 8 Using OR function ..................................................................................................................................... 9 Using NOT condition ................................................................................................................................. 9 IF with date Function .............................................................................................................................. 10 Working with VLOOKUP Function........................................................................................................... 11 Example of VLOOKUP with TRUE Statement .......................................................................................... 12 Working with INDEX Function................................................................................................................. 13 Database Functions................................................................................................................................. 15 Financial Functions.................................................................................................................................. 16 Data Validation ........................................................................................................................................... 18 Designate valid cell entries ..................................................................................................................... 18 Working with Protections ........................................................................................................................... 20 Protecting a worksheet by using password ............................................................................................ 20 Protecting a part of a worksheet ............................................................................................................ 20 Protecting a workbook............................................................................................................................ 21 Workbook File for Protection ................................................................................................................. 22 Built-in templates........................................................................................................................................ 23

Creating and managing templates.......................................................................................................... 23 Creating and using a template ................................................................................................................ 23 Modifying templates............................................................................................................................... 23 Guidelines for entering data in a worksheet .............................................................................................. 24 Data Organization: .................................................................................................................................. 24 Data Format: ........................................................................................................................................... 24 About Data lists....................................................................................................................................... 25 Sorting and Filtering Data ........................................................................................................................... 27 Performing a Simple Sort ........................................................................................................................ 27 Filtering a List Using Auto Filter .............................................................................................................. 29 Filtering a List using Advanced Filter ...................................................................................................... 30 Filtering unique records .......................................................................................................................... 30 Grouping Data Using Subtotal .................................................................................................................... 31 Display Subtotal at Single Level .............................................................................................................. 31 Displaying Nested Subtotals ................................................................................................................... 32 Pivot Tables ................................................................................................................................................. 33 Steps to Create a Simple Pivot Table ...................................................................................................... 33 Updating a Pivot Table ............................................................................................................................ 35 Format a PivotTable report..................................................................................................................... 36 Top N Report........................................................................................................................................... 36 Group items in a PivotTable.................................................................................................................... 36 Create a Graph using Pivot Data ............................................................................................................. 37 What â&#x20AC;&#x201C; If Analysis Tools .............................................................................................................................. 38 Using the Goal Seek Command............................................................................................................... 38 Projecting Figures Using a Data Table..................................................................................................... 39 What If Scenario.......................................................................................................................................... 42 Creating Scenarios .................................................................................................................................. 42 Create a scenario summary report ......................................................................................................... 43 Merge scenarios from another worksheet ............................................................................................. 44 Delete a scenario .................................................................................................................................... 44 Protecting Scenarios ............................................................................................................................... 45 Using multiple worksheets.......................................................................................................................... 46

Linking worksheets by using 3-D formulas ............................................................................................. 46 Consolidating Data .................................................................................................................................. 46 Creating Hyper Link................................................................................................................................. 48 Creating links between different worksheets......................................................................................... 48 Creating links between different software ............................................................................................. 49 Data Forms.................................................................................................................................................. 50 Add a Record........................................................................................................................................... 50 Modify a Record...................................................................................................................................... 50 Delete a record ....................................................................................................................................... 51 Auditing features ........................................................................................................................................ 52 Tracing errors in a worksheet ................................................................................................................. 52 Workgroup collaboration........................................................................................................................ 53 Sharing workbooks.................................................................................................................................. 54 Merging workbooks ................................................................................................................................ 54 Tracking changes..................................................................................................................................... 55 To highlight changes: .............................................................................................................................. 55 To review workbook changes and accept or reject them:...................................................................... 55 Advanced formatting features.................................................................................................................... 57 AutoFormat............................................................................................................................................. 57 Conditional formatting............................................................................................................................ 57 Conditional formatting using Formula.................................................................................................... 58 Conditional formatting using Formula with multiple conditions............................................................ 59 Importing Data ............................................................................................................................................ 60 Import a text file ..................................................................................................................................... 60 Importing data from databases and files................................................................................................ 60 Importing data with Microsoft Query..................................................................................................... 61 Importing data from the Web................................................................................................................. 61 Macros ........................................................................................................................................................ 62 What is Macro? ....................................................................................................................................... 62 Creating Macro ....................................................................................................................................... 62 Recording Macro..................................................................................................................................... 62 Define the Macro .................................................................................................................................... 63

Stop Recording........................................................................................................................................ 64 Relative Reference Macro........................................................................................................................... 65 Scenario 1................................................................................................................................................ 65 Run your Macro ...................................................................................................................................... 66 Run the Macro By Name ......................................................................................................................... 66 Scenario 2................................................................................................................................................ 67 Scenario 3................................................................................................................................................ 67 Creating Menus or Buttons......................................................................................................................... 68 Create Menu ........................................................................................................................................... 68 Create Menu Items ................................................................................................................................. 68 Modify Menus or Button ........................................................................................................................ 68 Scenario 4................................................................................................................................................ 69 Creating Buttons on MS Excel worksheet................................................................................................... 71 Scenario 5................................................................................................................................................ 71 Editing the Recorded Macros...................................................................................................................... 73 Scenario 6................................................................................................................................................ 73

Changing Default setting Customizing Toolbars and Menus Excel has many in- built toolbars and menus. Some of the toolbars, such as Standard and Formatting, appear automatically when you start Excel. You can add, delete, and move buttons on these toolbars to perform your tasks more effectively. Excel automatically saves any changes you make to the toolbars and menus. If we notice Standard and Formatting Toolbars appearing on same row and the full menu being displayed after a short delay, we can change this setting by clicking on Tools â&#x20AC;&#x201C;Customize â&#x20AC;&#x201C; Option tab, then clicking on the checkbox from Show the Standard and Formatting toolbars on two rows and always show the full menu, as displayed below.

Changing default settings Excel allows you to change many aspects of its behavior and how you interact with it. You can change default settings such as number of iterations, font, file locations, and the file that opens on starting Excel. To select the dialog box of Options you need to click on Tools Option and select the General tab from the dialog box.

General options You can change workbook settings by using the General tab Options dialog box. General options are applied to all new workbooks. The following explains some of the options you will find on General tab.

Opening files automatically Whenever you start Excel, the default workbook, Book1, opens automatically. You can also open other workbooks automatically when you start Excel. On the General tab, in the Alternate startup file location box, specify the path of the folder that contains the workbook(s).Click OK. When restart Excel, the workbook in the specified folder will open automatically. If the folder contains more than one workbook, Excel opens all workbooks.

Default font and file location In Excel, the default font setting is Arial and the default file location is My Documents folder. You can change these settings on the General tab of the Tools Options dialog box. Use the Standard font list to change the font. The folder you specify in the Default file location box will appear in the Save As and Open dialog boxes when you first open them. You will need to restart Excel for the changes to take effect.

Recently used File list By default, Excel displays list of last four files used in the File menu. We can increase the number of files maximum upto 9 files. Note: Any changes in the setting will be applicable from new workbook and not the existing workbook.

Cell Reference and Range Name Using relative references Relative reference is the default cell reference in Excel and it makes it easy for you to create formulas once and then copy them. If the formula is copied column wise, the cell reference changes from A1 to B1 and if it is copied row wise it will change to A2. There are situations, however, in which you don't want Excel to adjust a reference relative to the location of a copied formula. For these situations, Excel provides for absolute references and mixed references. Limitations of relative references Usually, relative references work very well when you copy a formula. But if a formula refers to a specific cell that should not change, then you need to type or edit the formula again if you are not aware of freezing the cell. This can happen when you refer to commission or discount rates which are typed in one of the cell and need to create a formula and copy.

Absolute references When you donâ&#x20AC;&#x2122;t want a reference to change while copying it, you can use an absolute reference. To make a reference absolute, you place a dollar sign before the column letter and the row number for the reference. For example, to create an absolute reference to cell A1, enter $A$1. When you copy an absolute reference to another location, Excel will not change the reference.

1 2

100000

10%

11%

12%

=$A$1*B1 =$A$1*C1 =$A$1*D1

Mixed references You can also create mixed references by placing a dollar sign either before the column letter or the row number. When copied, the relative part of the reference will adjust relative to the new location, while the absolute part will not. You can cycle through the reference possibilities as soon as you enter a reference by pressing F4, e.g., $A1 or A$1, so if use $A1 means the Column is fixed and if you type A$1 then Row is fixed.

Example

A B C D 1 100000 10% 11% 12% 2 =$A1*B1 =$A1*C1 =$A1*D1

You can cycle through the reference possibilities as soon as you enter a reference by pressing F4 Function key. F4 key – Once

– Absolute cell reference i.e. $H$2

F4 key – Twice

– Only Row absolute i.e. H$2

F4 key – Thrice

– Only Column absolute i.e. $H2

F4 key – Four times

– Relative Cell Reference i.e. H2

Scenario Let us calculate HRA, DA, and Conveyance by writing a single formula rather than writing 3 different formulas. (We can use Mixed Cell reference in the formula) Select Cell I3 The formula will be as follows: =$H3*I$1

Working with Range Name Generally when we create a formula or function we refer to the cell reference such as sum(A2:A200). If cells A2:A200 contains the Salary of East Region, wouldn’t it be easier to understand if the formula was sum(East Region)? In another case if we create a formula from different sheet, we usually get the formula with

different sheet name and the range name. However instead of that we can create a formula using the range name. We will see how to name the individual cell or range of cells. These are different ways of creating a Range Name. • • •

Create a name for a range using Name box. Click the insert menu then click ‘Name’ and then select ‘Define’ Click the insert menu then click ‘Name’ and then select ‘Create’

On a Formula bar on the left hand side there is Name box. Simply select the cell or range of the cells for which you want to specify the name, click on the Name box, type the name you want to use. Press Enter, and you’ve created the range name. Clicking on the Drop down arrow for the Name box displayed the range names in a workbook by pressing F3 button. Creating Range Name by using the Name Create Command In the below given example if you want to create a name what is on the left hand of the cell, instead of typing for each cell , to do this select the cell from A4:B7 and click on Insert-Name-Create and select the Option on the left column. Creating Range Name by Using the Name Define Command. Suppose if you want to assign the name Prn to the cell range A1:K7, select the range, click on Insert –Name-Define, you will get dialog box, and from there you can type the name and click on add button, from the define name dialog box you can delete the name.

Working with Formulas and Function Using Formulas in a Worksheet Formulas are equations that perform calculations on values in your worksheet. A formula starts with an equal sign (=). For example, the following formulas multiply 2 by 3 and then add 5 to the result. =5+2*3 A formula can also contain one or all of the following: functions, references, operators, and constants.

Using array formulas An array formula enables you to perform many calculations by using a single formula. For example, consider a worksheet containing quantities in one-column and unit prices in another, as shown below. In cell B8, you want to calculate the total sales of items without calculating the sales amount of individual items. You can do using the array formula shown below. This formula multiplies the quantity by the unit price for each item, and then adds all the results. In the formula, A2:A6 and B2:B6 are known as array arguments. Each array argument must have the same number of rows and columns. After entering array formula, press Ctrl+Shift+Enter, instead of pressing a Enter key. When you enter a formula, it will appear on the Formula bar enclosed in braces ({}), indicating that it is an array formula.

Using functions Using functions to calculate values Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, called the syntax. For example, the SUM function adds values or ranges of cells, or the PMT function calculates the loan payments based on the interest rate, the period of the loan payment, and the principal amount for the loan. Arguments can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A. or cell references. The arguments you designate must produce a valid value for that argument. Arguments can also be constants, formulas, or other functions.

The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, type an equal sign (=) before the function name .As you create a formula that contains a function, the Formula Palette will assist you. =Function_name(argument1,argument2,â&#x20AC;Ś.) = SUM (A10, B5: B10, 50,37)

= COUNT(B5:B10) = COUNTA(B5:B10) = MAX(B5:B10) = MIN(B5:B10) = AVERAGE(B5:B10) Note: For working with Function click on Fx on the Formula bar [New from 2002 onwards] In 2000 click on Fx icon which is on formatting Tool bar

IF function Suppose in salary worksheet if you want to calculate HRA according to condition, i.e. if designation is Manager then hra is 1000, or designation is Officer then hra is 750 and designation is clerk then hra is 500, or if you want to calculate Income tax slab wise. In the above scenario you can use the IF function to evaluate a condition. The IF function returns different values depending on whether the condition is true or false. The syntax for the IF function is: If(Condition ,True, False) The first argument is the condition that you want the function to evaluate; the second argument is the value to be returned if the condition is true, and the third argument is the value to be returned if the condition is false. Example Suppose you want to calculate HRA based on designation of the employees, if Designation is Manager then HRA is1000 or else 500. Then the function code will be as follows: =if(D2="Manager",1000,500) 7

Nesting Function using IF In certain cases, you may need to use a function as one of the arguments of another function, for example if designation is manager then hra is 1000, or designation is officer then hra is 750, or designation is clerk then hra is 500. The syntax for the nested IF function is: If(Condition ,True, if(Condition ,True,False)) Example You can use nested IF functions to evaluate complex conditions. For example, if the Salary <5000 then tax is 5%, if salary between 5000 and 1000 then it is 10% else 15%. =if(salary<5000,salary*.05,if(salary<10000,salary*.10,salary*.15)) Note: Replace Salary with Cell Reference. Suppose you want to assign letter grades to numbers referenced by the name Average Score. See the following table. If Average Score Then is return You can use the following nested IF function: IF(AverageScore>89,"A",IF(AverageScore>79,"B",IF( AverageScore>69,"C",IF(AverageScore>59,"D","F")))) Note: AverageScore will be the cell address of the cell. Excel allows maximum seven IF functions in a formula.

Greater than 89

From 80 to 89

From 70 to 79

From 60 to 69

IF function [Using And/ Or/ Not]

Using AND function

Suppose if you want to calculate HRA based on condition, Less than 60 i.e. if the Employee is Manager and his Grade is A1 if both the condition are satisfied then HRA will be 50% of salary, in such scenario you can use AND function with if condition.

AND is a logical function, which is used when there are multiple conditions in a formula. When all the conditions are satisfied then it will return True value else returns the false value. Syntax using And with If function If(and(Condition1,condition2â&#x20AC;Ś.condition 30),T,F)

There can be maximum 30 conditions which can be passed to AND function. Example: If the Employee is Manager and his Grade is A1 then calculate HRA 50% of salary or else 30% of salary. =if(and(d2="manager",e2="A1"),g2*.5,g2*.3)

Using OR function Suppose if you want to calculate HRA based on condition, i.e. if the employee is Manager or his Grade is A1 in such scenario you can use OR function with If condition. OR is a logical function, which is used when there are multiple conditions in a formula. When any one of the condition is satisfied then it will return True value else returns the False value. 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. Syntax with If condition If(or(condition1, condition2,...) There can be maximum 30 conditions which can be passed to OR function. Example: If the employee is in Sales, Mktg or Hr dept, then hra is 50% of salary or else 30% of salary Eg.if(or(b2="Mktg",b2="Sales",b2="Hr"),h2*.5,h2*.3)

Using NOT condition Suppose if you want to calculate HRA i.e. except Marketing Dept then you can use NOT Function using If. Only one condition in NOT Function Example: If the employee does not work in mktg dept then hra is 50% of salary or else 30% of salary. Eg.if(not(b2="Mktg"),h2*.5,h2*.3)

IF with date Function All those employees who have completed 15 years or more in the company are eligible for a picnic to Singapore. To find out the total yrs of experience, we can make use of DATEDIF function. The syntax of Datedif Function is as follows: =Datedif(Date1, Date2, Interval) We can use Datedif Function alongwith the IF Function as follows: =IF(DATEDIF(H3,today(),”y”) >=15, “Eligible”, “Not Eligible”) Date1 is the first date,Date2 is the second date,Interval is the interval type to return. If Date1 is later than Date2, DATEDIF will return a #NUM! error. If either Date1 or Date2 is not a valid date, DATEDIF will return a #VALUE error.The Interval value should be one of Interval Meaning

Description

Months

Complete calendar months between the dates.

Days

Number of days between two dates.

Years

Complete calendar years between the dates

Months Excluding Years

Complete calendar months between the dates excluding yrs

Days Excluding Years

Days Excluding

Complete calendar days between the dates as if they were of the same year. Complete calendar days between the dates as if they were of the Years And Months same month and same year.

The formula will be as follows: =datedif(H3,today(),”y”) H3 cell in this example contains the joining date of the employee. Today() function always returns current system date. We want the difference in years, so the interval is “y”.

Working with VLOOKUP Function Suppose you want to add incentive value for some other worksheet or different file on given employee code for that you can use function known as Vlookup. Vlookup- stands for Vertical Lookup. If we want to the get the value of one column from some other file or sheet based on common field then you can used function vlookup. It searches 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. Syntax of Vlookup VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

You can use this function using in Built Function, Select Insert Menu- and Click on Insert Function or click on Fx from formula bar or from Standard Toolbar- Select Lookup & Reference category and select Vlookup, you will get a dialog box as shown below, Lookup_value is the value to be found in the first column of the table. 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. Col_index_num is the column number in table_array from which the matching value must be returned. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. If it is true then it finds the, the next largest value that is less than lookup value is returned.

Example of Vlookup with False Statement Suppose you want to add incentive value from another sheet in the Salary Sheet, Incentive worksheet consists of an incentive table whose range is A1:B12 Select the cell, where you want the result. Click on Insert Function –Select the Vlookup () function from Lookup and Reference category. Lookup Value – Select A2 [The Employee code] Table Array: Select the Incentive Sheet and Select the Range from $A$1:$B$12 [i.e. Employee code and incentive Column] Column Index: Type 2 [Column 2 is the Incentive column in Incentive table ] Range Lookup: Type False [we are searching the exact match from the table for the lookup value] Note: To remove #NA, you can use the function Iserror. The Syntax of iserror is as follows: =if(iserror(Vlookup….),””,Vlookup)

Example of VLOOKUP with TRUE Statement Suppose you want to add incentive Value based on Slab-wise, instead of If condition you can use can use Vlookup with True condition. Suppose if we take example based on salary give the incentive value, so rate a table as given below, and in table array select the table given below and range lookup type true instead of false.

5000

10000

10%

15000

15%

Note: the Table slab should be in ascending order.

Working with INDEX Function Index function can be used to fetch the value present at a particular cell. For this function we need to give the row number and the corresponding column number. This is actually a draw back of Index function, however, this drawback can certainly be overcome by cascading the Match function in Index function. All the parameters are shown in figure below

In the Index function we have seen that we can fetch the values present at the intersection of given row number and the column number. But in this we have to manually put in the value for the row number and the column number. However, if we get any function which can automatically give the row and the column number of the values to be searched, then that would help us to enhance the working of the Index function. And that can be done by using the MATCH function. Match function can only be used to find the location of particular value present in a defined range. It only returns the numerical value. The parameters of the Match function are explained as follows: Lookup_value: This is the value whose location is to be found out within a defined range or set of values.

Lookup_array: This a range where the value is to be searched or matched. While defining the range for Match function, we need to keep in mind that the range has to be one dimensional. I.e. The range should not include multiple rows and columns. Match_type: It means the perfect value to be searched or any value which is nearest to the given value. AS per the earlier scenario, we need to find the location of RAM in the vertical column, in the lookup array shown. Similarly, we need to find the location of February in the horizontal lookup array. =match( lookup_value, lookup_array, Match_type ) For the vertical lookup array: =MATCH(B7,$A$2:$A$5,0) Result : 3 For the horizontal lookup array: =MATCH(B8,$B$1:$D$1,0) Result: 2 Futher, we can make use of INDEX function in conjunction with MATCH Function.

=INDEX($B$2:$D$5,MATCH(B7,$A$2:$A$5,0),MATCH(B8,$B$1:$D$1,0))

Similarly, the next match function is to be used to find the column number for that particular value. Lookup value is B8 i.e . Month Name - Feb Lookup array is B1:D1. i.e. the range of cells where all the month name appears. Match type is FALSE as we are searching for an exact match.

After putting the above mentioned values, we will get 50 as the output. Here we have used the combination of the index and the match function. We have used Index function to display the value present at the intersection of the given row and column number respectively. We have used match function to find the location of RAM and February month in the specified range.

Database Functions Database functions are used to perform total based on the given criteria. The functions have 3 arguments — database, field, and criteria. The database argument is the range that contains your list. You must include the row that contains the column labels in the range The field argument is the label for the column you want to summarize. The criteria argument is the range that contains a condition you specify. DSUM It sums up the values in a column of a list or database that match conditions you specify Syntax =DSUM (database, field, criteria) Database functions are used to perform total based on the given criteria. The functions have 3 arguments — database, field, and criteria. The database argument is the range that contains your list. You must include the row that contains the column labels in the range The field argument is the label for the column you want to summarize. The criteria argument is the range that contains a condition you specify. Examples The following illustration shows a database of Employee Information System, if you want to find the Total Salary of employees from Mktg Dept of the East Region, you can use the function Dsum. Note: The Advantage of using Dsum is if you change the condition from Mktg to Finance criteria then you will get the result of given condition. DSUM (Database,”salary”a1:b2). This function calculates the total Salary for Region East and Mktg Dept. DCOUNT (Database,”salary”a1:b2)). This function counts the no of employee those who are in East Region and in Mktg Dept.

DMAX (Database,”salary”a1:b2)). This function calculates for the Maximum Salary paid in Region East and in Mktg Dept.

DMIN (Database,”salary”a1:b2)). This function calculates for Minimum Salary paid in Region East and in Mktg Dept. DAVERAGE (Database,”salary”a1:b2). This function calculates Average Salary paid in Region East and in Mktg Dept.

Financial Functions PMT Calculates the payment for a loan based on constant payments and a constant interest rate. Syntax

Loan Amount

100000

Interest rate

8.50%

Period

EMI

=pmt(b2/12,b3,b1)

=PMT(rate,nper,pv,fv,type) 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. Steps to calculate PMT Click on Insert menu, Click on Function, Select Financial from Select a Category box Select a PMT function in it. Enter Rate as cell B2/12 or 8.5%/12, Nper as cell B3 or 36 and Pv as cell B1 or 100000 in respective fields. Click on OK button. FV Returns the future value of an investment based on periodic, constant payments and a constant interest rate.

Syntax FV(rate,nper,pmt,pv,type) Rate is the interest rate per period. Nper is the total number of payment periods in an annuity. Pmt is the payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes. If pmt is omitted, you must include the pv argument. Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument. Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. Example of FV: Suppose a person wants to open a saving account in a bank with an initial deposit of Rs.10000 and Rs.200 as monthly deposit, for a period of 5 years at 5% rate of interest. What will be the amount incurred by the person after 5 years? Enter the following data in your worksheet and find the future value on an investment.

5 6 7 8

C Rate Of interest Period in yrs Payment /month Initial Deposit

Future Value

D 5% 5 -200 -10000 =FV( d5/12,d6*12,d7,d8)

Data Validation Data Validation is a process which restricts the users from entering invalid data for individual cells or cell ranges; restricts the data entry to a particular type, such as whole numbers, decimal numbers, text; and sets limit on valid entries.

Designate valid cell entries Select the cell to validate. On the Data menu, click Validation, and then click the Settings tab. Specify the type of validation you want: 1. Allow values from a list This Validation allows the user to pickup the data from the dropdown, where the source will be in different cell, or to define the list locally, type the list values separated by commas. Steps are as follows • • • • •

Select a blank cell Select Data- Validation In the Allow Box, Select List In Source, select the cells with values, or type the data seperated by comma. If the Source is from different sheet, name the range of values, and use name by typing the equal sign (=) followed by the name of the range.

2. Allow numbers within limits • • •

In the Allow box, click Whole Number or Decimal. In the Data box, click the type of restriction you want. For example, to set upper and lower limits, click between. Enter the minimum, maximum, or specific value to allow.

3. Allow dates or times within a timeframe •

In the Allow box, click Date or Time.

• •

In the Data box, click the type of restriction you want. For example, to allow dates after a certain day, click greater than. Enter the start, end, or specific date or time to allow.

4. Allow text of a specified length • • •

In the Allow box, click Text Length. In the Data box, click the type of restriction you want. For example, to allow up to a certain number of characters, click less than or equal to. Enter the minimum, maximum, or specific length for the text.

5. Calculate what's allowed based on the content of another cell • • •

In the Allow box, select the type of data you want. In the Data box, select the operator (for criteria) you want. In the box or boxes below the Data box, click the cell that you want to use to specify what's allowed. • For example, to allow entries for an account only if the result won't go over the budget, click Decimal for Allow, click less than or equal to for Data, and in the Maximum box, click the cell that contains the budget amount. 6. Use a formula to get the unique value. In the Allow box, click Custom. In the Formula box, enter a formula that calculates a logical value (TRUE for valid entries or FALSE for invalid). For example, to allow the unique value to be entered in the cell you need to use formula =COUNTIF(A:A,$A1)=1 To display an optional input message when the cell is clicked, click the Input Message tab, and make sure the Show input message when cell is selected check box is selected, and fill in the title and text for the message(up to 225 characters). Specify how you want Microsoft Excel to respond when invalid data is entered: Click the Error Alert tab, and make sure the Show error alert after invalid data is entered check box is selected. Note: If you don't enter a title or text, the title defaults to "Microsoft Excel" and the message to: "The value you entered is not valid. A user has restricted values that can be entered into this cell." Select one of the following options for the Style box: To display an information message that does not prevent entry of invalid data, click Information. To display a warning message that does not prevent entry of invalid data, click Warning. To prevent entry of invalid data, click Stop.

Working with Protections By protecting a worksheet, you can prevent unauthorized users from modifying it. We can choose to protect an entire worksheet or to protect a part of it, allowing users to edit some cells in worksheet.

Protecting a worksheet by using password Choose Tools – Protection - Protect Sheet to open the Protect Sheet dialog box. Select the options you want. Enter a password and click OK. A Confirm Password dialog box will appear. Enter the same password in the “Re-enter password to proceed” text box and click OK.

Protecting a part of a worksheet To password protect only a part of a worksheet: Step 1 Select the range of cells that you want users should be allowed to modify Click on Format, Cells to open the Format Cells dialog box and then activate the Protection tab. Clear the Locked check box and click OK Select the range of cells where the formula is applied, Click on Format Cell-Protection, and Select both Locked as well as hidden checkbox. Step 2 Now Click on Tools Menu-Protection – Protect sheet and give the Password.

Allow different users to Modify All steps in Step 1 Select the range of cells that you want users should be allowed to modify Click on Tools, click on Protection, select Allow Users to Edit Ranges, Click on New… Name the range and assign password to it. Click on Ok and again Ok Now Click on Tools Menu-Protection – Protect sheet and give the Password

Protecting a workbook To stop the user from taking actions such as renaming a worksheet or inserting new worksheets, you must use workbook-level protection. To protect your workbook, go to Tools – Protection - Protect Workbook option. This displays the following Protect Workbook dialog box. Structure: Prevents any changes to the worksheets themselves. The user cannot change the order of any worksheets, their names, add new ones, or delete existing ones. Windows: Stops the user from moving any windows used in the workbook. At the bottom of the dialog box you can specify a password to use for this level of protection. When you click your mouse on OK, Excel asks you to repeat any password you have specified. If you did not provide a password, then Excel simply protects your workbook directly. Note: If you later want to remove the protection you have applied to a workbook, you do so by choosing Protection from the Tools menu and then Unprotect Workbook from the resulting submenu. If you used a password to protect the workbook, Excel displays the Unprotect Workbook dialog box. At this point, all you need to do is enter the correct password and the workbook will be unprotected. You should note that protecting a workbook without protecting the worksheets it contains doesn't offer much protection. The reason is that even though a user cannot delete a worksheet in a 21

protected workbook, they can delete the information the worksheet contains. In other words, workbook protection does not protect the contents of your workbook at any level "lower" than individual worksheets.

Workbook File for Protection We want to save a workbook in such a way that we must supply a password to open or modify it: In Excel 97, Select File - Save As to display the Save As dialog box; then click Options in that box to call up the Save Options dialog. In Excel 2000 and later, Select Tools - General Options when the Save As dialog box appears. If you enter a password in the "Password to open" field, the user must provide the correct password to open the file. If you enter a password in the "Password to modify" field, the user must give the right password to make changes to the file. Without the correct password, the file opens in read - only mode.

Built-in templates A template is a special type of workbook you can use as a starting point for other workbooks. Templates can contain labels and other data, formatting, styles, and functionality you can use for a particular purpose. Excel provides several built-in templates to help you create forms such as invoices, purchase orders and expense statements. You can view Excel’s built-in templates by activating the Spreadsheet Solutions tab of the Templates dialog box, by clicking on Task pane, Select New Workbook., Templates –Click on my computer Note: In 2000 click on File New – Click on Spreadsheet Solutions tab.

Creating and managing templates When Excel does not provide a template that suits your needs, you can create your own custom templates. Like built-in templates, custom templates make it easy to create and maintain multiple workbooks with the same formatting, styles, content, and functionality.

Creating and using a template To create a template: • • • • • • •

Open or create the workbook on which you want to base the template. Choose File, Save As to open the Save As dialog box. From the Save as type list, select Template. In the File name box, enter a name for the template. Click Save. Close the Workbook Once you create the template Create a new workbook based on the template. Enter the remaining information. Save the workbook close it.

Modifying templates Sometimes, you might need to modify custom templates. To modify a template, choose File, Open to display the Open dialog box; specify the location and the name of template file that you want to modify, and click Open. Make the changes you want and update the template. Note: Templates are saved in C:\Documents and Settings\User_name\Application Data\Microsoft\Templates.

Guidelines for entering data in a worksheet Microsoft Excel has a number of features that make it easy to manage and analyze data. To take advantage of these features, enter data in your worksheet according to the following guidelines.

Data Organization: Put similar items in one column Design the data so that all rows have similar items in the same column. Keep the range separate Leave at least one blank column and one blank row between the related data range and other data on the worksheet. Excel can then more easily detect and select the range when you sort, filter, or insert automatic subtotals. Position critical data above or below the range Avoid placing critical data to the left or right of the range; the data might be hidden when you filter the range. Show rows and columns Make sure any hidden rows or columns are displayed before making changes to the range. When rows and columns in a range are not showing, data can be deleted inadvertently.

Data Format: Use formatted column labels Create column labels in the first row of the range of data. Excel uses the labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format you assign to the data in the range. Format the cells as text before you type the column labels. Use cell borders When you want to separate labels from data, use cell bordersâ&#x20AC;&#x201D; not blank rows or dashed linesâ&#x20AC;&#x201D; to insert lines below the labels. Avoid blank rows and columns Avoid putting blank rows and columns in the range so that Excel can more easily detect and select the related data range. Don't type leading or trailing spaces Extra spaces at the beginning or end of a cell affect sorting and searching. Instead of typing spaces, indent the text within the cell. Extend data formats and formulas When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must

use the same format for a format to be extended. All of the preceding formulas must be consistent for a formula to be extended.

About Data lists A Microsoft Excel list provides features designed to make it easier to manage and analyze groups of related data in an Excel worksheet. When you designate a range as a list, you can manage and analyze the data in the list independently of data outside the list. For e.g. using only the data contained within the list, you can filter columns, add a row for totals, and even create a PivotTable report, using only the data contained within the list. You can have multiple lists on your worksheet, which allows you a great deal of flexibility for separating your data into distinct, manageable sets according to your needs. Note: You cannot create a list in a shared workbook. You must remove the workbook from shared use first if you want to create a list.

Every column in the list has AutoFilter enabled by default in the header row. AutoFilter allows you to filter or sort your data quickly. The dark blue border around the list clearly distinguishes the range of cells that makes up your list. The row that contains an asterisk is called the insert row. Typing information in this row will automatically add data to the list and expand the border of the list. A total row can be added to your list. When you click a cell within the total row, a drop-down list of aggregate functions becomes available. You can modify the size of your list by dragging the resize handle found on the bottom corner of the list border. The benefits of lists Sort and filter lists You can sort lists in ascending or descending order or create custom sort orders. You can also filter lists to show only the data that meets the criteria you specify.

Format list objects worksheet.

You can format cells in a list the same way that you format cells in a

Note: Data List option is new feature from Excel 2003. There are a few BASIC RULES that you need to follow when setting up your list in the first place. These are: Check that any numbers in your spreadsheet are in a numerical format. Cells containing both text and numbers need to be formatted as text. Dates and times must be formatted correctly. You must unhide any hidden rows or columns (we will cover hiding rows and columns later in this lesson). Make sure you have no leading spaces. This can happen when you push your Space bar before typing into the cell. Enter column labels in one row (use Alt + Enter to put a hard return in if you need to) or use the Orientation feature under Format>Cells-Alignment.

Sorting and Filtering Data Data sorting is an extremely handy and versatile feature within Excel. Generally, sorting is performed on a list, which is defined as a contiguous (no blank cells) group of data where the data is displayed in columns and/or rows. Excel allows you to sort Worksheet data alphabetically, numerically or chronologically. You can sort by columns, by rows, in an ascending or descending order and from left to right. When you sort within Excel, it will rearrange the contents of the sort area according to the instructions that you give it. Excel will always sort blank cells to the bottom of a list.

Performing a Simple Sort To sort your entire list, just select a single cell in the list and choose the Sort command from the Data menu. Microsoft Excel automatically selects the whole list for you. If your list has column labels in the first row, Microsoft Excel excludes them from the sort and uses them to help you choose the Sort by column. If Microsoft Excel does not automatically select the data you want to sort, manually select the data and choose the Sort command again. Rows that contain duplicate items in the Sort by column appear together in the sorted list in their original order. If you want to sort these duplicate rows further, you can specify a second column to sort by in the first Then By box. If there are duplicate items in this second column, you can specify a third column to sort by in the second Then By box. You can sort data up to three columns at a time.

Microsoft Excel saves the sort options you select in the Sort dialog box and Sort Options dialog box - such as the Sort By column, sort order (ascending or descending), and sort orientation (top to bottom or left to right) - and displays them each time you choose the Sort command until you change the options or sort another list. In a database it generally becomes very difficult to view the data in a logical sequence. We want to sequentially arrange our Salary Data Region wise and then Department wise in ascending order. Sometimes we need to sort data into a recognizable order that is not alphabetical. For example, maybe we want regions listed as “West", “East“, “North” and then “South". Using a regular sort, there is not a good way to force W to sort before E, N and S. Here, we can make use of Custom Sorting. First, type the correct order in an out of the way place from the menu, select Tools – Options – Custom List

On the Data menu, click Sort. In the Sort by box, click the column you want to sort. Click Options. Under First key sort order, click the custom list you created. For example, click West, East, North and South. Click OK. Select any other sort options you want, and then click OK.

Filtering a List Using Auto Filter In Excel Spreadsheet if you want to track the sales information or salary information in existing data, or often want to extract the salary details base on some condition , in such scenario Excel has given a command known as Filter. Auto Filter enables you to display a subset of your list with a click of a mouse button. When you choose the Filter command from the Data menu and then choose Auto Filter, Microsoft Excel places drop-down arrows directly on the column of your list. Clicking an arrow displays a list of all the unique items in the column. By selecting an item from a list for a specific column, you can instantly hide all rows except that row that contain the selected value and its related information (that complete row). The item you select in a column drop-down list is called the filter criterion. For example, selecting East from the Region drop-down list hides all rows except those that contain region east. You can filter your list further by choosing another criterion from another column. For example, selecting Mktg from the Dept drop-down list also hides all rows except those that contain East and Mktg Dept.

In some case if you want to select the multiple regions i.e. East and West in such case you have to select the custom from dept drop down. Tip: If the list of items is long, you can quickly move to an item by selecting the arrow, then typing the first letters of the item.

Filtering a List using Advanced Filter To filter a list using more complex criteria e.g., display the details of employees whose salary is between 3000 and less than 5000, or between 10000 to 15000, for those who are in East or North Region in such case you canâ&#x20AC;&#x2122;t retrieve the data using Autofilter. In such case you have to use Advance Filter (Data menu, Filter sub menu). Here besides having a data in tabular format, we have to also specify the condition to be met in a different area of a worksheet. For a clearer picture refer the following e.g. The Advanced Filter command filters your list in place, as Auto Filter does, but it does not display drop-down lists for columns. Instead, you have to select the List Range i.e. your data, type criteria in a criteria range on your worksheet and select the Criteria Range and in output range type the cell address where you want to display the output. It is optional.

Filtering unique records Select the column or click a cell in the range or list you want to filter. On the Data menu, point to Filter, and then click Advanced Filter. Do one of the following. To filter the range or list in place, similar to using AutoFilter, click Filter the list, in-place. To copy the results of the filter to another location, click Copy to another location. Then, in the Copy To box, enter a cell reference. Select the Unique Records Only check box. Note: Advanced filter, copy to option copies on a same worksheet, if you want to copy the Filter data in to different worksheet, and then select the Advanced Filter command from a new worksheet.

Grouping Data Using Subtotal If you want to summarize the salary details according to region wise, you display automatic subtotals by selecting a single cell in the list and choosing the Subtotals command from the Data menu. Microsoft Excel uses the column labels to help you identify the items you want grouped and the values you want summarized. Microsoft Excel can automatically calculate subtotal and grand total values in a list. When you insert automatic subtotals, Excel outlines the list so that you can display and hide the detail rows for each subtotal. To insert subtotals, you first sort your list so that the rows you want to subtotal are grouped together. You can then calculate subtotals for any column that contains numbers.

Display Subtotal at Single Level For performing Subtotal on data, first the table fields on which subtotal are done must be sorted. After you click the subtotals command from the Data menu, select the desired column from At Each Change In listbox then select the function which you want to perform on data from the Use function listbox, now select the column on which you want to perform subtotals from the Add Subtotal to field. When you choose the OK button, Microsoft Excel inserts a subtotal row for each group of identical items in the selected column. Choosing a Summary Function The first time you use the Subtotals command for a list, Microsoft Excel suggests a summary function based on the type of data in the column you select in the Add Subtotal To box, Choose a different calculation, such as Average, by selecting a different summary function in the Use Function box in the Subtotal dialog box. Choosing the Values to Summarize The first time you use the Subtotals command; the Add Subtotal To box displays the label of the right-most column. You can leave that label as your selection, or you can select the label of any other column in the list. The next time you use the Subtotals command, Microsoft Excel displays the label of the last column you selected.

Displaying Subtotal Rows above the Detail Data If you want your subtotal rows to appear above their associated detail data, and if you want the Grand Total row to appear at the top of the list, clear the Summary below Data check box in the label of the end of the list to see grand total data.

Displaying Nested Subtotals You can insert subtotals for smaller groups within existing subtotal groups; For example, you can insert subtotals for each type Dept in a list that already has subtotals for each Region wise. Data should be sorted Regionwise and Deptwise.

Note: If you want to copy only the summary details then select the outline2 select the column, press [Alt] + [;] i.e. ALT and Semicolon keys that will select only visible cell and then copy and paste it.

Pivot Tables A pivot table is an interactive worksheet table that quickly summarizes large amounts of data using the given format and calculation methods you select from the given option. Different Views can be created using Pivot Table. As source data changes, you can update a pivot table; you can integrate a pivot table into a larger worksheet model using standard function. Steps to Create a Simple Pivot Table You create a pivot table using the Pivot Table Wizard. The Pivot Table Wizard is an interactive set of dialog boxes that guide you through the steps of choosing the source data and layout you want to use for the pivot table. Start the Pivot Table Wizard A Pivot Table is an interactive table that summarizes and analyses data from existing lists and tables. Use the PivotTable Wizard to specify the list or table you want to use and to define how you want to arrange the data in the PivotTable. After you create a PivotTable, you can reorganize the data by dragging the fields and items. When you base a PivotTable on external data, you may want to retrieve the external data before you create the PivotTable. Open the workbook where you want to create the PivotTable. If you are placing the PivotTable on a Microsoft Excel list or database, click a cell in the list or database. On the Data menu, click PivotTable and PivotChart Report. Follow the instructions in the PivotTable Wizard. Step1: Specify the Type of Source Data In this step user is asked to select whether the data is from single range, multiple ranges, some external application, or an existing Pivot table. The option you choose determines which screen will be displayed next. Assume that the first option i.e., Microsoft Excel List or Database is selected and proceed further to the Step 2 by clicking the Next button at the bottom of the dialog box.

Step2: Specify the Location of the Source Data In this step, enter the source of data from which the pivot table is constructed.

Create a Pivot Table Layout

In Step 3 clicks on layout for the pivot table Once you click on the Layout you get the screen shown below:

Drag the fields you want to use as row field and column field labels into the Row and Column areas. The more fields you include in an area, the finished pivot table contains more fields. Adding two fields to the Row area will result in outline view By moving the Dept field to the Page area, you create a pivot table that displays data for one particular Dept at a time. (Dept wise report is generated. If there is any date/month field, a datewise report could be generated.). It is mandatory to place any one of the fields into the DATA area. If a numeric field is placed by default, it calculates the total of that field, else any other text/date/ character field is placed, and then the count of it will be displayed at the end of report. If you donâ&#x20AC;&#x2122;t specify any field in this area, wizard wonâ&#x20AC;&#x2122;t allow you to proceed to the next step.

Select the Worksheet Destination and Display Options In Step 4 of the Pivot Table Wizard, specify where you want the pivot table to appear. You can place a pivot table on any worksheet you choose or on a separate new worksheet. If you will be changing the source data or layout of the pivot table often, avoid placing the pivot table where it can overwrite existing data. You need to type or select the cell reference only for the upper-left cell of the rectangle where you want the pivot table to appear (in case if you choose existing worksheet option). If the destination worksheet is displayed, you can select the cell on the worksheet to enter the cell reference. To create the pivot table on a new worksheet, select the new worksheet option. When you choose the Finish button, Microsoft Excel creates the pivot table and displays the Pivot Table toolbar on the worksheet. The Pivot Table toolbar buttons to help you quickly customize the pivot table. How to Save Data with the Pivot Table Layout When you create a pivot table, Microsoft Excel stores a copy of the source data as hidden data with the pivot table layout on the worksheet. If you have a large amount of data and you do not want to store a copy of it with the pivot table, clear the Save Data with Table Layout check box in Pivot table Options dialog box(click the options button in step 3 for it).

Updating a Pivot Table If you change data in the source list of table, you can update or refresh the pivot table without recreating it. Most changes you make to the source data can be quickly displayed in the pivot table using the Refresh Data command on the data menu, or by clicking the Refresh Data button on the Pivot Table toolbar. These include Changes to data in a data field in the source list or table. Fields or items deleted from the source list or table

New or changed items in existing rows or fields in the source list or table. (Refer to the figure given in the previous page)

Format a PivotTable report Click on the Pivot Table â&#x20AC;&#x201C; Click on Format Menu then click on Auto format. Calculate the Percentage of the field In the Layout add field that you want get the Percentage, Double click on the Field click on Option Select the % of total from show data.

Top N Report If we want to find which are the top 5 countries which have generated the sales information, or in which 3 years more recruitment has been done, in such scenario, double click on field ,click on advance and click on Top 10 Autoshow, and change the number to what we want.

Group items in a PivotTable If you want to generate a report on Year wise Quarter wise based on existing data you have for such scenario you can use a group option in Pivot Table Group date field Right-click the field with the dates or times, point to Group and Show Details on the shortcut menu, and then clicks Group. Enter the first date or time to group in the Starting at box, and enter the last date or time to group in the Ending at box. In the By box, click one or more time periods for the groups. If you have grouping on date field, you can group items by weeks, click Days in the By box, make sure Days is the only time period selected, and then click 7 in the Number of days box. You can then click additional time periods to group by, such as Month, if you want. 36

Create a Graph using Pivot Data Click on Chart icon from the pivot Tool bar The Graph will be created based on the pivot table. You have drop down to select the graph according to condition wise. Note : if the pivot table is updated with modified source data, Pivot chart will be updated automatically. Further, we want to make this report more specific by showing the same trend for each department. A field we add to the Page Field area (department, for example) lets us filter our PivotTable using values in that field. By adding Department to the Page Field area, we can view the Yearwise, Quarter-wise recruitment trend for each department. Let us now, break down this single page Pivot Table report into multiple pages (sheets). Each page will have a different view of each department.

For getting a Pivot table report for each department in a different sheet, we can click on the Pivot Table toolbar â&#x20AC;&#x201C; Show Pages. Select the field from the Show Pages list and click on OK. It will create copies of the pivot table with a view of each department in separate sheets.

What – If Analysis Tools You have calculated PMT using PMT function. Now you want to know if you pay ‘x’ amount as a PMT, in how many months you can complete the installment? If you know the target value of a given formula but not the input value, the Goal Seek command will help us to compute a value for the input that sets the value for the given formula that matches the specified target. You can use the Goal Seek feature available by clicking Goal Seek on the Tools menu By using Goal Seek you save your time instead of trial-and error analysis.

Using the Goal Seek Command If a person takes a Loan of 100000 for 36 Months the PMT is 3250, if he pays monthly 5000 then in how many months he will complete his installment. To find a specific value that solves a formula, select the cell containing the formula. Then choose Goal Seek from the Tools menu. Select the reference or name of the cell containing the formula for which you want to find a specific solution if it is not displayed in the Set Cell option of the Goal seek dialog box. Output will be as follows Goal-Seeking Guidelines You can enter either cell reference or name in the Set Cell and By Changing Cell boxes. The address in the Set Cell box must be a formula. A changing cell must contain a value that the formula in the Set Cell box depends on, either directly or indirectly.

A changing cell cannot contain a formula. While goal seeking proceeds, the Goal Seek Status dialog box appears on the screen. To interrupt the operation, choose the Pause button. If you want to continue one step at a time after choosing the Pause button, choose the Step button to resume normal operation. When goal seeking is complete, Microsoft Excel displays the results on the worksheet and in the Goal Seek Status dialog box. Choose the OK button to keep the solution values on the worksheet; choose the Cancel button to restore the original values. If you decide to keep the solution on the worksheet but then change your mind, choose Undo Goal Seek from the Edit menu immediately after goal seeking is complete.

Projecting Figures Using a Data Table Most of the spread sheet models contain assumption about certain parameters or input to the model. For e.g. based on different cost price and selling price what will be the profit. Once you've entered formulas on your worksheet, you can perform a "what-if" analysis using a data table to see how changing certain values in your formula affects the results of formulas. A data table is a range of cells that show the results of substituting different values in one or more dependent cells for that formula. Data tables provide: A shortcut for calculating multiple variations in one operation. A way to view and compare the results of all of the different variations together on your worksheet. There are two types of data tables. One-Input Data Table Two-Input Data Table To see how the change in one variable affects calculated values in one or more formulae, use a one-input data table. Now that you have calculated the PMT for the given loan amount, Rate of Interest and No. of Payments.

Suppose, you want to make an assumption that if the loan has to be repaid in 12 months instead of 36 months, what will be the PMT? Likewise, you want to get the similar results for various assumptions. Create a table with all the assumptions (i.e list of input values) in a single column. Then, select the rectangular range containing the formula and the input values (A7:B12). Click on Data â&#x20AC;&#x201C; Table. Select cell B6 as the column input cell. (Because, we have made alternative assumptions for the No. of payments) Adding Formulas or input Values to an Existing Data Table You can use as many formulas and input values, as you need in a one-input data table. However, each formula must directly or indirectly refer to the same input cell. To add additional formulas or input values to a data table, enter them in the blank cell below or to the right of the existing formulas. Then select the entire table and modify it using the Table command on the Data menu. In the preceding illustration, for example, you can also enter a formula in cell C8 that calculates total interest paid on the loan. First, select the rectangular range containing both the formulas and the input values, as shown in the following illustration. Then specify cell B3 as the columninput cell. After you choose the OK button, the new values are added to the table under cell C8. Row Input Values versus Column Input Values In addition to entering input values in a column, as shown in the preceding example, you can also enter input values in a row across the top of the table and enter formulas on the side of the table. With this layout, you specify a Row Input Cell in the Table dialog box. Suppose, you want to make some more assumptions that if you take a larger amount of loan to be repaid in 12 months instead of 36 months, what will be the PMT? Likewise, you want to get the similar results for various assumptions.

In this case, two variable of one formula are to be changed. i.e Loan Amount and No. of Payments. You want to determine how the monthly payment (PMT) will vary as these inputs change. To see how changes in two variables affect one formula, use a two-input data table. You can include a number of values for each of the two variables, but you can use only one formula in a two-input data table. Using a Two-Input Data Table In preparation for creating a data table, let us enter the loan amounts in the range C7:E7 and possible No. of payments in the range B8:B12. Cell B7 contains the output we want to recalculate for various input combinations. Next, select the table range (B7:E12) and choose Data, Table. Because numbers in the first column of the table range are No. of Payments, the column input cell is B6. Numbers in the first row of the table are Loan amounts, so our row input cell is B4. After clicking OK, we see the data table. As you change input values in a spreadsheet, the values calculated by a data table change, too. For example, if we increased fixed cost by $10,000, all profit numbers in the data table would be reduced by $10,000. You canâ&#x20AC;&#x2122;t delete or edit a portion of a data table. If you want to save the values in a data table, select the table range, copy the values, and then choose Edit, Paste Special. Select Values in the Paste area of the dialog box. If you take this step, however, changes to your spreadsheet inputs will no longer cause the data table calculations to update. When setting up a two-way data table, be careful not to mix up your row and column input cells. A mix-up will cause nonsensical results. Most people set their spreadsheet calculation mode to Automatic. With this setting, any change in your spreadsheet will cause all your data tables to be recalculated. Usually, you want this, but if your data tables are large, automatic recalculation can be incredibly slow. If the constant recalculation of data tables is slowing your work down, select Tools, Options, and then click the Calculation tab. Select Automatic Except For Tables. When Automatic Except For Tables is selected, all your data tables recalculate only when you press the F9 (recalculation) key. 41

What If Scenario You would like to create best, worst, and most-likely scenarios for the finding out the Net Income of a company by varying the values of year 1 sales, gross Incomes and Expenses. Data tables for sensitivity analysis allow us to vary only one or two inputs, so we can’t use a data table. Does Excel have a tool that allows us to vary more than two inputs in a sensitivity analysis? The Scenario Manager enables you to perform sensitivity analysis by varying as many as 32 input cells Scenarios are part of a suite of commands called what-if analysis tools. A scenario is a set of values that Microsoft Excel saves and can substitute automatically in your worksheet. You can use scenarios to forecast the outcome of a worksheet model. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. You can define up to 32 changing cell per scenario. You can use the Scenario Manager to: • • • • • •

Create multiple scenarios with multiple sets of changing cells. View the results of each scenario on your worksheet. Create a summary report of all input values and results. Merge scenarios from a group into a single scenario model. Protect scenarios from modification and hide scenarios. Keep track of modifications with an automatic scenario history.

Creating Scenarios

Given are the Sales, Incomes & Expenses of a company for the Year 2009. It also shows the final Net Income of that year.

INCOME Sales Other Income Gross Income EXPENSES Tel. Exp Tra. Exp Misc. Exp Total Expenses

Year 2009 50000 25000 75000 3200 3500 2500 9200

NET INCOME

65800

Let us create following 3 scenarios. i.e assumptions in the rise or fall of either sales, incomes or expenses in the next year.

On the Tools menu, click Scenarios. Click Add. In the Scenario name box, type a name for the scenario. In the Changing cells box, enter the references for the cells that you want to change. [e.g. select the range of current scenario range] Click OK. In the Scenario Values dialog box, type the values you want for the changing cells. To create the scenario, click OK. If you want to create additional scenarios, click Add again, and then repeat the procedure. When you finish creating scenarios, click OK

Create a scenario summary report • • • •

On the Tools menu, click Scenarios. Click Summary. Click Scenario summary or Scenario PivotTable. In the Result cells box, enter the references for the cells that refer to cells whose values are changed by the scenarios. E.g. [Net Income in the above example]. Separate multiple references with commas.

Remarks: The option named Scenario PivotTable Report presents the scenario results in a PivotTable format. Suppose we select a scenario (say Worst) in the Scenario Manager dialog box and then click the Show button. The input cells’ values for the Worst scenario then appear in the spreadsheet and all formulas are recalculated. This tool is great for presenting a “slide show” of your scenarios. We would like to name each cell in the range D16:D20 with the correct abbreviation. To do this, Select the range D16:D20 Choose Insert Name – Create Choose the Create Names - In Left Column option

Merge scenarios from another worksheet It is easier to merge scenarios when all what-if models on the worksheets are identical. All changing cells on the source worksheet must refer to the corresponding changing cells on the active worksheet. Microsoft Excel copies all scenarios on the source sheet to the active worksheet.

Delete a scenario On the Tools menu, click Scenarios. Click the name of the scenario you want to delete, and then click Delete. Display a scenario When you display a scenario, you change the values of the cells saved as part of that scenario.

On the Tools menu, click Scenarios. Click the name of the scenario you want to display. Click Show. Tip: Double-clicking the name of the scenario displayed in the Scenarios box is the same as selecting the name and choosing the Show values. Open all of the workbooks that contain the scenarios you want to merge. Switch to the worksheet where you want to merge the scenarios. On the Tools menu, click Scenarios. Click Merge. In the Book box, click a workbook name. In the Sheet box, click the name of a worksheet that contains the scenarios you want to merge, and then click OK. Repeat this process if you want to merge scenarios from more worksheets.

Protecting Scenarios The Add Scenario and Edit Scenario dialog boxes contain two protection options: Prevent Changes and Hide. If you select Prevent Changes and then activate sheet protection, the scenarios you define cannot be edited. However, this does not prevent you from the values of the changing cells directly on the sheet (unless the cells themselves are locked). Rather, the scenarios themselves are protected from modification when the Prevent Changes check box is selected. In addition, selecting the Hide check box removes a scenario name from the list of defined scenarios, preventing its display. Once you select protection options in the Add Scenario or Edit Scenario dialog box, you must activate sheet protection. To do this, use the protection command on the Tools menu, and then choose Protect Sheet. Note: When sheet protection is activated, you can still add scenarios. However, you cannot edit or delete them unless the Prevent Changes check box is cleared.

Using multiple worksheets Linking worksheets by using 3-D formulas Excel formulas can refer to cells and ranges in other worksheets within a workbook. They can also refer to cells or ranges on a range of worksheets. 3-D formulas A 3-D formula references the same cell or range on multiple worksheets. For example, the formula = Jan!b2+Feb!b2+March!b2 will add the data from the different worksheet range. The syntax for referring to cells in another worksheet is: worksheet_name!reference Here, worksheet_name, refers to the name of the worksheet that provides the data, reference is the name of the cell or range, and ! is the divider between the worksheet reference and the cell reference. To insert a 3-D reference in a formula: Begin to enter your formula or function up to the point of needing a reference or an argument. Activate the tab for the first worksheet to which you want a reference. While holding the Shift key, activate the tab for the last worksheet you want to reference. Select the cell or range of cells you want to reference. Complete and enter the formula or function.

Consolidating Data Consolidation means collecting data which is scattered in different workbooks or worksheets. To consolidate data, you combine the values from several ranges of data. For example, if you have a worksheet of sales figures for each of your regional offices, you might use a consolidation to roll these figures into a corporate sales worksheet. If we have a sheet consisting sales figure for different products for each month like Jan, Feb and March. Suppose we want to find quarterly sales i.e. total sales of the first three months. In such

cases, we can consolidate the three worksheets and get a summary of the same. Letâ&#x20AC;&#x2122;s see how we do it.

Jan Worksheet Feb Worksheet Mar Worksheet In a new sheet, Click on Dataâ&#x20AC;&#x201D;Consolidate. The consolidate dialog box will open.

Select the function from the Function drop down menu. In the Reference option, click on the small red arrow and select the data from Jan. sheet. Click on Add. Similarly select the range for Feb. and March and click on Add. In the Use Labels In option, select the option Left column and Top row. Select the option Create links to source data. Then click on OK. The consolidated data will be displayed on the new sheet (Qtr)

Creating Hyper Link Creating links within the same sheet To link a particular cell, first define name to the cell. Select the cell, click on Insert – Name – Define .The define name dialogue box will appear. In the define dialogue box type a name for the cell and click on Add. Click on Close. Now click on the cell where the hyper link is to be created and Click on Insert- Hyperlink or press Ctrl+K. The Insert Hyperlink box will appear. Click on the Browse button of the ‘Place in this document’ option. All available names from current workbook will be listed in the option ‘select a place in this document’. Select the defined name, which we created in the above step and click on OK. The link is created.

Creating links between different worksheets. To link a particular cell, first define name to the cell. Select the cell, click on Insert – Name – Define .The define name dialogue box will appear. In the define dialogue box type a name for the cell and click on Add. Click on Close. Now click on the cell where the hyperlink is to be created and Click on Insert- Hyperlink or press Ctrl+K. The Insert Hyperlink box will appear. Click on the Browse option of the ‘Existing File or web pages’ option and select the workbook for which the cell is to be linked. Then click on Bookmark. All available names from current workbook will be listed in the option ‘select a place in this document’. Select the defined name, which we created in the above step and click on OK. The link is created.

Creating links between different software Let's assume we have to copy a certain data from an excel sheet to the Word document .If you do a normal copy-paste the contents pasted in the Word document are static i.e. they will not be updated even if there is a change of data in the excel worksheet. Now let's see how to create a link so that the data is updated even in the Word document. Copy the contents from the Excel worksheet. While pasting in a Word document click on Edit-Paste special In the Paste Special dialog box select the option Paste Link and click on OK Now whenever the data in the excel worksheet in changed the change will be automatically reflected in the word document.

Data Forms Excel can generate a built-in data form for your range. The data form displays all of your column labels in a single dialog box, with a blank space beside each label for you to fill in data for the column. You can enter new data; find rows based on cell contents, update existing data, and delete rows from the range. Use a data form when a simple form listing the columns is sufficient and you don't need more sophisticated or custom features. A data form can make data entry easier than typing across the columns. When you have a wide range of columns, all of them will fit on the same screen. Before you can use a data form to add a record to a new range or list, the range or list must have labels at the top of each column. Microsoft Excel uses these labels to create fields on the form.

Add a Record A data form is a dialog box that gives you a convenient way to enter or display one complete row of information, or record, in a range or list at one time.

Go to Data â&#x20AC;&#x201C; Form To add a new record: Click New. Type the information for the new record. When you finish typing data, press ENTER to add the record.

Modify a Record To modify an existing record Find the record you want to change.(To move through records one at a time, use the scroll bar arrows in the dialog box or use Find Next or Find Previous buttons) To set search conditions, or comparison criteria, click Criteria, then enter the criteria into the data form. To find records that match the criteria, click Find Next or Find Previous. To return to the data form without searching for records based on the criteria you specified, click Form.

You can use, Wildcard characters in criteria The following wildcard characters can be used as comparison criteria for filters, and when searching and replacing content. Use ? (question mark) * (asterisk) ~ (tilde) followed by ?, *, or ~

To find Any single character For example, sm?th finds "smith" and "smyth" Any number of characters For example, *east finds "Northeast" and "Southeast" A question mark, asterisk, or tilde For example, fy91~? finds "fy91?"

Change the information in the record. Fields that contain formulas display the results of the formula as a label. The label cannot be changed in the data form. If you change a record that contains a formula, the formula is not calculated until you press ENTER or click Close to update the record. To move to the next field, press TAB. To move to the previous field, press SHIFT+TAB. When you finish changing data, press ENTER to update the record and move to the next record. When you finish changing records, click Close to update displayed record and close data form.

Delete a record Find the record you want to delete. Click Delete Note Data forms can display a maximum of 32 fields at one time. While you are adding or changing a record, you can undo changes by clicking Restore as long as the record is the active record in the data form

Auditing features You can use Excel’s auditing features to trace errors in a worksheet. You can also trace the relationships between cells and formulas on your worksheets.

Tracing errors in a worksheet When Excel detects an error in a worksheet, it will display an error value in the cell as well as an Error Checking Smart Tag. You can click this smart tag to display a list of commands that can help you to trace and correct the error. The Formula Auditing toolbar also provides tools for doing this. To correct an error, select the cell that contains the error and then either click the Trace Error button (on the Formula Auditing toolbar) or choose the Trace Error command (available through the Smart Tag options or the Tools, Formula Auditing submenu). When you do so, you will see tracer arrows pointing from the cell containing the error to other cells that contain values used by the formula in the first cell. Suppose, we get some error while computing Tax in Salary database. How can we easily find out which cells in the spreadsheet model were used for this calculation? We can use Excel’s auditing features to trace errors in a worksheet. We can also trace the relationships between cells and formulas on our worksheets. Go to Tools – Formula Auditing – Formula Auditing Toolbar Trace Precedents: Visually identifies the cells used in the formula being reviewed. If the cell is on a different sheet or workbook, a dotted line will appear. Double click the dotted line and chose the reference you want to see and you will be taken there.

Trace Dependents: Visually identifies the cells that depend on the current cell. As with the Precedent button, this can span across a workbook to other workbooks as long as all related workbooks are open. This can be used when deleting cells to ensure that the cell is not used elsewhere in the spreadsheet.

Displays the Watch window where you can see the result of changes to the source cell on destination cells without navigating between worksheets.

On the Formula Auditing Toolbar, click on the option â&#x20AC;&#x201C; Show Watch Window. Add Cell G12 which contains the total Gross Salary in the Watch Window. Watch Window will display all changes in that cell. i.e if the basic salary of any employee is changed in the database, without navigating till the total Gross Salary(G12) cell, we can see the effect of that change in cell G12.

Workgroup collaboration Sharing a workbook makes it possible for several members of a workgroup to collaborate on the same set of data. For example, several sales managers could enter their respective regional sales figures in the same workbook, making it unnecessary to collect and consolidate the data manually.

Sharing workbooks To share a workbook: Open the workbook that you want to share. Choose Tools, Share Workbook to open the Share Workbook dialog box, and then activate the Editing tab. Check Allow changes by more than one user at the same time, and then click OK. Save the workbook in a location where other users can access it. You can control how a workbook is shared by using the advanced tab of the Share Workbook dialog box. For example, under Update changes, you can select when file is saved to see other usersâ&#x20AC;&#x2122; changes each time you save the workbook. You can also set the interval at which changes will be shown automatically.

Merging workbooks You may need to share a workbook among users who cannot access the same file simultaneously. In such a situation, you can distribute copies of the shared workbook, allow users to make changes to their copies, and then merge those copies into a single workbook. To share a workbook that you intend to merge later: Open the Share Workbook dialog box, activate the Editing tab, and check Allow changes by more than one user at the same time. On the Advanced tab, under Track changes, select Keep change history for. In the box, enter the number of days you want to allow users to make changes in the workbook, and then click OK. Make copies of the workbook and distribute one to each user. After the users have made changes to their copies of the workbook, you can merge the copies into a single workbook. Hereâ&#x20AC;&#x2122;s how: Choose Tools, Compare and Merge Workbooks to open the Select Files to Merge Into Current Workbook dialog box. From the list, select the copy or copies of the workbook that contain changes you want to merge. Click OK.

Tracking changes You can analyze changes users have made to a workbook by using the Track Changes feature. This will tell you who made the changes, when they were made, and the original and changed values without having to manually compare the two workbooks. If your workbook is not shared, Excel makes the workbook shared automatically when you turn on the Track Changes feature.

To highlight changes: Choose Tools, Track Changes, Highlight Changes to open the Highlight Changes dialog box. If the workbook is not shared, check Track changes while editing. If the workbook is shared, this option will be checked by default. Specify how you want changes to be tracked: If you want view changes based on when they were made (for example, after a specific date), check when and then select the desired setting from the list. If you want to view the changes made by a specific user, check whom and then select Everyone, Everyone but your own user name, or me. If you want to view the changes made to a specific range of cells, check where and then enter the range. Click OK.

To review workbook changes and accept or reject them: Open the workbook that has the tracked changes. Choose Tools, Track Changes, Accept or Reject Changes. Youâ&#x20AC;&#x2122;ll be prompted to save the workbook. Click OK to save the workbook. The Select Changes to Accept or Reject dialog box appears. If you want view changes based on when they were made, check when and then select a time setting.

Click OK to open the Accept or Reject Changes dialog box. At the same time, the cell that contains the changed value will be highlighted. The dialog box displays information about each change, including the name of the person who made the change, the date and time when it was made, and other changes that will occur if you accept or reject the suggested change. You can scroll down to view the rest of the contents. Click Accept to accept the change or Reject to keep the original value. The next cell with a changed value will be highlighted.

Advanced formatting features Excel has all sorts of formatting features that you can use to quickly format data, increase consistency, or highlight particular data. These features include AutoFormat, conditional formatting, and merging styles.

AutoFormat The AutoFormat feature provides single-step combinations of text formatting, borders, colors, and shading. The feature works best on data that is logically arranged in a table. By default, the AutoFormat feature will interpret the first row and column of a selected range as headings, and the last row and column as totals. To apply an AutoFormat to a range: Select the range to which you want to apply the format. Choose Format, AutoFormat to open the AutoFormat dialog box. From the list of sample formats, select the format you want, and click OK.

Conditional formatting You can use conditional formatting when you want to apply a format to a cell or range only if certain conditions are met. You can specify a maximum of three conditions. Each condition can have a separate format, such as a different font, border, or pattern. These conditions can be based on two types of data, the cell value or the formula. To apply conditional formatting based on a cell value: Select the range to which you want to apply conditional formatting. Choose Format, Conditional Formatting to open the Conditional Formatting dialog box. From the comparison phrase list, select a comparison phrase This list contains phrases such as between, equal to, greater than, and less than.

If you select the between or not between comparison phrases, two condition boxes will appear. For all the other comparison phrases, only one condition box will appear. In the condition box, enter a value or a cell reference to form a condition. Click Format to open the Format Cells dialog box. Specify a format for the cells that satisfy the condition. You can specify a font, border style, or pattern for these cells.

Comparison phrase list

Condition boxes

Click OK twice to close both dialog boxes. Tip: To remove all conditional formats as well as all other cell formats for selected cells, point to Clear on the Edit menu, and then click Formats. Suppose we want to highlight all the Sales department records, we can use conditional formatting.

Conditional formatting using Formula In the earlier scenario, you can format the column, but if you want to format the entire row then instead of cell value select the formula option, e.g. if you want the highlight the entire row those who are in sales department, in such scenario Select the entire data with heading Click on Format- Conditional Formatting Select ‘Formula is’ instead of ‘cell value is’ Type the Formula as given below

Conditional formatting using Formula with multiple conditions

In the above scenario, we were highlighting all the Sales Department records. However, if we want to highlight only Sales Department from East region, we can change the formula:

Importing Data By importing data, you don't have to retype or copy the data you want to analyze in Microsoft Excel. You can also update your Excel reports and summaries automatically from the original source database whenever the database is updated with new information

Import a text file Click the cell where you want to put the data from the text file. To ensure that the external data does not replace existing data, make sure that the worksheet has no data below or to the right of the cell you click. On the Data menu, point to Import External Data, and then click Import Data. In the Files of type box, click Text Files. In the Look in list, locate and double-click the text file you want to import. To specify how you want to divide the text into columns, follow the instructions in the Text Import Wizard, and then click Finish.

Importing data from databases and files You can import data to Excel from most data sources by pointing to Import External Data on the Data menu, clicking Import Data, and then choosing the data you want to import in the Select Data Source dialog box.

The Data Connection Wizard, available when you click New Source in the Select Data Source dialog box, makes it possible to import data from external data connections not available from the Select Data Source dialog box.

Importing data with Microsoft Query In most cases, you can import data by using the Import Data command as described in the section above. Use Query or another program only if you need to perform specialized query tasks such as the following: • • • • •

Filter rows or columns of data before they are brought into Excel. Create a parameter query. Sort data before it is brought into Excel. Join multiple tables. Microsoft Query provides a simple front end, easily accessible from within Excel, to perform these specialized query tasks.

You can use Query to set up ODBC data sources to retrieve data. In Query, you can use the Query Wizard to create a simple query, or you can use advanced criteria in Query to create a more complex query. You can access Query from Excel, or you can create a query from within the PivotTable and PivotChart Wizard

Importing data from the Web We all know that the World Wide Web contains useful data on just about everything. However, we can’t really do any sort of analysis of this data while it’s on the Web. We need to import the data into Excel. • • •

Choose Data-Import External Data- New Web Query. Paste the URL into the New Web Query dialog box, and then click Go. The Web page appears, and you can select the data you want to import into Excel.

Click on data – Import External data – New Web Query – Type the Web-site address – Select the required data – Import. Output will be as shown in the below figure.

Macros What is Macro? • A Macro is a series of commands written in logical order to automate any repeated task. •

It is stored in Microsoft Visual Basic Module.

•

It can be assigned to a menu item or button.

For example To automatically add a standard company header to any spreadsheet at the press of a button. To format a text file from a general ledger system into a more usable format. To print out certain sheets from within a workbook, rather than going through each sheet and printing individually.

Creating Macro Writing

: Write code for the actions in the sequence using VBA language for the macros.

Recording: Record your actions in Excel using Macro Recorder. Excel has a Macro Recorder

What should be the approach to create a macro? Identify the exact problem and the end result user wants from the macro. Plan the steps of your macro to get the end result successfully. Create your macro either by recording, writing or combining both. Note: Record the action which you have in excel otherwise write it.

Recording Macro 1. Click Tools menu 2. Select Macros 3. Select Record New Macro

Define the Macro

Macro Name

Short cut Macro Storage Description

1. Type the Macro Name 2. Assign shortcut key for the macro.(not mandatory) 3. Click the location where you want to store the macro. 4.

Type additional comments in Description

Click on OK

Macro Name: Follow the following rules while giving Macro name. Macro name can consist of Alphabets, numbers. It should not start with digits. It should not have any special symbol except underscore(_). It can have maximum 255 characters. Do not use a macro name that is also a cell reference. Macro Storage: Personal Macro Workbook: Recording will be performed on current workbook whereas macro will be stored in a file named Personal.xls. This a hidden file (located inside XLSTART folder ) always which gets open whenever excel application is open. This Workbook: Recording will be performed on current workbook and macro will be stored in current file. New Workbook:

Recording will be performed on current workbook whereas macro will be stored in new file. Note: Macros can be used if the file in which they are stored is open. If you want a macro to be available whenever you use Excel, select Personal Macro Workbook. Macro Shortcut: You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #. The shortcut key will override any equivalent default Microsoft Excel shortcut keys while the workbook that contains the macro is open. Macro Description: Description is used to write details about the macro like what is the aim of this macro, etc…Which helps in post maintenance.

Stop Recording

Note: If Stop Recording toolbar does not appear automatically, do the following steps 1. Click View menu 2. Select Toolbars 3. Select Customize 4. Click Toolbars tab 5. Select Stop Recording toolbar

Relative Reference Macro

Stop Recording

Relative Reference

Note : Relative reference button is a toggle button. Be careful about it, before you start recording check whether it is selected or not.

Scenario 1 Create a macro to automatically add the companyâ&#x20AC;&#x2122;s name with the specific format in the first row to any spreadsheet.

Steps to be performed are as follows (Refer Training File1.xls)

1. Start recording, 2. Give the name Company_name, 3. Give short cut CTRL + SHIFT + C 4. Carry out the following steps, 1) Select the first cell.(since name should come on first row), 2) Type your companyâ&#x20AC;&#x2122;s name,

3) Apply the format.(Font size 20, Bold , Blue Font , White Background), 4) Select a1 to h1 cells, 5) Click on Merge tool, Stop the recording. 1) Tools menu 2) Macro 3) Stop Recording.

Run your Macro Macros can be run by The shortcut(which you assign while defining macro) , The name, The tool or menu on the workbook. The button on worksheet

Run the Macro By Name

1. Click tools menu, 2. Select Macro, 3. Select Macrosâ&#x20AC;Ś,(Snapshot 1) 4. Select the macro which you want to run, (Snapshot 2) 5. Click Run button.

Scenario 2 • Create a macro to display a product table with the table headers, Product Name, Quantity, Price , Total and Net , • Table must appear always from the second row and first column, • Excel should not accept any negative values for price and quantity.

The above macro will always results from second row first column (A1 reference). Steps to be performed as follows Start recording.(give name Product_Table and short cut CTRL + Shift + P) Carry out following steps, Select the cell A2.(table must always appear from the second row and first column) Create a table as per the snapshot given, Write the formula for total and Net Total Format it. Do validation for quantity and price cells.(negative values are restricted) Stop the recording.

Scenario 3 Create a macro to display the same product table (Scenario2) but this time it should appear anywhere, where ever user wants (Using relative reference) The above macro depends on user’s selection. Steps to be performed as follows Start recording.(give name Product_Table_Relative and short cut ) Carry out following steps, 1. Switch on Relative Reference button on the Stop Recording toolbar,

2. Create a table from the current cell as per the snapshot given,Note: Start typing from where ever you 3. 4. 5. 6. 7.

are in the sheet , do not click unnecessary in the sheet while creating a relative reference macro Write the formula for total and Net Total Format it. Do validation for quantity and price cells.(negative values are restricted) Switch off the Relative Reference button. Stop the recording.

Creating Menus or Buttons

Creating Buttons or Menus

Create Menu • • • • •

Open Customize Box i,.e., Tools menu Customize ( Snapshot 1) Select Commands tab Select New Menu from Categories ( Snapshot 1) Drag New Menu from Commands and drop anywhere on the menu bar ( Snapshot 1) Click Modify Selection button and give another name

Change the Label of button

Change Button image

Create Menu Items • • • •

Select Macros from Categories (Snapshot 2) Drag Custom Menu Item from Commands and drop inside the New Menu Click Modify Selection button to change the label and assign macro Repeat the steps 2 – 3 for more menu items inside.

Assign Macro

Modify Menus or Button Whenever you want to create new button , edit the existing button or remove any button from the toolbar, do as follows; 1. Open Customize Box,(Tools menu

Customize)

2. Select the button which you want to modify in the toolbar, 3. Click Modify Selection Button to edit existing 4. Drag and drop your button from toolbar into the Customize box to remove the button Note : Use ‘&’ symbol in the Name of button to activate the button by Alt key. For example : &Company Header button can be activated by pressing Alt + C.

Scenario 4 • Create a macro which will extract region-wise sum of salary. (Use “Salary worksheet”) • On click of “Subtotal” button, region-wise subtotal should be added on a new worksheet.

Click to subtotal

Solution

1. Start Recording( give name Subtotal_Macro) (Refer Training File2.xls) 2. Select the Salary sheet (source data) 3. Select cell A1(Database starts from A1) 4. Perform Sort on Region(Snapshot 1) 1. Data menu 2. Sort 3. Select Region(second and third level is not mandatory to select but Region must be first level of sort) 4. Click OK 69

5. Perform Subtotal on Region(Snapshot 2) 1. Data menu 2. Subtotal 3. Select Region in At each change in 4. Select Sum function(you can chose any other functions as per the requirement) 5. Select Salary Field 6. Click OK Note: Sorting must be as per the requirement of Subtotal. Wrong sorting will result in wrong subtotal.

Second Level of Outline

Selection of Visible cells (Alt + ; )

1. Click on Second level of Outline (only subtotal must be shown) 2. Select the data 3. Press Alt + ;(semi colon) key combination to select visible cells from the selection 4. Copy the selection (Ctrl + C) 5. Insert new worksheet ( Shift + F11) 6. Paste the copied data (Ctrl + V) 7. Go to Salary sheet (source data) 8. Remove Subtotal 1. Data menu 2. Subtotal 3. Click RemoveAll button 9. Activate the previous sheet (Ctrl + PageUp)

10. Stop Recording

Creating Buttons on MS Excel worksheet

Button

1. Activate the salary sheet, 2. Click View menu, 3. Click Toolbars, 4. Click Forms toolbar (Snapshot 1), 5. Click button in the toolbar and draw over the sheet (Snapshot 2), 6. Assign the subtotal_macro(Snapshot 3), 7. Change the Caption(Right click and Edit Text) (Snapshot 4).

Scenario 5 â&#x20AC;˘ Create a macro which will extract records from different department and region. â&#x20AC;˘ User will type the required department and region and click filter button

Note: The above macro will automate the job of Advance Filter

Solution

1. Activate Salary sheet 2. Create criteria range for Advance Filter 3. Start recording 4. Give name Filter_Macro 5. Store in This workbook 6. Click on heading of database(cell A5) 7. Do Advance Filter 1. Click Data menu 2. Click Filter

Advance Filter (Snapshot 1)

3. Provide List range i.e., database range (Snapshot 2) 4. Provide Criteria Range (Snapshot 2) 5. Click OK 8. Stop Recording 9. Create a Button from Forms toolbar 10. Assign Filter macro to this button

Editing the Recorded Macros Sometimes recorded macros may not give you full automation. Then you require to edit your recorded macros or you want to add some actions in your recorded macro then you can edit your macro. For example in the scenario – 5 instead of changing criteria in excel your macro should ask which region and dept, user will type then click ok and macro will perform filter. To edit your recorded macro do the following steps 1. Tools 2. Macro 3. Visual Basic Editor 4. Open the code window of your macro

Scenario 6 • Open Scenario – 5 and modify the code to do the following • User should get an InputBox to type the required department and region and click filter button

Solution

To edit your recorded macro do the following steps • Tools, • Macro, • Select Filter_Macro, • Click Edit (See the Snapshot), • Open the code window of your macro. Previous Code

Modify the code as follows:

• • •

Macro definition starts with the keyword Sub and ends with the line End Sub Inputbox is a function used to take input from the end user. Range is a class.

SHORT CUT FOR EXCEL Select cells, columns, rows, or objects in worksheets and workbooks by using shortcut keys Select the current region around the active cell (the current region is an area enclosed by blank rows and blank columns) Extend the selection by one cell Extend the selection to the last nonblank cell in the same column or row as the active cell Extend the selection to the beginning of the row Extend the selection to the beginning of the worksheet Extend the selection to the last cell used on the worksheet (lower-right corner) Select the entire column Select the entire row Select the entire worksheet If multiple cells are selected, select only the active cell Extend the selection down one screen Extend the selection up one screen With an object selected, select all objects on a sheet Alternate between hiding objects, displaying objects, and displaying placeholders for objects Show or hide the Standard toolbar In End mode, to Turn End mode on or off Extend the selection to the last nonblank cell in the same column or row as the active cell Extend the selection to the last cell used on the worksheet (lower-right corner) Extend the selection to the last cell in the current row; this keystroke is unavailable if you selected the Transition navigation keys check box on the Transition tab (Tools menu, Options command) With SCROLL LOCK on, to Turn SCROLL LOCK on or off Scroll the screen up or down one row Scroll the screen left or right one column Extend the selection to the cell in the upper-left corner of the window Extend the selection to the cell in the lower-right corner of the window

CTRL+SHIFT+* (ASTERISK) SHIFT+ arrow key CTRL+SHIFT+ arrow key SHIFT+HOME CTRL+SHIFT+HOME CTRL+SHIFT+END CTRL+SPACEBAR SHIFT+SPACEBAR CTRL+A SHIFT+BACKSPACE SHIFT+PAGE DOWN SHIFT+PAGE UP CTRL+SHIFT+SPACEBAR CTRL+6 CTRL+7 Press END END, SHIFT+ arrow key END, SHIFT+HOME

END, SHIFT+ENTER Press SCROLL LOCK UP/DOWN ARROW LEFT.RIGHT ARROW SHIFT+HOME SHIFT+END

Select cells with special characteristics by using shortcut keys

Select the current region around the active cell (the current region is an area enclosed by blank rows and blank columns) Select the current array, which is the array that the active cell belongs to Select all cells with comments Select cells whose contents are different from the comparison cell in each row (for each row, the comparison cell is in the same column as the active cell) Select cells whose contents are different from the comparison cell in each column (for each column, the comparison cell is in the same row as the active cell) Select only cells that are directly referred to by formulas in the selection Select all cells that are directly or indirectly referred to by formulas in the selection Select only cells with formulas that refer directly to the active cell Select all cells with formulas that refer directly or indirectly to the active cell Select only visible cells in the current selection

CTRL+SHIFT+* (ASTERISK) CTRL+/ CTRL+SHIFT+O (the letter O)

CTRL+\

CTRL+SHIFT+| CTRL+[ CTRL+SHIFT+{ CTRL+] CTRL+SHIFT+} ALT+SEMICOLON

Move and scroll on a worksheet or workbook by using shortcut keys Move one cell in a given direction Move to the edge of the current data region Move between unlocked cells on a protected worksheet Move to the beginning of the row Move to the beginning of the worksheet Move to the last cell on the worksheet Move down one screen Move up one screen Move one screen to the right Move one screen to the left Move to the next sheet in the workbook Move to the previous sheet in the workbook Move to the next workbook or window Move to the previous workbook or window Move to the next pane Move to the previous pane Scroll to display the active cell In End mode, to Turn End mode on or off Move by one block of data within a row or column Move to the last cell on the worksheet Move to the last cell to the right in the current row that is not blank With SCROLL LOCK turned on, to Turn SCROLL LOCK on or off Move to the cell in the upper-left corner of the window

Arrow key CTRL+ arrow key TAB HOME CTRL+HOME CTRL+END PAGE DOWN PAGE UP ALT+PAGE DOWN ALT+PAGE UP CTRL+PAGE DOWN CTRL+PAGE UP CTRL+F6 or CTRL+TAB CTRL+SHIFT+F6 F6 SHIFT+F6 CTRL+BACKSPACE Press END END, arrow key END, HOME END, ENTER Press SCROLL LOCK HOME

Move to the cell in the lower-right corner of the window Scroll one row up or down Scroll one column left or right

END UP/DOWN ARROW LEFT/RIGHT ARROW

Format data by using shortcut keys Display the Style command (Format menu) Display the Cells command (Format menu) Apply the General number format Apply the Currency format with two decimal places (negative numbers appear in parentheses) Apply the Percentage format with no decimal places Apply the Exponential number format with two decimal places Apply the Date format with the day, month, and year Apply the Time format with the hour and minute, and indicate A.M. or P.M. Apply the Number format with two decimal places, 1000 separator, and â&#x20AC;&#x201C; for negative values Apply the outline border Remove all borders Apply or remove bold formatting Apply or remove italic formatting Apply or remove an underline Apply or remove strikethrough formatting Hide rows Unhide rows Hide columns Unhide columns

ALT+' (APOSTROPHE) CTRL+1 CTRL+SHIFT+~ CTRL+SHIFT+$ CTRL+SHIFT+% CTRL+SHIFT+^ CTRL+SHIFT+# CTRL+SHIFT+@

CTRL+SHIFT+! CTRL+SHIFT+& CTRL+SHIFT+_ CTRL+B CTRL+I CTRL+U CTRL+5 CTRL+9 CTRL+SHIFT+( CTRL+0 (ZERO) CTRL+SHIFT+)

Print and preview a document by using shortcut keys Display the Print command (File menu) Move around the page when zoomed in Move by one page when zoomed out Move to the first page when zoomed out Move to the last page when zoomed out

CTRL+P Arrow keys PAGE UP or PAGE DOWN CTRL+UP ARROW CTRL+DOWN ARROW

Keys for menus Show a shortcut menu Make the menu bar active Show the program icon menu (on the program title bar) Select the next or previous command on the menu or submenu Select the menu to the left or right, or, with a submenu visible, switch between the main menu and the submenu Select the first or last command on the menu or submenu Close the visible menu and submenu at the same time Close the visible menu, or, with a submenu visible, close the submenu only

SHIFT+F10 F10 or ALT ALT+SPACEBAR DOWN/UP ARROW LEFT/RIGHT ARROW HOME or END ALT ESC

Edit data by using shortcut keys Edit the active cell and complete Cancel an entry in the cell or formula bar Edit the active cell and then clear it, or delete the preceding character in the active cell as you edit the cell contents Paste a name into a formula Enter a formula as an array formula Display the Formula Palette after you type a valid function name in a formula Insert the argument names and parentheses for a function, after you type a valid function name in a formula

F2 + Enter ESC BACKSPACE F3 CTRL+SHIFT+ENTER CTRL+A CTRL+SHIFT+A

Insert, delete, and copy a selection by using shortcut keys Copy the selection Paste the selection Cut the selection Clear the contents of the selection Insert blank cells Delete the selection Undo the last action

CTRL+C CTRL+V CTRL+X DELETE CTRL+SHIFT+PLUS SIGN CTRL+ â&#x20AC;&#x201C; CTRL+Z

Move within a selection by using shortcut keys Move from top to bottom within the selection (down), or in the direction that is selected on the Edit tab (Tools menu, Options command) Move from bottom to top within the selection (up), or opposite to the direction that is selected on the Edit tab (Tools menu, Options command) Move from left to right within the selection, or move down one cell if only one column is selected Move from right to left within the selection, or move up one cell if only one column is selected Move clockwise to the next corner of the selection Move to the right between nonadjacent selections Move to the left between nonadjacent selections

ENTER

SHIFT+ENTER TAB SHIFT+TAB CTRL+PERIOD CTRL+ALT+RIGHT ARROW CTRL+ALT+LEFT ARROW