Excel Assignment 4: Complete Beginner's Guide

Automated Employee Performance Dashboard

📋 What You'll Learn

Power Query (importing and combining data)
Advanced formulas (IFS, XLOOKUP, IFERROR)
Sparklines (mini charts in cells)
Goal Seek (what-if analysis)
Macros (automation)

⏱️ Estimated Time: 2-3 hours

PART 1: CREATING THE CSV FILES

Step 1: Create Employee_Info.csv
For Windows Users:
1. Open Notepad
Press Windows Key + R
Type notepad and press Enter
2. Type this EXACTLY (including commas):

EmployeeID,FullName,Department
E101,Riya Sharma,Sales
E102,Ben Carter,Marketing
E103,Chloe Davis,Sales
E104,David Lee,Marketing

3. Save the file

Click File → Save As
In "File name", type: Employee_Info.csv
In "Save as type", select All Files (*.*)
Choose your Desktop or a folder you can find easily
 Click Save

For Mac Users:

1. Open TextEdit
Press Command + Space
Type TextEdit and press Enter
2. Make it plain text
Click Format → Make Plain Text
3. Type the same data as above
4. Save the file
Click File → Save
Name it: Employee_Info.csv
Choose Desktop
Click Save

Step 2: Create Quarterly_Sales.csv

Follow the same process but with this data:

EmpID,Q1_Sales,Q2_Sales
E101,75000,82000
E102,55000,58000
E103,95000,99000
E104,48000,51000

Save it as: Quarterly_Sales.csv

✅ Checkpoint: You should now have 2 CSV files on your Desktop

PART 2: POWER QUERY - IMPORTING DATA

Step 3: Open Excel and Import First File
1. Open Excel
Open a blank workbook
2. Go to Data Tab
 Click the Data tab at the top (between "Formulas" and "Review")
3. Import Employee_Info.csv
Click Get Data (or Get & Transform Data in some versions)
Hover over From File
Click From Text/CSV
4. Find your file
Navigate to where you saved Employee_Info.csv
Click on it
Click Import
5. Preview Window appears
You'll see a preview of your data
Click the Transform Data button (NOT "Load")
What just happened? You opened Power Query Editor - a special tool for cleaning data

Step 4: Import Second File in Power Query

You should now be in Power Query Editor (different window from Excel)
1. Import the second CSV
In Power Query Editor, click New Source (top left area)
Click File → Text/CSV
Find and select Quarterly_Sales.csv
Click OK
2. You should see both queries
Look at the left panel
You should see:
Employee_Info
Quarterly_Sales

✅ Checkpoint: Both files are now loaded in Power Query

Step 5: Merge the Two Tables

Why merge? We need to combine employee info with their sales data
1. Select Employee_Info query
 Click on Employee_Info in the left panel
2. Start the merge
Click Home tab (in Power Query)
Click Merge Queries dropdown (has a down arrow)
Select Merge Queries as New
3. Set up the merge
A dialog box appears with two tables
Top table: Should show Employee_Info (already selected)
Bottom dropdown: Select Quarterly_Sales
4. Create the connection
Click on the EmployeeID column header in the TOP table
Click on the EmpID column header in the BOTTOM table
Both should be highlighted
At the bottom, select Left Outer join kind
Click OK

What just happened? You created a new query that combines both tables based on matching
employee IDs

Step 6: Expand the Merged Data

1. Find the new merged column
You'll see a column called Quarterly_Sales with "Table" written in cells
There's a small icon with two arrows (↔) in the column header
2. Click the expand icon (↔)
A menu drops down showing available columns
You'll see: EmpID, Q1_Sales, Q2_Sales
3. Select what to expand
Uncheck the box that says "Use original column name as prefix"
Keep Q1_Sales and Q2_Sales checked
Uncheck EmpID (we already have EmployeeID)
Click OK

What just happened? The sales data is now part of your main table!
Step 7: Add a Total_Sales Column
1. Add Custom Column
Click the Add Column tab (at the top)
Click Custom Column
2. Create the formula
In "New column name", type: Total_Sales
In the formula box, type EXACTLY:

=[Q1_Sales]+[Q2_Sales]

Click OK

Important notes:
The equal sign = must be there
Column names must be in square brackets [ ]
No spaces around the + sign is fine

Step 8: Load Data to Excel

1. Close & Load
Click the Home tab
Click Close & Load (big button on the left)
2. Excel reopens
You'll see your merged data in a table
The sheet might be named "Merge1"
3. Rename the sheet
Right-click on the sheet tab (bottom of screen)
Click Rename
Type: Performance_Report
Press Enter

✅ Checkpoint: You should have a table with columns: EmployeeID, FullName, Department,
Q1_Sales, Q2_Sales, Total_Sales
PART 3: CREATING THE BONUS LOOKUP TABLE
Step 9: Create Bonus Table
1. Click on a cell to the right of your data
Click on cell H2 (or any cell with space to the right)
2. Type the headers
In H2 , type: Target
In I2 , type: Bonus %
Press Enter
3. Type the values
In H3 , type: 100000
In I3 , type: 5%
In H4 , type: 150000
In I4 , type: 8%
In H5 , type: 180000
In I5 , type: 12%
4. Format as percentage (if not showing as %)
Select cells I3:I5
Right-click → Format Cells
Select Percentage
Click OK

