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

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

Create Dynamic Rows for an Amortization Schedule with Power Query

By Jeff Lenning | June 19, 2018 |

Sometimes in Excel, we want to use formulas to compute row values, but, the number of rows is dynamic and changes periodically. For example, let’s say we want to create an amortization schedule and use it for a variety of loans. Some loans are paid in 36 months, some in 120 months, and some in…

Read More

Power Query and Zip Code Formatting

By Jeff Lenning | June 12, 2018 |

Power Query is an amazing tool, and I love learning about it. In the Power Query Editor, as you click the command icons, Excel is actually writing M code behind the scenes. M code has many more functions than are available in the ribbon. So, the thing to keep in mind is that if you…

Read More

Split Delimited List into Rows

By Jeff Lenning | June 6, 2018 |

This is the second of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In the first post, we combined rows into a delimited list. In this post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for … let’s…

Read More

Combine Rows into a Delimited List

By Jeff Lenning | May 31, 2018 |

This is the first of two related posts that demonstrate how to use Power Query to deal with rows and delimited lists. In this first post, we’ll combine rows into a delimited list. In the second post, we’ll do the opposite and convert a delimited list into rows. Well, what are we waiting for ……

Read More

Power Pivot CalCPA Article

By Jeff Lenning | May 16, 2018 |

I’m assuming you love PivotTables. I mean, who doesn’t, right? Well, traditional PivotTables are amazing, but they’re not without limits. Instead of using a traditional table as the PivotTable source, you can take your PivotTables to the next level when you use Power Pivot instead. In this California CPA Magazine article, I talk about a…

Read More

Excel Breakout Puzzle 4

By Jeff Lenning | April 24, 2018 |

It is time for another breakout puzzle … yay! Have you ever played Sudoku? My family loves doing these puzzles, in fact, last week we had a full-on Sudoku race and competition. Who won? Not me. My daughter totally crushed it. I’d get like 5 squares done, and she’d be like, “done!” Again and again.…

Read More