FORMULAS Archives - Page 13 of 17

Apr62020 by excelhelp13No Comments

DATA VALIDATION Restrict User to add any data for a specific cell or range.

For Creating DATA VALIDATION Restrict User to add any data for a specific cell or range. please follow the few step shown below. Select a cell where you want to have restriction for user to not enter any data.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select Custom from that dialog box and go down one step into Formula row in that dialog box.Now enter the Formula For example :- =B2="EXCEL HELP"Once added the formula just hit ok button in the dialog box and its done.NOW B2 Cell will only accept the value EXCEL HELPNow for that particular range or a single cell user cannot enter any other value then the cell contain already.If you want to show a message when a user select the restricted cell just go to Input message next to setting in the dialog bo...

Apr62020 by excelhelp13No Comments

Data Validation Restrict User To Add Limited Character.

FORMULAS, Text

For Creating a Data Validation restriction for user to enter limited character in a particular cell please follow the few step shown below. Select a cell where you want to have restriction for user to enter the number of limited character.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select Text Length from that dialog box and go down one step into source row in that dialog box.Now enter the minimum and maximum character the user must have to enter. For example :- Minimum = 5 and Maximum = 10Once Selected just hit ok button in the dialog box and its done.Now for that particular range or a single cell user only can enter the minimum to maximum character.If you want to show a message when a user select the restricted cell just go to Input message next...

Apr62020 by excelhelp13No Comments

PIVOT TABLE In Excel

FORMULAS, Text

Pivot Table is used to summarize and organize the data of extensive table. This includes sums, count, average or any other data statistics. Pivot table groups and organize data in a presentable way. Pivot tables are most used to sort out the data and prepare a summarize report in just few seconds. To create a pivot table and generate a summarize report please follow the step mentioned below. First Go To Insert Tab available on top ribbon of excel sheet.Then click on Pivot TableOnce clicked a Dialog Box will pop-up.In the select range source bar it will automatic select the source table. But if you want to select the different range you can select it.Then little down in that dialog box you will find 2 option to select the sheet (1) New Sheet (2) Existing Sheet.If you select New Sheet...

Apr52020 by excelhelp13No Comments

VLOOKUP In Excel using Different Sheet

FORMULAS, Lookup & Reference

TO use V-lookup on different sheet and extract the data from different sheet please follow the mentioned step below. V-LOOKUP is a vertical lookup formula in excel. V-LOOKUP only looks from left to right. It always take a lookup reference from first column and give result from the column index selected. Let's understand with the example. In this example we need to extract the EMAIL ID From sheet 2 with the help of V-lookup. STEP-1 Select THE CELL STEP-2 =Vlookup( Lookup value , go to sheet 2 and select the table, provide the column index number where Email exist, 0 ( For exact match) HIT ENTER.

Apr52020 by excelhelp13No Comments

Transpose Data From Column to Row or Row to Column

FORMULAS, Text

To exchange or transpose or copy the data from Column to Row or Row to Column please follow the step mentioned below. Transpose means to exchange the place of data.Transpose to Row to Column means to copy the data from Row to Column (Header).Transpose to Column to Row means to copy the data from Column (Header) to Row.Select the data from where you want to transpose it. From COLUMN or ROW.Then copy the selected data (Make sure the transpose range is same or the range is enough to paste it.Make Sure there is no Merge Cells exist.Select the cell where you want to paste the data.Then right click and Click Paste Special.One Dialog box will pop-up.Down to the right bottom check the checkbox of TRANSPOSE and click Ok.For more detail please check the image and video provided below. ...

Apr52020 by excelhelp13No Comments

Extract Text Using Left – Right – Find – and LEN Function In Excel

FORMULAS, Text

For extracting specific text from a particular sentence or a word please follow the step shown below. TO EXTRACT FROM LEFT Here we have a list of Emails in the excel sheet and we want to find and extract the text before the character @ from the email id Example01@hHotmail.comFor that we need 2 function LEFT and FINDLEFT FUNCTION Looks and start from left side.FIND FUNCTION find the specific character from the reference text.Use Formula =LEFT(A2,FIND("@",A2,1) But this will extract the character "@" too. Result = Example01@But not to worry for that simply minus (-) 1 character. Formula =LEFT(A2,FIND("@",A2,1)-1) Result = Example01For example please see the image and video provided below. TO EXTRACT FROM RIGHT Here we have a list of Emails in the excel sheet and we want to find...

Apr42020 by excelhelp13No Comments

EXTRACT TEXT With LEN() and RIGHT() Function

FORMULAS, Text

Suppose you only want a specific text from a particular cell and avoid extra label like "Name:-" or whatever your database contain. To apply it and get the result you will have to follow the few step as mentioned below. First find out the total length of the particular cell text by using =LEN(A2) formula and apply in next to name column OR you can directly apply into the LEN function like this =RIght(A2,LEN(A2)-7) NAME:- <------(7) character. .Remember it always depends on your criteria so apply according to your criteria baseNOTE: LEN() function count the space too.Follow the step shown in image and video provided below. IMAGE EXAMPLE ONE : IMAGE EXAMPLE TWO :

Apr42020 by excelhelp13No Comments

NESTED SUMIF With Drop Down List

FORMULAS, Maths, Text

Create a Drop Down List and then apply SUMIF formula in next cell. Just follow the below step to create drop down menu and then apply the Sumif formula First we need to create a drop down list.Select a cell where you want to have your drop down list.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select List from that dialog box and go down one step into source row in that dialog box.Now select the range for the data you want to create a drop down list. For Ex:- A2:A10Once Selected just hit ok button in the dialog box and your drop down list is prepared.Then in the next cell apply the NESTED SUMIF formula.For Example see the step shown in the image and a video provided below. (adsbygoogle = window.adsbygoogle ||...

Apr42020 by excelhelp13No Comments

DROP DOWN List In Excel

FORMULAS, Statistical, Text

For Creating a Drop Down List just follow the few step shown below. Select a cell where you want to have your drop down list.Go to Data TabGo to Data Validation and click on it and one dialog box will pop up.Select List from that dialog box and go down one step into source row in that dialog box.Now select the range for the data you want to create a drop down list. For Ex:- A2:A10Once Selected just hit ok button in the dialog box and your drop down list is prepared.For Example see the step shown in the image and a video provided below.

Apr32020 by excelhelp13No Comments

AND Function In Excel

FORMULAS, Logical

AND Function is a logical function in excel. Basically AND Function is used to fulfill more than 1 criteria and show result in the form of TRUE or FALSE. If a single criteria is not matching then as a result it will give you false. Suppose if you want to find the logical condition true or false as per example shown in image You can also use this AND function with other function in excel like SUM, Count, Countif, Countifs and many more.