Formatting Datasets

Formatting Datasets

Formatting is an important part of working with datasets in Microsoft
Excel. Good formatting makes information easier to read, understand,
navigate and communicate.

However, data analysts must understand an important principle:
formatting should improve readability without damaging the structure
of the underlying data.

A beautiful spreadsheet can still be a poor analytical dataset if the
data is badly structured.

1. What Is Dataset Formatting?

Dataset formatting is the process of changing the appearance and display
of information in an Excel worksheet so that the data is easier to
understand and work with.

Formatting can include:

• Changing fonts.
• Changing font size.
• Applying bold or italic text.
• Changing alignment.
• Applying number formats.
• Formatting dates.
• Formatting currency.
• Applying borders.
• Using cell colors.
• Applying conditional formatting.
• Adjusting column widths.
• Adjusting row heights.

2. Why Formatting Matters in Data Analysis

Good formatting helps analysts and decision-makers quickly understand
the information contained in a dataset.

Proper formatting can improve:

• Readability
• Data interpretation
• Professional presentation
• Navigation
• Report quality
• Error detection

3. Formatting vs Data Cleaning

Formatting and data cleaning are related but different activities.

Formatting changes how data appears.

Data cleaning focuses on correcting, standardizing or
removing problematic data.

For example, changing a cell to display ₦150,000 instead of 150000 is
primarily formatting.

Changing “Agbor”, “agbor” and “AGBOR” into one standardized category is
a data-cleaning activity.

4. Selecting a Dataset

Before applying formatting, select the relevant cells or dataset.

You can select:

• A single cell.
• A row.
• A column.
• A range.
• An entire dataset.

Careful selection helps prevent accidental formatting of unrelated data.

5. Formatting Column Headings

Column headings should be easy to identify.

For example:

Headings can be made bold and visually distinct from the records below
them.

6. Font Formatting

Excel allows you to change font properties such as:

• Font family
• Font size
• Bold
• Italic
• Underline
• Font color

Use fonts consistently. Avoid using many unrelated font styles in the
same dataset.

7. Bold Text

Bold formatting can be useful for headings, totals and important labels.

For example:

Student ID | Name | Course | Score

The heading row could be bold to distinguish it from the records.

8. Font Size

Font size can improve readability.

Headings may use a slightly larger font while the dataset body uses a
normal readable size.

Avoid excessive font sizes inside large datasets because they can make
the worksheet difficult to navigate.

9. Cell Alignment

Alignment controls how content is positioned within a cell.

Common alignment options include:

• Left
• Center
• Right
• Top
• Middle
• Bottom

Text is commonly displayed toward the left while numbers are commonly
displayed toward the right, although alignment can be customized.

10. Number Formatting

Number formatting controls how numeric values are displayed.

Common number formats include:

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

11. Currency Formatting

Financial datasets often require currency formatting.

For example, the underlying value:

150000

can be displayed using an appropriate Nigerian Naira currency format.

The important point is that the underlying value should remain numeric
so that Excel can calculate totals and other financial measures.

12. Percentage Formatting

Percentage formatting is useful for values such as:

• Growth rate
• Conversion rate
• Pass rate
• Profit margin
• Completion rate

For example, a numeric value can be displayed as a percentage rather
than a general number.

13. Date Formatting

Dates can be displayed in different ways.
Examples include:

01/10/2026
1-Oct-2026
October 1, 2026
2026-10-01

Choose a consistent format that is appropriate for the project.

14. Time Formatting

Time values can also be formatted.
Examples include:

08:30 AM
14:30
2:30 PM

Consistent time formatting is important when analyzing schedules,
attendance, timestamps or transaction records.

15. Decimal Places

Excel allows you to control the number of decimal places displayed.

For example:

25
25.5
25.50
25.500

These displays may represent the same numeric value with different
display precision.

Do not confuse display formatting with changing the underlying value.

16. Borders

Borders can help visually separate headings, totals and sections.

