Advanced spreadsheets – LibreOffice Calc, manual

Download (PDF, 1.84MB)

CONTENT:

Preface

1. FORMATTING

1.1 CELLS

Apply an autoformat/table style to a cell range

Apply conditional formatting based on cell content

1.2 WORKSHEET

Copy, move worksheets between spreadsheets.

Split a window. Move, remove split bars.

Hide, show rows, columns, worksheets.

2. FUNCTIONS AND FORMULAS

Use functions and formulas: TODAY, NOW, DAY, MONTH, YEAR

Use mathematical functions: ROUNDDOWN, ROUNDUP, SUMIF

Use statistical functions: COUNTIF, COUNTBLANK, RANK.

Use text functions: LEFT, RIGHT, MID, TRIM, CONCATENATE

Use financial functions: FV, PV, PMT

Use lookup functions: VLOOKUP, HLOOKUP.

Use database functions: DSUM, DMIN, DMAX, DCOUNT, DAVERAGE

Create a two-level nested function

Use a 3-D reference within a sum function

Use mixed references in formulas

3. CHARTS

3.1 CREATING CHARTS

Create a combined column and line chart.

Add a secondary axis to a chart.

Change the chart type for a defined data series

Add, delete a data series in a chart

3.2 FORMATTING CHARTS

Re-position chart title, legend, data labels

Change scale of value axis: minimum, maximum number to display, major interval

Change display units on value axis without changing data source: hundreds, thousands, millions

Format columns, bars, plot area, chart area to display an image

4. ANALYSIS

4.1 USING TABLES

Create, modify a pivot table/datapilot.

Modify the data source and refresh the pivot table/datapilot.

Filter, sort data in a pivot table/datapilot.

Automatically, manually group data in a pivot table/datapilot and rename groups.

Use one-input, two-input data tables/multiple operations tables.

4.2 SORTING AND FILTERING

Sort data by multiple columns at the same time

Create a customized list and perform a custom sort

Automatically filter a list in place

Apply advanced filter options to a list

Use automatic sub-totalling features.

4.3 SCENARIOS

Create named scenarios

Show, edit, delete scenarios

5. VALIDATING AND AUDITING

5.1 VALIDATING

Set, edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time

Enter the input message and the error warning

5.2 AUDITING

Trace precedent, dependent cells. Identify cells with missing dependents

Show all formulas in a worksheet, rather than the resulting values

Insert, edit, delete, show, hide comments/notes

6. ENHANCING PRODUCTIVITY

6.1 NAMING CELLS

Name cell ranges, delete names for cell ranges

Use named cell ranges in a function

6.2 PASTE SPECIAL

Use paste special options: add, subtract, multiply, divide

Use paste special options: values/numbers, transpose

6.3 TEMPLATES

Create a spreadsheet based on an existing template

Modify a template

6.4 LINKING, EMBEDDING AND IMPORTING

Insert, edit, remove a hyperlink

Link data within a spreadsheet, between spreadsheets, between applications

Import delimited data from a text file.

6.5 AUTOMATION

Record a simple macro like: change page setup, apply a custom number format, apply autoformats to a cell range, insert fields in worksheet header, footer

Run a macro

Assign a macro to a custom button on a toolbar.

7. COLLABORATIVE EDITING

7.1 TRACKING AND REVIEWING

Turn on, off track changes. Track changes in a worksheet using a specified display view

Accept, reject changes in a worksheet

Compare and merge spreadsheets.

7.2 SECURITY

Add, remove password protection for a spreadsheet: to open, to modify

Protect, unprotect cells, worksheet with a password

Hide, unhide formulas.

8. GENERAL TERMS OF USE

___________________________

RELATED PAGES:

Advanced spreadsheets – handbook s and quizzes: link

Module 4 – Spreadsheets – Microsoft Excel 2010: link

Module 4 – Spreadsheets – LibreOffice Calc: link

Advanced spreadsheets – Microsoft Excel 2010: link

Advanced spreadsheets: analysis and special tools – quiz

Advanced spreadsheets: usage of functions – quiz

Advanced spreadsheets: protection, sorting and queries – quiz

Advanced spreadsheets: naming cell ranges – advanced formatting – quiz

Advanced spreadsheets: handling data – connecting templates and charts – quiz

Advanced spreadsheets: editing data and display – quiz