I.

Preface/ Foreword
This course presents an overview of Information Technology Tools
used in business environments. This includes computer terminology,
hardware, software, operating systems and information &
application systems. This course also explores business applications
of software including spreadsheets, databases, presentation
graphics, word processing and business- oriented utilization of the
internet.

II. Content

Learning Outcomes:
At the end of the discussions, the student will be able to:

• Understand the use of spreadsheets and Excel

• Learn the parts of the Excel window
• Create a basic worksheet by entering text, values, and formulas.
• Change the appearance of worksheet data by using a variety of
formatting techniques.
• Create formulas by using some of Excel's built-in functions.
• Filter and sort Excel data.
• Plan, create and modify charts.
• Prepare a document for printing by using a variety of printing
options.

Microsoft Excel

 Is a spreadsheet developed by Microsoft for Windows, macOS, Android

and IOS.

 Is a convenient program because it allows user to create large

spreadsheets, reference information, and it allows for better storage of
information
Getting Started

There are a number of ways to open the Excel program.

First, look for the Excel icon on your desktop and double click it. The Excel
screen should open for you.

If you cannot find the Excel icon, click the Start button on the bottom left
corner of your desktop to display the Start menu, then choose:
Programs/All Programs > Microsoft Office > Microsoft Office Excel
Microsoft excel consists of workbooks. Within each workbook, there is an
infinite number of worksheets. Each worksheet contains Columns and
Rows. Where a column and a row intersect is called a cell. For example, cell
D5 is located where column D and row 5 meet. The tabs at the bottom of the
screen represent different worksheets within a workbook. You can use the
scrolling buttons on the left to bring other worksheets into view.

Interacting with Excel:

 Column

The Quick Access Toolbar

The Quick Access Toolbar lets you access common commands no matter
which tab is selected. By default, it includes the Save, Undo, and Repeat
commands. You can add other commands depending on your preference.

To add commands to the Quick Access toolbar

 Click the drop-down arrow to the right of the Quick Access toolbar.
 Select the command you wish to add from the drop-down
menu. To choose from more commands, select More
Commands.
 The command will be added to the Quick Access toolbar
The Ribbon
This part of the Excel interface is what allows you to control, format, and edit
the data stored in the Excel grid area of the spreadsheet. This is also where
the tools that help you analyse, interpret, organise, and present your data
are found.
 The Ribbon contains multiple tabs, each with several groups of commands.
You will use these tabs to perform the most common tasks in Excel.

If you let your mouse pointer hover on a button or control, you will see a

shaded box appear. This box, also known as a 'Screen Tip', will show you
the name and a brief description of the button or control in question.

Screen Tip

To minimize and maximize the Ribbon

The Ribbon is designed to respond to your current task, but you can choose
to minimize it if you find that it takes up too much screen space.

1. Click the Ribbon Display Options arrow in the upper-right corner of the
 Ribbon.

2. Select the desired minimizing option from the drop-down menu:

 Auto-hide Ribbon: Auto-hide displays your workbook in full-
screen mode and completely hides the Ribbon. To show the
Ribbon, click the Expand Ribbon command at the top of screen.
 Show Tabs: This option hides all command groups when not in
use, but tabs will remain visible. To show the Ribbon, simply
click a tab.
 Show Tabs and Commands: This option maximizes the
Ribbon. All of the tabs and commands will be visible. This option
is selected by default when you open Excel for the first time.

The Formula Bar

In the formula bar, you can enter or edit data, a formula, or a function that will
appear in a specific cell.

The Name Box

The Name box displays the location, or "name" of a selected cell.

The Worksheet Views

Excel has a variety of viewing options that change how your workbook is
displayed. You can choose to view any workbook in Normal view, Page
Layout view, or Page Break view. These views can be useful for various
tasks, especially if you're planning to print the spreadsheet.
To change worksheet views, locate and select the desired worksheet view
command in the bottom-right corner of the Excel window.
Zoom Control
To use the Zoom control, click and drag the slider. The number to the right
of the slider reflects the zoom percentage.

The Worksheet

Excel files are called workbooks. Each workbook holds one or more
worksheets (also known as "spreadsheets")

Whenever you create a new Excel workbook, it will contain one worksheet
named Sheet1. A worksheet is a grid of columns and rows where columns
are designated by letters running across the top of the worksheet and rows
are designated by numbers running down the left side of the worksheet.

When working with a large amount of data, you can create multiple

worksheets to help organize your workbook and make it easier to find

content. You can also group worksheets to quickly add information to
multiple worksheets at the same time.
 To rename a worksheet

 Right-click the worksheet you wish to rename, then select Rename

from the worksheet menu. Or you can just double click the worksheet
name.

 Type the desired name for the worksheet.

 Click anywhere outside of the worksheet, or press Enter on your
keyboard. The worksheet will be renamed.

To insert a new worksheet

 Locate and select the New sheet button.

Click to Add a New

Worksheet

 A new, blank worksheet will appear.

To delete a worksheet
 Right-click the worksheet you wish to delete, then select Delete from the
worksheet menu.

 Alternatively, from the Home Tab in the Cells Group click on Delete and
select Delete Sheet.

Warning: The Undo button will not undo the deletion of a worksheet.

To copy a worksheet
If you need to duplicate the content of one worksheet to another, Excel
allows you to copy an existing worksheet.
 Right-click the worksheet you want to copy, then select Move or Copy
