Microsoft Excel: Build pivot tables and charts of survey response data
We are creating a marketing research report based on interview data we collected from 120 unique business manager respondents. There are about 80 unique questions in total but the survey had branches, so each respondent only answered approximately 40 questions each. Respondents were in two different job roles across six different industries (12 subgroups total) Questions are mostly multiple-choice, numeric scores or short responses.
We have three Excel files of survey response data (one of the files has cross-industry data for job type A, the second has the same data for job type B and the third covers about 10 questions we asked both job types). The Excel files came from Survey Monkey, with some minor cleanup of column labels by us so pivot tables would work.
We're writing a report on this data. The actual report text and layout is not part of this job, but we need relatively simple tabulation of the data and for some questions, charting of the different responses. For example, "what was the numeric average satisfaction rating for question X across each of the response groups", "for multiple-choice question Y, how many people gave answer A, how many gave answer B, how many A and B, etc?" and "here's a nice pie or bar chart of that data". We don't anticipate a need for complex cross-tabulation, standard deviation, confidence intervals, etc.
We did this work in-house for prior reports but are looking to add skills and hands to hit deadlines. The Excel files are independent of each other and we'd like to process them one by one. We would like to have one resource do all the reporting, but we could break it up based on availability and progress.
Inputs for the job include the Excel files, a copy of the original survey (for context), chart format preferences, samples of our prior report charts and we're available for email/phone consultation as needed.
The deliverables for this job include finished Excel sheets/pivot tables/charts tied to a common Excel sheet of source data. The final list of tables and questions will iterate somewhat based on initial results, but it's realistic to expect a pivot report for each question (cosmetics may not matter) and perhaps 15-20 presentable Excel charts that confirm to agreed-on chart types/consistent color scheme. These should be linked to the source sheet so we can add new data rows (from a few pending responses) or cleaned-up column labels to the source sheet and then refresh the data behind the previously-built tables.
For reference, samples of prior pivot tables/chart we developed in-house are attached. The tables were used as an internal reference, the chart portions were used as shown. A similar deliverable from the new Excel files would be acceptable.