Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
Excel can now write formulas for you. Simply give it an example or two and it’ll suggest a formula to complete the task. Remember how amazing Flash Fill was when you saw it for the first time? Well, Formula by Example is 10x better because it isn’t a one time and done solution. Being a …
Excel linear regression is easy with the built-in tools. In this tutorial, we'll explore what linear regression is, why it's useful, and the Excel tools that enable you to do it with the click of a few buttons. Table of Contents Video - Excel Linear Regression Download the Workbook What is Linear Regression and its …
Do you find yourself frustrated by sluggish Excel files that take forever to load or recalculate? If so, you're not alone. Slow Excel files can be a common headache for anyone who uses spreadsheets regularly. Fortunately, there are many tips and tricks you can use to improve Excel performance. In this tutorial, we'll identify the …
One of my most popular tutorials has been my Excel Project Management dashboard with nearly 2M views to date. However, I often get asked if there’s a way to switch between viewing the projects by day, week or month. The answer is yes and, in this tutorial I’m going to show you how we can …
In today's world, where remote work has become increasingly common, the need to securely share files with multiple people has never been greater. Whether your file is in the cloud or a local network drive, Microsoft has tools to enable you to prevent unauthorised opening, editing and downloading of your data. Table of Contents How …
Excel check boxes are a type of form control that can be added to a spreadsheet with just a few clicks to create an interactive list of items that can be checked off. You can also link them to formulas to dynamically turn off and on items you want displayed in a chart or conditional …
Power Query if statements, nested if, 'if or' and 'if and' statements are a bit different to writing IF formulas in Excel, but once you get the hang of them they’re pretty easy. In fact, the Power Query IF formula, IF OR, and IF AND are even easier than the Excel equivalents. Table of Contents …
When working in multiple Excel files we can waste a lot of time flitting back and forth between them. In this tutorial you'll learn some game-changing tips that are guaranteed to streamline your workflow, making it easier than ever to navigate, arrange, and seamlessly work between multiple Excel files. Watch the Video Navigating Multiple Excel …
When building reports, it's important that people not only understand what you're saying but also remember it. Picture fill Excel charts are a simple way to make your data visualizations more captivating and memorable. They’re suitable for infographics and fun reports, but I wouldn’t necessarily use them in corporate style reports. You can create them …
Writing Excel formulas is one of the most important tasks to master in Excel, but it can be a mine field trying to get your head around all the functions available and knowing which one to use for the task at hand. But now with this free AI-aided formula editor you can have it write …
Everyone’s talking about using ChatGPT for Excel and how it can improve your productivity with claims it’ll make you n times more productive and the like. I can tell you it’s not all it’s cracked up to be! If you want to be more productive in Excel, learn Power Query. That’s not to say ChatGPT …
In this tutorial I’m going to share with you some hidden Excel chart techniques that will make your reports stand out and draw your readers in. These techniques are often used for infographic style reports, but that’s not to say you can’t use them in dashboards or even PowerPoint presentations. Infographics are a one off …
Designing dashboards and reports can be a time consuming task, particularly if you’re not artistically inclined. In 60 seconds, AI generated four dashboard designs for me for free! All I had to do was give it a sentence worth of instructions and it returned the four bicycle sales dashboards you see below. I think you’ll …
Excel templates can save you time designing, setting up and standardising forms and reports. There are hundreds of free templates covering many types of documents and themes available for all Excel users. Excel templates work in the Excel desktop app and Excel Online. Watch the Video Free Excel Templates You’ll find free Excel templates via …
Excel Speedometer charts or gauge charts as they’re also known, have a worse reputation in data visualization circles than the disdained pie chart. It’s primarily because they take up a huge amount of space and ink relative to the amount of information they convey. In this tutorial we’re going to learn how to build Excel …
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 LAMBDA functions, 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 …
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 …
If you’re going to build dark themed Excel dashboards, then you need to have some tricks up your sleeve in order to give them that wow factor. In this tutorial I’m going to show you some techniques that work well with dark colours that will have your data popping. In the video below I build …
Keyboard shortcuts are great, but there are also hidden Excel double click features and shortcuts available for mouse users. Which means everyone, because we all use the mouse, even those hardcore keyboard shortcut fans. Watch the Video Download the Quick Reference Cheat Sheet [FREE] Enter your email address below to download the sample workbook. Get …
Excel Burn Down and Burn Up Charts are easy to make with line or scatter charts. If you’re familiar with Agile Project Management, then you’ll know that these charts are useful for monitoring the progress of a project against expectations. While the terminology is specific to Agile Project Management, the concept is relevant to managing …
Excel WeePeople font charts enable you to build interesting variations on waffle, bar and column charts, to name a few. They’re great for visualising demographic data, survey data and more. The free WeePeople font, courtesy of ProPublica, consists of 20 different people silhouettes hand drawn by Alberto Cairo. The last 6 letters of the alphabet …
One of the things that makes Excel stand apart from other reporting tools is its immense flexibility. With some tinkering you can make charts and graphics that don’t look anything like Excel. In this post I’m going to take you through the steps to create Excel dot map charts. And while there’s no built in …
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 …
