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.
3a. Enter the date 6/13/2016 into cell B2 and the date 6/16/2016 into cell B3.
3b. Select cell B2 and cell B3, click on the lower right corner of this range and drag it down.
The sky is the limit!
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
Insert Comment | Edit Comment | Show/Hide Comment
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.
Note: to hide the comment, select cell B4 and click Show/Hide Comment again. To keep all comments visible all the time, click Show All
Comments.
Hide Columns or Rows
Hide | Unhide
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
Paste Special Transpose | Transpose Function
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
Extract | Join
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.
Formulas and Functions
Enter a Formula | Edit a Formula | Operator Precedence | Copy/Paste a Formula | Insert a Function
A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.
For example, cell A3 below contains a formula which adds the value of cell A2 to the value of cell A1.
For example, 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 a 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. Excel counts the number of cells that are higher 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.
Percent Change
The percent change formula is used very often in Excel. For example, to calculate the Monthly Change and Total Change.
1a. Select cell C3 and enter the formula shown below.
1b. Select cell C3. On the Home tab, in the Number group, apply a Percentage format.
1c. Select cell C3, click on the lower right corner of cell C3 and drag it down to cell C13.
1d. Check if everything went alright.
2a. In a similar way, we can calculate the Total Change. This time we fix the reference to cell B2. Select cell D3 and enter the formula
shown below.
2b. Select cell D3. On the Home tab, in the Number group, apply a Percentage format.
2c. Select cell D3, click on the lower right corner of cell D3 and drag it down to cell D13.
2d. Check if everything went alright.
Explanation: when we drag the formula down, the absolute reference ($B$2) stays the same, while the relative reference (B3) changes
to B4, B5, B6, etc. Maybe this is one step too far for you at this stage, but it shows you one of the many other powerful features Excel
has to offer.
Names in Formulas
Named Range | Named Constant | Name Manager
Create a named range or a named constant and use these names in your formulas. This way you can make your formulas easier to
understand.
Named Range
To create a named range, execute the following steps.
1. 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.
Note: if the tax rate changes, use the Name Manager to edit the name and Excel automatically updates all the formulas that use TaxRate.
Name Manager
To edit and delete defined names, execute the following steps.
1. On the Formulas tab, in the Defined Names group, click Name Manager.
2. For example, select TaxRate and click Edit.
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 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),
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 named range expands.
7. Click OK and Close.
8. Now, when you add a value to the range, Excel updates the sum automatically.
Paste Options
Paste | Values | Formulas | Formatting | Paste Special
This example illustrates the various paste options in Excel. Cell B5 below contains the SUM function which calculates the sum of the
range B2:B4. Furthermore, we changed the background color of this cell to yellow and added borders.
Paste
The Paste option pastes everything.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell F5, right click, and then click Paste under 'Paste Options:' (or press CTRL + v).
Result.
Values
The Values option pastes the result of the formula.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell D5, right click, and then click Values under 'Paste Options:'
Result.
Note: to quickly replace the formula in cell B5 with its own result, select cell B5, press F2 (to edit the formula) and press F9.
Formulas
The Formulas option only pastes the formula.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell F5, right click, and then click Formulas under 'Paste Options:'
Result.
Formatting
The Formatting option only pastes the formatting.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell D5, right click, and then click Formatting under 'Paste Options:'
Result.
Note: the Format Painter copy/pastes formatting even quicker.
Paste Special
The Paste Special dialog box offers many more paste options. To launch the Paste Special dialog box, execute the following steps.
1. Select cell B5, right click, and then click Copy (or press CTRL + c).
2. Next, select cell D5, right click, and then click Paste Special.
The Paste Special dialog box appears.
Note: here you can also find the paste options described above. You can also paste comments only, validation criteria only, use the
source theme, all except borders, column widths, formulas and number formats, values and number formats. You can also use the Paste
Special dialog box to perform quick operations, skip blanks and transpose data.
Status Bar
The quickest way to see the average, count, numerical count, minimum, maximum or sum of selected cells is by taking a look at the
status bar.
1. Select a range of cells.
2. Look at the status bar at the bottom of your window to see the sum of these cells.
3. Right click the status bar to add the average, count, numerical count, minimum or maximum.
Result:
Quick Operations
Use the 'Paste Special Operations' to quickly perform operations on a range of cells in Excel.
1. Select cell D3.
2. Right click, and then click Copy.
3. Select the range A1:B8.
4. Right click, and then click Paste Special.
5. Click Multiply.
Note: you can also Divide, Add or Subtract a value.
6. Click OK.
Note: all values are increased by 10 percent. Without this feature, you would have to create a temporary range (with formulas that multiply
the values in the range A1:B8 by 1.1) and then replace the range A1:B8 by copy and pasting the temporary range as values.