ITKokka
Lesson 7 · Note 9

Formatting a Worksheet

📌 The Short Note

Download

📖 Explanation

Formatting a Worksheet

Introduction

Worksheet formatting changes how data is displayed in a spreadsheet. It helps make information neat, clear, organised, and easy to read without changing the purpose of the data itself.

Spreadsheet software provides formatting tools for changing font appearance, alignment, decimal places, and number formats. These tools are useful when preparing marks sheets, financial records, contact lists, reports, and other worksheets.

This note explains common worksheet formatting tools and how to format decimal places, text, percentages, scientific numbers, dates, times, currency, and cell alignment.

1. Worksheet Formatting Tools

Worksheet formatting is the process of changing the appearance and display style of cells and their contents in a spreadsheet.

Formatting can help users:

  • Read information more easily.
  • Distinguish headings from other data.
  • Display numbers in an appropriate format.
  • Present a worksheet in a professional and organised way.

Common formatting tools are available on the Home tab in spreadsheet software such as Microsoft Excel. The exact position or appearance of tools may differ between spreadsheet applications and versions.

Common formatting tools

ToolPurpose
Font typeChanges the style of characters.
Font sizeChanges the size of characters.
BoldMakes text appear thicker and darker.
ItalicSlants text to the right.
UnderlineAdds a line below text.
Text alignmentChanges the position of content inside a cell.
Decimal placesIncreases or decreases the number of decimal digits displayed.
Number formatChanges how values such as percentages, dates, times, and currency are displayed.
Example: Formatting a worksheet heading

A heading such as Monthly Sales Report can be made bold and increased in font size. It can also be centre-aligned so that it is visually distinct from the data below it.

2. Formatting Decimal Places

A decimal number may contain several digits after the decimal point. Spreadsheet software allows users to control how many decimal places are displayed.

The Increase Decimal and Decrease Decimal tools change the number of decimal digits shown in a cell.

For example, the value 3.14159 can be displayed as:

Display settingDisplayed value
Original value3.14159
1 decimal place3.1
2 decimal places3.14

Another example is 10.5:

  • With one decimal place, it appears as 10.5.
  • With two decimal places, it appears as 10.50.

The second display includes a trailing zero to show two decimal places.

Increasing and decreasing decimal places

  • Increase Decimal: Displays more digits after the decimal point.
  • Decrease Decimal: Displays fewer digits after the decimal point.

When fewer decimal places are displayed, the spreadsheet may round the displayed number. The underlying stored value is generally retained; changing the display format is not the same as changing the actual value through a rounding formula or calculation.

Example: Displaying marks to two decimal places

If a calculated average is stored as 78.4567, displaying two decimal places shows 78.46. This makes a marks sheet easier to read while retaining the underlying precision.

3. Formatting Cells as Text

The Text number format tells the spreadsheet to treat the cell content as text rather than as a number to be used in ordinary numerical calculations.

This is useful for information that may contain digits but is not intended for arithmetic, such as:

  • Telephone numbers.
  • Identification numbers.
  • Membership numbers.
  • Product codes.

Why text formatting is useful

Some identifiers begin with one or more zeros. If a spreadsheet interprets an entry as a number, leading zeros may be removed.

For example:

EntryPossible numeric displayText display
07123456787123456780712345678
01104567891104567890110456789
0012312300123

Formatting the cell as Text helps preserve the leading zeros.

Example: Entering a telephone number

A telephone number such as 0712345678 is an identifier, not a quantity. It should not be added to another telephone number or used in a mathematical calculation. Treating it as text helps preserve its intended appearance.

Important: If a value has already been entered and the spreadsheet has removed leading zeros, changing the cell format afterwards may not restore them automatically. The value may need to be entered again as text or with the required zeros.

4. Formatting Percentages

A percentage expresses a value as a part of 100. Spreadsheet software provides a Percentage button (%) to display suitable numerical values as percentages.

To convert a decimal value into a percentage display, the spreadsheet multiplies the displayed value by 100 and adds the percent symbol.

Stored valuePercentage displayMeaning
0.2525%25 out of 100
0.550%50 out of 100
0.7575%75 out of 100
Example: Displaying a discount rate

If a cell contains 0.15, applying percentage formatting displays it as 15%. This is useful for showing discounts, interest rates, and percentage-based results.

Key Idea: Percentage formatting changes how a value is displayed. For example, the stored value 0.5 is displayed as 50%.

5. Scientific Number Format

Scientific number format displays very large or very small numbers in scientific notation. It is useful when ordinary number displays contain many digits or several leading zeros.

Scientific notation represents a number using a coefficient and a power of ten. In spreadsheet displays, the letter E is commonly used to represent “times ten to the power of”.

Examples:

