Excel Final Assignments
Apply your Excel, Advanced Excel, PivotTable, dashboard, and VBA programming skills through practical business assignments. These projects will help you practise data preparation, analysis, reporting, automation, validation, and professional report creation.
1. Introduction to Final Assignments
Final assignments combine the topics covered in the Excel Practice module. Instead of practising each feature separately, you will use formulas, tables, PivotTables, charts, and VBA macros to solve practical business problems.
Imagine that you are working as a reporting analyst in a company. The company provides sales records and expects you to prepare accurate summaries, identify business trends, create a dashboard, and automate repetitive reporting tasks.
Complete the assignments in sequence. Start with data preparation, proceed to formulas and PivotTables, create the dashboard, and then automate suitable tasks using VBA.
Final Assignment Workflow
Follow the workflow from source-data preparation to the final validated and automated report.
2. Assignment 1: Create a Sales Dataset
Create a new workbook named
FinalSalesAssignments.xlsx. Add a worksheet named
SalesData and enter the following sample records.
| Order ID | Date | Region | Product | Quantity | Sales (₹) | Cost (₹) |
|---|---|---|---|---|---|---|
| ORD001 | 01-Oct-2026 | West | Laptop | 2 | 1,00,000 | 70,000 |
| ORD002 | 02-Oct-2026 | South | Mobile | 3 | 60,000 | 42,000 |
| ORD003 | 03-Oct-2026 | North | Tablet | 4 | 48,000 | 32,000 |
| ORD004 | 04-Oct-2026 | West | Laptop | 1 | 50,000 | 35,000 |
| ORD005 | 05-Oct-2026 | North | Mobile | 2 | 40,000 | 28,000 |
Tasks to Complete
- Enter all records with the correct column headings.
- Format the Date column as a valid Excel date.
- Format Sales and Cost as currency values.
- Convert the data range into an Excel Table.
- Name the table
tblSales. - Check that Order ID values are unique and required fields are populated.
3. Assignment 2: Apply Excel Formulas
Add three calculated columns to the sales dataset: Profit, Profit Margin, and Average Sales per Unit.
If the original columns are A:G, use column H for Profit, column I for Profit Margin, and column J for Average Sales per Unit. Enter the following formulas in row 2 and fill them down.
=F2-G2
=IFERROR(H2/F2,0)
=IFERROR(F2/E2,0)
Format Profit as currency and Profit Margin as a percentage. Average Sales per Unit should use a suitable currency format.
Summary Formula Tasks
| No. | Requirement | Formula | Expected Result |
|---|---|---|---|
| 1 | Total Sales | =SUM(F2:F6) |
₹2,98,000 |
| 2 | Total Cost | =SUM(G2:G6) |
₹2,07,000 |
| 3 | Total Profit | =SUM(H2:H6) |
₹91,000 |
| 4 | Number of Orders | =COUNTA(A2:A6) |
5 |
| 5 | Average Order Sales | =AVERAGE(F2:F6) |
₹59,600 |
| 6 | West Region Sales | =SUMIF(C2:C6,"West",F2:F6) |
₹1,50,000 |
| 7 | Mobile Sales | =SUMIF(D2:D6,"Mobile",F2:F6) |
₹1,00,000 |
| 8 | Orders with Sales above ₹50,000 | =COUNTIF(F2:F6,">50000") |
2 |
4. Assignment 3: Use Lookup and Logical Functions
Create a separate worksheet named ProductMaster.
Enter the product details below and practise lookup and logical
functions using the master data.
| Product | Category | Target Sales (₹) |
|---|---|---|
| Laptop | Computers | 1,20,000 |
| Mobile | Electronics | 1,10,000 |
| Tablet | Electronics | 50,000 |
Practical Tasks
-
Use
XLOOKUPto retrieve the category for each product in the sales dataset. -
Use
IFto display "High Value" when sales exceed ₹50,000; otherwise display "Standard". -
Use
IFERRORto display a helpful message when a lookup value is missing. -
Use
SUMIFSto calculate sales for a selected region and product. -
Use
COUNTIFSto count orders that meet two conditions, such as a selected region and product.
=XLOOKUP(D2,ProductMaster!A:A,ProductMaster!B:B,"Not Found")
=IF(F2>50000,"High Value","Standard")
=SUMIFS(F2:F6,C2:C6,"West",D2:D6,"Laptop")
5. Assignment 4: Data Cleaning and Validation
Reporting accuracy depends on data quality. Create a copy of the original dataset and intentionally introduce a few test issues, such as extra spaces, inconsistent text case, missing values, duplicate Order IDs, and invalid quantities.
Tasks to Complete
| No. | Task | Expected Outcome |
|---|---|---|
| 1 | Remove unnecessary spaces from text. | Consistent region and product names |
| 2 | Identify duplicate Order IDs. | Duplicates reviewed and resolved |
| 3 | Find blank required fields. | Missing values identified |
| 4 | Validate Quantity values. | Only valid positive quantities accepted |
| 5 | Standardize region and product names. | Consistent category values |
| 6 | Check date and currency formats. | Correctly stored dates and numbers |
Useful Cleaning Formulas
=TRIM(C2)
=PROPER(TRIM(C2))
Use Data Validation to restrict Quantity to whole numbers greater than zero. Test the rule with both valid and invalid inputs. Use Remove Duplicates only after checking whether duplicate records represent actual errors.
6. Assignment 5: Create PivotTables
Use tblSales to create PivotTables that summarize
business performance. Place the reports in a worksheet named
PivotReports.
| No. | PivotTable Requirement | Rows | Values |
|---|---|---|---|
| 1 | Region-wise Sales | Region | Sum of Sales |
| 2 | Product-wise Profit | Product | Sum of Profit |
| 3 | Region and Product Analysis | Region, Product | Sum of Sales |
| 4 | Order Count by Region | Region | Count of Order ID |
| 5 | Date-wise Sales | Date | Sum of Sales |
Procedure
- Select a cell inside
tblSales. - Choose Insert → PivotTable.
- Select the destination worksheet.
- Drag the required fields into Rows, Columns, and Values.
- Confirm that Sales and Profit use Sum, not Count.
- Apply suitable currency and date formats.
PivotTable Field Arrangement
Field placement determines how the PivotTable groups, summarizes, and filters the source data.
7. Assignment 6: Create Charts and Visual Reports
Create a worksheet named Charts. Use your PivotTable
results to create charts that answer clear business questions.
| No. | Chart | Purpose | Recommended Type |
|---|---|---|---|
| 1 | Region-wise Sales | Compare sales across regions. | Clustered Column |
| 2 | Product-wise Sales | Compare product performance. | Bar or Column |
| 3 | Sales and Profit | Compare revenue and profitability. | Combo Chart, if appropriate |
| 4 | Date-wise Sales | Review changes over time. | Line Chart |
Chart Formatting Tasks
- Add a descriptive title to every chart.
- Display readable category labels and units.
- Use consistent number formats.
- Avoid unnecessary 3-D effects and decorative elements.
- Check that chart values match the source PivotTable.
8. Assignment 7: Build an Interactive Dashboard
Create a worksheet named Dashboard. Arrange the
key performance indicators at the top and charts below them.
Use consistent fonts, spacing, and number formats.
Required KPI Cards
| KPI | Calculation | Expected Result |
|---|---|---|
| Total Sales | Sum of Sales | ₹2,98,000 |
| Total Cost | Sum of Cost | ₹2,07,000 |
| Total Profit | Total Sales − Total Cost | ₹91,000 |
| Total Orders | Count of Order IDs | 5 |
| Average Order Sales | Total Sales ÷ Number of Orders | ₹59,600 |
| Profit Margin | Total Profit ÷ Total Sales | Approximately 30.54% |
Dashboard Requirements
- Add a clear dashboard title.
- Create the KPI cards using formula-driven values.
- Insert region-wise and product-wise sales charts.
- Add a date filter or timeline where appropriate.
- Add a Region slicer to compatible PivotTables and PivotCharts.
- Connect the slicer to the reports it should control.
- Test the figures after changing filters and refreshing the data.
Suggested Dashboard Layout
Use this layout as a planning guide. The actual chart values, filter controls, and KPI cards must be linked to your workbook data.
9. Assignment 8: Record and Write VBA Macros
Save a copy of your workbook as
FinalSalesAssignments.xlsm. Enable the Developer tab
and use the Visual Basic Editor to create a standard module.
Task A: Format the Sales Report
Write a macro that formats the header row, adjusts column widths, applies currency formatting to Sales and Cost, and adds a title.
Option Explicit
Sub FormatSalesReport()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("SalesData")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No sales records are available.", vbExclamation
Exit Sub
End If
With ws.Range("A1:G1")
.Font.Bold = True
.Font.Color = vbWhite
.Interior.Color = RGB(13, 110, 253)
End With
ws.Columns("B").NumberFormat = "dd-mmm-yyyy"
ws.Range("F2:G" & lastRow).NumberFormat = "₹#,##0.00"
ws.Columns("A:G").AutoFit
MsgBox "Sales report formatting completed.", vbInformation
End Sub
Run the macro and verify that the heading format, date display, currency values, and column widths are correct.
Task B: Add a Profit Column
Option Explicit
Sub CalculateProfit()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("SalesData")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If lastRow < 2 Then
MsgBox "No sales records are available.", vbExclamation
Exit Sub
End If
ws.Range("H1").Value = "Profit"
ws.Range("H2:H" & lastRow).Formula = "=F2-G2"
ws.Range("H2:H" & lastRow).NumberFormat = "₹#,##0.00"
ws.Columns("H").AutoFit
MsgBox "Profit calculation completed.", vbInformation
End Sub
Task C: Export the Report as PDF
Option Explicit
Sub ExportSalesReportToPDF()
Dim ws As Worksheet
Dim outputPath As String
Set ws = ThisWorkbook.Worksheets("SalesData")
If Len(ThisWorkbook.Path) = 0 Then
MsgBox "Save the workbook before exporting the PDF.", vbExclamation
Exit Sub
End If
outputPath = ThisWorkbook.Path & Application.PathSeparator & _
"SalesReport.pdf"
ws.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=outputPath, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
MsgBox "PDF report exported successfully.", vbInformation
End Sub
10. Assignment 9: Automate the Reporting Process
Combine your formatting, calculation, and report preparation procedures into a single master macro. The master procedure should execute each step in the correct order.
Option Explicit
Sub RunSalesReportingProcess()
On Error GoTo ErrorHandler
Application.ScreenUpdating = False
Call FormatSalesReport
Call CalculateProfit
ThisWorkbook.Worksheets("SalesData").Activate
MsgBox "Sales reporting process completed.", vbInformation
CleanExit:
Application.ScreenUpdating = True
Exit Sub
ErrorHandler:
MsgBox "The reporting process failed: " & Err.Description, _
vbExclamation
Resume CleanExit
End Sub
Place the procedures in a standard module. Run
RunSalesReportingProcess and verify that the
formatting and profit calculation procedures both execute.
Additional Automation Tasks
- Add a button to run the master reporting macro.
- Display a confirmation message before exporting a PDF.
- Check that the source worksheet exists before processing.
- Add suitable error handling for missing sheets or invalid data.
- Document the steps required to run the automation.
11. Assignment 10: Test and Validate the Workbook
Before delivering the workbook, verify its calculations, PivotTables, charts, and VBA procedures. A report should not be considered complete merely because it looks correct.
| No. | Test | Expected Result |
|---|---|---|
| 1 | Compare Total Sales with the source data. | ₹2,98,000 for the supplied sample |
| 2 | Verify Total Profit. | ₹91,000 for the supplied sample |
| 3 | Refresh the PivotTables. | Reports reflect the current source range |
| 4 | Change the Region slicer selection. | Connected reports respond correctly |
| 5 | Add a new test record. | Table formulas and reports can include the record |
| 6 | Run the formatting macro twice. | No unexpected changes or duplicate columns |
| 7 | Export the report as PDF. | File opens and displays the intended report |
| 8 | Test missing or invalid inputs. | Errors are handled or reported clearly |
12. Final Capstone Project: Sales Reporting System
Build a complete sales reporting system that combines the skills from all previous assignments. The workbook should support data entry, calculations, analysis, visualization, and report delivery.
Required Worksheets
| Worksheet | Purpose | Required Content |
|---|---|---|
| SalesData | Store the source records. | Structured table and calculated columns |
| ProductMaster | Maintain product reference data. | Product, category, and target sales |
| PivotReports | Summarize business performance. | Region, product, and date analysis |
| Dashboard | Present important metrics visually. | KPI cards, charts, and filters |
| Instructions | Explain how to use the workbook. | Refresh, macro, and export instructions |
Final Project Deliverables
- A clean, structured sales dataset.
- Formula-driven Profit and Profit Margin calculations.
- Lookup and conditional analysis.
- At least three useful PivotTable summaries.
- An interactive dashboard with connected filters.
- VBA macros for formatting and profit calculation.
- A PDF export of the final report.
- Testing notes and a short user guide.
13. Final Assignment Evaluation
Use the following evaluation checklist to review the completed project. Tick each item after testing the corresponding feature.
| Criteria | What to Check | Marks |
|---|---|---|
| Data Preparation | Correct structure, clean values, and validation | 15 |
| Excel Formulas | Correct calculations and error handling | 20 |
| PivotTables | Accurate grouping, summaries, and refresh | 15 |
| Dashboard | Readable KPI cards, charts, and filters | 20 |
| VBA Automation | Working macros and basic error handling | 20 |
| Documentation | Clear instructions and validation evidence | 10 |
| Total | 100 | |
14. Important Points
- Preserve the original source data before cleaning or modifying it.
- Use clear column headings and structured Excel Tables.
- Verify formulas with independently calculated totals.
- Use the appropriate aggregation method in PivotTables.
- Connect slicers only to compatible reports and test their behaviour.
-
Save macro-enabled workbooks with the
.xlsmextension. - Test VBA macros on a copy of the workbook before routine use.
- Handle missing worksheets, empty data, and invalid inputs safely.
- Refresh PivotTables and verify that new records are included.
- Keep the final workbook, exported PDF, and instructions organized.
15. Summary
In this module, you applied Excel formulas, lookup functions, logical functions, data-cleaning techniques, Data Validation, PivotTables, charts, and dashboard design to a practical sales reporting scenario.
You also practised writing VBA macros for report formatting, profit calculations, and PDF export. The automation assignment introduced procedure reuse, execution order, and basic error handling.
Complete the capstone project by validating every calculation, testing interactive controls, and documenting the workbook. These practices will help you build reliable Excel reporting solutions for real business requirements.