Introduction to MS Excel

DEPARTMENT OF CIVIL ENGINEERING

NED UNIVERSITY of Engineering and Technology,
Karachi-75270, Pakistan.
Getting Familiar with Excel Interface

After starting Excel, you will see two windows - one within the other. The outer window is the Application
Window and the inner window is the Workbook Window. When maximized, the Excel Workbook Window
blends in with the Application Window.

Name Box

Row

Zoom Control

The Application Window

The Application Window provides the space for your worksheets and workbook elements such as charts. The
components of the Application Window are described below.

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

1. Click the drop-down arrow to the right of the Quick Access toolbar.
2. Select the command you wish to add from the drop-down menu. To choose from more commands, select
More Commands.

3. The command will be added to the Quick Access toolbar.

The Ribbon

Excel uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs, each with
several groups of commands. You will use these tabs to perform the most common tasks in Excel.

Clicka tab to see

into groups

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.

Click the Expand Ribbon

appears in full screen command to show the Ribbon

To Customize the Ribbon in Excel

You can customize the Ribbon by creating your own tabs with whichever commands you want. Commands are
always housed within a group, and you can create as many groups as you want in order to keep your tab
organized. If you want, you can even add commands to any of the default tabs, as long as you create a custom
group in the tab.

1. Right-click the Ribbon and then select Customize the Ribbon... from the drop-down menu.
2. The Excel Options dialog box will appear. Locate and select New Tab.

3. Make sure the New Group is selected, select a command, and then click Add. You can also drag commands
directly into a group

4. When you are done adding commands, click OK. The commands will be added to the Ribbon

Select

click Add
 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.

In the image below, cell C1 is selected and 1984 is entered into the formula bar. Note how the data appears in
both the formula bar and in cell C1.

The Name Box

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

In the image below, cell B4 is selected. Note that cell B4 is where column B and row 4 intersect.

The Backstage View (The File Menu)

Click the File tab on the Ribbon. Backstage view will appear.

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.

Page Layout
View Page Break
View

Zoom Control

To use the Zoom control, click and drag the slider. The number to the right of the slider reflects the zoom
percentage.
 Cell Basics

Whenever you work with Excel, you'll enter information, or content, into cells. Cells are the basic
building blocks of a worksheet. You'll need to learn the basics of cells and cell content to calculate,
analyze, and organize data in Excel.

Understanding Cells

Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection
of a row and a column. Columns are identified by letters (A, B, C), while rows are identified by
numbers (1, 2, 3).

Row

Each cell has its own name, or cell address, based on its column and row. In this example, the selected
cell intersects column C and row 5, so the cell address is C5. The cell address will also appear in the Name box.
Note that a cell's column and row headings are highlighted when the cell is selected.

You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell
address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by
a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.

In the images below, two different cell ranges are selected:

 Cell range A1:A8

 Cell range A1:B8

To select a cell range

Sometimes you may want to select a larger group of cells, or a cell range.

1. Click, hold, and drag the mouse until all of the adjoining cells you wish to select are highlighted.
2. Release the mouse to select the desired cell range. The cells will remain selected until you click
another cell in the worksheet.
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. In our example, SUM (B4:B7) adds the
value of each cell in cell range B4:B7 and displays the total in cell B8.
To insert content

1. Click a cell to select it.

2. Type content into the selected cell, then press Enter on your keyboard. The content will appear in the
cell and the formula bar. You can also input and edit cell content in the formula bar.

and formula bar

To delete cell content

1. Select the cell with content you wish to delete.

2. Press the Delete or Backspace key on your keyboard. The cell's contents will be deleted.

To delete cells

There is an important difference between deleting the content of a cell and deleting the cell itself. If
you delete the entire cell, the cells below it will shift up and replace the deleted cells.

1. Select the cell(s) you wish to delete.

2. Select the Delete command from the Home tab on the Ribbon.

3. The cells below will shift up.

