Columns

Table of Contents

Purpose
Open A Spreadsheet
Label Columns Insert An Extra Column
Change The Appearance of Column Labels
- Center Column Labels
- Select “Bold” And “Light Blue” For The Column Labels
Make Entries In the Check Registe
Borders And Shading
Miscellaneous Information
- Hide A Column
- Show A Hidden Colum

Tutorial donated by Wayne Tschirhard

Purpose

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.

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

Open A Spreadsheet

IF on the desktop, click > All Programs > OpenOffice.org 2.1 > OpenOffice.org Calc.
IF you are in OpenOffice.org Writer, click File > New > Spreadsheet.
In either case, the spreadsheet called appears on our screen.

Label Columns

Make A1 the active cell.
Type Check No.
Press the Tab key and type Paid To. (active cell is B1.)
Press Tab and type Description. (active cell is C1.)
Press Tab and type Deposit. (active cell is D1.)
Press Tab and type Withdrawal. (active cell is E1.)
Press Tab and type Balance. (active cell is F1.)
Press Enter.

Insert An Extra Column

Something is missing. It would be nice to know when a transaction took place, so we need a date column. We could add it after the balance column, but that would be lazy. The date is usually the first item, so add a column:

Select column A. (Click on the "A" at the top of the column. The column turns black.)
Click Insert > Columns. (The cell contents in columns "A" through "F" shift to the right to columns "B" through "G". "A1" cells are empty.)
Select A1.
Type Date and press Enter.

Change The Appearance of Column Labels

Use column labels that clearly describe the data in the column below. Be creative and use color, different fonts, bold, etc., that make the column labels stand out.

Center Column Labels

We have all the columns we need for our check register, but it isn't very pretty. Let's make the column labels more attractive:

Select A1:G1. (Click on "A1" and without releasing the click, drag the cursor over the cells on "line 1" that have names. Release the click)
Go to the Formatting toolbar. (That's where you change the font.)
Hold the mouse cursor over the icons in the toolbar until you find the Align Center Horizontally icon, then click it.

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

While the cells are still selected, move to the left and click the Bold, , icon.
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 the "blue" square).

You can also format columns and text the long way:

Click Format > Cells.
Click on the Alignment tab to do change centering and orientation.
Click on the Font tab to change the font.
Click on the Font Effects tab to change color, etc.

Helpful Hint: If you are using data that has units like inches, feet, square miles, etc., enter the unit in the cell below the label.

Make Entries In the Check Register

Enter An Initial Balance:

Select A2.
Enter a date, 08/12/06. (Enter the date in the mm/dd/yy format.)
Select G2.
Enter 5000.

Format Columns For Currency

The Deposit, Withdrawal and Balance columns will all contain values that represent currency, so we will format them accordingly:

Select columns E through G. (Click on "E" and drag to "G".)
Click the Number Format Currency, , icon on the Formatting Toolbar or you can click Format > Cells... (The "Format Cells" dialog box appears.) and follow the instructions in Step 3 through Step 6.
Click the Numbers tab.

In the Category box, click Currency.
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.
Click OK.

The three columns will show the $ when they have numbers in them.

Enter Check

Click on A3 Date column, enter a date, 08/18/06. Press Tab.
Click on B3 Check No. column, enter a check number of 205 then press Tab.
Click on C3 Paid To column, enter Energetic Electric then press Tab.
Click on D3 Description column, enter Monthly electricity bill then press Tab.
Press Tab again.
Click on F3 Withdrawal column, enter 250.

Adjust Column Width

Look at the text you entered in the Paid To and Description columns. Energetic Electric is cut off and Monthly electric bill extends into the Deposit column. Let's fix it so we can read them:

Select column C. (Click on "C".)
Click Format > Column > Optimal Width...
Click OK.
Repeat steps 1-3 for column D.

You can also resize cells manually:

Place your pointer over the column dividing line between the letters C and D.
When your pointer changes to , click the left mouse button and drag.

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

To enter this into the spreadsheet:

Select G3.
Type =.
Click on G2.
Type +.
Click on E3.
Type - .
Click on F3.
Press Enter.

You have everything you need to use your check register! Enter another check on row 4. For this check, put a withdrawal amount bigger than your balance. (Ladies & Gentlemen - Don't try this at home!)

Click on A4 Date column, enter a date, 08/20/06. Press Tab.

Click on B4 Check No. column, enter a check number of 206 then press Tab.

Click on C4 Paid To column, enter Fast Cars, Inc.
Click on D4 Description column, enter New Car.
Click on F4 Withdrawal column, enter 7000.

Now we need to copy the formula in the G3 to the G4:

Select G3.
Look at the black frame around the cell and notice the little black box on the bottom right corner.
Put the cursor over the box. When you see a , click on it and drag it down to cell G4.

You now have a negative balance and the font turned red. Dragging the little black box (I call it drag-copy) is the shortcut for copying the contents of one cell to a neighboring cell. You can also use the Copy and Paste commands in the menu. The number turned red because the currency format was set up that way. Cool, huh?

When you copy formulas, the cells referenced in the formula will change. Activate cell G3 and look at the Input Line. You will see the formula for that cell. Click G4 and you'll see the cell addresses change. That's because spreadsheet programs use relative addressing. In other words, the program doesn't store the actual cell address; rather, it stores something like, the active cell = one cell up + two cells to the left – one cell to the left. You need to keep that in mind when you copy formulas.

Now, pretend we forgot to enter an earlier deposit. In that case, we would:

Select A4:G4. (Holding the "Shift Key" down, click "A4" then click "G4" or click Row 4.)
Click Insert > Cells... (Row 4 cell contents shift to Row 5; Row 4 has empty cells.)
Click A4 Date column, enter a date, 08/19/06.
Click D4 Description column or Tab over to D4, enter Paycheck and press Tab.
Click E4 Deposit column, enter 20,000 for an amount. (Wouldn't that be great!)
Drag-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".)

Note: If you tried to insert the cells on row 3 and drag-copied up, you will find that the technique doesn't work correctly. To remedy the problem, in the Input line, edit the formula in G3 (G1+E3-F3) and change it to G2+E3-F3 (replace G1 with G2), then drag-copy G3 to G5.

Whew! At least we're out of debt now! I encourage you to enter more checks and practice copying formulas to get more comfortable with the information presented so far.

Before we continue, change the name of Sheet1 and save your work:

Click Format > Sheet... > Rename...
Name it Checking.
Click File > Save As...>My Documents (or another folder of your choice).
Type a name like CalcIntro in the File name box 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 A1. Hold down the Shift key. Click on cell G25. (Another way to select a block of cells.)
Click Format > Cells...
Click on the Borders tab. Find the User-defined box. See the four gray boxes? Click to the left of the top left box. (Toward the middle of the box side.) Click between the top two boxes. (Toward the middle of the box sides.) Click to the right of the top right box.