Lecture Notes: Class 2
Basic Formatting and Cell Styles
Objective:
Learn to format cells
and use basic styling features in Excel to enhance the presentation of data.
1. Formatting
Cells: Font, Size, Color, Borders, and Fill
Explanation:
- Font Style and Size: You can change the font style and size
to make your data more readable or to highlight important information.
- To change font: Select the cells, go to
the Home tab, and use the Font dropdown.
- To change size: Use the Font Size
dropdown next to the font style.
- Font Color: Use colors to differentiate data. For
example, highlight key figures in red.
- Select the cells, go to the Home
tab, click on the Font Color button, and choose a color.
- Borders: Borders make the data easier to read and organize.
- Select the cells, go to the Home
tab, and click on the Borders button. Choose from options like
bottom border, top border, or all borders.
- Fill Color: You can fill a cell with color to
visually group data.
- Select the cells, go to the Home
tab, and click on the Fill Color button.
2. Aligning Text:
Horizontal and Vertical Alignment
Explanation:
- Horizontal Alignment: This aligns your text left, right, or
center within the cell.
- Select the cells, go to the Home
tab, and click on the Align Left, Center, or Align Right
buttons in the Alignment group.
- Vertical Alignment: Adjusts the text within the height of
the cell.
- Go to the Home tab and select Top
Align, Middle Align, or Bottom Align in the Alignment
group.
- Wrap Text: If your content is too long for a cell,
use this feature to display all of it within the same cell by wrapping it
onto multiple lines.
- Select the cell, and click Wrap Text
in the Alignment group.
3. Using Cell
Styles and Themes
Explanation:
- Cell Styles: Predefined styles in Excel that combine
font, color, and borders to format cells quickly.
- Go to the Home tab and click on Cell
Styles to apply or create a custom style.
- Themes: Excel offers themes that include coordinated color schemes,
fonts, and effects to apply across your entire workbook for consistency.
- Go to the Page Layout tab, and
select a theme under Themes.
4. Number
Formatting: Currency, Percentage, Date
Explanation:
- Currency Formatting: Used for financial data.
- Select the cells, go to the Home
tab, and click on Currency in the Number group.
- Percentage Formatting: Converts numbers into percentages.
- Select the cells, go to the Home
tab, and click on the Percentage button in the Number
group.
- Date Formatting: Formats numbers into date values.
- Select the cells, go to the Home
tab, and choose a date format from the Number Format dropdown in
the Number group.
Exercise:
- Format a Given Dataset:
DataSet:
Date | Item | Sales | Change |
01/10/2024 | Product A | 1000 | 0.05 |
01/11/2024 | Product B | 1500 | 0.10 |
01/12/2024 | Product C | 1200 | -0.02 |
- Apply bold, change the font size, and use
a background color for the headers.
- Add borders to all cells in the dataset.
- Apply Number Formatting:
- Use currency formatting for a list of
financial data (e.g., sales figures).
- Apply percentage formatting to show the
increase or decrease in sales percentages.
- Format date values to a consistent style.
DataSet:
|
Date |
Item |
Sales |
Change |
|
01/10/2024 |
Product A |
1000 |
0.05 |
|
01/11/2024 |
Product B |
1500 |
0.10 |
|
01/12/2024 |
Product C |
1200 |
-0.02 |
Following these steps will:
- Make
the headers bold, with a larger font size and background color.
- Add
borders around all cells.
- Format
Sales as currency (e.g., $1,000.00).
- Format
Change as a percentage (e.g., 5%).
- Standardize
Date as 01-Oct-2024 or another preferred format.
Steps are as follows:
1. Formatting the Headers
- Select
the Header Row: Click on the row containing your headers (e.g., Row
1).
- Bold
the Headers: Click on the Home tab, then select the Bold (B)
button.
- Change
Font Size: With the headers still selected, adjust the Font Size
dropdown to a larger size (e.g., 14).
- Apply
Background Color:
- In
the Home tab, click the Fill Color icon (paint bucket).
- Choose
a color (e.g., light gray or blue) to apply a background color to the
header row.
2. Adding Borders to All Cells
- Select
the Entire Dataset: Highlight the cells containing your data,
including the headers.
- Add
Borders: Go to the Home tab, click on the Borders
dropdown, and select All Borders. This will apply borders to every
cell in the selection.
3. Applying Currency
Formatting to Financial Data
- Select
Financial Data Cells: Highlight the column(s) with financial data
(e.g., "Sales" column).
- Apply
Currency Format:
- In
the Home tab, locate the Number group.
- Click
on the Currency icon or select Currency from the dropdown
menu.
- This
will format the values with a currency symbol and two decimal places
(e.g., $1,000.00).
4. Applying Percentage
Formatting
- Select
Percentage Cells: Highlight the column(s) with percentage data (e.g.,
"Change" column).
- Format
as Percentage:
- In
the Home tab, click on the Percentage icon in the Number
group.
- Adjust
the decimal places if needed by clicking Increase Decimal or Decrease
Decimal.
5. Formatting Date Values
Consistently
- Select
Date Cells: Highlight the column(s) with date data.
- Choose
Date Format:
- In
the Home tab, open the Number Format dropdown.
- Select
either Short Date or Long Date to apply a consistent date
format (e.g., 01/10/2024).
- For
a custom date format, go to More Number Formats and set a
preferred format like dd-mm-yyyy.
Example: Final Outcome
Suppose your data initially looks
like this:
|
Date |
Item |
Sales |
Change |
|
01/10/2024 |
Product A |
1000 |
0.05 |
|
01/11/2024 |
Product B |
1500 |
0.10 |
|
01/12/2024 |
Product C |
1200 |
-0.02 |
After applying the above
formatting:
- Headers
are bold, with a larger font size and background color.
- Borders
are added around all cells.
- Sales
figures appear as currency (e.g., $1,000.00).
- Change
figures appear as percentages (e.g., 5%).
- Date
values are in a consistent format (e.g., 01-Oct-2024).
By the end of this
lesson, students will be able to format their Excel data effectively, making it
more readable and professional for presentations or reports.
