Learn Microsoft Excel Online FreeThis post will guide you how to count the number of cells that contain errors within a range of cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through COUNTIF function. But if there is an easy way to count cells contain errors in… read more »
Sometimes we may have the requirement that subtotal data by group in a table. To just subtotal data in a column, we can use SUM function directly; for data in a column, amount data in another column, we can use SUMIF function to subtotal data. But to subtotal data in group, we need to classify… read more »
To compare two columns and remove the duplicate values, we have already posted a tutorial about how to solve this problem by ‘Conditional Formatting’ feature ‘Remove Duplicate’ rule. Actually, there are some other ways to solve it as well. If you are not familiar with ‘Conditional Formatting’ feature, you can also use a formula to… read more »
This post will guide you how to extract another cell value if one specified cell equals to a given value or an adjacent cell value using a formula in Excel 2013/2016. Normally, if you want to do some operation when a given cell equals a certain value, and you can create a new formula based… read more »
This post will guide you how to concatenate cells but ignore all blank cells in your worksheet in Excel. How do I concatenate cells but ignore blank cells with a formula in Excel. How to create a concatenate formula to skip blank cells with user defined function in Excel 2013/2016. Concatenating Cells but Ignore Blanks… 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 need to count time difference when participating a contest or an exam, and if the time difference (between start time and end time) is greater than certain time, participants are failed in the contest. So, in this situation we need to know the method to compare time difference with certain time. This article… read more »
Sometimes there are some blank cells exist in created table, if we want to mark them with user-defined text to make them easier to be found out, how can we do? Actually, there are several ways to implement this. In this article, we will introduce you two tricks to display user defined text in blank… read more »
Suppose we have two lists, list A contains all students, list B only contains students who passed exam. So, list A is longer than list B, and students in list B are all included in list A. If we want to find out which student failed the exam, we can though compare the two list… read more »
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 »
In our daily life we often create tables for recording the money we spend for lunch or dinner or something else per date. This can help us know the total cost or average cost per date or other unit clearly via some statistic related functions in excel, and then we can cut back on unnecessary… 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 our daily work we often need to count the total working-days or working-hours for employees then we can pay for them the salary in some cases. But we have to count the total exclude weekends or holidays as we don’t work on these days. So, we need to know how can we calculate the… read more »
Sometimes we create a table with formula applied to specific column. And when we editing the table, for example inserting a new row with data into the table, we want the new data lists in the specific column is also applied with this formula as well. Normally we can drag down the upper cell applied… read more »
Sometimes we want to find out the smallest positive value among a set of values. We should ignore the negative value and zero value when getting the smallest positive value. So we design different cases in this article to demonstrate how can we get the smallest positive value by different formulas. Returns the Smallest Value… read more »
In our daily work, we usually have the problem of counting the average of a period. If the given dates are continuous, we can handle this situation very well, and if the dates are discontinuous, how do we count the average for different periods? In this article, we will use some examples to show you… read more »
The median is the number in the middle of a range of data by order, half of the data in this range is larger than it, and half is smaller than it. Sometimes we want to calculate the median for a range of data. This article will introduce you the formula to calculate the median… read more »
Sometimes we want to find all zero value cells in worksheet and hide zero value to make the data to be checked easier. In this article, we will provide you some simple ways like changing format settings or by IF function to hide zero values. Prepare a table with some 0 value cells. At the… 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 »
Sometimes we open an excel file and find there are some blank cells in the worksheet. We want to mark them with special texts so that readers can find them easily and ignore them. From this article, we will provide you two ways to enter special texts into blank cell to mark them different with… read more »
