Basic Excel Functions Everybody Should Know

-

Microsoft Excel logo on a green background 

 Microsoft Excel contains many functions that allow you to perform tasks without a calculator or extra work. But if you’re unfamiliar with formulas, it can feel intimidating. Here, we’ve listed 12 simple but useful Excel functions to get you started.

 Table of Contents

1. Add Numbers in Cells: SUM
2. Average Numbers in Cells: AVERAGE
3. Find the High or Low Value: MIN and MAX
4. Find the Middle Value: MEDIAN
5. Count Cells Containing Numbers: COUNT
6. Insert the Current Date and Time: NOW
7. Round to a Certain Number of Digits: ROUND
8. Truncate a Number by Removing the Fraction: TRUNC
9. Find the Product by Multiplying Cells: PRODUCT
10. Use the Reference Number of a Given Cell: COLUMN and ROW
11. Eliminate White Space: TRIM
12. Count the Number of Characters in a String: LEN

1. Add Numbers in Cells: SUM

One of the most basic things you can do with numbers is add them. Using the SUM function in Excel you can add numbers in cells.

The syntax is SUM(value1, value2,...) where value1 is required and value2 is optional. So for each argument, you can use a number, cell reference, or cell range.

 

For example, to add the numbers in cells A2 through A10, you would enter the following and press Enter:

=SUM(A2:A10)

You then get your result in the cell containing the formula.

SUM function in Excel

2. Average Numbers in Cells: AVERAGE

Averaging a group of numbers is another common mathematical function.

The syntax is the same for the AVERAGE function in Excel as with the SUM function, AVERAGE(value1, value2,...) with value1 required and value2 optional. You can enter cell references or ranges for the arguments.

To average the numbers in cells A2 through A10, you would enter the following formula and press Enter:

=AVERAGE(A2:A10)

You then get your average in the cell containing the formula.

AVERAGE function in Excel

RELATED: How to Calculate a Weighted Average in Excel

3. Find the High or Low Value: MIN and MAX

When you need to find the minimum or maximum value in a range of cells, you use the MIN and MAX functions.

The syntaxes for these functions are the same as the others, MIN(value1, value2,...) and MAX(value1, value2,...) with value1 required and value2 optional.

To find the minimum, lowest value, in a group of cells, enter the following replacing the cell references with your own. Then, hit Enter:

=MIN(B2:B10)

And to find the maximum, highest value, use:

=MAX(B2:B10)

You’ll then see the smallest or largest value in the cell with the formula.

MIN and MAX functions in Excel

4. Find the Middle Value: MEDIAN

Instead of the minimum or maximum value, you may want the middle one.

As you may have guessed, the syntax is the same, MEDIAN(value1, value2,...) with the first argument required and the second optional.

For the middle value in a range of cells enter the following and press Enter:

=MEDIAN(A2:A10)

You’ll then see the middle number of your cell range.

MEDIAN function in Excel

5. Count Cells Containing Numbers: COUNT

Maybe you’d like to count how many cells in a range contain numbers. For this, you would use the COUNT function.

The syntax is the same as the above two functions, COUNT(value1, value2,...) with the first argument required and the second optional.

To count the number of cells that contain numbers in the range A1 through B10, you would enter the following and press Enter:

=COUNT(A1:B10)

You then get your count in the cell containing the formula.

COUNT function in Excel

RELATED: How to Count Cells in Microsoft Excel

6. Insert the Current Date and Time: NOW

If you’d like to display the current date and time whenever you open your spreadsheet, use the NOW function in Excel.

The syntax is NOW() because the function has no required arguments. You can, however, add or remove from the current date and time if you like.

To return the current date and time, enter the following and press Enter:

=NOW()

To return the date and time five days in the future from the current date and time, enter this formula and hit Enter:

=NOW()+5

And here’s how the results would look for each of the above formulas.

NOW function in Excel

7. Round to a Certain Number of Digits: ROUND

If you have decimal numbers in your sheet that you want to round up or down, use the ROUND function in Excel.

 

The syntax is ROUND(value1, digits) where both arguments are required. For value1 , use the number you want to round. For digits , use the number of decimal places to round the number.

For example, to round the number 2.25 up one decimal place, enter the following and press Enter:

=ROUND(2.25,1)

And you have your results. If you want to round down, simply use a negative number for the digits argument.

ROUND function in Excel

8. Truncate a Number by Removing the Fraction: TRUNC

Maybe you’d prefer to truncate a number rather than round it. Using the TRUNC function, you can remove the fraction from the number.

