We assume that you are already familiar with what
** Microsoft Excel** is and why it is used; if not, please check

**. We also assume you know how to create and format a simple table; if you're unsure, please check**

*here***. If you don't even have**

*here***Microsoft Excel**installed, check

**, then go through the previous two lessons of this excellent**

*here***Microsoft Excel**tutorial. Then, return to this third lesson, one of the most important in

**Microsoft Excel**. Why the most important? Even though it's hard to highlight anything in particular about

**Microsoft Excel**, we assume that the built-in functions and calculations you create are the most important reason for using

**Microsoft Excel**. However, efficiently managing

**Microsoft Excel**functions and calculations requires a combination of knowledge, practice, and creativity.

Incorporating these tips into your daily routine will not
only improve your productivity but also ease your work with data on new and
larger levels. Whether you're a beginner or an experienced user, you should
always be aware that **Microsoft Excel**, as a powerful tool for working with
tabular data, offers unlimited possibilities for effectively managing functions
and calculations. But despite all these possibilities and the immense help
**Microsoft Excel** offers, your creativity depends mainly on you, your practice,
and professionalism in how you want to create and present your tables. Your
tabular data and calculations must always be accurate and precise. Mistakes in
this regard are unacceptable and **non-negotiable**. However, the aesthetics of
working with** Microsoft Excel** documents depend solely on you or the company rules you
follow.

**A girl calculates a household budget in Microsoft Excel**

The first step towards effectively managing

**Microsoft Excel**is, among other things, a thorough understanding of basic functions and formulas. This third lesson of our

**Microsoft Excel**tutorial will help you master the basics, such as using the

**SUM, AVERAGE, COUNT**, and

**COUNTA**functions, in the simplest way possible, so you can quickly and accurately process data. We will also use calculations necessary for us and whose results we want to see in the table, even though these calculations are not part of

**Microsoft Excel**functions.

**Built-in**

**Microsoft Excel**functions and the calculations you or your company might need often go hand in hand in practice. Therefore, be sure to follow along and do everything we do in this exceptional tutorial.

Regardless of the topic we're covering, we always provide more than what you might need to learn at any given moment. For example, in the last lesson, we couldn't cover all the formatting options

**Microsoft Excel**offers. However, in this lesson, we continue with the formatting you currently need to learn. By the end of the entire tutorial, you'll be so practiced that you'll be creating workbooks and worksheets like a professional. In this third lesson, we focus on naming data groups, creating formulas for calculating different values, summarizing data that meets certain criteria, and using array formulas. This might all seem too complicated, but when you do it practically, you'll realize how simple and useful these things are.

**How to copy a formatted table to a new workbook?**

**Let's assume you work at a private programming school where you have**

**10**students,

**5**instructors, and

**5**programming languages. Each student learns a specific programming language, takes

**3**tests, and their average score determines whether they have completed their course. Your school director wants the following information from you:

**A list of instructors, their email addresses, the programming languages they teach, the number of students they have taught, the course fees, and how much each instructor has earned for the school.**

**Microsoft Excel**document to our new

**Microsoft Excel**document. How do we do this?

Simply, open **Microsoft Excel**, find the
**microsoft_excel_tutorial** folder, and open the **02. Correct Data Entry into
Tables.xlsx** document. I hope you haven't forgotten the password, as the
document is **password-protected**. Copy the entire table by clicking on cell **B2**,
holding down the **SHIFT** key, and clicking on cell **I13**. While the table is
selected, press the **CTRL** and **C** keys or click the **Copy** button in the toolbar.
After copying, do not close the document. Instead, continue by clicking on
**File**, then **New,** and then **Blank workbook**. Click on cell **B2** in the new **Microsoft
Excel** document, and while cell **B2** is selected, click on the **Paste drop-down**
menu and choose the **Keep Source Formatting** button. The result will look like
the following image.

**Copied formatted table of students in a new document**

**Microsoft Excel**document and name it

**03. Microsoft Excel Functions and Calculations**. You can now close the

**Microsoft Excel**document from which you copied the table. Regarding the student table, expand the columns so that the names and contents of the columns are fully visible. According to our tasks, we need to expand this table with the missing data; however, we cannot expand the table indefinitely to the right because we want the table to look neat when printed. This sometimes means repeating data. To minimize this, we will only repeat the

**Student ID**column. Therefore, create the following columns from cells

**F15**to

**H15**:

Student ID | Average
| Completed the Course

Fill the first column with numbers from **1 **to **10**. Do this
in such a way that after entering the numbers** 1** and **2**, the remaining cells
automatically fill in. You do this by selecting the first two cells while
holding down the **SHIFT** key, then release the key, and with the mouse in the
shape of a black cross, drag the selection down to cell **F25**. The selected cells
will automatically fill with numbers, and it will look as if you have typed the
numbers **1** to **10** in that column. Then, in cell **G16**, type the assignment sign **=**
and start typing aver. You will be prompted to select one of the functions with
a similar name. Click on **AVERAGE**, and you will get the following:

**=AVERAGE(**

If you see this, scroll your worksheet up and click on
cell **G4** and then on **I4** to select all three test cells from **G4** to **I4**.

**=AVERAGE(G4:I4**

You don't even need to close the parenthesis; just press
**Enter**, and in cell **G16**, you will get the result **66**, which is the average of **68,
77,** and **53**, the first student's scores. Repeat the same action for the second
student, then automatically calculate and fill the cells for the entire column.
Since some results are displayed as **non-integers **(i.e., with decimal points),
select the entire group of numbers representing the Average column. In the
toolbar, under the Number panel, click on the **Number Format** **ComboBox** and select
**More Number Formats.... **Then, in the** Category ListBox,** click on **Number,** set
**Decimal** places to** 0**, and click **OK**. Now, your entire column will display the
results as whole numbers without decimal points. If you have succeeded, move on
to the next column and cell **H16**. Type the following formula:

**=IF(G16>=80, "TRUE")**

This is our custom formula, indicating that if a student
has an average score of **80 **or higher, it will display **TRUE**, meaning the student
has passed the course. Otherwise, it will automatically display **FALSE** if the
condition is not met. Do the same for the second student, then let the entire
column autofill. Next, we will expand our table with two more cells. In cell**
H26**, type **"Count".** In cell **I26**, type the following formula:

**=COUNTA(**

Then select only the **TRUE** results in the Completed the
Course column and press **Enter**. You will get the result **6**. To see how to format
the table aesthetically and how it should look, refer to the following image.

**Extended students table that calculates the average and determines which student passed the course**

With all this knowledge you have learned in this third lesson, you can easily apply it to the next task, specially designed for you and our third lesson. Create a table of lecturers and do everything necessary to make everything look like the following picture. You have all the necessary data in the table, don't just copy the table, but do the formulas. That way, if you change some data, the result will also change.

## No comments:

## Post a Comment