MS EXCEL LESSON 5

1.0 Cell Reference in Excel

A cell reference (or cell address) is a combination of a column letter and a row number that
identifies a specific cell in a worksheet. These references are essential for formulas and
functions, allowing you to retrieve, calculate, or manipulate data.

For example:
• A2 refers to the cell in column A and row 2.
• B5 refers to the cell in column B and row 5.
These cell addresses can be used in formulas like =A2+B5 to calculate or copy values
between cells.

For example:

Types of Cell Reference in Excel

Understanding various cell references primarily makes it easier for us to use Excel formulas
and avoid unexpected formula errors. When copying and pasting Excel formulas, this is quite
useful. Based on various use situations, Excel offers three main types of cell references,
including:
• Relative Cell Reference
• Absolute Cell Reference
• Mixed Cell Reference

1. Relative Cell Reference

Relative cell references are the default in Excel. They change automatically when a formula is
copied to another cell, maintaining the relative position between cells. There is no dollar ($)
sign in the relative reference for the cell.

How to Use Relative Cell References in Excel (With Examples)

Let's see how to use the Relative Cell references through examples,

Example 1:
If you refer to cell B1 from cell E1, actually you would be referring to a cell that is 3 columns
to the left (E-B = 3) within the same row number 1.
When it is copied to other locations present in a worksheet, the relative reference for that
location will be changed automatically. (because relative cell reference describes offset to
another cell rather than a fixed address as In our example, offset is : 3 columns left in the
same row).

Example 2:
If you copy the formula = C2 / A2 from the cell "E2" to "E3", the formula in E3 will
automatically become =C3/A3.

When to Use Relative Cell References in Excel

When you need to develop a formula for a set of cells and the formula needs to make a
reference to a relative cell reference, relative cell references come in handy.

When this occurs, you can create the formula in one cell and copy it before pasting it into
every other cell.

2. Absolute Cell Reference

When copying or using AutoFill, there are times when the cell reference must stay the same.
A column and/or row reference is kept constant using dollar signs. So, to get an absolute
reference from a relative, we can use the dollar sign ($) characters.
To refer to an actual fixed location on a worksheet whenever copying is done, we use
absolute reference. The reference here is locked such that rows and columns do not shift
when copied.
How to Use Absolute Cell References in Excel
Below is an example depicting how to use Absolute Cell References in Excel.
Example:
To lock the reference A2, use $A$2 in a formula like =C2/$A$2. When copied to other cells,
$A$2 will remain fixed.

G2 = C2/$A$2, when copied to G3, G3 becomes = C3/$A$2

Note: C3 is 4 columns left to G3 in the same row.

Here, original cell reference A2 is maintained whenever we copy G2 to any of the cells. So I3
= E3/$A$2 because E3 comes from the relative reference (4 columns left to the current one)
& /$A$2 comes from the absolute reference.

Therefore, I3 = E3//$A$2 = 12/10 = 1.2

What Does the Dollar ($) Sign Do

When the row and column numbers are preceded by the dollar symbol ($), it becomes
absolute (i.e., stops the row and column number from changing when copied to other cells).
Dollar ($) before the row fixes the row & before the column fixes the column.

Practical Use Cases for Absolute References in Excel

When you need a fixed value in formulas, such as tax rates, constants, or specific cell
addresses. You can also use Absolute Cell Reference Keyboard Shortcut F4.

3. Mixed Cell Reference

Mixed references combine relative and absolute references. You can lock either the row or
the column, depending on your needs. Here, the Dollar ($) before the row number
fixes/locks the row & before the column name fixes/locks the column.

Example:
When we fix the only row: If we have G2 = C2/A$2 then :

We used $ before the row number, so we are locking the only row here. When G2 is copied
to G3, G3 = C3/A$2 (not C3/A3) because the row has been fixed already.
Here, whenever we copy G2 to any other cell, always the divisor will refer to a fixed row 2
(column vary according to the concept of relative reference)

So, when G2 is copied to I3, I3 = E3/C$2 because E3 comes from the relative reference (4
columns left to the current one) & C$2 comes from the absolute reference for row & relative
reference for Column (6 Columns left to the current one)

Other Useful Ways to Use Cell References and Addresses

Now that we are familiar with the basics of using Cell References in Excel, let's see some
other ways of using cell references.

Relative and Absolute References for Dates

We can use relative and absolute cell references to calculate dates.
• Relative Reference Example: =Order Date + Days to Deliver
• Absolute Reference Example: =Today() - Age in Days

