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.

Power Query

UPPER lower Proper Case (3 ways)

By Jeff Lenning | December 5, 2023 |

The objective of this tutorial is to demonstrate three different methods to change the case of text in Microsoft Excel: Flash Fill, formulas, and Power Query. By the end of this tutorial, you will be confident in using these techniques to convert text to upper case, lower case, and proper case. Video Walkthrough Let’s walk…

Read More

Get QuickBooks Exports into Excel with Power Query

By Jeff Lenning | August 8, 2023 |

Welcome to this tutorial on how to pull QuickBooks exports into Excel with Power Query. In this tutorial, we will run a QuickBooks report, download it to an Excel format, and then use Power Query to clean it up and get the data into an Excel table. We will use Power Query to import this…

Read More

Compare Two Columns with Microsoft Excel Power Query

By Jeff Lenning | July 25, 2023 |

If you’re looking to compare two columns in Excel with Power Query, you’ve come to the right place. In this tutorial, you’ll learn how to use Power Query to compare two lists to find which items appear on both and which items appear on only one list. Plus, you’ll learn how to match on multiple…

Read More

Excel How To Remove Duplicates (3 Easy Ways)

By Jeff Lenning | May 30, 2023 |

This post demonstrates how to remove duplicates from your Microsoft Excel worksheets. As with just about anything in Excel, there are multiple ways. I’ll show you 3 of the easiest and fastest ways to do it, specifically: the Remove Duplicates command, the UNIQUE function, and Power Query. Video Narrative We’ll just walk through each of…

Read More

Excel Tips from 20 Experts

By Jeff Lenning | February 21, 2023 |

My friend John Michaloudis does an annual survey of Excel MVPs and experts and asks for their best Excel tip. He compiles the tips into an audio podcast along with show notes and related links. This is a great resource and I love sharing it every year. This year, he rounded up tips from: You’ll…

Read More

Skipping Excel 1

By Jeff Lenning | November 22, 2022 |

Excel is used for many different tasks, such as budgeting, reporting, comparing bank statements, and other things. It can also be used to export, clean, and import data between two separate systems. For example, suppose you export transactions from an e-commerce system, open them in Excel, tidy up the information a little, and then transfer…

Read More

CalCPA Article Instant Reconciliations

By Jeff Lenning | November 1, 2022 |

When you have two different lists that you need to compare, how do you do it? For example, you want to identify which items on one list (e.g., a check register) appear on the other (e.g. bank activity)? I used to print both lists, grab a ruler and go down row by row ticking items…

Read More
man looking at a computer while learning How to Merge Excel Files without using VBA

How to Merge Excel Files (Without Using VBA)

By Excel University | October 11, 2022 |

You might be surprised how much time is lost flipping between Excel workbooks trying to find information – especially if you have a lot to sort through. Learning how to merge Excel files will make finding information easier, faster, and more user-friendly (especially if you’re sharing workbooks). While you can use VBA to combine files,…

Read More

List of files in Excel (easy way, no VBA macros or formulas)

By Jeff Lenning | September 27, 2022 |

In this post, I’ll answer a question about how to create a list of files in a folder (and any subfolders) in Excel. For this illustration, we’ll use Power Query instead of VBA/Macros. Objective Before we jump into the details, let’s look at the objective. We want to create a list of files in a…

Read More
Man making notes on a large calendar underneath an open laptop

Top 5 Excel Calendar Tutorials & Free Templates

By Excel University | June 28, 2022 |

Do you use Excel to keep track of your to-do lists, upcoming events, deadlines, etc.? Since spreadsheets are so customizable, many Excel users rely on the software to create unique calendars tailored to their needs. It’s one of the most popular uses for Excel and great practice for users of all skill levels. If you’re…

Read More