Export data to Excel
In this walk through you will see how to export data and then convert it into graphs and table charts
Last updated
In this walk through you will see how to export data and then convert it into graphs and table charts
Last updated
(c) 2024 Data Illusion Zumbrunn. All rights reserved. Reproduction strictly forbidden.
Go to Data Export and click on the small arrow icon next to the Excel format option to generate the data export file for download.
After loading and opening the file, transform your data set into a table format by pressing Ctrl+T and then clicking OK.
In Excel, to create a simple statistical table, use the Data Analysis tool located in the upper right corner under the Data ribbon.
If the tool is not activated, follow these steps:
Go to File: Navigate to the bottom and select Options.
Choose Add-ins: In the left panel, click on Add-ins.
Select Analysis ToolPak: Find and select Analysis ToolPak in the list.
Press Go: Click the Go button to continue.
Add-ins Window: In the Add-ins window, check Analysis ToolPak and press OK.
With the Data Analysis tool, you can create simple descriptive statistics, such as a data summary.
For this walkthrough, we will focus on the column General_satisfaction, which represents the survey question, "How would you rate your overall satisfaction with our service?" The responses range from 1 (Very unsatisfied) to 5 (Very satisfied).
Open Data Analysis: In the Data Analysis tool, select Descriptive Statistics.
Set Input Range: Specify the Input Range that corresponds to the column you want to analyze, such as General_satisfaction.
Choose Output Range: Define the Output Range where you want the statistical summary to appear on your Excel sheet.
Select Summary Statistics: Check the Summary statistics option and click OK to generate the statistical summary.
This summary provides a quick understanding of overall customer satisfaction and its distribution:
Average Rating (Mean): The average rating for overall satisfaction is 3.04 out of 5.
Variability (Standard Deviation): Ratings vary widely around the mean, with a standard deviation of approximately 1.40, indicating diverse opinions.
Mode: The most frequent rating for overall satisfaction is 3 ("Neutral" ), reflecting the typical response.
Number of Responses (Count): This summary is based on feedback from 205 respondents, providing a comprehensive view of satisfaction levels.
To create a visualization:
Select the input range of data (e.g., column or rows) that you want to visualize, such as General_satisfaction.
Navigate to the Insert tab.
Choose the appropriate graph type for your data. For example, select Boxplot (or Box and Whisker Plot) to visualize General_satisfaction.
This visualization complements the summary statistics by visually representing the central tendency and distribution of satisfaction levels among respondents.
Mean: The plot displays the mean satisfaction rating prominently at 3.04, shown as a line within the box.
Distribution: It shows the range of ratings from 1 to 5 (Minimum and Maximum in the summary) using the box and whisker format:
Where the box spans from the 25th to the 75th percentile (2 to 4), indicating where the middle 50% of ratings fall.
The absence of the outliers indicates that all the answers fall within the given range.
Symmetry: The symmetrical distribution of the box plot suggests that roughly equal numbers of people rated their satisfaction as low, moderate, and high. In other words, there's no strong tendency towards extreme opinions—like everyone being very satisfied or very dissatisfied.
When you dive deeper into visualizations, like breaking down box plots into different groups, you can spot unique trends and differences between segments.
To explore subgroup dynamics using a box plot:
Right-click on the box plot and select Select Data.
In the Select Data Source dialog box, under the Horizontal (Category) Axis Labels, click Edit.
Select the column range that represents the subgroups you want to explore. In this walkthrough, we're using the column that reflects client tenure, based on the survey question "How long have you been using our service?" The answers range from Less than 1 month to More than 2 years.
Key insights:
Less than 1 month, 1-6 months, 6-12 months: Satisfaction is moderate and steady with no extremes for new customers.
1-2 years: Shows more variability with some lower satisfaction, indicating diverse experiences.
More than 2 years: High satisfaction with a strong trend towards maximum ratings for long-term customers.
Satisfaction increases with longer service use. However, overall satisfaction is moderate, suggesting room for improvement, especially for those in the 1-2 year range.
