LibreOffice Calc - Data Validation

How to Add Numeric Data Validation in Calc Spreadsheet

This tutorial will explain the basics of numeric data validation in LibreOffice Calc worksheet.

Calc provides various ways of data validation in worksheet cells – numeric validation, range validation etc. When a worksheet is used to receive input data from users, data validation can be used to restrict user from invalid data input. Lets see how can it be done in Calc.

Validation

I have a worksheet where I want to restrict user to input any number from 1 to 10 only.

Select the input cell and click Data -> Validity

LibreOffice Calc - Data Validation in Menu

 

In the Validity window, choose Allow: Whole Numbers ; Data: valid range ; Minimum: 1 and Maximum:10.

Go to Input Help tab and enter the message you want to show to user when the cell is selected. The message would be shown as popup when the cell is selected by user.

LibreOffice Calc - Input Help

Go to next tab Error Alert and put the message which can be shown as popup error message to the user. You can opt it out by checking off the option – ‘Show error message when invalid values are entered’.

LibreOffice Calc - Input Help

Press OK once all the options are set. Now click the cell, you can see the popup message which set in Input Help tab:

LibreOffice Calc - Input Help on Display

Now, if you try to enter any value e.g. 20 which is invalid as per the Min and Max we have set in Criteria tab, the popup message box would be displayed:

LibreOffice Calc - Data Validation

This is the way you can input proper accepted values in Calc spreadsheet.

Feel free to leave a comment if you have any questions.

Calendar Template - Imported

How to Download, Import and Use Templates in LibreOffice Calc

This tutorial will show how to use templates in LibreOffice Calc. I will show how to download a template, import it, use it and manage it in LibreOffice Calc.

Download Templates

Templates are a great way to extend the functionality of LibreOffice. Huge numbers of community developed free LibreOffice Calc templates available in extensions.libreoffice.org/templates/.

Download any Calc template you want and save it in your computer. For this tutorial, I have downloaded Calendar Creator template.

Download calendar-creator template

Template files are having .ots (ODF Spreadsheet Template) extension which is used by LibreOffice Calc spreadsheet applications.

Import Templates

To open the manage templates dialog, from the menu choose File -> Templates -> Manage. On the Template Manager Dialog, go to Spreadsheet Tab and click My Templates. 

Template Manager_Import

Using Import button, choose the template .ots file you have downloaded. Template manager will import the .ots file and you can see it under My Templates. Select the template and click open.

Open Template - LibreOffice Calc

 

Use Templates

It might take a moment to load the template, once loaded you can see the template in Calc workspace. Now you can save it as .ods files and your use.

Calendar Template - Imported

 

This is how you can download and use a Calc spreadsheet template in LibreOffice. Feel free to leave a comment or if you are looking for something else using comment box down below.

Next Chapter Data Validation

Style Find Using Find and Replace

How to Find A Paragraph Style in LibreOffice Writer Document

If you have a Writer document e.g. book draft containing various styles of text/paragraphs, it is sometimes needed to find where in the document a specific style present. In this tutorial, I will show you how to find a paragraph style in the LibreOffice Writer document.

Sample Document

I have a sample document, generated with dummy text as below. I have also changed the letter spacing of certain lines which I would like to find.

Sample Text for Style Search
Sample Text for Style Search

Create Style

Select the spacing lines and from the style toolbar, create New Style (Or, you can find it from the menu – Style -> New Style). In the Style creation dialog, give the style a name e.g. “Test-style-1”.

Select and Create Style
Select and Create Style

Open the Find and Replace dialog (keyboard CTRL+H) from menu: Edit -> Find and Replace. In this dialog, under ‘Other Options’, select the checkbox search for paragraph styles. In the Search For drop-down, select the style you want to find – ‘Test-style-1’. And hit Find Next.

Find and Replace Dialog
Find and Replace Dialog

Find

Once you click Find Next, Writer will start searching the entire document for the specific styles and highlight the same.

Style Find Using Find and Replace
Style Find Using Find and Replace

