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)