How to use SUMPRODUCT function in Excel 2013

SUMPRODUCT is perfect formula if we have two or more arrays (range of cells in column/row) and you want to find the total value of the columns multiplied together.

Syntax:      =SUMPRODUCT(array1, [array2], [array3],...,[array255])

• array1: Data in the first column/row
• array2: Data in the second column/row and so on.

SUMPRODUCT formula can accommodate up to 255 arrays.

IMPORTANT NOTES:

• All the arrays should have same number of data entries. You can't have array1 containing 13 cells in a column, and array2 having only 12 cells. This will return #VALUE! error.
• Cells containing text OR blank cells are treated as zeroes by SUMPRODUCT function.

How to find and delete all merged cells in Excel 2013 worksheet

Random occurrences of merged cells is a sign of poorly formatted excel sheet. Merged cells are fine when we want to make excel sheet look pretty, but in case we need something very functional (e.g. sorting data in an entire column), it is essential to find and remove merged cells.

Following steps will help you to find and remove all merged cells:

1. Press Ctrl+F to open "Find and Replace" dialog box option

Ensure that "Find What:" field is empty.

2. Click on "Options" button in the dialog box

This will further expand the "Find and Replace" dialog box.

3. Click on the "Format" button >>> Go to "Alignment" tab >> "Text control" area

4. Place a check mark next to "Merge Cells" button by clicking it and then press "OK"

This will close the "Find Format" dialog box and " Find and Replace" dialog box gets activated.

5. Now click on "Find All" button to locate all the merged cells

Excel displays a list of all merged cells in the worksheet. Click an address in the list and the corresponding merged cell is activated.

REMOVING ALL MERGED CELLS

1. Click anywhere in the list window and press Ctrl+A

This will select all of the found occurrences of merged cells.

2. Go to "HOME" tab in ribbon >> "Delete" button >> Delete Cells [Shortcut: Alt+HDD]

This will remove all the merged cells from the worksheet. It will ask for option to choose the shifting of remaining cells.

IMPORTANT NOTE:

• In case the data is organized in certain manner and only deleting the cells do not work, you can also delete corresponding row or columns as below:

To delete rows containing merged cells:

Go to "HOME" tab in ribbon >> "Delete" button >> Delete Sheet Rows [Shortcut: Alt+HDR]

To delete columns containing merged cells:

Go to "HOME" tab in ribbon >> "Delete" button >> Delete Sheet Columns [Shortcut: Alt+HDC]

Show Developer Tab in the toolbar in Excel 2013

How do I get the DEVELOPER tab to display in the Ribbon in Excel 2013? Well, that's very easy!!

The DEVELOPER tab is the toolbar that has important tools like VBA editor, Form control buttons, ActiveX controls, Macros etc.

To display Developer Tab in Excel 2013, go to:

"FILE" tab in the ribbon >>> Options >>> Customize Ribbon [Shortcut: Alt+FTC]

Click the Developer checkbox under the list of "Main Tabs" on right side of the dialog box. Hit "OK".

Now, the DEVELOPER tab should be visible on the ribbon along with other tabs.

How to highlight Duplicates in Excel 2013

Is there an easy way to locate and highlight duplicates in a list in Excel?

Yes, Excel has many powerful tools to locate and highlight duplicates in a list or data range. Conditional formatting is one of the tool with which we can highlight duplicates.

Let's learn the process of highlighting duplicates with the help of an example:

1. Select the cell range where you want to find and highlight duplicates

2. Use the Conditional Formatting tool located in "Home" tab of the ribbon as mentioned below:

Conditional Formatting >>> Highlight Cells Rules >>> Duplicate Values [Shortcut: Alt+HLHD]

This will activate the "Duplicate Values" dialog box.

3. Select "Duplicate" from the dropdown in the dialog box and also select relevant highlight option

 To apply any customized format you can select "Custom Format..." from the dropdown list. The preview of format will appear while you are selecting from the dropdown.

4. Click "OK"

This will apply the desired format to duplicate values in the selected range.

IMPORTANT NOTES:

• The feature is NOT case sensitive.
• Excel will recognize the cells as duplicate only if both cells contains exactly same data.
• In case any extra spacings are present in any one of the cells, Excel will not recognize them as duplicates (If both cells have same extra spacings, then it will recognize as duplicates!!)

Creating drop down list in Excel 2013 using data validation

Validating your data while entering data into the cell helps you to prevent unnecessary/invalid data in the input cell. Excel 2013 provides great tools to ensure the validation of input data in a given cell.

Drop-down lists or combo boxes helps in picking up a value from a valid list to enter in a cell.

Let's learn to create drop down list in Excel with the help of an example:

• Creating a drop down menu list using Data Validation
• Inserting an "Input message" tab using Data Validation
• Inserting an "Error alert" tab using Data Validation

1. Create a separate list of data items which we need for the drop-down

2. Select a cell where the drop down list is required