However, avoid excessive borders throughout a large dataset because
they can make the worksheet visually cluttered.

17. Cell Fill Colors

Fill colors can be used to highlight headings or important sections.

Use colors consistently and purposefully.

For professional analytical work, avoid using many bright colors without
a clear meaning.

18. Conditional Formatting

Conditional Formatting allows Excel to change the appearance of cells
based on rules.

Examples include:

• Highlighting scores below 50.
• Highlighting high sales values.
• Identifying duplicate values.
• Displaying data bars.
• Displaying color scales.
• Highlighting dates approaching a deadline.

19. Example of Conditional Formatting

Suppose a student score column contains:

35
48
62
78
91

A rule could highlight scores below 50.
This makes students requiring attention easier to identify.

20. Column Width

Column width determines how much horizontal space is available for the
contents of a column.

If a heading is cut off, you can increase the column width.

Excel also provides AutoFit options that can automatically adjust the
width based on the content.

21. Row Height

Row height controls the vertical size of rows.

It can be adjusted when:

• Text is wrapped.
• Headings are larger.
• Multiple lines are displayed.

22. Wrap Text

Wrap Text allows long content to appear on multiple lines within a cell.

This can be useful for long headings or descriptions.

However, excessively long text inside analytical datasets may indicate
that a field needs to be redesigned.

23. Merge Cells

Merge Cells combines multiple cells into one visual area.

Merging can be useful for report titles.

However, avoid unnecessary merged cells inside raw datasets because
they can interfere with sorting, filtering and other analytical tasks.

24. Formatting as a Table

Excel’s Table feature can automatically provide structured formatting
and analytical controls.

A table can provide:

• Header formatting
• Filter buttons
• Automatic expansion
• Structured references
• Total rows
• Consistent formatting

Tables will be covered in greater detail later in this module.

25. Format Painter

Format Painter allows you to copy formatting from one area and apply it
to another area.

It is useful when several sections need the same visual appearance.

26. Clear Formatting

Sometimes imported or manually prepared data contains unwanted
formatting.

Excel provides options for clearing formatting while preserving the
underlying content.

This can be useful when preparing a dataset for consistent analysis.

27. Avoid Over-Formatting Raw Data

Raw data should generally remain simple and structured.

Avoid turning every cell into a heavily designed visual element.

The primary purpose of raw data is accuracy and analysis, not decoration.

28. Formatting Headers Correctly

A good header row should:

• Clearly describe the column.
• Use consistent formatting.
• Contain unique names where practical.
• Avoid unnecessary blank headings.
• Remain easy to identify.

29. Avoid Duplicate Column Names

Avoid creating ambiguous headings such as:

Score | Score | Score

Instead, use meaningful names:

English Score | Mathematics Score | Computer Score

Clear column names are important for analysis and reporting.

30. Formatting Does Not Change Data Meaning

It is important to understand that formatting usually changes how a
value is displayed rather than changing the underlying value.

For example, a number such as:

150000

can be displayed as:

₦150,000.00

while the underlying numeric value remains 150000.

31. Formatting and Data Types

Formatting should support the intended data type.

32. Formatting Totals

Totals can be visually distinguished from individual records using
bold text, borders or other appropriate formatting.

However, do not manually type totals when a formula can calculate them.
A formula-based total can update automatically when the underlying data
changes.

33. Formatting and Sorting

Good formatting should not prevent sorting.

Avoid merged cells and irregular layouts inside the main dataset.
A clean rectangular structure is easier to sort and filter.

34. Formatting and Filtering

Filtering works best when the dataset has clear headings and a consistent
structure.

If headings are unclear or the dataset contains unnecessary blank rows,
filtering may become difficult.

35. Formatting and Charts

Charts rely on properly structured data.
Formatting alone does not make a chart accurate.

Before creating a chart, make sure:

