Sometimes, we need to perform Surveys on some criteria among different groups of people. This practice is also prevalent in many companies so that they can improve the work environment. After completing the survey, we prefer to store the results in an Excel workbook. Because with Excel, it’s very easy to understand and analyze the results. It also offers a visualization of the Survey Results so that viewers can understand better. In this article, we’ll show you the step-by-step procedures for Display Survey Results in Excel.
Download Practice Workbook
Download the following workbook to practice by yourself.
Step by Step Procedures to Display Survey Results in Excel
With only texts and numbers, people may find it difficult to grasp the total survey result. So, it’ll be beneficial to display the result by some means of charts or graphs. This article will guide you through the steps below to perform the task. Therefore, go through the following steps carefully.
STEP 1: Input Survey Results in Excel
- Firstly, input the Survey Results in an Excel worksheet.
- Here, make the necessary columns and rows as per your requirement.
- The following picture demonstrates an example of the Employee Satisfaction Survey conducted in a company.
STEP 2: Create Data Preparation Table
Now, we’ll create another table from which we’ll take the data to plot a chart. So, follow the process below.
- Copy the range B4:G9 by pressing the Ctrl and C keys together at first.
- Then, select the range B11:G16 and paste it there by using the Paste Link feature. In this way, any updates in the main table will also affect the chart that we’ll create later.
- After that, insert a Minus sign before the cell references in columns C and D. look at the below figure for a clear understanding.
- Subsequently, select the range F11:G16 and drag it to the next column.
- As a result, you’ll get a blank column F.
- Now, select cell F11 and type the formula:
- Next, press Enter and use AutoFill to complete the rest.
- Consequently, it’ll return another Neutral column.
- Afterward, in cell E12, modify the formula:
- Press Enter and fill the rest with AutoFill.
- Again, in cell F12, modify the formula:
- Accordingly, press Enter and use AutoFill.
- Hence, our Data Preparation table is completed.
STEP 3: Insert Excel Stacked Bar Chart to Display Survey Results
It’s time to Insert a Stacked Bar Chart next. This chart will Display Survey Results. Therefore, learn the process.
- First of all, select the range B11:H16.
- Then, go to the Insert tab.
- There, choose your desired 2-D Bar chart.
- Thus, you’ll get a chart as it’s shown below.
STEP 4: Switch Row & Column
The chart demonstrated above is not clear and may create confusion. That’s why we’ll modify the chart.
- First, select the chart.
- Next, go to the Chart Design tab and select Switch Row/Column.
- Subsequently, select the Y-axis labels and press Ctrl and 1.
- As a result, you’ll get the Format Axis pane.
- Choose Low from the Label Position drop-down.
- Hence, it’ll return a clearer chart. See the figure below.
STEP 5: Adjust Excel Data Preparation Table
However, our chart is still not complete to present to someone. You can see that the Neutral section is on the leftmost side where the Firmly Disagree is supposed to be. So, we need to make some adjustments.
- In the beginning, exchange the range C11:C16 with E11:E16 using the original formulas.
- Therefore, you’ll see that the Firmly Disagree section and Neutral section have swapped their positions in the chart.
STEP 6: Edit Color Scheme
So many different colors in the chart may create some disturbances when analyzing the data. Thus, it’s better to stick to some distinct colors that may soothe the viewer’s eye.
- Select each color section and format the Fill color section as per your wish.
- In this example, choose a Light Orange color for Neutral, Orange for Disagree, Deep Orange for Firmly Disagree, Light Green for Agree, and Green for Firmly Disagree.
STEP 7: Update Legend
Lastly, we have another issue to handle. We have to make some changes to the X-axis Legend so that it precisely conveys the chart message.
- First, click the Legend It’ll activate the Legend.
- Click again on the Neutral (leftmost) reference.
- Press Delete.
- Now, insert a blank Disagree column by dragging the range F11:H16 one column to the right.
- After that, copy the blank range F11:F16 and paste it into the chart.
- This won’t affect the main chart display section. But, a new Disagree reference will appear in the Legend.
- Next, right-click on the chart and choose Select Data from the context menu.
- As a result, the Select Data Source dialog box will pop out.
- There, select Disagree (the last one in the list) and move to the position between Firmly Disagree and Neutral by pressing the upper arrow icon. The following picture depicts the process clearly.
- At last, press OK.
- Thus, you’ll get the following figure where our Legend order is accurate.
- Lastly, select Disagree present on the leftmost side in the Legend and press Delete.
- Finally, you’ll get a precise Display of Survey Results in Excel.
Henceforth, you will be able to Display Survey Results in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.