Go to:  “DATA” tab in ribbon >> Data Validation button >>> Data Validation [Shortcut: Alt+AVV]

3. In the Data Validation dialog box, go to settings and select "List" from the drop down menu

Ensure that "In-cell dropdown"  button is checked. Also, check "Ignore blank" if you want to allow blank entries.

4. You can manually enter the range which you require in drop-down list OR can give cell reference in the "Source" field

5. Press "OK"

Inserting an "Input message" tab using Data Validation

The input message provides the information regarding the valid input data to the user when the cell containing the drop-down is selected.

• In the Data Validation dialog box, go to "Input Message" tab
• Enter the required title and input message that you want to populate (ensure that "Show input message when cell is selected" is checked)

Inserting an "Error alert" tab using Data Validation

Error alert populates when any invalid data is entered into the cell by the user.

• In the Data Validation dialog box, go to "Error Alert" tab
• Select the error symbol from "Style" drop-down
• Enter the required title and error message that you want to populate when error occurs (when the data entered in the input cell is different from the validation list)

How to use SUBSTITUTE function in Excel 2013

SUBSTITUTE is very useful function in Excel when we want to replace specific text in a text string with some other text.

Syntax:               =SUBSTITUTE(text, old_text, new_text, [instance_num])

• text: The original text string containing the text to be replaced
• old_text: The text/data you want to replace with new_text
• new_text: The text/data that is used to replace the old_text
• [instance_num]: An option which specifies which occurance of old_text you want to replace

• If [instance_num] is specified, just that instance of the old_text is replaced;
• Otherwise, all instances of old_text are replaced with new_text.

IMPORTANT NOTES:

• The SUBSTITUTE function is Case Sensitive.
• The function can take texts, numbers as well as cell references for replacement.
• Text strings should always be placed using double quotes (eg. “Tom") while the reference cell formula (eg. C4) do not need double quotes. For numbers, using double quotes is optional.
• The output generated will always in text format, even if the input data were in number format.

How to generate RANDOM number in Excel 2013

Excel provides excellent functions to generate random numbers. RANDBETWEEN and RAND are two primary functions used to generate random numbers.

Using RANDBETWEEN Function:

This function returns random whole number between the given range.

Syntax:        =RANDBETWEEN(lower end of range, higher end of range)  OR
                    =RANDBETWEEN(bottom, top)

For example, to obtain a random number between 25 and 150 we enter as below:

=RANDBETWEEN(25, 150)

Using RAND Function:

This function returns a random decimal number between 0 and 1. Hence, (0,1) is the default range for the function.

Syntax:          =RAND()

To generate a random real number between a and b, you can use following formula:

=RAND()*(b-a)+a

For example, to generate real number between 25 and 150, use the formula below:

=RAND()*125+25     {the first part will give number between 0 to 125 which will then get added to 25}

IMPORTANT NOTES:

• The number generated by these functions will change each time we refresh the sheet or on pressing F9 button. To use these functions effectively, it should be combined with other functions like CHOOSE.
• If you want to use these functions to generate a random number but don't want the numbers to change every time the cell is calculated, you can enter =RAND()  or =RANDBETWEEN(a,b) in the formula bar, and then press F9 to change the formula to a permanent random number.

How to combine or join text cells in Excel 2013

Excel provides great tools to combine or join text/data from multiple cells into one cell. There are primarily two ways in which we can join text using Excel 2013:

1. Using Ampersand Operator (&)
2. By applying CONCATENATE function

Let's discuss both of these methods with some examples. Say for example we have a client list with "First Name" in column A, "Last Name" in column B and "Age" in column C. We wish to join these text into single cell (say in column D).

1. Using Ampersand Operator (&)

To join text of first name entry in cell A4 with last name in cell B4 and age in cell C4, use the formula below in cell D4 (since we need output data in column D):

=A4&" "&B4&" "&C4      [Output: Tiger Woods 37]

You can add customized text or numbers inside the formula to get desired results. For example,

="Name-"&A4&" "&B4&"; "&"Age-"&C4            [Output: Name-Tiger Woods; Age-37]

Text strings should always be placed using double quotes (eg. "Name-" or " " for space) while the reference cell formula (eg. C4) do not need double quotes. For numbers, using double quotes is optional.

2. By applying CONCATENATE function

The CONCATENATE function in Excel joins upto 255 text/data cells into one text string. We can join text, cell references, number or special characters using this function.

Syntax:       CONCATENATE(data1, data2, data3, ....., data255)

• data1: this is the first data/text that needs to be concatenated (required field)

=CONCATENATE("Name-",A4," ",B4,"; ","Age-",C4)          [Output: Name-Tiger Woods; Age-37]

CONCATENATE is always preferable for joining text/data of multiple cells since it effective to use and less prone to error.

IMPORTANT NOTE:

Text strings should always be placed using double quotes (eg. "Name-" or " " for space) while the reference cell formula (eg. C4) do not need double quotes. For numbers, using double quotes is optional.