Skip to main content

KNOWUREXCEL

Find Latest Excel formulas and Excel tricks and Excel VBA Codes to simply your work.

Search This Blog

Home
Macros
Excel Formulas
Conditional Formatting
Excel Tricks

Showing posts with the label Excel Formulas

Posts

Sum Values in Excel Sheet | Sum Formula in Excel

on July 10, 2018

+

Get link
Facebook
X
Pinterest
Email
Other Apps

How to sum values in Excel Sheet | Sum values in Excel Sheet

on July 10, 2018

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Countif formula in excel | How countif formula works in excel | Countif function

on December 29, 2017

+

Get link
Facebook
X
Pinterest
Email
Other Apps

vlookup formula in Excel | vlookup function in excel | vlookup formula in excel with examples | vlookup syntax

on December 26, 2017

+

Get link
Facebook
X
Pinterest
Email
Other Apps

formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas

on December 22, 2017

+

Get link
Facebook
X
Pinterest
Email
Other Apps

Archive

July 20182
January 20185
December 20179

Labels

Conditional Formatting
Excel Formulas
Excel Tricks
Excel VBA

Popular Posts

VBA Code to display Message when click on button

VBA Code to display Message when click on button Step 1: Open Excel Sheet and go to the Insert tab. Step 2: Insert one of the by taking shape in Insert Shapes Box. Step 3: After inserting shape right click on the shape and go to assign macro. Step 4: New Module window openes and insert the VBA Code shown below and save.(before save you must save your sheet as Macro - Enabled sheet) Step 5: Change the code as per your requirement as shown below. Step 6: Now click on the shape and you can see message when you click. VBA Code: Sub RoundedRectangle1_Click() MsgBox "Hello I Am Rahul" End Sub Changes to be made at: MsgBox " "

formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas

formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas To find sum of total numbers in a column : Example : If in an excel sheet numbers starts from Column A and Row 1 (i.e. A1) and numbers ends in Column and Row6 (i.e. A6) which is shown in below Picture: formula to sum numbers in a column in excel | how to sum total of numbers in a column | Excel Formulas The Insert the formula =sum(A1:A6) in cell A7 which is shown empty in the picture and then press enter. You will get the result in cell A7. The other method to sum is, drag the cells from A1 to A7 and press Alt + = and you will get the result in A7 cell.

How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel

How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel VBA Code to Create Shortcut Key to Paste Special in Excel: Sub PasteVal() Selection.PasteSpecial Paste:=xlValues End Sub Open Excel Sheet Press Alt + F11 Key Go to the Insert Menu and Click on new Module in New Module copy and paste the above VBA code and save. Now Press Alt + F8 and new windows will open which is shown in below figure, in that windows you will ask to enter a key to create shortcut for Paste Special Enter a letter in the box. How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel How to Create Shortcut Key to Paste Special in Excel | VBA Code to Create Shortcut Key to Paste Special in Excel

How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel

How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Step1: Select the range of cells in excel sheet and click on conditional formatting in excel and select new rule which is shown in figure. How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Step 2: A window will open when u click on new rule, in that new rule windows select "format only cells that contain". In below edit rule description, select " Blanks" How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Then click on format and a new windows will open Step 3: Go to fill in format window ...

VBA Code to delete hidden rows in Excel Sheet | Macro to delete hidden rows in Excel Sheet

VBA Code to delete hidden rows in Excel Sheet | Macro to delete hidden rows in Excel Sheet VBA Code: Open Excel sheet and Press Alt F11 Click on Insert Button and Insert new module Paste the below code and save. Before saving the code you must enable the sheet as Macro Enabled Sheet. Sub deletehidden() For lp = 256 To 1 Step -1 If Columns(lp).EntireColumn.Hidden = True Then Columns(lp).EntireColumn.Delete Else Next For lp = 65536 To 1 Step -1 If Rows(lp).EntireRow.Hidden = True Then Rows(lp).EntireRow.Delete Else Next End Sub here in the above code change the range of rows i.e. For Lp = 65536 according to your requirement.

