What is overfitting while building model? What exactly is overfitting while building models ?

I like @RRMT's answer, but I would like to emphasize this problem: adding many variables could look like it has a significant impact on the prediction power of the model, because it does... only for the data on hand.
Overfitting refers to creating a model trying to explain everything which is happening in the data, using way too many parameters/features compared to what is truly relevant for the model. Usually, the predictions of the model in the training data get better and better as we add (useless) variables, but the out-of-sample predictions could get worse and worse, or at least stagnate, while our expectations were higher because of our accuracy in-sample.
For example, imagine that you have the following relation:
$$Y_i=1+X_i+2X_i^2+\epsilon_i$$
with $X_i =-11+i, i=1,...,21$, and $\epsilon_i\sim N(0,\sigma^2=20^2)$
The data I simulated looks like this:

The green line represents $E(Y_i\vert X_i)$, i.e. the true model without the random errors.
If we try to fit the data using only a linear regression with $X$ as a predictor, then we create a simple line. The model underfits because the regressors we use are not sufficient to understand the underlying logic of the data.

If we add $X^2$ as a regressor, then our fit is very good, because the regressors we used explain very well the actual underlying logic of the data.

However, if you could add many regressors $X^k,k\in\{3,4,...,20\}$, you would end up creating a model which makes perfect predictions because you would have as many regressors as observed data points. The model would then overfit because it fits the data on hand perfectly, but way too much to really understands the logic of the data. The prediction curve (I failed to draw it because my regression becomes unstable due to the high numbers in the regressors) would go through all the observed points $\{x_i,y_i\}$ and it would make big swings between points, making out-of-sample predictions very bad.
I suggest you look at this article for a nice visual example.
EDIT:
I was able to add regressors up to $X^7$, here is what it looks like:

You can see that there is a first sign of overfitting at the last data point: the regression overfits and make the predicition very close to the actual point whereas it should not be that close: we observe a deviation between the green line and the red line, due to overfitting, which corresponds to a worsening of the out-of-sample prediction accuracy in that area.
EDIT:
@RRMT proposed solutions which can be applied in the training data. In addition to these solutions, you can divide your dataset into a training set and a testing set in order to apply some backtesting procedures (such as cross-validation) in order to try to identify is some overfitting is occuring.
EDIT 2:
I changed my data (but kept the general concepts: quadratic true model vs high order polynonial (15)). Here is the result:

You can see that using many predictors allowed the model to create a prediction curve which comes extremely close to all the observations. But this accuracy is only artificial, it creates deviations from the true model. This is an example of overfitting.
In addition, I suggest you have a look the interesting comments in the comments below my answer.
