excel help Archives - Page 5 of 9

Dec22020 by excelhelp13No Comments

How To Copy And Paste Data Using Button In VBA

VBA, VBA Range

INTRODUCTION: Range Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples "A1:A5" or "A1" Etc... Open Excel File Click Alt + F11One Dialog Screen Will Get OpenClick Insert And Add ModuleCreate Sub ProcedureMake Sure The Procedure Name Start With String And Without Space.Write The Code In Between Sub And Sub End Click Run ButtonBy Clicking The Range Data Will Get Select And All Data Will Be Copied And Pasted Automatically On Another Sheet.For Copy The Data Use Range("a1:a5").Copy Sheets(2).Range("a1")To Cut And Paste Data Use Range("a1:a6").Cut Sheets(2).Range("b1")To Automate The Process Using Button Please Insert A Button Right Click And Assign Related Procedure / Macro To A Button.Any Sub Procedure / Macro Can Be Assign To A Button Like This Exampl...

Dec22020 by excelhelp13No Comments

How To Copy And Paste Data In Different Sheet With VBA

VBA, VBA Range

INTRODUCTION: Range Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples "A1:A5" or "A1" Etc... Open Excel File Click Alt + F11One Dialog Screen Will Get OpenClick Insert And Add ModuleCreate Sub ProcedureMake Sure The Procedure Name Start With String And Without Space.Write The Code In Between Sub And Sub End Click Run ButtonBy Clicking The Range Data Will Get Select And All Data Will Be Copied And Pasted Automatically On Another Sheet.To Copy The Data Use Range("a1:a5").Copy Sheets(2).Range("a1")To Cut And Paste Data Use Range("a1:a6").Cut Sheets(2).Range("b1")That's It :)Please Check The Images And Video For More Info.

Dec22020 by excelhelp13No Comments

How To Copy And Paste Selected Range Data In VBA

VBA, VBA Range

INTRODUCTION: Range Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples "A1:A5" or "A1" Etc... Open Excel File Click Alt + F11One Dialog Screen Will Get OpenClick Insert And Add ModuleCreate Sub ProcedureMake Sure The Procedure Name Start With String And Without Space.Write The Code In Between Sub And Sub End Click Run ButtonBy Clicking - All Range Data Will Get Copied And Pasted Automatically To Destination Range.To Copy The Content Use Range("a1:a5").Copy Range("b5:b10")For Cut And Paste Data Use Range("a1:a5").Cut Range("b5:b10")That's It :)Please Check The Images And Video For More Info.

Dec22020 by excelhelp13No Comments

How To Clear Selected Range Data With VBA

VBA, VBA Range

INTRODUCTION: Range Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples "A1:A5" or "A1" Etc... Open Excel File Click Alt + F11One Dialog Screen Will Get OpenClick Insert And Add ModuleCreate Sub ProcedureMake Sure The Procedure Name Start With String And Without Space.Write The Code In Between Sub And Sub End Click Run ButtonBy Clicking The Range Data Will Get Select And All Data Will Be Deleted Automatically.For Only Content Deletion Use Range("a1:a5").ClearContentsTo Clear All Like Formatting - Data -Etc. Use Range("a1:a5").ClearThat's It :)Please Check The Images And Video For More Info.

Dec12020 by excelhelp13No Comments

How To Populate Range With VBA

VBA, VBA Range

INTRODUCTION: Range Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples "A1:A5" or "A1" Etc... Open Excel File Click Alt + F11One Dialog Screen Will Get OpenClick Insert And Add ModuleCreate Sub ProcedureMake Sure The Procedure Name Start With String And Without Space.Write The Code In Between Sub And Sub End Click Run ButtonBy Clicking - The Range Will Be Populated Automatically With The Sentence "My First Code". You Can Have Any Words Or Sentence Or Numbers.That's It :)Please Check The Images And Video For More Info.

Dec12020 by excelhelp13No Comments

How To Select RangeWith VBA

VBA, VBA Range

INTRODUCTION: Range Is Used To Define The Start And End Point For A Particular Selection Of Cells. Examples "A1:A5" or "A1" Etc... Open Excel File Click Alt + F11One Dialog Screen Will Get OpenClick Insert And Add ModuleCreate Sub ProcedureMake Sure The Procedure Name Start With String And Without Space.Write The Code In Between Sub And Sub End Click Run ButtonBy Clicking The Range Will Be Select Automatically.That's It :)Please Check The Images And Video For More Info.

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 :)

Apr152020 by excelhelp13No Comments

CONDITIONAL FORMATTING With Formula In Excel

NEW RULE

How to use and apply the Conditional Formatting rules by using custom formulas please follow the step shown below and example in image and video. NOTE:- Conditional Formatting Rules Always Work On The Basis Of True & False Only.Whether The Condition Should Be True Or Condition Should Be False.First Create A Formula On The Base Of Your Requirement In Excel Sheet.Once Created Check The Result It Should Be Either True Or False.Now Select The Range Of Your Data.GO To Home TabThen Go To Conditional Formatting.Then Click & Select New Rule.One Dialog Box Will Pop-UpThen Select Use A Formula - Last In Option List.Type The Formula In Rule Description Or Paste The Formula You Already Created In Excel Sheet.Once Its Done Select The Formatting Option According To Your Requirement As Show...

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