Excel for CFOs

The ABCs of Excel ?? Credits to Josh Aharonoff, CPA, CPA , follow him for more content. Here's the original post ---- The ABCs of Excel ?? Sing along with me and learn Excel ?? A Alt Press "Alt" to access Excel's Ribbon shortcuts for quick navigation and task execution. B Bar Chart One of the most popular ways to display data via a chat with an X and Y-axis C Conditional Formatting Automatically formats cells based on specified criteria D Donut chart A popular chart showing proportionate parts of a whole, with a center cutout E Excel The most powerful tool on the planet F Function Predefined formula that performs calculations using arguments G Group Combine multiple rows or columns into a single collapsible/expandable outline H Hide Conceal rows, columns, or worksheets from view without deleting them I Insert Add new cells, rows, columns, worksheets, or objects into a spreadsheet J Jump to next / last active cell Keyboard shortcut for quick navigation using CTRL + Arrow keys K Keyboard shortcut Key combinations allowing you to perform commands faster without using the mouse L Lookup Search for a value in a row or column and return a corresponding value from a different row or column. M Macro sequence of commands or actions utilizing VBA to automate repetitive tasks in Excel. N Named range Assign a name to a cell range for easier reference O Offset Function that returns a reference to a range that is a specified number of rows and columns from a cell P PivotTable Interactive table that automatically extracts, organizes, and summarizes large amounts of data for dynamic analysis and reporting Q Query Retrieve data based on specific criteria from external databases or different areas within Excel. R Range A selection of two or more cells on a spreadsheet S Sumifs Function that adds up numbers in a range that meet one or multiple criteria across corresponding ranges. T Table Structured range of data in a tabular format U Unique Spill Function that returns the unique values from a range V Vlookup Function that searches for a value in the first column of a table and returns a value in the same row from a specified column. W Wrap Text Automatically adjusts cell height to display cell contents on multiple lines if they exceed cell width. X Xlookup A function that searches a range or an array, and returns an item corresponding to the first match it finds. Y Y-Axis Vertical axis in a chart of graph Z Zoom Adjust the size of your worksheet by utilizing the zoom slider, or hit View > Zoom === Now you know your ABCs, thanks for signing along with me ????? Which was your favorite letter? and what are some other items you’d add to our Excel Alphabet? Let us know what you would add in the comments below! ?? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Learn Power Query…One of Excel's Most Powerful Features If you aren’t using Power Query in your spreadsheets… you are missing out. ?? Watch my latest video to see how I use Power Query to transform hours of Excel work into seconds https://lnkd.in/eHgSRSSs Once you learn how to use Power Query, I can promise you your capabilities in Excel will skyrocket Let’s go over how it works… ?? What is Power Query? Power Query (now referred to as “Get & Transform Data”) is an ETL tool… which stands for: E→ Extract T → Transform L → Load Let’s go through each… ?? EXTRACT With Power Query, you can extract information from a ton of sources, such as… 1?? A table / range in your workbook 2?? An external file / CSV 3?? A folder containing multiple files 4?? A database 5?? A website and much more… Once you have your data extracted, you can now start manipulating it… ?? TRANSFORM Now comes the fun part - manipulating…or “transforming” your data. Here you’ll be taken to the power query editor where you can make any changes that you want to your source data… Think of this like a macro that you’re designing, where you can manipulate data over a number of different actions… each of these actions will get stored each time you hit Refresh, transforming your source data into the new format ?? LOAD Now that you have your data transformed, it’s time to load your data into a new location. This won’t overwrite your source data, but instead will allow you to load the newly transformed data to: 1?? a Table 2?? a PivotTable Report 3?? a PivotChart 4?? to a connection (where you can further manipulate with other queries, without physically loading to a location in your spreadsheet ?? OK…so what’s so special about Power Query? The whole idea with Power Query is you can now pull data from a number of different sources in your Excel file easily…without creating a notorious “external reference”… and then once your data is pulled, you can set rules for how you want that data to be manipulated only once… allowing you to see the output of those edits by simply hitting refresh. This will save you a TON of time ?? What is my favorite use of Power Query? My favorite use by far is UNPIVOTING dates… that allows me to take for example a profit and loss that has an X Axis ? (for dates)… and UNPIVOT those dates into on column going vertically ? That makes it incredibly easier to reference when creating dashboards and other reporting === This is just a preview of what you can accomplish with Power Query… Want to dive deeper? Check out the video below, where I show you how to use Power Query to combine multiple currency files, transform messy data, and create automated department reporting in just minutes. ?? What questions do you have on Power Query? And what are your favorite use cases? Let us know by joining in on the discussion in the comments below ??

