LibreOffice Calc, Microsoft Excel bury insert rows and columns feature

Spreadsheet applications are not used as much as word processors, but they are used for just as diverse purposes. Grocery shoppers can use them to create lists. Financial managers use them to create charts from data showing forecasts for organizations. There are many other users and uses.

Though people from diverse walks of life use spreadsheets differently, they all typically perform some of the same functions. These include adding and deleting columns and rows.

This article compares how these are done in LibreOffice Calc to how they are done in Microsoft Excel 2016 for Windows.

While these to spreadsheet applications are considered to be the most powerful and feature-rich applications. However, they are not as user-friendly as some other popular spreadsheets, when it comes to adding or deleting rows and columns.

First the article will explain how these are performed.

LibreOffice
The open-source application gives you several options for adding or deleting rows and columns.

  • Standard toolbar
  • Insert and Delete dialogs
  • Sheet menu

LibreOffice Calc: Inserting multiple rows and columns from OS-College on Vimeo.

Insert rows
Insert rows in Standard toolbar in LibreOffice CalcRows can be inserted above or below the row where you place a cursor.
The first step is to select a cell in the row where you want to insert a row above or below.
None of the icons or dialogs allow you to choose how many rows you want to insert.
Highlight the number of rows in the spreadsheet that you want to insert.
Use one of the below methods.

Standard toolbar
There is a rows icon in the Standard toolbar that has an option for Insert Rows Above and Insert Rows Below. The same number of rows that you selected will be inserted.

Sheet menu
Insert rows and columns in Sheet menu of LibreOffice CalcLike in the Standard toolbar, the Sheet menu has a Rows Above and Rows Below item. They are located in the Insert Rows sub-menu.

Highlight the sub-menu and select the desired one. The same number of rows that you selected will be inserted.

Insert dialog
Right-click menu in LibreOfficeCalcThis dialog is accessed through the right-click menu. After highlighting the same number of rows that you want to insert, right-click on the selection. Then click the Insert items.

The dialog launches. Put a mark in the Entire Row radio button. Click the OK button.

The rows will be inserted above the top row of the selection.

Insert Columns
Columns can be inserted to the left or to the right of the column where you place a cursor.

  1. The first step is to select a cell in the column where you want to insert a column to be to the left or right of.
    1. None of the icons or dialogs allow you to choose how many columns you want to insert.
    2. Highlight the number of columns in the spreadsheet that you want to insert.
  2. Use one of the below methods.

Standard toolbar
Insert columns in Standard toolbar in LibreOffice CalcThere is a columns icon in the Standard toolbar that has an option for Insert Columns Left and Insert Columns Right. The same number of columns that you selected will be inserted.

Sheet menu
Like in the Standard toolbar, the Sheet menu has a Columns Left and Columns Right item. They are located in the Insert Columns sub-menu.

Highlight the sub-menu and select the desired one. The same number of columns that you selected will be inserted.

Insert dialog
Insert dialog in LibreOffice CalcThis dialog is accessed through the right-click menu. After highlighting the same number of columns that you want to insert, right-click on the selection. Then click the Insert items.

The dialog launches. Put a mark in the Entire column radio button. Click the OK button.

The columns will be inserted to the left of the farthest left column of the selection.

Insert rows in Standard toolbar in LibreOffice CalcDeleting
Deleting rows and columns is done by using the same dialog, menus, and toolbar. The first step is to select the desired rows or columns that you want to delete.

Standard toolbar
The Row and Column icons have delete items in their drop-down menus: Delete Rows and Delete Columns.

Click the desired one and the selected rows or columns disappear.

Sheet menu
This menu has a Delete Rows and Delete Columns item. Click either one to delete the selection.

Delete dialog
Delete dialog in LibreOffice CalcLike the insert dialog, the Delete dialog is accessed through the right-click menu. Right-click on the selection of columns or rows. Select Delete from the menu. This launches the dialog.

Click either Delete entire rows or Delete entire columns. Then click the OK button. The selected rows or columns now disappear.

Microsoft Excel 2016
Excel’s delete and insert functions are similar to that of Calc’s functions. However, Calc has icons in the Standard toolbar that allows you to choose whether the inserted rows go above or below the selection. It lets you choose whether new columns will be placed to the left of or to the right of the selection.

Excel will always place new rows above the selection or to the left of the selection. These functions can be found in the Home ribbon and right-click menu.

Like Calc, the same number of columns or rows that are to be inserted needs to be highlighted.

Insert rows
Highlight the same number of rows that you want to insert. The new rows will be inserted above the top row in your selection.

Home ribbon
The ribbon has an Insert drop-down menu in the Cells section. There is an Insert Sheet Rows item in the drop-down list for the icon.

Click the arrow. Then click Insert Sheet Rows. The new row or rows appear above your selection.

Right-click menu
Right-click on the selection to reveal the menu. Then click the Insert item in the list. This launches the Insert dialog.

Place a mark in the Entire row radio button. Then click the OK button.

The new row or rows are placed above the selected rows.

Insert columns
This is similar to Inserting a row.

Home ribbon
Insert icon in Home ribbon of ExcelThe ribbon has an Insert drop-down menu in the Cells section. There is an Insert Sheet Columns item in the drop-down list for the icon.

Insert and Delete items in right-click menu of Microsoft ExcelClick the arrow. Then click Insert Sheet Columns. The new column or columns appear to the left of your selection.

Right-click menu
Right-click on the selection to reveal the menu. Then click the Insert item in the list. This launches the Insert dialog.

Place a mark in the Entire column radio button. Then click the OK button.

The new column or columns are placed to the left of the selected columns.

Deleting
Deleting columns and rows are also done through the Home ribbon and right-click menu. The first step is to select cells in all the columns or rows that you want to delete.

Home ribbon
Delete icon in Home ribbon of ExcelThe ribbon has a Delete icon with a drop-down menu. This is like the Insert menu in the ribbon.

Click the arrow associated with the menu. Then click either Delete Sheet Rows or Delete Sheet Columns, depending on what you have selected.

Right-click menu
Delete dialog in ExcelThe right-click menu has a Delete item that launches a Delete dialog. After selecting the columns or rows that you want to delete, right-click on the selection. Then click Delete.

In the Delete menu that launches, put a mark in Entire row or Entire column, depending on what you have selected. Click the OK button.

The selected rows or columns will now disappear.

Conclusion
Calc and Excel are powerful applications. However, they drop the ball when it comes to inserting rows and columns. Two other popular spreadsheets are Google Sheets and Apple Number. Like Calc and Excel, they have multiple ways of inserting them, and these ways give users the option to insert rows above or below and columns to the left of or to the right of.

Excel always inserts rows above a selection and columns to the left of one. Calc only gives you the option to choose where the new rows or columns are to be placed in the Insert Rows and Insert Columns sub-menus that are in the Sheet menu.

Both could learn a lesson from Apple’s and Google’s spreadsheet and not bury their insert rows and columns settings. Excel also needs to have a setting that allows the user to insert rows above or below the selection of cells or insert columns to the left of or to the right of the selection.

They also could use a dialog that allows you to set the number or rows and columns you want to insert.

Leave a Reply

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