excel help in excel Archives

Mar302023 by excelhelp13No Comments

Multiple IF Condition With IFS Function In Excel

FORMULAS, Logical

IFS function is used when we want to test / check multiple criteria for the same cell value as we have used in the below mentioned example. This condition is also known as Nested IF condition. But The IFS condition make it much easier to use in comparison to Normal Nested IF condition. Note : In IFS function if the result is not TRUE or we can say none of the passed condition/criteria matched it returns #N/A error. Syntax : IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2],[logical_test3, value_if_true3]… ) FORMULA =IFS(B4<35,"FAIL",B4<=40,"D",B4<50,"C",B4<=60,"B",B4>70,"A")

Nov302020 by excelhelp13No Comments

Rank Function In Excel

Maths, Statistical

Open Excel File.Select The Cell Where you Want To The Result To Get Populated.Then Add This Formula In Particular Cell =RANK(10,A1:A10)There Is Third Optional Parameter (Ascending / Descending ) =RANK(10,A1:A10, 1 'For Ascending Order') or =RANK(10,A1:A10, 0 For Descending Order)Make Sure To Lock The Range By $ Sign.Descending Order Is By Default Set Optional Parameter.That's it :)

Apr132020 by excelhelp13No Comments

FIND SMALLEST Number With Position In Excel

Statistical

First Select The Cell Where You Want The Result.Then Apply The Formula And Select The Array / Range And Define The Position. Once Applied It Will Check In The Array And Give You The Result By Finding The Position Of The Smallest Number.That's It :)

Apr132020 by excelhelp13No Comments

DEPENDENT Drop Down List In Excel

Lookup & Reference

TO create a dependent drop down list please follow the step shown below and example provided with image and video under it. First Select The Reference Cell Where you Want To Have Drop Down List.Then GO To Data Tab.Then Go To Data Validation.One Dialog Box Will Pop-Up.Under That Select The List From Drop Down List.Then Under That In Source Tab Just Provide The Reference Range And Click Ok.Once Your Drop Down List Created Move To Next Column Cell.Then Again Go To Data Validation And Paste This Formula =OFFSET($D$1,1,MATCH($A2,$D$1:$F$1,0)-1,10,1) And Click Ok.OFFSET FUNCTION:- Offset Function Return A Reference To A Range For The Given Number Of Rows And Columns For The Given Reference.MATCH FUNCTION:- Match Function heck The Position Of A Particular Reference In A Given Array.NOTE:- If ...

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

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() functi...

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