Learn Microsoft Excel Online FreeAVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last N numbers from a range contains both numeric values and non-numeric values. EXAMPLE In this… read more »
Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT and LARGE functions. LARGE can return largest values based on criteria, SUMPRODUCT can sum up… read more »
Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and SMALL functions. SMALL can return smallest values based on criteria, SUMPRODUCT can sum up these… read more »
Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then we can get a list of days. But for some special requirement, we just want… read more »
In our daily work we may need to create a dynamical dropdown list and sort all values by alphabetical order. To create a dropdown list like this, we need to apply some built-in features like ‘Define Name’ and ‘Data Validation’ features, and we also need the help of formula which is combined with excel functions…. read more »
Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight rows by excel ‘Conditional Formatting’ function. Actually, ‘Conditional Formatting’ has a powerful capability to highlight… read more »
When counting the number of times for objects appear in a list or a range, we usually record the duplicate value only once and ignore the redundant ones. We cannot apply formula with only one function to implement this in excel, so we need to update formula and use a formula combine more than one… read more »
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 »
In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function. This time if we want to stack data from multiple columns to one column, how can we do? Actually, we can still use VBA script or formula of Index function to… read more »
We enter a list of numbers or products and there are some duplicates in the list, if we want to just do count for the unique values and exclude the duplicates, how can we do? Now you can follow the below steps to solve this question by formula quickly. Prepare a list of products and… read more »
This post will guide you how to transpose data from rows to column with a formula in Excel. How do I transpose every N rows from one column to multiple columns in Excel. Assuming that you have a list of data in range A1:A10 in column A, and you want to transpose every 2 rows… read more »
This post will guide you how to increment cell reference by x Rows when you drag a formula to populate cells in a column in Excel. By default, when you drag the AutoFill Handle down to other cells , the cell reference in the formulas will be increated by one. And if you want to… read more »
This post will guide you how to get row number from a Vlookup in Excel. How do I return a row number from a Vlookup with a formula in Excel. Assuming that you have a list of data in range A1:B5, you can use the VLOOKUP function to lookup a value in the first column… read more »
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 »
This post will guide you how to move every other row to a new column in Excel. How do I move every odd row to another column with formula in Excel 2013/2016. How to move every even row to other column in Excel with VBA Macro in Excel. Move Every Other Row to New Column… read more »
This post will guide you how to get the active Cell address with a formula in Excel. How do I return address of active cell with a VBA Macro in Excel. Get the Active Cell Address with Formula Get the Active Cell Address with VBA Get the Active Cell Address with Formula If you want… read more »
This post will guide you how to convert multiple rows or columns into a single row with a formula in Excel 3013/2016. How do I convert groups of rows to a single row of columns with VBA Macro in Excel. Convert Multiple Row into Single Row with Formula Convert Multiple Row into Single Row with… read more »
This post will guide you how to remove all numbers from a text string in one cell in Excel. How do I strip all numeric characters from a given cell with a formula in Excel. How do I remove numbers from cells with text using a User Defined Function in Excel. Remove Numbers From Text… read more »
This post will guide you how to highlight every other row in Excel. How do I shade every other row with conditional formatting in Excel. Highlight Every Other Row Video: Highlight Every Other Row Highlight Every Other Row Assuming that you have a list of data in range A1:C9, and you want to highlight every… read more »
This post will guide you how get the nth matching values with VLOOKUP function in Excel. How do I find the nth match value with Index/Match formula in Excel. How to find the first, second, third or nth matching value from a range of cells using VLOOKUP formula. Find the First Match Value Using VLOOKUP… read more »
