This has been confusing for a while, so I'll provide my argument in the hope that is it correct.
Suppose $ Q $ is an orthogonal matrix ($ Q^{T} Q = I $).
It is known that orthogonal matrices preserve the dot product, as $ (Q\mathbf{x}) \cdot (Q\mathbf{y}) = (Q\mathbf{x})^{T} (Q \mathbf{y}) = \mathbf{x}^{T} Q^{T} Q \mathbf{y} = \mathbf{x}^{T} \mathbf{y} = \mathbf{x} \cdot \mathbf{y} $ ).
Thus mapping from one orthonormal basis $$ [ \mathbf{e}_{1},\mathbf{e}_{2},\mathbf{e}_{3}] $$ with $ \mathbf{e}_{i} \cdot \mathbf{e}_{j} = \delta_{ij} $ gives another $$ [R \mathbf{e}_{1}, R \mathbf{e}_{2}, R \mathbf{e}_{3}] $$ with $ R\mathbf{e}_{i} \cdot R \mathbf{e}_{j} = \delta_{ij} $, as dot product is preserved.
Morever, this means the lengths of all the vectors and the angles between them are preserved.
Alright, nearly there. So why does $ | Q | = 1 $ mean it must be a rotation?
Suppose $ \mathbf{e}_{1},\mathbf{e}_{2},\mathbf{e}_{3} $ form a right-handed set, ie. they are orientated such that
$$ \mathbf{e}_{1} \cdot (\mathbf{e}_{2} \times \mathbf{e}_{3}) = 1 $$
(We know this is one as being orthonormal, they are unit vectors after all)
Knowing the relation between the determinant and the scalar triple product for a 3 x 3 matrix, , if $ | R | = 1 $ it follows that
$$ R \mathbf{e}_{1} \cdot ( R \mathbf{e}_{2} \times R \mathbf{e}_{3} ) = 1 $$
So it is clear that these vectors are orientated in the same way, hence this transformation must be a rotation. (A similar argument can show $|R| = -1$ gives a reflection).
Does that all make sense? Have I missed anything?
I'm interested in seeing a nicer argument if people have got one.
Also, it's tricky to see how this generalises to $n$ dimensions; shedding some light there would be appreciated.
EDIT
I have looked at this question, A proof that an orthogonal matrix with a determinant 1 is a rotation matrix but as you can see the argument is pretty bare… so this is not a duplicate.
Best Answer
A (real) $3 \times 3$ orthogonal matrix $Q$ with determinant $1$ has a characteristic polynomial $P(\lambda) = \det(Q - \lambda I)$ that is a cubic with real coefficients. The coefficient of $\lambda^3$ is $-1$, while the constant coefficient is $\det(Q) = 1$, therefore there must be a positive eigenvalue, and (because $Q$ preserves lengths) this positive eigenvalue can only be $1$. Thus there is some nonzero vector $w$ that $Q$ leaves fixed. Since $Q$ preserves the dot product, it maps the plane $V$ orthogonal to $w$ to itself. The restriction of $Q$ to $V$ corresponds to $2 \times 2$ orthogonal matrix with determinant $1$. From analysis of $2 \times 2$ orthogonal matrices with determinant $1$, we find that $Q$ acts as a rotation on $V$. Thus $Q$ is a rotation of $3$-dimensional space around an axis in the direction of $w$.