Did you find the work to be intellectually satisfying and creative, or was it somewhat rote in the sense of having to spend lots of energy on data hygiene?
I found it to be very intellectually satisfying and creative! But there were parts to the job which were also very boring, including cleaning data. Cleaning data took up probably about as much time as all the fun analysis and exploration.
What software is used in the cleaning and analysis?
By hand one Excel cell at a time :) It's why they get paid well.
I never used Excel, except to present reports. Data warehousing and analysis all took place with SQL and an RDBMS.