JA EXCEL

Form controls in Excel are objects that you can add to a worksheet to create interactive forms, questionnaires, or surveys. These controls can be used to input or display data, and to trigger actions based on user input. Some of the most commonly used form controls in Excel include: Check boxes: These are used to give users a simple yes/no or true/false option. Radio buttons: These are used to provide users with a set of mutually exclusive options from which they must choose one. Drop-down lists: These are used to provide users with a list of options from which they can select one. Combo boxes: These are similar to drop-down lists, but they also allow users to type in a custom option. Spin buttons: These are used to increment or decrement a value in a cell by a certain amount. Scroll bars: These are used to provide a visual way for users to adjust a value within a range. Text boxes: These are used to allow users to input text or numerical data. Option buttons: These are simil...

\ Form controls in Excel are objects that you can add to a worksheet to create interactive forms, questionnaires, or surveys. These controls can be used to input or display data, and to trigger actions based on user input. Some of the most commonly used form controls in Excel include: Check boxes: These are used to give users a simple yes/no or true/false option. Radio buttons: These are used to provide users with a set of mutually exclusive options from which they must choose one. Drop-down lists: These are used to provide users with a list of options from which they can select one. Combo boxes: These are similar to drop-down lists, but they also allow users to type in a custom option. Spin buttons: These are used to increment or decrement a value in a cell by a certain amount. Scroll bars: These are used to provide a visual way for users to adjust a value within a range. Text boxes: These are used to allow users to input text or numerical data. Option buttons: These are simi...

Mail merge is a feature in word processing programs, such as Microsoft Word, that allows you to create personalized documents, such as letters or emails, by combining a standard document with a data source. This is particularly useful when you need to create multiple documents that share the same format but contain unique information for each recipient. To perform a mail merge, you typically start by creating a standard document that contains the text and formatting you want to use for all the documents. Next, you create a data source, which can be a spreadsheet or database, that contains the unique information for each document, such as names and addresses. Once you have your standard document and data source, you can use the mail merge feature to combine the two. The software will automatically insert the unique information from the data source into the appropriate places in the document, creating a personalized document for each recipient. Mail merge is commonly used for crea...

Click here to download addin or copy paste below code in your excel module Function Numbertowords(ByVal MyNumber)     Dim Rupees, paisa, Temp     Dim DecimalPlace, Count     ReDim Place(9) As String     Place(2) = " Thousand "     Place(3) = " Million "     Place(4) = " Billion "     Place(5) = " Trillion "     ' String representation of amount.     MyNumber = Trim(Str(MyNumber))     ' Position of decimal place 0 if none.     DecimalPlace = InStr(MyNumber, ".")     ' Convert paisa and set MyNumber to Rupee amount.     If DecimalPlace > 0 Then         paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _                   "00", 2))         MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))     End If     Count = 1     Do While MyNumbe...

 

 General Open help F1 Undo last action Ctrl + Z Redo last action Ctrl + Y Copy selection Ctrl + C Repeat last action F4 Cut selection Ctrl + X Paste content from clipboard Ctrl + V Display the Paste Special dialog box Ctrl + Alt + V Find previous match (after initial Find) Ctrl + Shift + F4 Find next match (after initial Find) Shift + F4 Insert embedded chart Alt + F1 Insert embedded chart Alt + F1 Toggle Autofilter Ctrl + Shift + L Activate Filter Alt + ↓ Create table Ctrl + T  Select table row Shift + Space Select table column Ctrl + Space  Select table (when active cell is in table) Ctrl + A Clear slicer filter  Alt + C Run Spellcheck F7 Open Thesaurus Shift + F7 Open Macro dialog box Alt + F8 Open VBA Editor Alt + F11 Duplicate object Ctrl + D Snap to grid (whilst dragging) Alt Hide or show objects Ctrl + 6 Open Modify Cell Style dialog box Alt + '  Show right-click menu Shift + F10 Display control menu Alt Space Worksheet  Insert new...

>>>>>>>>>>>   Please click here to download addin Or  copy paste below code in your excel module Function multilookup(lookupvalue As String, lookuprange As Range, Optional columnnumber As Integer = "2") Dim i As Integer Dim Result As String For i = 1 To lookuprange.Rows.Count If lookupvalue = lookuprange.Cells(i, 1).Value Then Result = Result & " " & lookuprange.Cells(i, columnnumber) & "," End If Next i multilookup = Left(Result, Len(Result) - 1) End Function

In Excel, the Fill Series feature allows you to quickly fill a range of cells with a series of data, such as a list of dates, numbers, or text. To use the Fill Series feature: Select the first cell in the range where you want to create the series. Type the first value in the series. Select the next cell or cells in the range where you want to create the series. Go to the Home tab, in the Editing group, click on the Fill drop-down arrow and then click on Series. In the Series dialog box, select the type of series you want to create (e.g. Linear, Date, AutoFill) and set any additional options as needed. Click OK to create the series. Note that you can also use the Fill Handle to fill a series. To do this, select the first cell of the series, and then drag the fill handle (the small black square at the bottom-right corner of the selected cell) to the last cell of the range where you want to create the series. You can also use the Ctrl + R to fill right and Ctrl + D to fill down. Ex...

