Conversion Between the Cartesian and Spherical Bases

The spherical harmonics of a particular rank are covariant components of an irreducible tensor. This can be used to find the prescription for converting between the spherical and Cartesian bases.

This loads the package with coordinate systems. (Quiet suppresses some shadowing warnings that will occur if the ADM package is already loaded.)
Here are the spherical harmonics of rank 1 in terms of the angular coordinates θ and ϕ.
Here are rules for converting the angular coordinates to Cartesian coordinates.
The spherical harmonics in terms of Cartesian coordinates, with a new normalization.

The spherical harmonics are still in the spherical basis, but they are written in terms of the coordinates x, y, and z. To put them in the Cartesian basis, we want to find a linear (unitary) transformation whose result transforms like a Cartesian vector, i.e., like {x,y,z}.

Find a unitary matrix that transforms the spherical harmonics to {x,y,z}.

The ADM package has functions that use this matrix to convert between the spherical and Cartesian bases.

ToCovariant[expr]convert expr to a covariant spherical tensor
ToContravariant[expr]convert expr to a contravariant spherical tensor
ToCartesian[expr]convert expr to Cartesian basis

Converting between standard and Cartesian bases.

Load the ADM package.
Convert the Cartesian components of a vector to covariant spherical components.
Convert to contravariant spherical components.
ToCartesian converts either set back to Cartesian components.
Convert the rank-1 spherical harmonics to the Cartesian basis.
Plot them. They clearly will transform like the Cartesian basis vectors.

The conversion functions also work on vectors whose components are operators.