excelhelp13, Author at Excel-Help

Jan242021 by excelhelp13No Comments

Conditional Highlight Negative And Positive Cell In Excel

CONDITIONAL FORMATING, HIGHLIGHTING CELL RULES

To Highlight Positive Or Negative Number We Can Use Conditional Formatting. Please Follow The Step Shown Below. First Select The Range Were You Want To Apply The Conditional Formatting. Example : A2:F10Then Go To Home Tab.Click On Conditional Formatting.Select Highlight Cell Rules.To Highlight The Positive Number Click On Greater Than.To Highlight The Negative Number Click On Less Than.One Dialog Box Will Pop-Up To Enter The Value As Per Need,For Positive Number Add 0. ( In Greater Than. )For Negative Number Add 0 ( In Less Than. )For Custom Format Select Custom From Drop Down List On Right Hand Side.You Can Apply Many Formatting Like Font Color - Border - Cell Color Etc..Once Done Click Ok.Now you Must See the Applied formatting On A The Cells.Now If you Change The Number The Formatti...

Jan242021 by excelhelp13No Comments

COUNTIF By Cell Background Color In Excel

FORMULAS, Maths, Most Used Formulas

To COUNTIF By Color Please Follow The Step Shown Below With Images. Go To Formula Tab. Then Click On Define Name. You Will See One Dialog Box Get Pop-Up. In Name Bar Please Add Any Name Without Space Ex: CELLCOLOR. In Scope Dropdown Please Select According To Your Requirement. In Comment Box Add Any Comment To Display On Cell Selection. In Refer To Bar Add This Formula =GET.CELL(38,Sheet12!$A2) Once This Done Create A Separate Column Called Color Code. Apply Formula =CELLCOLOR or Whatever Name you Have Given In Name Bar. Once You Have The Color Code You Can Apply Formula On It As Shown In Image Below. NOTE: Change Reference According To Your Range & Sheet. This Formula Will Refer To Single Cell In A Sequence Manner. Click Ok. Please Check Imag...

Jan242021 by excelhelp13No Comments

SUMIF By Cell Background Color In Excel

FORMULAS, Maths, Most Used Formulas

To SUMIF By Color Please Follow The Step Shown Below With Images. Go To Formula Tab Bar. Click On Define Name. You Will See One Dialog Box Get Pop-Up. In Name Bar Please Add Any Name Without Space Ex: CELLCOLOR. In Scope Dropdown Please Select According To Your Requirement. In Comment Box Add Any Comment To Display Upon Cell Selection. In The Refer To Bar Box Add The Formula =GET.CELL(38,Sheet12!$A2) Once Done Then Create A Separate Column Heading Called Color Code. Apply Formula =CELLCOLOR or Whatever Name You Have Given In Name Bar. Once You Have The Color Code You Can Apply Any Related Formula On It. NOTE: Please Change Reference According To Your Range & Sheet. This Formula Will Refer To Single Cell In A Sequence Manner. Click Ok. Please Che...

Jan232021 by excelhelp13No Comments

Extract Only Date From Date & Time In Excel

FORMULAS, Text

DEFINITION: The TEXT function allow you to change the way a number display by applying formatting to it with format rule. It is useful in lots of situations where you need to display numbers in a more readable format accordingly, or you want to combine numbers with text or symbols. Specially it useful to extract or format a date according to your need.

Jan212021 by excelhelp13No Comments

MID Plus FIND Function To Extract Specific Text

FORMULAS, Text

