LoginGet started

60 Formulas in Excel. How to use formulas in excel for beginners ?

DC HELPING・146 minutes read

Microsoft Excel has over 400 formulas, with 64 crucial ones explained in a video, covering functions like financial, text, date, and lookup. The IF function is important for logical operations, while using functions accurately requires understanding Function Arguments and Function Features in Excel.

Insights

  • Microsoft Excel offers over 400 formulas, with 64 key ones explained in a video, covering financial, logical, text, date, and time functions.
  • The Insert Function feature in Excel aids in searching for specific functions or formulas, while the Function Arguments dialog box helps in understanding and correctly applying functions.
  • The IF function in Excel is pivotal for logical operations, while the SUM function necessitates typing the equal sign before the function to function correctly.
  • Functions like PMT, IPMT, PF, and NPER in Excel are crucial for financial calculations, especially for determining loan installments weekly, monthly, quarterly, semi-annually, and annually.

Get key ideas from YouTube videos. It’s free

Recent questions

  • What are some essential Excel functions?

    Various functions include financial, logical, text, date, and time.

Related videos

Summary

00:00

Master Excel Formulas and Functions for Efficiency

  • Microsoft Excel has over 400 formulas, with 64 crucial ones explained in a video.
  • Functions in Excel include financial, logical, text, date and time, lookup, and more.
  • The Insert Function feature allows searching for specific functions or formulas.
  • The Function Arguments dialog box helps understand and use functions correctly.
  • The IF function in Excel is a crucial formula for logical operations.
  • The SUM function in Excel requires pressing the equal sign before typing the function.
  • Financial functions in Excel include PMT, IPMT, PF, and NPER for various calculations.
  • Calculating weekly, monthly, quarterly, semi-annual, and annual loan installments in Excel involves using the PMT function with specific arguments.
  • Formatting numbers in Excel, especially percentages, is essential for accurate calculations.
  • Understanding the division of principal and interest in loan installments is crucial, with PMT and IPMT functions helping in the calculation.

14:22

Excel Functions for Monthly Payment Calculations

  • To calculate the principal amount and first month's interest, use the PMT function with the rate divided by 12.
  • The period selection is crucial for determining the principal amount and interest for each month.
  • The total time is calculated by multiplying the income by 12.
  • The IPMT function helps in calculating the interest for the first month.
  • The balance decreases every month, leading to a reduction in interest.
  • To adjust the monthly payment to a desired amount, use the Goal Seek feature in Excel.
  • Future value calculations involve depositing a fixed amount monthly at a specified interest rate.
  • The IF function in Excel allows for setting conditions to determine bonuses based on salary levels.
  • Different bonus percentages are applied based on salary thresholds set in the IF function.
  • Closing the brackets correctly is essential for accurate calculations in Excel functions.

31:20

Automated Bonus Calculation with Excel Functions

  • Press enter to see the bonus calculated automatically based on the condition.
  • Bonuses are automatically paid out according to the condition.
  • The bonus is calculated automatically based on the condition.
  • The function to count bonuses is explained.
  • The IF function is used to determine bonuses based on conditions.
  • The AND function is used to match multiple conditions for bonus eligibility.
  • The IF function is used to determine bonus eligibility based on conditions.
  • The AND function is used to match multiple conditions for bonus eligibility.
  • The END function is used to ensure all conditions are met for bonus eligibility.
  • The IFERROR function is used to handle errors in calculations.

45:37

Excel Functions for Error, Average, Care, Code

  • The text discusses using functions like Error, Average, Care, Code, Proper, Lower, Upper, Text, Repeat, Find, Search, Mid, Substitute, Date, and Time in Excel.
  • It explains how to use the Error function to calculate errors and the Average function to find the average.
  • The Care function is used to determine the character of a specific code in Excel.
  • The Code function helps identify the code of a character in Excel.
  • The Proper function capitalizes the first letter of a text and converts the rest to lowercase.
  • The Lower function converts text to lowercase, while the Upper function converts it to uppercase.
  • The Text function calculates the number of characters in a text.
  • The Mid function extracts characters from the middle of a text based on specified start and end numbers.
  • The Substitute function replaces specific text within a cell with new text.
  • The Date function helps separate date components like day, month, and year in Excel.

01:01:28

