The 15 Basic Excel Formulas Everyone Needs to Know

Learn how to add arithmetic, string, time series, and complex formulas in Microsoft Excel by NZAYISENGA Adrien What is an Excel Formula? Microsoft Excel is a popular tool for managing data and performing data analysis. It is used for generating analytical reports, business insights, and storing operational records. To perform simple calculations or analyses on data, we need Excel formulas. Even simple Excel formulas allow us to manipulate string, number, and date data fields. Furthermore, you can use if-else statements, find and replace, mathematics and trigonometry, finance, logical, and engineering formulas. Unlike programming languages, you will be writing the formula name and arguments. That’s it, nothing complex. You can also use Excel-assisted user interference to add formulas. The basic formulas in Excel are SUM(), MIN(), MAX(), AVERAGE(), COUNT(), POWER(), CEILING(), FLOOR(), CONCAT(), TRIM(), REPLACE(), SUBSTITUTE(), LEFT(), RIGHT(), MID(), UPPER(), LOWER(), PROPER(), NOW(), TODAY(), DATEDIF(), VLOOKUP(), HLOOKUP(), and IF(). See below for guidance on each of these. Why Are Excel Formulas Important? Excel formulas are essential for several reasons: Efficiency: They automate repetitive tasks, saving time and reducing manual errors. Data analysis: Excel's range of formulas enables sophisticated data analysis, crucial for informed decision-making. Accuracy: Formulas ensure consistent and accurate results, essential in fields like finance and accounting. Data manipulation: They allow for efficient sorting, filtering, and manipulation of large datasets. Accessibility: Excel provides a user-friendly platform, making complex data analysis accessible to non-technical users. Versatility: Widely used across various industries, proficiency in Excel formulas enhances employability and career advancement. Customization: Excel offers customizable formula options to meet specific data handling needs. In essence, Excel formulas are a foundational tool for effective data management, analysis, and decision-making. Advance Your Career with Excel Gain the skills to maximize Excel—no experience required. How to Use Excel Formulas Adding the Excel formula is relatively easy. It will come to you naturally if you are familiar with any business intelligence software. The most effective and fast way to use formulas is by adding them manually. In the example below, we are calculating the BMI (Body Mass Index) of the athletes shown in the table. BMI = weight (KG)/ (Height (m))2 Choose the cell for the resulting output. You can use the mouse to select the cell or use the arrow key to navigate. Type “=” in the cell. The equal sign will appear in the cell and formula bar. Type the address of the cell that we want to use for our calculation. In our case, it is E2 (weight/KG). Add divide sign “/” To convert height from centimeters to a meter, we will divide the D2 by 100. Take the squared “^2” of the height and press Enter. Note: To get the address of any cell, you need to look at the column name (A, B, C, … ) and combine it with a row number (1, 2, 3, …). For example, A2, B5, and C12 That’s it; we have successfully calculated the BMI of A Dijiang.

We can also add the Excel formula by using assisted GUI. It is simple. In the example below, we will be using GUI to add an IF formula to convert ‘M’ to ‘Male’ and ‘F’ to Female. Click on the fx button next to the formula bar. It will pop up in the window with the most used function. You can either search for the specific formula or select the formula by scrolling. In our case, we will be specifying the IF formula. Add the logic B2=’M” into the logical_test argument. Add “Male” in value_if_true argument and “Female” in value_if_false argument. The formula works similarly to the if else statement. If the logical_test statement is TRUE, the formula will return “Male” otherwise “Female.”

How to insert formulas in excel for an entire column We have learned to add the formula to a single row. Now, we will learn to apply the same formula to the entire column. There are multiple ways to add formulas: Dragging down the fill handle: when you select the cell, you will see the small green box at the bottom right. It is called a fill handle. Click and hold the fill handle and drag it down to the last row. It is commonly used to apply formulas to selected rows. Double click the fill handle: select the cell with the formula and double click the fill handle. Within seconds it will apply the formula to the entire column. Shortcut: select the cell with the formula and the empty cells below it. Press CTRL + D to apply the formula. Make sure you are not selecting anything above the formula cell. Copy-pasting: copy the cell with the formula (CTRL + C), select the empty rows in a column, and paste it (CTRL + V). Make sure you are not using a fill handle to select the rows. The visual representation below shows all the ways we can apply the formula to multiple