excel INDEX

How to Average Absolute Values in Excel

We can use AVERAGE function to calculate average of certain values. We can use ABS function to get absolute values for both positive number and negative number. If we want to get the average absolute values, we need to combine both above two functions in the formula. In this free tutorial, we will provide two… read more »

How to Dynamically Extract Unique Values from A Column List in Excel

Suppose we have a list of some objects in one column, and some of them are duplicate, they also can be replaced by typing different object name, here’s the question, how can we dynamically extract unique values from this list in time if they are changed frequently? This article will show you two methods to… read more »

How to Find the Earliest and Latest Date in Excel

We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function or INDEX function together to find the matched date based on some criteria. Except using… read more »

How to Create Dynamic Drop Down List without Blank in Excel

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be used to reduce mistake and typing errors. And it is also be used to restrict… read more »

How To Align Duplicate Values within Two Columns in Excel

This post will guide you how to align duplicate values within two columns based on the first column in your worksheet in Excel. How do I use an formula to align two columns duplicate values in Excel. Aligning Duplicate Values in Two Columns Assuming that you have two columns including product names in your worksheet…. read more »

How to Extract Number from Text String in Excel

This post will guide you how to extract number from a given test string in Excel. How do I extract all numbers from string using a formula in Excel. How to get all number from a given test string using user defined function in Excel. Extract Number from String with Formula Extract Number from String… read more »

How to Convert Horizontal List of Data into Vertical in Excel

This post will guide you how to transpose horizontal list to vertical list in your current worksheet in Excel. How do I convert horizontal rows to vertical columns with a formula in Excel 2013/2016. Convert Horizontal List to Vertical with a Formula Convert Horizontal List to Vertical using Paste Special Feature Assuming that you have… read more »

How to Lookup Values from Right to Left in Excel

This post will guide you how to vlookup values from right to left in your worksheet in excel. How do I look up values to left column with a column in Excel 2013/2016. How to use Vlookup function to extract the corresponding value of the left column in your worksheet in Excel. Lookup Values to… read more »

How to Create Dynamic Named Range in Excel

This post will guide you how to create dynamic named range that can expand automatically when you add a new value into the current range in Excel 2013/2016. How do I create a dynamic named range with a formula in Excel. How to create a dynamic named range by creating a table in Excel. Creating… read more »

How to Extract a Unique List based on Criteria in Excel

This post will guide you how to extract unique values in a range based on one criteria with a formula in Excel. How do I create a unique distinct list based on one condition in Excel. Extract Unique List of Values based on Criteria Assuming that you have a list of data in range A1:C6,… read more »

How to Pick a Random Name from a List in Excel

This post will guide you how to pick a random name from a list in Excel. How do I select random value from a list or a table with a formula in Excel 2013/2016. Pick Random Name from a List Assuming that you have a list of names in range B1:B6, and you want to… read more »

Sidebar