Excel for CFOs

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.

Excel vs Google Sheets - which one do you like better? Credits to Josh Aharonoff, CPA, follow him for more content. ---- Excel vs Google Sheets - which one do you like better? Let’s get ready to rumble ???? Let’s analyze a bunch of common features with each, and see which one wins: ?? PRICING Excel is a Microsoft product, and as such, they want you to buy Microsoft 365. Google sheets is free for those users who have a gmail account, which is free for personal use, and about $6-$10 for a business account. This round goes to Google ??? ?? KEYBOARD SHORTCUTS If you’ve followed my content for a while, you know that I’m a total keyboard shortcuts nerd. When you utilize keyboard shortcuts in excel you can literally FLY and boost your productivity Both Google Sheets and Excel have keyboard shortcuts…but the library in Excel is much larger than google sheets. Google recently created an ability for you to enable keyboard shortcuts that are compatible with excel by hitting CTRL and / Although Google is catching up here, I’m still a bigger fan of Excel’s keyboard shortcuts library. This one goes to Excel ??? ?? PROCESSING POWER OK…this one is a big one. If you’re building a financial model, your file size will grow to a point where it becomes very slow. While this can affect both google sheets and Excel, I’ve found that Excel’s processing power is much more powerful than google sheets. To me, this round is an easy one - Excel take the win ??? ?? DYNAMIC TABLES Dynamic tables are a really powerful tool in Excel - they allow you to take advantage of many automations that you can’t get with a traditional range, such as automatically extending your range as new data gets updated, creating total rows, and much more. Unfortunately, Google Sheets doesn’t have any capabilities on dynamic tables at the moment. Excel wins this round ?? ?? POWER QUERY OK…here comes my favorite Excel feature. With Power Query you can load data from multiple sources, whether that be another excel file, a database, or a source hosted on the web… then TRANSFORM that data with steps that you define in advance, and finally load that data back to Excel. This is like writing VBA only there is a pretty interface where you don’t need to learn any code. Unfortunately, Google Sheet doesn’t have this ability, making this round an easy one for Excel to claim victory ?? ?? SLICERS & TIMELINES Ever use a timeline or slicer in your pivottable? There are a really cool way in which you can toggle to different views with a simple click of a button: Unfortunately, this is another area that Google Sheet doesn’t support, giving Excel yet another victory. === As you can see, Excel takes the victory in almost every category…except for collaboration. And to be entirely honest….collaboration is a REALLY important factor in your spreadsheets. How about you? What has your experience been? ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.

How to do Departmental Forecasting with Power Query Credits to Josh Aharonoff, CPA, follow him for more content. The original post ---- How to do Departmental Forecasting with Power Query If you don’t know Power Query…you are missing out! In my job as a fractional CFO, I’m frequently asked by clients to create a departmental forecast… and by departmental forecast forecast, I mean: ? intake forms for each department head ? sharing existing data with department heads ? consolidating information into a master forecast file When I first managed this task I did it the way most of us would - the manual way OPTION 1: THE MANUAL WAY 1?? Export information from your payroll provider 2?? Delete unneeded columns, add new columns as needed 3?? Filter on the file for each department 4?? Copy and paste the data into separate files for each department head Once the data was sent to each department head, I had to then copy and paste each intake form back into our consolidated file. This process may not seem like a lot of time…but what happened next month when I needed to refresh the data? Now you need to repeat ALL of these steps ?? Surely there has got to be a better way… Well today I’m glad to annouce that there is - and it’s called Power Query OPTION 2: THE EFFICIENT WAY With Power Query, you can create CONNECTIONS to different files that will easily update with just one click of a button. but that’s not all… you can also CLEAN your data by making any sort of TRANSFORMATIONS directly in the power query editor… so that all you need to do is load your new source data, hit one button, and everything is done. That means instead of repeating all of these steps…you can just set it up ONCE, and get your result. That’s pretty amazing…it’s like having an excel assistant working for you where all you need to do is click 1 button ---- Follow our page Excel for CFOs to learn more about Excel in Accounting.

This Excel hack will change the way you build charts (click to see steps ??) Credits to Chris Reilly, follow him for more content. The original post ---- Ever wish you could show one line, but have part of it solid for "Actuals" and the other part dotted for "Forecast"? You can do that using =NA(), here's how: 1. First, put your dates in Column A 2. Then, create two more columns next to the dates: Column B: Actuals Column C: Forecast ~~~ For the Actuals column, use this formula: =IF(the date is ???????? than or equal to my "latest actuals" date, give me the number, else NA()) And for the Forecast column, use this formula: =IF(the date is ?????????????? than or equal to my "latest actuals" date, give me the number, else NA()) ~~~ You should end up with something that looks like the data field in the infographic. *Note: you should see a number for ???????? Actual and Forecast for the "latest actual" date to avoid a gap in the chart (see the 8/24 week in the image) ~~~ Now you can create a line chart by grabbing all 3 fields (Date, Actual, Forecast). And in the data formatting window, you now have two formatting options (Actual or Forecast). So, you can format Actuals to be a solid line, and Forecast to be a dotted line. That's it! Nice little hack to greatly improve the effectiveness of your visual aid. My most common use case is in a cash flow forecast. ---- Follow our page Excel for CFOs to learn more about Excel in Accounting.

Excel formulas giving you a headache? Credits to Mynda Treacy, follow her for more content. The original post ---- Excel formulas giving you a headache? Named ranges are your secret weapon. ?? Get the hi-res cheatsheet: https://lnkd.in/dHK8GRZB Ever stared at a complex Excel formula, utterly lost? You're not alone. But here's the trick the pros use: Named ranges. This cheatsheet breaks down how to: - Make formulas readable at a glance. - Update multiple calculations instantly. - Create dynamic ranges that grow with your data. No more decoding cryptic cell references. Just clear, maintainable spreadsheets. ?What's the most confusing Excel formula you've encountered? Let's simplify it together. ---- Follow our page Excel for CFOs to learn more about Excel in Finance & Accounting.