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.

Add-Ins

Sample Selections

By Jeff Lenning | February 18, 2020 |

For many decades, Excel has shipped with an add-in called the Analysis ToolPak. But, by default it is disabled. When you enable it, you get access to a bunch of cool stuff … including the ability to have Excel make sample selections. I recently received a question from Jo (thanks Jo!) asking about how to…

Read More

Stock Quotes CalCPA Article

By Jeff Lenning | July 15, 2019 |

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…

Read More

Stop Wasting Time 2

By Jeff Lenning | April 9, 2019 |

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…

Read More

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

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

Impossible PivotTables 4 – Average of Distinct Count

By Jeff Lenning | July 24, 2018 |

This is the 4th and final post in the Impossible PivotTables series, where we are exploring Power Pivot by looking at some limitations encountered with traditional PivotTables. In this post, we’ll look at how to compute the average when there are multiple rows per item. That is, where a simple sum divided by the count…

Read More

Data Model Walkthrough CalCPA Article

By Jeff Lenning | July 17, 2018 |

Have you heard people talking about Power Pivot and the data model, but you aren’t exactly sure what they are or how to use them? Well, in this article, we’ll use them to build a basic PivotTable. Workbooks used to summarize exported data can potentially be improved by using Power Pivot and the Data Model. Check…

Read More

Impossible PivotTables 3 – Multiple Data Tables

By Jeff Lenning | July 10, 2018 |

This is the 3rd post in the Impossible PivotTables series. This series is designed to explore Power Pivot. I thought looking at a few limitations of traditional PivotTables would be a fun way to do this. So, in this post, we will look at how traditional PivotTables support a single data table while Power Pivot…

Read More

Impossible PivotTables 2 – Show Values As

By Jeff Lenning | July 2, 2018 |

This is the second post in the Impossible PivotTables series, where we are exploring Power Pivot. In the first post, we examined the limitation of calculated field references in a traditional PivotTable. In this post, we’ll address the limitation of using the Show Values As option for subsequent calculations. If we use the Show Values…

Read More

Impossible PivotTables 1 – Calculated Fields

By Jeff Lenning | June 27, 2018 |

This is the first post in a series called Impossible PivotTables. The purpose of this series is to explore Power Pivot. I thought a fun way to do that would be to demonstrate how using the data model enables us to build PivotTables that are either impossible with traditional PivotTables or that require workarounds. I…

Read More