Everything about excel array formula

ARRAY

Normally you can't perform multiple formulas at a time. But if you use ARRAY then you can perform. To perform ARRAY just press Ctrl+Shift+Enter to execute.It means that you will write formulas normally like =SUM(A1*B1) but to execute you don't press enter, you have to press Ctrl+Shift+Enter.
Example:
In the table, you have Customer Id, Total amount, Discount.Now let's find the total amount after the discount and also do the sum of all amount.
If you do this normally then what you will do is that at first you will subtract total amount and discount and after finding the discount you will apply the sum formula.

Output without using ARRAY:

This method takes time but you can do this easily by using ARRAY.

After writing the formula press Ctrl+Shift+Enter to execute.
Output after applying ARRAY formula:

Here you can see that the output with using ARRAY and output using ARRAY is the same but using ARRAY you did less work, use less time, and get a good result.

How to create search box in excel?

If I want to search for anything in the dataset and after searching if you want to highlight those cells then use the search box. Suppose you have three columns, salesperson, product name, and area in a table. Now you want to find that how many times Rafsun present in the name column or any area name or any product name in the table and want to see how many times those are present in the column and also want to highlight those cells, in this case use the search box.

To create first prepare a cell where you want to type that name which you want to search. Then select the total table but don't select the column name. Then go to conditional formatting(in the Home tab) new rule option. After this, a dialog box will open. There click on use a formula to determine which cells to use as format option. After clicking you will see an option named format value where this formula cells to format. Go there and select that cell where you want to write the search value. Then give equal and select table first column first cell. Select two cells one cell where you will write the search value and one is the table first column first cell. Remove the dollar sign from the first column's first cell value. After doing this you will see the format option below. Go there and select a color and then click on ok.

Now go to that cell type and hit enter you will see that if the value is present then all those cells will be highlighted.