excelhelp.in Archives - Page 8 of 11

Apr112020 by excelhelp13No Comments

CONDITIONAL FORMATING In Excel

NEW RULE

To Use And Apply The Conditional Formatting Please Follow The Step Shown Below And Example In Image And Video. HIGHLIGHT GREATER THAN:- When you apply this greater than highlighting rule it will highlight the number which is greater than the reference number.LESS THAN:- When you apply this less than highlighting rule it will highlight the number which is less than the reference number.BETWEEN:- When you apply this between highlighting rule it will highlight the number which is are between the given two reference number.EQUAL TO:- When you apply this equal to highlighting rule it will highlight the number which is equal to or same to reference number.TEXT THAT CONTAINS:- When you apply this text to contains highlighting rule it will highlight the text from the selectio...

Apr72020 by excelhelp13No Comments

PRINT TITLE On Every Page In Excel

FORMULAS, Page Layout, Statistical

How to use the already built in function in excel for printing the title on every page. Please follow the step mentioned below. GO to Page Layout.Then go to Print Tiles and click on it.Then you will see One Dialog Box Will Appear.In that dialog box go to the Repeat On Top Option.After that select the option showing Title range.Then simply click ok button located at bottom right in dialog box.That's it :)

Apr72020 by excelhelp13No Comments

SUM AVERAGE MAX & MIN Function In Excel.

FORMULAS, Maths

For using these 4 function in excel and to know the actual purpose of all those function. Please follow the step mentioned below. SUM () function add all the numeric value available in different range. SUM() function only need the reference range For Example:- =SUM(B2:C7)AVERAGE () function add all the numeric value available in different range and divide it by the counting the cell with numeric value available. Suppose there are 4 cell and its total is 100 then Average () function will divide 100 by 4 100/4 = 25 is the average. For Example:- =AVERAGE(B2:C7)MAX() function find the bigger number from the given range or a cell reference. Suppose there are 4 cell and its contain 10, 20, 30, 40, then MAX() function will give you the 40 as a max number. For Example:- =MAX(B2:C7)MIN() functio...

Apr62020 by excelhelp13No Comments

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

FORMULAS, Logical, Text

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 box...

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 :