Fill the empty cells below

I am sure you have faced a situation where the data has been copied out of a pivot table, which looks like this

Many a times there is a need that you fill up the empty cells under a section with the same values. Like in the above example you need ABC in row number 2 to 5 and BCD in row number 8 to 11. There are many ways to accomplish this, with Copy - paste, Ctrl + D, Using power utils add in to name a few.

There is a way using formulas and selecting visible cells. To use this way apply the formula in the first empty cell (A3 in our case). The formula should pick up the value from the cell above to it. In our case A3 should contain a formula "=A2".

Follow these steps to fill all the empty cells with the contents which appear once in the section.

1. Copy the cell in which the formula is applied.
2. Apply an auto filter on the same column. Select all the empty cells that come as a result.
3. Filter for the blank cells, press "Ctrl +G". From the Go To dialog box, click on "Special". From the "Go To Special: dialog box select "Visible Cells only" and then click "OK". This will leave only the empty cells which are below the section text.
4. Now paste the formula which you have copied. And all the empty cells will be filled with the section text. 

How to remove Data Validations

There are multiple ways to do it and probably the simplest is to copy and paste an empty cell over the cell which contains the data validation. However if you just want to clear the data validation leaving the data & the format intact. Use the Clear All button on the Data Validation dialog box.

How to remove duplicates

Lets start with the original data which contains the duplicate values. Similar to one shown below

First select the entire data including the header row.

Now go to the Remove Duplicate command which is available on the Data tab of the ribbon

Select the column header from which you want to remove the duplicates, then click ok.

That is it, the duplicates have been removed. You will be shown a message showing how many duplicates have been removed and how many unique values remain.