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.


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

Paste Special Add

By Jeff Lenning | April 13, 2021 |

In addition to the standard Copy and Paste commands, Excel offers a variety of options for pasting values and formulas. In this post, I’ll chat about one of those options … Paste Special Add. It works with values AND formulas. Let’s dig in. Overview Before we get into the details, let’s zoom out for a…

Read More

Hide and Unhide Multiple Sheets

By Jeff Lenning | April 6, 2021 |

Hiding a single worksheet from view is fairly straightforward … you can right-click the tab and select Hide. Hiding multiple worksheets at the same time is also fairly straightforward. You select multiple sheets by holding down Ctrl or Shift, and then right-click and select Hide. It is also fairly straightforward to Unhide a single sheet.…

Read More

Pivot Text Values with Power Query

By Jeff Lenning | March 23, 2021 |

In this post, we’ll talk about how to Pivot text values with Power Query. We would like to do this even if there are a variable number of fields per record. This can be REALLY hard to visualize based on a short text description, so I think it is best to show screenshots of the…

Read More

Split Column Headers from Values

By Jeff Lenning | March 9, 2021 |

I was recently asked how to separate multiple column headers from their values. My favorite tool for transforming data like this is Power Query. So, this post walks through the steps with Power Query. Thanks Ron for your question! Objective Before we get into the steps, let’s visualize the start and end points. Ron states…

Read More

Slow to Fast 3

By Jeff Lenning | February 22, 2021 |

This is the final post in the Slow to Fast series, where we are incrementally improving the efficiency of reconciliations. In the first post, we looked at conditional formatting. This was a great option when the lists were relatively short, stored on the same worksheet, and only required us to look at a single column.…

Read More

Slow to Fast 1

By Jeff Lenning | February 8, 2021 |

This is the first post in the Slow to Fast series, where we will incrementally improve the task of comparing two lists (commonly called a reconciliation). The illustration I’ll use is a bank reconciliation. We want to compare the list of checks in our accounting system with the list of checks from our bank download.…

Read More

Automatically Format Rows

By Jeff Lenning | January 26, 2021 |

I was recently asked how to set up conditional formatting to format a row based on the value in a column. In this post, I’ll demonstrate how to create a conditional formatting rule that automatically formats a row based on a single column value for all rows in the worksheet. Thanks Karla for your question!…

Read More

List of Workbook Tables and References

By Jeff Lenning | December 8, 2020 |

John asked if there was a way to create a list of all table names in the workbook, along with the underlying table reference. In this post, I’ll demonstrate how this can be accomplished with Power Query and an Excel formula. Objective Before we get into the mechanics, let’s confirm our goal here. We have…

Read More