Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How To Create Frequency Distribution in Excel
  • How To Insert and Customize Sparklines in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Conditional Formatting Data bars Examples in Excel
  • Working With Tables in Excel

References

  • How to use Excel MMULT Function
  • Multi-criteria lookup and transpose in Excel
  • Left Lookup in Excel
  • Extract data with helper column in Excel
  • Two-column Lookup in Excel

Data Validations

  • Excel Data validation require unique number
  • Excel Data validation with conditional list
  • Excel Data validation number multiple 100
  • Excel Data validation specific characters only
  • Excel Data validation date in next 30 days

Excel Autofill Cell Ranges, Copy, Paste

by

Understanding Cell Ranges, Auto fills, Copy/Paste, Insert Row, Column in Excel

Auto fill options in excel is to cell based on another or fill column with same value without dragging

CELL Ranges

People find it difficult to understand Cell Ranges in Excel. To effectively work with formulas it is apparent to have a good knowledge of selections (cell ranges) in Excel.

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

Excel Office will take you through an overview of some very important range operations.

N/B: Microsoft refers excel as a workbook.

A workbook comprises of one or more worksheets and a worksheet is made up  rows and columns.  The intersection of a row and a column is called a cell, the cell holds the value.

Important! Excel does not recognise entered values but identifies them through their cell names which is indicated at the name box when a cell is selected.

A name box displays the name of the selected cell. e.g B4 as illustrated below:

Worked Example:   How to get first row number in range in Excel

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 or Fill data automatically in worksheet cells

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.

Worked Example:   Dynamic Named Range in Excel

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.

Note: visit our page about AutoFill for many more examples.

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).

Worked Example:   Check if multiple cells have same value in Excel

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).

Select the cell or range of cells. Select Copy or press Ctrl + C. Select Paste or press Ctrl + V.

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.

Insert Row, Column

https://youtu.be/dWHPQ6bOvh8

See more illustrations below:

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.

N/B: In a similar way, you can insert a column and also, delete cells, rows and columns.

Post navigation

Previous Post:

IFS function: Description, Usage, Syntax, Examples and Explanation

Next Post:

231 Keyboard Shortcut Keys In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • Nested IF function example in Excel
  • Extract multiple matches into separate rows in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples

Date Time

  • Count birthdays by month in Excel
  • Excel Date & Time Functions Example
  • How to calculate Quarter of Date in Excel
  • Extract date from a date and time in Excel
  • How to calculate Next working/business day in Excel

Grouping

  • Group arbitrary text values in Excel
  • Running count group by n size in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Group numbers at uneven intervals in Excel

General

  • How to increase by percentage in Excel
  • Subtotal invoices by age in Excel
  • How to calculate percent variance in Excel
  • Count cells less than in Excel
  • Zoom Worksheet in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning