Often there comes a spreadsheet where you would need to make a preliminary check to see if any row is hidden. Fear not; it is very easy to check. Hiding rows is an interesting feature of MS Excel where not relevant information can be concealed in a spreadsheet without deleting them forever. While working on the file, your teammates may have concealed rows to optimize their visual perspective of the data by removing unnecessary rows, computations, or anything else that might reduce the file's readability. In this tutorial, we explain how to identify hidden rows in your excel ... Read More

There are cases when we need to compare multiple sheets for consolidating the data in a single sheet against similar entries. Manually this task may need huge manpower as well as time. On the other side the same data can be consolidated by using a one or two formulas and copying the same in all respective sheets. In this article, we will be working on the following formulas for identifying one list against another. =VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup]) =MATCH(lookup_value, lookup_array, [match_type]) Check one list against another using VLOOKUP function Step 1 − We have taken the sample data ... Read More

If we have a huge data where hyperlinks have been scattered throughout the worksheet, then it becomes a tedious task to find the hyperlinks and external references. This tutorial will help a user to find the hyperlinks available in a worksheet in the following scenarios − Find all the hyperlinks in Excel Find all the hyperlinks linked to a specific text Find all hyperlink locations with VBA Code Find All Hyperlinks in Excel Step 1 − A sample worksheet has been shown below with scattered hyperlinks. Step 2 − Please note that, using Find and Replace feature, ... Read More

Excel has a bunch of formulas and data analysts use this application to prepare various reports and dashboards. To support that analysis this article will help an excel user to identify all the cells whose values either starts with a number or an alphabet from a collection of dataset. We will identify these cells using the combination of following formulas. IF function ISERR function Left function Check if first character in a cell is a number or a letter The following formulas can be used to check if the first character of a cell is number or a ... Read More

If we have a dataset in which we need to check if there are any cell values or text which is not required or inappropriate, then the same can be identified in seconds using an excel function. There are many functions through which we can identify specific set of text in cell values of the complete dataset. Let’s have a look at the formulas and example and learn how to implement the same in a dataset. Check if dataset contains one or more values from a specific list Check if dataset contains one or more values ... Read More

When we have a list of entries in excel file and need to identify if there are some entities starting with a specific letter of special character, then looking at each value and counting will be a tedious task to do. This article will help you to find those entities using a formula without looking at each value of the list. Searching Cells containing Specific Character using Formula Searching the cells starting with a specific character Let’s take an example of the following data − Now, if you want to check the cells begin with the character “$” then ... Read More

The AVERAGE function determines the average value of the numbers that are passed to it in the argument list. Excel first totals all of the numerical values, then divides that total by the number of numerical values to arrive at the average. The AVERAGE function accepts several arguments, up to a maximum of 255 in total, in the pattern num1, num2, num3, and so on. Arguments can be anything from constants and arrays to ranges and cell references. They can even be numbers. There is no processing done on cells that are either empty or contain text or logical values. ... Read More

You are going to experience a great deal of frustration if there are hundreds of blank rows that need to be eliminated from the entire worksheet or workbook. In the following section of this article, you will find a listing of some efficient ways that will handle this job for you step by step. Remove Multiple Blank Rows in Excel with the Go To Special Command Now, kindly follow the steps below to remove rows that are empty and replace them with "Go To Special" rows. Let’s understand step by step with an example. Step 1 In our example, we ... Read More

The typical user of Microsoft Excel can benefit from the use of a dropdown list in order to simplify the data entry process. When entering data into a selected cell, using a drop-down to restrict entry options expedites the process and reduces the likelihood of making errors. Using the Data Validation feature of Excel, which will be covered in this post, you may quickly and easily generate a drop-down list of options. You can construct a drop down list in Excel by naming a list of things that is based on an Excel table that has a specific name. After ... Read More

Excel has the capability to automatically include related data into a range, provided that there are no empty rows or columns inside the area that has been specified. It is ok to leave empty rows and columns between groups of related data. Excel will then examine the data area to see whether it contains any field names, and if it does, it will remove rows containing those names from the set of records that will be sorted. Filtering and sorting are two of the most useful functions that can be found in Microsoft Excel. In the field of data analysis, ... Read More