0% found this document useful (0 votes)

44 views72 pages

Excel Introduction and Basics

Uploaded by

yaashwanth811

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

44 views72 pages

Excel Introduction and Basics

Uploaded by

yaashwanth811

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

You are on page 1/ 72

Introduction

1 Range: A range in Excel is a collection of two or more cells.

2 Formulas and Functions: A formula is an expression which calculates the value of a cell. Functions

are predefined formulas and are already available in Excel.

Range

A range in Excel is a collection of two or more cells. This chapter gives an overview of some very

important range operations.

Cell, Row, Column

Let's start by selecting a cell, row and column.

1. To select cell C3, click on the box at the intersection of column C and row 3.

2. To select column C, click on the column C header.

3. To select row 3, click on the row 3 header.

Range Examples
A range is a collection of two or more cells.

1. To select the range B2:C4, click on cell B2 and drag it to cell C4.

2. To select a range of individual cells, hold down CTRL and click on each cell that you want to

include in the range.

Fill a Range
To fill a range, execute the following steps.

1a. Enter the value 2 into cell B2.

1b. Select cell B2, click on the lower right corner of cell B2 and drag it down to cell B8.

Result:

This dragging technique is very important and you will use it very often in Excel. Here's another

example.

2a. Enter the value 2 into cell B2 and the value 4 into cell B3.
2b. Select cell B2 and cell B3, click on the lower right corner of this range and drag it down.

Excel automatically fills the range based on the pattern of the first two values. Pretty cool, huh?

Here's another example.

3a. Enter the date 6/13/2030 into cell B2 and the date 6/16/2030 into cell B3.

3b. Select cell B2 and cell B3, click on the lower right corner of this range and drag it down.
Move a Range
To move a range, execute the following steps.

1. Select a range and click on the border of the range.

2. Drag the range to its new location.

Copy/Paste a Range

To copy and paste a range, execute the following steps.

1. Select the range, right click, and then click Copy (or press CTRL + c).

2. Select the cell where you want the first cell of the range to appear, right click, and then click Paste

under 'Paste Options:' (or press CTRL + v).

Insert Row, Column
To insert a row between the values 20 and 40 below, execute the following steps.

1. Select row 3.

2. Right click, and then click Insert.

Result:
The rows below the new row are shifted down. In a similar way, you can insert a column.

Formulas and Functions

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and

are already available in Excel.

Cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.

Cell A3 below contains the SUM function which calculates the sum of the range A1:A2.

Enter a Formula
To enter a formula, execute the following steps.

1. Select a cell.

2. To let Excel know that you want to enter a formula, type an equal sign (=).
3. For example, type the formula A1+A2.

Tip: instead of typing A1 and A2, simply select cell A1 and cell A2.

4. Change the value of cell A1 to 3.

Excel automatically recalculates the value of cell A3. This is one of Excel's most powerful features!

Edit a Formula
When you select a cell, Excel shows the value or formula of the cell in the formula bar.

1. To edit a formula, click in the formula bar and change the formula.
2. Press Enter.

Operator Precedence
Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that

part will be calculated first. It then performs multiplication or division calculations. Once this is

complete, Excel will add and subtract the remainder of your formula. See the example below.

First, Excel performs multiplication (A1 * A2). Next, Excel adds the value of cell A3 to this result.

Another example,
First, Excel calculates the part in parentheses (A2+A3). Next, it multiplies this result by the value of

cell A1.

Copy/Paste a Formula
When you copy a formula, Excel automatically adjusts the cell references for each new cell the

formula is copied to. To understand this, execute the following steps.

1. Enter the formula shown below into cell A4.

2a. Select cell A4, right click, and then click Copy (or press CTRL + c)...

...next, select cell B4, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).
2b. You can also drag the formula to cell B4. Select cell A4, click on the lower right corner of cell A4

and drag it across to cell B4. This is much easier and gives the exact same result!

Result: the formula in cell B4 references the values in column B.

Insert Function
Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM.

The part between the brackets (arguments) means we give Excel the range A1:A4 as input. This

function adds the values in cells A1, A2, A3 and A4. It's not easy to remember which function and

which arguments to use for each task. Fortunately, the Insert Function feature in Excel helps you with

this.

To insert a function, execute the following steps.

1. Select a cell.
2. Click the Insert Function button.

The 'Insert Function' dialog box appears.

3. Search for a function or select a function from a category. For example, choose COUNTIF from the

Statistical category.

4. Click OK.

The 'Function Arguments' dialog box appears.

5. Click in the Range box and select the range A1:C2.

6. Click in the Criteria box and type >5.

7. Click OK.
Result: the COUNTIF function counts the number of cells that are greater than 5.

Note: instead of using the Insert Function feature, simply type =COUNTIF(A1:C2,">5"). When you

