Libre Office Calc – Ten interesting features you could use while creating Spreadsheets
Libre Office is a set of freely available open source based office productivity application. Libre Office Calc is the application for making spreadsheets. In this article, we look at nine important features that you might not have known previously, but could prove very useful while using a spread sheet application. Most of the features might also be available with Open Office and MS Office equivalent applications.
- Functions: The function parameter f(x) can be found just before the formula input line in Libre Office Calc. This contains a lot of pre-programmed formulas to calculate the result of specific functions. The pre-programmed functions include mathematical formulas like Average(N1,N2…), geometric functions like ACOS(val), ACOT(val), conversion functions like BIN2DEC(Number), etc that convert binary, hexadecimal, octal, decimal numbers in to any of the four, DEGREES(val) to convert radians in to degrees, financial transaction functions like IPMT(Rate, Period, NPER, PV, FV, Type) to calculate the compound interest value for a set of input variables, character functions like LOWER(text) that convert lower case text to upper case text, date and time functions like NETWORKDAYS(start date, end date, holidays) to return the number of working days between two dates excluding the holidays, NOW() to return the current time value as per the computer. These are just examples, and there are many more pre-programmed functions in each category.
- Change the function of the Enter Key: Its possible to change the function of the enter key. By default, when the Enter key is pressed, the cell below the current working cell is selected. However, if you need to keep moving right frequently, you can set the function of the enter key to move to the immediate right side cell, each time the Enter key is pressed. (Tools –> Options –> Libre Office Calc –> General)
- Freeze: If a spread sheet contains a lot of columns, certain columns can be frozen on the screen while the remaining columns move freely. For example, if you have two columns as name and company, these two can be frozen on the left hand side of the screen while moving/ viewing the data on the other columns. If they are not frozen, it may not be possible to determine (beyond a certain columns) which row (name and company) the data in the columns belongs to, for example. (Window –> Freeze)
- Text Formatting: Some times, when you enter a number like 00123 in a cell (if you want a five digit number), it automatically becomes 123 due to the default formatting. If you don’t have the time to right click and format the cell as text, you could just enter ‘00123 and the cell is automatically formatted as text and the value is entered as it is. This can also be applied to text, dates, etc. But you cannot apply formulas to such cells.
- Auto-Fill: You could select a cell, move the mouse pointer to the right hand bottom position of the cell till a ‘+’ symbol appears. Now you could click and drag the mouse pointer to cover a certain number of cells below. This Auto fill function does the following: If there is text in the first column, the text is copied to all the selected cells below it. If there is a number (example 1), then the selected cells below would contain incremental numbers (2,3,4,….). If there is a formula in the first cell, then the same formula is copied to the selected cells below with corresponding row attributes. Its also possible to auto-fill a series – You could input 3 and 6 in two cells one below the other, and then drag down for the corresponding cells below to contain 9.12,15…. etc.
- Deleting a cell: When you select a cell and press delete, you are presented with options to delete either the data in the cell (or) the cell formatting (or) both. This is useful in some cases where you want to delete a cell containing a decimal number and enter text immediately, without having to change the format of the cell separately.
- Formatting Cells: Cell formatting can do a lot of things beyond selecting it as text, number, date etc. For example, its possible to – strike through the text (using a line); wrap text automatically (so that if the text is bigger than the column size, it can be extended over to the next line(s) without extending the column size); shrinked to fit a cell size (column size); background colour added; formula hidden, etc. (Format –> Cells)
- Printing Options: There are a number of options available with Libre Office Calc when one wants to take print out of the spreadsheets. For example, a page break can be introduced (either row break or column break) to restrict the content printed in a page; header/footer information could be entered to print certain data like date/ company name etc, in all the pages; the formulas contained in the cell can be printed out without the actual data (very useful for trouble shooting), etc.
- Insert Options: There are options to insert special characters (like 1/4, etc) in a single cell; insert a coloured theme background; insert charts (pie chart, bar chart), etc.
- Auto-Filter: Assume that you have a column (for example) called city and each row in the spreadsheet included a city name in it. There are hundreds of entries on this particular sheet, and you want to see only those entries belonging to city called ‘London’. You could apply the auto-filter to this particular column and a drop box appears on the top of the column. Now you could select ‘London’ from the drop box to see only the rows with a value of ‘London’ in the ‘city’ column. Cool ain’t it?