Excel University Blog
Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.
Jeff Lenning
Excel has numerous date-related features and functions. In this post, we’ll explore a few of them. We need an illustration that will tie them all together, so, we’ll create a graphical calendar with a PivotTable. Even if you don’t need a graphical calendar in your workbooks, the underlying mechanics that enable us to build it…
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…
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…