from the worksheet menu.

 The Move or Copy dialog box will appear. Choose where the sheet will
appear in the Before sheet: field. In our example, we'll choose (move
to end) to place the worksheet to the right of the existing worksheet.
  Check the box next to Create a copy, then click OK.

The worksheet will be copied. It will have the same title as the original
worksheet, as well as a version number.
TIP: You can also copy a worksheet to an entirely different workbook. You
can select any workbook that is currently open from the To book: drop-down
menu.

To move a worksheet
Sometimes you may want to move a worksheet to rearrange your workbook.
1. Select the worksheet you wish to move. The cursor will become a small
worksheet icon .
2. Hold and drag the mouse until a small black arrow appears above the
desired location.
3. Release the mouse. The worksheet will be moved
To change the worksheet color
You can change a worksheet's color to help organize your worksheets and
make your workbook easier to navigate.
1. Right-click the desired worksheet, and hover the mouse over Tab
Color. The Color menu will appear.
2. Select the desired color. A live preview of the new worksheet color
will appear as you hover the mouse over different options. In our
example, we'll choose Red.

3. The worksheet color will be changed.

The worksheet color is considerably less noticeable when the
worksheet is selected. Select another worksheet to see how the color
will appear when the worksheet is not selected.

The Scroll Bars

Your spreadsheet may frequently have more data than you can see on the
screen at once. Click, hold and drag the vertical or horizontal scroll bar
depending on what part of the page you want to see.
Columns, Rows, Cells
Columns, rows, and cells are the most fundamental components of a work
sheet.
A column is a vertical series of adjacent cells from top to bottom.
 A row is a horizontal series of cells from left to right.
A cell is a single rectangle anywhere in the grid area of a worksheet.

The Active Cell

Every cell in your spreadsheet has a name or reference that can be formed by a
letter-number combination, e.g., B4 (shown in the figure below)
When you select a cell in an Excel worksheet, it becomes enhanced with a
thicker border. The cell you have chosen is now the active cell, and its name
or reference is the cell column letter followed by the cell row number.
The column letter and row number of the active cell are displayed in the
Name Box near the upper left corner of the Excel grid.
If you enter text or numbers into the Formula Bar, the text or number that
you type will also be entered into the active cell (shown below).

Selecting Cells
To select a group of cells, place your mouse pointer in the center of a cell. A
group of cells is known as a cell range. When the pointer turns into a thick
white shaded cross, hold the left mouse button down and drag the pointer
across the row or down the column of cells you want to select. In this image,
the cells B1 to B4 have been selected. For example, a cell range that
included cells B1, B2, B3 and B4 would be written as B1:B4.
Cell Content
Any information you enter into a spreadsheet will be stored in a cell. Each
cell can contain several different kinds of content, including text, formatting,
formulas, and functions.
Text - Cells can contain text, such as letters, numbers, and dates.
Formatting Attributes - Cells can contain formatting attributes that
change the way letters, numbers, and dates are displayed. For
example, percentages can appear as 0.15 or 15%. You can even
change a cell's background color
Formulas and Functions - Cells can contain formulas and functions that
calculate cell values.
Entering and Editing Data
One way to enter data into an Excel worksheet is to click on the cell you
want to use (making it the active cell) and enter the information directly into
it. When you type something in the active cell, what you type will also be
displayed into the formula bar.
If you type text, numbers, or formulas in the Formula Bar and press Enter,
the data or formula you typed will be entered into the active cell. When you
enter data in the formula bar, you will see an X and a check mark next to the
data entry field. If you click the X (cancel), the data in the formula bar will be
cleared. Clicking the check mark will enter the data just like pressing the
Enter key.
Handy hint: Values should be entered in without currency symbols or
commas as these can be applied using number formats later on.
Using Undo and Redo
Anyone can make mistakes, especially when creating a complex worksheet.
Excel provides a way of backtracking over, or undoing incorrect or
unintended actions.
To undo an action, click the Undo button on the Quick Access Toolbar. This
will undo the very last action you performed. The shortcut combination of
CTRL + Z performs the same action
If you want to redo an action (perform an action that you undid with Undo),
 you can click the Redo button on the quick access toolbar.

Undo Redo

You can also click the downward pointing triangle to show a list of recent actions.
You can select actions from this list and then click the selected item to delete it
and any other actions above it in the list.
Formatting Cells
All cell content uses the same formatting by default, which can make it
difficult to read a workbook with a lot of information. Basic formatting can
customize the look and feel of your workbook, allowing you to draw attention
to specific sections and making your content easier to view and understand.
You can also apply number formatting to tell Excel exactly what type of data
you’re using in the workbook, such as percentages (%), currency ($), and so
on.
Font Formatting and Text Alignment are already discussed in the Microsoft
word and the same goes with Ms Excel.
Cell borders and fill colors
Cell borders and fill colors allow you to create clear and defined boundaries
for different sections of your worksheet.
To add a border
 Select the cell(s) you wish to modify.
 Click the drop-down arrow next to the Borders command on the
Home tab. The Borders dropdown menu will appear.
  Select the border style you want to use.
 The selected border style will appear.
TIP: 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 add a fill color
 Select the cell(s) you wish to modify.
 Click the drop-down arrow next to the Fill Color command on the
