Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 …
Custom Excel dashboard backgrounds are super easy to create and can bring a level of polish and professionalism to your reports. Taking your reports from this: To this: And the good news is they’re super easy to create using PowerPoint. Watch the Video Custom Excel Dashboard Backgrounds Step by Step The first step is to …
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. …
S-curve charts are a common tool used in project management to track the cumulative costs, hours, sales etc. over time. The chart forms the shape of an S because typically progress starts slow and ends slow, with acceleration in the middle periods. For example, in the s-curve chart below you can see progress is slow …
In most cases you can easily remove Excel password protection if you’ve forgotten the password. However, the likelihood of your success depends on which password protection method was used, as some of the four options below are more robust than others. Excel password protection is available for: Sheets Workbooks File Open File Edit Each method …
Power Query variables are a great way to store data that can be used multiple times and can be easily updated in one place. There are a few ways you can create variables. 1. From the worksheet, which means you don’t even need to open the query editor to update the variable. This is great …
New to Windows is the ability to import data from a picture to Excel*. No more typing data manually into spreadsheets. It works with image files or screen captures on your clipboard. It's available on the Data tab of the Ribbon > From Picture, as shown below: *Import data from a picture requires a Microsoft …
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 …
