Free Excel courseInformation technology

Lecture 6 – Cell references and named ranges in Excel

Cell references - Absolute and Relative - Defined names.

Back to Excel course main page

Cell references and named ranges in Excel

Relative and absolute cell references are needed when you copy and paste formulas. If you have ever broke your formulas after a copy paste operation it’s exactly because of wrong cell referencing style. In the video tutorial you will learn more about absolute, relative and semi relative¬†references as well as named ranges that make formulas easier to read.

Exercises

The goal is to create a table that shows a computer screen size when the width and height is known. The standard way to calculate a screen size is to measure distance between the opposite corners in inches. One inch = 2.54 centimeters. You can measure your own screen for a reference.

A general formula (not Excel formula) to calculate that distance between opposite corners is squareroot(width^2+height^2)/2.54. If your height and width are in inches don’t make the division by 2.54.

Open a new worksheet. In the first column type the heights. I’ll use centimeters but inches are ok too. Heights in cells A2:A6 could be for example 15, 20, 25, 30 and 35. Similarly enter widths in the first row in cells B1:F1. You could choose 25, 35, 45, 55 and 65.

In the cell B2 compose a formula that calculates the first screen size and then paste the formula to whole B2:F6 range.

See the answer
Write the formula =SQRT($A2^2+B$1^2)/2.54 in cell B2. Then you get the height always from the first column and width always from the first row.

Lecture materials

Absolute and relative cell references and named ranges in Excel

Video tutorial

Leave a comment