Here is a step-by-step tutorial using my ABC Analysis Template on Excel. Once you click the OK button, Excel will display the number of duplicate values that have been deleted if it existed in the data set.ABC Analysis in Excel: an example in 5 steps Here you can choose which columns to be included for the data validation. In Excel to handle duplicates, first, select the data range and then select Remove Duplicates option from the Data menu. One of the common tasks analysts do is to eliminate duplicate values from the data. Duplicate Removal and Data SplitДata cleaning is a key element of any data analysis process. Users can simply select values from the drop-down list and this, in turn, helps you to control what is being entered in the table. Voila, your list is ready and you can copy it to all the required cells in the data set. Here, choose вЂListвЂ™ for Allow validation criteria and for Source choose the rows/column where you have entered the categorical values to be added in the list.

Next, choose a cell where you want the drop-down list to be added and from the Data menu and select the Data Validation option. Firstly you need to copy in a row or column the set of values that should go into the drop-down list. I have added a drop-down list to the AQI category column from the Air Quality data set.

For example, if the same sheet is being analysed by multiple users, and some status is being entered by them then you can add a field with all their names or status, which they can choose easily from the drop-down list. This will come in handy while handling data by multiple users. Drop-down ListДЄnother cool feature in Excel is adding a drop-down list to categorical data fields. More information on its usage can be gotten from the Microsoft portal. Similar to Vlookup() there are few other lookup functions in Excel, which are Hlookup(), Lookup(), and Xlookup().

Users can simply use it like a search form rather than scouting through the data. This customized Search/Lookup for data can be implemented in a new sheet and locked for any modification of the formula. where users may want to search for specific data among the humongous rows and tables. This is applicable for any data set such as Sales, Finance, etc. So whichever state I want to search the data for, I simply have to enter in the state field and my vlookup function automatically fetches the result. I have used the vlookup function on two fields namely, Total Public Health Facility and Population. Here is the output that I have created using the vlookup function on the data. For example for the above-given data set I have the following vlookup function, =VLOOKUP(G47,B1:I38,8,FALSE). The format of the vlookup() function is as follows: =VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match - indicated as 1/TRUE, or 0/FALSE). You can insert the vlookup function from the Formulas menu in Excel. For example, in the given data set if you want to quickly find the number of public health centers for a particular state then this function can be modeled to extract only the required data from the entire data set. Also, another use of this functionality is that it can be used as a neat lookup form when many users are working on a particular data set. Vlookup is a handy function to search for specific data when there are many columns and rows in your data set.

Turn on data analysis excel