excelhelp13, Author at Excel-Help

Feb12021 by excelhelp13No Comments

DSUM Function To Sum For Specific Criteria

FORMULAS, Maths

SYNTAX : =DSUM ( Database , Field , Criteria) DATABASE: The Whole Table Or A range of Cells With Header.FIELD: The Label Or You Can Say Header Of The Table / RangeCRITERIA: Single Or Multiple Range With Header. NOTE : You Can Have The Field In Separate Column As This Example Have Or You Can Directly Pick Any Of Those Directly From The Table Header By Giving Reference. EXAMPLE 1 : EXAMPLE 2 : EXAMPLE 3 :

Jan312021 by excelhelp13No Comments

Number And Date Format With TEXT Function In Excel

FORMULAS, Text

(1) To Format The NUMBER In A Required Output We Can Use the Text Function To Format It Properly As Needed. Please Check The Image Below. (2) To Format The DATE For A Required Output Like Day - Date - Year We Can Use the Text Function To Format It Properly As Needed. Please Check The Image Below. (3) To Format The DATE In A Required Output We Can Use the Text Function To Format It Properly As Needed. Please Check The Image Below. (4) To Use Formula & Display Amount As 1000 Separator In Format. We Can Use the Text Function To Format It Properly As Needed. Please Check The Image Below.

Jan292021 by excelhelp13No Comments

CHAR Function To Display Symbol In Excel

FORMULAS, Text

DEFINITION: CHAR means CHARACTER. The CHAR function in Excel returns a specific symbol or character based on a code number provided. Which is between 1 to 255. These characters are inbuilt Characters. These characters cannot be typed from keyboard. Please Check The Table Below/ NOTE: Some Character Number May Display The Similar Or Same Symbol As Other. Syntax: =CHAR (number) Formula ResultFormula ResultFormula ResultFormula Result=CHAR(1)=CHAR(65)A=CHAR(129)=CHAR(193)Á=CHAR(2)=CHAR(66)B=CHAR(130)‚=CHAR(194)Â=CHAR(3)=CHAR(67)C=CHAR(131)ƒ=CHAR(195)Ã=CHAR(4)=CHAR(68)D=CHAR(132)„=CHAR(196)Ä=CHAR(5)=CHAR(69)E=CHAR(133)…=CHAR(197)Å=CHAR(6)=CHAR(70)F=CHAR(134)†=CHAR(198)Æ=CHAR(7)=CHAR(71)G=CHAR(135)‡=CHAR(199)Ç=CHAR(8)=CHAR(72)H=CHAR(136)ˆ=CHAR(200)È=C...

Jan292021 by excelhelp13No Comments

Flash Fill To Reverse First And Last Name In Excel

FORMULAS, Text

DEFINITION: Flash Fill automatically detect the data and it senses a pattern available in the particular column. For example, you can use Flash Fill to reverse first and last names from a two different column as we have used in this example, or it also can be used to separate the first and last name from different columns. Please Check The Steps & Images Below. Go To First Cell Of Combine Name Column.Then Type The First Last Name & Then Last Name In First Cell As Shown In Image Below.Then Select The Column Including The Typed Name (Shown In Image Below).Then Go To Home Tab.Then Go To Fill ( Shown In Image Below.) Or Go To Data -And Click Flash Fill. Or Use Short Cut Key Ctrl + E.You Will See All Last Name Transferred To First Name Position & All Last Name As First Name Po...

Jan292021 by excelhelp13No Comments

Flash Fill To Combine First And Last Name In Excel

FORMULAS, Text

DEFINITION: Flash Fill automatically detect the data and it senses a pattern available in the particular column. For example, you can use Flash Fill to combine first and last names from a two different column as we have used in this example, or it also can be used to separate the first and last name from different columns. Please Check The Steps 7 Images Below. Go To First Cell Of Combine Name Column.Then Type The First Name & Last Name In First Cell.Then Select The Column Including The Typed Name (Shown In Image Below).Then Go To Home Tab.Then Go To Fill ( Shown In Image Below.) Or Go To Data -> Flash Fill. Or Use Short Cut Key.Click On Flash Fill.You Will See All First & Last Name Combined & Reflect In Combine Column.NOTE: Flash Fill Can Work For One Column At A Time. ...

Jan292021 by excelhelp13No Comments

Flash Fill To Separate First And Last Name In Excel

FORMULAS, Text

