When we construct a worksheet, we might need to insert cells, columns, or rows to accommodate new data, but how to insert multiple rows/columns/cells in Excel?
It is required if we might want to insert a new column to perform calculations or insert a new row to list a new entry. A new row or column is inserted above or to the left of our selection. If the cells surrounding the new cells are formatted, Excel will assume that the user wants the new column or row similarly formatted. Excel will also automatically revise all formulas that may have been affected by the insertion.
Although the number of rows and columns in a worksheet is fixed, we can still insert and delete rows and columns if we need to make space for additional information. These operations do not change the number of rows or columns at all. When we try to insert a new row that moves down the other rows to accommodate the new row. If the last row is empty, then it is simply removed from the worksheet. Similarly, when we try to insert a new column that shifts the other columns to the right, and the last column is removed if it is empty.
Remember that if the last row or last column is not empty, we can’t insert a new row or a new column, respectively. In either case, attempting to add a row or column displays the dialog box shown below:
HOW TO INSERT MULTIPLE ROWS/COLUMNS/CELLS IN EXCEL?
In this tutorial, we have explained the three types of insertion in worksheet level, for example: How to insert multiple rows in Excel? How to insert columns in Excel? How to insert cells in Excel?
(01) HOW TO INSERT MULTIPLE ROWS IN EXCEL?
We can insert a row or multiple rows in excel either in 04 methods.
Before inserting an entire row or multiple rows, we should select a single row in any of the following 02 ways:
⇒ WAY 1: Click in any cell within the row we want to insert, then press Shift+Spacebar (continuous pressing the Shift key and then press Spacebar) which will select the entire row.
⇒ WAY 2: Alternatively, select a row by clicking the row number (mouse pointer changes to a thick right arrow).
If we want to insert the sequential blank rows above the selection, then press the Shift+Down arrow (⬇).
If we want to insert the non-contiguous rows above the selection, then continuously press the Ctrl Key and click the row number only by one.
➢ METHOD 1: EXCEL INSERT ROW SHORTCUT CTRL+ + (PLUS)
After selecting an entire row or multiple rows we want to insert, press Ctrl++ (plus) which will insert new row(s) above the active row(s).
■ Note: Continuous the Ctrl key and then press the + (plus) key.
• Example 1: Contiguous rows are inserted.
• Example 2: Non-contiguous rows are inserted.
■ Note: We had detail discussed on CTRL shortcuts in a separate tutorial, suggested you read this tutorial: 90+ Best Excel CTRL Shortcuts | Useful Keyboard Shortcuts |
➢ METHOD 2: EXCEL INSERT ROW SHORTCUT ALT+H+I+R
Alternatively, after selecting an entire row or multiple rows we want to insert, press Alt+H+I+R (sequentially press Alt, H, I, R) which will insert new row(s) above the active row(s).
• Example 1: Contiguous rows are inserted.
• Example 2: Non-contiguous rows are inserted.
■ Note: We had detail discussed on ALT shortcuts in a separate tutorial, suggested you read this tutorial: 80+ Excel Shortcuts with ALT Key || Best Hotkey of Keyboard Shortcuts
➢ METHOD 3: INSERT MULTIPLE ROWS IN EXCEL USING THE MOUSE RIGHT-CLICK
Equivalently, after selecting an entire row or multiple rows, right-click on the selection and choose Insert from the shortcut menu. As a result, new row(s) are inserted above the active row(s).
• Example 1: Contiguous rows are inserted.
• Example 2: Non-contiguous rows are inserted.
➢ METHOD 4: INSERT MULTIPLE ROWS IN EXCEL USING THE RIBBON
After selecting an entire row or multiple rows we want to delete ➪ go to the Home tab ➪ Click the ‘Insert’ down arrow in the Cells group ➪ Click ‘Insert Sheet Rows’ which will insert new row(s) above the active row(s).
• Example 1: Contiguous rows are inserted.
• Example 2: Non-contiguous rows are inserted.
(02) HOW TO INSERT A COLUMN IN EXCEL?
Similarly, We can insert a new column or columns in excel either in 04 methods.
But before inserting an entire column or multiple columns, we should select a single column in any of the following 02 ways:
⇒ WAY 1: Click in any cell within the column we want to insert, then press Ctrl+Spacebar (continuous pressing the Ctrl key and then press Spacebar) which will select the entire column.
⇒ WAY 2: Alternatively, select a column by clicking column letter (mouse pointer changes to a thick down arrow).
If we want to insert the sequential columns, then press the Shift+Right arrow (➡) or Shift+Left arrow (⬅).
If we want to insert the non-contiguous columns, then continuously press the Ctrl Key and click the column header only by one.
➢ METHOD 1: INSERT COLUMN IN EXCEL USING EXCEL SHORTCUT CTRL+ + (PLUS)
After selecting an entire column or multiple columns we want to insert, press Ctrl++ (plus) which will insert the entire new column(s) to the left of the current column(s).
Note: hold the Ctrl key and then press the + (plus) key.
➢ METHOD 2: INSERT COLUMN IN EXCEL USING EXCEL SHORTCUT ALT+H+I+C
Alternatively, after selecting an entire column or multiple columns we want to insert, press Alt+H+I+C (sequentially press Alt, H, I, C) which will insert the entire column(s) to the left of the current column(s).
➢ METHOD 3: INSERT COLUMN IN EXCEL USING THE MOUSE RIGHT-CLICK
After selecting an entire column or multiple columns we want to insert, right-click anywhere on the selected column(s), and then choose Insert from the shortcut menu. As a result, new column(s) are inserted to the left of the current column(s).
➢ METHOD 4: INSERT COLUMN IN EXCEL USING THE RIBBON
After selecting an entire column or multiple columns we want to insert ➪ Go to the Home tab ➪ Click the ‘Insert’ down arrow in the Cells group ➪ Select ‘Insert Sheet Columns’ which will insert the new column(s) to the left of the current column(s).
(03) HOW TO INSERT CELLS IN EXCEL?
We can also insert cells rather than just rows or columns, even we can insert a single cell in a particular row or column. When we insert blank cells, we can choose to move the other cells in the same column down (Shift cells down) or to move the other cells in the same row to the right (Shift cells right) to accommodate the new cells.
Notice that the Insert Dialog Box also has options for inserting entire rows and entire columns.
➢ METHOD 1: EXCEL INSERT CELLS SHORTCUT CTRL+ + (PLUS)
Select the cell or range of cells where we want the new cell ➪ Press Ctrl+ + (Plus) which will open the ‘Insert’ dialog box ➪ Choose any of the four options from the Insert dialog box to position the new cell ➪ Click OK or press Enter.
➢ METHOD 2: EXCEL INSERT CELLS SHORTCUT ALT+H+I+I
Select the cell or range of cells where we want the new cell ➪ Press Alt+H+I+I (sequentially press Alt, H, I, I) which will open the ‘Insert’ dialog box ➪ Choose any of the four options from the Insert dialog box to position the new cell ➪ Click OK or press Enter.
➢ METHOD 3: INSERT CELLS IN EXCEL USING THE MOUSE RIGHT-CLICK
Select the cell or range of cells ➪ Right-click on the selected range ➪ Click ‘Insert’ from the context menu which will open the ‘Insert’ dialog box ➪ Choose any of the four options from the Insert dialog box to position the new cell ➪ Click OK or press Enter.
➢ METHOD 4: INSERT CELLS IN EXCEL USING THE RIBBON
Select the cell or range of cells ➪ Home ➪ Click ‘Insert’ down arrow in the Cells group ➪ Further click ‘Insert Cells’ which will open the ‘Insert’ dialog box ➪ Choose any of the four options from the Insert dialog box to position the new cell ➪ Click OK or press Enter.
If you would like to improve your academic and professional career as well, then the below courses help you a lot. Instead of Advanced Excel, a number of best courses suggested you through this platform that boosts your confidence and flies your career high.
Advance Excel Forum
Premium Courses on ed2go
Microsoft Excel 2019/Office 365 Series
Microsoft Excel 2019 and Statistics Suite
Advanced Microsoft Excel 2019/Office 365 (Self-Paced Tutorial)
Microsoft Access 2019 Certification Training
Microsoft Office Specialist 2016 (MOS) Certification Training
Microsoft Project 2016
Accounting with MS Excel 2019 Suite
Microsoft Excel and Project 2019 Suite
Visual Basic Series
Bestseller
4.9/5
Premium Courses on Coursera
Excel Power Tools for Data Analysis
Excel Fundamentals for Data Analysis
Data Visualization in Excel
Data Analysis and Visualization Foundations
Business Analytics for Decision Making
Data Analysis and Visualization
Excel Skills for Business
Advanced Data Science with IBM
Data Science Fundamentals for Data Analysts
Bestseller
4.9/5
Premium Courses on Udemy
77-727 Microsoft Excel 2016 Core Certification (Microsoft Office Specialist – Become a Certified Microsoft Excel Specialist)
Excel VBA Macros: Hyper-disambiguated Excel VBA Programming [Learn Excel VBA macros, using clean, clear VBA programming techniques and code that you fully understand]
Microsoft Excel – Excel from Beginner to Advanced & VBA (Master Excel with this A-Z Microsoft Excel Course (Microsoft Excel 2013, Excel 2016, and Excel 2019)
Project Finance and Financial Modeling in Excel (Learn Project Finance concepts and learn the modeling in excel)
Master Microsoft Access – Access from Beginner to Advanced (Develop dynamic Access databases fast in this easy to learn Microsoft Access Course (2010, 2013, 2016, 2019 & 365)
Excel Crash Course: Master Excel for Financial Analysis (Beginner to Advanced: Learn Excel Shortcuts, Formulas & Functions for Financial Modeling & Corporate Finance).
The Complete Zero to Master Excel VBA and Macros (Learn how to snag the most in demand role in the tech field today!)
The Complete Financial Analyst Course 2021 (Excel, Accounting, Financial Statement Analysis, Business Analysis, Financial Math, PowerPoint: Everything is Included!)
Certified Six Sigma Green Belt (+ Excel Application) (Top-rated BKO/BKE accredited certifications for Lean Six Sigma White, Green and Black Belt. ASQ aligned. Practical.)
Top Rated
4.7/5
10 Alternate Methods: How to Copy Formatting in Excel?
06 Tips: How to Apply and Customize Cell Styles in Excel?
06 Alternative Methods: How to Copy Formula in Excel?
12 Examples || How to Use Excel Go To Special?
07 Points Guided You How to Find And Replace in Excel?
05 Points Should Learn How to Freeze Panes in Excel?
12 Things Guided You How to Manage An Excel Workbook
04 Simple to Advanced Methods: How to Filter in Excel?
05 Best Ways: Create Password Protect Excel & Unprotect it
08 Best Examples: How to Use Excel Conditional Formatting?
04 BEST WAYS: HOW TO TRANSPOSE DATA IN EXCEL
Tutorial 01: How to Use Excel SUMIFS Function with Single & Multiple Criteria