Suddenly, it's November, and it's time to plan what you'll wear on St. Andrew's Day (November 30th). He is the patron saint of Scotland (and other countries), so of course you'll need a kilt, made in your family tartan. Fortunately, you can create a tartan pattern in Excel, if your family doesn't have a tartan. Thanks to John Marshall for creating this handy tool.
You can use Excel hyperlinks to navigate through a workbook, go to web pages, open other Excel files, and even open PDF files. However, if you try to use an Excel hyperlink to run command files, you'll run into problems. Until now! Here's how J.Woolley uses hyperlinks, with a background macro, to avoid those problems – and a other problems too. He also found a strange hyperlink bug.
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.
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.
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.
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.
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.
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 their using. To help you keep track of them, AlexJ is sharing 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 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.
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.
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.