MS-Excel Insights

  XLOOKUP function which is used to extract the cell value from any selected data source with reference to the selected lookup value. XLOOKUP works for the data either it is vertically or horizontally organized which means XLOOKUP has the functionally for both VLOOKUP & HLOOKUP function. Syntax There are 6 arguments used in XLOOKUP function out of which first 3 arguments are required and others are optional as follows. lookup_value :   Cell that contain text or number which is selected to search the same from the selected lookup-array and extract the desired value from the selected return_array .    lookup-array : Range of cells from the source data that is selected to search the selected lookup_value. return_array Range of cells from the source data that is selected from which the corresponding value will be returned, based on the position of lookup_value in the lookup_array . [if_not_found] (optional) This argument allows th...

      1.         Ctrl + A: This is used to select all the cells in a worksheet.   2.         Ctrl + B: This is used to bold the value of selected cell or range of cells in a worksheet. 3.         Ctrl + C: This is used to copy the value of selected cell or range of cells in a worksheet. 4.         Ctrl + V: This is used to paste the copied cell value in cell or range of cells in a worksheet. 5.         Ctrl + F: This is used to find out the cell value in selected cell or range of cells in a worksheet. 6.         Ctrl + H:         This is used to override the existing cell value in selected cell or range of cells in a  worksheet. 7.         Ctrl + G:    ...

  In MS-Excel, we can type anything like numbers, text, date etc.  in a cell  or range but if we want to restrict a cell or range to have a specific data, we can use data validation. For Instance: Let suppose we have a data as shown in a following screenshot,   In this data we want to apply the restriction that we cannot write anything other than given 4 Standard Reasons in the selected range.  Now, select the cell or range where data validation is required to be added, go to the Data menu and click on Data Validation option (Shortcut key: Alt + A + V ) and then select the Data Validation as shown in the following screenshot. By an option, the following dialog box will get appeared in which, select  List  option from a dropdown and then in the field of   Source,  enter the reference of range wherein the standard reasons  are mentioned  and click on   OK . Now, while editing cell or range wherein the Data Validation added, we w...