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 Calendar with One Formula

By Jeff Lenning | October 20, 2021 |

In this post, we’ll see how to create an Excel calendar with a single formula. Specifically, we will write a formula that displays the days of any month in a graphical calendar format. Our graphical calendar displays the days of the specified month in 7 columns (Sunday through Saturday) and includes a row for each…

Read More

PivotTable Text Values Alternative with FILTER

By Jeff Lenning | October 12, 2021 |

This is the third and final post in the PivotTable Text Values Alternative series, where we are discussing alternatives to displaying text values in PivotTables. In the first post, we used Power Query as an alternative. In the second post, we combined multiple text values. In this post, we’ll use the FILTER function as an…

Read More

PivotTable with Multiple Text Values Alternative

By Jeff Lenning | October 5, 2021 |

If you have tried to insert a text field into the values layout area of a PivotTable, you may have noticed that the text string itself is not displayed. Instead, the count is displayed by default. In this series, we are talking about how to display the desired text values by using Power Query instead…

Read More

PivotTable Text Values Alternative

By Jeff Lenning | September 28, 2021 |

This post show how to use Power Query instead of a PivotTable to display text values in a report.

Read More

Retrieve QuickBooks Data with Genius Sheets

By Jeff Lenning | September 15, 2021 |

This is the next post in the add-in series, where we are exploring various add-ins that expand the capabilities of Excel. In this post, we’ll take a look at the Genius Sheets add-in. In summary, it enables us to get QuickBooks Online data into our Excel workbooks. The add-in provides several custom functions that we…

Read More

Mapping Table without a Helper Column

By Jeff Lenning | September 7, 2021 |

A mapping table is a handy way to automatically translate labels between systems and reports. As with just about anything in Excel, there are many ways to implement a mapping table. For example, we could create a helper column to store the amounts with the SUMIFS function as I discussed in this Journal of Accountancy…

Read More

2021 Scholarship Winners

By Jeff Lenning | August 24, 2021 |

I am excited to announce this year’s Excel University scholarship winners! These scholarships are supported by the Excel University Alumni Association, so thanks to all of you who have helped make these awards possible. Each will receive a cash award plus an Excel University training pass. The recipients are terrific examples of the type of…

Read More

Public Company Financials with XBRL Filed Data

By Jeff Lenning | August 11, 2021 |

This is the next post in the add-ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at the XBRL Filed Data add-in. In summary, it enables us to get public company financial data (and more) into Excel. Objective Before we get…

Read More

Excel to Word Document Automation

By Jeff Lenning | August 3, 2021 |

This is the next post in the add-ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at the Excel-to-Word Document Automation add-in. In summary, it enables us to get Excel data into a Word (or Power Point) document. It creates a…

Read More

Text Toolkit by Ablebits

By Jeff Lenning | July 20, 2021 |

This is the next post in the Add-Ins series, where we are exploring various add-ins that enhance and extend the capabilities of Excel. In this post, we’ll take a look at Text Toolkit by Ablebits. In summary, it enables us to easily make changes to text without writing formulas. What types of changes? Many! Examples…

Read More