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 – Formatting Numbers
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
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
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 – Date and Time Functions
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
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 – 3-D and Mixed Reference
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
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.
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
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
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
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
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
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
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. From the … 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. To insert a hyperlink, … Take lesson
Advanced Spreadsheets – Link data within a spreadsheet, between spreadsheets.
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.
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
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. In … 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 – Accept, reject changes in a worksheet.
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
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