The syntax is TRUNC(value1, digits) with value1 required and digits optional. If you don’t enter the digits, the default value is zero.

So, to truncate the number 7.2 you would enter the following and press Enter:

=TRUNC(7.2)

The result of this formula would be the number seven.

TRUNC function in Excel

9. Find the Product by Multiplying Cells: PRODUCT

If you need to multiply several cells, using the PRODUCT function is more efficient than using the multiplication symbol (*) in a formula.

 

The syntax is PRODUCT(value1, value2,...) with value1 required and value2 optional. You can use value1 for the cell range and value2 for an additional cell range if needed.

To find the product of cells A2 through A10, you would enter the following and hit Enter:

=PRODUCT(A2:A10)

As you can see, this is much simpler than entering A2 * A3 * A4 , and so on.

PRODUCT function in Excel

RELATED: How to Add or Multiply Values with Paste Special in Microsoft Excel

10. Use the Reference Number of a Given Cell: COLUMN and ROW

With the COLUMN and ROW functions in Excel, you can return the position number of a cell. These functions are useful for entering a series of reference numbers in your sheet, or row numbers, for instance.

The syntax for each is COLUMN(reference) and ROW(reference) where the argument is not required. If you don’t enter an argument, the formula returns the reference for the cell containing the formula.

For example, if you enter the following formula into cell B2, the result would be 2 because B2 is in the second row.

=ROW()

But if you enter the following formula with an argument, you’ll receive the reference number for the cell.

=ROW(C5)

You can see here; the result is 5 because C5 is in the fifth row.

ROW function in Excel

11. Eliminate White Space: TRIM

Oftentimes you when you paste or import data, it contains extra spaces. The TRIM function eliminates white space.

The syntax is TRIM(reference) with the argument required for the cell reference containing the data.

To remove extra spaces from cell A1, you would enter the following and hit Enter:

=TRIM(A1)

You’ll then see the data in your referenced cell without the leading and trailing spaces.

TRIM function in Excel

12. Count the Number of Characters in a String: LEN

Maybe you need to find the number of characters in a string of text. Here, you’d use the LEN function in Excel.

The syntax is LEN(reference) with the argument required for the cell reference containing the text.

To find the number of characters in cell A1, enter the following formula and press Enter:

=LEN(A1)

The result is 25 because “Use the data from Finance” contains that number of characters and note that spaces are counted as characters.

LEN function in Excel 


 

 

Comments

Post a Comment

Popular Post

My Printer Not Connecting

-
Image
 If you are having an issue with installing a printer or getting other issues dealing with a printer or printer setup on the computer, try this troubleshooter.  If a step doesn't apply, skip to next step  1. Turn computer off  2. Turn printer off 3. Disconnect power to printer for 2 minutes  4. Turn printer back on  5. Wait one minute  6. Make sure all connections are good  7. If your printer has both parallel and USB connectors, only have one of the two connected and not both. The USB is faster. (most new systems do not have parallel connections)  8. Turn computer back on  9. Make sure the printer has ink  10. Is the printer networked? If so is it connected to the network via computer or router. If it is connected via a computer and the computer is off, the other systems will not see it.  11. Make sure the dependencies to the print spool is started  12. Check the manufacturer of the printer to see if there is a newer driv...
Read more

RESISTOR TESTING

-
Image
1- Visual inspection: Checks for missing or any burning on resistance.   2- Using Digital Multimeter: You can check the resistor resistance by selecting the ohm range in the digital multimeter, If the resistor is in circuit, you will generally have to remove the resistor so you are testing only the resistor value and not the other components in the circuit.   Connect test probes across the two points of the resistor as shown in the photo, polarity isn’t important, value of resistance shown is 9.86 kΩ. Note: All resistors have a specific tolerance rating. The tolerance rating is marked by a color band on the end of the resistor’s body, normally the band is gold indicating 5 % tolerance. If the tolerance band is silver, then the rating is 10 %. How to test SMD Resistors: Because of the small size of SMD resistors, there is often not room for the traditional color band code to be printed on them. Therefore, new resistor SMD codes were developed. The most commonly seen codes are t...
Read more

HARDWARE TECHNICIAN

-
Image
   As a Laptop technician, you must understand a basic rule of business, time is money.      Whether you are boss or work for someone else, the ability to identify and isolate a Laptop fault quickly and decisively is very important to the success of your business. It requires some common sense, and a little bit of focus. It also requires an understanding of the troubleshooting process, and reliable plan of action. Even though the number of Laptop configuration and setups are virtually unlimited, the methodology used to approach each repair is always about the same. This introduction is intended to isolate the concepts of basic troubleshooting and show you how to apply basic laptop repair steps that will help you narrow the problem down before you even take a screwdriver to the Laptop. By applying a constant technique. You can safe precious time from every laptop repair. The General Troubleshooting Steps Regardless of how your particular Laptop might be, a depend...
