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 12 → 012
- #
→ Digit placeholder (no extra zeros)
- Example:
### with value 12 → 12
- .
→ 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 |