Your bonus table should look like this:

Target Bonus %

100000 5%

150000 8%

180000 12%
PART 4: ADVANCED FORMULAS
Step 10: Add Rating Column with IFS Function
Understanding IFS:
IFS checks multiple conditions and returns a result for the first TRUE condition.
Syntax: =IFS(condition1, result1, condition2, result2, ...)
1. Add the column header
Click on cell G1 (the column next to Total_Sales)
Type: Rating
Press Enter
2. Click on cell G2 (first data cell under Rating)
3. Type this formula EXACTLY:

excel

=IFS(F2>180000,"Excellent",F2>150000,"Good",F2>100000,"Meets Expectations",TRUE,"
 

4. Press Enter
5. Copy the formula down
Click on cell G2 again
Move your mouse to the bottom-right corner of the cell
You'll see a small black square (fill handle)
Click and drag down to copy to all employee rows

Formula Breakdown:
F2>180000,"Excellent" → If total sales > 180000, show "Excellent"
F2>150000,"Good" → Else if > 150000, show "Good"
F2>100000,"Meets Expectations" → Else if > 100000, show "Meets Expectations"
TRUE,"Needs Improvement" → Otherwise, show "Needs Improvement"

Note: Adjust F2 if your Total_Sales column is different!

Step 11: Add Bonus_Amount Column with XLOOKUP and IFERROR
Understanding the formula:
XLOOKUP: Finds a value in a list and returns a corresponding result
IFERROR: If XLOOKUP fails (no match), return 0 instead of error

1. Add the column header

Click on cell H1
Type: Bonus_Amount
Press Enter
2. Click on cell H2
3. Type this formula EXACTLY:

excel

=IFERROR(XLOOKUP(F2,$H$3:$H$5,$I$3:$I$5,,-1)*F2,0)

4. Press Enter
5. Copy down to all employee rows (same as before)

Formula Breakdown:
XLOOKUP(F2,$H$3:$H$5,$I$3:$I$5,,-1) → Looks up F2 (Total_Sales) in the Target
column and returns the matching Bonus %
The -1 means "find the next smallest value" (approximate match)
Dollar signs $ keep the bonus table reference fixed
*F2 → Multiply the bonus percentage by Total_Sales
IFERROR(...,0) → If no match found, return 0

Important:
Replace $H$3:$H$5 and $I$3:$I$5 with the actual cell ranges where you created your
bonus table
Replace F2 with your actual Total_Sales cell
PART 5: SPARKLINES AND GOAL SEEK
Step 12: Add Sparklines (Mini Charts)
What are Sparklines?
Tiny charts that fit in a single cell showing trends
1. Add column header
Click on cell I1 (or next empty column)
Type: Sales_Trend
Press Enter
2. Insert Sparkline for first employee
Click on cell I2 (under Sales_Trend)
Click Insert tab
Click Line in the Sparklines group
3. Set up Sparkline
A dialog appears
Data Range: Click and drag to select D2:E2 (Q1_Sales and Q2_Sales for first
employee)
Location Range: Should show $I$2
Click OK
4. Copy Sparkline down
Click I2
Use the fill handle (drag corner) to copy down to all employees
Each employee gets their own mini chart!

✅ Checkpoint: You should see small line charts showing if sales went up or down

Step 13: Calculate Total Bonus Payout

1. Find an empty cell
Click on cell H7 (below your data)
2. Type a label
In G7 , type: Total Bonus Payout
3. Create SUM formula
In H7 , type:
 excel

=SUM(H2:H5)

Press Enter
(Adjust range if your Bonus_Amount column is different)

This cell now shows the total of all bonuses

Step 14: Goal Seek - Find Required Q2 Sales

What is Goal Seek?
A tool that works backwards to find what input value you need to reach a specific result
Goal: Find what Riya Sharma's Q2 sales need to be for her bonus to equal ₹10,000
1. Find Riya Sharma's row
Look for E101 - Riya Sharma (should be row 2)
2. Open Goal Seek
Click the Data tab
Click What-If Analysis (in Forecast group)
Click Goal Seek...
3. Fill in the Goal Seek dialog:
Set cell: Click on Riya's Bonus_Amount cell (H2)
To value: Type 10000
By changing cell: Click on Riya's Q2_Sales cell (E2)
4. Click OK
5. Goal Seek Status appears
It shows the solution
Click OK to accept

What happened? Excel changed Riya's Q2_Sales to calculate what she'd need to earn exactly
₹10,000 bonus
PART 6: MACROS - AUTOMATION
Step 15: Enable Developer Tab (if not visible)
1. Open Excel Options
Click File → Options
2. Customize Ribbon
Click Customize Ribbon on the left
3. Enable Developer
On the right side, check the box for Developer
Click OK

Now you'll see a Developer tab!

Step 16: Record a Macro

What is a Macro?
A recorded sequence of actions that you can replay with one click
1. Start recording
Click the Developer tab
Click Record Macro
2. Name your macro
Macro name: ApplyReportStyle (no spaces!)
Click OK
🔴 RECORDING STARTED - Everything you do now is being recorded!
3. Perform these actions IN ORDER: a) Format header row:
Click on row number 1 (entire first row selected)
Click Home tab
Click the Fill Color button dropdown (paint bucket icon)
Choose a dark blue color
Click the Font Color button dropdown (A with color bar)
Choose white
Click the Bold button (B)
b) Auto-fit columns:
Click on any cell in your data table
 Press Ctrl+A (selects entire table)
