\n| Formula<\/b><\/td>\n | Syntax<\/b><\/td>\n | Description<\/b><\/td>\n<\/tr>\n\n| LOOKUP<\/td>\n | LOOKUP(lookup_value, lookup_vector, [result_vector])<\/td>\n | Searches for a value in a range. Most users prefer VLOOKUP or HLOOKUP.<\/td>\n<\/tr>\n | \n| VLOOKUP<\/td>\n | =VLOOKUP(val,range,col,0)<\/td>\n | Looks up value vertically in a table<\/td>\n<\/tr>\n | \n| HLOOKUP<\/td>\n | =HLOOKUP(val,range,row,0)<\/td>\n | Looks up value horizontally<\/td>\n<\/tr>\n | \n| INDEX<\/td>\n | =INDEX(range,row,col)<\/td>\n | Returns value at given row\/column<\/td>\n<\/tr>\n | \n| MATCH<\/td>\n | =MATCH(val,range,0)<\/td>\n | Returns position of a value in a range<\/td>\n<\/tr>\n | \n| INDEX+MATCH<\/td>\n | =INDEX(B1:B10,MATCH(val,A1:A10,0))<\/td>\n | Smarter alternative to VLOOKUP<\/td>\n<\/tr>\n | \n| XLOOKUP<\/td>\n | =XLOOKUP(val,lookup,return)<\/td>\n | Excel 365 replaces VLOOKUP\/HLOOKUP<\/td>\n<\/tr>\n | \n| OFFSET<\/td>\n | =OFFSET(A1,2,3)<\/td>\n | Returns a cell offset from a reference<\/td>\n<\/tr>\n | \n| INDIRECT<\/td>\n | =INDIRECT(“A”&B1)<\/td>\n | Returns reference from a text string<\/td>\n<\/tr>\n | \n| CHOOSE<\/td>\n | =CHOOSE(2,”a”,”b”,”c”)<\/td>\n | Returns value from a list by index<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\nDate, time, and financial functions<\/h2>\nDate and time functions<\/h3>\nNeed to work with dates and times in your spreadsheet? Excel has a full set of built-in functions to help you calculate durations, automate schedules, and manage time-based data effortlessly.<\/p>\n \n\n\nFormula<\/strong><\/td>\nSyntax<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n\n| TODAY<\/td>\n | =TODAY()<\/td>\n | Returns today’s date<\/td>\n<\/tr>\n | \n| NOW<\/td>\n | =NOW()<\/td>\n | Returns current date and time<\/td>\n<\/tr>\n | \n| DATE<\/td>\n | =DATE(2026,6,17)<\/td>\n | Creates a date from year, month, day<\/td>\n<\/tr>\n | \n| YEAR<\/td>\n | =YEAR(A1)<\/td>\n | Extracts the year from a date<\/td>\n<\/tr>\n | \n| MONTH<\/td>\n | =MONTH(A1)<\/td>\n | Extracts the month from a date<\/td>\n<\/tr>\n | \n| DAY<\/td>\n | =DAY(A1)<\/td>\n | Extracts the day from a date<\/td>\n<\/tr>\n | \n| DATEIF<\/td>\n | =DATEIF(A1, B1,”D”)<\/td>\n | Calculates difference between dates<\/td>\n<\/tr>\n | \n| NETWORKDAYS<\/td>\n | -NETWORKDAYS(A1,B1)<\/td>\n | Counts working days between dates<\/td>\n<\/tr>\n | \n| WORKDAY<\/td>\n | =WORKDAY(A1, 10)<\/td>\n | Adds working days to a date<\/td>\n<\/tr>\n | \n| EDATE<\/td>\n | =EDATE(A1, 3)<\/td>\n | Adds months to a date<\/td>\n<\/tr>\n | \n| EMONTH<\/td>\n | =EMONTH(A1,0)<\/td>\n | Returns last day of a month<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nFinancial functions<\/h3>\nWhen it comes to finance, Excel is in a league of its own. Its powerful built-in financial functions are designed for real-world use, covering loan calculations, investment analysis, and depreciation with ease.<\/p>\n \n\n\nFormula<\/strong><\/td>\nSyntax\u00a0<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n\n| NPV<\/td>\n | =NPV(rate,value1,value2,…)<\/td>\n | Net present value of cash flows.<\/td>\n<\/tr>\n | \n| PMT<\/td>\n | =PMT(rate,nper,pv)<\/td>\n | Calculates loan payment amount.<\/td>\n<\/tr>\n | \n| DDB<\/td>\n | =DDB(cost_basis, salvage_cost, life, period, [factor])<\/td>\n | The DDB function calculates the depreciation of an asset with a double-declining balance.<\/td>\n<\/tr>\n | \n| FV<\/td>\n | =FV(rate,nper,pmt,pv)<\/td>\n | Future value of an investment.<\/td>\n<\/tr>\n | \n| PV<\/td>\n | =PV(rate,nper,pmt)<\/td>\n | Present value of an investment.<\/td>\n<\/tr>\n | \n| IRR<\/td>\n | =IRR(values)<\/td>\n | Internal rate of return.<\/td>\n<\/tr>\n | \n| IPMT<\/td>\n | =IPMT(interest_rate, payment_period, number_of_payments, present_value, [desired_cash_balance], [eob])<\/td>\n | Returns the interest portion of a loan payment.<\/td>\n<\/tr>\n | \n| INTRATE<\/td>\n | =INTRATE(settlement_date, maturity_date, invested, redemption, [basis])<\/td>\n | INTRATE returns the interest rate for a security that\u2019s been invested in; in other words, the rate of return.<\/td>\n<\/tr>\n | \n| SLN<\/td>\n | =SLN(cost, salvage_value, life)<\/td>\n | The SLN function will return depreciation on an asset for a single period, calculated on a straight line.<\/td>\n<\/tr>\n | \n| XNPV<\/td>\n | =XNPV(rate, value_range, dates)<\/td>\n | This function is similar to the NPV function, but it calculates it based on irregular values rather than regular values.<\/td>\n<\/tr>\n | \n| XIRR<\/td>\n | =XIRR(values, dates, [guess])<\/td>\n | This function is similar to the IRR function, but it calculates it based on irregular values rather than regular values.<\/td>\n<\/tr>\n | \n| YIELD<\/td>\n | =YIELD<\/td>\n | The YIELD function returns the amount that has been yielded on a security with periodic interest.<\/td>\n<\/tr>\n | \n| RATE<\/td>\n | =RATE(nper,pmt,pv)<\/td>\n | Calculates interest rate per period.<\/td>\n<\/tr>\n | \n| NPER<\/td>\n | =NPER(rate,pmt,pv)<\/td>\n | Number of periods for an investment.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nText Formulas<\/h2>\nWorking with text in Excel is easier than you think. These formulas let you split, join, clean, and transform text data in just a few clicks.<\/p>\n \n\n\nFormula<\/strong><\/td>\nSyntax\u00a0<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n\n| \n CONCATENATE<\/p>\n<\/td>\n | \n =CONCATENATE(A1,” “,B1)<\/p>\n<\/td>\n | \n Joins text strings together<\/p>\n<\/td>\n<\/tr>\n | \n| \n TEXTJOIN<\/p>\n<\/td>\n | \n =TEXTJOIN(“, “,TRUE,A1:A5)<\/p>\n<\/td>\n | \n Joins with delimiter, ignores blanks<\/p>\n<\/td>\n<\/tr>\n | \n| LEFT<\/td>\n | \n =LEFT(A1,5)<\/p>\n<\/td>\n | \n Extracts characters from the left<\/p>\n<\/td>\n<\/tr>\n | \n| RIGHT<\/td>\n | \n =RIGHT(A1,4)<\/p>\n<\/td>\n | \n Extracts characters from the right<\/p>\n<\/td>\n<\/tr>\n | \n| MID<\/td>\n | \n =MID(A1,3,5)<\/p>\n<\/td>\n | \n Extracts from middle of a string<\/p>\n<\/td>\n<\/tr>\n | \n| LEN<\/td>\n | \n =LEN(A1)<\/p>\n<\/td>\n | \n Returns length of a string<\/p>\n<\/td>\n<\/tr>\n | \n| TRIM<\/td>\n | \n =TRIM(A1)<\/p>\n<\/td>\n | \n Removes extra spaces<\/p>\n<\/td>\n<\/tr>\n | \n| UPPER<\/td>\n | \n =UPPER(A1)<\/p>\n<\/td>\n | \n Converts text to uppercase<\/p>\n<\/td>\n<\/tr>\n | \n| LOWER<\/td>\n | \n =LOWER(A1)<\/p>\n<\/td>\n | \n Converts text to lowercase<\/p>\n<\/td>\n<\/tr>\n | \n| PROPER<\/td>\n | \n =PROPER(A1)<\/p>\n<\/td>\n | \n Capitalizes first letter of each word<\/p>\n<\/td>\n<\/tr>\n | \n| FIND<\/td>\n | \n =FIND(“x”,A1)<\/p>\n<\/td>\n | \n Finds position of text (case-sensitive)<\/p>\n<\/td>\n<\/tr>\n | \n| SEARCH<\/td>\n | \n =SEARCH(“x”,A1)<\/p>\n<\/td>\n | \n Finds position (not case-sensitive)<\/p>\n<\/td>\n<\/tr>\n | \n| SUBSTITUTE<\/td>\n | \n =SUBSTITUTE(A1,”old”,”new”)<\/p>\n<\/td>\n | \n Replaces specific text<\/p>\n<\/td>\n<\/tr>\n | \n| REPLACE<\/td>\n | \n =REPLACE(A1,1,3,”new”)<\/p>\n<\/td>\n | \n Replaces by position<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Logical Formulas<\/h2>\nLogical formulas let Excel make decisions for you. They evaluate conditions and return different results based on whether something is true or false.<\/p>\n \n\n\n| Formula<\/b><\/td>\n | Syntax<\/b><\/td>\n | Description<\/b><\/td>\n<\/tr>\n\n| IF<\/td>\n | \n =IF(A1>10,”Yes”,”No”)<\/p>\n<\/td>\n | \n Returns value based on a condition<\/p>\n<\/td>\n<\/tr>\n | \n| IFS<\/td>\n | \n =IFS(A1>90,”A”,A1>80,”B”)<\/p>\n<\/td>\n | \n Tests multiple conditions in sequence<\/p>\n<\/td>\n<\/tr>\n | \n| AND<\/td>\n | \n =AND(A1>5,B1<10)<\/p>\n<\/td>\n | \n Returns TRUE if all conditions are true<\/p>\n<\/td>\n<\/tr>\n | \n| OR<\/td>\n | \n =OR(A1>5,B1<10)<\/p>\n<\/td>\n | \n Returns TRUE if any condition is true<\/p>\n<\/td>\n<\/tr>\n | \n| NOT<\/td>\n | \n =NOT(A1=0)<\/p>\n<\/td>\n | \n Reverses a logical value<\/p>\n<\/td>\n<\/tr>\n | \n| IFERROR<\/td>\n | \n =IFERROR(formula,”Error”)<\/p>\n<\/td>\n | \n Returns fallback if formula errors<\/p>\n<\/td>\n<\/tr>\n | \n| IFNA<\/td>\n | \n =IFNA(VLOOKUP(…), ‘N\/A’)<\/p>\n<\/td>\n | \n Returns fallback only for #N\/A errors<\/p>\n<\/td>\n<\/tr>\n | \n| Nested IF<\/td>\n | \n =IF(A1>90,”A”,IF(A1>80,”B”,”C”))<\/p>\n<\/td>\n | \n Multiple conditions in one formula<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Remaining Excel Formulas & Formatting Shortcuts<\/h2>\nSpeed up your workflow even further with these additional shortcuts. From formatting cells to navigating formulas, these are the ones worth adding to your daily routine.<\/p>\n \n\n\n| Shortcut<\/b><\/td>\n | Action<\/b><\/td>\n<\/tr>\n\n| Ctrl + G<\/td>\n | Go To dialog box<\/td>\n<\/tr>\n | \n| F5<\/td>\n | Open Go To dialog<\/td>\n<\/tr>\n | \n| Ctrl + Tab<\/td>\n | Switch between open workbooks<\/td>\n<\/tr>\n | \n| Ctrl + U<\/td>\n | Underline selected text<\/td>\n<\/tr>\n | \n| Ctrl + 1<\/td>\n | Open Format Cells dialog<\/td>\n<\/tr>\n | \n| Ctrl + Shift + $<\/td>\n | Apply Currency format<\/td>\n<\/tr>\n | \n| Ctrl + Shift + %<\/td>\n | Apply Percentage format<\/td>\n<\/tr>\n | \n| Ctrl + Shift + #<\/td>\n | Apply Date format<\/td>\n<\/tr>\n | \n| Alt + H + H<\/td>\n | Fill cell with color<\/td>\n<\/tr>\n | \n| Alt + H + B<\/td>\n | Apply border<\/td>\n<\/tr>\n | \n| = (Equals)<\/td>\n | Start a formula<\/td>\n<\/tr>\n | \n| F2<\/td>\n | Edit the active cell<\/td>\n<\/tr>\n | \n| F4<\/td>\n | Toggle absolute\/relative reference ($A$1)<\/td>\n<\/tr>\n | \n| Ctrl + `<\/td>\n | Show all formulas in worksheet<\/td>\n<\/tr>\n | \n| Ctrl + Shift + Enter<\/td>\n | Enter as Array formula<\/td>\n<\/tr>\n | \n| Alt + =<\/td>\n | Auto-sum selected range<\/td>\n<\/tr>\n | \n| F9<\/td>\n | Recalculate all formulas<\/td>\n<\/tr>\n | \n| Shift + F3<\/td>\n | Insert Function dialog<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nPro Tips to Use Excel Formulas Faster<\/b><\/h2>\n1. Use Named Ranges<\/strong><\/p>\nInstead of typing something like =SUM( B2:B50 ) , you can name that whole range \u201cSales\u201d and then just use =SUM( Sales ). When you do this, your formulas look way clearer right away, and they’re also simpler to handle, update, and maintain over time.<\/p>\n 2. Combine Formulas for Powerful Results<\/strong><\/p>\nExample: =IFERROR(VLOOKUP(A2,Sheet2!A:B,2,0),”Not Found”) – this looks up a value and handles errors gracefully in one step.<\/p>\n 3. Absolute vs. Relative Refere<\/strong><\/p>\n\n- Relative reference: A1: changes when copied<\/li>\n
- Absolute reference: $A$1: stays fixed when copied<\/li>\n
- Mixed reference: $A1 or A$1: fixes column or row only<\/li>\n
- Press F4 while editing to toggle between types<\/li>\n<\/ul>\n
4. Use Formula Auditing Tools<\/strong><\/p>\n\n \n- Formulas tab > Trace Precedents: see which cells feed into a formula<\/li>\n
- Formulas tab > Trace Dependents: see which cells depend on this one<\/li>\n
- Formulas tab > Evaluate Formula: step through a formula calculation.<\/li>\n<\/ul>\n<\/div>\n
Conclusion: Master Excel Formulas One Function at a Time<\/h2>\nLearning Excel formulas is one of the best investments you can make in your professional skills. Every Excel formulas in this guide saves you real time every single day.<\/p>\n Start applying what you’ve learned and share this guide with a colleague. Ready to go further? Send us an inquiry<\/a>, and let’s take your Excel skills to the next level together.<\/p>\n","protected":false},"excerpt":{"rendered":"Excel formulas really are the backbone of efficient data handling. Learn them, and you can save hours of manual work every week, turning a basic spreadsheet into a powerful decision-making tool. This guide covers 50+ essential Excel formulas and keyboard shortcuts, grouped by category, so you can grab what you need fast. Keep this cheat […]<\/p>\n","protected":false},"author":6,"featured_media":4229,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[313,73,54,270],"tags":[],"class_list":["post-4190","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-after-see","category-computer-networking","category-ict","category-online-training"],"_links":{"self":[{"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/4190","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/comments?post=4190"}],"version-history":[{"count":36,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/4190\/revisions"}],"predecessor-version":[{"id":4227,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/posts\/4190\/revisions\/4227"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/media\/4229"}],"wp:attachment":[{"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/media?parent=4190"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/categories?post=4190"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/broadwayinfosys.com\/blog\/wp-json\/wp\/v2\/tags?post=4190"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}} | | | | | | | | | | | | | | | | | |