Lab Manual: Practical 1 – Number Formatting

Course: Advanced Excel with Visualization
Course Code: CAD41VSP201
Practical Number: 1
Topic: Number Formatting in Microsoft Excel
Duration: 2 Hours

 

1. Objective

The objective of this practical session is to enable students to:

• Understand various number formatting options in Microsoft Excel.
• Enhance data readability, accuracy, and presentation without altering the underlying values.
• Apply both standard and custom number formats for different data types.

 

2. Introduction to Number Formatting

In Excel, data can be entered as numbers, text, dates, or times. While the cell’s actual value remains unchanged, its display can be modified using number formatting. Proper formatting is essential for:

• Readability: Makes large datasets easier to interpret.
• Clarity: Ensures values are represented in a universally understood format (e.g., currency, percentages).
• Professionalism: Creates well-structured, presentable reports and dashboards.

This practical introduces common number formats and demonstrates how to use custom formatting for specific requirements.

 

3. Prerequisites

Before starting this practical, students should:

• Be familiar with the Microsoft Excel interface.
• Know how to open, save, and navigate worksheets.
• Have basic data entry skills.

 

4. Software Requirement

• Microsoft Excel (2016 or newer version, as specified in the syllabus).

 

5. Procedure: Step-by-Step Practical

Follow these steps to learn and apply number formatting in Excel.

 

Step 5.1: Creating a Workbook and Entering Data

1.     Open Excel → Select Blank Workbook.

2.     Rename Sheet → Double-click Sheet1 and rename it to Data Formatting.

3.     Enter the following data starting from cell A1:

Category	Value 1	Value 2	Value 3
Sales Amount	12345.6789	5000	250000
Growth Rate	0.15	0.05	0.75
Item Count	123456	45000	100000
Date Recorded	44789	44800	44700
Time Recorded	0.5	0.25	0.75
Product Code	00123	567	00987
Temperature	23.56	30	-5
Budget	1500000	800000	-200000

 

Step 5.2: Applying Basic Number Formats

• Currency Format
• Select B2:D2 → Home → Number group → $ symbol.
• Observe the currency symbol and two decimal places.
• Percentage Format
• Select B3:D3 → Home → Number group → % symbol.
• Adjust decimals using the Increase/Decrease Decimal buttons.
• Comma Style (Thousands Separator)
• Select B4:D4 → Home → Number group → , button.

Step 5.3: Formatting Dates and Times

• Short Date
• Select B5:D5 → Home → Number group → Choose Short Date.
• Long Date
• With B5:D5 still selected → Choose Long Date.
• Time Format
• Select B6:D6 → Format as Time.

Step 5.4: Using the Format Cells Dialog (Ctrl + 1)

• Press Ctrl + 1 or right-click → Format Cells.
• Explore:
• Currency: Change symbols and negative number formats.
• Date/Time: Choose different display styles.
• Special: Useful for codes like Zip Codes or IDs.
• Custom: Define your own format (e.g., 00000 for Product Codes).

Step 5.5: Handling Negative Numbers

• Select B8:D8 → Format as Number → Choose negative display (red or in parentheses).
• Select B9:D9 → Format as Currency → Show negatives in red and/or with parentheses.

Step 5.6: Custom Number Formats

Custom formats use four sections: Positive; Negative; Zero; Text

Examples:

• Product Codes: 00000 → Ensures leading zeros.
• Sales Label: "Sales: " #,##0.00 → Displays Sales: 12,345.68.
• Advanced: #,##0.00;[Red](#,##0.00);"No Budget";"Invalid Data"