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

combine duplicate rows1

Step2: on the DATA tab, click Consolidate command under Data Tools group.

combine duplicate rows2

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.

combine duplicate rows3

Step5: you will see that all duplicate rows are combined and the corresponding values summed.

combine duplicate rows4

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.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: In the VBE, click on Insert from the top menu and select Module to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Combine Duplicate Rows and Sum the Values vba 1.png
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.

combine duplicate rows6

Step6: Follow the prompts to select the source range and output range for the processed data. Click OK to start processing the data.

Combine Duplicate Rows and Sum the Values vba 3.png
Combine Duplicate Rows and Sum the Values vba 4.png

Step7: The processed data will be displayed in the output range you specified.

combine duplicate rows7

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.

Sort by Second or Third character in a Column

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.

=RIGHT(Cell, LEN(Cell)-1)

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

sort string by second character1

Step2: type the following formula in the formula box of Cell B1, then press Enter key.

=RIGHT(A1,LEN(A1)-1)
sort string by second character2

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.

sort string by second character3

Step4: select the range B1:B4, on the DATA tab, click sort A to Z command under Sort & Filter group.

sort string by second character4

Step5: choose Expand the selection radio button in the Sort Warning window. Then click Sort button.

sort string by second character5

Step6: you will see that the column A and B are sorted by the second character.

sort string by second character6

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.

sort string by second character7

Step2: on the DATA tab, click Text to Column command, the Convert Text to Columns Wizard window will appear.

sort string by second character7

Step3: choose Delimited radio button in the first step window, click Next button

sort string by second character9

Step4: type the delimited character which is used to join the text string in Cell, click Next button

sort string by second character10

Step5: choose the destination cell reference, then click Finish button.

sort string by second character11

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.

sort string by second character12

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:

=MID(A1,FIND("-",A1)+1,1)
sort string by second character13

4. Related Formulas

  • 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)….

How to Generate Random Passwords in Excel

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.

2. Generate Random Password

If you want to generate a random and secure password in Excel, you can use a combination of the CHAR function, the RANDBETWEEN function and concatenation operator to build a new Excel formula.

Just use the following steps:

Step1: Select one cell that you want to generate the random password,

Step2: then enter this formula in that Cell

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(97,122))&CHAR(RANDBETWEEN(65,90))&RANDBETWEEN(100,999)&CHAR(RANDBETWEEN(42,43))

Step3: press Enter key to apply this formula. You will see that it will generate an eight characters password.

generate random passwords1

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.

=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))
How to Generate Random Passwords in Excel 1.png

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:

=CHAR(RANDBETWEEN(65,90))
generate random passwords2

5. Generate Random Lowercase Letter String

If you want only to generate a random lowercase letter string, you can use the below formula:

=CHAR(RANDBETWEEN(97,122))
generate random passwords3

6. Generate Random Password Only Contain Numeric Characters

If you want to generate a random password only contain 6-digit numeric characters, you can use the following formula:

=RANDBETWEEN(100000,900000)
generate random passwords4

7. Generate Random Symbol String

If you want to generate a random symbol string, just use the following formula:

=CHAR(RANDBETWEEN(33,47))
generate random passwords5

This formula will randomly generate one of the following specific characters: !”#$%&'()*+,-./

We talked that the CHAR function will generate lots of specific characters, and if you just want to create a password that can be type on the keyboard, so you should keep the specific character simple and avoid to use characters like ¶, Œ, or ©.

8. Conclusion

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

7 Basic Formulas And Functions parameters You NEED to KNOW

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.

function and formula parameter1

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

function and formula parameter2

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

function and formula parameter3

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.

function and formula parameter4

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:

function and formula parameter5

OR

Select A1:B2->Enter “Numbers” in Name Box:

function and formula parameter6

STEP 2: Entering “Numbers” as argument.

function and formula parameter7

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.

function and formula parameter8

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.

function and formula parameter9

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.

function and formula parameter10

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

function and formula parameter11

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.

function and formula parameter12

Example 3:

F1=SUM(A1:B2); A1:B2={1,2;3,4}; The array is a multidimensional array.

function and formula parameter13

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

function and formula parameter14

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

function and formula parameter16

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.

function and formula parameter17

IF

The IF function is used to make logical judgments

Syntax: IF(logical_test, value_if_true, value_if_false)

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.

function and formula parameter18

– 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

function and formula parameter19

Example2: Reference “Numbers” is missing

function and formula parameter20

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.

function and formula parameter21

Example 2: Lookup value is incorrect; A is not listed in the first column of lookup table A2:B6.

function and formula parameter22

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:

function and formula parameter23

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

function and formula parameter24

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.

function and formula parameter25

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

function and formula parameter26

Resolution

We can move mouse over the warning icon to show the floating indication message.  

function and formula parameter27

You can also click the icon to do the following operations.

function and formula parameter28

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

function and formula parameter29

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.

How to Convert Ounces to Pounds or Grams in Excel

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 ounces to pounds1

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 pounds2

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.

convert ounces to pounds3

Delete Rows Based on Cell Value

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.

