2.0.
Intended Learning Outcomes
(ILO's)
Intended Learning Outcomes (ILO's)
At the end of the lesson the students are expected to be able to:
1. Demonstrate how to change the color and style of text and cells, align text, and
2. Apply special formatting to numbers and dates.
3. Demonstrate how to use the Save and Save As commands, how to save as
an Excel 97-2003 compatible workbook, and how to save as a PDF.
4. Apply how to create simple formulas in Excel to add, subtract, multiply, and divide
values in a workbook.
5. Demonstrate various ways you can use cell references to make working with
formulas easier and more efficient.
List of topic for Module 2
2.1. Introduction - Formatting Cells / Saving / Creating Simple Formula
2.2. Formatting Cells
2.2.1. Formatting text
2.2.2. Format Painter
2.3. Saving
2.3.1. Saving workbooks
2.3.2. To save as a PDF
2.4. Creating Simple Formulas
2.4.1. Simple formulas
2.4.2. Creating formulas with cell references
2.5. Summary - Formatting Cells / Saving / Creating Simple Formula
2.1. Introduction - Formatting
Cells / Saving / Creating Simple
Formula
Introduction
In Excel, there are many tools you can use to format text and cells. In this lesson, you will
learn how to change the color and style of text and cells, align text, and apply special
formatting to numbers and dates.
2.2. Formatting Cells
Spreadsheets that have not been formatted can be difficult to read. Formatted text and
cells can draw attention to specific parts of the spreadsheet and make the spreadsheet
more visually appealing and easier to understand.
In Excel, there are many tools you can use to format text and cells.
We use Format Cells to change the formatting of cell number without changing the
number itself. We can use Format cells to change the number, alignment, font style,
Border style, Fill options and Protection.
2.2.1. Formatting text
Formatting text
Many of the commands you will use to format text can be found in the Font, Alignment,
and Number groups on the Ribbon. Font commands let you change the style, size, and
color of text. You can also use them to add borders and fill colors to
cells. Alignment commands let you format how text is displayed across cells both
horizontally and vertically. Number commands let you change how selected cells display
numbers and dates.
To change the font:
1. Select the cells you want to modify.
2. Click the drop-down arrow next to the Font command on the Home tab. The font drop-
down menu appears.
3. Move your mouse over the various fonts. A live preview of the font will appear in the
worksheet.
4. Select the font you want to use.
To change the font size:
1. Select the cells you want to modify.
2. Click the drop-down arrow next to the font size command on the Home tab. The font
size drop-down menu appears.
3. Move your mouse over the various font sizes. A live preview of the font size will appear
in the worksheet.
4. Select the font size you want to use.
You can also use the Grow Font and Shrink Font commands to change the size.
To use the bold, italic, and underline commands:
1. Select the cells you want to modify.
2. Click the Bold, Italic, or Underline command on the Home tab.
To add a border:
1. Select the cells you want to modify.
2. Click the drop-down arrow next to the Borders command on the Home tab. The border
drop-down menu appears.
3. Select the border style you want to use.
You can draw borders and change the line style and color of borders with the Draw
Borders tools at the bottom of the Borders drop-down menu.
To change font color:
1. Select the cells you want to modify.
2. Click the drop-down arrow next to the font color command on the Home tab.
The color menu appears.
3. Move your mouse over the various font colors. A live preview of the color will appear in
the worksheet.
4. Select the font color you want to use.
Your color choices are not limited to the drop-down menu that appears. Select More
Colors at the bottom of the menu to access additional color options.
To add a fill color:
1. Select the cells you want to modify.
2. Click the drop-down arrow next to the fill color command on the Home tab.
The color menu appears.
3. Move your cursor over the various fill colors. A live preview of the color will appear in the
worksheet.
4. Select the fill color you want to use.
To change horizontal text alignment:
1. Select the cells you want to modify.
2. Select one of the three horizontal Alignment commands on the Home tab.
o Align Text Left: Aligns text to the left of the cell
o Center: Aligns text to the center of the cell
o Align Text Right: Aligns text to the right of the cell
To change vertical text alignment:
1. Select the cells you want to modify.
2. Select one of the three vertical Alignment commands on the Home tab.
o Top Align: Aligns text to the top of the cell
o Middle Align: Aligns text to the middle of the cell
o Bottom Align: Aligns text to the bottom of the cell
By default, numbers align to the bottom-right of cells, while words and letters align to
the bottom-left of cells.
2.2.2. Format Painter
Format Painter
If you want to copy formatting from one cell to another, you can use the Format
Painter command on the Home tab. When you click the Format Painter, it will copy all of
the formatting from the selected cell. You can then click and drag over any cells you
want to paste the formatting to.
Formatting numbers and dates
One of Excel's most useful features is its ability to format numbers and dates in a variety
of ways. For example, you might need to format numbers with decimal places, currency
symbols ($), or percent symbols (%).
To format numbers and dates:
1. Select the cells you want to modify.
2. Click the drop-down arrow next to the Number Format command on the Home tab.
3. Select the number format you want. For some number formats, you can then use
the Increase Decimal and Decrease Decimal commands (below the Number Format
command) to change the number of decimal places that are displayed.
2.3. Saving
Are you saving a workbook for the first time? Saving it as another name? Sharing it with
someone who doesn't have Excel 2010? There are many ways you share and receive
workbooks, which will affect how you need to save the file.
2.3.1 Saving workbooks
Saving workbooks
When you create a new workbook in Excel, you'll need to know how to save it to access
and edit it later. Excel allows you to save your documents in several ways.
To use the Save As command:
Save As allows you to choose a name and location for your workbook. Use it if you are
saving a workbook for the first time or if you want to save a different version of a
workbook while keeping the original.
1. Click the File tab.
2. Select Save As.
3. The Save As dialog box will appear. Select the location where you want to save
the workbook.
4. Enter a name for the workbook, then click Save.
If you are using Windows 7, you will most likely want to save files to your Documents
library. For other versions of Windows, you will most likely want to save files to the My
Documents folder. For more information, check out our lessons on Windows 7 (Links to
an external site.) and Windows XP (Links to an external site.).
To use the Save command:
1. Click the Save command on the Quick Access toolbar.
2. The workbook will be saved in its current location with the same file name.
If you are saving for the first time and select Save, the Save As dialog box will appear.
To use AutoRecover:
Excel automatically saves your workbooks to a temporary folder while you're working on
them. If you forget to save your changes or if Excel crashes, you can recover the
autosaved file.
1. Open a workbook that was previously closed without saving.
2. In Backstage view, click Info.
3. If there are autosaved versions of your workbook, they will appear
under Versions. Click the file to open it.
4. A yellow caution note will appear on the Ribbon of the workbook. To restore this
version of the workbook, click Restore, then click OK.
By default, Excel autosaves every 10 minutes. If you are editing a workbook for less than
10 minutes, Excel may not create an autosaved version.
If you do not see the file you're looking for—or if you're looking for an autosaved version
of a file that has no previously saved versions—you can browse all autosaved files by
clicking the Manage Versions button and selecting Recover Unsaved Workbooks from
the drop-down menu.
To save as an Excel 97-2003 workbook:
You can share your workbooks with anyone using Excel 2010 or 2007 because they use
the same file format. However, earlier versions of Excel use a different file format, so if
you want to share your workbook with someone using an earlier version of Excel you
will need to save it as an Excel 97-2003 workbook.
1. Click the File tab.
2. Select Save As.
3. In the Save as type drop-down menu, select Excel 97-2003 Workbook.
4. Select the location where you want to save the file.
5. Enter a name for the file, then click Save.
2.3.2. To save as a PDF
To save as a PDF
Saving your workbook as an Adobe Acrobat Document—which is called a PDF file—can
be especially useful when your recipients do not have Excel. A PDF will make it possible
for recipients to view the content from your workbook, but they will not be able to edit
anything. If you are not sure what a PDF looks like, you can download our PDF
example (Links to an external site.) for this lesson.
1. Click the File tab.
2. Select Save As.
3. In the Save as type drop-down menu, select PDF.
4. Select the location where you want to save the file.
5. Enter a name for the file, then click Save.
Excel defaults to saving the active worksheet only. If you have multiple worksheets and
want to save all of them in the same PDF file, click Options. The Options dialog box will
appear. Select Entire workbook from the Options dialog box, then click OK.
2.4. Creating Simple Formulas
Excel can be used to calculate numerical information.
A formula is an equation that performs a calculation. Like a calculator, Excel can execute
formulas that add, subtract, multiply, and divide.
One of Excel's most useful features is its ability to calculate using a cell address to
represent the value in a cell. This is called using a cell reference.
To maximize the capabilities of Excel, it is important to understand how to create simple
formulas and use cell references.
2.4.1. Simple formulas
Simple formulas
Excel uses standard operators for equations, such as a plus sign for addition (+), minus
sign for subtraction (-), asterisk for multiplication (*), forward slash for division (/),
and caret (^) for exponents.
The key thing to remember when writing formulas for Excel is that all formulas must
begin with an equals sign (=). This is because the cell contains—or is equal to—the
formula and its value.
To create a simple formula in Excel:
1. Select the cell where the answer will appear (B4, for example).
2. Type the equals sign (=).
3. Type in the formula you want Excel to calculate (75/250, for example).
4. Press Enter. The formula will be calculated, and the value will be displayed in the
cell.
If the result of a formula is too large to be displayed in a cell, it may appear as pound
signs (#######) instead of a value. This means the column is not wide enough to display
the cell content. Simply increase the column width to show the cell content.
2.4.2. Creating formulas with cell
references
Creating formulas with cell references
When a formula contains a cell address, it is called a cell reference. Creating a formula
with cell references is useful because you can update data in your worksheet without
having to rewrite the values in the formula.
To create a formula using cell references:
1. Select the cell where the answer will appear (B3, for example).
2. Type the equals sign (=).
3. Type the cell address that contains the first number in the equation (B1, for
example).
4. Type the operator you need for your formula. For example, type the addition sign
(+).
5. Type the cell address that contains the second number in the equation (B2, for
example).
6. Press Enter. The formula will be calculated, and the value will be displayed in the
cell.
If you change a value in either B1 or B2, the total will automatically recalculate.
Excel will not always tell you if your formula contains an error, so it's up to you to check
all of your formulas. To learn how to do this, you can read the Double-Check Your
Formulas (Links to an external site.) lesson from our Excel Formulas (Links to an external
site.) tutorial.
To create a formula using the point-and-click method:
1. Select the cell where the answer will appear (B4, for example).
2. Type the equals sign (=).
3. Click the first cell to be included in the formula (A3, for example).
4. Type the operator you need for the formula. For example, type the multiplication
sign (*).
5. Click the next cell in the formula (B3, for example).
6. Press Enter. The formula will be calculated, and the value will be displayed in the
cell.
To edit a formula:
1. Click the cell you want to edit.
2. Insert the cursor in the formula bar, and edit the formula as desired. You can
also double-click the cell to view and edit the formula directly from the cell.
3. When you're done, press Enter or select the Enter command .
4. The new value will be displayed in the cell.
If you change your mind, use the Cancel command in the formula bar to avoid
accidentally making changes to your formula.
2.5. Summary - Formatting Cells /
Saving / Creating Simple Formula
Summary
Font commands let you change the style, size, and color of text. You can also use
them to add borders and fill colors to cells.
Alignment commands let you format how text is displayed across cells both
horizontally and vertically.
Number commands let you change how selected cells display numbers and dates.
You can draw borders and change the line style and color of borders with
the Draw Borders tools at the bottom of the Borders drop-down menu.
If you want to copy formatting from one cell to another, you can use the Format
Painter command on the Home tab.
One of Excel's most useful features is its ability to format numbers and dates in a
variety of ways.
