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.

How to Split worksheet into panes in Excel 2013

E-BOOK AND DVD

COMING SOON....WAIT FOR THIS PAGE

Solutions for VLOOKUP not working or showing #N/A

Major reasons for your VLOOKUP not working properly or showing error (#N/A or #REF!) could be:

1. The Lookup values have Trailing space at the end
2. The Numbers entered have been formatted as Text
3. You forgot to reference lock your table array
4. The file or table array to lookup has been deleted

So, let's tackle these issues and get solution for each of them.

1. Lookup values having trailing space at the end

Let's look at the below VLOOKUP example. Although, the values are available in the source table, the lookup table is not returning values for some of them. The problem is not directly evident. To see the problem, go to the lookup value in the source table and click the cursor at the very end of value. The extra space will be evident at the end of the input.

Solution: It is very simple. Please remove the extra space by pressing "Backspace" in edit mode.

2. The numbers entered have been formatted as Text (shows #N/A error)

In case none of the lookup value are not returning results (#N/A is appearing), it may be possible that the formatting of source is incorrect (the numbers will appear to be aligned left).  Click on the lookup reference of the data set and check in the formula bar if there is an apostrophe before your entry.

This is sometimes intentionally done to populate leading zero in the number (although a better way is through custom formatting...!!).

Solution: Again, the solution is very simple. Multiply all your lookup values by 1. This will convert all of them into number format (the numbers will get automatically aligned right).

3. You forgot to reference lock your table array

This is very common problem that occurs when we drag the VLOOKUP formula after entering it in one cell. Often we forget to lock the table array and as a result the lookup values you are trying to match are no longer part of original lookup array (since the lookup array also shifts down when we drag it).

Solution: Before dragging the VLOOKUP formula, press F4 key to lock lookup array (in edit mode)

4. The file or table array to lookup has been deleted (shows #REF! error)

Sometimes the source data used for VLOOKUP function gets deleted or sheets are improperly moved without reference. In such case VLOOKUP shows #REF! error.

Solution: Try and locate the source data and verify the linking of VLOOKUP formula again

How to Freeze Panes in Excel 2013

Freeze Panes is an excellent tool in Excel 2013 for viewing different parts of the same worksheet that normally cannot be seen together. Generally, this tool is used to freeze headings in top rows and first columns so that it is visible at all times while scrolling the worksheet.

This is particularly useful in cases where we have large spreadsheet with too many rows and columns that do not fit on the screen.

Let's understand this on the basis of an example. The spreadsheet contains financials of a company with multiple years and several line times. We want to view heading and line items at all times. To create and freeze these panes, follow these steps:

1. Position the cell cursor in cell B2 (intersection cell)

Excel will freeze the rows above this cell and columns to the left of this cell.

2.  "VIEW" tab in ribbon>>>Freeze Panes dropdown>>>Freeze Panes [Shortcut: Alt+WFF]

Excel will automatically show black line on the top row and left column of the selected cell indicating frozen rows and columns.

FREEZE TOP ROW or FIRST COLUMN:

Excel also provides customized option to freeze only top row or first column of the worksheet, regardless of where the cell cursor is located. Follow the below steps:

"VIEW" tab >>> Freeze Panes dropdown >>> Freeze Top Row [Shortcut: Alt+WFR]

"VIEW" tab >>> Freeze Panes dropdown >>> Freeze First Column [Shortcut: Alt+WFC]

UNFREEZE PANES:

To unfreeze any of the panes, there is one stop solution. Go to:

Go to: "VIEW" tab >>> Freeze Panes dropdown >>> Unfreeze Panes [Shortcut: Alt+WFF]

This will automatically remove the black lines from the worksheet, indicating that Excel has unfrozen them.

IMPORTANT NOTE:

• There is no default option to freeze multiple rows without freezing any columns. To do this, go to Column A and place the cell cursor below the rows you want to freeze and then go to freeze panes (Alt+WFF).
• Similarly, there is no default option to freeze multiple columns without freezing any rows. To do this, go to Row 1 and place the cell cursor to the right of columns that needs to be freezed and then click on freeze panes (Alt+WFF).

How to use OFFSET function in Excel 2013

The OFFSET function returns a cell or range of cells that is a specified number of rows and/or columns from the reference cell.

IMPORTANT NOTES:

• If either of the “rows”, “columns”, “height” or “width” components are left blank, Excel will assume its value to be zero. For example, if the formula is written as OFFSET(D20, , 1, , ) Excel will interpret this as OFFSET(D20, 0, 1, 0, 0). This can also be written as OFFSET(D20, , 1) since “height” and “width” can be omitted.
• Note that if “height” and “width” are included in the formula, they cannot be equal zero or a #REF! error will result.

EXCEL EXAMPLES

RECOMMENDED BOOKS

Excel 2013 Bible
Microsoft Excel 2013
Excel 2013 Dummies



Excel 2013 Formulas
Microsoft Excel 2013
Office 2013 Library



Excel 2010 Dummies
Excel 2013 VBA
Financial Analysis


Excel Pivot Tables
Excel VBA Dummies

How to create a Scenario Summary Report in Excel 2013

After using Scenario Manager to add scenarios to a table in a worksheet, we can generate a summary report to visualize all the scenarios as well as scenario with current input values. The summary report show input values in different scenarios as well as the output generated (based on the selection).

1. Open the workbook containing the required scenarios.

2. Open the Scenario Manager dialog box.

Go To: “DATA” tab in the ribbon>> What-If Analysis>>>Scenario Manager [Shortcut: Alt+AWS]

3. Click on the "Summary" button in this dialog box.

A new dialog box will open giving the options to generate the summary report. The dialog box gives us choice of reports that can be generated: Scenario summary (static) and Scenario PivotTable report (dynamic).

After selecting the type of report, click on "Result cells" and select the result cell (required output).

4. Click "OK" to generate the report.

Excel creates a separate worksheet with input values along with selected result cells for all the scenarios as well as current input values. We can appropriately rename and re-position this Scenario summary worksheet and then save the workbook.

How to use Scenario Manager in Excel 2013

Scenarios form an important part in real-world problems. The inputs used in the real-world problems are dynamic and hence lead to different outputs. Scenario Manager in Excel help us in getting solutions of these problems by accounting for changing inputs. Before we create a scenario, we must design the worksheet so that it contains at least one formula that is dependent on the input cells.

Once various scenarios are created based on different input values, Excel provides facility to create a detailed summary report of these scenarios.

Let's understand this on the basis of Costing example. The example calculates Net revenue of a coffee shop.

Formula: Net revenue = Gross Revenue - Total Variable Cost - Total Fixed Cost

Where, Gross Revenue = (Number of coffee cups sold *Selling Price per unit);                                 Total Variable Cost = (Number of coffee cups sold*Variable cost per unit)

Let's name these input cells as Number_Units_Sold, Selling_Price_Unit, Variable_Unit, Total_Fixed

Go To: "DATA" tab in the ribbon>> What-If Analysis>>>Scenario Manager [Shortcut: Alt+AWS]

CREATING SCENARIOS:

1. Click on the "Add" button in the Scenario Manager dialog box.

Insert Scenario name as "Base Case" and select all the input values (C2:C5) in Changing Cells field. In case the input values are scattered, we can hold CTRL key while clicking on different input cells OR use comma after clicking on each input cell.

2. Click "OK". This will open Scenario Values dialog box.

Insert various input values corresponding to this scenario as shown above.

3. Click "Add" to insert new scenario OR click "OK" to stop adding scenarios.

Once all the scenarios are added, the Scenario Manager dialog box will open again and show names of all the entered scenarios.

4. To see result of a particular scenario, click on the Scenario name and press "Show" button.

Excel will automatically change input values as listed in the scenario and show relevant output.

IMPORTANT NOTE:

• Always assign range names to your input cells before you begin creating the various scenarios. It is easy to recognize the input filed by names while using the Scenario Values dialog box to enter input values for various scenarios.

How to Trace Precedents and Dependents in Excel 2013

Excel offers excellent formula auditing tools for tracking down a cell that is causing error by tracing the relationships between the formulas in the cells of spreadsheet. By tracing the relationships, we will be able to locate the Precedents and Dependents.

Also read: How to use Goal Seek feature in Excel 2013

Precedents are cells or ranges of cells that affect the active cell value.
Dependents are cell or ranges of cells that are affective by the active cell.

Trace Precedents and Trace Dependents are powerful tools under Formula Auditing in Excel. Go to:       "FORMULAS" tab in the ribbon>>>Functions under Formula Auditing [Alt+M]

• Trace Precedents [Alt+MP]: When we click this button when the active cell contains formula, it draws arrows from the primary cells which are directly affecting the formula in the active cells (direct precedents). On clicking the button once again, it shows more arrows from cells which are affecting direct precedent cells and so on.
• Trace Dependents [Alt+MD]: When we click this button, it draws arrows from the selected cell to the cells (direct dependents) that use, or depend on, the results of the formula in the selected cell. On clicking the button once again, it shows "tracer" arrows identifying the cells (indirect dependents) that refer to formulas found in the direct dependents.
• Remove Arrows [Alt+MA]: On clicking this button, all the arrows created by precedents and dependents are removed. Additionally, Excel also gives options of customized removal of arrows by providing two buttons in the drop-down besides Remove Arrows.

1. Remove Precedent Arrows [Shortcut: Alt+MAP] - Removes only precedent arrows
2. Remove Dependent Arrows [Shortcut: Alt+MAD] - Removes only dependent arrows

IMPORTANT NOTES:

• Double-clicking an arrow activates the cell on one end of the line. Double-clicking again activates the cell on the other end.
• If the relationship shown by arrows are in the same worksheet, then Excel draws red or blue arrows. It shows black dotted arrow with miniature icon of worksheet in case the relationship exists with another worksheet (in same or different workbook).

More interesting articles on this website:

• How to create Two-Variable Data Table in Excel 2013
• Text Alignment keyboard shortcuts for Excel 2013
• How to freeze panes in Excel 2013
• How to generate RANDOM number in Excel 2013

How to use MATCH function in Excel 2013

How to use INDEX function in Excel 2013

How to use CHOOSE function in Excel 2013

Excel's Lookup functions like VLOOKUP, HLOOKUP, CHOOSE, INDEX, MATCH etc. are used to find and return data from a list or table based on a lookup value or index number.

In CHOOSE function, it uses an index number to find and return a specific value from a corresponding list of data. The index number indicates the position of the value in the list.

Syntax:     CHOOSE(index_num, value1, [value2], [value3]....[value254])

• index_num: Specifies the Value is to be returned by the function. It could be a number or formula or a cell reference. The value for this should be betweent 1 and 254.
• value1: The value/data we want to fetch when "index_num" equals 1
• value2: The value/data we want to fetch when "index_num" equals 2

The value/data for output { value1, value2, .....etc.} could be numbers, cell references, defined names, formulas, functions, or text.

IMPORTANT NOTES:

• If index_num < 1 OR if index_num > "number of values" then Excel gives #VALUE! error
• If index_num is a fraction, it is changed to the lowest integer before being used (eg. 3.8 to 3)
• If index_num is an array, every value is evaluated when CHOOSE is evaluated

How to use VLOOKUP in Excel 2013

VLOOKUP is probably one of the most useful function in Excel when it comes to looking up fields related to a variable. We can use the VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.

The V in VLOOKUP stands for Vertical i.e. it looks up in vertical table. Since most of the real world data appears in vertical table, the VLOOKUP is one of the most used function in Excel. It works similar to human mind, first it searches for relevant key item and goes further in the row to get it's details.

Let's understand this better by most commonly encountered real world example of Employee details. Suppose a company has 10 employees with unique Employee ID followed by details like Name, Departement, Contact Number and E-mail ID.

If we need the Contact Number corresponding to Employee ID 201309 we first search the row in which this ID exists and then we move further in the same row to locate Contact Number. That's exactly how Excel also works, we just need to feed the data for what we are looking and from where we need to look.

Syntax:      VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Insert the value to search in the first column of range or table. This value could be a value or it could be a cell reference (more dynamic). This helps excel to identify the row in which our data exists.
• table_array: The entire range of cells that contains data. We can directly use the range reference or a range name. This tells excel the location for search.
• col_index_num: The column in range selected under table_array corresponding to desired matching value field. (eg. Contact Number lies in column 4, hence 4 should be inserted)
• range_lookup: Optional field. Specifies whether we need exact match or approximate match. FALSE (or 0) corresponds to exact match while TRUE (or 1) corresponds to approximate match. The default value is FALSE (or 0) i.e. exact match.

IMPORTANT NOTES:

• If col_index_num < 1, Excel gives #VALUE! error
• If col_index_num > number of columns in range, Excel gives #REF! error
• While searching for text values in the first column, ensure that any cell value does not contain leading spaces, trailing spaces or inconsistent quotation marks. It may give error or incorrect values in the output.
• While searching for number or date values in the first column, ensure that data is stored in appropriate format (not as text format)

How to use Goal Seek feature in Excel 2013

Goal Seek feature is an excellent tool in excel to obtain an input value for the desired output value for a given relationship between variables. This eliminates trial and error method to insert different set of input values to get desired output value and above all it's dynamic to use.

The word "Goal Seek" explains the feature that it will seek to find appropriate input value for desired goal (output).

Let's straight away jump into an example of costing to understand it better. The example given here is used to calculate the Total Cost of the company for given variables i.e. Number of units produced and Variable cost per unit. For example, the fixed cost is $1,000 (cell B2), number of units is inserted in cell B3 (say 500) and variable cost per unit in cell B4 (say $2.5 per unit)

Output calculated as: [Fixed_Cost]+[Units_Produced*Variable_Per_Unit] OR = B2+(B3*B4)

The final output derived for this is in cell B6 (Total cost = $2,250).

Now, suppose I have certain budget for Total Cost (say $1800, the desired output) and I have to determine the Number of units that can be produced with this constraint (for given fixed cost and variable cost per unit assumptions).

Following steps will be useful to use Goal Seek the appropriate input value (Number of units) for desired Total Cost (output):

1. Select the cell whose value you wish to change.

This should be the cell that already contains a formula (here cell B6 which calculates Total Cost)

2. Go to Ribbon. Click...Data--->What-if Analysis--->Goal Seek [ Shortcut: Alt+AWG ]

This will show a Goal Seek dialog box on the Excel screen.

4. The dialog box will have three fields as below:

Set Cell: The cell that will contain the desired output (cell B6 with formula that is already selected)

To Value: Insert the desired output that is required (here 1800, the
value of Total Cost)

By Changing Cell: The cell with input value that needs to be changed as per desired output (Units)

5. Click Ok.

On clicking "OK", a new dialog box showing the status of the analysis will be populated.

IMPORTANT NOTE:

• The status dialog box informs that goal seeking process a found the solution. When this is not the case, the Step and Pause buttons inside the dialog box become visible, and further iterations could be carried to narrow and ultimately get the desired value.

Click "OK" to keep the values. Click "Cancel" in case you want to return to original values