Read more

INTRODUCTION TO CHIP-LEVEL TECHNOLOGY

-
Image
Chapter 1: Introduction to Chip-Level Technology     1.1 What is Chip-Level Technology?   Explanation of chip-level technology: Delving into the microscopic world of electronic components and circuits. How chip-level technicians work at the heart of devices to diagnose and repair intricate issues.  1.2 Importance of Chip-Level Expertise   Highlighting the significance of chip-level technicians in repairing complex electronic devices. Examples of devices where chip-level expertise is crucial: smartphones, laptops, gaming consoles, and more Chapter 2: Basics of Electronic Components   2.1 Understanding Electronic Components Introduction to fundamental components: resistors, capacitors, diodes, transistors, and integrated circuits (ICs). How components combine to create circuits, allowing devices to function. 2.2 Role of Components in a Circuit Explanation of how each component contributes to the overall operatio...
Read more

IP Address: internet protocol

-
Image
   What is an IP address?     An IP (Internet Protocol) address is a unique numerical label assigned to each device connected to a computer network that uses the Internet Protocol for communication.     It serves two main functions: identifying the host or network interface and providing the location of the device in the network. Types of IP addresses:     IPv4: This is the most common type of IP address used today. It consists of four sets of numbers separated by periods (e.g., 192.168.0.1). IPv4 allows for approximately 4.3 billion unique addresses.     IPv6: With the increasing number of connected devices, IPv6 was introduced to address the depletion of IPv4 addresses. It uses eight sets of numbers separated by colons (e.g., 2001:0db8:85a3:0000:0000:8a2e:0370:7334). IPv6 provides a significantly larger address space. How IP addresses work:     When a device wants to communicate with a...
Read more

How to Hack WiFi Password: Guide to Crack Wi-Fi Network

-
What is Cybercrime? Cybercrime is defined as an unlawful action against any person using a computer, its systems, and its online or offline applications. It occurs when information technology is used to commit or cover an offense. However, the act is only considered Cybercrime if it is intentional and not accidental. In this tutorial, you will learn: What is Cybercrime? Example of Cybercrime Cybercrime Attack Types Cyber Crime Tools Example of Cybercrime Here, are some most commonly occurring Cybercrimes: The fraud did by manipulating computer network Unauthorized access to or modification of data or application Intellectual property theft that includes software piracy Industrial spying and access to or theft of computer materials Writing or spreading computer viruses or malware Digitally distributing child pornography Cybercrime Attack Types Cybercrime can attack in various ways. Here, is some most common cybercrime attack mode: Hacking: It is an act...
Read more
-
Image
j BIOS (Basic Input/Output System) is an essential firmware embedded in a computer's motherboard that initializes hardware components and enables the operating system to boot. Sometimes, updating or reprogramming the BIOS becomes necessary to fix compatibility issues, add new features, or enhance system performance. This comprehensive guide will walk you through the process of BIOS reprogramming, covering the necessary steps and precautions to ensure a successful update. Understand the Purpose of BIOS Reprogramming : Before proceeding with BIOS reprogramming, it's crucial to understand why you need to update or reprogram it. Common reasons include resolving hardware compatibility issues, improving system stability, fixing security vulnerabilities, enabling new features, or supporting updated hardware. Make sure to identify the specific need for reprogramming to avoid unnecessary updates that could potentially cause problems. Research and Gather Necessary Materials : Begin by re...
Read more

An Introduction to IT Chiplevel Repair: Unveiling the Inner Workings of Modern Devices

-
Image
  In the fast-paced world of technology, where devices are becoming more intricate and compact, the need for skilled professionals capable of delving into the core components of electronics has grown exponentially. Chiplevel repair and chiplevel engineering have emerged as crucial disciplines, allowing technicians to diagnose and mend devices at a microscopic level. In this article, we'll explore the fundamentals of chiplevel repair, its significance in the realm of IT, and how it contributes to the functioning of devices we rely on daily. What is Chiplevel Repair?   Chiplevel repair , often referred to as board-level repair , is a specialized field within electronics repair that focuses on identifying and resolving issues at the individual component level of a device's circuit board. Unlike traditional repair methods that involve simply replacing faulty parts, chiplevel repair involves diagnosing and fixing problems within integrated circuits, microchips, and other intricat...
Read more