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

By Hong Zhou

Master Excel in less than two weeks with this unique project-based book! Let’s face it, we all master skills in our own way, but building a soup-to-nuts project is one of the best ways to make learning stick and get up to speed quickly. Whether you are just getting started with Excel or are an experienced user, this book will elevate your knowledge and skills. For a beginner, the micro 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, will help you become more creative in your interactions with Excel.

Readers will benefit from building eight unique projects, each covering a different topic, including a word game, a food nutrition ranking, a payroll (tax withholding) calculation, an encryption, a two-way table, a Kaplan-Meier analysis, a data analysis via a pivot table and the K-means Clustering data mining method. Through these projects, you will experience firsthand how Excel skills are organized together to accomplish tasks that sound complex and daunting when first described.

Get started with a word game which asks users to find English words that amount to exactly 100 points, with each letter of the alphabet assigned a point 1, 2, 3, … 26, respectively. You will disassemble a word into letters and then sum up their points, and then take it one step further, contemplating how to make the completed Excel worksheet more user friendly and completely automated. Increasingly challenging tasks like this example build on what you have learned and increase your confidence along the way, ensuring your mastery of Excel.

What You Will Learn

• Gain confidence to tackle a challenging Excel-related mission, even those that seem impossible
• Become skilled in the creative uses of Excel formulas and functions and other built-in features
• Appreciate the art of refining worksheets to maximize automation
• Understand the value of treating each worksheet as a unique product

Who This Book Is For

People who are interested in learning Excel as quickly and efficiently as possible. While Excel beginners and intermediate users are the primary audience, experienced Excel users might also discover new skills and ways of working with Excel.

• Language: English
• Publisher: Apress
• Release date: Nov 27, 2021
• ISBN: 9781484278420

Book preview

© The Author(s), under exclusive license to APress Media, LLC, part of Springer Nature 2022

H. ZhouMastering Excel Through Projectshttps://doi.org/10.1007/978-1-4842-7842-0_1

1. Master Excel Through Projects

Hong Zhou¹  

(1)

University of Saint Joseph, West Hartford, CT, USA

There are quite some books that teach us how to master Excel worksheets, and many of them are excellent. Some books go into every detail and are so valuable that they can be referenced as an encyclopedia for Excel. Nevertheless, this book takes a unique route to practice and master Excel.

Why Learn Through Projects

Different people learn/master Excel skills through different approaches, but example-based is probably the most common. Unfortunately, simple examples cannot help us develop deep problem-solving skills, that is, they do not offer us the practices to connect dots. This book introduces a unique project-based approach that can lead us into creative usage of various Excel skills in addition to formulas/functions. There are eight projects, each covering a distinct 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, we will experience how Excel skills are organized together to accomplish missions that seem very far away.

A project is different from a simple example dedicated to a single and specific skill. To complete a project, we must assemble all our Excel knowledge and skills to come up with an optimal solution. We often have to learn new skills for a new project. In addition, we must analyze the given data and arrange the data in an easy-to-access setup before we fabricate optimal formulas. This indeed offers a valuable and unique experience.

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

When we study simple examples, we finish the learning and immediately move on. However, learning through projects is different because frequently we want the end result of a project to be a product, a functional Excel workbook/worksheet that can be delivered as an application. This requires us to spend additional effort to maximize the automation of our worksheet(s). Fundamentally, the reason why we make use of Excel is to automate our data processing procedures, isn’t it?

In one sentence, Excel is for storing, managing, and analyzing data. In two words, managing data. Certainly, we do not want to manage or process data manually; we want to use Excel to help us automate the process to speed up. That is why we are going to study Excel from the perspective of automation, that is, whenever we work on a project, we should keep asking ourselves: how can we make it more automatic?

There are four elements in Excel that can help automate data processing (excluding VBA and macros). They are cell reference, autofill, built-in features, and formulas. The first two elements are fairly simple but critical. The built-in features are easy to understand, too. The biggest challenge lies with formulas which require familiarity with and creative uses of Excel functions. Don’t worry, we will go through all the four elements by simple examples and projects, though we won’t be able to cover every Excel function. However, once we have gone through all the projects in this book, we should have a fair learning ability to self-learn Excel functions not covered here.

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

