Advanced Spreadsheet Lessons

Apply an auto-format/table style to a cell range. Auto formatting for a cell range formats that particular cell range as per the selected options automatically. This means that the complete selected cell range will get formatted in the selected manner without having to manually format each individual cell. To apply auto-formatting to a cell range,…

Apply conditional formatting based on cell content. Conditional formatting helps us in formatting only those particular cells which satisfy the specified conditions. This means that in any selected cell range, we will define some condition and the cells which satisfy this condition will get formatted while the others will remain as it is. To apply…

Create and apply custom number formats. We have a variety of number formats which can be used to format the data in a cell in Calc. But sometimes we might need to format our data in some format which might not be available in Calc by default. In such situations, we can create our own…

Copy, move worksheets between spreadsheets. To copy or move a worksheet between spreadsheets, right-click on the sheet name which needs to be moved or copied and from the resulting menu, click on ‘Move/Copy Sheet’. This will open the ‘Move/Copy Sheet’ dialog box. In this dialog box, we have two radio buttons to choose if we…

Split a window. Move and remove split bars. Splitting a window into multiple instances can help us in viewing and operating with multiple spreadsheets at the same time. To split a window, Click on the row header below the rows where you want to split the screen horizontally or click on the column header to…

Hide, show rows, columns, worksheets. To hide a row or a column, just select the row or column you want to hide. After selecting the row or column, click on ‘Format’ from the main menu bar and from the drop-down, click on ‘Row’ or ‘Column’ as per the selected data range. From the sub-menu, click…

Use date and time functions: today, now, day, month, and year. To use the different types of functions and formulas in Calc, select the cell in which you want to display the result of the function, and then click on ‘Insert’ from the main menu bar, and from the resulting drop-down menu, click on ‘Function’.…

Use mathematical functions: rounddown, roundup, sumif. To use mathematical functions, in the same ‘Function Wizard’, select the ‘Mathematical’ category. From the ‘Function’ drop-down list, select ‘ROUNDDOWN’. This function rounds a number down to a predefined accuracy. This means that the number is rounded in such a way that the lower value is always displayed. After…

Use statistical functions: countif, countblank, rank. In the same wizard, under the ‘Statistical’ category, we have the ‘COUNTIF’ function. This function counts the number of arguments which meet the set conditions. This function takes two arguments, ‘range’ and ‘criteria’. These give the range of cells to be evaluated and the criteria to be used for…

Use text functions: left, right, mid, trim, concatenate. In the ‘Function Wizard’, under the ‘Text’ category, we have some functions which can be used to analyze text values. One such function is ‘LEFT’. This function returns a specified number of first characters from any word. This function takes two arguments, ‘text’ and ‘number’. The ‘text’…

Use financial functions: fv, pv, pmt. In the same wizard, under the ‘Financial’ category, we have the ‘FV’ function. This function returns the future value of an investment based on regular payments and a constant rate of interest. This function takes up five values, ‘Rate’, ‘NPER’, ‘PMT’, ‘PV’ and ‘Type’. ‘Rate’ is the rate of…

Use lookup functions: vlookup, hlookup. In the same ‘Function Wizard’, under the ‘Spreadsheet’ category, we have the ‘VLOOKUP’ and ‘HLOOKUP’ functions. ‘VLOOKUP’ is vertical lookup and ‘HLOOKUP’ is the horizontal lookup. ‘VLOOKUP’ does the vertical search and references to the indicated cells. ‘HLOOKUP’ does the horizontal search and reference to the cells located below. Both…

Use database functions: dsum, dmin, dmax, dcount, daverage. In the same ‘Function Wizard’, under the ‘Database’ category, we have some database functions. One of these functions is ‘DSUM’. This function adds all the cells of a data range where the contents match the search criteria. This function takes 3 arguments, ‘Database’, ‘Database field’, and ‘Search…

Create a two-level nested function. A two-level nested function means that we use another function as an argument for a function. We’ll take this with the help of an example. Say we are using a ‘SUM’ function. We can input numbers as the arguments manually or we can input cells or data ranges by using…

