Format & Arrange:

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

If you decide to include additional types of data on your spreadsheet, you may need to insert  rows or columns to contain it. Blank rows and columns can be used to guarantee white space around your data.

A workbook can have a large number of worksheets. The default workbook only has 3 worksheets. You will need to know how to add new worksheets and how to rearrange their tabs.

Where you are:
JegsWorks > Lessons > Numbers

Before you start...

Project 1: Excel IntroTo subtopics

Project 2: Excel BasicsTo subtopics    

Project 3: Format & Arrange
    Format CellsTo subtopics
    Format ChartTo subtopics
    Arrange Open arrow to subtopics
    Exercises To subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics


Icon Step-by-Step

Step-by-Step: Insert

 Icon Step-by-Step

What you will learn:

to insert a column
to insert a row
to insert a cell
to insert a sheet
to reorder sheet tabs

Start with: Class disk trips17.xls - Sheet1 (saved in previous lesson)

You will now insert a column for the name of the travel agent who arranged each special offer trip. You will adjust the spacing in the sheet by adding a row, though it might be more efficient to resize the blank row that is already there.

A little experimentation will show you that adding a cell or two in the middle of things is not often a good idea.

You will add several sheets to the workbook, which you will use later to show information about each special trip separately.

Insert: Columns

You've decided that you want to include the name of the travel agent in charge of each trip. You need to add a column to the upper table.

  1. Click on the tab for Sheet1 to make it the active sheet.
  2. Select all of Column B.
  3.  From the menu select  Insert  |  Columns .
    The new column appears to the left of the selected column. Notice that the new column inherited some formatting in Row 4 and Row 25. The titles in the merged cells at the top have merged across the new column also.

    Message: That command cannot be used with selectors that contain entire rows or column and also other cellsIcon: TroubleExcel 97 Excel 97:  You get an error message that doesn't make a lot of sense. The problem is that you selected a column with a merged cell in it.

    1. Click on OK to close the message.
    2. Blank column insertedSelect the single cell B4, instead of the whole column, and then   Insert  |  Columns . Now it works - a new column appears to the left of the selection. Notice that it inherited some formatting in Row 4 and Row 25. The titles in the merged cells at the top have merged across the new column also.
  4. Travel Agent column enteredWith cell B4 selected, type  Travel Agent  and press ENTER. Your typing is formatted like all the other labels.
  5. Type in the names for the three travel agents responsible for the special offer trips, as shown in the illustration at the right.

    TipAfter you have entered a name the first time, if you start typing that name in another cell in the same column, Excel will offer to AutoComplete your typing. Excel displays its suggestion as highlighted in the cell, like the final entry in the illustration. Press ENTER or use the down arrow to accept the offer and to move the selection down a cell. This will save you a lot of typing!

Insert: Row

You decided that you need more space between the upper and lower tables on Sheet1. You could change the height of Row 24 but you decide to insert another row.

  1. Row 27 insertedSelect cell C27.
  2. From the menu select  Insert  |  Rows . A blank row is inserted above Row 27.

Insert: Cell

Inserting cells requires that something be moved to make room. You will again have the choices only of right or down for a direction, but not both at the same time.

  1. Dialog: Insert - Shift cells downSelect cell D25, the total number of people.
  2. From the menu select  Insert  |  Cells  The Insert dialog appears.

  3. Column shifted downSelect Shift cells down and click OK. All of the cells below D25 in the column are shifted down one cell, along with their formats.

    This effect could be useful if you find you have entered some data in the wrong rows or columns. However, it would be easy to scramble a table by inserting a cell or two without careful thought.

  4. Since this is not very useful at this point, Undo.
  5. Whole Row insertedTry again. Select cell D25 Insert  |  Row  
    The whole Row 25 and all cells below it are moved down one row.
  6. Since this doesn't seem to be too helpful, for this particular sheet, Undo again.
  7. Preview trips18.xlsClass disk Save as  trips18.xls  
    full floppy diskHow to handle a full disk


Insert: Sheet

You need to create some new sheets to which you can copy data in the next lesson.

  1. Menu: Insert | WorksheetFrom the menu select  Insert  | Worksheet . Sheet2 appears.

    A new sheet always appears to the left of the active sheet.


  2. Sheet tabs: sheets addedRepeat to insert 2 additional sheets, Sheet4 and Sheet5.  
  3. Rename Sheet1 (the one that you have been working with) as Specials and drag it to the far left of the sheets tabs.
  4. Sheet tabs - moving TahitiRename Sheet2 as Tahiti and drag it to the right of Specials.  The small black arrow shows where the sheet will be placed when you drop it.
  5. Sheet tabs:  renamed Specials,  Tahiti, New Zealand, World, OtherRename other sheets as New Zealand, World, and Other.
  6. Class disk Save [trips18.xls]
    full floppy diskHow to handle a full disk