Slicers make it easy to select from a list of items, but they take up valuable space on your worksheet. To get the benefits of a Slicer, without the real estate cost, AlexJ made this Excel Pop Up Selector – it’s hidden until you click the small button on the sheet, and disappears again, after you use it.
Category: Excel VBA
Freeze All Worksheets Macro
If you’re working with a large worksheet in Excel, it usually helps if you freeze the cells at the top and/or the left side of the sheet. That way, your headings are always visible, along with other key information that you’ve put at the top of the sheet. You can freeze each sheet individually, or use this macro to freeze all worksheets at once.
Popup List of Excel Sheets
If you’re working in an Excel file with lots of worksheets, it can take a while to scroll to the ones that you need. Sometimes you can’t even remember where the sheets are, and that takes even longer! To make it easier for myself, I created an add-in with a popup list of Excel sheets. See the details below, and there’s a link to my site where you can download it.
Excel Form Control Button Quirks
If you’re building an Excel workbook for other people to use, you can add form control buttons, so it’s easy for them to run macros. One of my sample files has Navigation Buttons on each sheet, so you can quickly go to the previous or next sheet. There are a few form control button quirks though – maybe you’ve run into some of them. Today, we’ll take a look at one of the button name quirks – a hidden name that Excel fiercely protects.
How to Prevent Grouped Dates in Excel
As a teenager, group dates can be fun. If you have strict parents, that might be the only kind of dates they allow! But, in your Excel worksheets, it can be annoying when dates are automatically grouped. This happens in AutoFilters, and in pivot tables too (in newer versions). Here’s how you can undo or prevent grouped dates in Excel AutoFilters and Pivot Tables.
How to Show Excel Table Name on the Sheet
If you have an Excel workbook with lots of tables and pivot tables, it can be hard to remember what they’re named, or what source data they’re using. To help you keep track of them, AlexJ shared the User Defined Function (UDF) that he uses in his files. With this code in your file, just add a formula, click on a cell, and show the pivot table name or Excel table name on the sheet.
The code is shown below, and there is also a link for downloading a sample file with the code installed in it.
Continue reading “How to Show Excel Table Name on the Sheet”
Hide Sheets Based on Tab Color
Last month, I showed you my code that hides worksheets, based on the text in their names. Someone asked we could hide sheets based on tab color instead, so that’s how today’s example works.
Just select a sheet type from the drop down list, and any sheets with matching tab color are visible. All other sheets are hidden, except the Menu. Then, select “(All)” to see all the sheets again. Watch the video, and get the details, below.
Show Specific Sheets in Excel
In a workbook with lots of worksheets, it can be hard to find the ones that you need, to get a specific task done. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show just a few specific sheets in Excel. You’ll be able to focus on what you need to do, and ignore everything else.
How to Change Excel Right-Click Menus
If you use the mouse most of the time, when you’re working in Excel, you probably right-click, to see the popup menus. For example, right-click a column heading to insert a new column. If your favourite commands aren’t on those popup menus, here’s how you can change Excel right-click menus, to add them. Also, if you have a minute, please take the 3-question Excel survey on my Debra D blog. Thanks!
List All Excel Sheets With Used Range
Last week I was updating one of my Excel sample files, and noticed that it was way bigger than it should be. Most of the sample files are just a few kilobytes in size, but this one was about 1.5 MB. What was going on?
Here’s how I found the problem, and a macro that you can use for troubleshooting in your workbooks.