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.
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
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.
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’.
Press OK once all the options are set. Now click the cell, you can see the popup message which set in Input Help tab:
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:
This is the way you can input proper accepted values in Calc spreadsheet.
Feel free to leave a comment if you have any questions.
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.
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.
Template files are having .ots (ODF Spreadsheet Template) extension which is used by LibreOffice Calc spreadsheet applications.
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.
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.
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.
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.
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.
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.
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”.
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.
Once you click Find Next, Writer will start searching the entire document for the specific styles and highlight the same.
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.
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
- 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.
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.
This article explains how to perform basic search and replace in a Calc spreadshet.
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.
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.
If you keep clicking the Find, Calc would show all the matching strings one-by-one.
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.
Replacing a text is easy in Calc. Open the Find and Replace dialog from the Menu: Edit -> 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.
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.
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”.
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.
Use Borders, border style, Font Color and alignment from the toolbar to give the cell a different look:
Once it is applied to the cell, it would look like this: