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.

Posts

Power Query Calendar

By Jeff Lenning | February 6, 2019 |

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…

Read More

Tips from 17 Excel Experts

By Jeff Lenning | January 22, 2019 |

My Excel-friend John Michaloudis has compiled a podcast with tips from 17 Excel experts and MVPs. He does this annually, and this year he has put together a great collection of tips. This year, you’ll hear tips from: Bill Jelen John Michaloudis Mynda Treacy Jeff Lenning Jon Acampora Danielle Stein Fairhurst Ken Puls Kyle Pew…

Read More

PivotTable Calendar

By Jeff Lenning | January 14, 2019 |

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…

Read More

Power BI CalCPA Article

By Jeff Lenning | January 9, 2019 | Comments Off on Power BI CalCPA Article

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. https://www.excel-university.com/articles/cal-cpa/power-and-pivot/  

Read More

Sum Every Nth Row in a Column

By Jeff Lenning | December 19, 2018 |

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…

Read More

Identify Missing IDs and Sequence Gaps

By Jeff Lenning | December 10, 2018 |

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…

Read More

Geography Data Type

By Jeff Lenning | December 5, 2018 |

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…

Read More

Stock Data Type – Stock Quotes and More

By Jeff Lenning | November 27, 2018 |

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…

Read More

Bar Chart Target Markers

By Jeff Lenning | November 14, 2018 |

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…

Read More

Dynamic Chart Title with Slicers

By Jeff Lenning | November 7, 2018 |

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…

Read More