arrive at: =COUNTIF( instead of typing A1:C2, simply select the range A1:C2.
Basics
1 Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to use the ribbon.

2 Workbook: A workbook is another word for your Excel file. When you start Excel, click Blank

workbook to create an Excel workbook from scratch.

3 Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. Each

Excel workbook can contain multiple worksheets.

4 Format Cells: When we format cells in Excel, we change the appearance of a number without

changing the number itself.

5 Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.

6 Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based

on a template. There are many free templates available, waiting to be used.

7 Data Validation: Use data validation in Excel to make sure that users enter certain values into a cell.

8 Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your

mouse to increase your speed.

9 Print: This chapter teaches you how to print a worksheet and how to change some important print

settings in Excel.

10 Share: Learn how to share Excel data with Word documents and other files.

11 Protect: Encrypt an Excel file with a password so that it requires a password to open it.

Transpose | Split Cells | Flash Fill | Move Columns | ROW function

Percentage | Named Range | Dynamic Named Range | Paste Options | Discount

Name Box

You can use the name box in Excel to select a cell, range or named range. You can also use the name

box to quickly create a named range (important).

1. We start simple. The name box below displays the address of the active cell (B6).

2. You can use the name box to select a cell, range or named range. For example, click in the name

box and type E2:F8.

3. Press Enter.
Note: the name box only displays the upper-left cell of a selected range.

4. Use the name box to quickly create a named range. For example, select the range A2:A9 below,

click in the name box and type Scores.

5. Press Enter.
6. Now let's use this named range in a formula.

7. Finally, click the drop-down arrow in the name box to see a list of all the named ranges in a

workbook. For example, click Ages.

8. Excel selects the named range Ages on Sheet2.
Named Range

Create a named range or a named constant and use these names in your Excel formulas. This way

you can make your formulas easier to understand.

Create a Named Range

To create a named range in Excel, execute the following steps.

1. For example, select the range A1:A4.

2. On the Formulas tab, in the Defined Names group, click Define Name.

3. Enter a name and click OK.

There's an even quicker way of doing this.

4. Select the range, type the name in the Name box and press Enter.

5. Now you can use this named range in your formulas. For example, sum Prices.

Named Constant
To create a named constant, execute the following steps.

1. On the Formulas tab, in the Defined Names group, click Define Name.

2. Enter a name, type a value, and click OK.

3. Now you can use this named constant in your formulas.

Name Manager
Use the Name Manager in Excel to view, edit and delete named ranges and named constants.

1. On the Formulas tab, in the Defined Names group, click Name Manager.

2. For example, select TaxRate and click Edit.

3. Change the tax rate from 0.1 to 0.2.

4. Click OK.

Result: Excel automatically updates all the formulas that use TaxRate.
Create from Selection
If your data has labels, you can quickly create named ranges in Excel.

1. For example, select the range A1:D13.

2. On the Formulas tab, in the Defined Names group, click Create from Selection.

3. Check Top row and Left column and click OK.

4. Excel created 12 + 3 = 15 named ranges! Simply select a range and look at the Name box.

5. Use the intersect operator (space) to return the intersection of two named ranges.
Dynamic Named Range

A dynamic named range expands automatically when you add a value to the range.

1. For example, select the range A1:A4 and name it Prices.

2. Calculate the sum.

3. When you add a value to the range, Excel does not update the sum.
To expand the named range automatically when you add a value to the range, execute the following

steps.

4. On the Formulas tab, in the Defined Names group, click Name Manager.

5. Click Edit.
6. Click in the "Refers to" box and enter the formula =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Explanation: the OFFSET function takes 5 arguments. Reference: $A$1, rows to offset: 0, columns to

offset: 0, height: COUNTA($A:$A) and width: 1. COUNTA($A:$A) counts the number of values in

column A that are not empty. When you add a value to the range, COUNTA($A:$A) increases. As a

result, the range returned by the OFFSET function expands.

7. Click OK and Close.

8. Now, when you add a value to the range, Excel updates the sum automatically.

Cell References

Cell references in Excel are very important. Understand the difference between relative, absolute and

mixed reference, and you are on your way to success.

Relative Reference
By default, Excel uses relative references. See the formula in cell D2 below. Cell D2 references (points

to) cell B2 and cell C2. Both references are relative.

1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.

Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5

and cell C5. In other words: each cell references its two neighbors on the left.

Absolute Reference
See the formula in cell E3 below.

1. To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row

number ($H$3) in the formula of cell E3.

2. Now we can quickly drag this formula to the other cells.

The reference to cell H3 is locked (when we drag the formula down and across). As a result, the

correct lengths and widths in inches are calculated. Visit our page about absolute reference to learn

more about this type of reference.

Absolute Reference

To create an absolute reference in Excel, add $ symbols to a cell or range reference. This locks the

reference. When you copy a formula, an absolute reference never changes.

Cell
Let's start by locking a reference to a cell.

1. Cell C2 below refers to cell B2 and cell E2. Both references are relative.
2. This is what happens when we copy the formula in cell C2 to cell C3.

3. Lock the reference to cell E2 by placing a $ symbol in front of the column letter and row number

like this: $E$2. This creates an absolute reference.

4. To quickly copy the formula in cell C2 to the other cells, select cell C2, click on the lower right

corner of cell C2 and drag it down to cell C7.

Check:
Explanation: the absolute reference ($E$2) stays the same, while the relative reference (B2) changes

to B3, B4, B5, B6 and B7.

Range
Sometimes you need to lock a reference to a range. For example, when using the RANK function to

rank numbers in a data set.

1. Cell C2 below refers to cell B2 and the range B2:B7. Both references are relative.

2. This is what happens when we copy the formula in cell C2 to cell C3.

3. Lock the reference to the range B2:B7 by placing $ symbols in front of the column letters and row

numbers.
4. To quickly copy the formula in cell C2 to the other cells, select cell C2, click on the lower right

corner of cell C2 and drag it down to cell C7.

Check:
Explanation: the absolute reference ($B$2:$B$7) stays the same, while the relative reference (B2)

changes to B3, B4, B5, B6 and B7.

Named Range
Instead of using absolute range references, create a named range. This way you can make your

formulas easier to understand.

1. Select the range B2:B7, type a name in the Name box and press Enter.

2. Now you can use this named range in your formulas. Select cell C2, enter the RANK function

shown below and copy this formula to the other cells.

Check:
F4
Use the F4 key to quickly toggle between all 4 types of cell references.

1. For example, select cell C2 below, click in the formula bar and move the insertion point in or to the

right of E2.

2. Press F4 to create an absolute reference.

3. Press F4 again to create a mixed reference where the column is relative and the row is absolute.

4. Press F4 again to create a mixed reference where the column is absolute and the row is relative.
Mixed Reference
Sometimes we need a combination of relative and absolute reference (mixed reference).

1. See the formula in cell F2 below.

2. We want to copy this formula to the other cells quickly. Drag cell F2 across one cell, and look at

the formula in cell G2.

Do you see what happens? The reference to the price should be a locked reference to column B.

Solution: place a $ symbol in front of the column letter ($B2) in the formula of cell F2. In a similar

way, when we drag cell F2 down, the reference to the reduction should be a locked reference to row

6. Solution: place a $ symbol in front of the row number (B$6) in the formula of cell F2.

Result:
Note: we don't place a $ symbol in front of the row number of $B2 (this way we allow the reference

to change from $B2 (Jeans) to $B3 (Shirts) when we drag the formula down). In a similar way, we