The confidence in self-learning is critical in our life. This type of confidence is built up through and can be reinforced by accomplishing tasks we feel unfamiliar with. This book will offer such a type of experience through the projects.

One advantage of this book is that the content is very much version-independent, that is, most skills introduced in this book are independent of the version of Excel we are using. So, some new features including some new functions only available to Office 365 or Excel 2019, for instance, MAXIFS, TEXTJOIN, and XLOOKUP, won’t be explained in this book.

Cell Reference and Formula

Open a blank Excel worksheet, and what jumps into our eyes is a huge table. Yes, Excel is just a huge table with columns and rows. As we can notice, the columns are labeled A, B, C, …, and the rows are numbered 1, 2, 3, etc. Each cell can be identified by a reference, that is, each cell has a unique name. The cell reference represents the cell. For example, the top left cell is referenced as A1, that is, column A and row 1. Observe that a cell reference must have two parts: the column index (A in A1) and the row index (1 in A1).

Excel is case insensitive. Cell reference A1 is treated the same as a1. This case insensitivity applies to almost everything in Excel. Keep this in mind, please.

The first usage of Excel cells is to store data. There are four common data types we will see in cells: text, number, date, and formula. We can type in cell A1 any value we want. For example, we can type in cell A1 the text Hello World (without the quotation marks), or a number 3.1415926, or a date 3/14/1970 (without the quotation marks), or a formula which is our next topic.

Formula

What is a formula? A formula is simply an expression that can perform operations on data. To write a formula, we always start our expression with the symbol =, and there should be no space(s) in front of =. When the content in a cell starts with the symbol =, for example, =1 + 2, the content becomes special because it is in fact a formula. Generally, expressions not starting with = are not treated as a formula but rather as a plain text string in Excel. The only exceptions are those starting with either + (plus) or – (minus) sign. Excel recognizes such expressions as a formula and automatically adds the symbol = at their beginning.

A formula is like a mathematical expression that conducts a calculation and then presents the result inside the cell. For example, if we type =1+2 in cell A1 and hit the Enter key, the expression 1+2 is calculated. Since the calculation result is 3, 3 is returned. In other words, 3 is displayed in cell A1 by default.

Mathematical expressions are the most commonly used formulas. Be aware that * is the multiplication operator, / is the division operator, and ^ is the exponent operator in Excel. Let’s conduct one experiment.

Suppose one can live up to 110 years, and each year has exactly 365 days. How can you use Excel to calculate the number of seconds equivalent to 110 years?

Open an Excel worksheet. In cell A1, type the following expression, and then hit the Enter key:

=110 * 365 * 24 * 60 * 60

This is a formula since it starts with =. Recall that * is the multiplication operator. Be aware if what we typed is 110 * 365 * 24 * 60 * 60 in cell A1, then it is not a formula but a text string only.

Cell Reference in Formulas

Assume we type the integer 67890 inside A1. At this point, the cell reference A1 represents the integer 67890. For example, if we type in cell B1 the formula =A1, 67890 will be displayed in B1 because the formula =A1 is translated to =67890.

Now, let’s assume a friend is looking for a job and this integer 67890 is the proposed salary of one job offer. Our friend wants to know, given this salary, if paid monthly, biweekly, or weekly, what is the amount of every pay? Let’s do the following experiment.

Experiment

Enter 67890 in cell A1 (we can also enter it like $67890). Enter the formula =67890/12 in cell B1. This calculates monthly payment. Again, there should be no space before =.

Enter =67890/26 and =67890/52 in cells C1 and D1, respectively, as shown in Figure 1-1. C1 is for biweekly payment, and D1 is for weekly payment.

Take a close look at cells B1, C1, and D1 shown in Figure 1-1. We should notice again that there is no space before the operator =.

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig1_HTML.jpg

Figure 1-1

Use the value inside cell A1 directly

After entering the formulas in cells B1, C1, and D1, our worksheet should look like Figure 1-2. Be aware that the decimal digits in our cells may look different from those in Figure 1-2.

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig2_HTML.jpg

