Excel\/Google Sheets Formulas - AiPro Institute\u2122<\/title>\n <style>\n * {\n margin: 0;\n padding: 0;\n box-sizing: border-box;\n }\n\n body {\n font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;\n line-height: 1.6;\n color: #2c3e50;\n background: #ffffff;\n padding: 20px;\n }\n\n .container {\n max-width: 1400px;\n margin: 0 auto;\n background: white;\n border-radius: 16px;\n box-shadow: 0 4px 20px rgba(0,0,0,0.08);\n overflow: hidden;\n }\n\n .header {\n background: linear-gradient(135deg, #7B3FF2 0%, #00A8E8 100%);\n color: white;\n padding: 30px 40px;\n text-align: center;\n }\n\n .header h1 {\n font-size: 32px;\n margin-bottom: 10px;\n font-weight: 700;\n }\n\n .header p {\n font-size: 16px;\n opacity: 0.95;\n }\n\n .intro-banner {\n background: #f8f9fa;\n padding: 30px 40px;\n border-left: 5px solid #7B3FF2;\n margin: 30px 40px;\n border-radius: 8px;\n }\n\n .intro-banner h2 {\n color: #7B3FF2;\n margin-bottom: 15px;\n font-size: 24px;\n }\n\n .intro-banner p {\n color: #495057;\n font-size: 16px;\n line-height: 1.8;\n }\n\n .content-section {\n padding: 20px 40px;\n }\n\n .category-header {\n background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);\n color: white;\n padding: 15px 25px;\n border-radius: 8px;\n margin: 30px 0 20px 0;\n font-size: 20px;\n font-weight: 600;\n }\n\n .formula-grid {\n display: grid;\n grid-template-columns: repeat(auto-fit, minmax(450px, 1fr));\n gap: 20px;\n margin-bottom: 30px;\n }\n\n .formula-card {\n background: #f8f9fa;\n border: 2px solid #e9ecef;\n border-radius: 10px;\n padding: 20px;\n transition: all 0.3s ease;\n }\n\n .formula-card:hover {\n border-color: #7B3FF2;\n transform: translateY(-2px);\n box-shadow: 0 4px 12px rgba(123, 63, 242, 0.15);\n }\n\n .formula-name {\n color: #7B3FF2;\n font-size: 18px;\n font-weight: 700;\n margin-bottom: 10px;\n font-family: 'Courier New', monospace;\n }\n\n .formula-syntax {\n background: #ffffff;\n border-left: 4px solid #00A8E8;\n padding: 12px 15px;\n margin: 10px 0;\n font-family: 'Courier New', monospace;\n font-size: 14px;\n color: #2c3e50;\n border-radius: 4px;\n overflow-x: auto;\n }\n\n .formula-description {\n color: #495057;\n font-size: 14px;\n line-height: 1.6;\n margin: 10px 0;\n }\n\n .formula-example {\n background: #e7f3ff;\n padding: 12px 15px;\n margin: 10px 0;\n border-radius: 6px;\n font-size: 13px;\n color: #2c3e50;\n }\n\n .example-label {\n color: #00A8E8;\n font-weight: 600;\n margin-bottom: 5px;\n }\n\n .tips-section {\n background: #d4edda;\n padding: 25px 30px;\n margin: 30px 0;\n border-radius: 10px;\n border-left: 5px solid #28a745;\n }\n\n .tips-section h3 {\n color: #155724;\n font-size: 20px;\n margin-bottom: 15px;\n }\n\n .tips-list {\n list-style: none;\n }\n\n .tips-list li {\n color: #155724;\n padding: 8px 0;\n padding-left: 25px;\n position: relative;\n font-size: 15px;\n }\n\n .tips-list li:before {\n content: \"\u2713\";\n position: absolute;\n left: 0;\n color: #28a745;\n font-weight: bold;\n font-size: 18px;\n }\n\n .comparison-table {\n background: #f8f9fa;\n padding: 25px;\n border-radius: 10px;\n margin: 20px 0;\n }\n\n .comparison-table h3 {\n color: #7B3FF2;\n margin-bottom: 15px;\n font-size: 18px;\n }\n\n .comparison-grid {\n display: grid;\n grid-template-columns: 1fr 1fr;\n gap: 20px;\n }\n\n .platform-column {\n background: white;\n padding: 20px;\n border-radius: 8px;\n border: 2px solid #e9ecef;\n }\n\n .platform-column h4 {\n color: #00A8E8;\n margin-bottom: 10px;\n font-size: 16px;\n }\n\n .platform-column code {\n display: block;\n background: #f8f9fa;\n padding: 8px 12px;\n margin: 8px 0;\n border-radius: 4px;\n font-family: 'Courier New', monospace;\n font-size: 13px;\n color: #2c3e50;\n }\n\n .footer {\n background: #f8f9fa;\n padding: 25px 40px;\n text-align: center;\n margin-top: 40px;\n border-top: 3px solid #7B3FF2;\n }\n\n .footer p {\n color: #6c757d;\n font-size: 14px;\n }\n\n @media print {\n body {\n background: white;\n }\n .container {\n box-shadow: none;\n }\n .formula-card {\n page-break-inside: avoid;\n }\n }\n\n @media (max-width: 768px) {\n .formula-grid {\n grid-template-columns: 1fr;\n }\n .comparison-grid {\n grid-template-columns: 1fr;\n }\n .content-section {\n padding: 15px 20px;\n }\n .intro-banner {\n margin: 20px 20px;\n padding: 20px;\n }\n }\n <\/style>\n<\/head>\n<body>\n <div class=\"container\">\n <div class=\"header\">\n <h1>\ud83d\udcca Excel\/Google Sheets Formulas<\/h1>\n <p>Essential formulas for data analysis and automation<\/p>\n <\/div>\n\n <div class=\"intro-banner\">\n <h2>Master Spreadsheet Formulas<\/h2>\n <p>This comprehensive guide covers the most important formulas for Excel and Google Sheets. Whether you're analyzing data, building dashboards, or automating workflows, these formulas will boost your productivity significantly.<\/p>\n <\/div>\n\n <div class=\"content-section\">\n <div class=\"category-header\">\ud83d\udcc8 Lookup & Reference Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">VLOOKUP<\/div>\n <div class=\"formula-syntax\">=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/div>\n <div class=\"formula-description\">Searches for a value in the first column of a range and returns a value in the same row from a specified column.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =VLOOKUP(A2, Products!A:D, 3, FALSE)<br>\n Finds product in A2 and returns price from column 3\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">XLOOKUP<\/div>\n <div class=\"formula-syntax\">=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])<\/div>\n <div class=\"formula-description\">Modern replacement for VLOOKUP. Searches any column and returns corresponding values from any column (Excel 365\/Sheets).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =XLOOKUP(B2, Names, Salaries, \"Not Found\")<br>\n Finds name in B2 and returns salary\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">INDEX + MATCH<\/div>\n <div class=\"formula-syntax\">=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))<\/div>\n <div class=\"formula-description\">Powerful combination for flexible lookups. More versatile than VLOOKUP - can look left or right.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =INDEX(C:C, MATCH(A2, A:A, 0))<br>\n Returns value from column C where A2 matches column A\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">OFFSET<\/div>\n <div class=\"formula-syntax\">=OFFSET(reference, rows, cols, [height], [width])<\/div>\n <div class=\"formula-description\">Returns a reference to a range that is offset from a starting cell by a specified number of rows and columns.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =OFFSET(A1, 5, 2, 3, 1)<br>\n Returns 3 cells starting 5 rows down and 2 columns right from A1\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udd22 Mathematical & Statistical Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">SUMIF \/ SUMIFS<\/div>\n <div class=\"formula-syntax\">=SUMIF(range, criteria, [sum_range])<br>=SUMIFS(sum_range, criteria_range1, criteria1, ...)<\/div>\n <div class=\"formula-description\">Sums values based on one (SUMIF) or multiple (SUMIFS) conditions.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =SUMIFS(D:D, A:A, \"North\", B:B, \">1000\")<br>\n Sums sales for North region where value > 1000\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">COUNTIF \/ COUNTIFS<\/div>\n <div class=\"formula-syntax\">=COUNTIF(range, criteria)<br>=COUNTIFS(criteria_range1, criteria1, ...)<\/div>\n <div class=\"formula-description\">Counts cells that meet one or multiple criteria.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =COUNTIFS(A:A, \"Complete\", B:B, \"High Priority\")<br>\n Counts rows with \"Complete\" status and \"High Priority\"\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">AVERAGEIF \/ AVERAGEIFS<\/div>\n <div class=\"formula-syntax\">=AVERAGEIF(range, criteria, [average_range])<br>=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)<\/div>\n <div class=\"formula-description\">Calculates average based on one or multiple conditions.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =AVERAGEIFS(D:D, A:A, \"Product A\", C:C, \">=2024\")<br>\n Average sales for Product A from 2024 onwards\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">ROUND \/ ROUNDUP \/ ROUNDDOWN<\/div>\n <div class=\"formula-syntax\">=ROUND(number, num_digits)<\/div>\n <div class=\"formula-description\">Rounds numbers to specified decimal places. ROUNDUP always rounds up, ROUNDDOWN always rounds down.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =ROUND(A1*1.15, 2)<br>\n Multiplies by 1.15 and rounds to 2 decimals\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udcdd Text Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">CONCATENATE \/ TEXTJOIN<\/div>\n <div class=\"formula-syntax\">=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)<\/div>\n <div class=\"formula-description\">Joins multiple text strings with a specified delimiter. More powerful than CONCATENATE.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =TEXTJOIN(\", \", TRUE, A2:A10)<br>\n Joins cells A2 to A10 with commas, ignoring blanks\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">LEFT \/ RIGHT \/ MID<\/div>\n <div class=\"formula-syntax\">=LEFT(text, [num_chars])<br>=RIGHT(text, [num_chars])<br>=MID(text, start_num, num_chars)<\/div>\n <div class=\"formula-description\">Extracts characters from text strings from the beginning, end, or middle.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =LEFT(A2, 5)<br>\n Returns first 5 characters from A2\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">TRIM \/ CLEAN<\/div>\n <div class=\"formula-syntax\">=TRIM(text)<br>=CLEAN(text)<\/div>\n <div class=\"formula-description\">TRIM removes extra spaces. CLEAN removes non-printable characters.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =TRIM(A2)<br>\n Removes leading, trailing, and extra spaces\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">UPPER \/ LOWER \/ PROPER<\/div>\n <div class=\"formula-syntax\">=UPPER(text) \/ =LOWER(text) \/ =PROPER(text)<\/div>\n <div class=\"formula-description\">Converts text to uppercase, lowercase, or proper case (first letter capitalized).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =PROPER(A2)<br>\n Converts \"john doe\" to \"John Doe\"\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udcc5 Date & Time Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">TODAY \/ NOW<\/div>\n <div class=\"formula-syntax\">=TODAY()<br>=NOW()<\/div>\n <div class=\"formula-description\">TODAY returns current date. NOW returns current date and time. Both update automatically.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =TODAY()-A2<br>\n Calculates days since date in A2\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">DATE \/ DATEDIF<\/div>\n <div class=\"formula-syntax\">=DATE(year, month, day)<br>=DATEDIF(start_date, end_date, unit)<\/div>\n <div class=\"formula-description\">DATE creates a date. DATEDIF calculates difference between dates in days, months, or years.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =DATEDIF(A2, TODAY(), \"Y\")<br>\n Calculates age in years from birthdate\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">EOMONTH \/ EDATE<\/div>\n <div class=\"formula-syntax\">=EOMONTH(start_date, months)<br>=EDATE(start_date, months)<\/div>\n <div class=\"formula-description\">EOMONTH returns last day of month. EDATE adds\/subtracts months from a date.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =EOMONTH(TODAY(), 0)<br>\n Returns last day of current month\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">WEEKDAY \/ WORKDAY<\/div>\n <div class=\"formula-syntax\">=WEEKDAY(serial_number, [return_type])<br>=WORKDAY(start_date, days, [holidays])<\/div>\n <div class=\"formula-description\">WEEKDAY returns day of week (1-7). WORKDAY calculates working days excluding weekends and holidays.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =WORKDAY(TODAY(), 10)<br>\n Returns date 10 working days from today\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83c\udfaf Logical Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">IF \/ IFS<\/div>\n <div class=\"formula-syntax\">=IF(logical_test, value_if_true, value_if_false)<br>=IFS(condition1, value1, condition2, value2, ...)<\/div>\n <div class=\"formula-description\">IF tests a condition. IFS tests multiple conditions (no nesting needed).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =IFS(A2>=90, \"A\", A2>=80, \"B\", A2>=70, \"C\", TRUE, \"F\")<br>\n Assigns letter grade based on score\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">AND \/ OR \/ NOT<\/div>\n <div class=\"formula-syntax\">=AND(logical1, [logical2], ...)<br>=OR(logical1, [logical2], ...)<\/div>\n <div class=\"formula-description\">AND requires all conditions to be TRUE. OR requires at least one TRUE. NOT reverses TRUE\/FALSE.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =IF(AND(A2>100, B2=\"Active\"), \"Qualified\", \"Not Qualified\")<br>\n Checks multiple conditions\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">IFERROR \/ IFNA<\/div>\n <div class=\"formula-syntax\">=IFERROR(value, value_if_error)<br>=IFNA(value, value_if_na)<\/div>\n <div class=\"formula-description\">Returns alternative value if formula results in error (IFERROR) or #N\/A (IFNA).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =IFERROR(VLOOKUP(A2, Data!A:B, 2, 0), \"Not Found\")<br>\n Shows \"Not Found\" if VLOOKUP fails\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">SWITCH<\/div>\n <div class=\"formula-syntax\">=SWITCH(expression, value1, result1, [value2, result2], ..., [default])<\/div>\n <div class=\"formula-description\">Evaluates expression against multiple values and returns corresponding result. Cleaner than nested IFs.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =SWITCH(A2, \"S\", \"Small\", \"M\", \"Medium\", \"L\", \"Large\", \"Unknown\")<br>\n Converts size codes to full names\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udcca Advanced Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">FILTER (Google Sheets)<\/div>\n <div class=\"formula-syntax\">=FILTER(range, condition1, [condition2], ...)<\/div>\n <div class=\"formula-description\">Returns filtered array based on specified conditions. Dynamic and powerful.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =FILTER(A2:D100, C2:C100>1000, B2:B100=\"Active\")<br>\n Returns rows where value>1000 and status is Active\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">UNIQUE \/ SORT<\/div>\n <div class=\"formula-syntax\">=UNIQUE(range, [by_col], [exactly_once])<br>=SORT(range, [sort_column], [is_ascending])<\/div>\n <div class=\"formula-description\">UNIQUE extracts unique values. SORT sorts data dynamically.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =SORT(UNIQUE(A2:A100), 1, TRUE)<br>\n Returns unique values sorted alphabetically\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">QUERY (Google Sheets)<\/div>\n <div class=\"formula-syntax\">=QUERY(data, query, [headers])<\/div>\n <div class=\"formula-description\">Uses SQL-like syntax to query data. Extremely powerful for data manipulation.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =QUERY(A1:D100, \"SELECT A, SUM(D) WHERE B='North' GROUP BY A\")<br>\n SQL-style query for aggregation\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">ARRAYFORMULA (Google Sheets)<\/div>\n <div class=\"formula-syntax\">=ARRAYFORMULA(array_formula)<\/div>\n <div class=\"formula-description\">Applies formula to entire range automatically. No need to drag formulas down.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =ARRAYFORMULA(IF(A2:A<>\"\", A2:A*B2:B, \"\"))<br>\n Multiplies two columns for all non-empty rows\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"comparison-table\">\n <h3>\ud83d\udd04 Excel vs Google Sheets Key Differences<\/h3>\n <div class=\"comparison-grid\">\n <div class=\"platform-column\">\n <h4>Excel Exclusive<\/h4>\n <code>=XLOOKUP() - Modern lookup<\/code>\n <code>=FILTER() - Requires 365<\/code>\n <code>=XMATCH() - Advanced matching<\/code>\n <code>=LET() - Variable assignment<\/code>\n <code>=LAMBDA() - Custom functions<\/code>\n <\/div>\n <div class=\"platform-column\">\n <h4>Google Sheets Exclusive<\/h4>\n <code>=QUERY() - SQL-like queries<\/code>\n <code>=ARRAYFORMULA() - Auto array<\/code>\n <code>=IMPORTRANGE() - Cross-sheet<\/code>\n <code>=GOOGLEFINANCE() - Stock data<\/code>\n <code>=IMPORTHTML() - Web scraping<\/code>\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"tips-section\">\n <h3>\ud83d\udca1 Pro Tips for Formula Mastery<\/h3>\n <ul class=\"tips-list\">\n <li><strong>Use Named Ranges:<\/strong> Instead of A1:A100, use \"Sales_Data\" for better readability and maintenance<\/li>\n <li><strong>Absolute References:<\/strong> Use $ (e.g., $A$1) when you don't want references to change when copying<\/li>\n <li><strong>F4 Shortcut:<\/strong> Press F4 to cycle through reference types (A1 \u2192 $A$1 \u2192 A$1 \u2192 $A1)<\/li>\n <li><strong>Array Formulas:<\/strong> Ctrl+Shift+Enter in Excel to create array formulas for complex calculations<\/li>\n <li><strong>Error Handling:<\/strong> Always wrap risky formulas (VLOOKUP, division) in IFERROR for cleaner sheets<\/li>\n <li><strong>Combine Functions:<\/strong> Nest functions for powerful results (e.g., IFERROR + VLOOKUP + TRIM)<\/li>\n <li><strong>Test Incrementally:<\/strong> Build complex formulas step by step, testing each component<\/li>\n <li><strong>Document Formulas:<\/strong> Add comments or nearby notes explaining complex formula logic<\/li>\n <li><strong>Use Helper Columns:<\/strong> Break complex formulas into multiple columns for easier debugging<\/li>\n <li><strong>Keyboard Shortcut:<\/strong> Press F2 to edit formulas quickly and see cell references highlighted<\/li>\n <\/ul>\n <\/div>\n <\/div>\n\n <div class=\"footer\">\n <p><strong>AiPro Institute\u2122<\/strong> | Member-Only Content | \u00a9 2026 All Rights Reserved<\/p>\n <p style=\"margin-top: 10px; font-size: 12px;\">This cheat sheet is for educational purposes for AiPro Institute members only.<\/p>\n <\/div>\n <\/div>\n<\/body>\n<\/html>\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"<p>Excel\/Google Sheets Formulas – AiPro Institute\u2122 \ud83d\udcca Excel\/Google Sheets Formulas Essential formulas for data analysis and automation Master Spreadsheet Formulas This comprehensive guide covers the most important formulas for Excel and Google Sheets. Whether you’re analyzing data, building dashboards, or automating workflows, these formulas will boost your productivity significantly. \ud83d\udcc8 Lookup & Reference Functions VLOOKUP…<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[84],"tags":[],"class_list":["post-3057","post","type-post","status-publish","format-standard","hentry","category-productivity-tools"],"acf":[],"_links":{"self":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts\/3057","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/comments?post=3057"}],"version-history":[{"count":4,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts\/3057\/revisions"}],"predecessor-version":[{"id":3067,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts\/3057\/revisions\/3067"}],"wp:attachment":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/media?parent=3057"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/categories?post=3057"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/tags?post=3057"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

\n\t\t\t\t\t\t

\n\t\t\t\t\t

\n\t\t\t

\n\t\t\t\t\t\t

\n\t\t\t\t\t\n\n\n \n \n Excel\/Google Sheets Formulas - AiPro Institute\u2122<\/title>\n <style>\n * {\n margin: 0;\n padding: 0;\n box-sizing: border-box;\n }\n\n body {\n font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, 'Helvetica Neue', Arial, sans-serif;\n line-height: 1.6;\n color: #2c3e50;\n background: #ffffff;\n padding: 20px;\n }\n\n .container {\n max-width: 1400px;\n margin: 0 auto;\n background: white;\n border-radius: 16px;\n box-shadow: 0 4px 20px rgba(0,0,0,0.08);\n overflow: hidden;\n }\n\n .header {\n background: linear-gradient(135deg, #7B3FF2 0%, #00A8E8 100%);\n color: white;\n padding: 30px 40px;\n text-align: center;\n }\n\n .header h1 {\n font-size: 32px;\n margin-bottom: 10px;\n font-weight: 700;\n }\n\n .header p {\n font-size: 16px;\n opacity: 0.95;\n }\n\n .intro-banner {\n background: #f8f9fa;\n padding: 30px 40px;\n border-left: 5px solid #7B3FF2;\n margin: 30px 40px;\n border-radius: 8px;\n }\n\n .intro-banner h2 {\n color: #7B3FF2;\n margin-bottom: 15px;\n font-size: 24px;\n }\n\n .intro-banner p {\n color: #495057;\n font-size: 16px;\n line-height: 1.8;\n }\n\n .content-section {\n padding: 20px 40px;\n }\n\n .category-header {\n background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);\n color: white;\n padding: 15px 25px;\n border-radius: 8px;\n margin: 30px 0 20px 0;\n font-size: 20px;\n font-weight: 600;\n }\n\n .formula-grid {\n display: grid;\n grid-template-columns: repeat(auto-fit, minmax(450px, 1fr));\n gap: 20px;\n margin-bottom: 30px;\n }\n\n .formula-card {\n background: #f8f9fa;\n border: 2px solid #e9ecef;\n border-radius: 10px;\n padding: 20px;\n transition: all 0.3s ease;\n }\n\n .formula-card:hover {\n border-color: #7B3FF2;\n transform: translateY(-2px);\n box-shadow: 0 4px 12px rgba(123, 63, 242, 0.15);\n }\n\n .formula-name {\n color: #7B3FF2;\n font-size: 18px;\n font-weight: 700;\n margin-bottom: 10px;\n font-family: 'Courier New', monospace;\n }\n\n .formula-syntax {\n background: #ffffff;\n border-left: 4px solid #00A8E8;\n padding: 12px 15px;\n margin: 10px 0;\n font-family: 'Courier New', monospace;\n font-size: 14px;\n color: #2c3e50;\n border-radius: 4px;\n overflow-x: auto;\n }\n\n .formula-description {\n color: #495057;\n font-size: 14px;\n line-height: 1.6;\n margin: 10px 0;\n }\n\n .formula-example {\n background: #e7f3ff;\n padding: 12px 15px;\n margin: 10px 0;\n border-radius: 6px;\n font-size: 13px;\n color: #2c3e50;\n }\n\n .example-label {\n color: #00A8E8;\n font-weight: 600;\n margin-bottom: 5px;\n }\n\n .tips-section {\n background: #d4edda;\n padding: 25px 30px;\n margin: 30px 0;\n border-radius: 10px;\n border-left: 5px solid #28a745;\n }\n\n .tips-section h3 {\n color: #155724;\n font-size: 20px;\n margin-bottom: 15px;\n }\n\n .tips-list {\n list-style: none;\n }\n\n .tips-list li {\n color: #155724;\n padding: 8px 0;\n padding-left: 25px;\n position: relative;\n font-size: 15px;\n }\n\n .tips-list li:before {\n content: \"\u2713\";\n position: absolute;\n left: 0;\n color: #28a745;\n font-weight: bold;\n font-size: 18px;\n }\n\n .comparison-table {\n background: #f8f9fa;\n padding: 25px;\n border-radius: 10px;\n margin: 20px 0;\n }\n\n .comparison-table h3 {\n color: #7B3FF2;\n margin-bottom: 15px;\n font-size: 18px;\n }\n\n .comparison-grid {\n display: grid;\n grid-template-columns: 1fr 1fr;\n gap: 20px;\n }\n\n .platform-column {\n background: white;\n padding: 20px;\n border-radius: 8px;\n border: 2px solid #e9ecef;\n }\n\n .platform-column h4 {\n color: #00A8E8;\n margin-bottom: 10px;\n font-size: 16px;\n }\n\n .platform-column code {\n display: block;\n background: #f8f9fa;\n padding: 8px 12px;\n margin: 8px 0;\n border-radius: 4px;\n font-family: 'Courier New', monospace;\n font-size: 13px;\n color: #2c3e50;\n }\n\n .footer {\n background: #f8f9fa;\n padding: 25px 40px;\n text-align: center;\n margin-top: 40px;\n border-top: 3px solid #7B3FF2;\n }\n\n .footer p {\n color: #6c757d;\n font-size: 14px;\n }\n\n @media print {\n body {\n background: white;\n }\n .container {\n box-shadow: none;\n }\n .formula-card {\n page-break-inside: avoid;\n }\n }\n\n @media (max-width: 768px) {\n .formula-grid {\n grid-template-columns: 1fr;\n }\n .comparison-grid {\n grid-template-columns: 1fr;\n }\n .content-section {\n padding: 15px 20px;\n }\n .intro-banner {\n margin: 20px 20px;\n padding: 20px;\n }\n }\n <\/style>\n<\/head>\n<body>\n <div class=\"container\">\n <div class=\"header\">\n <h1>\ud83d\udcca Excel\/Google Sheets Formulas<\/h1>\n <p>Essential formulas for data analysis and automation<\/p>\n <\/div>\n\n <div class=\"intro-banner\">\n <h2>Master Spreadsheet Formulas<\/h2>\n <p>This comprehensive guide covers the most important formulas for Excel and Google Sheets. Whether you're analyzing data, building dashboards, or automating workflows, these formulas will boost your productivity significantly.<\/p>\n <\/div>\n\n <div class=\"content-section\">\n <div class=\"category-header\">\ud83d\udcc8 Lookup & Reference Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">VLOOKUP<\/div>\n <div class=\"formula-syntax\">=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])<\/div>\n <div class=\"formula-description\">Searches for a value in the first column of a range and returns a value in the same row from a specified column.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =VLOOKUP(A2, Products!A:D, 3, FALSE)<br>\n Finds product in A2 and returns price from column 3\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">XLOOKUP<\/div>\n <div class=\"formula-syntax\">=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])<\/div>\n <div class=\"formula-description\">Modern replacement for VLOOKUP. Searches any column and returns corresponding values from any column (Excel 365\/Sheets).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =XLOOKUP(B2, Names, Salaries, \"Not Found\")<br>\n Finds name in B2 and returns salary\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">INDEX + MATCH<\/div>\n <div class=\"formula-syntax\">=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))<\/div>\n <div class=\"formula-description\">Powerful combination for flexible lookups. More versatile than VLOOKUP - can look left or right.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =INDEX(C:C, MATCH(A2, A:A, 0))<br>\n Returns value from column C where A2 matches column A\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">OFFSET<\/div>\n <div class=\"formula-syntax\">=OFFSET(reference, rows, cols, [height], [width])<\/div>\n <div class=\"formula-description\">Returns a reference to a range that is offset from a starting cell by a specified number of rows and columns.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =OFFSET(A1, 5, 2, 3, 1)<br>\n Returns 3 cells starting 5 rows down and 2 columns right from A1\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udd22 Mathematical & Statistical Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">SUMIF \/ SUMIFS<\/div>\n <div class=\"formula-syntax\">=SUMIF(range, criteria, [sum_range])<br>=SUMIFS(sum_range, criteria_range1, criteria1, ...)<\/div>\n <div class=\"formula-description\">Sums values based on one (SUMIF) or multiple (SUMIFS) conditions.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =SUMIFS(D:D, A:A, \"North\", B:B, \">1000\")<br>\n Sums sales for North region where value > 1000\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">COUNTIF \/ COUNTIFS<\/div>\n <div class=\"formula-syntax\">=COUNTIF(range, criteria)<br>=COUNTIFS(criteria_range1, criteria1, ...)<\/div>\n <div class=\"formula-description\">Counts cells that meet one or multiple criteria.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =COUNTIFS(A:A, \"Complete\", B:B, \"High Priority\")<br>\n Counts rows with \"Complete\" status and \"High Priority\"\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">AVERAGEIF \/ AVERAGEIFS<\/div>\n <div class=\"formula-syntax\">=AVERAGEIF(range, criteria, [average_range])<br>=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)<\/div>\n <div class=\"formula-description\">Calculates average based on one or multiple conditions.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =AVERAGEIFS(D:D, A:A, \"Product A\", C:C, \">=2024\")<br>\n Average sales for Product A from 2024 onwards\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">ROUND \/ ROUNDUP \/ ROUNDDOWN<\/div>\n <div class=\"formula-syntax\">=ROUND(number, num_digits)<\/div>\n <div class=\"formula-description\">Rounds numbers to specified decimal places. ROUNDUP always rounds up, ROUNDDOWN always rounds down.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =ROUND(A1*1.15, 2)<br>\n Multiplies by 1.15 and rounds to 2 decimals\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udcdd Text Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">CONCATENATE \/ TEXTJOIN<\/div>\n <div class=\"formula-syntax\">=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)<\/div>\n <div class=\"formula-description\">Joins multiple text strings with a specified delimiter. More powerful than CONCATENATE.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =TEXTJOIN(\", \", TRUE, A2:A10)<br>\n Joins cells A2 to A10 with commas, ignoring blanks\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">LEFT \/ RIGHT \/ MID<\/div>\n <div class=\"formula-syntax\">=LEFT(text, [num_chars])<br>=RIGHT(text, [num_chars])<br>=MID(text, start_num, num_chars)<\/div>\n <div class=\"formula-description\">Extracts characters from text strings from the beginning, end, or middle.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =LEFT(A2, 5)<br>\n Returns first 5 characters from A2\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">TRIM \/ CLEAN<\/div>\n <div class=\"formula-syntax\">=TRIM(text)<br>=CLEAN(text)<\/div>\n <div class=\"formula-description\">TRIM removes extra spaces. CLEAN removes non-printable characters.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =TRIM(A2)<br>\n Removes leading, trailing, and extra spaces\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">UPPER \/ LOWER \/ PROPER<\/div>\n <div class=\"formula-syntax\">=UPPER(text) \/ =LOWER(text) \/ =PROPER(text)<\/div>\n <div class=\"formula-description\">Converts text to uppercase, lowercase, or proper case (first letter capitalized).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =PROPER(A2)<br>\n Converts \"john doe\" to \"John Doe\"\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udcc5 Date & Time Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">TODAY \/ NOW<\/div>\n <div class=\"formula-syntax\">=TODAY()<br>=NOW()<\/div>\n <div class=\"formula-description\">TODAY returns current date. NOW returns current date and time. Both update automatically.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =TODAY()-A2<br>\n Calculates days since date in A2\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">DATE \/ DATEDIF<\/div>\n <div class=\"formula-syntax\">=DATE(year, month, day)<br>=DATEDIF(start_date, end_date, unit)<\/div>\n <div class=\"formula-description\">DATE creates a date. DATEDIF calculates difference between dates in days, months, or years.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =DATEDIF(A2, TODAY(), \"Y\")<br>\n Calculates age in years from birthdate\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">EOMONTH \/ EDATE<\/div>\n <div class=\"formula-syntax\">=EOMONTH(start_date, months)<br>=EDATE(start_date, months)<\/div>\n <div class=\"formula-description\">EOMONTH returns last day of month. EDATE adds\/subtracts months from a date.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =EOMONTH(TODAY(), 0)<br>\n Returns last day of current month\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">WEEKDAY \/ WORKDAY<\/div>\n <div class=\"formula-syntax\">=WEEKDAY(serial_number, [return_type])<br>=WORKDAY(start_date, days, [holidays])<\/div>\n <div class=\"formula-description\">WEEKDAY returns day of week (1-7). WORKDAY calculates working days excluding weekends and holidays.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =WORKDAY(TODAY(), 10)<br>\n Returns date 10 working days from today\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83c\udfaf Logical Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">IF \/ IFS<\/div>\n <div class=\"formula-syntax\">=IF(logical_test, value_if_true, value_if_false)<br>=IFS(condition1, value1, condition2, value2, ...)<\/div>\n <div class=\"formula-description\">IF tests a condition. IFS tests multiple conditions (no nesting needed).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =IFS(A2>=90, \"A\", A2>=80, \"B\", A2>=70, \"C\", TRUE, \"F\")<br>\n Assigns letter grade based on score\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">AND \/ OR \/ NOT<\/div>\n <div class=\"formula-syntax\">=AND(logical1, [logical2], ...)<br>=OR(logical1, [logical2], ...)<\/div>\n <div class=\"formula-description\">AND requires all conditions to be TRUE. OR requires at least one TRUE. NOT reverses TRUE\/FALSE.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =IF(AND(A2>100, B2=\"Active\"), \"Qualified\", \"Not Qualified\")<br>\n Checks multiple conditions\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">IFERROR \/ IFNA<\/div>\n <div class=\"formula-syntax\">=IFERROR(value, value_if_error)<br>=IFNA(value, value_if_na)<\/div>\n <div class=\"formula-description\">Returns alternative value if formula results in error (IFERROR) or #N\/A (IFNA).<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =IFERROR(VLOOKUP(A2, Data!A:B, 2, 0), \"Not Found\")<br>\n Shows \"Not Found\" if VLOOKUP fails\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">SWITCH<\/div>\n <div class=\"formula-syntax\">=SWITCH(expression, value1, result1, [value2, result2], ..., [default])<\/div>\n <div class=\"formula-description\">Evaluates expression against multiple values and returns corresponding result. Cleaner than nested IFs.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =SWITCH(A2, \"S\", \"Small\", \"M\", \"Medium\", \"L\", \"Large\", \"Unknown\")<br>\n Converts size codes to full names\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"category-header\">\ud83d\udcca Advanced Functions<\/div>\n \n <div class=\"formula-grid\">\n <div class=\"formula-card\">\n <div class=\"formula-name\">FILTER (Google Sheets)<\/div>\n <div class=\"formula-syntax\">=FILTER(range, condition1, [condition2], ...)<\/div>\n <div class=\"formula-description\">Returns filtered array based on specified conditions. Dynamic and powerful.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =FILTER(A2:D100, C2:C100>1000, B2:B100=\"Active\")<br>\n Returns rows where value>1000 and status is Active\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">UNIQUE \/ SORT<\/div>\n <div class=\"formula-syntax\">=UNIQUE(range, [by_col], [exactly_once])<br>=SORT(range, [sort_column], [is_ascending])<\/div>\n <div class=\"formula-description\">UNIQUE extracts unique values. SORT sorts data dynamically.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =SORT(UNIQUE(A2:A100), 1, TRUE)<br>\n Returns unique values sorted alphabetically\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">QUERY (Google Sheets)<\/div>\n <div class=\"formula-syntax\">=QUERY(data, query, [headers])<\/div>\n <div class=\"formula-description\">Uses SQL-like syntax to query data. Extremely powerful for data manipulation.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =QUERY(A1:D100, \"SELECT A, SUM(D) WHERE B='North' GROUP BY A\")<br>\n SQL-style query for aggregation\n <\/div>\n <\/div>\n\n <div class=\"formula-card\">\n <div class=\"formula-name\">ARRAYFORMULA (Google Sheets)<\/div>\n <div class=\"formula-syntax\">=ARRAYFORMULA(array_formula)<\/div>\n <div class=\"formula-description\">Applies formula to entire range automatically. No need to drag formulas down.<\/div>\n <div class=\"formula-example\">\n <div class=\"example-label\">Example:<\/div>\n =ARRAYFORMULA(IF(A2:A<>\"\", A2:A*B2:B, \"\"))<br>\n Multiplies two columns for all non-empty rows\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"comparison-table\">\n <h3>\ud83d\udd04 Excel vs Google Sheets Key Differences<\/h3>\n <div class=\"comparison-grid\">\n <div class=\"platform-column\">\n <h4>Excel Exclusive<\/h4>\n <code>=XLOOKUP() - Modern lookup<\/code>\n <code>=FILTER() - Requires 365<\/code>\n <code>=XMATCH() - Advanced matching<\/code>\n <code>=LET() - Variable assignment<\/code>\n <code>=LAMBDA() - Custom functions<\/code>\n <\/div>\n <div class=\"platform-column\">\n <h4>Google Sheets Exclusive<\/h4>\n <code>=QUERY() - SQL-like queries<\/code>\n <code>=ARRAYFORMULA() - Auto array<\/code>\n <code>=IMPORTRANGE() - Cross-sheet<\/code>\n <code>=GOOGLEFINANCE() - Stock data<\/code>\n <code>=IMPORTHTML() - Web scraping<\/code>\n <\/div>\n <\/div>\n <\/div>\n\n <div class=\"tips-section\">\n <h3>\ud83d\udca1 Pro Tips for Formula Mastery<\/h3>\n <ul class=\"tips-list\">\n <li><strong>Use Named Ranges:<\/strong> Instead of A1:A100, use \"Sales_Data\" for better readability and maintenance<\/li>\n <li><strong>Absolute References:<\/strong> Use $ (e.g., $A$1) when you don't want references to change when copying<\/li>\n <li><strong>F4 Shortcut:<\/strong> Press F4 to cycle through reference types (A1 \u2192 $A$1 \u2192 A$1 \u2192 $A1)<\/li>\n <li><strong>Array Formulas:<\/strong> Ctrl+Shift+Enter in Excel to create array formulas for complex calculations<\/li>\n <li><strong>Error Handling:<\/strong> Always wrap risky formulas (VLOOKUP, division) in IFERROR for cleaner sheets<\/li>\n <li><strong>Combine Functions:<\/strong> Nest functions for powerful results (e.g., IFERROR + VLOOKUP + TRIM)<\/li>\n <li><strong>Test Incrementally:<\/strong> Build complex formulas step by step, testing each component<\/li>\n <li><strong>Document Formulas:<\/strong> Add comments or nearby notes explaining complex formula logic<\/li>\n <li><strong>Use Helper Columns:<\/strong> Break complex formulas into multiple columns for easier debugging<\/li>\n <li><strong>Keyboard Shortcut:<\/strong> Press F2 to edit formulas quickly and see cell references highlighted<\/li>\n <\/ul>\n <\/div>\n <\/div>\n\n <div class=\"footer\">\n <p><strong>AiPro Institute\u2122<\/strong> | Member-Only Content | \u00a9 2026 All Rights Reserved<\/p>\n <p style=\"margin-top: 10px; font-size: 12px;\">This cheat sheet is for educational purposes for AiPro Institute members only.<\/p>\n <\/div>\n <\/div>\n<\/body>\n<\/html>\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t<\/div>","protected":false},"excerpt":{"rendered":"<p>Excel\/Google Sheets Formulas – AiPro Institute\u2122 \ud83d\udcca Excel\/Google Sheets Formulas Essential formulas for data analysis and automation Master Spreadsheet Formulas This comprehensive guide covers the most important formulas for Excel and Google Sheets. Whether you’re analyzing data, building dashboards, or automating workflows, these formulas will boost your productivity significantly. \ud83d\udcc8 Lookup & Reference Functions VLOOKUP…<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[84],"tags":[],"class_list":["post-3057","post","type-post","status-publish","format-standard","hentry","category-productivity-tools"],"acf":[],"_links":{"self":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts\/3057","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/comments?post=3057"}],"version-history":[{"count":4,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts\/3057\/revisions"}],"predecessor-version":[{"id":3067,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/posts\/3057\/revisions\/3067"}],"wp:attachment":[{"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/media?parent=3057"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/categories?post=3057"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/teen.aiproinstitute.com\/zh\/wp-json\/wp\/v2\/tags?post=3057"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}