Example: To Calculate the Date of Delivery online from the given date of the order placed &
no of days it will take to deliver :

Here, we calculate the Date of Delivery by = Order Date + No of days to deliver. We used
Relative cell reference so that individual product delivery dates can be calculated.

Absolute cell references for calculating dates:

Example:
To Calculate the Date of Birth When the age is known is a number of days using Current date
can be done by making use of absolute reference.

Here, We calculate DOB by = Current Date - Age in days. The Current date is contained in the
cell E2 & in subtraction, we fixed that date to subtract from the days.

Whole Column Reference

To refer to all cells in a specific column in Excel, use the column letter twice with a colon in
between, like B:B for column B. For example, to find the sum of all values in column B,
type =SUM(B:B). Unlike a range like B1:B10, this approach automatically includes any new
data added to the column, saving you the need to update the range manually.

Whole Row Reference

You will want to refer to all the cells inside a particular row when operating with an Excel
worksheet with any number of columns. Simply type a row number twice with a colon in
between to refer to the entire row, for example, 2:2.
Example:
You may want to find the sum of a row of data in certain cases. While you can do this with a
regular cell range, such as =SUM(A2 : J2), you will need to change the cell range if your
spreadsheet grows in size.
Excel, on the other hand, has a cell range that does not require the column letter and takes
all the cells in the row in action. If you wanted to find the sum of all the values in row 2, for
example, you would type =SUM (2:2). You can add as much data as you want to your
spreadsheet without having to change your cell ranges if you use this type of cell range.
Refer to an Entire Column, Excluding the First Few Rows
To refer to the entire column excluding the first few rows, you need to specify the range as we
give in a normal fashion. We know that the Excel worksheets can have only 1,048,576 rows. (To
check this, go to an empty cell & press: Ctrl + Down arrow Key)
So, we can do the sum of the entire column B except for the first 5 rows by =
SUM(B6:B1048576).

Using a Mixed Entire Column Reference in Excel

You can also create a mixed entire-column reference, say for example $B:B. But, practically, it
is difficult to find a situation where it would be used.
Example:

How to Switch between Different Cell References

The $ sign can be manually typed in an Excel formula to adjust a relative cell relation to absolute or
mixed. You can also speed things up by pressing the F4 key. You must be in formula edit mode to use
the F4 shortcut. The steps are : Firstly, choose the cell that contains the formula. Then, by pressing
the F2 key or double-clicking the cell, you can enter Edit mode. Select the cell reference in which you
want to make changes. Then, switch between four-cell reference forms by pressing F4.
Example:
When you select a cell having only relative reference (i.e., no $ sign), say = B2:
• The first time when you press F4, it becomes =$B$2
• The second time when you press F4, it becomes =B$2
• The third time when you press F4, it becomes=$B2
• The fourth time when you press F4, it becomes back to the relative reference=B2

B2 --Press F4--> =$B$2 --Press F4--> =B$2 --Press F4--> = =$B2 --Press F4--> =B2
So, using F4, you do not require to manually type the $ symbol.

Cell References Across Worksheets

Cell reference in Excel are not limited to just the current worksheet. Excel allows you to
reference cells from other worksheets in the same workbook. Follow the below steps to
learn Cell References Across Worksheets:
• Type = in a cell on the current worksheet.
• Switch to another sheet and click the desired cell.
• Press Enter to complete the reference.
Example: =Sheet2!A1 references cell A1 on "Sheet2".
Key Points to Remember
• Relative references adjust automatically when formulas are copied.
• Absolute references keep the referenced cell constant.
• Mixed references lock either the row or column.
• Use the F4 key to quickly toggle between reference types.

Conclusion
Mastering the use of relative, absolute, and mixed cell references unlocks the true potential
of Excel for managing complex data. These references provide the foundation for creating
adaptable and accurate formulas across single or multiple worksheets. By applying the
examples and tips shared here, you’ll streamline your workflows and improve efficiency.
Take the time to experiment with these techniques, and watch your Excel skills reach a new
level.

2.0 Separate Text and Numbers from One Cell Into Two Columns
Excel is a tool for organizing, storing, and analyzing large datasets. Data is entered into cells,
which are the intersections of rows and columns. Excel supports a wide range of tasks, such
as creating graphs, analyzing trends, and gaining insights using functions and formulas.
Separate Text and Numbers from One Cell Into Two Columns
Suppose we have value in a cell that consists of text as well as numbers as shown in the
table,
We want to separate the numbers and text from one cell to store it in a cell of different
columns, as given below:

