Scribd
Upload
226 views2 pages

Excel Data Analysis Practice Questions

The document outlines practical data analysis questions to solve using Microsoft Excel, categorized into basic, intermediate, and advanced levels. It includes tasks related to sales analysis, student scores, employee attendance, customer orders, budget vs actual expenses, survey data, trend forecasting, profitability analysis, and HR data. Each question aims to enhance skills in sorting, filtering, formulas, charts, and PivotTables.

Uploaded by

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

Excel Data Analysis Practice Questions

The document outlines practical data analysis questions to solve using Microsoft Excel, categorized into basic, intermediate, and advanced levels. It includes tasks related to sales analysis, student scores, employee attendance, customer orders, budget vs actual expenses, survey data, trend forecasting, profitability analysis, and HR data. Each question aims to enhance skills in sorting, filtering, formulas, charts, and PivotTables.

Uploaded by

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

Here are several practical data analysis questions you can solve using Microsoft Excel, ideal

for practicing key skills like sorting, filtering, using formulas, charts, and PivotTables:

Basic Level Questions

1. Sales Analysis
o Given a dataset of monthly sales for different products, answer:
 What is the total sales amount?
 Which product sold the most units?
 What is the average monthly sales for Product A?
2. Student Scores
o Given a dataset of students' scores in Math, English, and Science:
 Who scored highest in each subject?
 What is the average score per student?
 How many students passed all subjects (cutoff = 50)?
3. Employee Attendance
o Given a daily attendance sheet:
 Which employee has the highest attendance?
 How many days did each employee miss work?
 What is the attendance percentage for each employee?

Intermediate Level Questions

4. Customer Orders Analysis


o Given customer orders with Date, Product, Region, and Quantity:
 What is the total quantity sold in each region?
 Create a PivotTable to summarize total orders by product.
 Plot a line graph to show sales trend over time.
5. Budget vs Actual Expenses
o Given monthly budgeted and actual expenses:
 Where did the actual expenses exceed the budget?
 What is the total variance for the year?
 Create a bar chart comparing budget vs actual.
6. Survey Data Summary
o Analyze data from a customer satisfaction survey:
 What percentage rated the service as Excellent?
 What is the average satisfaction rating per age group?
 Use conditional formatting to highlight low ratings.
Advanced Level Questions

7. Trend Forecasting with LINEST or TREND


o Given monthly sales for 2 years:
 Use a trendline to forecast next 3 months’ sales.
 What is the growth rate month-over-month?
8. Profitability Analysis
o Dataset includes Product, Cost Price, Selling Price, Units Sold:
 Calculate profit per product.
 Which product is the most profitable?
 Create a chart showing profit margins.
9. PivotTable for HR Data
o Given employee data (Department, Gender, Salary, Years of Experience):
 What is the average salary per department?
 Gender distribution in each department?
 Years of experience vs. salary: any correlation?

Common questions

Powered by AI

Apply conditional formatting by selecting the survey data cells. Go to the 'Home' tab and click on 'Conditional Formatting'. Choose 'Highlight Cell Rules' and then 'Less Than'. Enter a threshold value that signifies low ratings and select a formatting style such as red fill. This will highlight all cells with a satisfaction rating below the specified threshold, visually identifying areas of concern.

In Excel, use the AVERAGEIF function to calculate the average satisfaction rating per age group. First, ensure your dataset includes both 'Age Group' and 'Satisfaction Rating' columns. Set up the AVERAGEIF formula to identify ranges corresponding to each age group and calculate the mean satisfaction score for each group. This provides insights into which age group has higher or lower overall satisfaction levels.

Use Excel's 'Data Analysis' tool to perform correlation analysis between years of experience and salary. First, ensure both data columns are properly organized. Navigate to the 'Data' tab, select 'Data Analysis', and choose 'Correlation'. Input the relevant data range to generate a correlation coefficient which indicates the strength and direction of the relationship between these variables. Visualize using scatter plots for better interpretation.

In Excel, create a column to calculate the difference between the actual and budgeted expenses (actual - budgeted) for each month. Use the SUMIF function to find the total amount where these differences are positive, indicating months where actual expenses exceeded the budget. highlight these differences using conditional formatting to easily identify discrepancies. Aggregate annual totals if necessary for comprehensive evaluation.

To forecast sales for the next three months with Excel, use either the LINEST or TREND function. First, organize your data in a spreadsheet with monthly sales figures. Then, apply the TREND function to fit a trendline through your existing data points to predict future values. For more sophisticated analysis, use LINEST for linear regression analysis which will give coefficients to apply in predicting future sales.

To create a PivotTable in Excel summarizing total orders by product, first select your dataset including column headers. Go to the 'Insert' tab and choose 'PivotTable'. In the Create PivotTable window, select the range and decide on a new worksheet or existing one for placement. Drag the 'Product' field to the Rows area and 'Orders' to the Values area. The PivotTable will display a summary of total orders for each product.

To calculate profit per product in Excel, create a new column for profit calculation. Use the formula (Selling Price - Cost Price) * Units Sold for each product to determine the profit. Enter the formula in the first row and apply it across all rows. This will compute the profit per product based on provided sales data. Use SUM for total profits or sort to identify the most profitable product.

To determine which product has the highest unit sales, you can use Excel's sorting and filtering features. First, enter the dataset into a spreadsheet. Sort the data by the product column and then by the units sold column in descending order. This will position the product with the highest units sold at the top. Alternatively, use the MAX function to find the highest units sold value and MATCH or INDEX to identify the corresponding product.

Create a bar chart to visually compare budget versus actual expenses in Excel. Organize your data with columns for months, budget, and actual expenses. Highlight the data and go to 'Insert' -> 'Chart' -> 'Bar Chart.' Select either clustered or another bar chart type that best represents your data. This approach allows clear visual comparison of each month's spending against its budget.

Use the SUMIF function to calculate the total quantity sold in each region. Identify the 'Region' and 'Quantity Sold' columns in your dataset. Apply the SUMIF formula, specifying the conditions to aggregate quantities based on each unique region value. This method consolidates data to reveal regional sales quantity totals succinctly.

You might also like