Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
A data analyst gathers, cleans, models, analyses, and interprets data. The gathering and cleaning should be done with Power Query. The modelling and analysis can be done with a combination of PivotTables/Power Pivot and functions. The other day I stumbled upon a post claiming to list the top Excel functions for data analysts. I disagreed …
Excel’s new Advanced Formula Environment (AFE) is still in its early stages of development, but it’s already very useful, particularly if you’re scared to write your own LAMBDAs, as you’ll see it can write them for you! You may have seen me use it a few times in tutorials lately, but recently it has undergone …
Excel Formula Writing Tips, Tricks and Tools that will change how you work in Excel Formulas are THE MOST IMPORTANT EXCEL SKILL you can master, but when you start nesting functions, things can quickly get out of hand. In this lesson I’m going to cover some little known Excel formula writing tips, tricks and tools …
Microsoft recently released 11 new Excel functions for shaping arrays (data). I already covered VSTACK and HSTACK which was super popular, and in this post I’m going to use some of the other new functions to do things that used to require ninja level function wrangling. You can see the individual tutorials covered in the …
We can learn a lot from looking at what others do in Excel, both the good and the not so good. Last week one of my course members asked me to help them understand what a nested IF formula was doing in a file they’d inherited in their new job. It contained 15 nested IF …
The new Excel IMAGE function enables you to insert images into cells with a formula. From there you can resize them, sort, filter and more. The IMAGE function supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP file types. The IMAGE function is currently in the beta testing phase so the function arguments may change …
Excel VSTACK and HSTACK functions are just two of a raft of new text manipulation functions available to Microsoft 365 users. I wrote about text splitting functions, TEXTSPLIT, TEXTBEFORE and TEXTAFTER a while back. The new VSTACK and HSTACK functions work to combine arrays arranged vertically (VSTACK) or horizontally (HSTACK) into a new single array. …
I often get asked how to identify overlapping dates and times in Excel. Excel doesn’t have an ‘overlap’ function, but with SUMPRODUCT we can identify dates or times that fall within the same range. For example, let’s say you have list of appointments scheduled for the month. If you plot them visually in a calendar …
It’s been a long wait, but we finally have some exciting new Excel text functions that are going to make life so much easier. In this tutorial I’m going to focus on TEXTAFTER, TEXTBEFORE and most exciting, TEXTSPLIT. The first two functions are fairly self-explanatory, and so is TEXTSPLIT to a degree. However, in this …
Most Excel users know the basic functions like SUM, COUNT, AVERAGE etc. Therefore, in this tutorial I’m going to cover the 10 most important intermediate level Excel functions every Excel user should know. Watch the video for an introduction to each function, and then dive deeper by following the links below. Note: there’s technically 17 …