Ordinary numberScientific format
1234567891.23E+08
0.00001231.23E-05
98700000009.87E+09

For example, 1.23E+08 means (1.23 \times 10^8), which is 123,000,000.

  • E+08 indicates multiplication by (10^8).
  • E-05 indicates multiplication by (10^{-5}).

Scientific format is useful in scientific, engineering, and technical work where very large or very small values are common.

6. Formatting Dates and Times

Spreadsheet cells can be formatted to display dates and times in different styles. This makes schedules, attendance records, transaction logs, and other time-based information easier to understand.

Date formats

Common date display formats include:

Format patternExample
dd/mm/yyyy01/10/2026
mm/dd/yyyy10/01/2026
yyyy-mm-dd2026-10-01

The same date can look different depending on the selected format and regional settings. Users should choose a format that is clear and suitable for the intended audience.

Time formats

Time values can be displayed using:

  • Hours and minutes.
  • Hours, minutes, and seconds.
  • A 12-hour clock with AM/PM.
  • A 24-hour clock.

Examples:

Time displayMeaning
2:30 PM2 hours and 30 minutes in the afternoon.
02:30 PMThe same time with a leading zero in the hour display.
14:45:1014 hours, 45 minutes, and 10 seconds in 24-hour format.

AM refers to the period before noon, while PM refers to the period from noon to before midnight.

7. Formatting Currency

The Currency number format displays numerical values as monetary amounts. It can add a currency symbol and display a chosen number of decimal places.

For Sri Lankan financial records, users may select the Sri Lankan Rupee symbol (Rs.) where it is available.

Examples:

Stored valueCurrency display
2500Rs. 2,500.00
12345.6Rs. 12,345.60
199.95Rs. 199.95

Currency formatting is useful for:

  • Budgets.
  • Sales records.
  • Expenses.
  • Invoices.
  • Financial calculations.

The currency format changes the display of a number. It does not, by itself, perform a currency conversion or change the numerical value.

Example: Formatting a monthly expense

If a cell contains 2500, applying a Sri Lankan Rupee currency format can display it as Rs. 2,500.00. The value remains available for calculations such as adding expenses or finding a total.

8. Cell and Text Alignment

Alignment controls the position of text or numbers inside a cell. Spreadsheet software provides horizontal and vertical alignment options.

Horizontal alignment

Horizontal alignment controls the position of content from left to right.

OptionDescription
LeftPlaces content towards the left side of the cell.
CentrePlaces content in the centre of the cell.
RightPlaces content towards the right side of the cell.

Vertical alignment

Vertical alignment controls the position of content from top to bottom.

OptionDescription
TopPlaces content at the top of the cell.
MiddlePlaces content in the middle of the cell.
BottomPlaces content at the bottom of the cell.
Example: Aligning a worksheet heading

A heading can be centre-aligned horizontally and middle-aligned vertically. This positions it in the centre of the cell area and can make the worksheet easier to read.

Alignment changes the position of content; it does not change the actual text or numerical value.

Choosing a Suitable Format

Different kinds of information need different display formats.

Data or purposeSuitable formatting
Worksheet headingsBold, larger font size, suitable alignment
Telephone numbers and codesText
Decimal measurementsAppropriate number of decimal places
Rates and proportionsPercentage
Very large or very small valuesScientific
Dates and schedulesDate
Times and durationsTime
Prices and expensesCurrency
Organising cell contentsHorizontal and vertical alignment

Choosing an appropriate format helps prevent confusion. For example, a telephone number should not be treated as a quantity, while a price should be clearly displayed as a monetary value.

Important Points for Examinations

  • Formatting tools change the appearance and display of worksheet data.
  • Font type, font size, bold, italic, and underline change the appearance of text.
  • Increase Decimal and Decrease Decimal control the number of decimal digits displayed.
  • Text formatting is useful for preserving leading zeros in telephone numbers and codes.
  • Percentage formatting displays a value as a percentage of 100.
  • Scientific number format is useful for very large and very small numbers.
  • Date and time formats display date and time values in different styles.
  • Currency format displays monetary values with a currency symbol.
  • Horizontal alignment includes left, centre, and right.
  • Vertical alignment includes top, middle, and bottom.

Key Idea: Formatting affects how information is presented in a worksheet. Always choose a format that matches the type and purpose of the data.

Summary

Worksheet formatting makes spreadsheet information easier to read and understand. Formatting tools can change fonts, text styles, alignment, decimal places, and the way numbers are displayed.

Text formatting helps preserve identifiers such as telephone numbers, while percentage, scientific, date, time, and currency formats present values in useful forms. By choosing suitable formats and alignments, users can prepare clear and well-organised worksheets.

Share:
← Spreadsheet FunctionsRelative and Absolute Cell References →