Looking for a Tutor Near You?

Post Learning Requirement »
x

Choose Country Code

x

Direction

x

Ask a Question

x

x
x
For better view click here
x
Hire a Tutor
DISCOVER

Basic Excel Formulas

Loading...

Published in: Accounts | Advanced Excel | MS Office
147 Views

This File contains the Basic Excel Formulas a Beginner should use.

Ganesh S / Delhi

10 years of teaching experience

Qualification: Certification in Excel Advanced

Teaches: ERP Training, Advanced Excel, Basic Computer, Computer for official job, MS Office, School Level Computer, Accent Training, Business Analytics, Project Management, Six Sigma Training, Business English, Corporate Communication, Effective Communication, IELTS, Public Speaking, Spoken English, Video Editing, Drawing / Painting, English, Tamil

Contact this Tutor
  1. EXCEL BASIC FORMULAS Building Strong Foundation and Sustainable Growth Boost your Career in Excel
  2. Introduction Welcome to a comprehensive guide on the essential functions of Excel! Whether you are a beginner or someone looking to brush up on fundamental Excel skills, this book will serve as your go-to resource for mastering the core functions that power this versatile tool. Each function has been carefully explained with detailed step-by-step instructions, accompanied by visuals to make learning intuitive and straightforward. As someone with over a decade of experience working with Excel, I understand the challenges that come with learning and using its many features. This is why I've not only simplified the explanations but have also made sure to include practical examples and photographs to help you visualize how each function works in real-world scenarios. My journey with Excel has allowed me to support thousands of users across platforms like Facebook, Quora, Microsoft Communities, and Reddit, and now through this book, I hope to extend that support to you. Let this be your starting point to building confidence in using Excel, whether for personal use, business, or problem-solving.
  3. Disclaimer! Although the publisher and the author have made every effort to ensure that the information in this book was correct at press time and while this publication is designed to provide accurate information in regard to the subject matter covered, the publisher and the author assume no responsibility for errors, inaccuracies, omissions, or any other inconsistencies herein and hereby disclaim any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from negligence, accident, or any other cause. Meet the Author As an Excel expert with over 15 years of hands-on experience, I have dedicated my career to mastering and simplifying the intricacies of Excel. Since 2020, I have been running a You Tube channel where I share tutorials, tips, and solutions to help users navigate and optimize their Excel skills. My passion extends beyond the channel, as I actively participate in solving Excel- related problems on social platforms such as Facebook, Quora, Microsoft Communities, and Reddit. I also enjoy assisting friends and colleagues with their specific Excel needs, ensuring they can tackle any challenge efficiently. My journey in Excel has been driven by a desire to make the complexities of data management and analysis more accessible to everyone. Whether its helping beginners understand basic formulas or guiding professionals through advanced techniques, my goal is to empower users with the knowledge and confidence to excel in Excel. 31 P a ge
  4. Introduction to Excel Formula An Excel formula is an expression that performs calculations or operations on data within a cell or range of cells. It typically starts with an equal sign (2) followed by functions, cell references, operators, and constants. Formulas are essential for automating tasks, analyzing data, and creating dynamic reports. Here's a breakdown of the key aspects of Excel formulas: What are Excel formulas? Excel formulas are expressions that perform calculations or operations on data within cells. They are used to automate tasks, analyze data, and create dynamic reports. Why are they made? Excel formulas are made to simplify data manipulation and analysis. They allow you to perform complex calculations without having to do them manually. How are they helpful? Excel formulas can be used to: Perform calculations (addition, subtraction, multiplication, division, etc.) Create dynamic reports Analyze data Automate tasks • Make decisions based on data When can you use them to ease your life? You can use Excel formulas in various scenarios, such as: Financial analysis: Calculating budgets, profit margins, and returns on investment. Data analysis: Summarizing and analyzing data sets. Creating reports: Generating reports with dynamic data. Automating tasks: Performing repetitive calculations or data manipulation tasks automatically. P a ge
  5. Problem solving: Using formulas to solve mathematical or logical problems. Overall, Excel formulas are a powerful tool that can help you save time, improve accuracy, and gain valuable insights from your data. ll. IV. V. VI. Vill. 'X. X. Xlll. XIV. Basic Functions SUM(range)' — Adds all numbers in a range. AVERAGE(range)' — Calculates the average of numbers. COUNT(range)' — Counts the number of cells that contain numbers. COUNTA(range)' — Counts all nonempty cells. MAX(range)' — Returns the largest number in a range. MIN(range)' — Returns the smallest number in a range. SUBTOTAL(function, range)' — Performs various functions on filtered data (SUM, AVERAGE, etc.). PRODUCT(range)' — Multiplies all numbers in a range. ABS(number)' — Returns the absolute value of a number. SQRT(number)' — Returns the square root of a number. ROUND(number, num_digits)' — Rounds a number to a specified number of digits. ROUNDUP(number, num_digits)' — Rounds a number up to a specified number of digits. ROUNDDOWN(number, num_digits)' — Rounds a number down to a specified number of digits. MOD(number, divisor)' — Returns the remainder after division. On the Next section, we shall see the detailed information on how to use the functions listed above. 51 P a ge
  6. SUM(range)' — Adds all numbers in a range Purpose: The SUM function adds values. You can add individual values, cell references or ranges or a mix of all three. For example: Adds the values in cells A2:10. Syntax. —SUM(number1 , [number2], ...) numberl , number2, ...: These can be individual numbers, cell references, or a range of cells. We can have multiple numbers to calculate the SUM of this answers using SUM function. Example: If you have numbers in cells Al through A5 and you want to sum them up: -SUM(AI :A5) If Al-10, A2-20, A3-30, A4-40, A5-50, then the result will be '150'. File Paste clipboard €16 Home Insert Page Layout v 12 Font Formulas Data Review Alignment View Entertai Entering the Formula Housing or red phone P a ge 00 00 s22 00 -SUM( s3,gs SUM(numberI, [number21, m)
  7. File Paste Clipboard DI 6 Home I nsert Insert Calibri Page Layout Font moo Page Layout Font Forrnulas Data Review Alignment Housing Mortgage Or File H ome paste Clipboard E17 Housing Category cr rent p hone Electricity 71 P a ge -SUM(CIG.016 Ente VidedD SUM(number1, [number21, Formulas Data Review Alignment En TOTAL $109000 Adding the cell References Displaying the Result $100000 $90 00 $396
  8. ll. AVERAGE(range)' — Calculates the average of numbers Purpose: Returns the average (arithmetic mean) of the arguments. For example, if the range Al :A20 contains numbers, the formula —AVERAGE(AI :A20) returns the average of those numbers. Syntax: —AVERAGE(number1 , [number2], ...) numberl , number2, ...: Can be individual numbers, cell references, or a range of cells. Example . To find the average of the values in cells Al to A5: Svntax -AVERAGE(AI :A5) If Al-10, A2-20, A3-30, A4-40, A5-50, the result will be '30', File Home Insert Page Layout Formulas -AVERAGE Data Paste Clipboard SUM Housing Cat Elætricity and 81 P a ge Review Alignment AVERAGEA AVERAGEIF AVERAGEIFS DAVERAGE Entering the Formula $1,000.00 $54 co suco ed Cmt tllocuoo $1cooo $5600 00 -$46 00 -$12 co
  9. File Home Cut CD Copy Pa ste Insert Page Layout Formulas Data Font Format Patnter Clipboard E16 Housing Mortgage or rent Estimt ed CMt soooo Ma CMt File Home Insert Page Layout Calibri Formulas Data v 12 Font Ma Cost Review rga Cost AVERAGE(numb Review Ave rga Cost Adding the cell References Pisplaying the Result CD copy Ste Format Painter Clipboard F17 Housing Mortaar or red Phone Electricity 91 P a ge $1,130.00 310000 $54 oc 00 -$46C0 412m
  10. Ill. COUNT(range)' — Counts the number of cells that contain numbers Purpose: The COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. Syntax: —COUNT(value1, [value2], ...) valuel , value2, ...: Can be cell references, ranges, or arrays of numbers. Only numbers are counted, not text or blank cells. Example : To count the number of cells containing numbers in Al to A5: -COUNT(AI :A5) If Al —10, A2—20, A3=30, A5—50, the result will be '4' , since only four cells contain numbers. Mortgage or rent Electricitp Gas and Waste r v al Other No. Of •terns : 101 Page n 100.00 $100 oo $56.00 $28.00 $8.00 $34.00 $10 oo Hello = COUNT( COUNT (value I, [value-2], . Entering the Formula
  11. category Mortgage or rent Electricity Water ard sewer Waste removal Supp i Other NO. Of Rems : Mortgage Or rent Water sower Cable Waste r or repairs Supp I i Other NO. Of : 1 | Page Estimated Cost $100. oo $28 oo $8.00 $34.00 $10.00 $0.00 $0.00 Ma Cost Ave rea $1_016.67 Adding the cell References I COUNT(value1, [value, I ed Cost n 100.00 $100 oo Hell o 9.00 Market cost $1.01 Displaying the Result
  12. IV. COUNTA(range)' — Counts all nonempty cells Purpose: COUNTA' counts the number of nonempty cells in a range. This includes both numbers and text values. Syntax: —COUNTA(value1 , [value2], ...) valuel , value2, ...: Can be cell references or ranges. It counts numbers, text, errors, and logical values (TRUE, FALSE), but excludes blank cells. Example . To count all nonempty cells in Al to A5: -COUNTA(AI :A5) If Al —10, A2—20, A3=30, and A5 is empty, the result will be '4' because four cells contain values. Catego ry Mortgage or rent Electricity Water and sewer W aste removal Mai or repairs Suppl i es Other NO. Of Items : 12 I Page Estimated Cost $1,100.00 $100.00 $28,00 $34.00 $10.00 Hello $0 00 $0 00 -couNTAd Market Cost Averga $1,0887 Entering the Formula N VALU I COUNTA(value1, [value2J, ..J
  13. Category Mortgage or rent Electricity Gas Water and sewer Cable Waste removal Maintenance or repairs Supplies Other No. Of Items : Category Mortgage or rent Electricity Gas Water and sev•ver Cabl e Waste removal Maintenance or repairs Supplies CRher No. Of Items : 131 Page Actual Cost Actual Cost Estimated Cost $110.00 $10000 $28.00 $34.00 $10.00 1+110 $0.00 $0.00 Market Cost Ave rea Adding the cem References ttVALU j COUNTA(value1, [value2], Estimated Cost $tmoo $100.00 $2800 $34.00 $1000 Hello $0.00 $0.00 9.00 Market Cost Averg $111 Displaying the Result
  14. V. MAX(range)' — Returns the largest value in a set of values. Purpose: The 'MAX' function returns the largest numeric value in a range of cells. Syntax: —MAX(number1 , [number2], ...) numberl , number2, ...: Can be numbers, cell references, or a range of cells. Example . To find the maximum value in Al to A5: -MAX(AI :A5) If Al -10, A2-20, A3-30, A4-40, A5-50, the result will be '50' , since that is the largest number. Category Mortgage or rent Phone Electricity Gas Water and sewer Cable Waste removal Maintenance or repairs Supplies Other No. Of Items : 141 Page Actual Cost Estimated Cost $1,100 00 $100.00 $200.00 $28.00 $548.00 $34.00 $10 oo Alpha $000 -MAX MAX Market Cost Averga Entering the Formula #VAL Returns the largest
  15. Category Mortgage or rent Electricity Gas W ater ard sewer Cable Waste removal Maintenance or repairs Suppl es Other No. Of Items : Mortgage or rent Electricity W Sewer Cable W a Ste Maintenance or repairs Supplies other NO. Of Items : 15 I Page Actual Cost Estimated Cost $1,100.00 smoo 320000 32800 $34.00 31000 Alpha smoo woo Market Cost Averga Cost 67 Adding the Gel/ References MAX(number1, (number2], ..) Estimt ed 00 Muket rga w u ALLIE' Displaying the Result Subtot
  16. VI. MIN(range)' — Returns the smallest number in a range Purpose: The 'MIN' function returns the smallest numeric value in a range of cells. Syntax: —MIN(number1 , [number2], ...) numberl , number2, ...: Can be numbers, cell references, or a range of cells. Example . To find the minimum value in Al to A5: -MIN(AI :A5) If Al -10, A2-20, A3-30, A4-40, A5-50, the result will be '10 , number. Category Mortgage or rent Phone Electricity Gas Water and sewer Cable Waste removal Maintenance or repairs Supplies Other NO. Of Items : 161 Page Actual Cost Estirnated Cost $1,100.00 $100 oo $200.00 $28.00 $548.00 $34.00 $10 oo Alpha $0.00 $0.00 -MIN since that is the smallest Market Cost Entering e Formula
  17. Category Mortgage or rent Electricity G as Water and sewer Cabl e Waste removal Maintenance or repairs Supplies NO. Of Items : Catego E e ctrici Cable 171 Page Actual Cost Estimated Cost $1,100.00 $100.00 $200.00 $28.00 $548.00 $34.00 $10.00 Al pha $0 00 $0.00 -MINtDIG:D2G $1.100.00 s 100.00 $200.00 $548.00 $10.00 o. 00 Market Cost Adding the Gel/ References Displaying the Result
  18. SUBTOTAL(function_num, range)' — Performs various functions on filtered data (SUM, AVERAGE, etc.) Purpose: SUBTOTAL' performs calculations (like SUM, AVERAGE, COUNT, MAX, MIN) on data, even if some rows are filtered or hidden. It ignores hidden rows and applies the specified function to visible data only. num, refl , [ref2], ...) function example: num: A number that represents the type of calculation you want to perform. For 1 for ' AVERAGE '9" for SUM '3' for 'COUNTA '4' for MAX' '5' for MIN refl , ref2, The range of cells to apply the function to. Example . To find the sum of the visible cells in Al to A5: -SUBTOTAL(9, Al :A5) If rows containing some values are filtered out, the 'SUBTOTAL' function will only sum the visible cells. Other commonly used SUBTOTAL' functions: '-SUBTOTAL(I, Al :A5)' for AVERAGE . '-SUBTOTAL(3, Al :A5)' for 'COUNTA . '-SUBTOTAL(4, Al :A5)' for MA)C. '-SUBTOTAL(5, Al :A5)' for MIN . 181 Page
  19. cost Estimated cost Market cost repairs No. Of E leerici'•,• Nm Of : 19 I Page Ave Cost $1 LIB 67 Esoo VA 33 $14E7 AL PHA $2267 is 67 118887 -SLR* TOTALt ref I, _ vsa3 t22S7 reft. Entering the Formula Adding the cell References
  20. Esti ed CMt Ma rket or rent Electricity Water and Cable or repairs Supplies NO. Of Items : 201 Page CMt saaco $23.33 ALPHA JACK $2267 $6.67 $166.67 163.5 Displaying the Result
  21. Vill. PRODUCT(range)' — Multiplies all numbers in a range Purpose: Multiplies all the numbers in a specified range or array. Syntax: PRODUCT(number1, [number2], . y •numberl, number2, These are the numbers or ranges of numbers you want to multiply. Usage Example : If you have numbers in cells Al through A3 as follows: Al -2, A2-3,A3-4 You can use the formula '—PRODUCT(AI :A3)' to get the result of '2 3 4 24'. Notes: The 'PRODUCT' function is particularly useful when multiplying large ranges of numbers, as it saves time compared to writing out multiple multiplication operations like '—Al A2 A3' Estimt ed Ma rket AWrga -pn0Dt.JCTIl PRODUCT(number1, [number Entering the Formula c. repa No. Of 21 | Page
  22. CMt Adding the cell References PRODUCT(number1, (number2 Estimated Cost Mortgage or rent Electricity 3 ewer Cable Waste removal or repairs S cpplies other No. Of Items : 22 I Page Muket Cost PRC)DUCT Ave Cost Displaying the Result
  23. 'X. ABS(number)' — Returns the absolute value of a number Purpose: Returns the absolute value of a number, which means it removes any negative sign, leaving the magnitude of the number. Syntax: ABS(number)' number'. This is the number or cell reference whose absolute value you want to calculate. Example : If cell Al contains '10', the formula '—ABS(AI)' will return '10'. Notes: This is useful for calculations where you need only the magnitude of a number, without considering whether it's negative or positive. Category Mortgage or rent Electricity Water and sewer Cable Maintenance or repairs Supplies Other Category Mortgage or rent Electricity Gas W'ater and sewer r e moval Maintenance or repairs Supplies Other 23 | Page Actua I Cost Actua I Cost Estimated Cost Ma rkot Cost syso_oo -$156.00 -$6.00 -$54000 $250.00 $325.00 $227.00 Avorga Cost AB S (n um ber) Estimated Cost Market Cost Averga Cost sgso_oo .SIS6.oo .ss40.oo S32s_oo 4227.00 8100.00 Entering the Formula Adding the cell References
  24. Categ ory Mortgage or rent El ectricity Water and sewer cable Waste removal Maintenance or repairs Supplies Other 241 Page Actua I Cost Estimated Cost Ma r ket Cost $950.00 _$46.00 -SIS6_oo -S6_oo -$540 0 $250.00 $326.00 -$227 _ oo -Sloo.oo Averga Cost S4600 Displaying the Result
  25. X. SQRT(number)' — Returns the square root of a number Purpose: Returns the square root of a number. Syntax: 'SQRT(number)' number'. This is the number for which you want to calculate the square root. Example: If you have '16' in cell Al, the formula '—SQRT(AI)' will return because the square root of 16 is 4. Notes: If the number is negative, Excel will return a '#NIJM!' error, since the square root of a negative number is not a real number (in standard arithmetic). Catego Mortgage or rent P hone Electricity Gas Water and sewer Cable Waste removal Maintenance or repa.rs Sup*es 0th Category Mongage or rent Phone Water and sewer Cable Was t removal Maintenance or repairs Supplies Other NO. Of Items 25 I Page Actua Cost Actual Cost Estima ted C ost Estimated Cost Ma r ket Cost 49.00 1600 Market Cost 81.00 16.00 Averga C -SORT SQRTPI I MSQRT Avorga Cost 61 Entering the Formula Adding the cell References
  26. Actual C '"t Est Mortgage or rent Phone El tric ity Water and sewer Cable Waste removal Maintenance or repairs Supplies 26 | Page Ma rket Cost 81.00 49.00 16.00 Ave Cost Displaying the Result
  27. X'. ROUND(number, num digits)' — Rounds a number to a specified number of digits Purpose: Rounds a number to the nearest specified number of decimal places or digits. Syntax: ROUND(number, num digits)' number'. The number you want to round. •num_digits' : The number of digits to which you want to round the number. Positive values round to the specified number of decimal places. Zero rounds to the nearest whole number. Negative values round to the left of the decimal point (to tens, hundreds, etc.). Example : If cell Al contains '5.678" , the formula '—ROUND(AI, 2)' will return '5.68' , rounding to two decimal places. If you use '—ROUND(AI , O)' , it will return , rounding to the nearest whole number. Notes: The ROUND function is useful in situations where precise control over decimal places is needed, such as in financial calculations. Projected 27 | Page Actual Difference $626,47887 $321.33 S2_36 S152S $2,13586 $641,607.51 ROUND -ROUND ROUND(number, num_digits) Ent the F inq mula
  28. proj ected Projected 28 | Page Difference 13654 6254788635 321326 1525a Actual Difference $13,65465 $25,47887 ROUND ROUND $625,480.00 $321.33 S300.oo S2_36 S240 $15.25 $15.25 $2,13586 $2,000.00 Adding the cell References Qisplaying the Result
  29. Xll. ROUNDUP(number, num_digits)' — Rounds a number up to a specified number of digits Purpose: Rounds a number upward to a specified number of decimal places, regardless of whether the next digit is below or above 5. Syntax: ROUNDUP(number, num digits)' number'. The number you want to round up. num_digits' : The number of digits to round to. Positive values round up to the specified number of decimal places. Zero rounds up to the nearest whole number. Negative values round up to the left of the decimal point. Example : If cell Al contains '5.678', the formula '—ROUNDUP(AI , 2)' will return '5.68' If you use '-ROUNDUP(AI, or, it will return Notes: This is different from 'ROUND' , as it always rounds up. Even if the digit after the rounding point is less than 5, it will still round up. Actual Difference S13.6S4ss $625.47837 S321 .33 S2_36 S' 5_25 ROUND nterinq Formula 29 I Page
  30. Actual cost 13654 6264783695 321026 ,2356 }15254 21358599 Diffe re S13.65465 %25.47807 $321.33 S2 36 $1525 $2.135_56 ROUND ROUND $13,70000 00 $15.26 S3,ooooo Adding the cell References Displaying the Result p rojected cost 301 Page Actual
  31. Xlll. ROUNDDOWN(number, num digits)' — Rounds a number down to a specified number of digits Purpose: Rounds a number downward to a specified number of decimal places, regardless of whether the next digit is above or below 5. Syntax: ROUNDDOWN(number, num digits)' number'. The number you want to round down. 'num_digits' : The number of digits to round to. Positive values round down to the specified number of decimal places. Zero rounds down to the nearest whole number. Negative values round down to the left of the decimal point. Example : If cell Al contains '5.678', the formula '—ROUNDDOWN(AI, 2)' will return '5.67'. If you use '-ROUNDDOWN(AI , or, it will return '5' Notes: Like ROUNDUP , this function does not follow standard rounding rules. It always rounds the number down. Projected 31 | Page Actual Difference $32133 S2_36 $1525 2,135.56 ROUND ROUNDDOWN(number, num_digits) Entering the Formula
  32. tual Difference 13654 6484 6254788696 321 _326 2856 15254 21356599 Difference S625,478B7 $32183 $236 S15_25 ROUND ROUND $13,600.00 S300_oo S2_30 $15.25 $2,000.00 Adding the cell References Displaying the Result Projected 32 | Page Actual
  33. XIV. MOD(number, divisor)' — Returns the remainder after division Purpose: Returns the remainder when one number is divided by another. Syntax: 'MOD(number, divisor)' number'. The number to be divided. divisor'. The number to divide by. Example : If you want to divide 10 by 3, the formula '—MOD(IO, 3)' will return since 10 divided by 3 equals 3 with a remainder of 1. Notes: The MOD function is useful for identifying whether a number is divisible by another number. For instance, if 'MOD(number, 2)' returns O, the number is even. Projected : ategory Jersonal ;tudent :redit card :redit card :redit card )ther ;ubtotal Subtctal 33 | Page Actual $9.00 $10.00 Sli.oc .00 .00 Difference $64 00 $49.00 $36.00 $25.00 $16.00 I MOD(number, divisor) Entering the Formula Adding the References :EQJ3$134)
  34. Category Personal Student Credit card Credit card Credit card Other Subtotal Projected cost Actual $100 $900 S9_oo $11 no $110 Sli_oo Difference $8100 S6400 S49_oo $36 no $2500 S16_oo MOD 4 3 3 5 Displaying the Result In this document, we have focused solely on the basic functions used in Excel. Based on user feedback, we will incorporate additional formulas tailored to their specific categories in future sections. Formulas related to the Excel sections will be developed. A. Financial B. Logical C. Text D. Date & time E. Lookup & Reference F. Math and Trigonometry G. Statistical H. Engineering . Cube J. Information K. Compatibility L. Web, etc. Advanced Excel shall cover advanced techniques and approaching functions and fabricating formulas specific to the needs of the users. 34 | Page

Need a Tutor or Coaching Class?

Post an enquiry and get instant responses from qualified and experienced tutors.

Post Requirement

Related Study Notes

Upload Study Notes

If you have your own Study Notes which you think can benefit others, please upload on LearnPick. For each approved Study Note you will get 100 Credit Points and 100 Activity Score which will increase your profile visibility.

Upload Now
Home > Study Notes > Basic Excel Formulas