Scribd
Upload
14 views2 pages

Excel Data Analysis Assessment Guide

Uploaded by

devesh.ghodpage2020
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
14 views2 pages

Excel Data Analysis Assessment Guide

Uploaded by

devesh.ghodpage2020
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Data Analysis Assessment questions

Note:

Please be advised that these assessment inquiries are designed to accommodate interns with diverse skill levels,
ranging from novices to seasoned analysts. Should you encounter any challenging questions, you are encouraged to
seek solutions independently or reach out to us for assistance at intern@[Link]. Best wishes for success
in completing the assessment!

Tools needed :- MS Excel


1. Data Import and Cleanup:

- Import the employee surveys dataset into Excel.

- Identify and handle any missing or inconsistent data.

2. Data Validation:

- Implement data validation for the "Response" column to ensure that values are within the range 0 to 4.

3. Conditional Formatting:

- Apply conditional formatting to highlight responses with "Strongly Disagree" in a distinctive color.

4. IF and Nested IF Functions:

- Use the IF function to categorize responses in a new column as "Positive," "Neutral," or "Negative"
based on the numeric scale.

5. VLOOKUP and HLOOKUP:

- Create a new sheet with a summary table that uses VLOOKUP and HLOOKUP to display responses for a
specific question and department.

6. Pivot Tables - Basic:

- Generate a Pivot Table to analyze the average response values for each department.

- Include slicers for easy filtering by department.

7. Pivot Tables - Calculated Fields:

- Enhance the previous Pivot Table by adding a calculated field to compute the overall average response
for each question.

1|Page
8. Pivot Charts:

- Create a Pivot Chart based on the Pivot Table created in task 7.

- Customize the chart to represent the average responses visually.

9. Advanced Formulas - INDEX-MATCH:

- Use INDEX-MATCH to retrieve the "Response Text" for a given response value in a separate sheet.

10. Data Analysis with Tables:

- Convert the dataset into a Table and perform data analysis tasks using Table functionalities

11. Scenario Manager:

- Use Scenario Manager to analyze different scenarios for response values, such as optimistic, pessimistic,
and realistic.

12. Solver Add-In:

- Utilize the Solver Add-In to optimize response values to achieve a target overall average while
considering constraints.

13. Advanced Charting - Waterfall Chart:

- Create a Waterfall Chart to visualize the cumulative effect of positive and negative responses for a
specific department.

14. Dynamic Named Ranges:

- Implement dynamic named ranges for the dataset to automatically expand as new survey responses are
added.

15. Data Model Relationships:

- Establish relationships between tables within Excel to facilitate more complex analysis.

16. DAX Formulas - Measures:

- Write DAX formulas within Excel to create measures that calculate the overall average response across
all questions

17. What-If Analysis - Goal Seek:

- Use Goal Seek within Excel to determine the required change in responses to achieve a specific overall
average.

18. Advanced Dashboard:

- Create an advanced dashboard using only Excel features, with interactive elements (e.g., dropdowns,
buttons) for dynamic exploration of survey data.

2|Page

Common questions

Powered by AI

The Solver Add-In can optimize survey response values by adjusting inputs to achieve a desired overall average while respecting constraints, such as departmental response quotas or limiting changes to specific questions. This enables goal-oriented data modeling to improve survey outcomes or target specific improvements without breaching set boundaries .

INDEX-MATCH is often favored over VLOOKUP for its flexibility and performance advantages. INDEX-MATCH can look up values in any column, not just the first, and works efficiently with large datasets. It can also handle both horizontal and vertical data lookups, making it a more versatile choice for retrieving survey response text based on numeric values, especially in complex datasets .

Establishing relationships between tables in Excel allows for integrated and complex analyses by linking related data sets, such as connecting survey responses with demographic information. This enables comprehensive insights through cross-referenced analyses and can reveal trends or patterns not visible within isolated datasets .

Scenario Manager in Excel can evaluate various hypothetical cases such as optimistic, pessimistic, and realistic scenarios for survey responses. By adjusting response values and conditions, analysts can compare the effects of these scenarios on overall satisfaction indices and prepare for potential outcomes by visualizing changes in response distributions .

An advanced dashboard with interactive elements like dropdowns and buttons in Excel allows for dynamic exploration of survey data by enabling users to filter and switch views easily. This interactivity supports real-time data manipulation and visualization, aiding user engagement by providing tailored insights based on specific analysis needs or inquiries .

DAX formulas in Excel create powerful measures that enhance survey data analysis by facilitating advanced calculations, such as overall averages or specific performance indices across all questions. By leveraging DAX, analysts can derive more precise insights, support dynamic metrics, and allow for detailed comparison across multiple dimensions .

Data validation in Excel can enforce data integrity by restricting the range of values that can be entered into the "Response" column. For instance, setting up data validation to allow only values between 0 and 4 ensures that inputs align with predefined survey response options, preventing errors and inconsistencies within the data .

A Pivot Table with calculated fields allows users to conduct more complex analyses by deriving new metrics from existing data. In the context of survey data, adding a calculated field to compute the overall average response for each question provides deeper insights and enables comparisons across departments. This enhances data insights by facilitating a higher-level view of trends and highlighting areas of concern or satisfaction .

Goal Seek in Excel is useful for determining necessary changes in survey responses to achieve a predefined overall average. By iterating different response adjustments, it identifies specific data points requiring modification to meet targets, aiding decision-making for data-driven improvements .

Dynamic named ranges in a survey dataset ensure automatic adjustments to include new data entries without manual updates. This maintains the accuracy of data analyses and enhances scalability by allowing seamless integration of new survey responses, ensuring that reports and calculations reflect the most current data .

You might also like