Office Applications: How Tos

Introduction

Here are some selected office application how-tos for myself. See also: Office Application Settings, Programs.

Parts of a spreadsheet document

cell = an intersection of a column and a row, for example cell A1

column = vertical group of cells

row = horizontal group of cells

worksheet (sheet, tab) = like a physical sheeting, contains one or more columns and rows

spreadsheet (or workbook) = a file containing one or more worksheets

Absolute reference

$A1 = column A locked, row can change

$B$2 = column B locked, row 2 locked

Fill Handle

Make cell A1 = 1, make cell A2 = 2, and then drag down the black fill handle

Refer to cells in other cells

A1;A13;D7 (refer to certain cells, one at a time, separated by semicolon)

B1:B10 (refer to an area (group) of cells, separate top left and bottom right cell with a colon)

Information type

use ' (apostrophe) as the first character in a cell to define cell's contents as text

Formula

use = (equal sign) as the first character in a cell to define the contents of the cell as a formula

Functions

SUM(A1:A3) = sum of cells A1, A2, and A3

IF(condition; value1 if condition true; value2 if condition false)

AND(argument1; argument2; ...; argument30)

Nested (embedded) functions

IF(AND(A1>10;B2>100;C3>1000);"SUCCESS";"FAIL") = if 3 cells contain high enough values, it's a success

Conditional Formatting

LibreOffice Calc: Format: Conditional Formatting: Condition

[Cell value is] [less than] [100]

[Apply Style] [Red text]

Sort data

LibreOffice Calc: select area of cells, Data: Sort

Automatic filtering ("sort"/"hide")

LibreOffice Calc: select header row (e.g. cells A1..A10), Data: Filter: Autofilter

Chart (graph): create

LibreOffice Calc: select a group of cells, Insert: Object: Chart

Chart: edit color, font, etc.

LibreOffice Calc: click once on the chart to select it, then double-click part of the chart, then click the section you want to modify

Last modified: November 2nd, 2013

Author: Tomi Häsä (tomi.hasa@gmail.com)

URL: http://sites.google.com/site/tomihasa/office-applications-how-tos