Number Formatting in Excel

Lab Manual: Practical 1 – Number Formatting

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

1. Objective

The primary objective of this practical is to familiarize students with various number formatting options in Microsoft Excel and understand how they can be used to improve data readability, accuracy, and presentation without changing the underlying cell value.

2. Introduction to Number Formatting

In Excel, data can be entered as numbers, text, dates, or times. While the underlying value of a cell remains unchanged, its appearance can be modified significantly through number formatting. Proper number formatting is crucial for:

  • Readability: Making large datasets easier to interpret.
  • Clarity: Ensuring numbers are presented in a universally understood context (e.g., currency, percentages).
  • Professionalism: Creating clean and organized reports and dashboards.

This practical will explore the common number formats and introduce you to custom formatting options.

3. Pre-requisites

  • Basic understanding of Microsoft Excel interface.
  • Ability to open, save, and navigate Excel worksheets.
  • Basic data entry skills.

4. Software Required

  • Microsoft Excel (preferably Excel 2016 or newer, as mentioned in your syllabus textbooks).

5. Procedure: Practical Steps with Examples

Follow these step-by-step instructions to apply various number formats.

Step 5.1: Opening a New Workbook and Entering Raw Data

1.     Open Excel: Launch Microsoft Excel and select Blank workbook.

2.     Rename Sheet: Double-click on the Sheet1 tab and rename it to Data Formatting.

3.     Enter Data: Input the following raw 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

1.     Select Cells: Select the range of cells B2:D2.

2.     Apply Currency Format:

o    On the Home tab, in the Number group, click the $ button directly, or click the dropdown arrow next to it and select your desired currency symbol (e.g., English for $).

o    Observe how the numbers are formatted with a currency symbol and two decimal places.

3.     Apply Percentage Format:

o    Select the range B3:D3.

o    On the Home tab, in the Number group, click the % button.

o    Observe how the decimal values are converted to percentages. You can increase or decrease decimal places using the buttons next to the percentage button.

4.     Apply Comma Style:

o    Select the range B4:D4.

o    On the Home tab, in the Number group, click the , button.

o    Observe how large numbers are formatted with a thousands separator.

5.     Increase/Decrease Decimals:

o    Select B2:D2 again.

o    Use the Increase Decimal and Decrease Decimal buttons in the Number group to see how you can control the number of decimal places displayed.

Step 5.3: Applying Date and Time Formats

Excel stores dates and times as serial numbers. Formatting changes how these numbers appear.

1.     Apply Short Date Format:

o    Select the range B5:D5. These are currently large numbers.

o    On the Home tab, in the Number group, click the dropdown next to the General format and select Short Date.

o    Observe how the numbers are converted to dates (e.g., 8/16/2022).

2.     Apply Long Date Format:

o    With B5:D5 still selected, click the dropdown and select Long Date.

o    Observe the full date display (e.g., Tuesday, August 16, 2022).

3.     Apply Time Format:

o    Select the range B6:D6. These are decimal numbers.

o    Click the dropdown and select Time.

o    Observe how the decimals are converted to times (e.g., 12:00 PM).

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

The Format Cells dialog box provides extensive control over number formatting, including custom options.

1.     Open Dialog Box:

o    Select cell B2.

o    Press Ctrl + 1 (or right-click the cell and select Format Cells…).

o    Go to the Number tab.

2.     Custom Currency:

o    In the Category list, select Currency.

o    Change the Symbol to a different currency if desired, and adjust the Decimal places.

o    Observe the Negative numbers options (e.g., display in red, with parentheses). Select a red option. Click OK.

3.     Custom Date/Time:

o    Select cell B5. Press Ctrl + 1.

o    In the Category list, select Date. Explore various date formats (e.g., 14-Mar-12, March 14, 2012).

o    Select Time. Explore various time formats.

4.     Special Formats (for Product Code):

o    Select B7:D7.

o    Press Ctrl + 1.

o    In the Category list, select Special. Explore Zip Code, Phone Number, etc.

o    Note: Product Code 00123 currently displays as 123. To force leading zeros without converting to text, we can use custom formatting.

§  In the Category list, select Custom.

§  In the Type field, enter 00000. This tells Excel to display the number with at least 5 digits, padding with leading zeros if necessary. Click OK.

§  Observe 123 becomes 00123. 567 becomes 00567. 987 becomes 00987.

Step 5.5: Custom Number Formats for Advanced Control

Custom number formatting allows you to define almost any display format. The format codes use sections separated by semicolons: Positive; Negative; Zero; Text.

1.     Custom Format for Product Code:

o    Select B7:D7. Press Ctrl + 1. Go to Custom.

o    Type 00000 in the Type box.

2.     Custom Format for Sales with Text:

o    Select cell B2. Press Ctrl + 1. Go to Custom.

o    In the Type box, enter "Sales: "#,##0.00.

§  "Sales: " adds the text "Sales: " before the number.

§  #,##0.00 formats the number with a thousands separator and two decimal places.

o    Click OK. Observe 12345.68 becomes Sales: 12,345.68.

3.     Custom Format for Positive/Negative/Zero:

o    Select cell B9. Press Ctrl + 1. Go to Custom.