To copy and paste cell content

Excel allows you to copy content that is already entered into your spreadsheet and paste that content to other
cells, which can save your time and effort.

1. Select the cell(s) you wish to copy.

2. Click the Copy command on the Home tab, or press Ctrl+C on your keyboard.

3. Select the cell(s) where you wish to paste the content. The copied cells will now have a dashed box
around them.

4. Click the Paste command on the Home tab, or press Ctrl+V on your keyboard.

5. The content will be pasted into the selected cells.

To access more paste options

You can also access additional paste options, which are especially convenient when working with
cells that contain formulas or formatting.

To access more paste options, click the drop-down arrow on the Paste command.

TIP: Rather than choosing commands from the Ribbon, you can access commands quickly by
right- clicking. Simply select the cell(s) you wish to format, then right-click the mouse. A drop-
down menu will appear, where you'll find several commands that are also located on the
Ribbon.
To drag and drop cells

Rather than cutting, copying, and pasting, you can drag and drop cells to move their contents.
1. Select the cell(s) you wish to move.

2. Hover the mouse over the border of the selected cell(s) until the cursor changes from a white cross
to a black cross with four arrows.

3. Click, hold, and drag the cells to the desired location.

4. Release the mouse, and the cells will be dropped in the selected location.
To use the fill handle

There may be times when you need to copy the content of one cell to several other cells in your
worksheet. You could copy and paste the content into each cell, but this method would be very time
consuming. Instead, you can use the fill handle to quickly copy and paste content to adjacent cells in the same
row or column.

1. Select the cell(s) containing the content you wish to use. The fill handle will appear as a small
square in the bottom-right corner of the selected cell(s).
 2. Click, hold, and drag the fill handle until all of the cells you wish to fill are selected.

3. Release the mouse to fill the selected cells.

To continue a series with the fill handle

The fill handle can also be used to continue a series. Whenever the content of a row or column follows a
sequential order, like numbers (1, 2, 3) or days (Monday, Tuesday, Wednesday), the fill handle can guess what
should come next in the series. In many cases, you may need to select multiple cells before using the fill handle
to help Excel determine the series order. In our example below, the fill handle is used to extend a series of
dates in a column.

Find and Replace

When working with a lot of data in Excel, it can be difficult and time consuming to locate specific
information. You can easily search your workbook using the Find feature, which also allows you to modify
content using the Replace feature.
To find content

1. From the Home tab, click the Find and Select command, then select Find... from the drop-down
menu.

2. The Find and Replace dialog box will appear. Enter the content you wish to find.

3. Click Find Next. If the content is found, the cell containing that content will be selected.

4. Click Find Next to find further instances or Find All to see every instance of the search term.

5. When you are finished, click Close to exit the Find and Replace dialog box.

TIP: You can also access the Find command by pressing Ctrl+F on your keyboard.

TIP: Click Options to see advanced search criteria in the Find and Replace dialog box.
To replace cell content

At times, you may discover that you've repeatedly made a mistake throughout your workbook (such as
misspelling someone's name), or that you need to exchange a particular word or phrase for another. You can use
Excel's Find and Replace feature to make quick revisions.
 1. From the Home tab, click the Find and Select command, then select Replace... from the drop- down
menu.

2. The Find and Replace dialog box will appear. Type the text you wish to find in the Find what: field.

3. Type the text you wish to replace it with in the Replace with: field, then click Find Next.

4. If the content is found, the cell containing that content will be selected.

5. Review the text to make sure you want to replace it.

6. If you wish to replace it, select one of the replace options:

 Replace will replace individual instances.

 Replace All will replace every instance of the text throughout the workbook. In our example, we'll
choose this option to save time.

7. A dialog box will appear, confirming the number of replacements made. Click OK to continue.
8. When you are finished, click Close to exit the Find and Replace dialog box.

Font Formatting

To change the font