VBA Code to Sum Numbers and Text in the same cell in excel

Step 1: Open Excel Sheet with values and numbers in a same cells. Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window. Step 3: Go to Insert Menu and select Module. Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet) Step 5: Go to the Data sheet, at last cell where you want calculate use the formula: =numb(select cells) and press enter. Function Numb(RNG As Range) As String Dim TotalItems As Integer Dim rCell As Range Dim TSum As Long Dim TStr As String TSum = 0 TVar = "" For Each rCell In RNG.Cells If rCell.Value <> "" Then If InStr(rCell.Value, " ") Then arrSplit = Split(rCell.Value, " ") ...

vlookup formula in Excel | vlookup function in excel | vlookup formula in excel with examples | vlookup syntax

vlookup formula in Excel | vlookup function in excel | vlookup formula in excel with examples | vlookup syntax Vlookup Formula in excel finds the data related to the input value. General formula for Vlookup is Vlookup(Lookupvalue, Table Array, Col index, [range lookup]) Example : To find the lookup value first save the reference table array in sheet. (shown in Picture 1). Next go the cell where you want to look up the values . Type the above formula in cell B1. In the formula A1 = cell where you want to lookup value AA1:AB4 = Table which is used as reference 2 = in the table which value to find. Here you are finding the second value 0 = finding the exact value. (if want to find approximately type 1 true in place of zero) Type the values in cell A1 and you will get the results in B1 which is shown in Picture 3. formula to sum numbers...

VBA Code to assign sequence serial numbers to cells in filtered excel sheet | Macro to assign sequence serial numbers to cells in filtered excel sheet

VBA Code to assign sequence serial numbers to cells in filtered excel sheet | Macro to assign sequence serial numbers to cells in filtered excel sheet Step 1: Open Filtered Data Excel Sheet. Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window. Step 3: Go to Insert Menu and select Module. Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet) Step 5: Go to the Filtered Data sheet, select cells where you want to give serial sequence numbers and go to Developer Menu and click on macro. You ill see Find Value Macro Macro, click and run the macro. VBA Code: Sub FillValue() 'updateby Extendoffice 20151228 Dim xRg As Range Dim xCell As Range Dim xTxt As String Dim xVal As Long Dim I As Long On Error Resume Next x...

how to write macro to assign sequence serial numbers to cells in filtered excel sheet | VBA Excel

how to write macro to assign sequence serial numbers to cells in filtered excel sheet | VBA Excel Step 1: Open Filtered Data Excel Sheet. Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window. Step 3: Go to Insert Menu and select Module. Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet) Step 5: Go to the Filtered Data sheet, select cells where you want to give serial sequence numbers and go to Developer Menu and click on macro. You ill see Find Value Macro Macro, click and run the macro. VBA Code: Sub FillValue() 'updateby Extendoffice 20151228 Dim xRg As Range Dim xCell As Range Dim xTxt As String Dim xVal As Long Dim I As Long On Error Resume Next xTxt = ActiveWindow.RangeSelection.Address ...

Excel VBA Macros: VBA Code to format rows and columns in filtered excel sheet | Macro to format rows and columns in filtered excel sheet

Excel VBA Macros: VBA Code to format rows and columns in filtered excel sheet | Macro to format rows and columns in filtered excel sheet Step 1: Open Filtered Data Excel Sheet. Step 2: Press Alt + F11 on keyboard and you can see Microsoft Visual basic for applications window. Step 3: Go to Insert Menu and select Module. Step 4: In empty module, copy and paste below VBA code and save. (before save you must save your sheet as Macro - Enabled sheet) Step 5: Go to the Filtered Data sheet, select rows and columns where you want to format cells and go to Developer Menu and click on macro. You ill see Find Value Macro Macro, click and run the macro. VBA Excel Macro Sub Unhide() Dim Col As Range For Each Col In Selection.EntireColumn If Col.Hidden = False Then Col.EntireColumn.AutoFit End If Next Col End Sub VBA Code to format rows and columns in filtered excel sheet...

Powered by Blogger

Knowurexcel