This post will demonstrate how to insert the same few items into a list and create a new row for each item (or each combination, if multiple items). For example, let’s say we have a list of some sort … we’ll use a list of T-Shirts for this illustration. We have a few T-Shirt options […]
In this post, we’ll discuss a way to simulate using VLOOKUP to return multiple matching rows and/or columns. What do you mean by “simulate” Jeff? Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any […]
A few years ago, I wrote a post about how to perform fuzzy lookups using the “Fuzzy Lookup Add-In for Excel.” However, this capability is now available in Power Query!! Wait, what? Yes! Performing a fuzzy match is so much easier and far more intuitive in Power Query. This post walks through the basics … […]
Microsoft recently updated the built-in tools for retrieving stock quotes into Excel. If you’re typing closing prices into Excel manually, my recent California CPA Magazine article may be able to help you get it done faster. Specifically, it talks about how the Stock data type retrieves current quotes and related information, and how Power Query […]
In this post, we are going to use the data model to improve our reporting process. This is the fourth post in the Stop Wasting Time series, and this is where everything we’ve learned comes together. By the time we have finished this post, updating our report will be extremely easy. In fact, it will […]
We are in the middle of a series called Stop Wasting Time, where we are learning about Excel features that help us improve how we update a report. In the beginning of our journey, we were updating the report manually. Now, as we’ve learned more, we’ve been able to automate parts of it. That is, […]
This is the second post in the Stop Wasting Time series. In this post, we learn how Power Query can help us update our report in less time. After all, why would we want to spend more time than necessary updating it? That is called “wasting time” and we just don’t like wasting such a […]
A few weeks ago I wrote a blog post on how to create a graphical calendar using a PivotTable. I received a few questions about how to insert event names into the calendar. Generally, PivotTable value fields support numeric values (such as day 1, 2, 3), and not text values (such as Filing Deadline or […]
You’ve heard the terms “Power BI,” “Power Query” and “Power Pivot,” but maybe aren’t sure what they are. Good news! They are free tools from Microsoft and this article will talk you through them. And, while we’re at it, we’ll also talk about Pivot Tables and Pivot Charts. Power and Pivot
Let’s say you have a single-column list of transactions. You want to add up the amount values, which are on every say 5th row. This is a perfect task for Power Query. So, in this post, I’ll demonstrate how to set up a query that removes and keeps a defined number of rows in a […]