My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
With Microsoft 365 we can now include images in Excel PivotTables, as well as built in data types like Stock and Geography, Power Query custom data types and Power BI Organisational Data Types. This opens up the ability to create dynamic and visually rich PivotTables like never before. When combined with some slick formatting we …
Annoyingly, there’s a shortcut for Merge & Center on the Ribbon, but nothing for Center Across Selection which is far superior. Center Across Selection achieves the same result but doesn’t cause the rude interruptions to selecting ranges that Merge & Center does. To remedy this, I consulted ChatGPT to write a Center Across Selection shortcut …
Dashboards have limited space which is why an Excel scroll and sort table is super handy. Scroll and sort tables allow you to embed an extract of data from a larger table in your Dashboard. Users can scroll to see all the records and toggle which column to sort by and set the sort order, …
Excel Formatting Best Practices Proper use of Excel formatting can significantly impact the speed and clarity in which users can work with and interpret your spreadsheets. It enables you to highlight essential information, direct users, and encode data. Effective formatting subtly enhances usability, while poor formatting can be a distraction. These are my top 10 …
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 …
The Advanced Formula Environment (AFE) is part of the Excel Labs add-in and 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, it can write them for you, as you’ll see! You may have seen me use it a few times …
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 …
