This course consists of two parts. In Part 1, we learn to create and use data vortices for analysis and summarization, and learn to create and use decision matrices to simplify complex formulas.

In Part 2, we learn how LibreOffice Calc array functions work and how to create and use them to enhance your calculations; learn how to create and use user-defined functions and arrays in code script to gain additional functionality and perform complex operations; and also learn how to return an entire array to the spreadsheet in one statement.

Topics Covered:
Part 1:

  • Data Vortices
  • Decision Matrices

Part 2:

  • User-defined Functions • Arrays in spreadsheets
  • Arrays in code script

Who Should Attend:
CPAs, accountants, and other financial professionals wanting to learn more advanced techniques for spreadsheet data manipulation.

Software and version used in course presentation:
In this course we use LibreOffice 7.x for our discussion and demonstrations. The code script language used will be LibreOffice Basic. (although users of various desktop versions currently supported by LibreOffice should benefit).

Learning Objectives
  • Discover and describe the purpose of data vortices and decision matrices, and use them to manipulate spreadsheet data.
  • Explore and describe the purpose of user-defined functions and arrays, and use them to manipulate spreadsheet data.
Last updated/reviewed: May 10, 2024
Prerequisites
Course Complexity: Intermediate
Intermediate knowledge of spreadsheets such as Microsoft Excel, LibreOffice Calc, or Google Sheets. Basic knowledge of computer programming is helpful.
Advance Preparation: Access to a computer with speakers or headset. Alternatively, access to a computer and a telephone (for audio reception).
Education Provider Information
Company: Illumeo, Inc., 75 East Santa Clara St., Suite 1215, San Jose, CA 95113
Contact: For more information regarding this course, including complaint and cancellation policies, please contact our offices at (408) 400- 3993 or send an e-mail to .
Instructor for this course
Course Syllabus
INTRODUCTION AND OVERVIEW
  Course Overview1:10
PART 1
  Part 1 - Learning Objectives0:12
  Data Vortices18:52
  Decision Matrices3:46
  Decision Matrices-Demonstration13:20
  Leveraging Data Vortices and Decision Matrices13:10
  Leveraging Demonstration Cont'd4:32
  Leveraging Demonstration Cont'd12:04
  Review0:10
PART 2
  Part 2 Learning Objectives0:34
  User Defined Function1:08
  User Defined Function - Demonstration Part 114:14
  UDFs Demonstration - Part 218:00
  UDFs Demonstration - Part 322:16
  UDFs Advantages and Disadvantages8:32
  Triggering UDFs21:36
  Arrays6:32
  Arrays Demonstration - Part 113:10
  Arrays Demonstration - Part 210:30
  Amortization Example17:54
  Review0:13
  Final Review1:15
CONTINUOUS PLAY
  Advanced Spreadsheet Techniques in LibreOffice Calc3:20:24
SUPPORTING MATERIAL
  Slides: Advanced Spreadsheet Techniques in LibreOffice CalcPDF
  Advanced Spreadsheet Techniques in LibreOffice Calc GlossaryPDF
REVIEW AND TEST
  REVIEW QUESTIONSquiz
 FINAL EXAMexam