Advanced Spreadsheets – Formatting Cells

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 … Take lesson

Advanced Spreadsheets – Conditional Formatting

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 … Take lesson

Advanced Spreadsheets – Worksheets

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’. … Take lesson

Advanced Spreadsheets – Split Bars

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 … Take lesson

Advanced Spreadsheets – Hide and Show

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, … Take lesson

Advanced Spreadsheets – Mathematical Functions

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 … Take lesson

Advanced Spreadsheets – Statistical Functions

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 … Take lesson

Advanced Spreadsheets – Text Functions

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 … Take lesson

Advanced Spreadsheets – Financial Functions

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 … Take lesson

Advanced Spreadsheets – Lookup Function

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 … Take lesson

Advanced Spreadsheets – Database Functions

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 … Take lesson

Advanced Spreadsheets – Nested Functions

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 … Take lesson

Advanced Spreadsheets – Creating Charts

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 … Take lesson

Advanced Spreadsheets – Using Pivot Tables

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 … Take lesson

Advanced Spreadsheets – Filter and Sort Data

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 … Take lesson

Advanced Spreadsheets – Group Data

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 … Take lesson

Advanced Spreadsheets – Sorting Data

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 … Take lesson

Advanced Spreadsheets – List and Sort

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. … Take lesson

Advanced Spreadsheets – Filter a List

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’. … Take lesson

Advanced Spreadsheets – Filter

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 … Take lesson

Advanced Spreadsheets – Sub-totaling

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 … Take lesson

Advanced Spreadsheets – Scenarios

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 … Take lesson

Advanced Spreadsheets – Validating

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 … Take lesson

Advanced Spreadsheets – Auditing

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’ … Take lesson

Advanced Spreadsheets – Show Formulas

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 … Take lesson

Advanced Spreadsheets – Comments & Notes

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, … Take lesson

Advanced Spreadsheets – Naming Cells

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 … Take lesson

Advanced Spreadsheets – Paste Special

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. … Take lesson

Advanced Spreadsheets – Templates

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 … Take lesson

Advanced Spreadsheets – Hyperlinks

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. … Take lesson

Advanced Spreadsheets – Automation

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 … Take lesson

Advanced Spreadsheets – Run a macro

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. … Take lesson

Advanced Spreadsheets – Collaborative Editing

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 … Take lesson

Advanced Spreadsheets – Security

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 … Take lesson