By default, the font of each new workbook is set to Calibri. However, Excel provides a variety of other fonts
you can use to customize your cell text. In the example below, we'll format our title cell to help distinguish
it from the rest of the worksheet.

1. Select the cell(s) you wish to modify.

2. Click the drop-down arrow next to the Font command on the Home tab. The Font drop-down
menu will appear.

3. Select the desired font. A live preview of the new font will appear as you hover the mouse over
different options.
 4. The text will change to the selected font.

TIP: When creating a workbook in the workplace, you'll want to select a font that is easy to read.
Along with Calibri, standard reading fonts include Cambria, Times New Roman, and Arial.

To change the font size

1. Select the cell(s) you wish to modify.

2. Click the drop-down arrow next to the Font Size command on the Home tab. The Font Size drop-
down menu will appear.

3. Select the desired font size. A live preview of the new font size will appear as you hover the
mouse over different options.

4. The text will change to the selected font size.

TIP: You can also use the Increase Font Size and Decrease Font Size commands or enter a customfont size
using your keyboard.

font size Increase and

Decrease Font
Size commands
To change the font color

1. Select the cell(s) you wish to modify.

2. Click the drop-down arrow next to the Font Color command on the Home tab. The Color
menu will appear.

3. Select the desired font color. A live preview of the new font color will appear as you
hover the mouse over different options.

4. The text will change to the selected font color.

To use the Bold, Italic, and Underline commands

1. Select the cell(s) you wish to modify.

2. Click the Bold (B), Italic (I), or Underline (U) command on the Home tab. In our example, we'll
make the selected cells bold.
 3. The selected style will be applied to the text.

TIP: You can also press Ctrl+B on your keyboard to make selected text bold, Ctrl+I to apply italics,and
Ctrl+U to apply an underline.

Text Alignment

By default, any text entered into your worksheet will be aligned to the bottom-left of a cell. Any numbers will
be aligned to the bottom-right of a cell. Changing the alignment of your cell content allows you to choose
how the content is displayed in any cell, which can make your cell content easier to read.

To change horizontal text alignment

5. Select the cell(s) you wish to modify.

6. Select one of the three horizontalalignment commands on the Home tab. In our example, we'll
choose Center Align.

7. The text will realign.

To change vertical text alignment

1. Select the cell(s) you wish to modify.

2. Select one of the three vertical alignment commands on the Home tab. In our example, we'll
choose Middle Align.
 3. The text will realign.

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

8. Select the cell(s) you wish to modify.

9. Click the drop-down arrow next to the Borders command on the Home tab. The Borders drop-
down menu will appear.

10. Select the border style you want to use.

11. The selected border style will appear.

TIP: You can draw borders and change the line style and color of borders with the Draw Borderstools at
the bottom of the Borders drop-down menu.
 To add a fill color

1. Select the cell(s) you wish to modify.

2. Click the drop-down arrow next to the Fill Color command on the Home tab. The Fill Color menu
will appear.

3. 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.

4. 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 willreplace anyexisting cell formatting except fortext alignment. You m
ay
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.

When you enter a number into the cell,

most appropriate. Number formats

with decimal places.

with a currency symbol.

aligns currency symbols and decimal

places within columns.
as M/D/YYYY.

Long Date formats numbers as

Weekday, Month DD, YYYY.
Time formats numbers as HH/MM/SS
and notes AM or PM.
Percentage formats numbers with

separated by the forward slash.

in scientific notation.

appear exactly as it was entered.

More Number Formats.

1. 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

1. Position the mouse over the column line in the column heading so the white cross becomes a
double arrow .
 2. Click, hold, and drag the mouse to increase or decrease the column width.

3. 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.

1. Position the mouse over the column line in the column heading so the white cross becomes a

double arrow .

2. Double-click the mouse. The column width will be changed automatically to fit the content.

