↓
 

The Place For Free Online Training Courses

Improve Your Career Prospects

  • Home
  • Office Software
    • Understanding Spreadsheets
    • Advanced Spreadsheets
    • Understanding Word Processing
    • Advanced Word Processing
    • Understanding Presentations
    • Advanced Presentations
    • Understanding Databases
    • Advanced Database
    • Math
  • ICDL Syllabus
    • ICDL Base Module Syllabus Word Processing
    • ICDL Base Module Syllabus Spreadsheets
    • ICDL Base Module Syllabus Presentations
    • ICDL Base Module Syllabus Databases
    • ICDL Advanced Module Syllabus Word Processing
    • ICDL Advanced Module Syllabus Spreadsheets
    • ICDL Advanced Module Syllabus Presentations
    • ICDL Advanced Module Syllabus Databases
  • FaQ

Advanced Spreadsheets Lessons

Advanced Spreadsheets – Formatting Cells

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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 – Formatting Numbers

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Worksheets

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Split Bars

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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 – Date and Time Functions

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Mathematical Functions

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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 – 3-D and Mixed Reference

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Creating Charts

The Place For Free Online Training Courses

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 – Add a secondary axis to a chart.

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Change the chart type for a defined data series

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Add and delete a data series in a chart

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Re-position chart title, legend, data labels

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Change scale of chart value

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Format chart to display an image

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Using Pivot Tables

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Templates

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Link data within a spreadsheet, between spreadsheets.

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Import delimited data from a text file.

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Automation

The Place For Free Online Training Courses

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

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Collaborative Editing

The Place For Free Online Training Courses

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 – Accept, reject changes in a worksheet.

The Place For Free Online Training Courses

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

Advanced Spreadsheets – Security

The Place For Free Online Training Courses

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

Advanced Spreadsheets Quiz

The Place For Free Online Training Courses
Take lesson
  • Terms and Conditions
  • Privacy Policy
  • Site Map
©2021 - The Place For Free Online Training Courses Privacy Policy
↑