Home tab. The Fill Color menu will appear.
 Select the fill color you want to use. A live preview of the new fill
color will appear as you hover the mouse over different options.
In our example, we'll choose Light Green.
 The selected fill color will appear in the selected cells.

Cell styles
Rather than formatting cells manually, you can use Excel's predesigned cell
styles. Cell styles are a quick way to include professional formatting for
different parts of your workbook, such as titles and headers.
To apply a cell style
 Select the cell(s) you wish to modify.
 Click the Cell Styles command on the Home tab, then choose the
desired style from the drop- down menu.
 The selected cell style will appear.
TIP: Applying a cell style will replace any existing cell formatting except for
text alignment. You may not want to use cell styles if you've already added
a lot of formatting to your workbook.

Formatting text and numbers

One of the most powerful tools in Excel is the ability to apply specific
formatting for text and numbers. Instead of displaying all cell content in
exactly the same way, you can use formatting to change the appearance of
dates, times, decimals, percentages (%), currency ($), and much more.
To apply number formatting
 Select the cells(s) you wish to modify.
 Click the drop-down arrow next to the Number Format command on
the Home tab. The Number Formatting drop-down menu will appear.
 Select the desired formatting option.
 The selected cells will change to the new formatting style.
Modifying Columns, Rows and Cells
By default, every row and column of a new workbook is always set to the
same height and width. Excel allows you to modify column width and row
height in different ways, including wrapping text and merging cells
To modify column width:

 the mouse over the column line in the column heading so

Position
the white cross becomes a double arrow
 Click, hold, and drag the mouse to increase or decrease the column
width.
 Release the mouse. The column width will be changed.
