Excel Workbook: 160 Exercises with Solutions and Comments

503 Pages·2021·2.318 MB·English

by  Alberto Clerici, Davide Del Corno, Maurizio De Pra, Massimo Ballerini, Chiara Debernardi Maria

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. [phone] – Fax [phone]
[e-mail] – www.egeaeditore.it
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
Table of Contents
PREFACE
PLAN OF THE WORKBOOK
SECTION 1. EXERCISES
UNIT 1. EXCEL BASICS
EXERCISE 1.1 - Quarterly sales
EXERCISE 1.2 - Four elections
EXERCISE 1.3 - Income
EXERCISE 1.4 - Cinema
EXERCISE 1.5 - Courses
EXERCISE 1.6 - Population by age
EXERCISE 1.7 - Sales data
EXERCISE 1.8 - Exams
EXERCISE 1.9 - Balance sheet
EXERCISE 1.10 - TV shows
EXERCISE 1.11 - Cigarettes
EXERCISE 1.12 - Families
EXERCISE 1.13 - Beer and wine
EXERCISE 1.14 - Three elections
EXERCISE 1.15 - Athletics
EXERCISE 1.16 - Bills
EXERCISE 1.17 - Turnover analysis
EXERCISE 1.18 - US and foreign sales
EXERCISE 1.19 - Rainfall
EXERCISE 1.20 - UFO
EXERCISE 1.21 - Accidents
EXERCISE 1.22 - Music sales
EXERCISE 1.23 - Seven dwarfs
EXERCISE 1.24 - Expenses
EXERCISE 1.25 - Three musketeers
EXERCISE 1.26 - Murders
EXERCISE 1.27 - City council
EXERCISE 1.28 - Parliamentary groups
EXERCISE 1.29 - Regional chief towns
EXERCISE 1.30 - Population (summary exercise)
UNIT 2. FROM SIMPLE FUNCTIONS TO NESTED FUNCTIONS
EXERCISE 2.1 - Home loans
EXERCISE 2.2 - Internet users
EXERCISE 2.3 - Top sellers
EXERCISE 2.4 - Alphabet Inc.
EXERCISE 2.5 - Show
EXERCISE 2.6 - Productivity
EXERCISE 2.7 - Customer care
EXERCISE 2.8 - Istat
EXERCISE 2.9 - Planets (summary exercise)
EXERCISE 2.10 - Home Sweet Home (summary exercise)
UNIT 3. MATH FUNCTIONS AND ADVANCED CHARTS
EXERCISE 3.1 - Drugstores
EXERCISE 3.2 - Men at work
EXERCISE 3.3 - Car Insurances
EXERCISE 3.4 - Exam
EXERCISE 3.5 - Clothing Store
EXERCISE 3.6 - Beauty Business
EXERCISE 3.7 - Cosmetics Shop
EXERCISE 3.8 - International Education
EXERCISE 3.9 - Dinner with classmates (summary exercise)
EXERCISE 3.10 - Wages (summary exercise)
UNIT 4. FINANCIAL FUNCTIONS AND WHAT-IF ANALYSIS
EXERCISE 4.1 - Emerald
EXERCISE 4.2 - Smart Courses
EXERCISE 4.3 - Credits and debts
EXERCISE 4.4 - Cloth Paradise
EXERCISE 4.5 - Discount on rates
EXERCISE 4.6 - Smart Investments Bank
EXERCISE 4.7 - Garden Center (summary exercise)
EXERCISE 4.8 - GreenBio (summary exercise)
EXERCISE 4.9 - PhotoMagic (summary exercise)
EXERCISE 4.10 - Bright Sun (summary exercise)
UNIT 5. TEXT FUNCTIONS AND PIVOT TABLES
EXERCISE 5.1 - IBAN codes
EXERCISE 5.2 - Names
EXERCISE 5.3 - Superstore
EXERCISE 5.4 - Beauty
EXERCISE 5.5 - Europe sales
EXERCISE 5.6 - Erasmus
EXERCISE 5.7 - Vitanic
EXERCISE 5.8 - Office Line
EXERCISE 5.9 - Greenwich Village (summary exercise)
EXERCISE 5.10 - Black-yellow supporters (summary exercise)
UNIT 6. MANAGING DATES AND TIMES
EXERCISE 6.1 - Two hours
EXERCISE 6.2 - Timesheet
EXERCISE 6.3 - Date check
EXERCISE 6.4 - Linguistic certifications
EXERCISE 6.5 - Techno Building
EXERCISE 6.6 - Hotel Booking
EXERCISE 6.7 - My Friends
EXERCISE 6.8 - Temporary workers
EXERCISE 6.9 - Payments (summary exercise)
EXERCISE 6.10 - Stationery (summary exercise)
UNIT 7. LOOKUP & REFERENCE FUNCTIONS, DATA PROTECTION
EXERCISE 7.1 - Rocky Sports
EXERCISE 7.2 - Property Evaluation
EXERCISE 7.3 - Copy shop
EXERCISE 7.4 - Paper and related
EXERCISE 7.5 - Purchasing Department
EXERCISE 7.6 - DoReMi
EXERCISE 7.7 - Thinkstore
EXERCISE 7.8 - Print Service
EXERCISE 7.9 - European University
EXERCISE 7.10 - Consulteam
EXERCISE 7.11 - Candidates selection
EXERCISE 7.12 - MagicWood
EXERCISE 7.13 - Phone calls (summary exercise)
EXERCISE 7.14 - Box Office Star (summary exercise)
EXERCISE 7.15 - Sunshine Events (summary exercise)
UNIT 8. MACROS AND ELEMENTS OF VBA
EXERCISE 8.1 - Students
EXERCISE 8.2 - Tickets
EXERCISE 8.3 - Product list
EXERCISE 8.4 - Stores
EXERCISE 8.5 - Household linens
EXERCISE 8.6 - Arithmetic