ICT LAB

LAB 02
MS EXCEL
Formatting Cells
The most formatting options are found on the Home Tab. All the options can be found in the Format
Cells window. This contains several tabs to help us format the contents of our spreadsheet. This
window can be opened by using the More Options button at the end of the Format, Alignment and
Number groups. You can also use the Keyboard Shortcut – Ctrl‐1 or choose Format Cells… from the
right‐click shortcut menu.

Font

1. Font – Sets the font of the selected cell(s). Fonts are different ways to show the same
letters.

2. Font Size – Sets the size of the letters (the font). Larger numbers give larger fonts.

3. Increase Font – Increases the font size

4. Decrease Font – Decreases the font size

5. Bold – Makes the selected cell(s) Bold

6. Italic – Makes the selected cell(s) Italicized

7. Underline – Makes the selected cell(s) Underlined. The drop down has a double underline.

8. Borders – Adds and removes borders for the selected cell(s). The drop down has More
Borders…

9. Fill Color – Changes the background color of the selected cell(s).

10. Font Color – Changes the color of the font of the selected cell(s).

11. More Options – This button will open the Format Cells dialog window.
Alignment

1. Top Align – Vertically aligns to the top of the cell.

2. Middle Align – Vertically aligns to middle of the cell.

3. Bottom Align – Vertically aligns to the bottom of the cell.

4. Orientation – Rotates the contents of the cell to the currently displayed option.

5. Wrap Text – Displays contents on multiple lines within the cell's column width.

6. Align Text Left – Horizontally aligns the contents to the left side of the column.

7. Center – Horizontally aligns the contents to the center of the cell.

8. Align Text Right – Horizontally aligns the contents to the right side of the cell.

9. Decrease Indent – Decreases the space between the text and the cell border

10. Increase Indent – Increases the space between the text and the cell border

11. Merge and Center – Joins selected (adjacent) cells into one cell and centers the result. If
there is data in more than one cell, Excel will only keep the information from the upper left
cell.

12. More Options – This button will open the Format Cells dialog window to the Alignment
Tab.
Number

1. Number Format – Allows you to change the way numeric values are displayed on the
spreadsheet. The drop-down arrow gives you a list of the most common formats, including a
More Number Formats option.

2. Currency Style – Sets the selected cell(s) to the Currency Style, this style keeps the dollar
signs on the left side of the cell, and the number on the right side. The drop-down arrow
gives you a list of other currency formats, such as the Euro (€).

3. Percent Style – Sets the selected cell(s) to the Percent Style, this style has zero decimal
places. Keyboard shortcut ‐ Ctrl‐Shift‐%. This button can be reset through Cell Styles on the
Home Tab.

4. Comma Style – Sets the selected cell(s) to the Comma Style, this style has a comma for
every thousand and two decimal places. This button can be reset through 5. Increase
Decimal – Increases the number of decimal places showing to the right of the decimal.

6. Decrease Decimal – Decreases the number of decimal places showing to the right of the
decimal.

7. More Options – This button will open the Format Cells dialog window to the Number Tab.
Cells Structures
There are a set number of cells within a Microsoft Excel worksheet. In the Ribbon versions (2007 and
later) there are 16,384 columns and 1,048,576 rows. As you insert and delete structures, you are not
reducing the number of cells, merely shifting where your data lies on the defined worksheet. Think
about moving a painting around on a wall. You're not changing the wall, just the position of the
painting.

Inserting

We use Insert to make new cells, columns, and rows.

Excel determines what you are trying to insert based on your selection. If a full column is
selected, Excel will assume you mean a full column and it will skip the Insert window.

You can insert a cell, row, or column by doing one of the following:

• Press Shift ‐ Ctrl ‐ = on the keyboard (ctrl plus)

• or from the Home tab, in the Cells group, choose Insert
• or open the Right‐click menu and choose insert.

‐ To insert multiple at once, select the number of cells/rows/columns you would like to
insert and follow the steps above.

‐ The size and format of the new space is determined by the previous row or column.

‐ This will push the existing cells, columns, or rows to the right or down to make room for
the new cells.
Deleting

