Clerici Alberto - Excel Workbook: 160 Exercises with Solutions and Comments

Excel is the most popular and widely used productivity software in all business environments, and it is an irreplaceable companion in ordinary work as in the analysis of large amounts of complex data. Nevertheless, the majority of users knows and uses only a very limited number of features, often in an elementary way. This workbook shows in practice the use of a wide variety of formulas, functions and features (like charts, pivot tables, macros or the Solver add-in) that allow to effectively and professionally work with Excel.

The workbook starts with the basics and gets progressively to deal with very complex cases. It is a valuable support for college students, professionals and managers who want to learn the basics or to improve the knowledge of Excel up to an advanced level.

DigitaBook format includes the digital edition of the book with all the initial and solved files available to carry out the exercises and check the solutions. Over 80 exercises are commented, to highlight the basic concepts and clarify the most complex ones.

Massimo Ballerini, Maria Chiara Debernardi, Davide Del Corno, Maurizio De Pra are lecturers for Computer Science courses at Universit Bocconi in Milan.

Alberto Clerici is director of ITEC (IT Education Center) at the same University.

THIRD EDITION

EXCEL
WORKBOOK

160 Exercises with Solutions and Comments

M. Ballerini A. Clerici

M. C. Debernardi D. Del Corno

M. De Pra

Copyright 2015, 2017, 2021 EGEA S.p.A.

Via Salasco, 5 20136 Milano

Tel. 02-58365751 Fax 02-58365753

All rights reserved, including but not limited to translation, total or partial adaptation, reproduction, and communication to the public by any means on any media (including microfilms, films, photocopies, electronic or digital media), as well as electronic information storage and retrieval systems. For more information or permission to use material from this text, see the website www.egeaeditore.it

Given the characteristics of Internet, the publisher is not responsible for any changes of address and contents of the websites mentioned.

Third edition: February 2021

ISBN Domestic Edition 978-88-31322-08-9

ISBN International Edition 978-88-31322-08-9

ISBN Digital International Edition 978-88-31322-09-6

This workbook derives from many years of experience of the authors in Excel courses. The experience with students of different ages, background and employment status, ranging from first year university students to master students, up to managers and professionals with several years of work experience, has allowed us to understand how Excel is an indispensable tool useful in all kinds of jobs and at all levels, and is as important as poorly known. The majority of users, even those who say or believe they know how to use Excel, actually know and use only a very limited number of features, often in an elementary and ineffective way.

Excel in fact is the most used tool in businesses and offices, and its knowledge is a competitive advantage that college students already have when they need to carry out the first group work: those who know how to use Excel have an edge over the others. This competitive advantage becomes more important when they enter into a business environment. When facing an interview, one of the most feared questions is: So, you told me that you have a good knowledge of Excel: lets try to build a pivot table to analyze these data... and here starts a cold sweat and, while you try to draft a response, the belief that the position will be assigned to someone else grows. Employers know that those who have a good knowledge of Excel work more efficiently, and workers know that with a solid Excel foundation one can perform daily work better and more quickly, whether it is a table with a few simple formulas or the analysis of complex data.

This workbook allows putting into practice the use of a wide variety of formulas, functions and features (such as pivot tables, macros, statistical and financial functions, and the Solver add-in) that allow working effectively and professionally with Excel.

This workbook is divided into 12 thematic units. Unit 1 starts with the basics of Excel, and unit after unit the workbook is designed to arrive in a progressive manner to deal with very complex cases. In the dedicate web area at http://mybook.egeaonline.it you can find the digital edition of the book with all the initial and solved files that allow to perform the exercises and test the solutions. Among the 160 exercises available, over 80 are commented, to highlight the basic concepts and the peculiarities, and clarify the most complex tasks.

Unit 1. Excel Basics

Topics: simple formulas (addition, subtraction, multiplication, division), using absolute and mixed references, calculating a percentage, calculating a percentage variation, SUM, AVERAGE, MAX, MIN, COUNTA, COUNT, IF, ABS functions, formatting, simple sorting, creating and customizing simple charts.

(30 exercises, 15 commented)

Unit 2. From Simple Functions to Nested Functions

Topics: importing data, TRIM, AND, OR, COUNTBLANK functions, nested functions, conditional formatting, sorting on multiple levels, filter, and Paste command options (Values).

(10 exercises, 6 commented)

Unit 3. Math Functions and Advanced Charts

Topics: SUMPRODUCT, COUNTIF, AVERAGEIF, SUMIF functions, conditional formatting with formulas, Subtotal, Remove Duplicates command, linking cells, creating charts with a trend line, and charting mixed charts and with two axes.

(10 exercises, 5 commented)

Unit 4. Financial Functions and What-If Analysis

Topics: financial functions: PMT, FV, naming cell or ranges, Paste command options (Transpose), Scenarios, and Goal Seek.

(10 exercises, 5 commented)

Unit 5. Text Functions and Pivot Tables

Topics: text functions: RIGHT, LEFT, MID, CONCATENATE, UPPER, LOWER, PROPER, LENGTH, SEARCH, statistical functions: COUNTIFS, SUMIFS, AVERAGEIFS, pivot table, pivot chart, data validation.

(10 exercises, 5 commented)

Unit 6. Managing Dates and Times

Topics: formulas with dates and times, normalization of time units, date and time functions: DAY, MONTH, YEAR, HOUR, MINUTE, SECOND, WEEKDAY, DAYS, TODAY, NOW, DATE, DATEDIF, and IFERROR functions.

(10 exercises, 7 commented)

Unit 7. Lookup & Reference Functions, Data Protection

Topics: Lookup & Reference functions: VLOOKUP, MATCH, statistical function RANK.EQ, worksheet protection, workbook protection, file protection, and encryption.

(15 exercises, 5 commented)

Unit 8. Macro and Elements of Visual Basic for Applications (VBA)

Topics: recording a macro in Excel, assigning a macro to a shape or a button, and simple VBA editing.

(20 exercises, 18 commented)

Unit 9. More Functions and Solver

Topics: ROUND, INT, RAND, RANDBETWEEN, HLOOKUP, PPMT, IPMT, RATE, VA, NPV, IRR, TEXT, CONVERT, NETWORKDAYS functions, Data tables, advanced filter, and Solver add-in.

(15 exercises, 6 commented)

Unit 10. Database Functions and Chart Options

Topics: use and creation of templates, SUM function with 3D references, Database functions: DSUM; DMIN; DMAX; DCOUNT; DAVERAGE, commenting, Compare and Merge Workbooks command, and advanced chart options.