?? I Built the World's GREATEST PivotTable... and You Can Get it FREE! ?? Every month I was spending hours putting together the same reports - P&L analysis, budget comparisons, variance breakdowns. I knew there had to be a better way. So I sought out to build the worlds greatest pivottable that allowed me to update it all with just 1 click…and after weeks and weeks of building, it’s finally ready. But this isn't just another pivot table tutorial. I utilized Claude and ChatGPT as my personal tutor to learn things I had NO IDEA were possible. The result? A complete system that transforms financial analysis forever. You can analyze your entire profit and loss and drill down to multiple levels all the way to your transactions. Compare values against prior period, prior year, and even your budget - all updating with just ONE click. ?? Want to the template + step by step cheat sheet to learn exactly how I built this? You can find everything in the description of the video down below! What makes it special? ? Analyze data for different time periods with just ONE click ? Compare values against prior period, prior year, and budget ? Complete drill-down functionality to transaction level ? Built with Power Query and Power Pivot ? Developed with AI assistance for maximum efficiency Here's exactly what you're getting ?? ?? PIVOT TABLE #1: The One-Click Wonder - Filter by ANY period instantly (YTD, TTM, Current Month) - Drill down to transaction-level detail - Department filtering built-in - Clean, professional design ready for presentation ?? PIVOT TABLE #2: The Comparison Machine - Prior Period analysis at your fingertips - Prior Year comparisons instantly - Variance $ and % calculated automatically - Perfect for month-over-month analysis ?? PIVOT TABLE #3: Budget vs Actuals Masterpiece - Compare actuals against budget effortlessly - Smart variance calculations (positive = good!) - Department-level budgeting included - Ready for your next board meeting The best part? I'm not just giving you the file - I'm sharing the entire setup. Every Power Query transformation, every DAX measure, every AI-assisted optimization. You're getting the complete package. If you understand the concepts from this file, you'll do amazing things with your job. Want to see exactly how it works? Watch the full tutorial here: https://lnkd.in/ekwkkjdu

I interviewed the WORLDS BEST in Excel ?? My goal was to find out one thing: How do you become an Excel pro? This was the first time I ever interviewed this many people on camera, and here’s what I found: 1?? EXCEL ISN'T TAUGHT IN SCHOOL You can't get a degree in Excel or become an Excel PhD. Most pros started by being thrown into real-world situations. Some learned from content creators, others from forums and online resources. But the most impressive path? Those who developed critical thinking skills by researching solutions to their own problems. 2?? YOUR FIRST STEP IS TO LEARN EVERYTHING EXCEL HAS TO OFFER Start by understanding the full scope of Excel's capabilities. The ribbon contains everything you need - each tab serves a specific purpose. From the Home tab for basics to the Developer tab for advanced features. Master each section: Insert for visuals, Page Layout for presentation, Formulas for calculations, Data for organization, and Review for collaboration. 3?? FUNCTIONS POWER EXCEL The masters agree - functions are Excel's engine. Start with the essentials: IF, SUMIFS, COUNTIFS, EOMONTH, and XLOOKUP (forget VLOOKUP!). Then graduate to dynamic arrays and SPILL functions. The game-changer? LAMBDA functions - they're functions that write functions. Mind-blowing, right? 4?? UNDERSTAND DATA STRUCTURES The pros emphasized that data structure knowledge is crucial. Know the difference between structured and unstructured data. Tables, Power Query, and Pivot Tables become powerful tools once you grasp data structures. As one expert put it, "structured data is one of the easiest ways to manipulate data." 5?? DON'T JUST TELL, SHOW Data visualization is where Excel masters shine. They shared eight creative ways to present budget vs. actuals: from custom KPI boxes to donut charts, speedometers to waterfall charts. Remember - working with data is one thing, presenting it effectively is another skill entirely. 6?? GET EFFICIENT The final piece? Keyboard shortcuts. The experts swear by them: CTRL + arrows to jump between cells ALT + = to AutoSum CTRL + Space to select columns CTRL + SHIFT + L for filtering ALT + W + FF to freeze panes This conference was one of the best experiences in my career, and I’m so grateful to Financial Modeling World Cup and Microsoft Excel Collegiate Challenge for hosting it, along with everyone who I met and had a chance to interview: ? Paul Barnhurst → Excel MVP, the FP&A guy and podcasting legend ? Diarmuid Early → 2x Financial Modeling World Champion ? Celia Alves → Excel MVP and incredible content creator ? David Fortin, CPA → Excel MVP, the one and only MC from the conference, Piggy Bank Accountant ? David Brown → Excel MVP, Associate professor of Finance, MECC founder ? Oz du Soleil → Excel MVP, world-class commentator, Excel on Fire ?? ? Danielle Stein Fairhurst → Excel MVP, Dynamic Arrays Pro, Plum solutions PS: Want to watch the FULL video right here https://lnkd.in/d56PxJHf