o    In the Type box, enter #,##0.00;[Red](#,##0.00);"No Budget";"Invalid Data".

§  #,##0.00 defines positive numbers.

§  [Red](#,##0.00) defines negative numbers in red and parentheses.

§  "No Budget" defines how 0 (zero) values appear.

§  "Invalid Data" defines how text values appear (if any were present).

o    Change the value in B9 to 0 and then to abc to see how the custom format reacts. Change it back to 1500000.

6. Smartphone

120

750

44780

Tablet

30

400

44795

o    Task C: In column D (Total Value), calculate Quantity In Stock * Cost Per Unit. Format this column as Currency in your local currency symbol (e.g., for Indian Rupee or $) with no decimal places.

o    Task D: Format the Last Updated column (E) to display dates as DD-MM-YYYY (e.g., 30-08-2022). (Hint: use custom date format or the format cells dialog).

o    Task E: Change the Quantity In Stock for ‘Tablet’ to 0. Observe the formatting. Now, apply a custom number format to column B (Quantity In Stock) such that if the quantity is 0, it displays Out of Stock instead of 0. (Hint: General;"";"Out of Stock" – you only need two sections if you want text only for zero.)

Understanding Custom Number Formatting in Excel

Excel stores all numbers, dates, and times as numerical values.

  • Example: The date 26-Aug-2025 is stored as a serial number (e.g., 45914).
  • The time 12:00 PM is stored as 0.5.

Number formatting only changes how a value appears—it does not change the actual value stored in the cell.

Custom number formats allow you to define your own display style using special codes.


How to Access Custom Number Formats

1.     Select the cell(s) you want to format.

2.     Press Ctrl + 1 (or right-click → Format Cells…).

3.     Go to the Number tab.

4.     Choose Custom from the Category list.

5.     In the Type field, enter your custom format code.

Structure of a Custom Number Format

A custom format can have up to four parts, separated by semicolons (;):

Positive Format ; Negative Format ; Zero Format ; Text Format

Each part controls how a specific type of value is displayed:

1.     Positive numbers

2.     Negative numbers

3.     Zero values

4.     Text

If you leave out any part, Excel uses the default format for that category.

 

1. Positive Number Format

This section defines how positive numbers are displayed.

Example: Add two decimal places, a thousands separator, and "Units".

Underlying Value

Custom Format

Displayed As

Explanation

12345.67

#,##0.00 "Units"

12,345.67 Units

#,##0.00 formats the number, "Units" adds text

500

#,##0 "Widgets"

500 Widgets

Displays without decimals, adds "Widgets"

 

2. Negative Number Format

This section defines how negative numbers are displayed (second section after a semicolon). Often, negatives are shown in red or within parentheses.

Example: Show positive numbers normally, negatives in red with parentheses.

Underlying Value

Custom Format

Displayed As

Explanation

15000

#,##0;[Red](#,##0)

15,000

First part formats positive numbers

-5000

#,##0;[Red](#,##0)

(5,000) (Red)

[Red](#,##0) applies to negatives

0

#,##0;[Red](#,##0)

0

Zero is not defined, so defaults to general

 

3. Zero Value Format

This section controls how zero values appear.

Example: Display "N/A" for zero.

Underlying Value

Custom Format

Displayed As

Explanation

75

General;General;"N/A"

75

General for positive

-10

General;General;"N/A"

-10

General for negative

0

General;General;"N/A"

N/A

Third part displays text for zero

 

4. Text Format

This section defines how text values are displayed.

Example: Add "Error: " before any text entered.

Underlying Value

Custom Format

Displayed As

Explanation

123

General;General;General;"Error: "@

123

Numbers remain unchanged

Data Missing

General;General;General;"Error: "@

Error: Data Missing

@ represents the cell text

 

Common Custom Format Codes

  • 0 → Digit placeholder (displays extra zeros)
    • Example: 000 with value 12012
  • # → Digit placeholder (no extra zeros)
    • Example: ### with value 1212
  • . → Decimal point
  • , → Thousands separator
  • % → Percentage (multiplies by 100)
  • ? → Space placeholder (align decimals)
  • "text" → Literal text
  • @ → Text placeholder
  • [Color] → Display color (e.g., [Red], [Green])
  • [Condition] → Conditional format (e.g., [>100])

Assessment / Deliverables

  • Submit the completed Excel workbook (.xlsx file) with both the Data Formatting sheet and the solution sheets for Student Scores and Inventory.
  • Ensure all formatting requirements are met precisely.

Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     New Sheet for Student Scores: Create a new worksheet named Student Scores.

o    Enter the following data:

Student ID

Marks Obtained

Max Marks

Result

S001

75

100

S002

88

100

S003

42

60

S004

30

40

S005

95

100

o    Task A: In column D (Result), calculate the Percentage for each student (Marks Obtained / Max Marks). Format this column as Percentage with one decimal place.

o    Task B: Apply a custom number format to the Student ID column (A) so that S001 appears as STUDENT-001. (Hint: use "STUDENT-"000 in custom format).

2.     New Sheet for Inventory Data: Create a new worksheet named Inventory.

o    Enter the following data:

Product

Quantity In Stock

Cost Per Unit

Total Value

Last Updated

Laptop

50

1200

44800