Josh Aharonoff, CPA’s Post

The Ultimate Excel CHEAT SHEET for Financial Professionals 📊🔥 Excel is an accountant's most valuable tool, and knowing its most useful functions can save you HOURS of time every week. ⏱️ Let’s dive in 👇 ➡️ ACCOUNTING FUNCTIONS YOU NEED TO KNOW 📈 SUMIFS - my go-to for summing stuff with conditions. This is how I aggregate detailed financial statements in to pretty summary dashboards. =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) COUNTIF - basically the same idea but counts instead of sums.  =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...) SUBTOTAL - useful for aggregating information while excluding other subtotaled values =SUBTOTAL(function_num, ref1, [ref2], ...) ➡️ DATA MANAGEMENT🔍 VLOOKUP is dead to me...but I recognize many still use it 🙄 XLOOKUP is the new and improved version and can look up information in BOTH DIRECTIONS. =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) If you’re scared of XLOOKUP, INDEX-MATCH is my personal favorite. While both achieve the same, INDEX-MATCH for me has easier syntax =INDEX(array, MATCH(lookup_value, lookup_array, [match_type])) --- ➡️ FINANCE CALCULATIONS 💰 NPV - calculate net present value =NPV(rate, value1, [value2], ...) XIRR - returns the internal rate of return for scheduled cash flows =XIRR(values, dates, [guess]) PMT - have a loan/mortgage? Use PMT to calculate what you owe and when =PMT(rate, nper, pv, [fv], [type]) --- ➡️ SHORTCUTS I USE EVERYDAY 🏎️ Ctrl + Arrow Keys - jumps to the end of data regions🏃♂️ Alt + W V G - removes gridlines. Makes screenshots look 10x better for presentations. ✨ Ctrl + T - Tables are what separates the pros from the amateurs🏆 Ctrl + Shift + L - toggle filters SHIFT + SPACE - Select entire row. --- ➡️ DEBUGGING ERRORS #DIV/0! - you divided by zero. Wrap it in IFERROR or just check with an IF statement first #VALUE! - usually means Excel's confused about data types. #REF! - deleted something important. Time to hit undo #NAME? - either typo in function name or Excel add-in not active. --- ➡️ CRITICAL EXCEL FEATURES Data validation lists - create an easy drop-down with any information Named ranges - makes formulas actually readable. Custom formatting - change the way a cell appears while keeping it’s identity the same Conditional formatting rules - set thresholds to highlight problems before they're problems. Tables with structured references - Tables unlock amazing functionality in Excel and makes it really easy to then manipulate your data --- That’s my take on the most important features in Excel for finance & accounting professionals. But there’s so much more…so what did I miss? Share in the comments below 👇