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.
VBA
In this post, I’ll share a short snippet of VBA code that creates new worksheets based on the names stored in cell values. This can be helpful when you have a list of departments, and want to create one worksheet for each department. Or a list of accounts, or employees, or regions, and so on.…
Read More
The SUBTOTAL function is one of my favorites. So, I wrote a post about it a while back that provided a macro to insert it. The macro worked kinda like the AutoSum command, but it inserted the SUBTOTAL function instead of the SUM function. Gary posted a suggestion for enhancing the macro, so, I wrote…
Read More
I really like to highlight input cells by applying the Input Cell Style. I also like to try to place all of the input cells for a workbook on a single worksheet. That way, it is easy to update the input cells, and since they aren’t on any of the sheets I need to print,…
Read More
Excel users often spend a bunch of time applying the same format to number cells. Some numbers are stored values and others are calculated with formulas. The task of manually applying the same format to all of the numbers in the worksheet, especially when the numbers aren’t in a continuous range, can feel quite repetitive.…
Read More
When storing utility macros, Excel users often elect to save them in the Personal Macro Workbook. The advantage is that the macros are available whenever Excel is running. The disadvantage is that if you switch or upgrade computers, you may forget to take a backup of the Personal Macro Workbook and thus lose your macros. In…
Read More
PivotTable users frequently spend time assigning the same number format to PivotTable values. To my knowledge, there isn’t a built-in setting that allows us to define a default value field format. But, it is pretty easy to set up a macro that instantly assigns a desired format. This post walks through the steps of creating such a macro. Objective…
Read More
The SUBTOTAL is a wonderful alternative to the SUM function, and this post shows how to set up a shortcut button that inserts the SUBTOTAL function. Our new shortcut button will be similar to the AutoSum button that inserts the SUM function because it will automatically include the cells above. Objective Before we get started, let’s…
Read More
Excel’s Merge and Center command has an icon, but, it is often better to Center Across Selection instead of merging cells. Unfortunately, the current version of Excel doesn’t have a Center Across Selection Ribbon icon. In this post, we’ll create a simple macro that we can activate with a Quick Access Toolbar (QAT) icon. Objective Let’s…
Read More
Did you know that Excel has two levels of hidden worksheets? Excel has “hidden” worksheets, and, “very hidden” worksheets. This post walks through the differences, and how to hide worksheets at each level. By default, all new worksheets are visible. A visible worksheet’s tab appears in the bottom of the Excel window, enabling the user…
Read More