Use a 3-D reference within a sum function. Till now we have seen how to use the cell ranges from the same sheet as the arguments for the functions. But we can also use cell ranges from other sheets as arguments for the function. This is known as 3-D referencing. To use 3-D referencing, use…

Create a combined column and line chart. A combined column and line chart is a combination of two chart types. It is useful for combining two distinct but related data series. To create a combined column and line chart in Calc, first of all we need to select some data which we are going to…

To add a secondary axis to a chart, first of all select the chart. After this, right-click anywhere on the chart area, and from the resulting menu click on ‘Insert/Delete Axes’. This will open the ‘Axes’ dialog box. In this dialog box, we have two checkboxes under ‘Secondary Axes’. Check any one or both of…

Changing the chart type for any chart is helpful when we want to showcase our data in a different format. Sometimes, we might create a particular chart for a set of data but then later figure out that some other chart type would have been a better way of depicting this data. In such a…

In the same way as we can change the chart type for a chart, in case some new data field is added for a chart, we can simply update this in the already existing chart and we do not need to create another one from the beginning. To add or delete a data series in…

To re-position the chart title or legends in a Calc chart, first of all select the chart. After this, click once on the chart title or the legends box, the chart title or the legend box will get selected. Now without releasing the mouse button drag the title or the legends to the position where…

Change scale of value axis: minimum, maximum number to display, major interval. In Calc, the values displayed with the axes of a chart are normally the default ones which are selected by the system as per the data being used. In some cases, we might need to change these values, or require different intervals between…

Format columns, bars, plot area, chart area to display an image. In Calc charts, we cannot use custom images by importing them into the charts from our local machine. Though Calc does provide the option for using the different types of ‘Fill’ options for the columns, bars, plot area or chart area like ‘Color’, ‘Hatching’,…

Create and modify a pivot table/data pilot. A pivot table is a very effective and efficient tool for comparing, combining, and analyzing large amounts of data in a simple manner. To use pivot table, the first thing which is required is some raw data. The data should be in the form of a database table…

Filter, sort data in a pivot table/data pilot. Filtering data means applying some conditions to the data so that only those data fields get displayed which satisfy these conditions. To filter data in a pivot table, simply right-click anywhere on the table and from the resulting menu, click on ‘Filter’. This will open the ‘Filter’…

Automatically, manually group data in a pivot table/data pilot and rename groups. Grouping data is a simple process which can be used to create groups of data and then analyzing these groups instead of analyzing the individual values. This simply means that instead of focusing on the individual values, we analyze these values as a…

Sort data by multiple columns at the same time. Sorting data means arranging data as per some pre-defined structures. To sort any data in Calc, the first thing which needs to be done is to select the data which needs to be sorted. After selecting the data, click on ‘Data’ from the main menu bar.…

Create a customized list and perform a custom sort. In some cases, when we might need to sort the data in a way other than the ones available by default in Calc, we can create our own custom list format. In the ‘Sort’ dialog box, under the ‘Options’ tab, we have the ‘Custom sort order’…

Automatically filter a list in place. To automatically filter a list, select the complete list which needs to be filtered. After selecting the list, click on ‘Data’ from the main menu bar and from the resulting drop-down click on ‘Filter’. From the sub-menu which is generated, click on ‘AutoFilter’. This will create a drop-down symbol…

Apply advanced filter options to a list. Advanced filter options help in filtering the data more precisely than it is possible with the default methods available in Calc. To apply advanced filter options to a list, select the list which needs to be filtered. After selecting the list, click on ‘Data’ from the main menu…

Use automatic sub-totaling features. Automatic sub-totaling features help in displaying the sub-totals of particular sections of a table. To use automatic sub-totaling features, first of all make sure that you have automatic filters enabled for all the columns for which sub-totaling needs to be done. After this, select any one cell from the entire range.…

Create named scenarios. A scenario is a tool in Calc which helps in testing ‘what-if’ questions. Anything which depends on a particular condition can be defined using a scenario. Say you need to calculate some amount in different currencies; you can have different scenarios for different currencies and can use them as per the requirement.…