We use Delete to remove cells, columns, and rows. Excel determines what you are trying to
delete based on your selection. You can delete a cell, row, or column by doing one of the
following:

• Press Shift ‐ Ctrl ‐ ‐ on the keyboard (Ctrl Minus)

• or from the Home tab, in the Cells group, choose Delete
• or open the Right‐click menu and choose insert.

‐ To delete multiple at once, select the number of cells/rows/columns you would like to
delete and follow the steps above.

‐ This will completely remove the structure, formatting and all, and the rows/columns/cells
will shift into this place. If you only intended to delete the contents not the cells, undo and
use the Clear Contents option instead.
Building an Equation
You can directly type in values, but that data stays constant. If you want to have the answers to your
equations update as you change your data, you should use the cell addresses. You will see the cell
addresses change colors so you can tell which ones are used in your equation.

Type in the exact cell address

Cells are labeled by their row and column headings. Rows are numbered and go horizontally
across (rows of chairs) and columns are lettered and go vertically top to bottom (columns of
a building). When we refer to the address of a cell, we use the column letter then the row
number such as A1.

• Click in the cell where the answer will appear

• Press the Equal sign (=)
• Type in the cell address you want to use in your equation
• Accept the answer or press the next math operator (+, ‐, *, /, ^)

Mathematical Operations

To let Excel know you expect it to "do math" you need start your cell with an equal sign (=).

• Addition, plus sign (+) = 5+2 result 7

• Subtraction, hyphen (‐) = 5‐2 result 3
(also used for negative) = ‐5 result ‐5
• Multiplication, asterisk (*) = 5*2 result 10
• Division, slash (/) = 5/2 result 2.5
• Exponent/Power, caret (^) = 5^2 result 25
AutoSum

We can build equations to do math on a large number of cells, but there are functions built
into Excel that can help us automate the most common ones: Sum, Average, Count,
Maximum, Minimum. On the far right of the Home tab you'll find the sigma ( ∑ ).

When you click on the word AutoSum, you'll get a sum function. There is a dropdown list at
the end of the button that will show more function options. The AutoSum button looks for
numbers above or to the left of the cell to choose the range (the set) of numbers. Make
sure to press enter or click the check to accept as soon as the function shows up. If you click
outside the cell while you see the function, you may break the equation.

Freeze Panes (Lock Titles to Top of Page)

1) Press Ctrl‐Home on the keyboard to return to Cell A1

2) Turn to the View Tab in the Ribbon

3) Find the Option Freeze Panes

a. Choose Freeze Top Row

b. Scroll down through the worksheet to see the titles in Row 1 stay at the top
Functions
Count

To count the number of cells that contain numbers, use the COUNT function.

CountIf

To count cells based on one criterion (for example, greater than 9), use the following
COUNTIF function.
CountIfs

To count cells based on multiple criteria (for example, green and greater than 9), use the
following COUNTIFS function.

Sum

To sum a range of cells, use the SUM function.

SumIf

To sum cells based on one criterion (for example, greater than 9), use the following SUMIF
function (two arguments).

To sum cells based on one criterion (for example, green), use the following SUMIF function
(three arguments, last argument is the range to sum).
SumIfs

To sum cells based on multiple criteria (for example, circle and red), use the following
SUMIFS function (first argument is the range to sum).

If

The IF function checks whether a condition is met, and returns one value if true and another
value if false. For example, take a look at the IF function in cell C2 below.

Explanation: if the score is greater than or equal to 60, the IF function returns Pass, else it
returns Fail.
And

The AND Function returns TRUE if all conditions are true and returns FALSE if any of the
conditions are false. For example, take a look at the AND function in cell D2 below.

Explanation: the AND function returns TRUE if the first score is greater than or equal to 60
and the second score is greater than or equal to 90, else it returns FALSE.

Or

The OR function returns TRUE if any of the conditions are TRUE and returns FALSE if all
conditions are false. For example, take a look at the OR function in cell D2 below.

Explanation: the OR function returns TRUE if at least one score is greater than or equal to
60, else it returns FALSE.
Not

The NOT function changes TRUE to FALSE, and FALSE to TRUE. For example, take a look at
the NOT function in cell D2 below.

Explanation: in this example, the NOT function reverses the result of the OR function.