Paste Special in Excel allows you to control how data is pasted into a worksheet, including options such as Skip Blanks. To use the Paste Special option to skip blank cells: Copy the data you want to paste into a worksheet. Select the cell or range of cells where you want to paste the data. Right-click and select Paste Special from the context menu. In the Paste Special dialog box, select the Skip Blanks checkbox. Click OK to paste the data and skip any blank cells. Alternatively, you can also use the keyboard shortcut, Ctrl+Alt+V and then select the Skip Blanks option. Using this option will paste only non-empty cells from the copied range, and leave any empty cells in the destination range unchanged. This can be useful when you want to copy data from one worksheet to another and want to preserve the existing data in the destination worksheet. It's important to note that the Skip Blanks option only applies to empty cells, not cells that contain formulas or formulas that eval...

=CHAR(160) When you use this function in a cell, it will return a non-breaking space, which is a special type of space that is used to prevent a line break at that point in the text. This can be useful for formatting cells that contain long text strings or for aligning text in a specific way. You can use this function in a formula or directly in the cell. It's also important to note that you can use the ALT + 0160 to insert the non-breaking space in the cell.

Sub MergeSelectedRange() 'Declare variables Dim rng As Range Dim cell As Range Dim lastCell As Range Dim currentValue As String Dim lastValue As String 'Prompt the user to select the range Set rng = Application.InputBox("Select the range to merge:", Type:=8) 'Initialize the lastValue variable lastValue = rng.Cells(1, 1).Value 'Loop through the selected range For Each cell In rng currentValue = cell.Value If currentValue = lastValue Then 'If the current cell has the same value as the last cell, add it to the range If lastCell Is Nothing Then Set lastCell = cell Else Set lastCell = Union(lastCell, cell) End If Else 'If the current cell has a different value than the last cell, merge the range If Not lastCell Is Nothing Then lastCell.Merge Set la...

Excel allows you to customize the sort order of your data by creating a custom sort order. This can be useful when you have data that does not sort correctly using the built-in sort options, or when you want to sort data in a specific order that is not alphabetical or numerical. To create a custom sort order in Excel: Select the data that you want to sort. Go to the Data tab, in the Sort & Filter group, click on the Sort A-Z button. In the Sort dialog box, select the column that you want to sort by. Click the Options button. Select the "Sort On" option and choose "Custom List" In the Custom List section, select the list you want to use or type in your own list. Click OK to apply the custom sort order. Alternatively, you can also use the VBA code to sort the data by a custom list. Here is an example of VBA code that sorts a range called "MyRange" using a custom list: Copy code With MyRange .Sort Key1:=.Cells(1), Order1:=xlAscending, _ K...

In Excel, you can sort data horizontally by using the "Sort Left to Right" option. To do this, select the range of cells that you want to sort, then go to the "Data" tab and click on the "Sort" button. In the "Sort" dialog box, select "Options" and then choose "Sort Left to Right". You can then specify the column that you want to sort by and whether you want the sort to be in ascending or descending order.

     

     

     

     

Flash Fill is a feature in Microsoft Excel that allows you to automatically fill in a column or row with data based on a pattern or example you provide. It works by analyzing the data you've entered in one cell and then using that information to automatically fill in the rest of the column or row. Flash Fill can be a useful tool for quickly and easily cleaning and formatting data in a spreadsheet. To use Flash Fill, simply enter the data you want to use as the pattern or example in the first cell, and then press the Ctrl + E key combination. Excel will automatically fill in the rest of the column or row with the appropriate data.      

     

     

     

     

     

     

     

     

The SUMIFS function in Excel is a function that allows you to sum the values in a range of cells based on multiple criteria. The syntax for the SUMIFS function is as follows: SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...). The "sum_range" is the range of cells that you want to sum, and each "criteria_range" and "criteria" pair specifies a criterion for including a cell in the sum. For example, to sum the values in the range A1:A10 where the values in the range B1:B10 are greater than 5 and the values in the range C1:C10 are less than 10, you would use the formula =SUMIFS(A1:A10, B1:B10, ">5", C1:C10, "

     

     

     

     

     

     

     

     

     

     

     

     

     

     

Syntax Reference form AGGREGATE(function_num, options, ref1, [ref2], …) Array form AGGREGATE(function_num, options, array, [k])

You can use the VLOOKUP function in Excel to search for a specific value in a table and return a corresponding value from a specified column, even if that column is not the first one in the table. To do this, you will need to use the COLUMN function in conjunction with the VLOOKUP function. The COLUMN function returns the column number of a specified cell. For example, COLUMN(A1) would return 1, since A1 is in column 1. You can use this function in conjunction with the VLOOKUP function to specify a column other than the first one in the table. Example: =VLOOKUP(A2,B2:D5,COLUMN(C1),FALSE) In this example, the function will look for the value in A2 in the first column of the table B2:D5, and return the corresponding value from the column represented by C1. Note that when you use the COLUMN function in the VLOOKUP function, the column number you provide must be relative to the table range, not the entire worksheet.

The VLOOKUP function in Excel can be used with wildcard characters to find and retrieve data based on a partial match of a lookup value. The wildcard characters that can be used with VLOOKUP are the "*" (asterisk) and "?" (question mark) characters. The "*" (asterisk) character is used to match any sequence of characters, while the "?" (question mark) character is used to match any single character. To use a wildcard character with VLOOKUP, you need to include the wildcard character in the lookup value within quotation marks. For example, to find the price of a product that starts with the letter "A" and has any number of characters after it, you can use the following formula: =VLOOKUP("A*",A1:C10,3,FALSE) This formula will look for the value "A*" in the first column of the range A1:C10, and if it finds a match, it will return the value in the third column of the same row. Similarly, if you want to find a pro...