Everything about excel data tab

Green marked area:

How to remove duplicate value in excel?

To remove duplicate values use the remove duplicates option. To remove duplicates, first select the column or table. Then click on the option. After clicking it will show two options, expand the selection and continue with the current selection. The first option means the rule will apply for overall data and the second option means the rule will apply for only selected data.

How to do flash fill in excel?

Suppose you have a name column. In the name column, each cell contains the first name and last name. Now if you want to add HR with each name. Then what you can do is you can create a column beside the name column. In the name column first cell name value Ahmad Rafsun. Now if you write HR Ahmad Rafsun and after this, if you select the below cells of the new name column and click on flash fill then you will get a new name column where before each name you will get HR.

In this image, I just wrote the first name of the first cell and then select the other cell and then click on the flash fill, and then automatically other cells get filled by the first name.

How to do data validation in excel?

Suppose you want to perform a lookup function. In this function, you will pass Id, and you will get total marks and ranking and the Group name of that id. If you have big data then it will be difficult for you to type id and group name each time. In this case, you can use data validation. If you create a drop-down menu of student id and group names then it will be helpful, because now you have to just click on the drop-down button and select the id and group name to get the total marks.

To do it, at first click on the data validation. After clicking a dialog box will open.
There you will see three options setting, input massage, error alert.
Setting:Here you will create the drop-down menu. At first, you will see allow option. Select the type like list,decimal,date,time etc. In the example, I will choose a list. Then click on the source and select the column.
Input message: Here you will type a message.If you go to the drop-down button then excel will display this message. You can say it as a guideline. This is optional
Error alert:Here you will type an error message. If the user gives the wrong input the error will be displayed. This is also optional.

How to use consolidate?

If you have data in multiple sheets and you want to perform sum, max, min, count, average, etc on those data together and want to get the output in another sheet then use consolidate.

Suppose you have data of 5 cells person. They sell three-brand phones iPhone, oneplus, and Asus. You have the data that how many phones and which brand phone they sold each month. Now you have to find that how many phones they sold in the last three months. To get that do sum of numbers of phone they sold in last three months. So click on consolidate option. Then select the function. Then click on the reference option then go to the first sheet. Select the data and click on add. Then go to the second sheet and click on add, then go to the third sheet. There are three checkboxes, click on those checkboxes and click on ok. To delete any sheet there is another option delete. Select the sheet from all references and click on delete.

Now click on the reference and go to each sheet and select the data. After selecting one sheet click on add and then select the next sheet.

How to use goal seek?

Suppose you have 5 subjects. You want that the average of all these subject marks should be 80 but you know only 4 subjects mark. Find what will be the missing subject mark to get an 80 average. To do this you can use the goal seek option.

To do this first find the average of the given subject's marks. Now you want 80 in that cell where you calculated the average. So select that cell and go to what analyze if drop-down menu and click on goal seek.

After clicking a dialog box will open. Here you can see that set cell= D12 and this cell is that cell where you calculated the average of other subjects' marks. In to value option put that value which you want. In this example, you want 80 so write 80. By changing cell means by changing which cell value you want to get that value. In this case ICT mark cell, so select that cell and click on ok.

How to create scenario manager in excel?

Senior manager create future scenarios depending on current data. Suppose you have 4 products Ice cream, Cold drinks, Teapot, coffee. Currently, you may have some quantity of these products. So you have to buy these products for summer and winter. Some products sell well in winter like a teapot, coffee and some products sell well in summers like cold drinks and ice cream. So you will not buy an equal quantity of products in each season. In summer you will buy more ice cream and cold drinks than teapot and coffee. Now we will create a scenario that in summer and winter Which products you should buy and what will be the quantity.

In the image, you can see the current data and a dialog box. This box opens after clicking on scenario manager. To get scenario manager go to the Data tab and then go to the what-if analysis drop-down menu. Now click on add in the dialog box.

Then write the scenario name and then go to the next option and select the target column cells. If you want to write a comment then write comment otherwise click on ok.

Now put values. In this case, you will put the quantity that you want in summer. Go to the next press tab button. After giving the value click on ok. Now again a new dialog box will open. Now if you want to add some more scenario then click on add and create more. Here you will create another for winter. Now close the tab. The work is done.
Let's create a table of manage scenario:
To do that go to the manage scenario and click on the summary. After clicking on summary a new dialog box will open.

You can see the summary as a table and also as a pivot table. You can see these two options in the image. If you have any cell-like total, sum, average, etc then select that cell in the result cell option and then click ok. In a new sheet, you will see the summary table.

Here you have cells named as item names. To get the item name just copy the item names and paste where you have cell names.