We can perform the above task using a Combination of RIGHT(), LEFT(), MIN() and FIND()
functions. For the purpose of demonstration, we will use the following string "Romy kumari
1998". As an example, We will separate the text and numbers. Follow the below steps,

Step 1: Find the position from where the numbers start in Combined Data.
To find the starting position of number data, we will use FIND() and MIN() functions.
1.1 FIND() function
It takes three inputs as parameters:
• find_text: the value we want to find.
• within_text: string value from which we need to find the value.
• start_num: starting position from within_text from where value is to be found.
It returns the position of find_text where it matches with the value inside with_in text.
Syntax:
=FIND(find_text, within_text, [start_num])

1.2 MIN() function

It takes an array of numbers as input and returns the minimum value from the array.
Syntax:
=MIN(number1, number2, number3...)
To find the starting position of number we will use the following formula,
=MIN(FIND({0,1,2,3,4,5,6,7,8,9},B4&"0123456789"))
Example

Output

Step 2: Extract text using RIGHT() or LEFT() function

2.1 LEFT() function
It extracts a given number of characters from the left side of a supplied text string.
Syntax
=LEFT(text, position)

2.2 RIGHT() function

It extracts a given number of characters from the right side of a supplied text string.

Syntax
=RIGHT(text, position)
Example:
Using the LEFT() function will extract text values from the given string and using the LEFT
function we can extract numbers from it.

Output

Therefore, we can separate the text and numbers using a Combination of RIGHT(), LEFT(),
MIN(), and FIND() functions.

3.0 Scroll Lock in Excel

Scroll Lock is a keyboard feature that changes the behaviour of the arrow keys. Instead of
moving the active cell, the arrow keys scroll the entire worksheet when Scroll Lock is
enabled. While this feature can be helpful in specific scenarios, it can cause confusion if
enabled accidentally.
Key Impacts of Scroll Lock in Excel
• With Scroll Lock On: Arrow keys scroll the worksheet but do not change the active
cell.
• With Scroll Lock Off: Arrow keys move the selection from one cell to another.

Check if Scroll Lock is Enabled in Excel

You can say the Scroll Lock feature is enabledl (on) as the status will display on the lower left
of the Excel in the status bar.
Check the Status Bar
• In Excel, the status bar at the bottom displays "Scroll Lock" when it is enabled.

Follow the below steps to turn on/off the scroll lock in Excel:
• Make sure the below setting is in your Excel file, which gives a notification of the
scroll lock [turned on/off].
• Right-click “Status bar” and make sure “Scroll Lock” should be checked
Toggle “Scroll lock” on your keyboard, if it is not available use “on-screen keyboard”.
To open/close the “on-screen keyboard”
• Option 1: Press keys together Windows + Ctrl + O.
• Option 2: Select Start >> Windows Ease of Access >> On-Screen Keyboard.

Scroll Lock – OFF [Default]

• Arrow keys navigate the active cells in the respective direction
Scroll Lock – ON
• Arrow keys scroll the entire worksheet in the respective direction

Turn Off Scroll Lock in Excel For Windows

If your arrow keys suddenly stop moving from cell to cell, Scroll Lock is likely on. To turn
Scroll Lock off, you can press the Scroll Lock (ScrLk) key on your physical keyboard. For most
keyboards, there is an indicator light that shows whether Scroll Lock is on or off. If your
keyboard does not have this key, you can use the On-Screen Keyboard to toggle it off. Here's
how:
Disable Scroll Lock Using a Physical Keyboard
Locate the Scroll Lock Key:
• Find the Scroll Lock (ScrLk) key on your keyboard.
• It may also be labeled differently, such as ScrLk or ScLk.

Press the Scroll Lock Key:

• Press the key to toggle Scroll Lock off.
• If your keyboard has an indicator light, it will turn off when Scroll Lock is disabled.

Turn Off Scroll Lock Using the On-Screen Keyboard

If your physical keyboard does not have a Scroll Lock key, use the On-Screen Keyboard.
Open the On-Screen Keyboard:
• Press Windows + R to open the Run dialog.
• Type OSK (without quotes) and press Enter.
• The On-Screen Keyboard will appear.
Locate the ScrLk Key:
 • Look for the ScrLk key on the On-Screen Keyboard (usually in blue).

Click on the ScrLK to turn it off

Disable Scroll Lock:
• Click the ScrLk key.
• It will turn black, indicating that Scroll Lock has been disabled.
Close the On-Screen Keyboard:
• Click the X in the top-right corner of the On-Screen Keyboard.

