If $X$ is full rank, the inverse of $X^TX$ exists and we get the least squares estimate: $$\hat\beta = (X^TX)^{-1}XY$$ and $$\operatorname{Var}(\hat\beta) = \sigma^2(X^TX)^{-1}$$
How can we intuitively explain $(X^TX)^{-1}$ in the variance formula? The technique of derivation is clear to me.
Consider a simple regression without a constant term, and where the single regressor is centered on its sample mean. Then $X'X$ is ($n$ times) its sample variance, and $(X'X)^{-1}$ its recirpocal. So the higher the variance = variability in the regressor, the lower the variance of the coefficient estimator: the more variability we have in the explanatory variable, the more accurately we can estimate the unknown coefficient.
Why? Because the more varying a regressor is, the more information it contains. When regressors are many, this generalizes to the inverse of their variance-covariance matrix, which takes also into account the co-variability of the regressors. In the extreme case where $X'X$ is diagonal, then the precision for each estimated coefficient depends only on the variance/variability of the associated regressor (given the variance of the error term).
A simple way of viewing $\sigma^2 \left(\mathbf{X}^{T} \mathbf{X} \right)^{-1}$ is as the matrix (multivariate) analogue of $\frac{\sigma^2}{\sum_{i=1}^n \left(X_i-\bar{X}\right)^2}$, which is the variance of the slope coefficient in simple OLS regression. One can even get $\frac{\sigma^2}{\sum_{i=1}^n X_i^2}$ for that variance by ommitting the intercept in the model, i.e. by performing regression through the origin.
From either one of these formulas it may be seen that larger variability of the predictor variable will in general lead to more precise estimation of its coefficient. This is the idea often exploited in the design of experiments, where by choosing values for the (non-random) predictors, one tries to make the determinant of $\left(\mathbf{X}^{T} \mathbf{X} \right)$ as large as possible, the determinant being a measure of variability.
Does linear transformation of Gaussian random variable help? Using the rule that if, $x \sim \mathcal{N}(\mu,\Sigma)$, then $Ax + b ~ \sim \mathcal{N}(A\mu + b,A\Sigma A^T)$.
Assuming, that $Y = X\beta + \epsilon$ is the underlying model and $\epsilon \sim \mathcal{N}(0, \sigma^2)$.
$$ \therefore Y \sim \mathcal{N}(X\beta,\sigma^2)\\ X^TY \sim \mathcal{N}(X^TX\beta, X^T\sigma^2 X)\\ (X^TX)^{-1}X^TY \sim \mathcal{N}[\beta,(X^TX)^{-1} \sigma^2] $$
So $(X^TX)^{-1}X^T$ is just a complicated scaling matrix that transforms the distribution of $Y$.
Hope that was helpful.
I'll take a different approach towards developing the intuition that underlies the formula $\text{Var}\,\hat{\beta}=\sigma^2 (X'X)^{-1}$. When developing intuition for the multiple regression model, it's helpful to consider the bivariate linear regression model, viz., $$y_i=\alpha+\beta x_i + \varepsilon_i, \quad i=1,\ldots,n.$$ $\alpha+\beta x_i$ is frequently called the deterministic contribution to $y_i$, and $\varepsilon_i$ is called the stochastic contribution. Expressed in terms of deviations from the sample means $(\bar{x},\bar{y})$, this model may also be written as $$(y_i-\bar{y}) = \beta(x_i-\bar{x})+(\varepsilon_i-\bar{\varepsilon}), \quad i=1,\ldots,n.$$
To help develop the intuition, we will assume that the simplest Gauss-Markov assumptions are satisfied: $x_i$ nonstochastic, $\sum_{i=1}^n(x_i-\bar{x})^2>0$ for all $n$, and $\varepsilon_i \sim \text{iid}(0,\sigma^2)$ for all $i=1,\ldots,n$. As you already know very well, these conditions guarantee that $$\text{Var}\,\hat{\beta}=\tfrac{1}{n}\sigma^2(\text{Var}\,x)^{-1}\text{,}$$ where $\text{Var}\,x$ is the sample variance of $x$. In words, this formula makes three claims: "The variance of $\hat{\beta}$ is inversely proportional to the sample size $n$, it is directly proportional to the variance of $\varepsilon$, and it is inversely proportional to the variance of $x$."
Why should doubling the sample size, ceteris paribus, cause the variance of $\hat{\beta}$ to be cut in half? This result is intimately linked to the iid assumption applied to $\varepsilon$: Since the individual errors are assumed to be iid, each observation should be treated ex ante as being equally informative. And, doubling the number of observations doubles the amount of information about the parameters that describe the (assumed linear) relationship between $x$ and $y$. Having twice as much information cuts the uncertainty about the parameters in half. Similarly, it should be straightforward to develop one's intuition as to why doubling $\sigma^2$ also doubles the variance of $\hat{\beta}$.
Let's turn, then, to your main question, which is about developing intuition for the claim that the variance of $\hat{\beta}$ is inversely proportional to the variance of $x$. To formalize notions, let us consider two separate bivariate linear regression models, called Model $(1)$ and Model $(2)$ from now on. We will assume that both models satisfy the assumptions of the simplest form of the Gauss-Markov theorem and that the models share the exact same values of $\alpha$, $\beta$, $n$, and $\sigma^2$. Under these assumptions, it is easy to show that $\text{E}\,\hat{\beta}{}^{(1)}=\text{E}\,\hat{\beta}{}^{(2)}=\beta$; in words, both estimators are unbiased. Crucially, we will also assume that whereas $\bar{x}^{(1)}=\bar{x}^{(2)}=\bar{x}$, $\text{Var}\,x^{(1)}\ne \text{Var}\,x^{(2)}$. Without loss of generality, let us assume that $\text{Var}\,x^{(1)}>\text{Var}\,x^{(2)}$. Which estimator of $\hat{\beta}$ will have the smaller variance? Put differently, will $\hat{\beta}{}^{(1)}$ or $\hat{\beta}{}^{(2)}$ be closer, on average, to $\beta$? From the earlier discussion, we have $\text{Var}\,\hat{\beta} {}^{(k)} =\tfrac{1}{n}\sigma^2/\text{Var}\,x{}^{(k)})$ for $k=1,2$. Because $\text{Var}\,x^{(1)}>\text{Var}\,x^{(2)}$ by assumption, it follows that $\text{Var}\,\hat{\beta}{}^{(1)} <\text{Var}\,\hat{\beta}{}^{(2)}$. What, then, is the intuition behind this result?
Because by assumption $\text{Var}\,x^{(1)}>\text{Var}\,x^{(2)}$, on average each $x_i^{(1)}$ will be farther away from $\bar{x}$ than is the case, on average, for $x_i^{(2)}$. Let us denote the expected average absolute difference between $x_i$ and $\bar{x}$ by $d_x$. The assumption that $\text{Var}\,x^{(1)}>\text{Var}\,x^{(2)}$ implies that $d_x^{(1)} >d_x^{(2)}$. The bivariate linear regression model, expressed in deviations from means, states that $d_y = \beta d_x^{(1)}$ for Model $(1)$ and $d_y = \beta d_x^{(2)}$ for Model $(2)$. If $\beta\ne0$, this means that the deterministic component of Model $(1)$, $\beta d_x^{(1)}$, has a greater influence on $d_y$ than does the deterministic component of Model $(2)$, $\beta d_x^{(2)}$. Recall that the both models are assumed to satisfy the Gauss-Markov assumptions, that the error variances are the same in both models, and that $\beta^{(1)}=\beta^{(2)}=\beta$. Since Model $(1)$ imparts more information about the contribution of the deterministic component of $y$ than does Model $(2)$, it follows that the precision with which the deterministic contribution can be estimated is greater for Model $(1)$ than is the case for Model $(2)$. The converse of greater precision is a lower variance of the point estimate of $\beta$.
It is reasonably straightforward to generalize the intuition obtained from studying the simple regression model to the general multiple linear regression model. The main complication is that instead of comparing scalar variances, it is necessary to compare the "size" of variance-covariance matrices. Having a good working knowledge of determinants, traces and eigenvalues of real symmetric matrices comes in very handy at this point :-)
This builds on @Alecos Papadopuolos' answer.
Recall that the result of a least-squares regression doesn't depend on the units of measurement of your variables. Suppose your X-variable is a length measurement, given in inches. Then rescaling X, say by multiplying by 2.54 to change the unit to centimeters, doesn't materially affect things. If you refit the model, the new regression estimate will be the old estimate divided by 2.54.
The $X'X$ matrix is the variance of X, and hence reflects the scale of measurement of X. If you change the scale, you have to reflect this in your estimate of $\beta$, and this is done by multiplying by the inverse of $X'X$.
Say we have $n$ observations (or sample size) and $p$ parameters.
The covariance matrix $\operatorname{Var}(\hat{\beta})$ of the estimated parameters $\hat{\beta}_1,\hat{\beta}_2$ etc. is a representation of the accuracy of the estimated parameters.
If in an ideal world the data could be perfectly described by the model, then the noise will be $\sigma^2= 0$. Now, the diagonal entries of $\operatorname{Var}(\hat{\beta})$ correspond to $\operatorname{Var}(\hat{\beta_1}),\operatorname{Var}(\hat{\beta_2})$ etc. The derived formula for the variance agrees with the intuition that if the noise is lower, the estimates will be more accurate.
In addition, as the number of measurements gets larger, the variance of the estimated parameters will decrease. So, overall the absolute value of the entries of $X^TX$ will be higher, as the number of columns of $X^T$ is $n$ and the number of rows of $X$ is $n$, and each entry of $X^TX$ is a sum of $n$ product pairs. The absolute value of the entries of the inverse $(X^TX)^{-1}$ will be lower.
Hence, even if there is a lot of noise, we can still reach good estimates $\hat{\beta_i}$ of the parameters if we increase the sample size $n$.
I hope this helps.
Reference: Section 7.3 on Least squares: Cosentino, Carlo, and Declan Bates. Feedback control in systems biology. Crc Press, 2011.
