Introduction

Data Formatting in Microsoft Excel is used to make spreadsheet data clear, attractive, and easy to understand. Proper formatting helps users present information professionally and quickly identify important data.

In this lesson, you will learn how to format text, numbers, dates, cells, rows, columns, borders, alignment, colors, and tables in Excel.

---

1. What is Data Formatting?

Data formatting means changing the appearance or display style of data without changing its actual value.

For example, the number:

50000

can be displayed as:

₹50,000.00

The actual value remains the same, but its presentation becomes easier to understand.

---

2. Font Formatting

Excel allows you to change the appearance of text inside cells.

You can change:

• Font type
• Font size
• Font color
• Bold
• Italic
• Underline

Example

STUDENT MARKSHEET

You can make the heading:

• Bold
• Larger
• Center aligned
• Different font color

Steps

1. Select the required cells.
2. Go to the Home tab.
3. Use the Font group.
4. Select the required formatting.

---

3. Bold, Italic and Underline

Bold

Makes text darker and thicker.

Shortcut: Ctrl + B

Italic

Makes text slanted.

Shortcut: Ctrl + I

Underline

Adds a line below the text.

Shortcut: Ctrl + U

These options are commonly used for headings and important information.

---

4. Cell Alignment

Alignment controls the position of data inside a cell.

Horizontal Alignment

• Left
• Center
• Right

Vertical Alignment

• Top
• Middle
• Bottom

Example

Student names can be left-aligned while marks can be center or right-aligned.

Steps

1. Select the cells.
2. Go to Home → Alignment.
3. Select the required alignment.

---

5. Wrap Text

Wrap Text displays long text on multiple lines within the same cell.

Example

Without Wrap Text:

Advanced Diploma in Computer Applications

may require a very wide column.

With Wrap Text, it can appear on two or more lines within the same cell.

Steps

Home → Wrap Text

---

6. Merge & Center

Merge & Center combines two or more selected cells and places the content in the center.

Example

Select:

A1:F1

and enter:

ADCA STUDENT MARKSHEET

Then use Merge & Center.

Steps

1. Select the required cells.
2. Go to Home.
3. Click Merge & Center.

Use merging carefully, especially in data tables, because merged cells can make sorting and filtering more difficult.

---

7. Cell Borders

Borders help separate and organize information.

Common border options include:

• Bottom Border
• Top Border
• Left Border
• Right Border
• All Borders
• Outside Borders
• Thick Outside Borders

Example

A marksheet can use borders around all cells to make the table easier to read.

Steps

1. Select the required cells.
2. Go to Home → Borders.
3. Select the required border style.

---

8. Cell Fill Color

Cell fill color can highlight headings or important information.

Example

You can use a different fill for:

• Table headings
• Total marks
• Important values
• Warning information

Steps

1. Select the cells.
2. Click Fill Color.
3. Choose a suitable color.

---

9. Font Color

Font color changes the color of text.

Steps

1. Select the required cells.
2. Click Font Color.
3. Select a suitable color.

Use readable color combinations for professional spreadsheets.

---

10. Number Formatting

Excel provides several number formats to display values appropriately.

Common formats include:

• General
• Number
• Currency
• Accounting
• Percentage
• Date
• Time
• Fraction
• Scientific

---

11. Number Format

The Number format allows you to control decimal places and number presentation.

Example:

1250

can be displayed as:

1,250.00

Steps

1. Select the cells.
2. Go to Home → Number.
3. Select Number.
4. Increase or decrease decimal places if required.

---

12. Currency Formatting

Currency formatting is useful for:

• Fees
• Salaries
• Expenses
• Sales
• Prices
• Budgets

Example

25000

can be displayed as:

₹25,000.00

Steps

1. Select the cells.
2. Open the Number Format options.
3. Select Currency or an appropriate regional currency format.

---

13. Percentage Formatting

Percentage formatting is used for values such as:

• Marks percentage
• Discount
• Profit margin
• Interest rate
• Growth rate

Example

0.75

can be displayed as:

75%

Steps

1. Select the cells.
2. Go to Home → Number.
3. Click Percent Style (%).

---

14. Date Formatting

Excel can display dates in different formats.

For example:

08/09/2026

may be displayed as:

• 08-Sep-2026
• 8 September 2026
• 09/08/2026

The exact display depends on the selected date format and regional settings.

Steps

1. Select the date cells.
2. Open Number Format.
3. Select Date or More Number Formats.
4. Choose the required format.

---

15. Time Formatting

Excel can also format time values.

Examples:

• 10:30 AM
• 04:45 PM
• 18:30

Time formatting is useful for:

• Attendance
• Work schedules
• Class timetables
• Employee records

---

16. Changing Row Height

You can change the height of rows to improve readability.

Steps

1. Select the required row.
2. Go to Home → Format.
3. Select Row Height.
4. Enter the required height.

You can also drag the boundary between row numbers.

---

17. Changing Column Width

Column width controls how much horizontal space is available for data.

Steps

1. Select the column.
2. Go to Home → Format → Column Width.
3. Enter the required width.

AutoFit Column Width

Double-click the boundary between two column headings to automatically adjust the width according to the content.

---

18. Conditional Formatting

Conditional Formatting automatically changes the appearance of cells when they meet specific conditions.

It can be used to highlight:

• High marks
• Low marks
• Duplicate values
• Top performers
• Pending payments
• Sales targets

Example

In a marksheet:

• Marks above 80 → highlighted
• Marks below 40 → highlighted

Steps

1. Select the data range.
2. Go to Home.
3. Click Conditional Formatting.
4. Select a rule.
5. Enter the required condition.
6. Apply the formatting.

---

19. Format as Table

Excel can convert a normal data range into a structured table.

Benefits

Tables provide:

• Automatic formatting
• Filter buttons
• Structured data
• Easier sorting
• Automatic expansion
• Better data management

Steps

1. Select the data range.
2. Go to Home.
3. Select Format as Table.
4. Choose a suitable table style.
5. Confirm the data range.
6. Click OK.

---

20. Format Painter

Format Painter copies formatting from one cell or range to another.

Example

If cell A1 has:

• Bold text
• Center alignment
• Border
• Fill color

you can copy the same formatting to another cell without manually applying each option.

Steps

1. Select the correctly formatted cell.
2. Click Format Painter.
3. Select the destination cell or range.

---

21. Clear Formatting

Excel allows you to remove formatting while keeping the data.

Steps

1. Select the required cells.
2. Go to Home → Clear.
3. Select Clear Formats.

This removes formatting but does not normally remove the cell’s content.

---

22. Using Cell Styles

Cell Styles provide predefined combinations of formatting.

They can be used for:

• Titles
• Headings
• Important values
• Total rows
• Notes

Steps

1. Select the required cell.
2. Go to Home → Cell Styles.
3. Select an appropriate style.

---

23. Practical Activity

Create and Format a Student Marksheet

Create an Excel workbook named:

ADCA_Data_Formatting.xlsx

Enter the following information:

Perform the Following Tasks

1. Add the title ADCA STUDENT MARKSHEET.
2. Merge and center the title across the table.
3. Make the title bold and larger.
4. Format column headings using bold text.
5. Apply suitable fill color to the headings.
6. Add borders to the complete table.
7. Center-align Roll No. and marks.
8. Apply ₹ Currency formatting to the Fees column.
9. Adjust column widths using AutoFit.
10. Apply Wrap Text where required.
11. Use Conditional Formatting to highlight marks below 40.
12. Convert the data into an Excel Table.
13. Use Format Painter to copy heading formatting.
14. Save the workbook as:

ADCA_Data_Formatting.xlsx