DEFINITION: Flash Fill automatically detect the data and it senses a pattern available in the particular column. For example, you can use Flash Fill to separate first and last names from a single column as we have used in this example, or it also can be used to combine the first and last name from different columns. Please Check The Steps 7 Images Below. Go To First Cell Of First Name Column.Then Type The First Name In First Cell.Then Select The Column Including The Typed Name.Then Go To Home Tab.Then Go To Fill ( Shown In Image Below.) Or Go To Data -> Flash Fill. Or Use Short Cut Key.Click On Flash Fill.You Will See All First Name Get Extracted.Then Go To Last Column And Type The Last Name And Follow The Steps 1 To 6.NOTE: Flash Fill Can Work For One Column At A Time. Flash Fill F...

Jan292021 by excelhelp13No Comments

Highlight Negative And Positive Number In Excel

CONDITIONAL FORMATING, HIGHLIGHTING CELL RULES

To Highlight The Negative And Positive Number Automatically In Excel Please Follow The Step & Images Shown Below. SELECT THE RANGE RIGHT CLICK >> GO TO FORMAT CELLS >> THEN GO TO CUSTOM FORMAT & PASTE THIS UNDER TYPE BOX :- [Green]#,##0.00;[Red]-#,##0.00 COPY-CUSTOM-CODE Download NOW YOU WILL SEE THE GREEN FONT FOR POSITIVE NUMBER & RED FONT FOR NEGATIVE NUMBER.IF NOW YOU CHANGE ANY NUMBER AND IF IT IS NEGATIVE IT WILL CHANGE TO RED AND IF IT IS POSITIVE IT WILL CHANGE TO GREEN.NOTE: THIS ONLY WORK FOR THE SELECTION RANGE. SO PLEASE MAKE SURE TO SELECT THE RANGE PROPERLY OR SELECT THE WHOLE SHEET CELLS BEFORE APPLYING THE CUSTOM FORMAT.

Jan282021 by excelhelp13No Comments

Split Space Separated Text With Formula In Excel

FORMULAS, Text

IMPLEMENT: In Column A We Just Need To Paste The Space Separated Text And Apply The Formula In Column B - C - D - E - F As Mentioned Below. Once The Formula Is Applied You It Will Automatically Separate Text To Columns. Please Check Formula & Notes & Image Below. NOTE: Please Change The Reference If You Want To Apply On Different Range. Also If you Have Comma Separated Text You Can Change Replace The Space With Comma And It Will Work For You. You Can Replace Any Character. NOTE: This Formula Works For 5 Space Separated Text Only. But By Editing The Formula It Is Possible To Increase For More Text. DOWNLOAD SPACE FORMULA FILE HERE ===>Download COLUMN B Formula : =IF(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=0,A2,IFERROR(LEFT(A2,FIND(" ",A2,1)-1),"")) COL...

Jan282021 by excelhelp13No Comments

Convert Single Cell Text line Into Multiple Line

FORMULAS, Text

Go To The Cell That Contain The Overflow Line.Then Select Multiple Row + Column Including The Cell Having Overflow Line As per your Idea That It Will Look Good Or Will Be Fit Accordingly.Then Go To Home Tab.Then Go To Fill. (Shown In The Image Below)Then Click On Justify.Note: The Text Written in The Cell Has Nothing To Do With This Rule. It Is Just For Demonstrate.Excel Version : 2016Please Check The image Below. TO CONVERT BACK AS IT WAS ORIGINAL PLEASE CHECK THE STEP AND IMAGES SHOWN BELOW. Go To The Original Cell That Was Having Overflow Line Before.Then Select Range Row + Columns Line As Per Your RequirementsThen Go To Home Tab.Then Go To Fill. (Shown In The Image Below)Then Click On Justify.Please Check The image Below.

Jan282021 by excelhelp13No Comments

Split Comma Separated Text With Formula In Excel

FORMULAS, Text

IMPLEMENT: In Column A We Just Need To Paste The Comma Separated Text And Apply The Formula In Column B - C - D - E - F As Mentioned Below. Once The Formula Is Applied You It Will Automatically Separate Text To Columns. Please Check Formula & Notes & Image Below. NOTE: Please Change The Reference If You Want To Apply On Different Range. Also If you Have Space Separated Text You Can Change Replace The Comma With Space And It Will Work For You. You Can Replace Any Character. NOTE: This Formula Works For 5 Comma Separated Text Only. But By Editing The Formula It Is Possible To Increase For More Text. DOWNLOAD FORMULA FILE HERE ===>Download TO GET ALL SEPARATED TEXT IN SINGLE CELL FORMULA: COPY THE FORMULA : CHECK AT LASTDownload