Advanced spreadsheets


Advanced spreadsheets – Microsoft Excel 2010: link

Advanced spreadsheets – LibreOffice Calc: link


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


Create a query with one or more criteria using the available options;

Use advanced queries/filtering options

start the quiz

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


Use the template; modify the template


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

start the quiz

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

start the quiz

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

start the quiz

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

start the quiz

Translation: Katarina Posilović

United Nations Volunteers Online Volunteering service.

Copyright © 2021 All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.5, from