Advanced spreadsheets
Handbooks:
Advanced spreadsheets – Microsoft Excel 2010: link
Advanced spreadsheets – LibreOffice Calc: link
Quizzes:
By using the quizzes posted on the links below test your knowledge of advanced work with spreadsheets in the Microsoft Excel 2010 program (prepared and drafted by: Mario Dujlo):
Editing data and layout: Name cells/cell range on a worksheet; Apply automatic formatting on the cells range; Create user number format; Use conditional formatting and special paste options; Import data from a text file delimited by a comma, space or a tab; Freeze row and/or column; Hide/unhide rows or columns or worksheets; Use automatic sub-totalling features; Use the table with one or two variables/”What if” tables |
start the quiz |
Protection and security: Protect/unprotect worksheet with a password; Add, remove password protection for a spreadsheet: to open, to modify: Data sorting: Sort the data according to the criteria in multiple columns; Perform a custom sort Queries/Filters: Create a query with one or more criteria using the available options; Use advanced queries/filtering options |
|
Data connecting: Link data/chart within a worksheet; Link data/chart between worksheets; Link data/chart between workbooks; Link data/chart with the document of the text editing application; Consolidate data in adjacent worksheets by using the 3D sum function Update, break a link Templates: Use the template; modify the template Charts: Creating charts Format text or numbers on the axes of the chart; Re-position chart title, legend or data labels; Change the angle of slices in a pie chart; „separate“ (explode) all the segments of the pie chart; Delete the data set from the chart; Change the chart type for a defined data series; Increase the distance between the columns in a 2D chart; Insert an image in a 2D chart |
|
Using functions: Use the date and time functions: TODAY, DAY, MONTH, YEAR; Use mathematical functions: SUMIF, ROUND; Use statistical functions: COUNT, COUNTA, COUNTIF; Use the functions for text: PROPER, UPPER, LOWER, CONCATENATE; Use financial functions: FV, NPV, PMT, PV, RATE; Use the functions for referencing and searching: HLOOKUP, VLOOKUP; Use logical functions: IF, AND, OR, ISERROR; Use available database functions: DSUM, DMIN, DMAX, DCOUNT; Use nested functions |
|
Analysis – Pivot Table: Create, modify a pivot table using the defined field names; Modify the data source and refresh a pivot table; Filter, sort, group data in a pivot table Analysis – Scenarios/Versions: Create named scenarios/versions based on a default cell range; Show, edit, delete scenarios; Create a scenario summary report Analysis – Formula Auditing: Trace precedent cells on a worksheet; Trace dependent cells on a worksheet; Show all formulas or see the locations of all formulas on a worksheet; Insert, edit, delete comments on a worksheet; Show, hide comments on a worksheet Special tools – Macros: Record a simple macro (e.g. change the page settings); Run a macro; Assign a macro to a custom button on the toolbar |
|
Enhancing Productivity:
Name cell ranges; use named cell ranges in a function; Use paste special options Advanced Formatting: Apply an autoformat/table style to a cell range; Apply conditional formatting based on cell content; Create and apply custom number formats; Import delimited data from a text file |
Translation: Katarina Posilović