Click Home tab
Click Format (in Cells group)
Click AutoFit Column Width
c) Click anywhere to deselect
4. Stop recording
Click Developer tab
Click Stop Recording

✅ Your macro is saved!

Step 17: Create a Macro Button

1. Insert a shape
Click the Insert tab
Click Shapes
Choose the first rectangle shape
Click and drag on your worksheet to draw a button (make it medium-sized)
2. Add text to the shape
Right-click the shape
Click Edit Text
Type: Format Report
Click outside the shape
3. Assign the macro
Right-click the shape again
Click Assign Macro...
Select ApplyReportStyle from the list
Click OK
4. Test your button!
Click on the button
Your formatting should apply automatically!
PART 7: SAVE AND SUBMIT
Step 18: Save as Macro-Enabled Workbook
CRITICAL: Regular Excel files (.xlsx) cannot save macros!
1. Save the file
Press Ctrl+S (or File → Save As )
2. Choose location
Select Desktop or your assignment folder
3. Choose file type
Click the Save as type dropdown
Select Excel Macro-Enabled Workbook (*.xlsm)
4. Name your file
Type: Assignment_4_YourName.xlsm
Click Save

Step 19: Take a Screenshot

Windows:
1. Open your Performance_Report sheet
2. Press Windows Key + Shift + S
3. Click and drag to select the entire sheet
4. Open Paint ( Windows Key + R , type mspaint )
5. Press Ctrl+V to paste
6. Save as: Performance_Report_Screenshot.png

Mac:
1. Press Command + Shift + 4
2. Click and drag to select the sheet
3. Screenshot saves to Desktop automatically

Step 20: Upload to Cloud and Submit

1. Create a folder in Google Drive or OneDrive
 Name it: Excel Assignment 4
2. Upload both files:
Assignment_4_YourName.xlsm
Performance_Report_Screenshot.png

3. Share the folder

Right-click folder → Share/Get Link
Set to "Anyone with the link can view"
Copy the link
4. Submit the link

🎯 FINAL CHECKLIST
Before submitting, verify:
CSV files created correctly
Data imported and merged in Power Query
Total_Sales column calculated
Bonus lookup table created (3 rows)
Rating column uses IFS formula
Bonus_Amount column uses XLOOKUP with IFERROR
Sparklines show Q1 to Q2 trend
Goal Seek performed on Riya Sharma
Macro recorded (ApplyReportStyle)
Macro button created and working
File saved as .xlsm (macro-enabled)
Screenshot taken
Both files uploaded and link shared

🆘 TROUBLESHOOTING
"I don't see Get Data option"
Try: Data → Get & Transform Data → From Text/CSV
Or: Data → From Text/CSV

"XLOOKUP not found"

You need Excel 2021 or Microsoft 365
 Alternative: Use VLOOKUP (ask your instructor)

"Macro button doesn't work"

Did you save as .xlsm?
Enable macros: File → Options → Trust Center → Trust Center Settings → Macro
Settings → Enable

"Power Query Editor doesn't open"

After importing CSV, make sure you click Transform Data , not Load

"IFS function not working"

Check for typos
Make sure cell references are correct (F2 for Total_Sales)
Each condition needs both: condition, result

"Sparklines not showing"

Make sure you selected the right data range (Q1 and Q2 sales only)
Try clicking the sparkline cell and looking for the Sparkline Design tab

💡 KEY CONCEPTS EXPLAINED

Power Query
Purpose: Import, clean, and combine data from multiple sources
Benefit: Changes automatically update if source data changes

IFS Function
Purpose: Multiple conditions without nesting many IFs
Reads: IF this THEN that, IF this THEN that, etc.

XLOOKUP
Purpose: Search for a value and return related data
Better than VLOOKUP: Can search any direction, handles errors better
Sparklines
Purpose: Show trends visually in tiny space
Types: Line, Column, Win/Loss

Goal Seek
Purpose: Reverse calculate what input creates desired output
Real-world use: "What sales do I need to hit my bonus target?"

Macros
Purpose: Automate repetitive tasks
Recording: Captures mouse clicks and keystrokes

📚 ADDITIONAL RESOURCES
Excel IFS function: Microsoft Support - IFS
Power Query basics: Search YouTube for "Power Query for beginners"
XLOOKUP tutorial: Microsoft Support - XLOOKUP

Good luck with your assignment! Take it step by step, and don't rush. You've got this! 🎓

Created for Excel Assignment 4 - Performance Dashboard Last updated: April 2026