Introduction
This section is for users with no knowledge
of Excel.
Microsoft Excel is one
of the most used software applications of all time. Hundreds of millions of
people around the world use Microsoft Excel. You can use Excel to enter all
sorts of data and perform financial, mathematical or statistical calculations.
1 Range: A range in Excel is a collection of two or
more cells. This chapter gives an overview of some very important range
operations.
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.
Want to learn much more about Excel? You can find related examples and features on the right side of
each chapter. Check out all 14 related examples and features.
Range
Cell, Row, Column | Range Examples | Fill a Range | Move a Range | Copy/Paste a Range | Insert Row, Column
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. That's pretty cool huh!? Here's another
example.
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.
Fibonacci Sequence
It's easy to create all sorts of
sequences in Excel. For
example, the Fibonacci sequence.
1. The first two numbers in the Fibonacci
sequence are 0 and 1.
2. Each subsequent number
can be found by adding up the two previous numbers.
3. Click on the lower right corner of cell A3
and drag it down.
Result. The Fibonacci Sequence in Excel.
Custom Lists
If you create a custom
list in Excel,
you can easily fill a range with your own list of departments, clients, cities,
credit card numbers, etc. This can save time and reduce errors.
First, we will look at an example of a
built-in list.
1. Type Sun into cell B2.
2. Select cell B2, click on
the lower right corner of cell B2 and drag it across to cell H2.
How does Excel know this?
3. On the File tab, click Options.
4. Under Advanced, go to General and click
Edit Custom Lists.
Here you can find the built-in 'days of the
week' lists. Also notice the 'months of the year' lists.
5. To create your own custom list, type some
list entries, and click Add.
Note: you can also import a list from a
worksheet.
6. Click OK.
7. Type London into cell C2.
8. Select cell C2, click on
the lower right corner of cell C2 and drag it down to cell C5.
Comments
You can insert a comment in Excel to
give feedback about the content of a cell.
Insert Comment
To insert a comment, execute the following
steps.
1. Select a cell.
2. Right click, and then click Insert
Comment.
3. Type your comment.
Excel displays a red triangle in the
upper-right corner of the cell.
4. Click outside the comment box.
5. Hover over the cell to view the comment.
Excel automatically adds your user name. To
change this name, execute the following steps.
6. On the File tab, click Options.
7. Change the User name.
Edit Comment
To edit a comment, execute the following
steps.
1. Select the cell with the comment you want
to edit.
2. Right click, and then click Edit
Comment.
3. Edit the comment.
Note: To delete a comment, click Delete
Comment.
Show/Hide Comment
By default, a comment is only visible when
you hover over the cell that contains the comment. To keep a comment visible
all the time, execute the following steps.
1. For example, select cell B4 below.
2. On the Review tab, in the Comments group,
click Show/Hide Comment.
3. Select another cell.
Hide Columns or Rows
Sometimes it can be useful to hide columns or rows in Excel.
Hide
To hide a column, execute the following
steps.
1. Select a column.
2. Right click, and then
click Hide.
Result:
Note: to hide a row, select a row, right
click, and then click Hide.
Unhide
To unhide a column, execute the following steps.
1. Select the columns on either side of the
hidden column.
2. Right click, and then
click Unhide.
Result:
Note: to unhide a row,
select the rows on either side of the hidden row, right click, and then click
Unhide.
Skip Blanks
Use the 'Paste Special Skip Blanks' option and Excel will not overwrite existing values
with blanks.
1. Select the range B1:B12.
2. Right click, and then click Copy.
3. Select cell A1.
4. Right click, and then click Paste Special.
5. Check Skip Blanks.
6. Click OK.
Transpose
Use the 'Paste Special Transpose'
option to switch rows to columns or columns to rows in Excel. You can also use the TRANSPOSE function.
Paste
Special Transpose
To transpose data, execute the following
steps.
1. Select the range A1:C1.
2. Right click, and then click Copy.
3. Select cell E2.
4. Right click, and then click Paste Special.
5. Check Transpose.
6. Click OK.
Transpose Function
To insert the TRANSPOSE function, execute the
following steps.
1. First, select the new range of cells.
2. Type in =TRANSPOSE(
3. Select the range A1:C1 and close with a
parenthesis.
4. Finish by pressing CTRL +
SHIFT + ENTER.
Note: The formula bar
indicates that this is an array formula by enclosing it in curly braces {}. To
delete this array formula, select the range E2:E4 and press Delete.
Union and Intersect
This example illustrates how to use the union and intersect operator (borders below for
illustration only) in Excel.
1. The union operator (comma) adds two
ranges.
Explanation: the SUM
function reduces to =SUM(C4:D8) + SUM(D7:E11), 20.
2. The intersect operator
(single space) returns the intersection of two ranges.
Explanation: the SUM
function reduces to =SUM(D7:D8), 2.
Flash Fill
Use flash fill in Excel 2013 or later to automatically extract or combine
data.
Extract
1. For example, use flash fill to extract the
numbers in column A below.
2. First, tell Excel what you want to do by
entering the value 4645 into cell B1.
3. On the Data tab, in the
Data Tools group, click Flash Fill.
Result:
Note: Excel does not insert formulas, if you
change the text strings in column A, Excel will not update the numbers in
Column B.
Join
1. For example, use flash fill to join the
last names in column A and the first names in column B to create email
addresses.
2. First, tell Excel what you want to do by
entering a correct email address in cell C1.
3. On the Data tab, in the
Data Tools group, click Flash Fill.
Result:
Note: again, Excel does not
insert formulas, if you change the text strings in column A or column B, Excel
will not update the email addresses in column C.
