The weighted average (also known as the weighted mean) is a calculation that provides an average where each value does not carry an equal impact on the final result. Using Excel’s AVERAGE function in these scenarios would give a misleading result. Thankfully, we can easily calculate the weighted average in Excel too. When to use Read More
Category: Blog
Power Query – Connect to OneDrive Personal files
I shared how to get data from OneDrive or SharePoint into Power Query in a previous post. Many people noted that the method shown in that post works with OneDrive for Business but doesn’t work with OneDrive Personal. So, in this post, I will demonstrate the technique needed to connect Power Query to files in Read More
Power Query – Connect to OneDrive or SharePoint
In Power Query, when we connect to a local file, it’s reasonably obvious how we do that; it’s built directly into the UI. We just need to navigate to the location… Bam. Done! However, what if we want Power Query to connect to a file on SharePoint or OneDrive? How do we do that? It’s Read More
Unblock Macros downloaded from the internet
Macros are a great way to automate Excel; they give us a lot of power to interact with, not just Excel but other Office applications. Unfortunately, hackers know about this power too, and can use files containing macros to distribute malware to PCs and entire networks. Microsoft are making changes so that any office files Read More
Using an Excel Table within a data validation list
Excel Tables expand automatically whenever new data is added to the bottom. This feature alone makes Tables one of the most powerful tools within the Excel users toolkit. A Table can be used as the source data for a chart and within a named range, both of which benefit from the auto-expand feature. Data validation Read More
GETPIVOTDATA vs CUBE functions
We’ve been able to create PivotTables in Excel since the mid-1990s. Ever since then, people have been asking how to extract data from PivotTables using formulas. As great as PivotTables are for analysis, they are not always the best for presentation, which is why extracting data using a formula is so useful. We currently have Read More
Running total in an Excel Table
Tables are one of the best features of Excel. While it is possible to use the standard cell referencing with a Table, they have their own referencing style called structured references. We have to think a little differently to create a running total in an Excel Table using structured references. We will look at all Read More
Create Slopegraphs in Excel
I’m not going to lie to you, the first time I saw a slopegraph, I was completely underwhelmed, I thought “it’s just a line chart with two points, hardly worth getting excited about”. But the reaction, the first time I used one in a presentation, was unbelievable. People even asked me for my template, so Read More
Excel – Create multiple PDFs based on a list
I was on a call recently where somebody asked if it was possible to create a unique PDF for each item in a list. The answer is “Yes”, so in this post, I want to show you how to do it; how to create multiple PDFs based on a list in Excel. Download the example Read More
Power Query: Stop Excel Table columns resizing
If we use Power Query to load data into an Excel Table, we may encounter an annoying default setting, whereby Table columns resize every time we refresh the query. It isn’t a problem if this is just data used for other calculations. However, if the query is part of your reporting, it may not display Read More
