Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
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 …
Microsoft are massively investing in Excel Online and as a result there are some features that are now better than they are in the desktop version of Excel. The great news is that anyone can use Excel Online for free. You don’t need an Excel license or 365 membership! Watch the Video Download Workbook Enter …
When you hover your mouse over an element in a visual, like a column in a clustered column chart, a tooltip like this appears. The mouse is over the first orange column so the tooltip is showing the country, year, and sale total for that column. The country could be removed as you can tell …
Embedding Excel dashboards in a web page is a great way to securely share reports. All you need to do is save your file on OneDrive or SharePoint and get the embed code. There are different approaches required depending on whether you have OneDrive Personal, or OneDrive for Business or SharePoint. The latter two offer …
Conditional formatting in Power BI visuals allows you to draw attention to, or highlight, data in text or numeric fields using color, icons or data bars. You can also assign URL's to fields to make them clickable and load web pages. Let's look at how to apply all of these different types of conditional formatting. …
I've used a range of techniques to build the interactive Excel student performance dashboard below, which are covered in the video and there are links to further tutorials beyond. Building dashboards for student data requires different techniques to typical Excel dashboards because there is a lot of data to fit into a relatively small space. …
When you import data to Power Query, it's not unusual to end up with a table containing blank rows and columns, like this There are a few ways to get rid of these 'blank' rows and columns, some better than others. Watch the Video Download Example Excel Workbook Enter your email address below to download …
You may have data that is contained in lists or records, or even lists inside records, or records inside lists. I've seen nested data like this come from API's or Sharepoint amongst other sources. In such cases you may want to extract only certain bits of data from the list(s) or record(s) and this blog …
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 …
When working with small multiple charts or panel charts as they’re also known, it’s handy to be able to switch the chart axes between the same axis for each chart and their own axis. Using the same axis enables you to compare the data between the charts because they all use the same scale. Whereas …
Writing DAX is hard enough. If there were some way to make the experience a little easier wouldn't that be great? To that end, below is a list of keyboard shortcuts you can use in the Power BI Desktop DAX Editor. Watch the Video To See What These Shortcuts Do Download DAX Editor Shortcuts PDF …
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 …
I'm going to look at situations that appear to be fairly common: loading data into Power Query where the source data has columns with different names. For example, in the following file received in August 2021, I have data that relates to various items over a 12 month period. What the data is showing is …
We can automatically email reminders from Excel with Power Automate and there’s no programming required. You don’t even need to open the Excel file to trigger the emails to send! In the video and tutorial below we’ll look at a scenario where we have a list of tasks, and we want to automatically remind those …
Design can make or break how well your dashboard communicates your message. Unfortunately, there are some common mistakes I see again and again. At best they make you look like an amateur and at worst they make your report difficult to read and can even be misleading. Thankfully there are some easy things we can …
Some might say that Excel CUBE functions are the new PivotTables because they allow us to build reports based on Power Pivot models in a more free-form fashion. Whereas PivotTables are quite restrictive in their layout and their cells cannot be edited. And just like PivotTables, we can use Slicers to filter CUBE functions for fully …
You can create more interest and give your charts infographic styling when you create Excel charts with Shapes. There’s no limit to the type of shapes you work with, but there are some techniques required to ensure they don’t get distorted when working with data of different values. Watch the Video Download Workbook Enter your …
Typically in Power BI visuals like a matrix or a table, and in pivot tables in Excel, we expect the totals to be the sums of the individual rows. This is probably because we are so used to adding up columns in Excel. In this example image (below) I've written a measure called [Target] to …
DAX is the Power Pivot formula language available in both Power BI and Excel. It stands for Data Analysis eXpression. As you can see in the images below, DAX functions are very similar to Excel functions, so it’s relatively easy for us Excel users to pick up. Below is a screenshot of how DAX formulas …
I know you’re probably thinking that everyone knows how to use the SUM function, but I’m willing to bet that most people don’t know all the shortcuts and tricks I’m about to show you. These are SUM function tricks you can use every day, and some also apply to all Excel functions. Watch the Video …
Below is a list of time saving keyboard shortcuts that you can use in Power Query, in both Excel and Power BI. Watch the video to see me demonstrate how to use them, and download a PDF containing all shortcuts. Watch the Video Download Power Query Shortcuts PDF Enter your email address below to download …
Power Pivot course member, Peter, asked if it was possible to toggle the top n with a Slicer for a Pivot Chart and the answer is yes! With Power Pivot and disconnected tables, we can create interactive charts like this: Watch the Video Download Workbook Enter your email address below to download the sample workbook. …
Manage and monitor where your money goes with this personal finance dashboard. Even if you’re not interested in building your own interactive personal finance dashboard, in this step by step tutorial you’ll learn a load of tips, tricks and techniques you can apply to any dashboard you build. There are no fancy tools or add-ins …
This is my source data and I want to remove the percentages Specifically I want to remove all occurrences of (00%) so my data ends up like this There's no native function in Power Query to do this so I'll have to write my own code. Watch the Video Download Sample Excel Workbook Enter …
If you've only ever used the user interface (Ribbon and menus) to create your transformations then you're missing out on some neat things you can do by using the Power Query Advanced Editor. In this post I'd like to show you what's in the Advanced Editor, how you can write code in it and hopefully …
If you’ve worked with dynamic array formulas*, you’ll have noticed that they’re able to spill the results to multiple cells. For example, in the image below we can see the UNIQUE function has spilled the results to cells G3:G6 which are highlighted with a blue border: With the hash sign in Excel formulas we …
Sometimes you have a table of data that describes a 1 to many relationship. The number of values on the Many side is unknown and can vary. So writing Power Query code to pivot this can be tricky. Let's look at an example. The first column is a list of the office locations of my …
I’ve received a lot of Excel files from our members over the years and I see the same mistakes time and again. In this post I’m going to list the 10 things you should never do in Excel and what to do instead to avoid trouble when working in Excel. Watch the Video Download Workbook …
In this tutorial I’m going to highlight some cool new Excel features that might have slipped under your radar, a couple of which we’ve wanted forever. These are available to Microsoft 365 users, and some are so new that they’re only available to Office Insiders users on the Beta Channel. If ever there was a …
