Cell Formatting in MS Excel
(Number, Text, Currency, Date)
Cell formatting in Microsoft
Excel is an essential skill that allows users to display data in a more
readable, consistent, and professional manner. Different types of data (such as
numbers, text, currency, or dates) can be formatted in a variety of ways to
enhance the clarity and visual appeal of the spreadsheet. Below is a detailed
explanation of how to format cells in Excel for different types of data.
1. Number
Formatting
Number formatting helps you
present numeric data in a readable format, and Excel provides several built-in
number formats.
Steps to
Format Numbers:
1. Select
the Cells to Format: Highlight the cells that contain numbers you want
to format.
2. Open
Format Cells Dialog Box:
o
Right-click on the selected cells and click on Format
Cells.
o
Or, go to the Home tab and in the Number
group, click the small arrow in the bottom-right corner.
3. Choose
the Desired Format: Under the Number tab, you will see
different number formats such as:
o
General: The default format where
numbers are displayed as entered.
o
Number: Allows you to set the number of
decimal places, whether to use a thousands separator (comma), and how negative
numbers are displayed.
o
Currency: Displays numbers with a
currency symbol.
o
Accounting: Similar to Currency but aligns
the currency symbol to the left of the cell.
o
Scientific: Displays numbers in scientific
notation (e.g., 1.23E+5 for 123,000).
o
Percentage: Converts the number to a
percentage (multiplies the value by 100 and adds the % symbol).
Example
of Number Formatting:
- 1000 can
be formatted as:
- Number:
1,000.00 (with two decimal places).
- Currency:
$1,000.00.
- Percentage:
100,000%.
2. Text
Formatting
Text formatting is useful when
you need to display data as plain text, such as names, addresses, or other
non-numeric entries.
Steps to
Format as Text:
1. Select
the Cells to Format: Highlight the cells with text data.
2. Open
Format Cells Dialog Box:
o
Right-click and choose Format Cells.
o
Or, go to the Home tab and select the Text
option in the Number group.
3. Choose
the Text Format: Select the Text category, which ensures
that whatever is entered into the cell will be treated as text, even if numbers
are entered.
Example
of Text Formatting:
- Entering
12345 in a cell formatted as Text will display as 12345
rather than interpreting it as a number.
3.
Currency Formatting
Currency formatting is used to
display monetary values with a specific currency symbol, such as dollars,
euros, or yen.
Steps to
Format Currency:
1. Select
the Cells to Format: Highlight the cells containing monetary values.
2. Open
Format Cells Dialog Box:
o
Right-click and select Format Cells.
o
Or, in the Home tab, click the Currency
dropdown in the Number group.
3. Choose
Currency Format: In the Format Cells dialog box, select Currency
or Accounting.
o
The Currency format allows you to select the
currency symbol (e.g., $, €, £) and the number of decimal places.
o
The Accounting format displays the currency
symbol aligned to the left of the cell, which can be useful for financial
statements.
Example
of Currency Formatting:
- 1000 can
be displayed as:
- Currency:
$1,000.00 (with two decimal places).
- Accounting:
$1,000.00 (with the currency symbol aligned to the left).
4. Date
Formatting
Date formatting is essential when
working with dates and times in Excel. Excel recognizes dates in a specific
serial number format, but the display can be customized according to your
needs.
Steps to
Format Dates:
1. Select
the Cells to Format: Highlight the cells that contain date values.
2. Open
Format Cells Dialog Box:
o
Right-click and choose Format Cells.
o
Or, in the Home tab, click the Date
dropdown in the Number group.
3. Choose
Date Format: Under the Date category in the Format
Cells dialog box, you can select from various date formats:
o
Short Date: A simple date format like 12/16/2024.
o
Long Date: A more detailed date format
like Monday, December 16, 2024.
o
Custom Date Formats: You can
create custom date formats, such as dd/mm/yyyy or mm-dd-yyyy, depending on your
regional preference.
Example
of Date Formatting:
- 12/16/2024 can
be formatted as:
- Short
Date: 12/16/2024.
- Long
Date: Monday, December 16, 2024.
- Custom:
16-Dec-2024.
Additional
Formatting Options
- Number
of Decimal Places: You can control the number of decimal places
displayed for numeric values by adjusting the Decimal Places option
in the Number format.
- Thousands
Separator: The Use 1000 Separator (,) option can
be selected for large numbers to make them more readable (e.g.,
1,000,000).
- Negative
Numbers: Excel allows different ways of displaying
negative numbers. You can choose from a variety of formats such as Red
text, Parentheses, or Negative sign.
- Text
Alignment: Text can be aligned to the left, center, or
right of the cell, and numbers can be aligned similarly.
- Font
Formatting: Excel allows you to change the font, size,
style (bold, italic, underline), and color to highlight important data.
Conclusion
Cell formatting in Excel allows you to present your data in a way that improves readability, organization, and professionalism. Whether you're dealing with numbers, text, currencies, or dates, understanding how to properly format cells will enhance the effectiveness of your spreadsheets. Always choose the appropriate format based on the type of data you're working with and the level of detail you need to display.
Assignments
Assignment
1: Basic Number Formatting
Objective:
Learn how to format numbers in
different ways.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following numbers in cells A1 to A5:
o
1234567.89
o
9876.5432
o
10000
o
0.001
o
-2000
3. Format
the cells as follows:
o
Cell A1: Format as a Number with 2 decimal
places and use the thousands separator.
o
Cell A2: Format as a Currency with a dollar
sign and 2 decimal places.
o
Cell A3: Format as Percentage with no
decimal places.
o
Cell A4: Format as Scientific with 2 decimal
places.
o
Cell A5: Format as Number with negative
numbers shown in red and in parentheses.
Expected
Outcome:
- The
cells should display the numbers in the specified formats.
Assignment
2: Text Formatting
Objective:
Understand how to format text
data in Excel.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following data in cells A1 to A5:
o
John Doe
o
jane_smith
o
Mary-Ann O'Conner
o
"1234"
o
hello123
3. Format
the following cells as Text:
o
Cell A1: Format the name as Text and make it
bold and italics.
o
Cell A2: Format as Text and apply a blue
color.
o
Cell A3: Format as Text and underline the
text.
o
Cell A4: Format as Text and change the font
size to 14.
o
Cell A5: Format as Text and center-align the
text within the cell.
Expected
Outcome:
- The
text should be displayed as per the formatting instructions.
Assignment
3: Currency Formatting
Objective:
Apply currency formatting to
represent monetary values.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following monetary values in cells A1 to A5:
o
15000
o
3000.5
o
1000000
o
-250
o
50.75
3. Format
the cells as follows:
o
Cell A1: Format as Currency with a $
symbol and 2 decimal places.
o
Cell A2: Format as Currency with a €
symbol and 1 decimal place.
o
Cell A3: Format as Accounting with a $
symbol and 2 decimal places.
o
Cell A4: Format as Currency with a $
symbol and 0 decimal places, and ensure negative values appear in red with
parentheses.
o
Cell A5: Format as Currency with a ₹
symbol and 2 decimal places.
Expected
Outcome:
- Each
cell will display a currency symbol with the appropriate number of decimal
places and formatting.
Assignment
4: Date Formatting
Objective:
Learn how to format dates in
various formats.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following dates in cells A1 to A5:
o
01/15/2024
o
12/25/2024
o
08/31/2024
o
03/05/2024
o
10/12/2024
3. Format
the dates as follows:
o
Cell A1: Format as Short Date (MM/DD/YYYY).
o
Cell A2: Format as Long Date (e.g., Monday,
December 25, 2024).
o
Cell A3: Format as Custom to display the
date as dd-mm-yyyy.
o
Cell A4: Format as Custom to display the
date as mmmm dd, yyyy.
o
Cell A5: Format as Date in the International
format (YYYY-MM-DD).
Expected
Outcome:
- The
dates should display according to the specified format, showing variations
in short, long, and custom date formats.
Assignment
5: Mix of Number, Text, Currency, and Date Formatting
Objective:
Combine different types of
formatting in one worksheet.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following data:
o
A1: 1234567.89 (Number)
o
A2: John Doe (Text)
o
A3: 1000 (Currency)
o
A4: 15/12/2024 (Date)
o
A5: 0.25 (Percentage)
3. Format
the cells as follows:
o
Cell A1: Format as Number with 2 decimal
places and a thousands separator.
o
Cell A2: Format as Text and center-align the
text.
o
Cell A3: Format as Currency with the $
symbol and 2 decimal places.
o
Cell A4: Format as Short Date (MM/DD/YYYY).
o
Cell A5: Format as Percentage with 1 decimal
place.
Expected
Outcome:
- Each
cell should display the data in the specified format, combining number,
text, currency, and date formats in one sheet.
Assignment
6: Custom Formatting Exercise
Objective:
Learn how to use custom number
formats in Excel.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following values in cells A1 to A4:
o
12345
o
67890
o
1234567
o
-9876
3. Format
the cells as follows:
o
Cell A1: Create a custom format to display the
number as * 12345 * (with asterisks on both sides).
o
Cell A2: Create a custom format to display the
number as ###-###-### (formatted like a phone number).
o
Cell A3: Create a custom format that displays the
number with a leading zero (e.g., 01234567).
o
Cell A4: Create a custom format that shows negative
numbers in red and surrounded by brackets (e.g., (9,876)).
Expected
Outcome:
- Custom
number formats should be applied to display the values as per the given
instructions.
Assignment
7: Advanced Number Formatting with Decimal Places
Objective:
Master advanced number
formatting, including decimal precision and thousands separators.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following numbers in cells A1 to A5:
o
2500.567
o
1000000
o
123.1234
o
987654.321
o
4587.99
3. Format
the cells as follows:
o
Cell A1: Format as Number with 3 decimal
places and use the thousands separator.
o
Cell A2: Format as Currency with the $
symbol and no decimal places.
o
Cell A3: Format as Number with 4 decimal
places.
o
Cell A4: Format as Accounting with the $
symbol and 2 decimal places.
o
Cell A5: Format as Currency with the €
symbol and 2 decimal places, and use a red color for negative
values.
Expected
Outcome:
- The
numbers should appear with the appropriate decimal places, symbols, and
separators.
Assignment
8: Text Formatting with Capitalization and Alignment
Objective:
Understand how to format text
with alignment and capitalization options.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following text data in cells A1 to A5:
o
mary
o
John Doe
o
james_smith
o
Hello world!
o
excel101
3. Format
the cells as follows:
o
Cell A1: Convert the text to uppercase and center-align
the text.
o
Cell A2: Change the text to capitalize each word
and left-align.
o
Cell A3: Convert the text to lowercase and
apply right-alignment.
o
Cell A4: Apply bold and italicize the
text, then center-align it.
o
Cell A5: Bold the text and add a fill
color to the cell.
Expected
Outcome:
- The
text should appear as per the formatting requirements, with proper alignment
and capitalization changes.
Assignment
9: Date Formatting for Various International Standards
Objective:
Learn to format dates in various
international formats.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following dates in cells A1 to A5:
o
04/01/2024 (MM/DD/YYYY)
o
15/01/2024 (DD/MM/YYYY)
o
2024/01/04 (YYYY/MM/DD)
o
2024-01-01
o
January 5, 2024
3. Format
the cells as follows:
o
Cell A1: Format as MM/DD/YYYY.
o
Cell A2: Format as DD/MM/YYYY.
o
Cell A3: Format as YYYY/MM/DD.
o
Cell A4: Format as Custom Date Format with dd-mmm-yyyy
(e.g., 01-Jan-2024).
o
Cell A5: Format as Long Date, including the
full weekday name (e.g., Friday, January 5, 2024).
Expected
Outcome:
- The
dates should be displayed in various formats as per the given instructions.
Assignment
10: Time Formatting
Objective:
Practice formatting time data and
adjusting for different time formats.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following times in cells A1 to A5:
o
12:45 PM
o
7:30 AM
o
3:00 PM
o
10:15 AM
o
6:00 PM
3. Format
the cells as follows:
o
Cell A1: Format as Short Time (HH:MM AM/PM).
o
Cell A2: Format as 24-Hour Time (HH:MM).
o
Cell A3: Format as Custom with the format hh:mm:ss
AM/PM.
o
Cell A4: Format as Custom with the format hh:mm:ss
(24-hour time).
o
Cell A5: Format as Time with hours
and minutes only (e.g., 18:00).
Expected
Outcome:
- Each
cell should display the time in the required format with correct AM/PM or
24-hour time format.
Assignment
11: Combining Date and Time Formatting
Objective:
Learn to combine date and time
formatting to display both together.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following data in cells A1 to A5:
o
01/15/2024 12:00 PM
o
03/25/2024 05:30 AM
o
06/05/2024 03:45 PM
o
12/31/2024 11:00 PM
o
08/22/2024 08:00 AM
3. Format
the cells as follows:
o
Cell A1: Format as Date and Time with Custom
format (MM/DD/YYYY hh:mm AM/PM).
o
Cell A2: Format as Long Date and Short
Time.
o
Cell A3: Format as Custom with the format yyyy-mm-dd
hh:mm:ss.
o
Cell A4: Format as Date and Time with full
weekday and time.
o
Cell A5: Format as Custom to show dddd, mmmm
dd, yyyy h:mm AM/PM.
Expected
Outcome:
- The
data should display as a combination of date and time, with correct
formatting as per the specified requirements.
Assignment
12: Advanced Currency Formatting
Objective:
Learn advanced formatting for
currency, including negative values, and customizing symbols.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following values in cells A1 to A5:
o
1567.99
o
-45.78
o
7890
o
-1011
o
275.50
3. Format
the cells as follows:
o
Cell A1: Format as Currency with the $
symbol and 2 decimal places.
o
Cell A2: Format as Currency with the €
symbol and 2 decimal places, and ensure negative values are shown in red.
o
Cell A3: Format as Currency with the $
symbol and display negative numbers in parentheses.
o
Cell A4: Format as Accounting with the $
symbol and display negative numbers in parentheses.
o
Cell A5: Format as Currency with the ₹
symbol and 2 decimal places, and ensure positive values have a green
background.
Expected
Outcome:
- Each
currency value should display with the appropriate formatting and color
handling for negative values.
Assignment
13: Text Data Analysis Using Formatting
Objective:
Apply text formatting to analyze
and display structured data.
Instructions:
1. Open a
new Excel workbook.
2. Enter the
following data in cells A1 to A5:
o
Apple
o
Orange
o
Banana
o
Strawberry
o
Pineapple
3. Format
the cells as follows:
o
Cell A1: Format as Text and apply a bold
font with yellow fill.
o
Cell A2: Format as Text and apply italicization
with a green font color.
o
Cell A3: Format as Text, set the font to red
and apply underline.
o
Cell A4: Format as Text with blue font
and center-align.
o
Cell A5: Format as Text and apply strikethrough
with font size 14.
Expected
Outcome:
- The
text should be formatted according to the specifications, showing
different text properties like font color, alignment, and style.
Conclusion:
These assignments provide a
comprehensive way to practice and understand various cell formatting options in
MS Excel, including number, text, currency, and date formats.