Now you can find any style you want in a LibreOffice Writer document using the above method. Feel free to leave a comment Or ask questions down below.

Dummy Text -LibreOffice Writer

Generate Random, Dummy Text in LibreOffice Writer

How can we generate a block of random text in LibreOffice Writer? In this tutorial, we will show you how.

It is often needed to generate a random block of text for various purposes e.g. checking fonts, paragraphs, and styles etc.

Though you can always use your own text for your needs. However, LibreOffice Writer provides a way to generate a block of random texts in a simple way.

Generate Random/Dummy Text in LibreOffice Writer

Follow the below steps:

  • Open a blank document in Writer.
  • Type this text: dt
dt
  • Then press function key F3. Once pressed, you can see below block of dummy text automatically displayed in writer. Now you can use this auto-generated text for your needs.
Dummy Text -LibreOffice Writer
Dummy Text -LibreOffice Writer

Tip: You can also create this using Menu: Tools -> AutoText; Standard -> Dummy Text.

So, this is how you can generate dummy text in Writer. Feel free to leave comments or ask questions down below.

Calc - Menu - Find

Basics of Find, Replace in LibreOffice Calc

This article explains how to perform basic search and replace in a Calc spreadshet.

Find

Spreadsheet may contains thousands of data. Find or Search functions used to locate a string/text in a spreadsheet. You can find option in Calc Menu : Edit -> Find.

Calc - Menu - Find

After clicking the Find from menu, the main dialog appears at the bottom of the Sheet with a textbox where you can type the text you want to search. Then hit the Find Next (down arrow icon).

As a result, you can see the first hit is highlighted/ selected in the sheet if the text is found.

Calc- Single Find - Next

If you keep clicking the Find, Calc would show all the matching strings one-by-one.

Find All

A text may appear multiple places in a sheet. The Find All button searches for all matching texts together and highlights them. Calc also shows a popup dialog with the list of cells numbers where it found the text. Find all also searches multiple sheets for the same text and popup dialog shows the sheet name as well.

Calc - Find All

Replace

Replacing a text is easy in Calc. Open the Find and Replace dialog from the Menu: Edit -> Find and Replace.

Calc - Menu - Find and Replace

The find a replace dialog shows two main text box. Enter the text you want to find in the Find text box. And enter the text which you want to be replaced with in the replace text box.

As a result of clicking the Replace All, all the matched text from find text box would be replaced with the replace text.

Calc- Replace All

 

Find and Replace in a Selected Range

LibreOffice Calc find and replace dialog searches and replaces strings in entire sheet. However, it is often needed to perform find and replace in a selected ranges but not in entire sheet. Here’s how you can do it:

Select a range where you want to find a text and replace.

Open the Find and Replace dialog (CTRL+H). Provide Find and Replace string. 

Under ‘Other options’, select “Current Selection Only” and press replace all.

Now you can see only the strings matching “East” inside the selection is replaced. Other non-selected cells strings remains untouched.

Drop a comment if you are facing difficulties or any suggestions.

 

Next Chapter Using Templates

Format Cells In LibreOffice Calc

LibreOffice Calc allows you to format cells in any type without changing its value. You can change a value “0.5” to “50%” or “$0.5”, or change the appearance of the cell itself.

Format Cells in Calc

Type any number in any cell of Calc.

By default, Calc uses default number format when you type a number in a cell. To format the number, right-click on the cell and select “Format Cells”.

Format Cell Option in Context Menu
Format Cells - Main

On the Format Cells dialog, choose the category as Currency, Format as USD $ (Or anything you want), select desired formatting in the format list. You can also choose a number of decimal places, leading zeros you want. Once you are satisfied by looking at the preview, press OK.

To change the above number to %, you can click the % icon in the toolbar after selecting the cell. Calc provides common number formats in the toolbar itself for easy access.

Percent in Toolbar

Use Borders, border style, Font Color and alignment from the toolbar to give the cell a different look:

order Styles in Toolbar
Borders in Toolbar
Color Palette in Toolbar

Once it is applied to the cell, it would look like this:

Next Chapter   Find and Replace