MID FUNCTION: MID function is a text function which is used to find out strings from any mid part of the sentence in excel. Mid function require the cell reference from which the text need to be extract, start number from which character need to be start , and number of character which need to be extract. FIND FUNCTION : The FIND Function is used to check or return the position of a provided Cell reference text or a character. FIND function is a case sensitive. NOTE: MID function count the space too. So in the result it will include a space too. SYNTAX=MID(Reference Cell, Start Number / Position, Number Of Character to Extract IMPLEMENT: In this example we have used FIND Function Within MID Function to make it dynamic. So whenever w...

Jan162021 by excelhelp13No Comments

IF With OR Function To Get Result From Multiple Criteria

FORMULAS, Logical

To get the specific output by matching the multiple criteria we can use Logical IF With And Function. The Logical IF used to populate the result if the OR Function result in True or False. The OR Function check the two matching criteria provided to evaluate the condition and result in TRUE or FALSE. Here we are checking that if the student name is Juan or the subject is English the result should be "Selected" else "Not Eligible". We can also provide the reference in place of "Selected" And "Not Eligible". Please check the image below. FORMULA : =IF(OR(A3="Juan",B3="English"),"Selected","Not Eleigible")FORMULA : =IF(OR(A3="Juan",B3="English"), A4,"Not Eleigible")

Jan152021 by excelhelp13No Comments

IF With And Function To Get Result From Multiple Criteria

FORMULAS, Logical

To get the specific output by matching the multiple criteria we can use Logical IF With And Function. The Logical IF used to populate the result if the AND function result in True or False. The AND function check the two matching criteria provided to evaluate the condition and result in TRUE or FALSE. Here we are checking that if the student name is Juan and the subject is English the result should be "Selected" else "Not Eligible". We can also provide the reference in place of "Selected" And "Not Eligible". Please check the image below. FORMULA : =IF(AND(A3="Juan",B3="English"),"Selected","Not Eleigible")FORMULA : =IF(AND(A3="Juan",B3="English"), A4,"Not Eleigible")

Jan42021 by excelhelp13No Comments

Set Page Break In Excel

Menu Bar, Page Layout

DEFINITION: Page Break Is Use To Print A Worksheet With The Exact Number Of Pages That You Want To Get Print From The Entire Worksheet. You Can Adjust The Page Breaks In The Worksheet Before You Print It. Page Breaks Is A Division That Break A Worksheet Into Separate Pages For Printing Them Individually. STEPS TO FOLLOW: There Are Two Types Of Page Breaks: Solid Line (Manual Page Break) To Adjust Where The New Page Begins. Another Is Dashed Lines Page Breaks That Inserted Automatically By Excel. You Can Set And Adjust The Columns And Rows According To Your Requirement In View Tab > Page Break View > Adjust Blue Line By Dragging It. INSERT PAGE BREAK : To Insert A Page Break, Follow The Steps Below. Select The Row From Where You Want To Start The New Pa...

Jan32021 by excelhelp13No Comments

Set Background Image From Local Or External Source

Menu Bar, Page Layout

DEFINITION: To Make Your Sheet Look Better Visually We Can Set A Colorful Background By Inserting A Background Images On A Worksheet. You Can Add it Through Local Files Stored on your PC or From External Source Like Bing image Search Or From One Drive If your Version Of Excel Support It. NOTE: In Excel You Can Use A Background Picture For Display Purposes Only. A Sheet Background Image Is Not Printable. STEPS TO FOLLOW: Go To Page Layout > BackgroundInsert Pictures Dialog Box Will Get Appear.Click On The The Browse Button To Add Background Image From Local File And Other Option For External File. Delete Background Image: STEPS: To Delete Background, Go To Page Layout > Click Delete Background.

Jan32021 by excelhelp13No Comments

Print Selected Area Only By Set Print Area In Excel

Menu Bar, Page Layout

DEFINITION : In A Situation When You Only Want To Print A Specific Selection Data Range From Entire Worksheet, You Can Use A Print Area To Set Or Print The Selection Data Range Only, You Can Select Single Or Multiple Data Range Using The Print Area. You Can Set Multiple Print Areas In A Worksheet. Each Print Area Field Will Be Print On A Separate Page. When You Print A Worksheet By Applying The Selection Print Area Then Only The Print Area Will Get Printed And Rest Of The Page Will Remain Blank. You Can Add More Cells Or Range To Expand The Print Area Selection As Needed, You Can Also Clear The Print Area To Print The Entire Worksheet. SET PRINT AREA: STEPS: Select The Worksheet From Which You Want To Print. Select The Range Of Data That You Want To Print ...