Figure 1-2

The calculated results are presented

Assume that after some negotiation, the salary of the job offer becomes $70896, and, therefore, our friend wants us to recalculate the monthly, biweekly, and weekly pay for her/him. What shall we do?

We have to retype the number 70896 in all the cells A1, B1, C1, and D1. This is truly inconvenient. What if the number in A1 needs to be changed again?

This is where cell reference comes into the play. As mentioned before, the reference A1 represents whatever is inside cell A1. Note that if the cell has a formula, then the cell reference represents the calculated result instead of the formula itself. Thus, when we enter the formulas inside B1, C1, and D1, we should not type the number 67890. Instead, we should replace 67890 with the reference A1. This is shown in Figure 1-3.

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig3_HTML.jpg

Figure 1-3

Use cell reference in formulas

Since we have replaced 67890 with the cell reference A1 in cells B1, C1, and D1, whenever we change the number in A1, the results in cells B1, C1, and D1 will be updated automatically. This is because the formulas in cells B1, C1, and D1 automatically recalculate.

So, the first lesson in this book is that we should always use the cell reference to represent the value inside a cell instead of using the cell value itself literally.

Excel Options and Show Formulas

In the recent experiment, we have learned that we need to use cell references as much as possible in our formulas so that any changes can be immediately reflected by automatic formula recalculation. In fact, Excel automatically encourages users to use cell references by setting the Workbook Calculation option to be Automatic. Let’s take a look at the following data.

Please click File ➤ Options as shown in Figure 1-4. The Excel Options window comes up.

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig4_HTML.jpg

Figure 1-4

Access Excel options

On the Excel Options window, please select Formula as illustrated in Figure 1-5. Figure 1-5 reveals that we can disable the Automatic option for Workbook Calculation, but I won’t suggest so. Once we disable the Automatic option for Workbook Calculation, cells B1, C1, and D1 won’t automatically update their results when A1 is changed. So, let’s keep the Workbook Calculation as Automatic for this book. Figure 1-5 also displays other Excel options that are related to how formulas work. Keep the default settings unless I instruct you to change them.

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig5_HTML.jpg

Figure 1-5

The Workbook Calculation options

Have you ever wondered how we can display formulas like Figure 1-1? Figure 1-1 and Figure 1-2 display the same worksheet in two different views. Figure 1-2 displays the regular outlook of a worksheet in which cells display the calculated results instead of the formulas inside.

If we want a worksheet to display the formulas so that we can examine the formulas visually, we can click the Formulas tab ➤ Show Formulas as illustrated in Figure 1-6.

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig6_HTML.jpg

Figure 1-6

Show Formulas

To go back to the regular view, click Show Formulas again.

Autofill or Copy

Autofill is a critical feature of Excel because it makes Excel capable of working with a relatively large dataset automatically. Autofill is also called copy by some people. Because autofill is so critical, we are going to learn this skill through multiple experiments.

Experiment 1

Let’s conduct the following experiment:

1.

Enter 1 in cell A1.

2.

Enter 2 in cell A2.

3.

Select both cells A1 and A2 by clicking cell A1 and dragging the cursor down to cell A2.

4.

Release the left mouse button. It is very important to release the left mouse button at this step.

5.

Move the mouse cursor to the bottom-right corner of cell A2 until the cursor becomes a black cross (shown in Figure 1-7).

../images/519560_1_En_1_Chapter/519560_1_En_1_Fig7_HTML.jpg

Figure 1-7

The mouse cursor becomes a black cross

6.

Press the left mouse button, and drag down to cell A6.

The cells A1:A6 are automatically filled with numbers 1, 2, 3, 4, 5, and 6. This process is called autofill, somewhat different from copy. Note that A1:A6 reads A1 to A6, meaning from cell A1 to cell A6.

So, how does Excel achieve this type of autofill?

Well, Excel is kind of smart. When our first two cells have numbers 1 and 2, Excel automatically computes the increment (or decrement) step value to be 1 (2 - 1 = 1). Thus, Excel automatically increments the values by 1 when