 |
| Advance Excel Education |
DATA VALIDATIONVIDEO NO.01 : DATA VALIDATION
Use to pickup from list of rules to limit the type of data like whole numbers,list etc. that can be entered in a cell using settings option from data validation. you can apply the condition to selected cell or cells. further you can insert own created input and error alert messages as well.
first apply the valid settings using validation criteria from drop down menu then insert both input message as well as error alert message using style stop/warning/information according to your worksheet inserted. to remove validation choose clear all option selecting the range.circle invalid data use to easily identify the data. and to clear circles invalid data use clear validation circles. this function is available in data tab under data tools group. video on data validation
VIDEO NO.02 : USING SCENARIOS
Try out various values for the formulas in excel sheet using scenario. scenarios are used to create budget.the function is available in data tab under data tools group in what-if-analysis.you can add more than one scenarios at a time.also you can edit/delete scenario.merge scenario option is used to create scenario from different sheets.summary is use to show added scenario which are yet to finalize. used to create big MIS statement/Big budget statement in an organization.
VIDEO NO.04 : HLOOKUP IN ADVANCE EXCEL
Looks for a value in the top row of a table or array of values and returns the value in the same column from a row specify. this formula you can easily apply when the typed sheet is horizontal.
formula =hlookup(lookup_value,table_array,row_index_num,true/false).
VIDEO NO.03 : VLOOKUP IN EXCEL
Looks for a value in the leftmost column of a table.Then returns a value in the same row from a column specify.Default table sorting is in an ascending order. this formula you can easily apply when the typed sheet is vertical.formula =vlookup(lookup_value,table_array,column_index_num,true/false)
 |