Set and edit validation criteria for data entry in a cell range like: whole number, decimal, list, date, time. To set validation criteria for data entry in a cell range, click on ‘Data’ from the main menu bar, and from the resulting menu, click on ‘Validity’. This will open the ‘Validity’ dialog box. In this…

Trace precedent, dependent cells. Identify cells with missing dependents. Trace precedent gives us the cells on which the value of the current selected cell depends. To trace the precedent of a cell, select the cell and then click on ‘Tools’ from the main menu bar. From the resulting drop-down menu, click on ‘Detective’ and from…

Show all formulas in a worksheet, rather than the resulting values. To show formulas in the worksheet instead of the resulting values, click on ‘Tools’ from the main menu bar. From the resulting drop-down, click on ‘Options’. This opens the ‘Options’ wizard. In the ‘Options’ wizard, under ‘LibreOffice Calc’, click on ‘View’. Then under ‘Display’,…

Insert, edit, delete, show, and hide comments/notes. To insert comments is a worksheet, select the cell to which you need to insert the comment and right-click on this cell. This will open a menu from which you need to click, ‘Insert Comment’. This will give you a small text box attached to the cell as…

Name cell ranges, delete names for cell ranges. To name a cell range, click on ‘Insert’ from the main menu bar. From the resulting menu, click on ‘Names’ and from the sub-menu which gets generated, click on ‘Define’. This will open the ‘Define Name’ dialog box. In this dialog box, under ‘Name’, give the name…

Use paste special options: add, subtract, multiply, divide. Paste special options help in making some quick calculations and formatting changes by a special copy paste function. We need to have some data to use the special paste function. From the available data, simply right-click on any cell and copy that cell to the clip board.…

Create a spreadsheet based on an existing template. To create a spreadsheet based on an already existing template, click on ‘File’ from main menu bar. From the resulting drop-down, click on ‘New’, and from the sub-menu, click on ‘Templates’. This will open the ‘Template Manager’ wizard. Under the ‘Spreadsheets’ tab, click on ‘My Templates’ or…

Insert, edit, and remove a hyperlink. Hyperlinks are used to jump to a different location from within a spread sheet and this location can be in the same file, in different files and also some websites. To insert a hyperlink, click on ‘Insert’ from the main menu bar, and from the resulting drop-down menu, click…

To link data between different sheets within a spreadsheet, select the cell on which you want to create the link. After this go to the input line and input ‘=’. After this, go to the sheet whose data you want to link to this sheet and select the cell which needs to be linked. Click…

There are some file types which are which are actually spreadsheet files, but in text format where cell contents are separated by a character. This character can be anything, say a comma or a colon. The extensions for such files are normally .csv or .txt and data from such files can be imported to Calc.…

Record a simple macro like: change page setup, apply a custom number format, apply auto formats to a cell range, and insert fields in worksheet header, footer. A macro is a saved sequence of commands or keystrokes that are stored for later use. The LibreOffice macro language is very flexible, allowing automation of both simple…

To run a macro, click on ‘Tools’ from the main menu bar. From the resulting drop-down menu, click on ‘Macros’, and from the sub-menu which gets displayed, click on ‘Run Macro’. This will open the ‘Macro Selector’ dialog box. In this dialog box, in the ‘Library’ field, we have the list of all the libraries.…

Tracking and Reviewing Turn on, off track changes. Track changes in a worksheet using a specified display view. Whenever we make changes to any document and send that document to someone else, then it might be possible that some of those changes might not be required by the other person. In such a case, it…

When the spreadsheet in which the changes have been made is sent to someone else, then they can accept or reject the changes and keep only the ones which are required. To accept or reject changes, simply click on ‘Edit’ from the main menu bar. From the drop-down, click on ‘Track Changes’, and from the…

Add, remove password protection for a spreadsheet: to open, to modify. To add password protection for a spreadsheet, click on ‘File’ from the main menu bar and from the resulting drop-down, click on ‘Save As’. This will open the ‘Save As’ dialog box. In this dialog box, we have a ‘Save with password’ checkbox. Check…