ScrLK turned off

Turn Off Scroll Lock on Dell and HP Laptops
Follow the below steps to Turn Off Scroll Lock in Laptops:
• Dell Laptops: Use the shortcut Fn + S to toggle Scroll Lock on or off.
• HP Laptops: Use the shortcut Fn + C to toggle Scroll Lock on or off.

Turn Off Scroll Lock on Different Windows Versions

Follow these steps to disable Scroll Lock on various Windows versions:
For Windows 10
• Click Start > Settings > Ease of Access > Keyboard.
• Enable the On-Screen Keyboard by toggling the slider.
• Use the ScrLk key on the On-Screen Keyboard to disable Scroll Lock.

For Windows 8.1

• Press Ctrl + C to open the Charms bar.
• Go to Change PC Settings > Ease of Access > Keyboard.
• Slide the On-Screen Keyboard button to turn it on.
• Disable Scroll Lock using the On-Screen Keyboard.

For Windows 7
• Click Start > All Programs > Accessories > Ease of Access.
• Select On-Screen Keyboard.
• Press the ScrLk key on the On-Screen Keyboard to disable Scroll Lock.
After turning on the On-Screen Keyboard, press the ScrLk key (Scroll Lock) to turn Scroll Lock
on or off. To close the keyboard, click the X in the top-right corner.
Scroll Lock in Excel for Mac
Unlike Excel for Windows, Excel for Mac doesn’t show the Scroll Lock status in the status bar.
Follow the below steps to check if Scroll Lock is on:
Remove Scroll Lock in Excel for Mac
To remove Scroll Lock in Excel for Mac, follow these steps:
Check if Scroll Lock is Enabled
If you notice that using the arrow keys moves the entire worksheet rather than navigating
between cells, Scroll Lock is likely enabled.
Use the On-Screen Keyboard
• Excel for Mac does not have a dedicated Scroll Lock key on most keyboards, but you
can use the On-Screen Keyboard to toggle Scroll Lock.
• Go to Apple Menu > System Preferences > Keyboard, and enable Show Keyboard
and Emoji viewers in menu bar.
• In the menu bar, click the Keyboard Viewer icon and select Show Keyboard Viewer.

Toggle Scroll Lock Using the On-Screen Keyboard

The On-Screen Keyboard will appear on your screen. Look for the F14 key (usually located as
a secondary function key).
Hold the Fn key and click F14 on the On-Screen Keyboard to toggle Scroll Lock off.
Use a Shortcut (Alternative Method)
Some Mac keyboards may not have an F14 key. In that case, try using Fn + Shift + F12 as an
alternative to toggle Scroll Lock.
After performing these steps, you should be able to navigate cells using the arrow keys
without moving the entire worksheet, indicating that Scroll Lock has been successfully
disabled.
The Functions of the Arrow Keys (With and Without the Scroll Lock)
Below are the Functions of Arrow Keys When Scroll Lock is turned on
• It allows the movement of rows (one row up and down) and columns (one column
left and right) with the help of the respective arrow keys.
• Moves quickly to the different areas of the worksheet by pressing the "Ctrl" and the
respective arrow key simultaneously.
Below are Functions of Arrow keys When Scroll Lock is turned off
• Move up, down, left, or right from one cell to the other with the help of respective
Arrow keys.
• By pressing the ctrl and the respective arrow key together, you can move to the last
cell of a blank range.
• By pressing the shift and the respective arrow key together, you can select the range
of cells vertically or horizontally.
Note: When Scroll Lock is enabled in Excel, the arrow keys won't let you select individual
cells. In this situation, you can use the mouse to click and select a cell.
Troubleshooting Scroll Lock Issues
If you're unable to turn off Scroll Lock, try these solutions:
• Restart Excel: Close and reopen Excel to reset any temporary glitches.
• Check External Keyboards: Ensure an external keyboard or device isn’t enabling Scroll
Lock.
• Update Drivers: Outdated keyboard drivers may cause issues. Update them through your
device manager.
• Test with Another Keyboard: If you suspect hardware issues, test Scroll Lock
functionality with another keyboard.
4.0 Add or Subtract Dates in Excel
New to Excel and seeking a way to add or subtract dates, then this guide on how to add or
subtract dates in Excel will explain to you the right way to manage project timelines,
schedules, or due dates. We’ll break down simple steps to add or subtract dates using easy
formulas and time-saving tips.
Subtract Dates in Excel
If you have two dates in cells, for example in B2 and C2, and you want to know how many
days there are between them, Excel has ways to help you find the days between two dates.
Subtract Date from Other Date Directly
In Excel, each date is actually stored as a special number. The number 1 represents March
31, 2000. So, to find the difference between two dates, you can just subtract one number
from the other:
'=C2-B2'
Using the DATEDIF Function
If you prefer a more advanced method, you can use the DATEDIF Function:
This function calculates the difference in days between the two dates. Just keep in mind that
it won't work if start date B2 is later than the end date (C2).
'=DATEDIF(B2, C2 "d")'