Mastering Date and Time Functions in Excel

  • To determine the year, start by selecting the year, then the month, and if it's "A," choose "main."
  • If paying, select accordingly, ensuring brackets are closed; if not, the date will still be reduced.
  • The formula for the date should be saved in date format, ensuring clarity.
  • To obtain a serial number, press equal and type the serial number, closing the bracket to get the desired result.
  • By selecting the date and closing the bracket, the day can be extracted separately.
  • To format days like Sunday, Monday, etc., separate formatting is necessary, which can be done through the cell dialogue box.
  • The week number can be determined by using the formula, which will indicate the week of the year.
  • The "dated" formula in Google Sheets can calculate the total days, months, and years between two dates accurately.
  • By using the "dated" formula with specific arguments, one can calculate the total time elapsed from a given date, including years, months, and days.
  • To calculate the total time from a specific date of birth to the present day, the "dated" formula can be utilized effectively, providing the exact number of years, months, and days.

01:17:24

Excel Functions for Efficient Data Referencing

  • Lookup and reference functions are essential in Excel.
  • Functions like lookup, match, and address are used for referencing.
  • Providing a reference cell is crucial for accurate results.
  • The address formula helps in determining cell locations.
  • Absolute and relative referencing styles impact formula results.
  • The substitute formula can replace specific text within a cell.
  • Hyperlinks can be inserted to link to external files or documents.
  • The choose function assigns numbers to values based on selection.
  • Index numbers are assigned to values using the choose function.
  • Dragging and dropping formulas can automate data entry and calculations.

01:33:27

Excel Lookup Function: Finding Data Efficiently

  • The text discusses using a lookup function to find specific data in Excel.
  • It explains the process of using the lookup function to find values based on specific criteria.
  • The text emphasizes the importance of accurate spelling when using the lookup function.
  • It details the steps of selecting the lookup value and the table for the function.
  • The text highlights the significance of selecting the correct column index number for the desired data.
  • It mentions the option of choosing between an exact match or an approximate match in the lookup function.
  • The text demonstrates the process of freezing the selected data range for accurate results.
  • It explains the process of finding ID numbers using the lookup function.
  • The text provides guidance on handling errors and ensuring proper data selection for the lookup function.
  • It concludes by discussing the application of the lookup function in finding phone numbers based on specific names in a dataset.

01:47:14

Efficient Data Retrieval Techniques in Excel

  • Adding data to a black cell reduces the search time for finding specific information.
  • Softening a table involves adding money to specific cells.
  • The right side of a cell is indicated by the column index.
  • Column numbers correspond to the data being searched for, such as phone numbers.
  • Using zero signifies an exact match, while other numbers indicate approximate matches.
  • The C Lookup function helps find data vertically or horizontally.
  • Creating a drop-down list involves selecting data validation and entering desired values.
  • Combining C Lookup with the Match function allows for flexible column selection.
  • The Match function provides the position of a value within a range.
  • Using the Match function with V Lookup streamlines data retrieval and allows for easy changes in column selection.

02:02:31

Mastering Excel: Matching and Analyzing Data

  • The focus is on determining the value of the right side only.
  • The left column contains values that need to be matched with the right column.
  • The lookup value is crucial for finding the corresponding value.
  • The "choose" function or the "index plus match" formula can be used for this purpose.
  • The "V Lookup" formula is explained, involving selecting and controlling data.
  • The "C Lookup" formula is detailed, including the use of curly brackets and index numbers.
  • The "index" function is demonstrated, specifying row and column numbers.
  • The "match" function is utilized to find specific values within a table.
  • The process of using the "index plus match" function is explained for finding values on both sides.
  • The creation of a pivot table for data analysis is outlined, including selecting fields and items for analysis.

02:18:13

Excel Functions for Calculations and Operations

  • The text discusses using Excel functions to perform various calculations and operations.
  • It mentions using the "randbetween" formula to generate random numbers within a specified range.
  • Explains the "power" formula for calculating the result of raising a number to a specific power.
  • Describes using the "sqrt" formula to find the square root of a given number.
  • Introduces the "subtotal" formula for calculating totals based on specific conditions.
  • Discusses the "sumif" formula for summing values based on specified criteria.
  • Explains the "sumifs" formula for summing values based on multiple conditions.
  • Mentions the "product" formula for multiplying numbers together.
  • Describes the "average" function for calculating the average of a set of values.
  • Introduces the "max" and "min" functions for finding the maximum and minimum values in a set of data.

02:36:54

"Calculating, Counting, and Forecasting Data Values"

  • The lowest value is the smallest, determined by finding the smallest value.
  • Drag and drop to achieve average maximum and find minimum values.
  • Count formula calculates the count of items, useful for understanding data.
  • Count function counts only numerical values, not alphabetical ones.
  • Count A formula counts both numerical and alphabetical values.
  • Count If function allows for setting conditions to count specific data.
  • Forecast function predicts future values based on historical data.
  • Forecast function requires target date, values, and timeline to provide accurate predictions.
  • Forecast function can be used to create charts for visual representation of data.
Channel avatarChannel avatarChannel avatarChannel avatarChannel avatar

Try it yourself — It’s free.