Recommended Excel Books
Power BI for the Excel Data Analyst e-Book by Wyn Hopkins.
A step-by-step guide on how to use Power BI. Written with Excel fans in mind, but with every Power BI student at its heart.
There are amazing books on DAX, Power Query, Visualisations, and more. This book is the one you should read before reading those. I wanted this book to cover the whole Power BI experience. Starting with Power Query, moving to data modelling, DAX, publishing and sharing, report design, and other additional important features. Mastering Power BI is a multi-year journey but learning the best practice principles from this book will give you a jump start and a solid foundation on which to build.
Microsoft 365 Excel: The Only App That Matters by Mike Girvin.
Few people know Excel as Mike does, and his YouTube channel – Excel Is Fun – has been the Excel school for many of us Excel Experts. It was one of the very first Excel channels I discovered, and since then, I have been marvelled by and grateful for the detail, kindness and generosity with which Mike teaches everything about Excel.
Excel 365 is dramatically more powerful and easier to use than any other spreadsheet version in history. The book starts from the very basics and fundamentals about Excel and moves to an advanced level by telling a logical story about using Excel and Power BI to build models, make calculations and perform data analysis. With this book and The Only App That Matters, you will have the power to create any solution that you can imagine efficiently! If there is a book to be called “the Excel bible,” this is the one! Almost 800 pages! Wow! There’s probably nothing about Excel that you can’t find in there.
![View this email in your browser Hello, , What a busy month February has been, and it is not over yet! I’ve had so much fun presenting at the Global Excel Summit 2023, hosting an Excel Battle at that same event, and presenting at the Vancouver Power BI & Modern Excel Meetup Group. Microsoft just launched a new Power Query experience in Excel for Mac (YES!!! 🍾🥂), added several improvements to Excel and recently announced their re-invented Bing search engine based on AI. They have been busy too! While recording new lessons for my course and new free content for social media, I continue to develop solutions and automations to solve my clients’ specific needs. If you want to learn my techniques to automate routine Excel reporting procedures, check my training program here. You can also read this newsletter and its previous issues at solveandexcel.ca/excel_news/ WHAT I HAVE BEEN UP TO See how to create dependent drop-down lists using named ranges and the dynamic array functionality in Excel. This is extremely useful to ensure data is entered properly. The great thing about this solution is that you can create as many categories and corresponding subcategories as you want, and your dropdown lists will all be updated and maintained accurately. For more tutorials, follow my content on a wide range of topics on Excel, Pivot Tables, Power Query and VBA Automation, and much more: FREE TUTORIALS on my YouTube Channel, Facebook, Twitter, Instagram, LinkedIn, LinkedIn Personal GUEST PRESENTER The Global Excel Summit has become a reference for the Excel community. The 3rd edition was a great success (again!), with fabulous sessions and learning and networking opportunities. I was thrilled to present for the 3rd time at this event. In my session, “The magic of Names in Excel,” I gave examples of why I use Named Ranges so much in the Excel solutions I develop. Learn here how to access the session replays. I felt like a true TV sports commentator co-hosting an Excel Battle the Global Excel Summit, organized by the Financial Modeling Word Cup. The problem was so much fun to solve! I did not manage to do it in 30 minutes, though, as the goal was. So, all my admiration and respect go out to all the competitors, especially the ones who achieved full marks! Learn here how to access the case files and the battle video recording. On February 2nd, I presented Dynamic Array Functions: a Must-Know in Excel to the Vancouver Power BI & Modern Excel Meet-up Group. I demonstrated some business cases where dynamic array functions shine in making our life so much easier. EXCEL IMPROVEMENTS AND NEW FEATURES Now, let’s dive into the Excel News, starting by saying a big “Thank You!” to David Benaim, who has helped curate some of the information in this newsletter. January was the month where the Semi-Annual Channel got their updates. Here’s what is new for them: Copy data from status bar Smooth scrolling Filter has been sped up Create linked data types in Power Query with the Advanced Dialog LAMBDA and the 7 Helper Functions You can find here a summary of what was announced as New for Excel in January The Excel features flyer at the bottom of that page allows you to check if a specific feature is in your version of Excel. Below are more details about some of the features. Please keep in mind that some feature posts were not updated to show the Excel versions where the feature is currently implemented. Improvements to Pivot Tables [Excel for Microsoft 365 – Desktop] Dates in Pivots from Power BI now are stored as dates, not strings. PivotTables overlapping with other content now has an improved view. Better tips with PivotTable incompatibility across versions. Automate your tasks with the Power Automate tab [Excel for Windows] [Excel for Mac] A brand-new tab in Excel dedicated to task automation. Power Query Editor and import data from additional sources are now generally available in Excel for Microsoft 365 for Mac [Excel for Mac] Cleaning and shaping data with the Power Query Editor in Excel for Mac now offers almost the same experience as Excel for Windows, including more supported data sources: You can import data into Excel using Power Query from a wide variety of data sources: Excel Workbook, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online List, OData, Blank Table, and Blank Query. More information about the connectors available here Record worksheet actions using Office Scripts. [Excel for Windows - Insiders] [Excel for Mac - Insiders] A macro recorder for Office Scripts now available in the Automate tab mentioned above. Check your formula with value preview tooltips [Excel for Windows - Insiders] [Excel for Mac - Insiders] No need to F9 anymore to check the result of a portion of a formula. You can get it by hovering your mouse on the function component you want to check. This will make editing your formulae much easier! Formula by Example [Excel for the Web] This is like Flash Fill, but so much better! Besides recognizing the pattern on your examples, Excel provides you with a formula, so that your results are dynamic in case you make changes to the original data. Amazing! However, it seems that this feature is currently available for users who have their OneDrive set to US English only. PivotTable show details [Excel for the web] Double-click or right-click and choose Show Details from a value cell in your PivotTable to create a new sheet with the detailed rows for that cell. Another step in making Excel for the Web closer to the desktop version. Other improvements include: Tech News Reinventing search with a new AI-powered Microsoft Bing and Edge, your copilot for the web Microsoft launched an all new, AI-powered Bing search engine and Edge browser, available in preview now at Bing.com, to deliver better search, more complete answers, a new chat experience and the ability to generate content. Microsoft thinks of these tools as an AI copilot for the web. This is live today in limited preview, expanding to millions soon. EXCEL EVENTS MS Excel Toronto NEXT MARCH 8 – (RESCHEDULED date) - What is the DAX CALCULATE Function and how does it work? | Wyn Hopkins Writing DAX is easy, writing the correct formula is hard. Understanding the concepts of how DAX works differently from Excel is one of the biggest hurdles to Power Pivot and Power BI adoption by the Excel crowd. This session will cover how I explain that DAX works to people, especially Excel users. MARCH 22 - Excel for Mac: The Need-to-Know Gotchas and Workarounds |Mike Thomas So you've bought or are considering buying a Mac or maybe you've landed that dream job and found out the company is Mac-only. How can you continue to use your favorite spreadsheet application without those hair-tearing moments? This session, aimed at switchers from the Windows world, will show you the differences, gotchas, and workarounds between Excel on Windows and Excel on the Mac (including Power Query Editor!) CONTENT YOU DON’T WANT TO MISS David Benaim published a video comparing Microsoft Forms vs Google Forms. Extremely useful as some of the differences in features may determine which one of the tools to use for a specific case scenario. Chris Webb posted an interesting item on his Blog this month. Understanding The “A cyclic reference was encountered during evaluation” Error In Power Query In Power BI Or Excel It discusses what causes and what to do if you run into the error message “Expression.Error: A cyclic reference was encountered during evaluation” Imke Feldmann came up with a strategy to Reference an intermediate step from a different query in Power Query “When you reference another query in Power Query you will automatically get the results of the final step. But what if you want to reference a step that sits within that referenced query?” RECOMMENDED BOOKS Power BI for the Excel Data Analyst e-Book by Wyn Hopkins. A step-by-step guide on how to use Power BI. Written with Excel fans in mind, but with every Power BI student at its heart. There are amazing books on DAX, Power Query, Visualisations, and more. This book is the one you should read before reading those. I wanted this book to cover the whole Power BI experience. Starting with Power Query, moving to data modelling, DAX, publishing and sharing, report design, and other additional important features. Mastering Power BI is a multi-year journey but learning the best practice principles from this book will give you a jump start and a solid foundation on which to build. Microsoft 365 Excel: The Only App That Matters by Mike Girvin. | Celia Alves - Solve and Excel](https://solveandexcel.ca/wp-content/uploads/2023/03/Microsoft-365-Excel-The-Only-App-That-Matters-by-Mike-Girvin.jpg)
Guerrilla Data Analysis 3rd Edition by Oz du Soliel and Bill Jelen.
Two of the leading Excel channels on YouTube join forces to combat bad data. This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These are tips honed by Bill Jelen, “MrExcel,” and Oz du Soleil during their careers run as financial analysts charged with taking mainframe data and turning it into useful information quickly. Topics include data quality, validation, perfectly sorting with one click every time, matching lists of data, data consolidation, data subtotals, pivot tables, pivot charts, tables, and much more.
Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas by Alan Murray.
If you want to improve your knowledge and skill about Excel functions, this book is for you. It starts from a basic level but dives into intricate detail to help you build a solid understanding of the fundamentals by learning the “whys” in addition to the “hows.” The book covers from classic to modern Excel functions, including all the most popular functions in Excel including VLOOKUP, SUMPRODUCT, COUNTIFS, MATCH, SORT, SEQUENCE, SORTBY, UNIQUE, XLOOKUP and FILTER.
You will learn how to return multiple results with a single formula. Harness the power of the dynamic array engine and create top N Lists, models and reports that would seem impossible without array formulas.
In the final chapter, we discover the amazing LAMBDA function in Excel to create your custom functions.
In this book, the formulas are not only written to return values to the grid but also for use with other Excel features such as charts and Conditional Formatting, to take them to another level. Practice files are provided to follow all examples shown in the book.
Note: I earn a sales commission on some of the products mentioned in this newsletter. It helps support the free content on this newsletter, my social media accounts, and my website.
Check out our product page .
Check out our product page, where you will find a range of Excel solutions designed to help you cut your daily workload in half. Our products are specifically tailored to automate those repetitive and time-consuming tasks that take up so much of your valuable time..
Imagine being able to complete your work in half the time it currently takes you. Think about all the extra time you could spend on more important tasks or even taking a well-deserved break. Our Excel solutions are designed to help you do just that..