Inserting Spreadsheet Elements
In the previous chapter, we took a look at some of the basic aspects of working with cells, rows, and columns. In this chapter, we'll continue to survey the commands that allow you to enhance your spreadsheets during the initial creation of a spreadsheet or during editing. As you create your spreadsheets, you will find that sometimes you will need to insert a spreadsheet element into the spreadsheet, such as a cell column or row. In larger spreadsheets, you may also have a need to add additional sheets to the spreadsheet. Let's take a look at some of the ways and means of inserting the different spreadsheet elements.
To Insert Cells
To position the insertion point for the new cells and determine the number of cells to be inserted, select a cell or cells in the spreadsheet.
You can insert single or multiple cells into a spreadsheet. The cells that will be displaced by the insertion can be shifted down or to the right. Right-click on the selected cell or cells. On the context menu that appears, select Insert Cells. The Insert Cells dialog box will appear (see Figure 4.1).
Figure 4.1. You can insert cells into your spreadsheet and shift the current cells down or to the right.
Select either the Shift cells down or Shift cells right option button to choose how to displace the current cells when you add the new cell or cells are added to the sheet (you also have the option of adding an entire row or column at the selected cell; we will discuss these options in the next two sections). Click OK. The new cell or cells will be inserted into the sheet.
You will find that inserting cells is particularly useful when you are entering a great deal of data into a spreadsheet and find that you have inadvertently entered a row or column of information that is off by one or more cells. You can move the data to its correct position by inserting a cell or cells and shifting the current data down or to the right. You can then enter the appropriate data into the new cell or cells.
To Insert Columns
It's very easy to insert columns “after the fact” as you edit and enhance your spreadsheets. You can add a single column or multiple columns to the current sheet.
Select a column or columns by clicking the column header or dragging to select multiple column headers, respectively. The number of columns you select will determine the number of new columns that are created.
To insert a column or columns, select Insert, then Columns. The new column or columns will be inserted at the left of the selected column or columns. Figure 4.2 shows a spreadsheet where two new columns have been inserted (columns C and D were selected before the new columns were inserted).
Figure 4.2. Columns can be easily inserted into a spreadsheet.
The great thing about inserting new columns is that they will not delete or overwrite data that is already in the spreadsheet. You should keep in mind that inserting columns can wreak havoc with formulas or functions that use absolute references (we discuss cell referencing in Chapter 5).
Note
You can also quickly insert a column or columns into a spreadsheet using the context menu. Just right-click on the selected column or columns and then click Insert Columns.
To Insert Rows
You can also insert new rows into your spreadsheet as needed. This is accomplished in much the same way as you add new columns.
Select the row or rows above which you wish to add the new row or rows. The number of rows you select will determine the number of rows that are added.
Select the Insert menu, and then select Rows. The new row or rows will be added to the spreadsheet. You can also add a row or rows using the context menu. However, when you right-click on the selected rows, you will find that there isn't a “Row” choice on the context menu. Just click Cells, and since the entire row (or more than one row) is selected, an entire row (or rows) of cells will be added to the spreadsheet.
To Insert Sheets
If you are working on a spreadsheet that uses multiple sheets, you can add additional sheets as needed. New sheets can consist of blank sheets, or existing spreadsheet sheets can be inserted into the current spreadsheet.
Select the sheet using the Sheets tab that will serve as the insertion point for the new sheet. The new sheet can be placed before or after the currently selected sheet.
Once you've selected the sheet, select the Insert menu, then select Sheet. The Insert Sheet dialog box will appear (see Figure 4.3).
Figure 4.3. You can add sheets to your spreadsheets.
Use the appropriate option button to insert the new sheet before or after the current sheet. The No. of sheets spin box is used to specify the number of new sheets you want to add.
By default, the sheets are given names that specify the sheet number that is being added to the spreadsheet (in the case of Figure 4.3, it's Sheet4). You can use the Name box to provide a name for the new sheet if you wish.
To insert a sheet from an existing spreadsheet, click the From file option button. You can then use the Browse button to open the Insert dialog box (see Figure 4.4).
Figure 4.4. The Insert dialog box allows you to browse for an existing sheet.
Use the Insert dialog box to locate the spreadsheet that contains the sheet or sheets you want to insert into the current spreadsheet. Once you locate and select the spreadsheet, click the Insert button.
All the sheets contained in the spreadsheet that you selected will be listed in the From file list box (see Figure 4.5). To insert a particular sheet, click the sheet name in the list. To insert more than one sheet from the spreadsheet, click to select that sheet as well (if you select a sheet by mistake, click the sheet a second time and it will be deselected).
Figure 4.5. Select the sheet or sheets you will insert from the existing Calc spreadsheet.
When you are ready to insert the sheet (a new sheet if the New sheet option was selected and an existing sheet in a file if the From file option was chosen), click OK. The sheet (or sheets) will be inserted into the spreadsheet either before or after the current sheet (depending on how you chose to insert the new sheet or sheets).