Masaryk University (CZECH REPUBLIC)
About this paper:
Appears in: INTED2021 Proceedings
Publication year: 2021
Pages: 1716-1723
ISBN: 978-84-09-27666-0
ISSN: 2340-1079
doi: 10.21125/inted.2021.0387
Conference name: 15th International Technology, Education and Development Conference
Dates: 8-9 March, 2021
Location: Online Conference
With the growing range of data files, in all areas of applications (business, public administration, healthcare, etc.) there are demands for faster, more efficient and especially automated processing and analysis of large data sets. These skills are highly expected from higher education graduates as one of the essential skills for the 21st century. The article aims to present the practical use of newly implemented dynamic functions in Excel and experience with their teaching to provide students with these skills.

Microsoft Excel is currently one of the most widely used data processing applications, often used to process increasingly large data files. Excel's approach to functions up to now was to use "single-cell" calculations, which were then copied. However, such traditionally conceived Excel functions ( such as filtering, sorting, searching for a unique value, creating number series, etc. ) do not allow requests to be processed efficiently and in an automated way under dynamically modified data sets.

Such more complex processes have so far been performed manually using appropriate procedures. The disadvantage of these solutions is non-validity and consequently, the need to repeat all procedures manually when changing the source data. Alternatively, VBA macros can be used, but these are often in various work and study environments banned, and if not, their programming requires experience and debugging takes a long time.

Therefore, in new versions of Excel, Microsoft introduces a novel philosophy of using dynamic areas for both already traditional functions and completely new dynamic functions.

That is, the result of these functions is not strictly limited to one particular cell in which the function is positioned, but the number of related cells varies according to the source data and requirements of the function.

To ensure teaching will keep pace with the demand of companies and institutions for the knowledge of graduates in processing large and dynamically changing data files, it is necessary to acquaint students with these new dynamic functionalities.

Thus, the article will present in detail the approach to working with the recently introduced dynamic functions in Excel, including their limitations and problems that may occur if they are used. This approach will also be demonstrated via instructive and practical examples and supplemented with recommendations on how to innovate Excel functions' teaching.
Excel, Dynamic Functions, dynamic areas.