Looking up data from a table or worksheet is probably the most common activity undertaken by Excel users to create reports. Learning to use VLOOKUP for many is their first taste of Excel’s power. But what about when using Power Query? There isn’t a VLOOKUP function, so how can we lookup a value from another table with Power … Read more
Charts and graphs are one of the best features of Excel; they are very flexible and can be used to make some very advanced visualization. However, this flexibility means there are hundreds of different options. We can create exactly the visualization we want but it can be time-consuming to apply. When we want to apply those hundreds … Read more
There are many circumstances where we receive information with multiple data points inside a single cell. This often occurs when the data’s original intention is slightly different from how we intend to use it. In these circumstances, we often need to split a cell into its constituent parts. This post will look at solving this … Read more
You are probably here because you’ve seen a warning message: Microsoft has blocked macros from running because the source of this file is untrusted. This post provides the background to this message and the steps you should take to enable the file. During 2022, Microsoft introduced new security into the Windows version of Microsoft Excel … Read more
Preparing data is probably the number 1 critical success factor in all data analysis and reporting. If the data layout is wrong, you’ll constantly be fighting Excel; You will have to use very advanced formulas; You will use unnecessary columns and might even duplicate data. Therefore, learning how to unpivot in Excel using Power Query is … Read more
Have you ever experienced the issue where Excel’s scroll bars become tiny? A small movement in the mouse can change the view by hundreds of rows or columns. This makes the worksheet exceptionally difficult to navigate around. The problem might reveal itself with an error message when trying to insert new rows or columns. In … Read more
Interpolation is the process of estimating data points within an existing data set. As this is an Excel blog, then clearly the question we want to answer is: can we interpolate with Excel. This is a common question. In fact, it was the following question from a reader which first made me look into this … Read more
In previous posts in this series, we’ve seen how to import external data from a single file, import all the files in a folder, and import data from a Table or range within the same workbook. But what if we want to import ALL the data within the same workbook? We are looking at that in this … Read more
In this post, we look at 6 easy ways to calculate how many days are between two dates using Microsoft Excel. As often with Excel, there are many ways to achieve a calculation, so we look at the most useful options for calculating days. Dates can be tricky to work with as there are often … Read more
In this post, we use Power Query to import all the files in a folder. We give Power Query a folder path, click a few buttons, and it imports and combines all the files into a single table. It’s like magic! To add another file to the output table, we only have to save a copy of … Read more