TIP: You canalso AutoFit the width for severalcolumns at the same time. Simply select the columnsyou would
like to AutoFit, then select the AutoFit Column Width command from the Format drop- down menu on
the Home tab. This method can also be used for Row height.
To modify row height

1. Position the cursor over the row line so the white cross becomes a double arrow .

2. Click, hold, and drag the mouse to increase or decrease the row height.

3. 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.

1. Locate and click the Select All button just below the formula bar to select every cell in the
worksheet.

2. Position the mouse over a row line so the white cross becomes a double arrow .

3. Click, hold, and drag the mouse to increase or decrease the row height.

4. 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

• Select the row heading below where you want the new row to appear.

• Click the Insert command on the Home tab.

 • The new row 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.

To insert columns

1. Select the column heading to the right of where you want the new column to appear.

2. Click the Insert command on the Home tab.

 3. The new column will appear to the left of the selected column.

TIP: When inserting rows and columns, make sure you select the entire rowor column by clickingthe heading.
If you select only a cell in the row or column, the Insert command will only insert a new cell.

To delete rows

It's easy to delete any row that you no longer need in your workbook.

1. Select the row(s) you want to delete.

2. Click the Delete command on the Home tab.

3. The selected row(s) will be deleted, and the rows below will shift up.
To delete columns

1. Select the columns(s) you want to delete.

2. Click the Delete command on the Home tab.

3. 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 simplyclearing
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.

1. 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.

2. 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.

3. Click the Insert command on the Home tab, then select Insert Cut Cells fromthe drop-down menu.

4. 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.

1. Select the column(s) you wish to hide, right-click the mouse, then select Hide from the formatting
menu.
 2. The columns will be hidden. The green column line indicates the location of the hidden columns.

Green

3. 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).

4. 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.

1. Select the cells you wish to wrap.

2. Select the Wrap Text command on the Home tab.

 3. 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

1. Select the cell range you want to merge together.

2. Select the Merge & Center command on the Home tab.

3. 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

6. Formulas and Functions

One of the most powerful features in Excel is the ability to calculate numerical information using formulas.
Simple Formulas

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.

Mathematical operators

Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (-), an
asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.

All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the
formula and the value it calculates.
Understanding cell references

While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will
use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure
that your formulas are always accurate because you can change the value of referenced cells without having to
rewrite the formula.

By combining a mathematical operator with cell references, you can create a variety of simple formulas in
Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:
To create a formula

1. Select the cell that will contain the formula.

2. Type the equals sign (=). Notice how it appears in both the cell and the formula bar.

both the cell and the

formula bar.

1. 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.

2. Type the mathematical operator you wish to use. In our example, we'll type the addition sign (+).

3. 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.

4. 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.

1. Select the cell that will contain the formula. In our example, we'll select cell D3.

2. Type the equals sign (=).

3. 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.

4. Type the mathematical operator you wish to use. In our example, we'll type the multiplication sign (*).

5. Select the cell you wish to refer 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.
 6. 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.

1. Select the cell containing the formula you wish to edit.

2. 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.

3. A border will appear around any referenced cells.

4. When finished, press Enter on your keyboard or select the Enter command in the formula bar.

5. 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 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 operations

Excel calculates formulas based on the following order of operations:

3. Operations enclosed in parentheses

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

5. Multiplication and division, whichever comes first

6. Addition and subtraction, whichever comes first

Creating complex formulas

In the example below, we will demonstrate how Excel solves a complex formula using the order of
operations. Here, we want to calculate the cost of sales tax for an invoice. To do this, we'll write our
formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then
multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.
 TIP: It is especially important to enter complex formulas with the correct order of [Link],
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.

1. Select the cell that will contain the formula. In our example, we'll select cell D2.

2. Enter the formula to calculate the desired value. In our example, we'll type =B2*C2.
3. Press Enter on your keyboard. The formula will be calculated, and the result will be displayed in the
cell.

4. Locate the fill handle in the lower-right corner ofthe desired cell. In our example, we'll locate the fill
handle for cell D2.

