Hidden and Very Hidden sheets in excel

This article explains what is hidden and very hidden sheets in excel, what is the difference between these two and how to hide and unhide sheets. Hidden and Very Hidden sheets in Excel In excel we can hide the sheets which are created in a workbook. There are 2 levels of hiding the worksheets. Hidden […]

Excel VBA listbox search as you type from all sheets

Search result in listbox as you type from all sheets

When you have large amount of data in multiple sheets and you want to get the search results dynamically as you type , you can add a user form in excel vba and create a search box as below. Display search result in listbox from all sheets as you type To create this search box, […]

VBA ColorIndex list and macro to list color index

In VBA we can assign the color property of the object by changing or assigning the color index. There are 56 color index which you can use in vba. Below are the list of color indexes you can use in vba. Below is the VBA code to list out the color and color codes in […]

How to use Excel EFFECT Function

Introduction:- EFFECT function in excel is a financial function. It is used to find effective annual interest rate. It is defined as the actual interest rate earned annually due to compounding. It is a worksheet function and can be entered in the cell in which value is required. Syntax:- =EFFECT(nominal_rate, npery) Arguments:- Nominal_Rate: It is […]

How to use Excel ERROR.TYPE Function

Introduction:- ERROR.TYPE function in excel is an information function. It is used to retrieve a number corresponding to an error value. If there is no error than #N/A is displayed. It is a worksheet function and can be entered in the cell in which value is required. Syntax:- =ERROR.TYPE(error_value) Arguments:- Error_Value: The error value whose […]

How to use Excel DURATION Function

Introduction:- DURATION function in excel is a financial function. It is used to find duration of a security in Macaulay form using Macaulay duration formula. Duration is expressed as weighted average of cash flows at present. It is a worksheet function and can be entered in the cell in which value is required. Syntax:- =DURATION […]

How to use Excel FV Function

Introduction:-     FV function in excel is a financial function. It is used to find the future value of an investment. It is a worksheet function and can be entered in the cell in which value is required. Syntax:- =FV (rate, nper, pmt, [pv], [type]) Arguments:- rate – It is the interest rate per period. nper – It […]

How to use Excel DISC Function

Introduction:- DISC function in excel is a financial function. It is used to find discount rate of a security. It is a worksheet function and can be entered in the cell in which value is required. Syntax:- =DISC(settlement, maturity, pr, redemption, basis) Arguments:- Settlement: It is the date on which security is settled. It comes […]

How to use Excel FVSHEDULE Function

Introduction:- FVSCHEDULE function in excel is a financial function. It is used to find the future value of an initial principal after a number of compound interest rates. It is a worksheet function and can be entered in the cell in which value is required. Syntax:- =FVSCHEDULE (Principal, Schedule) Arguments:- Principal: It is the present […]