Sunday, July 28, 2024

Effective management of Microsoft Excel functions and calculations

We assume that you are already familiar with what Microsoft Excel is and why it is used; if not, please check here. 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 Microsoft Excel installed, check here, then go through the previous two lessons of this excellent 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

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 students, their email addresses, the programming languages they attended, their test scores, the average score, and whether the student has completed the course, i.e., if they have an average of more than 80 points.
  •  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.
Regardless of the calculations you want to create, you should first check if there are already existing data or tables created in any previous workbooks or worksheets, or at least some of them. For the rest, you should inquire around the school and check the official website. But definitely, you need to do all the calculations yourself. If you remember, in the last lesson, we created a table with students, their emails, courses, and the scores of all three tests. So, first, we want to copy that exact table, which we will just expand in our workbook. We also want to copy all the formatting of the same table. So, we want to copy the formatted table from one 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

Copied formatted table of students in a new document

If you have succeeded, save the new 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

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.

Table of lecturers, programming languages, price and earnings of the school

Table of lecturers, programming languages, price and earnings of the school

If you are not sure how to do it, watch the following video.


Microsoft Excel - 3. Functions and Calculations














 

No comments:

Post a Comment