No matter where you look, there is a consistent message that it is bad to merge cells in Excel; instead, we should use Center Across Selection. Whenever my friend Richard Sumner posts about his love of merged cells on LinkedIn, he is always met with many replies stating that merging cells is terrible. But here Read More
Category: Blog
How to make cross filter visuals in Excel (amazing interactive visuals)
Power BI has a really nice way of cross-filtering visuals. When you click on a visual, it highlights the selected item in other charts while also retaining the total value in a lighter color. Can we cross filter visuals in Excel? No. But we can create something pretty close. Power BI Example: This post was Read More
Power Query: Rename columns in nested tables from list (without errors)
Power Query is a nightmare when we want to combine data with different column names, and even worse when those columns are not in the same order. Can we even combine that data? You bet we can! We’re looking at that today; using Power Query to rename columns in nested tables based on a list, Read More
How to create Multiple PDFs from Excel (Office Scripts + Power Automate)
Creating multiple PDFs based on a list is a common use case in Excel. I’m asked about it regularly. A few weeks ago, I received this question about our Office Scripts course: Before I start the course on Office Scripts, I was wondering if it is possible to loop through a spreadsheet and with each Read More
How to add fiscal Month, Quarter or Year Column in Power Query
Over the last few months, I’ve been asked several times how to add a fiscal month, fiscal quarter, or fiscal year column in Power Query. So, I decided to note down the method; therefore, I don’t have to keep reminding myself how I did it last time. The good news is that it’s reasonably straightforward. Read More
Power Query: How to multiple replace values based on a list
A few weeks ago, I posted about using Excel’s REDUCE and SUBSTITUTE functions to perform multiple find and replace actions to create dynamic text. But what if we want multiple find and replace during the data-cleaning process? In that scenario, we need to use Power Query to replace values based on a list. So, in Read More
How to transform nested tables in Power Query (without coding)
In Power Query, it is common to have nested Tables. These are Tables contained within a column, where each row contains a separate sub-Table. Sometimes we just want to expand the data; that is easy. But sometimes, we need to transform the data in the nested Table before expanding; that is NOT so easy. So, Read More
How to Find & Replace multiple words in Excel: REDUCE & SUBSTITUTE
As many regular readers will know, I’m a big fan of writing text dynamically. This means we don’t need to manually update text or headings in our reports. Instead, the text calculates and updates automatically. So, today I want to share how to Find and Replace multiple words in Excel using the REDUCE and SUBSTITUTE Read More
How to make an interactive view-only dashboard from Excel
Often as Excel users, we can look across at Power BI and see the convenience of having a sharable web-based dashboard. It’s nice to have each person viewing a dashboard without affecting other users. We don’t have anything similar in Excel… or do we? Normally, if we share an Excel Workbook saved in OneDrive or Read More
Power Query: How to Combine files based on a list
Power Query has a really powerful feature that lets us combine all the files in a folder. However, sometimes the files are not contained in a single folder. Sometimes they are spread out in multiple places. How can we deal with that? That’s what we are looking at in this post: how to combine files Read More