Hong Zhou - Mastering Excel Through Projects: A Learn-by-Doing Approach from Payroll to Crypto to Data Analysis

This Apress imprint is published by the registered company APress Media, LLC part of Springer Nature.

The registered company address is: 1 New York Plaza, New York, NY 10004, U.S.A.

Different people master Excel skills through different approaches, but example-based is probably the most common. However, simple examples cannot help you develop deeper problem-solving skills, that is, they do not offer you the challenge and practices to connect dots. This book introduces a unique project-based approach that can lead you into creative usage of various Excel skills in addition to formulas/functions. There are eight projects, each covering a different topic, including word game, food nutrition ranking, payroll (tax withholding) calculation, encryption, two-way table, Kaplan-Meier analysis, data analysis via PivotTable, and the data mining method K-means clustering. Through these projects, you will experience how Excel skills are organized together to accomplish missions that seem impossible.

A project is different from a simple example dedicated to a specific single skill. To complete a project, you must assemble all your Excel knowledge and skills to find an optimal solution. You must analyze the given information, arrange the data in an easy-to-access setup, find optimal Excel functions, and fabricate formulas. In most cases, you have to refine your solution. This step is equally important because it requires retro-thinking and creativity. In one sentence, a project is much more than an example.

The book starts with a simple but interesting project, the word game, which asks users to find English words that have exactly 100 points given the 26 alphabets having points 1, 2, 3, , 26, respectively. Not only does the book show you step by step how to disassemble a word into letters and then sum up their points, it takes you one step further by considering how to make the product, the completed Excel worksheet, more user-friendly and completely automatic. In this sense, the book is talking about developing a software product in the form of Excel worksheets.

Take the project Payroll calculation as another example. The focus of this project is tax withholding calculation. We know that there are a number of software tools which can automate tax withholding, and large accounting offices do not use Excel for this purpose. However, Excel is still a critical and popularly used tool for small business owners. Education-wise, Excel is not replaceable in accounting. Once you have finished this project, you will find out that you have a worksheet product that can automatically calculate tax withholding amount for your employees (assume you have employees).

One unique value of this textbook is that it emphasizes the idea of automation. Fundamentally, why we need Excel is to automate our procedures. Thus, all the projects endeavor to show how you can maximize the automation. To maximize automation, not only do you need to refine our formulas, but you also need to set up tables creatively and apply other built-in features in Excel. This book can guide you step by step on such skills.

No matter if you are a beginner or an experienced user of Excel, this book can definitely serve you well to further elevate your Excel knowledge and skills. For a beginner, the small examples in each chapter will warm you up before you dive into the projects. For experienced users, the projects, especially those with table setup considerations, can help you become more creative in your future endeavors.

I have been teaching and using Excel for more than a decade. My experience tells me that students can quickly forget what they have learned if the examples do not impress them deeply. Projects, however, can always burn a deeper impression than simple examples. For instance, in a Math class, I taught RSA encryption (public and private key) through an Excel project, and this does arouse students interest in Excel.

First of all, this book would not be possible without the assistance from the Apress editing team including Joan Murray and Jill Balzano. Many thanks go to them. Great thanks go to the technical reviewer Kolyu Minevski who thoroughly examined the book for accuracy and provided many valuable suggestions. Kolyus suggestions significantly improved the quality of this book. I would also like to thank my son David Zhou who reviewed the book before it was submitted to Apress. In addition, I would like to thank my colleagues Dr. Katie Martin and Dr. Joseph Manthey who motivated me to learn more about Excel.