Tutorials For OpenOffice: Columns
Image by: Sally Peterson

Columns

This tutorial explains how to use columns in spreadsheets.

A check register with columns (check number, payee, item description, withdrawal, deposit and balance) is created to help explain columns.

Additional information about columns is added at the end of this tutorial.

Open A Spreadsheet

  1. IF on the desktop, click > All Programs > OpenOffice.org 2.2 > OpenOffice.org Calc.

  2. IF you are in OpenOffice.org Writer, click File > New > Spreadsheet.

  3. In either case, the spreadsheet called appears on our screen.

Add A Label To Columns

  1. Make A1 the active cell. (Click in cell A1)

  2. Type Check No then press the Tab key (The cursor moves to B1)

  3. Type Paid To then press Tab. (The cursor moves to C1.)

  4. Type Description then press Tab.

  5. Type Deposit then press Tab.

  6. Type Withdrawal then press Tab.

  7. Type Balance then press Enter.

Insert An Additional Column

Insert a Date column in the first column.

  1. Select column A. (Click on the "A" at the top of the column. The column turns black.)

  2. Click Insert > Columns. (The cell contents in columns "A" through "F" shift to the right and becomes columns "B" through "G". Column "A" cells are empty.)

  3. Select A1.

  4. Type Date then press Enter.

Change The Appearance of Column Labels

Center The Column Labels

Use An Icon From The Formatting Toolbar

  1. Select cells A1 through G1 by dragging the mouse from cell A1 to cell G1 (Click on A1 and without releasing the click, move the cursor over B1, C1 etc until the cursor is in cell G1. Release the click)

  1. On the Formatting toolbar, click the Align Center Horizontally icon. (The column labels become centered)

Select “Bold” And “Light Blue” For The Column Labels

  1. While the cells are still selected, move to the left and click the Bold, , icon.

  2. Move to the right to the Font Color icon and pick Light Blue. (At the "Font Color", icon, click and hold the click until a choice of colors appears. Select [click on] the "blue" square). Press Enter.

Make Entries In the Check Register

Enter An Initial Balance:

  1. Select A2.

  2. Enter a date, 07/12/07. (type 07/12/07)

  3. Select G2.

  4. Enter 5000.

Format Columns For Currency

The Deposit, Withdrawal and Balance columns will contain dollars with a $ appearing in front of the numbers. All three columns can be formated at the same time.

  1. Use Drag to select columns “E” through “G”. Click on E. (The cursor is at the top of column “E”) Press and hold down the left mouse button. Move the mouse pointer to G by moving the mouse. Release the mouse button. (Columns “E”, “F”, and “G” are highlighted)

Continue by using either of the following two methods:

Use A Icon From The Formatting Toolbar

  1. Click the Number Format Currency, , icon. (The three columns will show the $ when they have numbers in them.)

Use Format From The Main Menu

This method is more complex than using the icon.

  1. Select columns E through G.

  1. Click Format > Cells... (The "Format Cells" dialog box appears.)

  2. Click the Numbers tab.

  1. In the Category box, click Currency.

  2. In the Options section, make sure that Decimal places is set to 2, Leading zeros is set to 1, and both check boxes are checked.

  3. Click OK.

Enter A Check

  1. In the Date column, click on A3, then enter 07/18/07. Press Tab.

  2. Enter a check number of 104 then press Tab.

  3. Enter Energetic Electric then press Tab.

  4. Enter Monthly electricity bill then press Tab.

  5. Press Tab again.

  6. In the Withdrawal column, enter 250, then press Enter.

Adjust Column Width

In the Paid To column Energetic Electric is cut off. In the Description column Monthly electric bill extends into the Deposit column.

Use either of the following two methods to adjust column width.

Use "Optimal Width"

  1. Select column C. (Click on "C".)
  2. Click Format > Column > Optimal Width... (The “Optimal Column Width” window appears)
  3. Click OK.

  4. Repeat steps 1-3 for column D.

    Resize Cells Manually

  5. Place your pointer over the column dividing line between the letters C and D.

  6. When your pointer changes to , click the left mouse button and drag.

  1. Size the column like you want it and release the left mouse button.

Enter Current Balance

The current balance is defined as the previous balance plus any deposits, minus any withdrawals. In equation form it looks like:

Current Balance = Previous Balance + Deposits – Withdrawals

In the spreadsheet, the formula is written as =G2+E3-F3.

  1. Click on cell G3.

  2. On your keyboard, press the = key.

  3. Click on cell G2, then press the + key.

  4. Click on cell E3, then press the key.

  5. Click on cell F3, then press Enter. ($4,750 appears in G3)

Enter A Check Bigger Than Your Balance

Enter a check on row 4 for an amount bigger than your balance.

  1. Click on A4 (The Date column) Enter the date 07/20/07. Press Tab.

  2. Enter a check number of 206 then press Tab.

  3. Enter Fast Cars, Inc. (Do not press Tab)

  4. Click on D4 that is in the Description column, enter New Car.

  5. Click on F4 that is in the Withdrawal column, enter 7000.

