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 from July, 2018

Posts

Sum Values in Excel Sheet | Sum Formula in Excel

on July 10, 2018

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

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

on July 10, 2018

+

Get link
Facebook
Twitter
Pinterest
Email
Other Apps

Archive

July 20182
January 20185
December 20179

Labels

Conditional Formatting
Excel Formulas
Excel Tricks
Excel VBA

Popular Posts

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.

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

Countif formula in excel | How Countif formula works in excel | Countif function Countif formula in excel counts the criteria in the data sheet Example: In Excel Sheet, if Column A and Column B contains repeated data, if we want to count one single name in column we use countif formula which counts only given criteria. (show in Picture) Countif formula in excel | How countif formula works in excel | Countif function Here is the formula =countif(range,criteria) here in the formula range = select from which data sheet you want to find the count of particular name. in formula criteria = name which you want to find. In the above picture range = (A:B) columns criteria = (Month or numbers in the columns) Countif formula in excel | How countif formula works in excel | Countif function in the above picture the formula is =COUNTIF(A1:B24,"Jan") the above formula finds how many times january repe

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 and select color and press ok How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel Now you can see th

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 in a column in excel | how to sum total of numbers in a co

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

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

How to Limit Length of the Text in Excel Cell | Limit Length of the Text in Excel Cell

How to Limit Length of the Text in Excel Cell | Limit Length of the Text in Excel Cell To Limit Text Length in the Excel, we should use data validation. Step 1: Select the range of the cells where you want to limit the Text length. Step 2: Now Go to the Data Tab and then click on Data Validation Tab which is shown in below picture. How to Limit Length of the Text in Excel Cell | Limit Length of the Text in Excel Cell Step 3: In Validation Tab, set the Validation Criteria as Text length and Set data and Length of Characters which is shown in below Picture. How to Limit Length of the Text in Excel Cell | Limit Length of the Text in Excel Cell Now Type the Text in selected range of cells. How to fill black cells with color using conditional formatting in excel | Conditional Formatting in Excel

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

VBA Code to format rows and columns in filtered excel sheet | Macro to format rows and columns in filtered excel sheet Steps to Execute VBA Code in 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 Code | 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 assign sequence serial numbers to cells in filtered excel sheet | Mac

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 Set xRg = Application.InputBox("Please select the data range", "

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.

Powered by Blogger

Knowurexcel