TIP: If you see pound signs (#######) in a cell, it means that the column
is not wide enough to display the cell content. Simply increase the column
width to show the cell content.
To AutoFit column width
The AutoFit feature will allow you to set a column's width to fit its content
automatically
 
Position the mouse over the column line in the column heading so
the white cross becomes a double arrow
 Double-click the mouse. The column width will be changed automatically
to fit the content

TIP: You can also AutoFit the width for several columns at the same time.
Simply select the columns you would like to AutoFit, then select the AutoFit
Column Width command from the Format dropdown menu on the Home tab.
This method can also be used for Row height.
To modify row height
 Position the cursor over the row line so the white cross becomes a double
arrow
 Click, hold, and drag the mouse to increase or decrease the row height.
 Release the mouse. The height of the selected row will be changed.

To modify all rows or columns

Rather than resizing rows and columns individually, you can modify the
height and width of every row and column at the same time. This method
allows you to set a uniform size for every row and column in your worksheet.
 Locate and click the Select All button just below the formula bar
to select every cell in the worksheet.


Position the mouse over a row line so the white cross becomes a double
arrow
  Click, hold, and drag the mouse to increase or decrease the row height.
 Release the mouse when you are satisfied with the new row height for
the worksheet

Inserting, deleting, moving, and hiding rows and columns

After you've been working with a workbook for a while, you may find that you
want to insert new columns or rows, delete certain rows or columns, move
them to a different location in the worksheet, or even hide them.
To insert rows/columns
 Select the row/column heading below where you want the new row to
appear.
 Click the Insert command on the Home tab

 The new row/column will appear above the selected row

TIP: When inserting new rows, columns, or cells, you will see the Insert Options
button
next to the inserted cells. This button allows you to choose how Excel
formats these cells. By default, Excel formats inserted rows with the same

formatting as the cells in the row above. To access more options, hover your
mouse over the Insert Options button, then click the drop-down arrow.

TIP: When inserting rows and columns, make sure you select the entire row or
column by clicking the heading. If you select only a cell in the row or column,
the Insert command will only insert a new cell.
To delete rows/columns
It's easy to delete any row that you no longer need in your workbook.
 Select the row(s)/column(s) you want to delete.
 Click the Delete command on the Home tab
 The selected row(s) will be deleted, and the rows below will shift up.
 The selected columns(s) will be deleted, and the columns to the
right will shift left.

TIP: It's important to understand the difference between deleting a row or

column and simply clearing its contents. If you want to remove the content
of a row or column without causing others to shift, right-click a heading, then
select Clear Contents from the drop-down menu.

To move a row or column

Sometimes you may want to move a column or row to rearrange the content of
your worksheet
 Select the desired column heading for the column you wish to move,
then click the Cut command on the Home tab or press Ctrl+X on your
keyboard.
 Select the column heading to the right of where you want to move
the column. For example, if you want to move a column between
columns B and C, select column C.
 Click the Insert command on the Home tab, then select Insert Cut
Cells from the drop-down menu
 The column will be moved to the selected location, and the columns to
the right will shift right
TIP: You can also access the Cut and Insert commands by right-clicking the
mouse and then selecting the desired commands from the drop-down menu.
To hide and unhide a row or column
At times, you may want to compare certain rows or columns without
changing the organization of your worksheet. Excel allows you to hide rows
and columns as needed.
 Select the column(s) you wish to hide, right-click the mouse, then
select Hide from the formatting menu.
 The columns will be hidden. The green column line indicates the location
of the hidden columns.

 To unhide the columns, select the columns to the left and right of
the hidden columns (in other words, the columns on both sides of
the hidden columns).
 Right-click the mouse, then select Unhide from the formatting menu.
The hidden columns will reappear.
Wrapping text and merging cells
Whenever you have too much cell content to be displayed in a single cell,
you may decide to wrap the text or merge the cell rather than resizing a
column. Wrapping the text will automatically modify a cell's row height,
allowing cell contents to be displayed on multiple lines. Merging allows you
to combine a cell with adjacent, empty cells to create one large cell.
 Select the cells you wish to wrap.
 Select the Wrap Text command on the Home tab.
 The text in the selected cells will be wrapped.

TIP: Click the Wrap Text command again to

unwrap the text To merge cells using the
Merge & Center command
 Select the cell range you want to merge together.
 Select the Merge & Center command on the Home tab.
  The selected cells will be merged, and the
text will be centered To access more merge options
Click the drop-down arrow next to the Merge & Center command on the
Home tab. The Merge drop- down menu will appear. From here, you can
choose to:
 Merge & Center: Merges the selected cells into one cell and centers the
text
 Merge Across: Merges the selected cells into larger cells while keeping
each row separate
 Merge Cells: Merges the selected cells into one cell, but does not center
the text
 Unmerge Cells: Unmerges selected cells

Using Formulas in Excel

One of the most powerful features in Excel is the ability to calculate numerical
information using formulas.
Formulas are used to perform calculations in a spreadsheet, including
addition, subtraction, multiplication and division.
The main advantage of using formulas is that unlike a value that stays the
same unless you edit it, the result of a formula will automatically recalculate
itself in response to values in the spreadsheet being changed.
Formulas can be easily recognized in a spreadsheet as all formulas begin with
an equal sign =.
Just like a calculator, Excel can add, subtract, multiply, and divide. In this
lesson, we'll show you how to use cell references to create simple formulas.
Basic Mathematical Operators To build formulas in Excel, you will have to use
the basic mathematical operators as shown in the following table.

() Brackets/Parentheses

+ Addition

- Subtraction

^ Exponent

* Multiplication

/ Division

To create a formula
 Select the cell that will contain the formula.
 Type the equals sign (=). Notice how it appears in both the cell and the
formula bar.

 Type the cell address of the cell you wish to reference first in
the formula: cell D1 in our example. A blue border will appear
around the referenced cell.
 Type the mathematical operator you wish to use. In our example, we'll
type the addition sign (+).
 Type the cell address of the cell you wish to reference second in
the formula: cell D2 in our example. A red border will appear
around the referenced cell.
 Press Enter on your keyboard. The formula will be calculated, and the
value will be displayed in the cell.
TIP: 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 that
the column is not wide enough to display the cell content. Simply increase
the column width to show the cell content.
Modifying values with cell references
The true advantage of cell references is that they allow you to update data in
your worksheet without having to rewrite formulas.
TIP: 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 create a formula using the point-and-click method

Rather than typing cell addresses manually, you can point and click on the
cells you wish to include in your formula. This method can save a lot of time
and effort when creating formulas. In our example below, we'll create a
formula to calculate the cost of ordering several boxes of plastic silverware.
 Select the cell that will contain the formula. In our example, we'll select
cell D3.

 Type the equals sign (=).

 Select the cell you wish to reference first in the formula: cell B3 in our
example. The cell address will appear in the formula, and a dashed
blue line will appear around the referenced cell.

 Type the mathematical operator you wish to use. In our example,

we'll type the multiplication sign (*).
 Select the cell you wish to reference second in the formula: cell C3 in
our example. The cell address will appear in the formula, and a
dashed red line will appear around the referenced cell.

 Press Enter on your keyboard. The formula will be calculated, and the
 value will be displayed in the cell.

Formulas can also be copied to adjacent cells with the fill handle, which can
save a lot of time and effort if you need to perform the same calculation
multiple times in a worksheet.
To edit a formula
Sometimes you may want to modify an existing formula. In the example
below, we've entered an incorrect cell address in our formula, so we'll need
to correct it
 Select the cell containing the formula you wish to edit.
 Click the formula bar to edit the formula. You can also double-click
the cell to view and edit the formula directly within the cell.
 A border will appear around any referenced cells.
 When finished, press Enter on your keyboard or select the Enter
command ü in the formula bar.
 The formula will be updated, and the new value will be displayed in the
cell.
TIP: If you change your mind, you can press the Esc key on your keyboard or
click the Cancel command
X in the formula bar to avoid accidentally making changes to your formula.

TIP: To show all of the formulas in a spreadsheet, you can hold the Ctrl key
and press ` (grave accent). The grave accent key is usually located in the
upper-left corner of the keyboard. You can press Ctrl+` again to switch back
to the normal view.

Complex Formulas
A simple formula is a mathematical expression with one operator, such as
7+9. A complex formula has more than one mathematical operator, such as
5+2*8. When there is more than one operation in a formula, the order of
operations tells Excel which operation to calculate first. In order to use Excel
to calculate complex formulas, you will need to understand the order of
operations.
Order of calculations – BEDMAS
In formulas where there is more than one operator, for example, an addition
and a multiplication, Excel will not necessarily calculate the result by reading
the formula from left to right.
Excel will follow a set rule for the order in which it
performs calculations. Excel calculates formulas based
on the following order of operations:
1. Operations enclosed in parentheses

2. Exponential calculations (3^2, for example)

3. Multiplication and division, whichever comes first

4. Addition and subtraction, whichever comes first

TIP: It is especially important to enter complex formulas with the correct

order of operations. Otherwise, Excel will not calculate the results
accurately. In our example, if the parentheses are not included, the
multiplication is calculated first and the result is incorrect. Parentheses are the
best way to define which calculations will be performed first in Excel.
Relative and Absolute Cell References
There are two types of cell references: relative and absolute. Relative and
absolute references behave differently when copied and filled to other cells.
Relative references change when a formula is copied to another cell.
Absolute references, on the other hand, remain constant, no matter where
they are copied.

Relative cell references

By default, all cell references are relative references. When copied across
multiple cells, they change based on the relative position of rows and
columns. For example, if you copy the formula =A1+B1 from row 1 to row 2,
the formula will become =A2+B2. Relative references are especially
convenient whenever you need to repeat the same calculation across
multiple rows or columns.

To create and copy a formula using relative references

In the following example, we want to create a formula that will multiply each
item's price by the quantity. Rather than creating a new formula for each
row, we can create a single formula in cell D2 and then copy it to the other
rows. We'll use relative references so the formula correctly calculates the
total for each item.
 Select the cell that will contain the formula. In our example, we'll select
cell D2.
 Enter the formula to calculate the desired value. In our example, we'll
type =B2*C2.

 Press Enter on your keyboard. The formula will be calculated, and the
result will be displayed in the cell.
 Locate the fill handle in the lower-right corner of the desired cell. In
our example, we'll locate the fill handle for cell D2.
  Click, hold, and drag the fill handle over the cells you wish to fill.

 Release the mouse. The formula will be copied to the selected cells
with relative references, and the values will be calculated in each
cell.
TIP: You can double-click the filled cells to check their formulas for
accuracy. The relative cell references should be different for each cell,
depending on their rows.

Absolute cell references

There may be times when you do not want a cell reference to change when
filling cells. Unlike relative references, absolute references do not change
when copied or filled. You can use an absolute reference to keep a row
and/or column constant.
An absolute reference is designated in a formula by the addition of a dollar
sign ($). It can precede the column reference, the row reference, or both.

You will generally use the $A$2 format when creating formulas that contain
absolute references. The other two formats are used much less frequently.
TIP: When writing a formula, you can press the F4 key on your keyboard to
switch between relative and absolute cell references. This is an easy way to
quickly insert an absolute reference.
To create and copy a formula using absolute references
In our example, we'll use the 7.5% sales tax rate in cell E1 to calculate the
sales tax for all items in column D. We'll need to use the absolute cell
reference $E$1 in our formula. Since each formula is using the same tax
rate, we want that reference to remain constant when the formula is copied
and filled to other cells in column D.
 Select the cell that will contain the formula. In our example, we'll select
cell D3.
 Enter the formula to calculate the desired value. In our example, we'll
type =(B3*C3)*$E$1.
 Press Enter on your keyboard. The formula will calculate, and the result
will display in the cell.

 Locate the fill handle in the lower-right corner of the desired cell.
 Release the mouse. The formula will be copied to the selected cells
with an absolute reference, and the values will be calculated in each
cell.

Functions
A function is a predefined formula that performs calculations using specific
values in a particular order. Excel includes many common functions that can
be useful for quickly finding the sum, average, count, maximum value, and
minimum value for a range of cells. In order to use functions correctly, you'll
need to understand the different parts of a function and how to create
arguments to calculate values and cell references.
 Formula =A1+A2+A3+A4+A5+A6+A7+A8
Function =SUM(A1:A8)

The parts of a function

In order to work correctly, a function must be written a specific way, which is

called the syntax. The basic syntax for a function is an equals sign (=), the
function name (SUM, for example), and one or more arguments. Arguments
contain the information you want to calculate.

Working with arguments

Arguments can refer to both individual cells and cell ranges and must be
enclosed within parentheses. You can include one argument or multiple
arguments, depending on the syntax required for the function.
For example, the function =AVERAGE(B1:B9) would calculate the average of
the values in the cell range B1:B9. This function contains only one
argument.

Multiple arguments must be separated by a comma. For example, the

function =SUM(A1:A3, C1:C2, E2) will add the values of all the cells in the
three arguments.

Creating a function
Excel has a variety of functions available. Here are some of the most common
functions you'll use:
 SUM: This function adds all of the values of the cells in the argument.
 AVERAGE: This function determines the average of the values included
in the argument. It calculates the sum of the cells and then divides
that value by the number of cells in the argument.
 COUNT: This function counts the number of cells with numerical
data in the argument. This function is useful for quickly counting
items in a cell range.
 MAX: This function determines the highest cell value included in the
argument.
 MIN: This function determines the lowest cell value included in the
argument.
To create a basic function
In our example below, we'll create a basic function to calculate the average
price per unit for a list of recently ordered items using the AVERAGE function.
 Select the cell that will contain the function.
 Type the equals sign (=) and enter the desired function name. You
can also select the desired function from the list of suggested
functions that will appear below the cell as you type. In our
example, we'll type =AVERAGE.
  Enter the cell range for the argument inside parentheses. In our example,
we'll type (D3:D12).
 Press Enter on your keyboard. The function will be calculated, and
the result will appear in the cell.

To create a function using the AutoSum command

The AutoSum command allows you to automatically insert the most
common functions into your formula, including SUM, AVERAGE, COUNT, MIN,
and MAX. In our example below, we'll create a function to calculate the total
cost for a list of recently ordered items using the SUM function.
 Select the cell that will contain the function.
 In the Editing group on the Home tab, locate and select the arrow next
to the AutoSum command and then choose the desired function from
the drop-down menu. In our example, we'll select Sum.

 The selected function will appear in the cell. If logically placed, the
AutoSum command will automatically select a cell range for the
argument. You can also manually enter the desired cell range into
the argument.
  Press Enter on your keyboard.
The Function Library
While there are hundreds of functions in Excel, the ones you use most
frequently will depend on the type of data your workbooks contains. There
is no need to learn every single function, but exploring some of the
different types of functions will be helpful as you create new projects. You
can search for functions by category, such as Financial, Logical, Text,
Date & Time, and more from the Function Library on the Formulas tab.
To access the Function Library, select the Formulas tab on the Ribbon.
The Function Library will appear.
 If you're having trouble finding the right function, the Insert
Function command allows you to search for functions using
keywords
 The AutoSum command allows you to automatically return results
for common functions, like SUM, AVERAGE, and COUNT.
 The Recently Used command gives you access to functions that you have
recently worked with.
 The Financial category contains functions for financial calculations
like determining a payment (PMT) or interest rate for a loan (RATE).
 Functions in the Logical category check arguments for a value or
condition. For example, if an order is over $50 add $4.99 for
shipping, but if it is over $100, do not charge for shipping (IF).
 The Text category contains functions that work with the text in
arguments to perform tasks, such as converting text to lowercase
(LOWER) or replacing text (REPLACE).
 The Date & Time category contains functions for working with dates
and time and will return results like the current date and time
(NOW) or the seconds (SECOND).
 The Lookup & Reference category contains functions that will return
results for finding and referencing information. For example, you can
add a hyperlink (HYPERLINK) to a cell or return the value of a
particular row and column intersection (INDEX).
 The Math & Trig category includes functions for numerical arguments.
For example, you can round values (ROUND), find the value of Pi (PI)
multiply (PRODUCT), subtotal (SUBTOTAL), and much more.
 More Functions contains additional functions under categories for
Statistical, Engineering, Cube, Information, and Compatibility.
To insert a function from the Function Library
 Select the cell that will contain the function.
 Click the Formulas tab on the Ribbon to access the Function Library
 From the Function Library group, select the desired function category.
 Select the desired function from the drop-down menu.
  The Function Arguments dialog box will appear. From here, you'll
be able to enter or select the cells that will make up the
arguments in the function.

 When you're satisfied with the arguments, click OK

 The function will be calculated, and the result will appear in the cell.
Like formulas, functions can be copied to adjacent cells. Hover the mouse
over the cell that contains the function, then click, hold, and drag the fill
handle over the cells you wish to fill. The function will be copied, and values
for those cells will be calculated relative to their rows or columns
31 | P a g e
The Insert Function command
If you're having trouble finding the right function, the Insert Function
command allows you to search for functions using keywords. While it can be
extremely useful, this command is sometimes a little difficult to use. If you
don't have much experience with functions, you may have more success
browsing the Function Library instead. For more advanced users, however,
the Insert Function command can be a powerful way to find a function
quickly.
To use the Insert Function command
 Select the cell that will contain the function.
 Click the Formulas tab on the Ribbon, then select the Insert Function
command.
 The Insert Function dialog box will appear.
 Type a few keywords describing the calculation you want the function to
perform, then click Go.
 Review the results to find the desired function, then click OK.

 The Function Arguments dialog box will appear.

 When you're satisfied, click OK.
 The function will be calculated, and the result will appear in the cell
Excel Formulas You Should Definitely Know
1. SUM
The SUM formula does exactly what you would expect. It allows you to
add 2 or more numbers together. You can use cell references as well in
this formula.
Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)
2. COUNT

