Maybe the following observation is useful for an intuitive understanding. As said before, a matrix A is orthonormal (often called "orthogonal") iff A^T A = I which means that the columns a_1,...,a_n of A form an orthonormal basis (perpendicular and with length one). But the columns of a matrix A are the images of the standard basis e_1,...,e_n, that means a_j = Ae_j. Hence a matrix is orthogonal iff the image of the standard orthonormall basis is an orthonormal basis again. This means that the orthonormal matrices are precisely those which preserve the metric geometry (length and angles) since the metric geometry is expressed the same way in any orthonormal coordinate system. Another way to see this: If = x^Ty denotes the euclidean inner product, then for any orthonormal matrix A we have
= (Ax)^T Ay = x^T A^T A y = x^T y = .
Thus A is orthonormal if and only if the inner product (which determines. the metric geometry) is preserved by A. The orthogonal group (group of orthogonal matrices) is up to translations the invariance group of euclidean geometry, the "congruence group" - therefore its great importance. Hope that these remarks improve understanding.
There is an even more basic structure theorem that is almost never mentioned. Any matrix (linear operator) m can always be decomposed into its SPECTRAL BASIS of mutually annihilating idempotents p_i and nilpotents q_i,
m = sum_i lamba_i p_i + q_i
determined by its characteristic polynomial, with eigenvalues lambda_i. The matrix m is orthogonal iff p_i^* = p_i^*, lambda_i lambda_i^* = 1 and the q_i = 0. See my paper
the set of n vectors is a basis if the space dimension is n : as all are relatively orthogonal, we can see intuitively the matrix as a kind of rotations combined with symmetries (simply the opposite) of the canonical basis.
Or a way to arrange n points on the n-sphere of radius 1 with constraints equivalent to the orthogonality.