delete rows based on cell value1

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.

delete rows based on cell value2

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.

delete rows based on cell value3

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.

delete rows based on cell value4

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.

delete rows based on cell value5

delete rows based on cell value6

delete rows based on cell value7

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

remove rows based on values3

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.

delete rows based on cell value9

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.

remove rows based on values1

remove rows based on values2

delete rows based on cell value12

How to Add Prefix or suffix to Cell

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.

add prefix or suffix 1

If you want to add suffix to all cells with concatenation operator, you can use the following formula:

=A1&"excelhow"

add prefix or suffix2

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

add prefix or suffix3

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

add prefix to cell113

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.

add prefix or suffix5

6# select a range that you want to add prefix, click OK button, then add one prefix string or character. Click OK button.

add prefix to cell111

add prefix to cell112

7# you will see that the prefix is added to all range of cells.

add prefix or suffix8

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

add prefix or suffix9


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],…)…

Quickly Insert Multiple Rows or Columns in Excel

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.

insert multiple rows or columns 1

2# On the HOME tab, click Insert command under Cells group. Or click Insert Cells from the drop-down list of Insert.

insert multiple rows or columns2

Or you can right-click on the selected rows, then choose the insert option from the pop-up menu list.

insert multiple rows or columns3

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.

insert multiple rows or columns5 insert multiple rows or columns6

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

insert multiple rows or columns7

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.

insert multiple rows or columns8

4# you will see that multiple rows are inserted after row 2.

insert multiple rows or columns9

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

insert multiple rows or columns10

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.

insert multiple rows or columns11

6# type rows number that you want to insert, then click OK button.

insert multiple rows or columns12

insert multiple rows or columns13

 

Split Excel workbook into separate Files

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

split workbook into separate file1

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.

split workbook into separate file2 split workbook into separate file3

 

How to Get the List of File Names From a Folder

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.

get list of file names from a folder1

2# On the FORMULAS Tab, click Define Name command under Defined Names group, then select Define Names… from the drop-down menu list.

get list of file names from a folder2

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)

get list of file names from a folder3

4# enter the following formula in the Cell B3, then drag the AutoFill Handle down to others cells to apply this formula.

=IFERROR(INDEX(FileNameList,ROW()-2)," ")

get list of file names from a folder4

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

get list of file names from a folder5

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.

get list of file names from a folder6

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.

get list of file names from a folder7

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.

get list of file names from a folder8

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

get list of file names from a folder9

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.

get list of file names from a folder10

6# select a folder that you want to get all names, then click OK button.

get list of file names from a folder11


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])….

Find Duplicate Values in Two Columns

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:

=IF(ISERROR(MATCH(A1,$B$1:$B$4,0))," ",A1)

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.

find duplicate values in two columns1

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

find duplicate cells in two columns111

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.

find duplicate values in two columns3

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.

find duplicate values in two columns4 find duplicate values in two columns5

7# let’s see the result:

find duplicate values in two columns6

Method 3: Find duplicate values in two columns with Conditional Formatting feature

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

find duplicate values in two columns7

2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

find duplicate values in two columns8

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

=COUNTIf($B:$B, $A1)

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.

7#you will be back to the New Formatting Rule windows and you can check a preview of the formatting you have selected. Then click OK button.

find duplicate values in two columns9

Then the conditional formatting rule will be applied to all values in two columns and highlighted the duplicate values.

find duplicate values in two columns9


Related Functions

  • 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……

Remove Leading and Trailing Spaces

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.

=TRIM(A1)

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.

remove leading and trailing spaces1

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:

=TRIM(SUBSTITUTE(A1,CHAR(160)," "))

remove leading and trailing spaces2

You will see that all of leading and trailing spaces are removed from the text string in range A1:A4.

Or try to use the following formula:

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

remove leading and trailing spaces3

The CHAR(160) formula returns a non-breaking space character.

The SUBSTITUTE function will replace all non-breaking space with new space character.

The CLEAN function will remove all non-printing characters, such as line breaks.

Method 2: Remove Leading and Trailing spaces with Excel VBA macro

If you want to quickly remove leading and trailing spaces, you can write an  Excel VBA macro code, just do it following:

1# click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

remove leading and trailing spaces112

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.

remove leading and trailing spaces5

6# select the range of cells that you want to remove leading and trailing spaces, then click OK button.

remove leading and trailing spaces111

remove leading and trailing spaces6

 


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

 

Copy a Cell to Clipboard with Single Click

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.

copy a cell to clipboard1

3# then the “Visual Basic Editor” window will appear

4# paste the below VBA code into the code window. Then clicking “Save” button.

copy a cell to clipboard2

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
        Target.Copy
    End If
End Sub

5# try to click cell A1 to A4, you will see that all Cell that you clicked are copied to clipboard automatically.

copy a cell to clipboard3

If you want to use this VBA macro code, just need to update the Range as you need.

 

 

 

How to Convert Positive Numbers to Negative

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

convert positive number to negative1

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.

convert positive number to negative2

4# the Paste Special window will appear.

