Data Types and Cell Formatting
Learn how Excel stores text, numbers, dates, currency, percentages, and formulas. Understand number formats, font styles, alignment, borders, fill colors, and professional spreadsheet presentation.
1. What Are Data Types in Excel?
A data type describes the kind of information stored in a cell. Excel can work with text, numbers, dates, times, logical values, and formulas. Correctly entering information helps Excel calculate results and sort or filter data reliably.
For example, Samadhan may enter his name in column A, examination marks in column B, an examination date in column C, and a formula in column D to calculate a total.
Illustration: a structured spreadsheet containing text, numeric values, dates, calculated totals, and results.
2. Common Data Types in Excel
Illustration: common data types and their example values.
| Type | Example | Typical Use |
|---|---|---|
| Text | Samadhan | Names, descriptions, labels |
| Number | 85 | Marks, quantities, counts |
| Date | 17-Oct-2026 | Joining dates, transactions, schedules |
| Currency | ₹1,250.00 | Fees, expenses, sales |
| Percentage | 75% | Growth, marks percentage, completion |
| Formula | =SUM(B2:C2) | Automatic calculations |
| Logical value | TRUE / FALSE | Conditions and logical tests |
3. What Is Cell Formatting?
Cell formatting changes the appearance of data without necessarily changing the underlying value. It helps make worksheets readable, consistent, and professional.
Common formatting options include:
- Font: change font family, size, bold, italic, and underline.
- Font Color: change the color of text.
- Fill Color: apply a background color to cells.
- Borders: draw outlines or separators around cells.
- Alignment: position text horizontally or vertically.
- Number Format: display values as numbers, dates, currency, or percentages.
- Wrap Text: show long text on multiple lines within a cell.
Illustration: formatting improves readability while preserving the original data values.
4. How to Format Cells in Excel
- Select the cell or range of cells you want to format.
- Open the Home tab on the Excel ribbon.
- Use the Font section to change font, size, bold, or color.
- Use Alignment to position text and enable Wrap Text.
- Use the Number Format dropdown to choose an appropriate format.
- Use Borders and Fill Color to make headings and totals easier to identify.
5. Understanding Number Formats
Number formatting controls how a numeric value appears on screen. In many cases, the underlying value remains the same.
| Format | Stored Value Example | Displayed Example |
|---|---|---|
| General | 1250 | 1250 |
| Number | 1250 | 1,250.00 (with two decimal places) |
| Currency | 1250 | ₹1,250.00, depending on regional settings |
| Percentage | 0.75 | 75% |
| Date | A date serial value | 17-Oct-2026, depending on the selected format |
6. Practical Example: Formatting Student Marks
Omkar is preparing a student marks report. He wants the headings to stand out, the marks to be easy to read, and the total column to use a formula.
| Student Name | Excel | VBA | Total | Percentage |
|---|---|---|---|---|
| Samadhan | 85 | 90 | 175 | 87.50% |
| Samiksha | 92 | 88 | 180 | 90.00% |
| Pradnya | 76 | 84 | 160 | 80.00% |
| Omkar | 68 | 80 | 148 | 74.00% |
| Neha | 88 | 95 | 183 | 91.50% |
Formula 1: Calculate Total Marks
If Excel marks are in B2 and VBA marks are in C2, enter this formula in D2:
=SUM(B2:C2)
The formula adds both marks. For Samadhan, the total is 175.
Formula 2: Calculate Percentage
If the maximum marks are 200, enter this formula in E2:
=D2/200
Apply Percentage format to E2 and set two decimal places. For a total of 175, Excel displays 87.50%.
Recommended Formatting
- Make the heading row bold and apply a dark green fill.
- Use borders to separate the table columns.
- Center-align numeric marks if appropriate.
- Apply Percentage format to the percentage column.
- Use consistent font sizes and avoid excessive colors.
7. Common Formatting Mistakes
- Using text values where numeric calculations are required.
- Displaying percentages incorrectly because the underlying value is wrong.
- Using different fonts, colors, and alignments without a clear purpose.
- Applying Currency format to values that are not monetary amounts.
- Assuming that changing a cell's appearance always changes its stored value.
- Using too many merged cells in datasets that need sorting and filtering.
8. Practice Exercise
Create and format a student marks worksheet.
- Enter the names Samadhan, Samiksha, Pradnya, Omkar, and Neha.
- Add two subject columns and enter sample marks.
- Calculate total marks with the SUM formula.
- Calculate percentages using the maximum possible marks.
- Format the heading row with bold text and a fill color.
- Apply borders and suitable number formats.
- Save the workbook and verify the displayed results.
Goal: Practice both data entry and professional cell formatting.
Important Points to Remember
- Excel cells can contain text, numbers, dates, logical values, and formulas.
- Number formatting changes the display of values in many cases, not the underlying value.
- Percentage format displays 0.75 as 75%.
- Cell formatting improves readability and report presentation.
- Use formulas to calculate totals and percentages instead of manually entering results.
Summary
In this lesson, you learned about common Excel data types, including text, numbers, dates, currency, percentages, logical values, and formulas. You also learned how to apply cell formatting, use number formats, and prepare a professional student marks report.
Correct data entry and formatting help make spreadsheets reliable and readable. In the next topic, we will learn about Basic Excel Formulas and Operators.