The count formula counts the number of cells in a range that

have numbers in them. It only counts the cells where there
are numbers.
Formula: =COUNT(A1:A10)

3. COUNTA
Counts the number of non-empty cells in a range. It will count cells that
have numbers and/or any other characters in them.
The COUNTA Formula works with all data types
It counts the number of non-empty cells no matter
the data type Formula: =COUNTA(A1:A10)
4. LEN
The LEN formula counts the number of characters in a cell. This includes
spaces!

Notice the difference in the formula results: 10 characters without

spaces in between the words, 12 with spaces between the words.

Formula: =LEN(A1)

5. VLOOKUP
Basically, VLOOKUP lets you search for specific information in your
spreadsheet. For example, if you have a list of products with prices,
you could search for the price of a specific item.

We’re going to use VLOOKUP to find the price of the Photo frame. You
can probably already see that the price is $9.99, but that’s because
this is a simple example. Once you learn how to use VLOOKUP, you’ll
be able to use it with larger, more complex spreadsheets, and that’s
when it will become truly useful.

Formula: =VLOOKUP(lookup_value, table_array, col_index_num,

range_lookup)
As with any formula, you’ll start with an equal sign (=). Then, type the formula
name
=VLOOKUP(“Photo frame”
The second argument is the cell range that contains the data. In this
example, our data is in A2:B16. As with any function, you’ll need to use a
comma to separate each argument:
=VLOOKUP(“Photo frame”, A2:B16
Note: It’s important to know that VLOOKUP will always search the first column
in this range. In this example, it will search column A for “Photo frame”. In
some cases, you may need to move the columns around so that the first
column contains the correct data.
The third argument is the column index number. It’s simpler than it sounds:
The first column in the range is 1, the second column is 2, etc. In this case,
we are trying to find the price of the item, and the prices are contained in
the second column. That means our third argument will be 2:
=VLOOKUP(“Photo frame”, A2:B16, 2
The fourth argument tells VLOOKUP whether to look for approximate
matches, and it can be either TRUE or FALSE. If it is TRUE, it will look for
approximate matches. Generally, this is only useful if the first column has
numerical values that have been sorted. Since we’re only looking for exact
matches, the fourth argument should be FALSE. This is our last argument, so
go ahead and close the parentheses:
=VLOOKUP(“Photo frame”, A2:B16, 2, FALSE)
And that’s it! When you press enter, it should give you the answer, which is
9.99.

6. IF Statements
Formula: =IF(logical_statement, return this if logical statement is true,
return this if logical statement is false).

Example:

Let’s say a salesperson has a quota to meet. You used VLOOKUP to

put the revenue next to the name. Now you can use an IF statement
that says: “IF the salesperson met their quota, say “Met quota”, if not
say “Did not meet quota”

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

This IF statement will tell us if the first salesperson met their quota or not.
We would then copy and paste this formula along all the entries in the list.
It would change for each sales person
Working with Data

Whenever you're working with a lot of data, it can be difficult to compare

information in your workbook

Freezing Panes and View Options

Excel includes several tools that make it easier to view content from
different parts of your workbook at the same time, such as the ability to
freeze panes and split your worksheet.

To freeze rows

You may want to see certain rows or columns all the time in your
worksheet, especially header cells. By freezing rows or columns in place,
you'll be able to scroll through your content while continuing to view the
frozen cells.

 Select the row below the row(s) you wish to freeze.

 Click the View tab on the Ribbon.
 Select the Freeze Panes command, then choose Freeze Panes from the drop-
down menu.
The rows will be frozen in place, as indicated by the gray line. You can scroll
down the worksheet while continuing to view the frozen rows at the top.
To freeze columns
 Select the column to the right of the column(s) you wish to freeze
 Click the View tab on the Ribbon.
 Select the Freeze Panes command, then choose Freeze Panes from the
drop-down menu.
 The column will be frozen in place, as indicated by the gray line.
You can scroll across the worksheet while continuing to view the
frozen column on the left
To unfreeze rows or columns, click the Freeze Panes command, then
select Unfreeze Panes from the drop-down menu.

To split a worksheet
Sometimes you may want to compare different sections of the same workbook
without creating a new window. The Split command allows you to divide the
worksheet into multiple panes that scroll separately.
 Select the cell where you wish to split the worksheet.
 Click the View tab on the Ribbon, then select the Split command.

 The workbook will be split into different panes. You can scroll
 through each pane separately using the scroll bars, allowing you to
compare different sections of the workbook.

To remove the split, click the Split command again.

Sorting Data
As you add more content to a worksheet, organizing that information
becomes especially important. You can quickly reorganize a worksheet by
sorting your data. For example, you could organize a list of contact
information by last name. Content can be sorted alphabetically, numerically,
and in many other ways.
When sorting data, it's important to first decide if you would like the sort to
apply to the entire worksheet or just a cell range.
 Sort sheet organizes all of the data in your worksheet by one column.
 Sort range sorts the data in a range of cells, which can be helpful
when working with a sheet that contains several tables. Sorting a
range will not affect other content on the worksheet.
To sort a sheet
In our example, we'll sort a T-shirt order form alphabetically by Last Name
(column C)
 Select a cell in the column you wish to sort by. In our example, we'll
select cell C2.

 Select the Data tab on the Ribbon, then click the Ascending
command to Sort A to Z, or the Descending command to Sort Z to
A. In our example, we'll click the Ascending command.
  The worksheet will be sorted by the selected column. In our example,
the worksheet is now sorted by last name.

Filtering Data
If your worksheet contains a lot of content, it can be difficult to find
information quickly. Filters can be used to narrow down the data in your
worksheet, allowing you to view only the information you need.
To filter data
 In order for filtering to work correctly, your worksheet should
include a header row, which is used to identify the name of each
column.
 Select the Data tab, then click the Filter command.

 A drop-down arrow will appear in the header cell for each column.
 Click the drop-down arrow for the column you wish to filter.
 The Filter menu will appear.
 Uncheck the box next to Select All to quickly deselect all data.
 Check the boxes next to the data you wish to filter, then click OK.
To remove all filters from your worksheet, click the Filter command on the Data
tab.

Working with Charts

Creating a chart in Microsoft Office Excel is quick and easy. Excel provides a
variety of chart types that you can choose from when you create a chart.
Excel offers Pie, Line, Bar, and Column charts to name but a few. Showing
data in a chart can make it clearer, more interesting and easier to read.
Charts can also help you evaluate your data and make comparisons between
different values.
Understanding charts
Excel has several different types of charts, allowing you to choose the one
that best fits your data. In order to use charts effectively, you'll need to
understand how different charts are used.
Types of Charts:
 Column charts use vertical bars to represent data. They can work
with many different types of data, but they're most frequently used
for comparing information.
 Line charts are ideal for showing trends. The data points are
connected with lines, making it easy to see whether values are
increasing or decreasing over time.
 Pie charts make it easy to compare proportions. Each value is
shown as a slice of the pie, so it's easy to see which values make up
the percentage of a whole.
 Bar charts work just like Column charts, but they use horizontal bars
instead of vertical bars.
 Area charts are similar to line charts, except that the areas under the
lines are filled in.
 Surface charts allow you to display data across a 3D landscape.
They work best with large data sets, allowing you to see a variety of
information at the same time.
To insert a chart
 Select the cells you want to chart, including the column titles and row
labels. These cells will be the source data for the chart.
 From the Insert tab, click the desired Chart command.
 Choose the desired chart type from the drop-down menu.
The selected chart will be inserted in the worksheet
TIP: If you're not sure which type of chart to use, the Recommended Charts
command will suggest several different charts based on the source data.

Chart layout and style

After inserting a chart, there are several things you may want to change
about the way your data is displayed. It's easy to edit a chart's layout and
style from the Design tab.
 Excel allows you to add chart elements—such as chart titles, legends,
and data labels—to make your chart easier to read. To add a chart
element, click the Add Chart Element command on the Design tab,
then choose the desired element from the drop-down menu.

 To edit a chart element, like a chart title, simply double-click the

placeholder and begin typing.
  If you don't want to add chart elements individually, you can use one
of Excel's predefined layouts. Simply click the Quick Layout
command, then choose the desired layout from the drop- down menu
 Excel also includes several different chart styles, which allow you to
quickly modify the look and feel of your chart. To change the chart
style, select the desired style from the Chart styles group.

TIP: You can also use the chart formatting shortcut buttons to quickly add
chart elements, change the chart style, and filter the chart data.
Other chart options
There are lots of other ways to customize and organize your charts. For
example, Excel allows you to rearrange a chart's data, change the chart type,
and even move the chart to a different location in the workbook.

To switch row and column data

Sometimes you may want to change the way charts group your data. For
example, in the chart below, the Book Sales data are grouped by year, with
columns for each genre. However, we could switch the rows and columns so
the chart will group the data by genre, with columns for each year. In both
cases, the chart contains the same data—it's just organized differently.
Select the chart you wish to modify.
From the Design tab, select the Switch Row/Column command

The rows and columns will be switched.

To change the chart type

If you find that your data isn't well suited to a certain chart, it's easy to switch
to a new chart type. In our example, we'll change our chart from a Column
chart to a Line chart.
 From the Design tab, click the Change Chart Type command

 The Change Chart Type dialog box will appear.

 The selected chart type will appear.
To move a chart
Whenever you insert a new chart, it will appear as an object on the same
worksheet that contains its source data. Alternatively, you can move the
chart to a new worksheet to help keep your data organized.
 Select the chart you wish to move.
 Click the Design tab, then select the Move Chart command.

 The Move Chart dialog box will appear. Select the desired location for the
chart.
 Click OK.
 The chart will appear in the selected location.

Printing Workbooks
There may be times when you want to print a workbook to view and share
your data offline. Once you've chosen your page layout settings, it's easy to
preview and print a workbook from Excel using the Print pane.

To access the Print pane

Select the File tab. Backstage view will appear
Select Print. The Print pane will appear

Choosing a print area

Before you print an Excel workbook, it's important to decide exactly what
information you want to print. For example, if you have multiple worksheets
in your workbook, you will need to decide if you want to print the entire
workbook or only active worksheets. There may also be times when you
want to print only a selection of content from your workbook.
To print active sheets
Worksheets are considered active when selected.
 Select the worksheet you want to print. To print multiple
worksheets, click the first worksheet, hold the Ctrl key on your
keyboard, then click any other worksheets you want to select.

 Navigate to the Print pane.

 Select Print Active Sheets from the Print Range drop-down menu.

 Click the Print button.

To print the entire workbook
 Navigate to the Print pane.
 Select Print Entire Workbook from the Print Range drop-down menu.

 A preview of your selection will appear in the Preview pane.

 Click the Print button to print the selection.
TIP: If you prefer, you can also set the print area in advance so you'll be
able to visualize which cells will be printed as you work in Excel. Simply
select the cells you want to print, click the Page Layout tab, select the
Print Area command, then choose Set Print Area.

Fitting and scaling content

On occasion, you may need to make small adjustments from the Print
pane to fit your workbook content neatly onto a printed page. The Print
pane includes several tools to help fit and scale your content, such as
scaling and page margins.
To fit content before printing
If some of your content is being cut off by the printer, you can use scaling
to fit your workbook to the page automatically.
 Navigate to the Print pane.
 Select the desired option from the Scaling drop-down menu. In our
example, we'll select Fit Sheet on One Page.

 The worksheet will be condensed to fit onto a single page.

 When you're satisfied with the scaling, click Print.
To modify margins in the Preview pane
Sometimes you may only need to adjust a single margin to make your data fit
more comfortably. You can modify individual page margins from the Preview
pane
 Navigate to the Print pane, then click the Show Margins button in the
lower-right corner

 The page margins will appear in the Preview pane. Hover the
mouse over one of the margin markers until the cursor becomes a
double arrow.
 Click, hold, and drag the mouse to increase or decrease the margin width.
 Release the mouse. The margin will be modified. In our
example, we were able to fit an additional column on the page.