DATEDIF Function
Subtracting from Today's Date
To find the difference between a dates in the past and today, you can use similar formulas.
Replace one of the dates with the 'TODAY()' function.
'=TODAY()-B2' Or '=DATEDIF(A2, TODAY(),"d") '
Note: This function does not work if the date you're subtracting from is later than today.
Example: Using the DATE Function
If you want to input the dates directly into the formula, you can use the 'DATE(year,
month,day)' function. This formula subtracts 15 August 2000 from 20th May 2023.
=DATE(2000, 8, 20) - DATE(2023, 5, 15)

Date Function
In general, if you're just looking to find the difference between two dates, the direct
subtraction method is the simplest and most straightforward.
For complex calculations, like finding the difference in months or years, the DATEDIF
function can be handy.
Subtract or Add Days to Date in Excel
Whether you're working with a single date or a list of dates, Excel makes it easy to add or
subtract a specific number of day using basic arithmetic operations.

Adding Days to a Date in Excel

To add a certain number of days to a date, follow this general formula:
DATE+ N days
Below are some ways to input the date:
• Use a cell reference, e.g., '=A2 + 10'
• Utilize the 'DATE(year, month, day)' function e.g., '=DATE(2015, 5, 6) +10 '
• Leverage a function result. For example, to add days to the current date, use the
'TODAY()' Function: ='TODAY() +10'

Adding days to date

Subtracting Days form a Date in Excel

Subtracting days form a date is just as straightforward. It's essentially the same process as
addition, but with the minus sign:
Date - N days
Here are examples of how to do this:
• '=A2 -10'
• '=DATE(2015, 5, 6) - 10'
• '=TODAY() -10'
 Subtract Days from date

Subtract Weeks to Date

When you need to adjust a date by specific number of weeks, Excel offers a simple solution.
You can build upon the formulas used for adding or subtracting days, with a small
modification: Just multiply the number of weeks by 7. This straightforward approach allows
you to seamlessly workwith weeks in your date calculations.
=Cell - N week*7
Subtarct Months to Date in Excel
If you want to add or subtract a certain number of whole months to a date, you can use the
DATE or EDATE function:
Example : Subtract months to a date with Excel EDATE
Excel provides a specialized function called EDATE that simplifies the process of working with
dates when adding or subtracting months. This function is available in all versions of Excel
form 2007:
=EDATE( start_date, months)
Arguments:
Start_date - the start date from which to count the numbers of months.
Months- the number of months to add( a positive value) or subtract (a negative value).
The following formula used on our column of dates yields exactly the same result as the
DATE function in the previous example:

Using Edate()
Subtract Years to date in Excel
Expanding on the concept of adding months, adjusting dates by years in Excel employs a
similar approach. The DATE(year, month, day)function remains your ally, with the added step
of specifying the number of years to add or subtract:
'DATE(YEAR(date) -N years, MONTH(date), DAY(date))'
Example: Subtracting Years from a Date in Excel:
'=DATE(YEAR(A2) - 5, MONTH(A2), DAY(A2))'
This formula subtracts 5 years from the date in cell A2.
Alternatively, you can establish a universal formula by entering the number of years to
add(positive number) or subtract (negative number) in a specified cell. Then, reference
within the DATE function.

DATE() Function

How to subtract dates to find the difference in months or years?

You can use DATEDIF function to find the difference in months or years between two dates.
The syntax is: '=DATEDIF(start_date,end_date,"m") for months and '=DATEDIF(start_date,
end_date,"y")' for years.

How to create a formula to subtract or add a dynamic number of days, months, or years to
a date?
You can use cell reference to store the number of days, months, or years you want to add or
subtract, and then incorporate these references into your formulas. This allow for dynamic
adjustments based on changing values in those cells.
How to format the result of date subtraction to display as a specific date format?
After subtracting dates, the result may appear as a serial number. To format it as a date,
select the cell(s), right click, choose "Format cells", and select the desired data format under
the Number tab.