Excel’s Copilot just released some CRAZY new features So I spent the entire weekend testing it…and I'm pretty blown away. And today, I'm going to show you exactly what it can (and can't) do. ??? Watch the FULL video tutorial via the link at the end of this post ?? WHAT IS COPILOT? Think of Copilot as an AI chatbot right in your Excel file… only instead of just chatting with you, it can actually comprehend and interact with your data. ?? HOW TO GET STARTED I found this a bit tricky - you won't find it in your add-ins menu. Instead, head over to Microsoft's website and download + follow their installation process. Once installed, you'll see the Copilot button in your home tab and whenever you right click ?? 8 GAME-CHANGING THINGS YOU CAN DO WITH COPILOT 1. Create PivotTables in Seconds No more manual dragging and dropping. Just tell Copilot what you want to analyze, and it creates your PivotTable. 2. Generate Charts Looking to tell a Story? Want to visualize your data? Copilot will suggest the most appropriate chart type and create it instantly. 3. Filter & Sort This is pretty cool…copilot will actually filter / sort your table. 4. Write Complex Formulas This may be the most powerful piece of copilot - here you can get the syntax for a custom formula, learn how the formula works or even better…have copilot add it directly to your Excel file. 5. Custom Formatting Another really neat feature - I took the column in an AP ledger showing the days outstanding as a number and added “days” to it via custom formatting. 6. Read Through Text and Summarize What good is an LLM if it can’t read and analyze data? I took another column from an AP ledger…”Delivery feedback”, and summarized how well each shipment was delivered 7. Clean Messy Data From text-to-columns to proper casing, Copilot makes data cleaning a breeze. 8. Comprehend and Analyze Data This may be the most important - an AI that actually has the context on your data. I was even able to get it to act like an FP&A analyst and forecast out 2024 salary by prorating start dates. ?? BUT WILL IT REPLACE US? Updates like these are equally exciting as well as terrifying. Will I still have a business if copilot can eventually replace what I do? Here's what I've learned after extensive testing: 1. Copilot excels with structured data but struggles with unstructured information 2. It can't handle complex visualizations like waterfall charts 3. It often gets "tired" during extended sessions (apparently, AI needs coffee breaks too) So, am I worried about being replaced? Not yet. While Copilot is impressive, it's still just a tool - albeit a powerful one. It's fantastic for those new to Excel, but experienced users will see it as an enhancer rather than a replacement. === Is Copilot part of your Excel workflow yet? Share your thoughts in the comments below ?? PS: Watch the FULL tutorial here: https://lnkd.in/eSGPXUYr

I use these Excel functions EVERY single day Credits to Josh Aharonoff, CPA, follow him for more content. ---- I use these Excel functions EVERY single day in my fractional CFO work ?? While there are hundreds of Excel functions available, there are only a select few that I use almost every day...9 to be exact. ?? Join me LIVE in December at the Active Cell conference with countless Microsoft MVPs and world Class Financial Modelers ?? Grab 10% off on your ticket here ?? https://lnkd.in/dGhDhYew ??? Today, we're going to do a deep dive on these 9 functions, and how you can use them in your work too. ?? ?? SUMIFS This one is my go-to, and is what it sounds like. It "SUMS" data in a range "IF" it meets a certain level of criteria. For example…I'll often times condense a profit & loss into a summarized profit and loss using SUMIFS. ?? INDEX MATCH The Dynamic Duo It in essence combines both the Index and the Match value, allowing you to have complete flexibility over your range with dynamic inputs. Many feel that XLOOKUP is superior, and I won't fight it - it's all a matter of personal preference! ?? XLOOKUP The beauty of Xlookup is it replaces the limitations you have with Vlookup and Hlookup (please, I'm begging you...stop using Vlookup)… allowing you to look up values in a 2 dimension table, both top to bottom?? and ?? left to right ?? COUNTIFS This one is not nearly used enough…it's extra useful when you're comparing 2 datasets. ?? IF The Logic Master The premise is extremely simple… IF my condition is TRUE, return this result. Otherwise (ie it's false), return this other value. ?? EOMONTH The Date Wizard Almost any financial report will show your dates going across this way ?? and your accounts going across this way ?? You can extend the dates by a month by using =EOMONTH(last period,1)… or by a quarter using =EOMONTH(last period,3)… or a year with =EOMONTH(last period,12) ?? UNIQUE The Duplicate Destroyer Duplicates can cause a headache ?? and while there are numerous ways to remove duplicates…UNIQUE is actually my favorite. ?? FILTER The Data Slicer OK, this one is a top favorite for me. With the FILTER function, you can set all sorts of conditions, and return a whole table based off of those conditions. Combine that with a SORT function to return your results in the order you need. ?? SUMPRODUCT The Hidden Champion. Most professionals simply don't realize its full potential. In fact…it's so powerful, that it replaces the need for each of these functions: XLOOKUP, SUMIFS, COUNTIFS, AVERAGEIFS, and can do things that these functions can't. And you can tap into this power once you start using it together with some of the other formulas I showed you today. But this is a topic for another time === Which one is your favorite? And which would you add? Let me know in the comments below ?? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Build a DYNAMIC Actuals vs Projections Chart in Excel Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- Build a DYNAMIC Actuals vs Projections Chart in Excel ?? Save time each month with excels automation Adding "Actuals" vs "Projections" to your dashboards is CRUCIAL when forecasting & showcasing your work to others. I used to do this manually each month by creating a shape in Excel…adding some text, and moving the marker to the breaking period from when Actuals end and Projections start. Well today, I’m excited to share a way you can do this automatically, saving you tons of time ?? ?? STEP 1: STRUCTURE YOUR DATA CORRECTLY If you’ve ever created an Excel chart, you’ll know that data structure is key. That means getting your data in a structure in which your data points are ideally in a TABULAR format, with dates & values going vertically. If you have data going horizontally (like most financial reports), you can use a simple lookup function to pull in your values into this new structure ?? STEP 2: ADD A NEW COLUMN AND CONCATENATE DATES WITH THE WORD “ACTUAL” OR “PROJECTED” Now let’s add “Actual” or “Projection” in our date section. Add a dropdown for your latest month of actuals, and then add a column in your table to calculate whether you are in an actuals month or a projections month. Use a concat function or “&” operator to join this value with the date, and use the TEXT function to format the date for “MMM-YY”. ?? STEP 3: ADD A DYNAMIC MARKER FOR "← ACTUAL | PROJECTION →" The last step is to add our dynamic marker that will adjust the position depending on what month is our breaking point between actuals and projections. To do that, we’ll add a new column that will populate the MAX value of our range only in the last month of actuals. This will help with the position of our marker so that it shows at the top of our values. Add one more column that will display the text “ ← ACTUAL | PROJECTION →” when we are in this month. OK now here’s the key…when you add this new MAX column to your chart, click add error bars. Then edit the error bars, and choose “Minus” and “No Cap”. Then choose custom, and specify values for the range, and reference the MAX range. Now you have an error bar from top to bottom in a nice thin line. Add a callout to the MAX column series, and make it equal to the text column that reads “← ACTUAL | PROJECTION →” Remove the fill from the bar chart so that the error bar only remains, and you’re done! === There are a lot of steps to doing this correctly…and it’s not so easy to explain it all with just text and 1 picture on LinkedIn ?? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Do you use Excel? Then you need to read this document ?? Credits to Chris Reilly follow him for more content. The original post ---- Do you use Excel? Then you need to read this document ?? Today I'm talking best practices. Specifically: model formatting. ...??????'?? ???????? ??????????????? Everything you buy at the store comes with an instruction manual. But your financial model (the tool that manages a hundred-million-dollar business) doesn't. Let's change that today. ~~~ I put this formatting guide on the very first page of the model (just like an instruction manual). ???????? ???????? ??????????????: - Formulas - Hardcodes - Inputs - Links - Checks ???????????? ??????????????: - Work-in-progress - Unique formula - Document tie-out ~~~ ????????'?? ?????? ??????: You ??????'?? have to follow this exactly (but you can if you want). Ultimately, just do what works for you. REMINDER — make sure to ?????????????? ???????? ?????????? ???? ?????? ?????????? ???????? ???? ???????? ??????????. That way, when someone opens the file it's the first thing they see. It might seem unnecessary, sure, I get that. But these little steps go a long way in making your file easy to understand, especially when you hand it to a new user. ---- Follow our page Excel for CFOs to learn more about Excel in Accounting.

The Google Sheets Cheat Sheet Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- The Google Sheets Cheat Sheet Learn EVERYTHING you can do in Google Sheets ?? Bookmark this and save TONS of time to boost your productivity If you’re like me, you use Google Sheets all the time in you job. But do you know just how powerful it really is? Here…let me show you ?? ?? Get the Google Sheets Cheat Sheet for FREE in the next 48 hours ?? https://lnkd.in/eeBXcEVv 1?? ?? Data Entry & Organization: Google Sheets is a tool used for data management…similar to Excel. The main difference is that the information is stored in the cloud, giving you greater capabilities for accessing your data on the go, and collaboration. Some popular features: - Create tables to organize data efficiently (though you can’t create dynamic tables like you can in excel). - Customize cell formatting for dates, currency, and more. - Ensure data accuracy with validation rules. - Sort and filter data seamlessly for analysis. 2?? ?? Formulas & Functions: What good would a spreadsheet tool be without the ability to implement formulas and functions? You’ll find close to all formulas that exist in Excel in Google Sheets, such as… - Arithmetic operations - SUM, AVERAGE, MAX, MIN. - Conditional statements and lookup functions - IF, VLOOKUP, INDEX/MATCH. - Dates and times - EOMONTH, EDATE, YEAR, MONTH 3?? ?? Advanced Data Analysis: Google Sheets gives you the ability to go deep with your data, whether that be via a PivotTable, a chart, or a complex function. ?? The processing power of Google Sheets is far more limited that Excel…be careful not to let your file get too large, or you’ll have performance issues. Despite the advanced data analysis capabilities, you also can’t use my favorite excel feature - power query! 4?? ?? Collaboration & Sharing: Here comes the heart and soul of Google Sheets. With Google Sheets you can: - Collaborate in real-time with multiple users editing the same spreadsheet simultaneously. - Add comments and notes to facilitate discussions and feedback. - Review previous versions and restore changes using Version History. You technically can also do much of this with Excel, but many of these would require you to be connected to Microsoft Onedrive, and I have found that the experience isn’t nearly as smooth. 5?? ?? Automation with Apps Script: Google Sheets has a lot of neat functionality when it comes to creating apps with scripts. - Extend Google Sheets' functionality with custom scripts using Apps Script. - Automate repetitive tasks and workflows to save time. - Integrate Google Sheets with other Google services and external APIs for seamless data management === I use Google Sheets all the time…and while I am a bigger fan of Excel, I know that Google Sheets is here to stay What’s your favorite feature in Google Sheets? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

Function signature Credits to Matthew Herbert, follow him for more content. The original post ---- Do you know what the anatomy of a function is? (Maybe you’ve heard the term “function signature”)? Okay, someone probably never taught you that "SUM(number1, [number2], …)" is a function signature. It’s called a signature because it’s not dissimilar to how you sign your name – it’s a unique way to represent a name ?? It’s distinct and can’t be repeated; no forgeries in Excel ??. A function’s anatomy includes a scope, name, parameter(s), and return type. ?? Scope refers to whether the function is visible or not. Because SUM is visible in the spreadsheet, its scope is public, not private. You can’t see anything that’s private; it’s hidden. ?? Name refers to the function’s name, or SUM in this illustration. ?? Parameters are always enclosed in parentheses (). SUM has 255 parameters: number1 to number255. (This is 2^8-1, which has its own significance). Parameters are either required or optional; optional parameters are enclosed in square brackets [ ]. number1 is required, number2 is optional, etc. As a side note, some functions have no parameters, such as TODAY(). (TODAY is a “parameterless” function). ?? Return type helps indicate the type of information returned from the function. (I’ll ignore the Variant data type for now to keep things simple). SUM returns a number. Some other examples include ISTEXT, which returns a logical (true/false), and ROMAN, which returns text. (Side note: Excel may not directly indicate the return type for a function in the help files). Arguments are the data passed into each parameter. Parameters also have a data type, but you will need the function’s help file to understand the information each parameter accepts. For example, the help file for SUM says that number1 is “the first number you want to add.” So, number1 needs a number(s). (Side note: Excel typically performs “implicit” conversion on your behalf, meaning it may transform the argument’s data type to match the parameter’s data type). Whenever you use a function, you proceed as follows: ? start with the equal sign (=), ? add the function’s name, ? add parentheses (), and ? input the arguments for each parameter (again, parameters are located inside the parentheses) =SUM(5, 6) returns 11 ?? 5 is the argument for the number1 parameter (required) ?? 6 is the argument for the number2 parameter (optional) Knowing the function’s anatomy enables you to correctly use and apply any of Excel’s 450+ functions! It also helps you explore creating your own functions using LAMBDA (or VBA), which is a topic for another day ??. When I first learned functions in Excel, this fundamental knowledge was never explained to me. It came much later in my Excel journey, and I wish I had known it sooner. Let me know in the comments if this was helpful. ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.