This is going to be perhaps vague, but I'l try to write down the idea. I've been told that in doing scheme theory, most of the time what is studied is not a scheme but a morphism of schemes $f:X\rightarrow S$. The studied of such a maps can be enriched allowing a chance of "base" $S$. Such a scheme $S$ can be crazy, but I'll impose finite properties to the map $f$ to keep everything under control.
Now, as you say, if we have two $S$-schemes $f:X\rightarrow S$ and $Y\rightarrow S$ then we can consider the fiber product of them (which is a categorical product of $X$ and $Y$ over $S$). Such a process can be though of as replacing the base $S$ by the scheme $Y$. In doing so, the new map $f_Y:X_Y\rightarrow Y$ (standing for $p_2:X\times_{S}Y\rightarrow Y$) may be easier to work with than the map itself $f$. This very construction is generalizing the idea of "extending scalars".
Here is a sort of example of a product described above. Given the point $s\in S$ and its residue field $k(s)$ of the local ring $\mathcal{O}_s$ at that point. Then it is well known that $X_s=X\times_S Spec(k(s))$ has an underlying space the "fiber" $f^{-1}(s)$ (as long as $f$ is "finite"). This space can be considered as an algebraic variety over the field $k(s)$. This way $S$ is the scheme parameterizing the varieties $X_s$ some of which a priori may have different ground fields $k(s)$.
On the other hand, back to the fiber product, in considering a variety $X$ over the field $k$ (scheme of finite type over $k$), we can extend the scalars from $k$ to a field extension $K$. In doing so, we may think of such a variety now over a field extension $K$. Here is where the product comes into play. Vaguely enough, this is saying that if you have an equation and you have solved it over the field $k$ now you might ask yourself if you'd be able to solved it again but over a field extension $K$, here you want to perform an extension of scalars and the ideas written above may help out.
Needless to say that we can consider as well $X\times_k Spec(\overline{k})$ and due to the fact that field $k$ may not have been algebraically closed, such a fiber product can be handful.