5# select Multiply radio button from the Operation section.

convert positive number to negative3

6# Click OK button, you will see that all of the positive numbers should be change to negative.

convert positive number to negative4

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 number to negative5

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

change positive to negative 112

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.

convert positive number to negative7

change positive to negative 111

convert positive number to negative7

 

Highlight duplicate values

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)

highlight duplicate values1

2# On HOME Tab, click Conditional Formatting command in the styles group.

highlight duplicate values2

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.

highlight duplicate rows3

5# you will see that the duplicate values in the range will now be highlighted.

highlight duplicate values3

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:

=COUNTIF($A$2:$C$6,A2)=3

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.

highlight duplicate values4

8# let’s see the result:

highlight duplicate values5

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.

=COUNTIF($A$2:$C$7,A2)>3

highlight duplicate values6 highlight duplicate values6


Related Functions

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

Find Duplicate Rows

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:

=IF(SUMPRODUCT(($A$2:$A$6=A2)*1,($B$2:$B$6=B2)*1,($C$2:$C$6=C2)*1)>1,"duplicates","No duplicates")

find duplicate rows1

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

 

Highlight Duplicate Rows

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

highlight duplicate rows1

2# on the HOME Tab, click Conditional Formatting command under Styles group, click Highlight Cells rules, and then select Duplicate Values.

highlight duplicate rows2

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.

highlight duplicate rows3

4# you will see that all rows which are duplicates are highlighted.

highlight duplicate rows4

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:

=COUNTIFS($A$2:$A$6,$A2,$B$2:$B$6,$B2,$C$2:$C$6,$C2)>1

Let’s see the below steps:

1# select the range of cells in your table

highlight duplicate rows5

2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

highlight rows2

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

highlight duplicate rows6

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.

highlight rows5

7# back in the New Formatting Rule window, you can see a preview of your rows background color. Then click OK button.

highlight duplicate rows8

8# let’s see the last result.

highlight duplicate rows9

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.

=COUNTIFS($A$2:$A2,$A2,$B$2:$B2,$B2,$C$2:$C2,$C2)>1

highlight duplicate rows10

Method 2:

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

highlight duplicate rows11

2# enter the following formula into the cell D2 to combine columns in each row

=CONCATENATE(A2,B2,C2)

highlight duplicate rows12

3# drag AutoFill Handle down to the rest of cells in column D

highlight duplicate rows13

4# select the range that you want to highlight duplicate rows including the column D

highlight duplicate rows14

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.

=COUNTIF($D$2:$D$6,$D2)>1

highlight duplicate rows15

7# click the Format… button, then switch to Fill tab, and choose one background color. Click OK.

highlight duplicate rows16

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

Highlight Rows

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

highlight rows1

2# on the HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

highlight rows2

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

highlight rows3

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.

highlight rows4

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.

highlight rows5

7# back in the New Formatting Rule window, you can see a preview of your rows background color. Then click OK button.

highlight rows6

8# you can see that the conditional formatting are beginning to take effect.

highlight rows7

Note:

If you want to highlight rows based on a text value, you can use the following generic formula to apply conditional formatting:

=$A2=”excel”

or

=SEARCH(“excel”,$B1)>0

highlight rows8

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.

=LEFT($A2,2)=”ex”

highlight rows9

highlight rows9

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

How to Insert Multiple Checkboxes

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.

insert multple checkboxes1

2# click one cell that you want to insert the checkbox. And it will appear in that cell.

insert multple checkboxes2

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.

insert multple checkboxes3

3# you will see that multiple checkboxes have been added in each cell.

insert multple checkboxes4

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

add multiple checkboxes 112

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.

insert multple checkboxes6

add multiple checkboxes 111

insert multple checkboxes6

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.

insert multple checkboxes9

insert multple checkboxes9

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.

insert multple checkboxes11

 

How to Delete Blank Rows in Excel 2016/2010

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

delete blank rows1

3# Select Blanks radio button and then click OK button.

delete blank rows2

4# you will see that all rows that contain blank cells are selected.

delete blank rows3

5# go to HOME tab, click Delete command under Cells group. Then click Delete Sheet Rows from the drop down menu list.

delete blank rows4

delete blank rows5

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

delete blank rows6

2# on the DATA tab, click Filter command under Sort& Filter group.

delete blank rows7

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.

delete blank rows8

delete blank rows9

4# you need to continue to filter other columns for blank cells until all cells in the selected range are blank.

delete blank rows10

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.

delete blank rows11

Or just right-click on the selected cell and then click Delete Row from the drop-down menu list.

delete blank rows12

delete blank rows13

6# on the DATA tab, click Clear command under Sort & Filter group.

delete blank rows14

7# you will see that all blank rows are removed.

delete blank rows15

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.

Get the position of the nth using excel vba1

2# then the “Visual Basic Editor” window will appear.

3# click “Insert” ->”Module” to create a new module

convert column number to letter3

4# paste the below VBA code into the code window. Then clicking “Save” button.

remove blank rows111

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.

delete blank rows17

delete blank rows18