This post will teach you how to combine duplicate rows and sum the corresponding values or calculate numbers in specific column in excel. And how to merge duplicate rows and then sum the values with VBA macro in Excel. How to combine rows with same name and sum the value.
1. Combine Duplicate Rows and Sum the Values with Consolidate Feature
You can use the Consolidate feature to combine duplicate rows and then sum the values in excel, let’s see the below steps:
Step1: select a cell that you want to display the result combined
Step2: on the DATA tab, click Consolidate command under Data Tools group.
Step3: the Consolidate window will appear.
Step4:choose Sum from Function: drop-down list, select the range that you want to combine, then click Add button to add it in the All references box. Select Top row and Left column checkbox. Then click OK button.
Step5: you will see that all duplicate rows are combined and the corresponding values summed.
2. Combine Duplicate Rows and Sum the Values with VBA code
You can also combine duplicate rows and sum the values with VBA code in Excel. Just do the following:
Step1: click on “Visual Basic” command under DEVELOPER Tab.
Step3: In the VBE, click on Insert from the top menu and select Module to create a new module.
Step4: paste the below VBA code into the code window. Then clicking “Save” button.
Sub CombineDuplicateRowsAndSum_ExcelHow()
Dim R As Range, OutputRange As Range
Set R = Application.Selection
Set R = Application.InputBox("Select one range:", "CombineDuplicateRowsAndSum", R.Address, Type:=8)
' Add prompt to select range for output
Set OutputRange = Application.InputBox("Select range for output:", "CombineDuplicateRowsAndSum", Type:=8)
Dim Dic As Object
Set Dic = CreateObject("Scripting.Dictionary")
Dim arr As Variant
arr = R.Value
Dim i As Long
For i = 1 To UBound(arr, 1)
Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
OutputRange.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
OutputRange.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub
Step5: back to the current worksheet, then select the CombineDuplicateRowsAndSum_ExcelHow macro and run the above excel macro.
Step6: Follow the prompts to select the source range and output range for the processed data. Click OK to start processing the data.
Step7: The processed data will be displayed in the output range you specified.
3. Video: Combine Duplicate Rows and Sum the Values
This video will demonstrate how to combine duplicate rows and sum the values in Excel using both the Consolidate feature and VBA code.
This post will guide you how to sort text string by second or third character in a Column in Excel. You may be want to sort a string in a Cell by the Second or last character in excel, how to quickly do it? You can use the Excel Formula or Text to Column feature to solve this problem. The below will give you the detailed description.
1. Video: Sort by Second Character in a Column
In the below video tutorial, you can learn how to sort by second or third character in a column by using a formula with the RIGHT function or by splitting the text into columns with the Text to Column feature.
2. Excel sort by second character with Formula
To sort test string in a cell by its second character, you can use a combination of the RIGHT function and the LEN function to build a new Excel formula, you can refer to the following generic formula.
This formula will remove the first character of the text string in Cell, then you can use the sort feature in Microsoft Excel to sort this column, and then the original column also will be sorted. Just do it following:
Assuming that you want to sort the range A1:A4 in Column A by second character.
Step1: select the cell B1 next to the cell A1 contains that you want to sort
Step2: type the following formula in the formula box of Cell B1, then press Enter key.
Step3:select Cell B1, then drag the AutoFill Handle down to the cell B2:B4 to apply this formula. You will see that the first character will be deleted.
Step4: select the range B1:B4, on the DATA tab, click sort A to Z command under Sort & Filter group.
Step5: choose Expand the selection radio button in the Sort Warning window. Then click Sort button.
Step6: you will see that the column A and B are sorted by the second character.
Step7: you can remove the column B now.
3. Excel sort by second character with Text to Columns
If the text strings are joined by the delimited character, and you want sort text string by the middle characters, then you can use the Text to Columns feature to achieve the result. Or you can use the MID function to extract the middle characters, then use the Sort feature in Excel to sort them.
Just refer to the following steps:
Step1: select the range of cells contain the text string that you want to sort.
Step2: on the DATA tab, click Text to Column command, the Convert Text to Columns Wizard window will appear.
Step3:choose Delimited radio button in the first step window, click Next button
Step4: type the delimited character which is used to join the text string in Cell, click Next button
Step5: choose the destination cell reference, then click Finish button.
Step6: you will see that the text string is split into three columns, and then you can select the column that contain the middle characters, click Sort A to Z command under DATA tab, the columns are sorted by the middle characters in each cell.
This method is only available for the text string that joined by the specific character or delimiter. So if the text string in Cell do not have any common delimiter, you can use the MID function to extract the middle characters, then following the Step5 to sort the text string. You can use the following generic formula:
Sort Names by Middle Name in Excel Assuming that you have a list of names in your worksheet and you would like to alphabetize by middle name. You can create an excel formula based on the IF function, the ISERR function, the FIND function, and the MID function.…
Split Multiple Lines from a Cell into Rows If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
Extract nth word from text string If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function..…
Get last word from text string If you want to get the last word from a text string, you can create an excel formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function..…
Extract word that starting with a specific character Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…
5. Related Functions
Excel RIGHT function The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
Excel LEN function The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
Excel FIND function The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
Excel MID function The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
This post will teach you how to quickly generate a random password with Formula in Excel. You can combine a series of worksheet functions to create a complex formula to generate a random password in Microsoft Excel. How do I create an random code generator in Excel.
1. Video: Generate Random Passwords or Letters in Excel
This tutorial video provides step-by-step instructions on how to use Excel’s built-in functions to generate random passwords or letters in Excel, making it easy to create secure and unique passwords for a variety of purposes.
Step3: press Enter key to apply this formula. You will see that it will generate an eight characters password.
3. Random Letter Generator in Excel
If you want to create a series of random letters or strings in Microsoft Excel Spreadsheet. and you can use built-in functions such as the CHAR function, which can convert a number to its corresponding ASCII character.
For example, to generate a 5-character string of uppercase letters, and you can use the following formula based on the CHAR function and the RANDBETWEEN function.
This formula generates a random uppercase letter for each position in the string. By copying and pasting the formula into multiple cells, users can generate a series of unique random strings for various purposes, such as generating temporary passwords or unique IDs.
4. Generate Random Uppercase Letter
If you want to generate a random password only contain alpha characters (random uppercase letter string), you can use the following formula:
Generating random passwords in Excel can be a quick and convenient way to create secure and unique passwords for various purposes. By using a combination of built-in functions, users can generate passwords of different lengths and complexity levels, as well as customize them to include different character sets.
9. Related Functions
Excel CHAR function The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
Excel RANDBETWEEN Function The Excel RANDBETWEEN function returns a random integer number that is between the numbers you specify.The syntax of the RANDBETWEEN function is as below:= RANDBETWEEN (bottom,top)….
Still cannot understand the functions and formulas in Excel or google sheets and trouble? The most basic excel or google sheets function parameters introduction, newcomers must see!
ABSTRACT
Our website introduces many basic uses of functions in Excel or google sheets as well as common examples from everyday life and work. We have also uploaded many articles on how to use functions to create formulas to solve common problems.
We found that, in fact, many people do not know much about Excel or google sheets functions, or a half-understanding, by reading our articles may solve the current problem, but they do not know how to use Excel or google sheets functions to create formulas when solving the problem, with what function? Which similar function to use? How to nest the formula? What is the input value? Why does it return an error?
We will have the above problems because we usually only think about how to quickly apply these functions as well as formulas to solve problems in a timely manner, and copy the formula directly to use it without thinking about how the formula works.
We will face a lot of problems, so before we learn Excel or google sheets functions and formulas, we must learn some basic concepts about functions and formulas, or a few basic elements that make up a function/formula. Then after understanding these basic concepts, you will really see how a formula is made up and what role each part plays in the formula.
– Arguments
Referring to the syntax structure of the function, each item in the function brackets is called an argument to the function. These arguments can be constants, cell reference, range reference, arrays, nested functions, etc. Next, we will introduce them according to different categories of arguments.
In the example below, number 1 and 2 are arguments to SUM function.
– Constants
Constant is an immutable value or data item. A constant can be a specified value, array, or character/string. In a normal Excel or google sheets formula, in addition to the value itself, you can also enter a cell reference or range reference that contains the value(s).
Note: Neither the formula nor the result calculated by the formula is a constant, because whenever the argument of the formula changes, it will affect the formula calculation and change the result.
In the example below, arguments 1 and 2 are entered manually, they are constants.
– Cell Reference & Range Reference
Cell reference and cell range reference are the most common arguments in Excel or google sheets functions. The purpose of the reference is to define the cell or range in the worksheet and specify the location of the data used by the formula or function, so that the function can easily use the data in various parts of the worksheet.
The values in cell references and range references can be static values, but they can also be dynamic values. The actual return value according to the change in the value of cell reference or range reference.
Cell Reference
In the example below, A2 and B2 are cell references. Number “1” is saved in cell reference A2, “2” is saved in cell B2. When selecting A2 in a function, that means number “1” in A2 is referenced.
A cell reference is delivered to function as its argument.
Range Reference
The entire selected multiple cells can be seen a range reference. In Excel or google sheets, for the common used lookup function VLOOKUP, its lookup range argument is usually entered with a range reference.
In the example below, A2:B2 is a range reference, it is one argument to SUM function not two arguments. This range reference contains an array actually. We will introduce array in the following paragraph.
When entering or typing function’s arguments, we can click on a single cell, or drag and drop to select multiple cells, in this way we can directly enter a cell reference or range reference.
We can also pre-define a name for a range reference in advance, in this way, we can enter this name directly as function’s argument when applying a function.
STEP 1: Name a range reference:
Formulas->Define Name, enter “Numbers” for selected range:
OR
Select A1:B2->Enter “Numbers” in Name Box:
STEP 2: Entering “Numbers” as argument.
As functions or formulas are often copied directly to other cells through drag-and-drop operations, usually, the reference will change automatically according to the cell location where the formula is located.
If you do not want to change the reference, we must add a dollar sign in front of the row or column index. For these two cases, we divided the reference into relative reference and absolute reference.
Relative Reference:
When dragging down the formula, A1:C1 is automatically updated to A2:C2.
Absolute Reference:
When dragging down the formula, $A$1:$C$1 is not changed. Because we add $ before row and column indexes, so range reference is locked.
Mixed Reference:
When dragging down the formula, for range reference $A$1:C1, $A$1 is locked, but C1 is updated from C1 to C2 as formula is copied down to D2.
– Array
Array, equivalent to our mathematical matrix, a matrix contains multiple elements, elements and different combinations of elements constitute a matrix of different dimensions.
In EXCLE workbook, the selected range can be expressed as an array, expressed as an N row * M column area, N and M are not 0.
The array formula is relative to the ordinary function formula; it has two characteristics.
1) Elements of the array are referenced by brackets {} and separated by commas or semicolons.
2) After entering an array formula, you must press Ctrl+Shift+Enter to load the result.
Example 1:
F1=SUM(A1:D1); A1:D1={1,2,3,4}; If all elements in on array are in one row, values are separated by comma.
Example 2:
F1=SUM(A1:A4); A1:A4={1;2;3;4}; If all elements in on array are in one column, values are separated by semicolon.
Example 3:
F1=SUM(A1:B2); A1:B2={1,2;3,4}; The array is a multidimensional array.
– Logical Values
There are TRUE and FALSE two logical values. In Excel or google sheets, IF function is a logical test function that returns the corresponding branch value based on the two logical values TRUE and FALSE.
Example:
If logical test value is TRUE, IF function returns “Yes” else returns “No”.
– Logical Operators
AND
Syntax: AND(logical1, logical2, …).
Description: Returns TRUE if all arguments are true; and If AND function detects an argument with a value of false, it returns false
Example:
If both conditions “N>3” and “M>5” are true, result is TRUE.
NOT
Syntax: NOT(logical)
Description: Find the opposite of a logical value or a logical expression. If you want to get the opposite of a logical value, you should use the NOT function.
Example:
Normally we can use IF function to create a logical test like =IF(A2>3,”TRUE”,”FALSE”) to return TRUE or FALSE result; Actually you can make it easier, you can also use “=NOT(A2>3)” to return the result of logical test “N<3”.
OR
Syntax: OR(logical1, logical2, …)
Description: Returns TRUE when any of the logical values is true.
Example:
If one of the conditions “N>3” and “M>5” is true, result is TRUE.
Description: IF function performs a logical test which can return different results depending on the TRUE or FALSE of the logical expression.
Example:
Set “Yes” to logical test value of TRUE; Set “No” to logical test value of FALSE.
– Error Values
Sometimes we don’t get the value we want after pressing the Enter key, and formula returns error value instead. These values usually consist of “#error!”.
When we find that an error is returned, we need to understand the meaning of these errors, they can help us to know what the cause of the error is and how to solve the error.
Below is a list of common error values and solutions in Excel or google sheets function applications.
Error: #NAME?
Explanation: The function was entered incorrectly with unrecognized text. For example, the function name is spelled incorrectly, the quoted text is not quoted, and so on.
Solution: Check for spelling errors, quotation marks and brackets
Example1: Function name “SUM” is spelled incorrectly
Example2: Reference “Numbers” is missing
Error: #N/A
Explanation: The lookup range cannot be found when using some lookup functions in Excel workbook (for example, the VLOOKUP function cannot find the input lookup range, or the lookup value cannot be found in the first column of the lookup range).
Solution: Check if the lookup range has been entered incorrectly; make sure the lookup value is in the first column of the lookup range.
Example 1: Lookup ID doesn’t exist in lookup table A2:B6.
Example 2: Lookup value is incorrect; A is not listed in the first column of lookup table A2:B6.
Error: #DIV/0!
Description: Function or formula has a divisor equal to 0 or the divisor cell is empty.
Solution: Change the divisor to a non-zero value.
Example:
Error: #NUM!
Explanation: A non-numeric value has been entered in the numeric argument or is out of Excel’s calculation range.
Solution: Check the argument type and enter a valid type.
Error: #VALUE!
Explanation: Incorrect argument type entered, e.g., a number needs to be entered but text is entered, a value needs to be entered but multiple values are entered, etc. If it is an array formula, forget to press Control+Shift+Enter.
Solution: Check the argument type of the function and enter the correct type.
Example:
Divisor is “a”.
Error: #REF!
Explanation: Cell reference or range reference is invalid. For example, clear value in the cell reference which is used by the formula, then formula reports this error code.
Solution: make sure reference is correct; check if the cell reference or range reference is empty, and if it is, re-enter values into values.
Example:
Lookup table exists in sheet2, but sheet2 is deleted.
Error: #NULL!
Explanation: When multiple ranges are entered, the separator is incorrect; the intersection of multiple ranges is empty.
Solution: Change the separator; make the ranges intersect.
Example:
Separator is incorrect. It should be =SUM(A1:B1,A2:B2).
Resolution
We can move mouse over the warning icon to show the floating indication message.
You can also click the icon to do the following operations.
– Nested Functions
In addition to the arguments listed above, functions can also be used as arguments to a function, this is called nested function. The value of this internal function will be delivered to the external function as an argument value.
Example:
SUM(A2:B2)>3 is an argument (logical test) to IF function.
This article introduces some basic concepts in Excel or google sheets function applications, which are the most basis information for new learners. You can bookmark this article so that you can refer to it when learning functions or building formulas.
This post will guide you how to convert ounces to pounds or grams with formula in Excel. How do I convert pounds to ounces or grams in Excel 2013/2016.
Convert Ounces to Pounds
Assuming that you have a list of data in range B1:B4, in which contain values with Ounces data, and you wish to convert these Ounces to pounds. How to do it. You can use an formula based on the CONVERT function to convert it quickly. Like this:
=CONVERT(B2,"ozm","lbm")
You need to type this formula into a blank cell such as: Cell C2, and press Enter key on your keyboard, and then drag the AutoFill handle down to other cells to apply this formula.
Convert Pounds to Ounces
If you want to convert your range of cells with Pounds data to Ounces, you can use the below formula based on the CONVERT function:
=CONVERT(C2,"lbm","ozm")
You need to type this formula into a blank cell such as: Cell D2, and press Enter key on your keyboard, and then drag the AutoFill handle down to other cells to apply this formula.
Convert Ounces to kg
If you want to convert kg into Ounces for you range B2:B5, just use the following formula:
=CONVERT(B2,"ozm","kg")
You need to type this formula into a blank cell such as: Cell E2, and press Enter key on your keyboard, and then drag the AutoFill handle down to other cells to apply this formula.
This post will guide you how to use the Find & Replace feature to delete or remove all rows based on certain cell value in Microsoft Excel. Or how to delete all rows that contain certain value with VBA code in excel, such as, removing all rows if cell contains 0 or any other value.
Assuming that you have a worksheet and you want to delete rows based on cell vlaue if the value is equal to 0. In another word, if the cell value contain 0, then delete that row contain value 0. The first thing you need to do is that how to find rows that contain certain value in your worksheet. And how to solve this problem? You can use the Find & Replace command or VBA code to achieve the result.
Delete rows based on cell value with Find & Replace feature
To delete rows based on a certain cell value with Find & Replace feature, you can refer to the following steps:
1# Select the range of cells that you want to delete rows based on certain cell value.
2# on the HOME tab, click Find & Replace command under Editing group. Or just press Ctrl +F shortcut to open the Find and Replace box.
3# the Find and Replace dialog box will appear on the screen.
4# type the certain value in the Find what: text box, and then click Find All button
5# you will see that the searched result will appear in the Find and Replace window.
6# select all found values in the Find and Replace window and you will see that all cells contain certain value will be highlighted in your selected range.
7# right-click on the selected cells, and select Delete…menu from the drop-down menu list. Then choose Entire row radio button in the Delete dialog box. Click OK button.
Or you can go to HOME tab, click Delete command. Then click Delete Sheet Rows.
So far, all rows contain the certain value are deleted in your selected range. And if you want to remove the columns base on the certain value, just select Entire column radio button in the step 7.
Delete Rows Based on cell value with Excel VBA Macro (VBA code)
If you want to delete rows that contain the certain value in your worksheet with excel VBA Marco, you can refer to the following steps:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub RemoveRowsBasedValue()
Dim Dr As Range
Set Ip = Application.Selection
Set Ip = Application.InputBox("Select one range that you want to remove rows:", "RemoveRowsBasedValue", Ip.Address, Type:=8)
Ds = Application.InputBox("Please type a text string:", "RemoveRowsBasedValue", Type:=2)
For Each R In Ip
If R.Value = Ds Then
If Dr Is Nothing Then
Set Dr = R
Else
Set Dr = Application.Union(Dr, R)
End If
End If
Next
Dr.EntireRow.Delete
End Sub
5# back to the current worksheet, then run the above excel macro. Click Run button.
6# select a range that you want to delete rows, click OK button. Then type a text string contained in the range of rows to delete.
This post will guide you how to add common prefix or suffix to all cells or the range of cells in your worksheet in Excel. How do I add the same prefix or suffix to a range of cells without any changing the existing numbers or text in the range. How to add prefix or suffix to all cells with Excel VBA Macro.
Assuming that you have a list of data that you want to add common text to the starting position of all cells, of course, you can do it by manually, but it will consume lots of time and it is not a good idea. And you can use the concatenate operator, the CONCATENATE function to create a new Excel formula to add prefix or suffix to the range of cells that you selected. Or you can write a new Excel VBA Macro to achieve the same result.
Method 1: Using Concatenation Operator to add prefix or suffix
If you want to add prefix or same text to the starting of all cells in your range, you can use the Concatenation operator to build a formula, for example, you want to add same text “excelhow” to all cells in range A1:A4, then you can use the following formula:
="excelhow"&A1
Ether this formula in a blank cell, such as: B1, then drag the AutoFill Handle down to other cells to apply this formula.
If you want to add suffix to all cells with concatenation operator, you can use the following formula:
=A1&"excelhow"
You will see that all cells are added the same prefix text in your worksheet.
Method 2: Using concatenation function to add prefix
To add prefix or suffix text or number to the range of cells with concatenation function, you can wirte down the following formula:
=CONCATENATE("excelhow",A1)
or
=CONCATENATE(A1,"excelhow")
Method 3: Using Excel VBA Macro to add prefix
You can also use the following VBA Macro code to add prefix or suffix in your worksheet in Excel. Let’s see the below steps:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub AddPrefixToCell()
Set W = Application.Selection
Set W = Application.InputBox("select one Range that you want to add prefix to each cell:", "AddPrefixToCell", W.Address, Type:=8)
S = Application.InputBox("Please type a prefix character:", "AddPrefixToCell", "", Type:=2)
For Each R In W
R.Value = S & R.Value
Next
End Sub
5# back to the current worksheet, then run the above excel macro. Click Run button.
6# select a range that you want to add prefix, click OK button, then add one prefix string or character. Click OK button.
7# you will see that the prefix is added to all range of cells.
If you want to add suffix to a range of cells with VBA macro in Excel, you can use the following VBA macro.
Sub AddSuffixToCell()
Set W = Application.Selection
Set W = Application.InputBox("select one Range that you want to add suffix to each cell:", "AddSuffixToCell", W.Address, Type:=8)
S = Application.InputBox("Please type a prefix character:", "AddSuffixToCell", "", Type:=2)
For Each R In W
R.Value = R.Value & S
Next
End Sub
Related Functions
Excel Concat function
The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…
This post will guide you how to quickly insert multiple rows or columns in your Microsoft worksheet. You will see that how to add new multiple rows or columns with shortcut or insert feature, or insert copied cells in excel. Or how to quickly insert rows or columns with Excel VBA macro.
Suppose that you want to quickly insert 5 blank rows between row 4 and row5 in your worksheet, and how to do it? May be you can do it by manually, but this is not a good idea, because if you need to insert hundreds of blank rows or columns, it will be tedious and time-consuming.
Of course, there are several ways to quickly insert new multiple rows or column in Excel. Let’s see the below detailed description.
Method 1: Insert multiple rows or columns in Excel using Insert feature
Insert Rows:
If you want to insert 5 rows between Row 3 and Row 4 in your worksheet, then you can do it following steps:
1# you should locate the destination row, it should be Row 4, so select 5 entire rows below from Row 3 using the row number buttons.
2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.
Or you can right-click on the selected rows, then choose the insert option from the pop-up menu list.
You will see that 5 rows are inserted after Row 3 in your worksheet.
Insert Columns:
If you want to insert 5 columns between column B and column C using insert feature, just do it follow:
1# select 5 columns next to column C (includes the column 3) using the column number buttons.
2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.
Method 2: Insert multiple rows or columns in Excel using shortcut
You can also use the shortcut F4 to insert multiple rows or columns in Excel, and this is the simplest way to insert new rows or columns in Excel.
Insert Rows:
If you want to insert 5 rows between Row 2 and Row 3, then you just need to select the Row 3, then press F4 shortcut 5 times, then it will insert 5 rows after Row 2.
Insert Columns:
If you want to insert 5 columns between Column B and Column C, then you just need to select the entire Column C, then press F4 shortcut 5 times. You will see that 5 new columns are inserted in your worksheet.
Method 3: Insert multiple rows or columns in Excel using Insert Copied Cells feature
You can also use the Insert Copied Cells functionality to inert multiple rows or columns, just follow the below steps:
1# select multiple rows in your worksheet (the selected rows should be equal to the number of rows that you want to inert)
2# press Ctrl +C to copy the selected rows
3# select the destination rows, such as, if you want to insert new rows between Row2 and Row 3, then you should select Row 3. Then right-click on it, choose Insert Copied Cells option from the drop-down menu list.
4# you will see that multiple rows are inserted after row 2.
And if you need to insert multiple columns using Insert Copied Cells method, just select multiple columns in the above Step1. Then repeat the rest steps.
Method 4: Insert multiple rows in Excel using Excel VBA macro
If you want to insert multiple rows with VBA macro in Excel, you can follow the below steps:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub InsertRowsAtCursor()
Answer = InputBox("How many Rows to insert? (50 Rows maximum)")
NumLines = Int(Val(Answer))
If NumLines > 50 Then
NumLines = 50
End If
If NumLines = 0 Then
GoTo EndInsertLines
End If
Do
Selection.EntireRow.Insert
Count = Count + 1
Loop While Count < NumLines
EndInsertLines:
End Sub
5# back to the current worksheet, then run the above excel macro. Click Run button.
6# type rows number that you want to insert, then click OK button.
This post will guide you how to split a large workbook to separate files and each worksheet should be saved as a single file. How do I split the whole workbook into several excel worksheets or single excel file.
Assuming that you need to split your workbook that contains lots of worksheets into multiple single Excel file and its extension name is CSV.
The simplest way is to copy each worksheet to a single excel workbook or file by manually. So you can select one worksheet that you want to save as a single excel file, then create a new workbook, paste it in the new workbook. Then save it.
If you have hundreds of worksheet in your workbook, then it will be time-consuming and tedious. So do we have a quick way to split workbook to separate excel file? Of course yes, the below will describe one way to split your workbook with VBA code.
Split Excel workbook into separate files with VBA code
Let’s see the following steps:
If you want to quickly split a large workbook into several single excel file for each worksheet, then you can write a new Excel VBA macro code to achieve the result. And the below VBA code will save the separated excel file at the directory same as with the master workbook.
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub SplitWorkbook()
Dim workbookPath As String
workbookPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each wSheet In ThisWorkbook.Sheets
wSheet.Copy
Application.ActiveWorkbook.SaveAs Filename:=workbookPath & "\" & wSheet.Name & ".xlsx"
Application.ActiveWorkbook.Close False
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
5# back to the current worksheet, then run the above excel macro. Click Run command.
This post will teach you how to get the list of file names in a given directory to a worksheet in Excel. You will learn that how to use Excel to view Files and Folders in your worksheet by get the list of file names with different ways, such as: Excel VBA Macro, or FILES function.
If you want to get the list of file names from a folder that contain hundreds of files in it. You may be think the simplest way is that selecting the file and copying its name, then paste the file name in a cell in your worksheet, then repeat the above steps. If you follow this way to do it, you will take a long time to complete it. So it is a simple way, but it is also a huge waste of time. Do we have some quick way to achieve the same result? Of course yes, the below will talk two ways to copy the list of file names from a folder into worksheet quickly.
Method 1: Using FILES function to get list of file names
You can use the FILES function to get the list of file names from a directory, and this function is only works in name Defined Names. And then you can use a combination of the IFERROR function, the INDEX function and the Row function to create an excel formula. Let’s see the below steps:
1# Assuming that you want to get the list of file names from a directory, such as: C:\Users\devops\Tracing\WPPMedia\*, and enter this directory path into Cell B1.
2# On the FORMULAS Tab, click Define Name command under Defined Names group, then select Define Names… from the drop-down menu list.
3# the New Name window will appear. Set a name in the Name box, such as: FileNameList, type the following formula in the Refers to box.
=FILES(Sheet7!$B$1)
4# enter the following formula in the Cell B3, then drag the AutoFill Handle down to others cells to apply this formula.
You will see that all file names from the specified folder are listed in your worksheet.
Method 2: Using File browser and web browser to get list of file names
If you want to use this method to get the list of file name from a directory, you need to install one web browser, such as: Firefox, Chrome or IE. Then do it follow steps:
1# Move to the destination folder or directory on the Windows File Explorer, then copy the path of that directory. Such as: C:\Users\devops\Tracing\WPPMedia
2# open any web browser installed in your computer and paste the copied path in the address bar, then press Enter key.
3# you will see that it will add prefix file:/// at the beginning of the path in the address bar. And the file name list will be shown in the web page.
4# press Ctrl + A shortcut to select all of file names in the web page and then press Ctrl +C to copy the selected content.
5# open your worksheet, then press Ctrl + V to paste all file names into your worksheet.
Or you can save the web page in the Step 3 as the offline copy. Just press Ctrl + S or right-click on the web page, and then select Save Page As to save that web page.
Open the saved web page from the web browser, and copy its web address. Then click From WEB command under Get External Data group on the DATA tab in your worksheet.
Paste the web address in Address box, then click Go button. Then click Import button. You will see that all files and folders details are imported to your worksheet.
Method 3: Using Excel VBA macro to get list of file names
You can write an excel VBA macro code to get the list of file names from a specified directory,
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Option Explicit
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\"
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub
5# back to the current worksheet, then run the above excel macro.
6# select a folder that you want to get all names, then click OK button.
Related Functions
Excel IFERROR function
The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)…
Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
This tutorial will show you how to find duplicate values in two given columns in Excel. Or How to compare two columns and find duplicate entries in Microsoft Excel worksheet. And you will learn two methods to compare columns so that the duplicate values can be highlighted or listed in a range of Cells.
Assuming that you need to compare two columns (Column A and Column B) to get the duplicate values in your worksheet, you can find duplicate values in two columns with Excel Formula, or Excel VBA Macro code.
Method 1: Find duplicate values in two columns with Excel Formula
To compare two given columns and find duplicate values in Excel, you can use a combination of the IF function, the ISERROR function, and the MATCH function to create an Excel formula. So you can use the following formula:
Now you need to type this formula in Cell C1, press Enter key, drag AutoFill Handle down to Cell C2:C4, you will see all of the duplicated values are displayed in Column C.
Method 2: Find duplicate values in two columns with VBA Macro code
If you are familiar with the programming language and you can use a Visual Basic Macro to compare the value in two columns and then find duplicate values, just refer to the following steps:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub FindUplicatesinTwoColumns()
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Select the first range in one column:", "FindUplicatesinTwoColumns", Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Select the second range in another column:", "FindUplicatesinTwoColumns", Type:=8)
For Each R1 In Range1
xValue = R1.Value
For Each R2 In Range2
If xValue = R2.Value Then
If R3 Is Nothing Then
Set R3 = R1
Else
Set R3 = Application.Union(R3, R1)
End If
End If
Next
Next
R3.Interior.ColorIndex=3
End Sub
5# back to the current worksheet, then run the above excel macro.
6# Select the first range A1:A4 in Column A, click OK button, then select the second range B1:B4 in Column B, click OK button.
You can use conditional formatting with on a formula based on the COUNTIF function and the AND function to find the duplicate values in two specified columns and then highlighted them. Just do it following:
1# Select the entire Column A via click on the Column Header and then the column A will be highlighted
2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.
3# select Use a formula to determine which cells to format as Rule Type in the New Formatting Rule window
4# Type the following formula in the Format values where this formula is true: box
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel ISERROR function
The Excel ISERROR function used to check for any error type that excel generates and it returns TRUE for any error type, and the ISERR function also can be checked for error values except #N/A error, it returns TRUE while the error is #N/A. The syntax of the ISERROR function is as below:= ISERROR (value)….
Excel MATCH function
The Excel MATCH function search a value in an array and returns the position of that item.The syntax of the MATCH function is as below:= MATCH (lookup_value, lookup_array, [match_type])….
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Excel AND function
The Excel AND function returns TRUE if all of arguments are TRUE, and it returns FALSE if any of arguments are FALSE.The syntax of the AND function is as below:= AND (condition1,[condition2],…)…
Related Posts
Highlight Rows
You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
Find Duplicate Rows
If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
Highlight Duplicate Rows
this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
Highlight duplicate values
this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……
In this tutorial you will learn that how to remove leading or trailing spaces from a text string in a cell in Excel. This post will guide you how to delete leading or trailing spaces of all cells in a column.
If you remove leading spaces in cells one by one by manually, it will waste a lot of time. So this post will show you two methods to quickly remove leading and trailing spaces from text in one or more cells in your worksheet.
Assuming that you need to remove all leading spaces and trailing spaces in the range of cells A1:A4, you can refer to the following two methods to achieve it.
Method 1: Remove Leading and Trailing spaces with Excel Formula
To remove leading and trailing spaces from text string in Cells, you can use the TRIM function to remove extra spaces. So you can type the following formula in Cell B1, then press Enter key.
Then select Cell B1 and drag the AutoFill Handle down to other cells that you want to remove the leading or trailing spaces.
You will see that all of your leading and trailing spaces in range A1:A4 are cleaned up.
Note: if the text contains non-braking spaces, this formula will not work, because the TRIM function is not able to remove non-breaking spaces. At this time, you should remove the non-breaking spaces firstly with the SUBSTITUTE function, and then use the TRIM function to remove the rest leading and trailing spaces. So you can write down the following Excel formula:
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub RemoveLeadingAndTrailingSpaces()
Set W = Application.Selection
Set W = Application.InputBox("Select one range that you want to remove leading and trailing spaces:", "RemoveLeadingAndTrailingSpaces", W.Address, Type:=8)
For Each R In W
R.Value = VBA.Trim(R.Value)
Next
End Sub
5# back to the current worksheet, then run the above excel macro.
6# select the range of cells that you want to remove leading and trailing spaces, then click OK button.
Related Formulas
count specific words in a cell or a range
If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.…
Extract word that starting with a specific character
Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…
Extract word that containing a specific character
If you want to extract word that contains a hash character in a text string in Cell B1, you can use a combination of the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the FIND function to create an excel formula.…
Count the number of words in a cell
If you want to count the number of words in a single cell, you can create an excel formula based on the IF function, the LEN function, the TRIM function and the SUBSTITUTE function. ..
Remove Spaces from a String
how to remove extra spaces between words or text string from the specified range of cells in excel. How to delete all spaces from excel cells. …
Related Functions
Excel Substitute function
The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE (text, old_text, new_text,[instance_num])….
Excel CHAR function
The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….
Excel TRIM function
The Excel TRIM function removes all spaces from text string except for single spaces between words. You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
Excel CLEAN function
The Excel CLEAN function removes all non-printable characters from a text string.The CLEAN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the CLEAN function is as below:= CLEAN (text)...
This post will teach you how to use the Excel VBA macro code to copy a Cell to clipboard with only single click in Excel. How do I automatically copy the content of cell to Clipboard while clicking on the Cell or just single click on the cell in Microsoft Excel 2016, 2013 and older versions.
Normally, to copy a cell to the clipboard, you need to select a Cell firstly, press Ctrl + C shortcut to copy the content into clipboard. Whether there is any other ways to omit one or two steps to copy a cell to clipboard. Of course yes, you can try to write an Excel Macro code to copy cell content into clipboard while you click on that cell.
Copy a Cell to Clipboard with Single Click with VBA Macro
Let’s see the below detailed steps:
1# open your worksheet contain cells that you want to copy them to clipboard.
2# right click on the sheet tab, then select View Code menu from the pop-up menu list.
This tutorial will teach you how to change positive numbers to negative with Paste special multiply operation in excel. How to convert positive number to negative or how to reverse the number signs with VBA code in Excel.
Convert Positive Numbers to Negative with Paste Special Operation
To change positive numbers to negative in excel, you can do it with copy and paste, you need to copy a cell that contains a negative number -1, then select the cells that contain the positive numbers, and use the multiply operation on the Pasted Special menu. Then it will perform the multiplication on the selected cell. So all positive number will be change to negative.
You can do it as following steps:
1# type one negative number –1 in one cell
2# select that cell that contains -1 and then copy it.
3# select the range of cells that contain positive numbers, then right-click on it, select Paste Special from the pop-up menu list.
4# the Paste Special window will appear.
5# select Multiply radio button from the Operation section.
6# Click OK button, you will see that all of the positive numbers should be change to negative.
If the selected range of cells contains negative numbers, then its values also will be multiplied by -1, so its signs also will be reversed as negative.
You can also write an Excel formula to multiply one negative number -1 to achieve the same result. the generic formula is like this:
=B1* -1
Convert Positive Numbers to Negative with VBA Code
You can also write a new excel macro to convert positive numbers to negative numbers in Excel VBA, just do the following:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub ChangePositiveToNegative()
Dim myCell As Range
Set W = Application.Selection
Set W = Application.InputBox("Select one range that you want to change positive to negative:", "ChangePositiveToNegative", W.Address, Type:=8)
Set W = W.SpecialCells(xlCellTypeConstants, xlNumbers)
For Each myCell In W
xValue = myCell.Value
If xValue > 0 Then
myCell.Value = xValue * -1
End If
Next
End Sub
5# back to the current worksheet, then run the above excel macro.
In the previous post, we talked that how to highlight duplicate rows based on a certain number in excel. And this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel.
Highlight duplicate values
You can easily identify duplicate values in a list, just do the following:
1# Select the range of cells that you want to check (or you can click one single cell firstly, then press shortcut CTRL +A)
2# On HOME Tab, click Conditional Formatting command in the styles group.
3# click Highlight Cells Rules, then select Duplicate Values from the drop-down menu list.
4# select formatting styles, then click OK button to identify the duplicate values.
5# you will see that the duplicate values in the range will now be highlighted.
Highlight triplicate values
The above way will identify the duplicate values including duplicates, triplicates, or quadruplicates. If you only want to highlight triplicate values in your data, how to do it? At this time, you may be need to create your own formula to apply the conditional formatting.
Assuming that you want to highlight triplicate values in the range of cells A2:C4, then you can use the following formula based on the COUNTIF function:
This formula will check if the value of Cell A2 have three duplicates in the range A2:C4. If returns TRUE, then excel conditional formatting will highlight the color of cells.
Let’s see the below steps:
1# select the range of cells in your table
2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.
3# the New Formatting Rule window will appear.
4# select the Use a formula to determine which cells to format option under Select a Rule Type: box, and then enter the above formula in the Format values where this formula is true
5# click the Format… button, then the Format Cells window will appear.
6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button. You can also switch to other tabs to tweak the settings as you want.
7# back in the New Formatting Rule window, you can see a preview of your rows background color. Then click OK button.
8# let’s see the result:
If you want to highlight the duplicate values that is equal to the value of A2 in range A2:C7, and the duplicate values are more than 3 times, you can use the following formula as the conditional formatting rule.
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Related Posts
Highlight Rows
You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
Find Duplicate Rows
If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
Highlight Duplicate Rows
this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
Combine Duplicate Rows and Sum the Values
This post will teach you how to combine duplicate rows and sum the corresponding values or calculate numbers in specific column in excel. And how to merge duplicate rows and then sum the values with VBA macro in Excel..…
In the previous post, we talked that how to highlight duplicate rows with conditional formatting feature in excel. And this post will teach you how to find duplicate rows with an excel formula.
If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. How to achieve it? You can create a formula based on the IF function and the SUMPRODUCT function.
Assuming that you want to find or check duplicate rows in range A2:C6, then you can write down the following formula:
You can enter the above formula into the formula box in Cell D2, then press Enter. You will see that if there are same rows in the range, it will display “duplicates”, or it will display “No duplicates”.
Last, you can drag the AutoFill Handle down to the rest cells in Column D to check duplicate rows.
Related Functions
Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Related Posts
Highlight Rows
You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
Highlight Duplicate Rows
this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
Highlight duplicate values
this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……
Combine Duplicate Rows and Sum the Values
This post will teach you how to combine duplicate rows and sum the corresponding values or calculate numbers in specific column in excel. And how to merge duplicate rows and then sum the values with VBA macro in Excel..…
This tutorial will teach you how to highlight duplicates rows using conditional formatting feature in Excel. In the previous post, we talked that how to change the color of rows based on a certain number or text or begin a specific character in a specified column. And this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows.
Highlight duplicate rows in only one column
If you data just have only one column in each rows, then you can following the below steps to highlight duplicate rows:
1# select the range of cells in that column
2# on the HOME Tab, click Conditional Formatting command under Styles group, click Highlight Cells rules, and then select Duplicate Values.
3# select Duplicate and Light Red Fill with Dark Red Text from the Format cells that contains box in the Duplicate Values window. Click OK button.
4# you will see that all rows which are duplicates are highlighted.
Highlight duplicate rows in multiple columns
Assuming that you want to highlight duplicate rows in a range of cells A2:C4 with conditional formatting, and you need to write a new formula to apply the conditions to find the duplicate rows.
Method 1:
You can create your own formula based on the COUNTIFS function to count duplicated values in each column of your range. So you can use the following formula with COUNTIFS function:
2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.
3# the New Formatting Rule window will appear.
4# select the Use a formula to determine which cells to format option under Select a Rule Type: box, and then enter the above formula in the Format values where this formula is true
5# click the Format… button, then the Format Cells window will appear.
6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button. you can also switch to other tabs to tweak the settings as you want.
7# back in the New Formatting Rule window, you can see a preview of your rows background color. Then click OK button.
8# let’s see the last result.
If you just want to highlight duplicate rows except for the first occurrences, and you can use the following COUNTIFS formula as the conditional formatting rule.
You can use the CONCATENATE function or concatenate operator to join all values into one cell in each row, then you just need to check only one cell value in one column to find the duplicate values in the same column. At this time, you can use the COUNTIF function to create a formula, then apply it as the conditional formatting rule to find the duplicate rows. You can do the following steps:
1# create another column where you want to combine all values and call it Joined, such as column D
2# enter the following formula into the cell D2 to combine columns in each row
3# drag AutoFill Handle down to the rest of cells in column D
4# select the range that you want to highlight duplicate rows including the column D
5# on the HOME tab-> Styles -> Conditional Formatting, and then click New Rule….
6# select the Use a formula to determine which cells to format option under Select a Rule Type: box, and then enter the following formula in the Format values where this formula is true box.
7# click the Format… button, then switch to Fill tab, and choose one background color. Click OK.
8# you can now delete the column D.
Related Functions
Excel Concat function
The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)…
Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
Related Posts
Highlight Rows
You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.…
Find Duplicate Rows
If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function..…
Highlight duplicate values
this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……
Combine Duplicate Rows and Sum the Values
This post will teach you how to combine duplicate rows and sum the corresponding values or calculate numbers in specific column in excel. And how to merge duplicate rows and then sum the values with VBA macro in Excel..…
This post will teach you how to highlight rows in a table with conditional formatting in Excel. You will learn that how to change the color of the entire rows if the value of cells in a specified column meets your conditions, such as, if the value of cells is equal to or greater than a certain number or text values, then excel should be highlight entire rows or change a row color as you need.
Highlight rows based on a numeric value
Assuming that you have a table in range, and you want to highlight rows in that table where the value of cells in column B is greater than 5, and then you need to create the Conditional Formatting Rules and use a formula to apply conditional formatting, here you can use the formula:
=$B1>5
Let’s see the below detailed steps:
1# select the range of cells in your table
2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.
3# the New Formatting Rule window will appear.
4# select the Use a formula to determine which cells to format option under Select a Rule Type: box, and then enter the following formula in the Format values where this formula is true: box: =$B2>5
Note: you can create your formulas to check a cell if meet your conditions. Such as, you can change the condition as $C2<0.
You also need to notice that the dollar sign $ before the cell’s reference, you need to keep it when the formula need to check in a specified column.
5# click the Format… button, then the Format Cells window will appear.
6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button. You can also switch to other tabs to tweak the settings as you want.
7# back in the New Formatting Rule window, you can see a preview of your rows background color. Then click OK button.
8# you can see that the conditional formatting are beginning to take effect.
Note:
If you want to highlight rows based on a text value, you can use the following generic formula to apply conditional formatting:
If you want to change a row color if the cell value in a specified column begin with a specific character or text string, you can use the following formula as the conditional formatting rule.
So you can create additional formulas for your complex needs.
Related Functions
Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])…
Excel LEFT function
The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
Related Posts
Highlight Duplicate Rows
this post will talk that how to highlight entire rows that are duplicates in excel 2016, 2013 or lower version. Or how to change the background color of duplicate rows..…
Find Duplicate Rows
If you want to check the entire row that duplicated or not, if True, then returns “duplicates” value, otherwise, returns “no duplicates”. You can create a formula based on the IF function and the SUMPRODUCT function…
Highlight duplicate values
this post will teach you how to highlight duplicate values in the range of cells in excel. Normally, you may be need to identify duplicate values with a range of cells in Excel. And there is one of the fasted way that is using conditional formatting feature in Microsoft Excel……
Combine Duplicate Rows and Sum the Values
This post will teach you how to combine duplicate rows and sum the corresponding values or calculate numbers in specific column in excel. And how to merge duplicate rows and then sum the values with VBA macro in Excel..…
This post will teach you how to quickly insert or add multiple checkboxes in excel. How to add one checkboxes in one cell in Excel. How to add multiple checkboxes with Fill Handle or with VBA Macro in Excel.
Add a checkbox in one cell
If you want to add one checkbox in a cell, you can do it as following steps:
1# go to Developer Tab, then click Insert command under Controls group, select Check Box.
2# click one cell that you want to insert the checkbox. And it will appear in that cell.
3# you can move the cursor to drag the checkbox to the desired position.
Insert multiple checkboxes with Fill Handle
If you want to quickly add multiple checkboxes in Excel, you can use the Fill handle to create multiple checkboxes based on one checkbox that has been created. Refer to the following steps:
1# insert one checkbox as described above
2# move the checkbox into a cell, then select that cell, Drag the Auto Fill Handle over the range of cells that you want to fill.
3# you will see that multiple checkboxes have been added in each cell.
Insert multiple checkboxes with VBA code
You can also write a new excel macro to quickly add multiple checkboxes in Excel VBA, just do the following:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub addMultipleCheckBoxes()
On Error Resume Next
Set wr = Application.Selection
Set wr = Application.InputBox("Select one Range that you want to add checkbox in:", "addMultipleCheckBoxes", wr.Address, Type:=8)
Set wsheet = Application.ActiveSheet
i = 1
For Each R In wr
With wsheet.CheckBoxes.Add(R.Left, R.Top, R.Width, R.Height)
With Selection
.Characters.Text = R.Value
.Caption = "Check Box" & i
i = i + 1
End With
End With
Next
wr.ClearContents
wr.Select
End Sub
5# back to the current worksheet, then run the above excel macro.
Change the checkbox name and caption name
When you create a new checkbox, the default caption name is like as CheckBox x. so if you want to change the caption name, you just need to right click the checkbox, select the Edit Text from the drop-down menu, and then type the name that you want to set.
If you want to change the checkbox name, you just need to select the checkbox, then type the desired name in the Name box, it will change immediately.
This post will guide you how to delete blank or empty rows or rows that contain blank cells in excel. If you want to remove blank rows that contain blank cells, you can use the Go To Special command. But if you just want to remove rows that are entirely blank, you should use the Filter blanks cell way.
Delete rows that contain blank cells
You can use the Go To Special command to remove all rows that contain blank cells, just do the following:
1# on the HOME tab, click Find & Select command under Editing group
2# click Go To Special
3# Select Blanks radio button and then click OK button.
4# you will see that all rows that contain blank cells are selected.
5# go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.
Delete Blank Rows
If you want to remove blank rows in excel, and you need to find the entire blank rows firstly, so you can use the Filter function to filter all blank rows, just do this following:
1# select the range of cells that you want to remove blank rows
2# on the DATA tab, click Filter command under Sort& Filter group.
3# click Filter arrow button and uncheck the Select All box, just check only Blanks, then all blank rows in that column will be filtered.
4# you need to continue to filter other columns for blank cells until all cells in the selected range are blank.
5# select all the filtered rows, go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.
Or just right-click on the selected cell and then click Delete Row from the drop-down menu list.
6# on the DATA tab, click Clear command under Sort & Filter group.
7# you will see that all blank rows are removed.
Delete blank rows in Excel VBA
You can also write an excel Macro to delete blank rows in excel, just refer to the following steps:
1# click on “Visual Basic” command under DEVELOPER Tab.
3# click “Insert” ->”Module” to create a new module
4# paste the below VBA code into the code window. Then clicking “Save” button.
Sub RemoveBlankRows()
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select one Range that you want to remove blank rows", "RemoveBlankRows", myRange.Address, Type:=8)
xRows = myRange.Rows.Count
For i = xRows To 1 Step -1
If Application.WorksheetFunction.CountA(myRange.Rows(i)) = 0 Then
myRange.Rows(i).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
End If
Next
End Sub
5# back to the current worksheet, then run the above excel macro.