Copy A Formula

Drag-to-Copy is a quick way to copy the contents of one cell to a neighboring cell or into a series of neighboring cells.

  1. Select G3.

  2. Look at the black frame around the cell and notice the little black box on the bottom right corner.

  3. Put the cursor over the little box. When you see a , click and drag it down to cell G4. ("-$2,250.00" appears in cell G4)

When you copy formulas, the cells referenced in the formula will change. Click on cell G3 and look at the Input Line that is just above the columns. You will see the formula =G2+E3-F3. Click G4 and you'll see the formula =G3+E4-F4.

All spreadsheet programs use relative addressing. The program does not store the actual cell address; rather, it stores something like the following in G3:

Cell G3 equals (=) one cell up (G2) plus (+) two cells to the left (E3) minus (–) one cell to the left (F3)

The program stores something like the following in G4:

Cell G4 equals (=) one cell up (G3) plus (+) two cells to the left (E4) minus (–) one cell to the left (F4)

Enter An Additional Row

You forgot to enter an earlier deposit. Enter that deposit now.

  1. Click on the 4 that is to the left of 07/20/07. Keep the cursor on the 4, then do a Right-click, then click Insert Rows. (Row 4 cell contents shift to Row 5; Row 4 has empty cells.)

  2. Click A4, enter 07/19/07.

  3. Click D4 enter Paycheck.

  4. Click E4, enter 20,000. (Wouldn't that be great!)

  5. Drag-to-Copy the formula from G3 down to G5. (Put the cursor over the box at the bottom right corner of "G3". When you see , click on it and drag it down to cell "G5".) ($17,750.00 appears in cell G5)

Rename Sheet1 And Save

  1. Click Format > Sheet... > Rename... (The “Rename Sheet” window appears)

  2. In the “Name” box, type Checking then click OK. (At the botton of the screen “Checking” appears in place of “Sheet1”

  3. Click File > Save As... In the “Save in:” pull-down menu, select My Documents

  4. In the “File name:” box type Check Register and click Save.

Borders And Shading

Borders can be used to separate data, mark certain cells or anything else you want. They are typically used to draw attention or separate. Add some borders to the check register worksheet:

Select A Block Of Cells

Use either of the following two methods;

Use Drag To Select A Block Of Cells

  1. In A1, click and hold the click, then move the cursor onto G25.

Use The Shift Key To Select A Block Of Cells

  1. Click A1. Press and hold down the Shift key then click G25.

    Add A Border

  2. Click Format > Cells...

  3. Click on the Borders tab. Find the User-defined box. Notice that there are four boxes created by inward facing triangles.

  4. Click to the left of the top left box . (Toward the middle of the box side.) (1)

  5. Click between the top two boxes. (Toward the middle of the box sides.) (2)

  6. Click to the right of the top right box. (3)


  1. You should have 3 vertical lines. Click OK.

  1. Select A1:G1. (Click "A1". Drag copy to "G1".)

  2. Click the Borders, , icon on the Formatting Toolbar.

  3. Click on the second box from the left on the second row.

Add A Background Color

  1. Click Format > Cells...
  2. Click the Background tab.
  3. Click on Gray 20%. (Use tool tips to find it.)

  4. Click the Borders tab.

  5. Click on the bottom horizontal line in the User-defined box.

  1. Click the 2.5 pt line weight in the Style box.

  2. Click OK.

Additional Information

Other Ways Of Formating Columns And Text

You can also change formatting by selecting a cell(s) or column(s) then clicking Format > Cells and doing the following

  1. IF you click on the Alignment tab, you can change centering and orientation.

  2. IF you click on the Font tab, you can change the font.

  3. IF you click on the Font Effects tab, you can change color, etc.

Hide Or Show A Column

A column(s) can be hidden so that the column(s) is not seen on the screen. Hidden column(s) will not appear in a printout. If any cell in the hidden column is used by a formula, the formula will still use hidden columns to produce the correct answer.

At the top of the columns, a missing letter(s) tells what column(s) is hidden. (If you see columns A followed by column C, column B is hidden.)

Hide A Column

  1. Select the column(s) that you want to hide.
  2. Click Format > Column > Hide.

    Show A Hidden Column

  3. Select the columns on both side of the hidden column (If column “B” is hidden, select columns A and C so that both columns are highlighted)

  4. Right-click > Show. (Column “B” will appear)

NOTE

Tutorials are improved by input from users. We solicit your constructive criticism.

Click here to E-mail your suggestions and comments

Edited by Sue Barron

Columns       09/08/07

 


Last modified: 2008.04.25 20:33 UTC
Creative Commons License
This work is licensed under a Creative Commons Attribution2.5 License.