There are several ways in Excel to find missing IDs (or gaps) in a big list of sequential IDs, such as check numbers or invoice numbers. In this post, we’ll use Power Query so that each time we have a new list, we simply click Refresh. Excel then creates an updated list of the missing […]
As a quick follow up to my previous post about the Stocks data type, I wanted to talk about another data type: Geography. With the Geography data type, we can retrieve rich geographical data into Excel. Let’s check it out. Objective Let’s say we are working on a project and we wanted to create a […]
Well, I have a great update for you! We have the new system for retrieving stock quotes that we’ve been waiting for. It is the Stocks data type. But, first, a quick background. Historically, we were able to retrieve stock quotes by using the MSN MoneyCentral web query. I wrote about that here. And, that […]
Using target markers in a bar chart to compare a value (such as actual sales) to a target (such as budget or forecast) provides a clean display. As with anything in Excel, there are several ways to build such a chart. In this post, we’ll walk through a technique that does not require any calculated […]
Here’s the situation. We have created a PivotTable and related PivotChart, and, since we are nice, we have also provided a Slicer so that the user can easily make selections. But, we’d like the report titles to dynamically update based on the selections made. As with anything in Excel, there are multiple ways to accomplish […]
Working fast is about knowing features and functions, for sure, but, it is also about how quickly we communicate with Excel. We can set up QAT buttons to activate frequently used commands … and … hidden commands. Hidden commands? Yes … Excel has far more commands than are provided in the standard ribbon tabs. It […]
In addition to knowing which features and functions can help you work faster in Excel, knowing a few keyboard shortcuts will help as well. You see, the faster you can communicate with Excel the faster you’ll get your Excel-work done. So, in this post, I’ll talk about 3 of my most-used shortcuts and then show […]
Historically, when it was time for Microsoft to release the next version of Excel, everyone would be buzzing, excited, and talking about the new features, functions, and improvements. I know that my heart would pitter-patter as the new version was installing on my system. But, things are different now. In fact, in a way, the […]
Let’s say we are building a query to clean up and prepare some data for use, and we have a specific value we need to use within the query. But, we would like to be able to change the value as desired, before refreshing the query. One option would be to enter and update that […]
The SUBTOTAL function is one of my favorites. So, I wrote a post about it a while back that provided a macro to insert it. The macro worked kinda like the AutoSum command, but it inserted the SUBTOTAL function instead of the SUM function. Gary posted a suggestion for enhancing the macro, so, I wrote […]