1. Click, hold, and drag the fill handle over the cells you wish to fill.

2. 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 cel
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: Whenwriting a formula, you canpress the F4 keyon 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.

1. Select the cell that will contain the formula. In our example, we'll select cell D3.

2. Enter the formula to calculate the desired value. In our example, we'll type =(B3*C3)*$E$1.

3. Press Enter on your keyboard. The formula will calculate, and the result will display in the cell.

4. Locate the fill handle in the lower-right corner of the desired cell.

5. 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
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.

Equal sign

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.

1. Select the cell that will contain the function.

2. 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.

3. Enter the cell range for the argument inside parentheses. In our example, we'll type (D3:D12).

4. 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.

1. Select the cell that will contain the function.

2. 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.

3. 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.

4. Press Enter on your keyboard.

6.3.2. 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

1. Select the cell that will contain the function.

2. Click the Formulas tab on the Ribbon to access the Function Library.

3. From the Function Library group, select the desired function category.

4. Select the desired function from the drop-down menu.

1. 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.
2. When you're satisfied with the arguments, click OK.
3. 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.

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

1. Select the cell that will contain the function.

2. Click the Formulas tab on the Ribbon, then select the Insert Function command.

3. The Insert Function dialog box will appear.

4. Type a few keywords describing the calculation you want the function to perform, then click Go.

5. Review the results to find the desired function, then click OK.
 6. The Function Arguments dialog box will appear.

7. When you're satisfied, click OK.

8. The function will be calculated, and the result will appear in the cell.

Excel Formulas You Should Definitely Know

1. SUM

Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

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.

2. COUNT

Formula: =COUNT(A1:A10)
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.

3. COUNTA

Formula: =COUNTA(A1:A10)

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.

4. LEN

Formula: =LEN(A1)

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.

5. VLOOKUP

Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

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.
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.

7. Working with Data

Whenever you're working with a lot of data, it can be difficult to compare information in your workbook.

7.1 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.
 1. Select the row below the row(s) you wish to freeze.

2. Click the View tab on the Ribbon.

3. 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

1. Select the column to the right of the column(s) you wish to freeze.
2. Click the View tab on the Ribbon.

3. Select the Freeze Panes command, then choose Freeze Panes from the drop-down menu.

4. 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.

1. Select the cell where you wish to split the worksheet.

2. Click the View tab on the Ribbon, then select the Split command.

3. 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 ofcells, 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).

4. Select a cell in the column you wish to sort by. In our example, we'll select cell C2.

5. 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.

6. 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

7. In order for filtering to work correctly, your worksheet should include a header row, which is used to
identify the name of each column.

8. Select the Data tab, then click the Filter command.

 9. A drop-down arrow will appear in the header cell for each column.

10. Click the drop-down arrow for the column you wish to filter.
11. The Filter menu will appear.

12. Uncheck the box next to Select All to quickly deselect all data.

13. 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 chartsallow you to display data across a 3D landscape. They workbest with large data sets, allowing
you to see a variety of information at the same time.

To insert a chart

1. Select the cells you want to chart, including the column titles and row labels. These cells will be the
source data for the chart.
2. From the Insert tab, click the desired Chart command.

3. Choose the desired chart type from the drop-down menu.

4. The selected chart will be inserted in the worksheet.

TIP: If you're not sure which type ofchart to use, the Recommended Charts command wilsuggest
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 quicklyadd 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.

1. Select the chart you wish to modify.

2. From the Design tab, select the Switch Row/Column command.

3. 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.

1. From the Design tab, click the Change Chart Type command.

2. The Change Chart Type dialog box will appear.

3. 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.

1. Select the chart you wish to move.

 2. Click the Design tab, then select the Move Chart command.

3. The Move Chart dialog box will appear. Select the desired location for the chart.

4. Click OK.

5. The chart will appear in the selected location.

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.