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. […]
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 […]
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, […]
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. […]
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 […]
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 […]
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 […]
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 […]
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 […]