• Column headings are clear.
• Numbers are stored correctly.
• Dates are recognized as dates.
• Categories are consistent.
• There are no unnecessary blank rows.

36. Professional Formatting Principles

• Keep formatting consistent.
• Use readable fonts.
• Use meaningful number formats.
• Use colors sparingly.
• Make headings easy to identify.
• Keep raw data structured.
• Avoid unnecessary merged cells.
• Use conditional formatting where it provides analytical value.
• Separate raw data from reports and dashboards.
• Prioritize clarity over decoration.

37. Example Dataset

Apply the following formatting:

1. Make the headings bold.
2. Adjust the column widths.
3. Format Score as a number.
4. Format Fee as currency.
5. Format Registration Date consistently.
6. Apply conditional formatting to scores below 50.
7. Keep the dataset rectangular and easy to filter.

38. Formatting Raw Data vs Reports

There is a difference between formatting raw data and formatting a final
report.

Raw data should prioritize:

• Structure
• Consistency
• Accuracy
• Machine readability

Reports and dashboards can prioritize:

• Visual hierarchy
• Branding
• Charts
• Highlights
• Executive readability

39. Common Formatting Mistakes

• Using too many colors.
• Using inconsistent number formats.
• Using merged cells throughout raw data.
• Leaving important headings unclear.
• Formatting numbers as text.
• Formatting dates inconsistently.
• Using excessive borders.
• Hiding important information through poor column widths.
• Changing data values accidentally while formatting.
• Using decoration instead of meaningful analytical formatting.

40. Practical Exercise

Create the following dataset:

Student ID | Name | Course | Score | Fee | Registration Date
DW001 | John | Data Analysis | 78 | 150000 | 01/10/2026
DW002 | Mary | Excel | 85 | 150000 | 02/10/2026
DW003 | David | Excel | 42 | 150000 | 03/10/2026
DW004 | Grace | Data Analysis | 91 | 150000 | 04/10/2026
DW005 | Peter | Excel | 56 | 150000 | 05/10/2026

Then:

1. Bold the column headings.
2. Adjust column widths.
3. Format Fee as Nigerian Naira currency.
4. Format Registration Date consistently.
5. Apply conditional formatting to scores below 50.
6. Center appropriate fields.
7. Apply a clean and readable heading style.
8. Avoid unnecessary merged cells.
9. Save the workbook.

Knowledge Check

1. What is dataset formatting?
2. Why is formatting important in data analysis?
3. What is the difference between formatting and data cleaning?
4. What is number formatting?
5. Why should currency values remain numeric?
6. What is conditional formatting?
7. What is AutoFit?
8. What is Wrap Text?
9. Why should merged cells generally be avoided inside raw datasets?
10. What is Format Painter?
11. Why should colors be used carefully?
12. How can formatting support error detection?
13. Why should date formats be consistent?
14. What is the difference between formatting raw data and formatting a report?
15. Why should formatting not interfere with sorting and filtering?

Key Terms

• Dataset Formatting
• Number Format
• Currency Format
• Percentage Format
• Conditional Formatting
• AutoFit
• Wrap Text
• Format Painter
• Cell Alignment
• Data Cleaning
• Raw Data
• Table
• Data Type
• Header
• Dataset Structure

Summary

In this lesson, you learned how to format datasets in Microsoft Excel
while preserving their analytical structure.

You learned about fonts, headings, alignment, number formats, currency,
percentages, dates, borders, fill colors, conditional formatting, column
widths, row heights, Wrap Text, Format Painter and Excel Tables.

You also learned that formatting should improve readability without
damaging the structure of the underlying data. Raw datasets should remain
simple, consistent and easy to sort, filter and analyze.

Professional Excel work is not simply about making a spreadsheet look
beautiful. Good formatting should make information easier to understand
while preserving data quality and analytical usability.

Next Lesson

Lesson 4: Sorting and Filtering

In the next lesson, you will learn how to organize, sort, filter and
quickly locate information inside Excel datasets.