don't place a $ symbol in front of the column letter of B$6 (this way we allow the reference to

change from B$6 (Jan) to C$6 (Feb) and D$6 (Mar) when we drag the formula across).

3. Now we can quickly drag this formula to the other cells.

The references to column B and row 6 are locked.

3D-reference

A 3D-reference in Excel refers to the same cell or range on multiple worksheets. First, we'll look at the

alternative.

1. On the Company sheet, select cell B2 and type an equal sign =

2. Go to the North sheet, select cell B2 and type a +

3. Repeat step 2 for the Mid and South sheet.

Result:

4. This is quite a lot of work. Instead of doing this, use the following 3D-reference: North:South!B2 as

the argument for the SUM function.

5. If you add worksheets between North and South, this worksheet is automatically included in the

formula in cell B2.

External References

An external reference in Excel is a reference to a cell or range of cells in another workbook. Below

you can find the workbooks of three divisions (North, Mid and South).
Create External Reference

To create an external reference, execute the following steps.

1. Open all workbooks.

2. In the Company workbook, select cell B2 and type the equal sign =
3. On the View tab, in the Window group, click Switch Windows.

4. Click North.

5. In the North workbook, select cell B2.

6. Type a +

7. Repeat steps 3 to 6 for the Mid workbook.

8. Repeat steps 3 to 5 for the South workbook.

9. Remove the $ symbols in the formula of cell B2.

Result:

10. Copy the formula to the other cells.

Alert
Close all workbooks. Change a number in the workbook of a division. Close all workbooks again.

Open the Company workbook.

A. To update all links, click Enable Content.

B. To not update the links, click the X.

Note: if you see another alert, click Update or Don't Update.

Edit Links
On the Data tab, in the Queries & Connections group, click Edit Links to launch the Edit Links dialog

box.

1. If you didn't update the links, you can still update the links here. Select a workbook and click

Update Values to update the links to this workbook. Note how the Status changes to OK.
2. If you don't want to display the alert and update the links automatically, Click Startup Prompt,

select the third option, and click OK.

Mixed Reference
Sometimes we need a combination of relative and absolute reference (mixed reference).
1. See the formula in cell F2 below.