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 is sharing the User Defined Function (UDF) that he uses to help manage his files. With AlexJ's code in your file, just add a formula, click on a cell, and show Excel table name, or pivot 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. You
Continue reading How to Show Excel Table Name on the Sheet
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.
Continue reading Hide Sheets Based on Tab Color
I’ve worked with “garbage” data many times in Excel. You know what I mean – data that is so messy it takes you hours (or days) to clean it up. This week though, I did a different type of garbage tracking -- an Excel waste collection schedule.
Maybe I went a little overboard, but you’ve probably done that too, right? Anyway, here’s how I spent way too much time on an Excel project, but had fun doing it. Don’t judge! What weird Excel projects have you done?
And speaking of schedules, it's summer now, here in Canada, so I'll be
Continue reading Keeping Track of Garbage in Excel
In a workbook with lots of worksheets, you can have a hard time finding the sheets that you need, to do a specific task. Instead of scrolling through all the sheet tabs, or using the popup list of worksheets, use a drop down list to show specific sheets in Excel. Also, all other sheets are hidden, and you’ll be able to focus on what you need to do, and ignore everything else.
Continue reading Show Specific Sheets in Excel
Have you ever copied football scores, such as 3-2, and pasted them into Excel, where they magically transform into dates? It's certainly annoying when a nice list of numbers change to dates in Excel, but fortunately there's an easy way to prevent that from happening.
Continue reading Numbers Change to Dates in Excel
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!
Continue reading How to Change Excel Right-Click Menus
If you need to get a product price in Excel, you can use VLOOKUP or INDEX/MATCH to get the price from a lookup table, based on a product code. But what if you have two pieces of information, such as a product name, and a size, and you want to find the price based on that information? How can you do an Excel lookup with two criteria?
Continue reading Excel Lookup With Two Criteria
If you're setting up a big pivot table, it's easy to lose track of what you've added, and what filters have been applied. To help you stay organized, I've created a macro to list all pivot fields and pivot items in the selected pivot table's row, column and filter areas. You can download the sample file, and test the macro in your own files.
Continue reading Macro to List All Pivot Fields and Pivot Items
You don't have to stick with the default formats for your pivot tables. You can create a pivot table style with your own colours, and other formatting options that you like. Here's how to get started, and a video with a simple formatting change that you can make.
Continue reading How to Create a Pivot Table Style
Have you ever recorded a macro to remove pivot table calculated fields? Just turn on the recorder, right-click on the field and hide it, and turn off the recorder. Then, if you try to run that macro later, Kaboom! You get an error message, "Run-time error '1004': Unable to set the Orientation property of the PivotField class".
Good news – you can download my sample file that has a macro that actually removes those pesky calculated fields, without creating an error message. The video shows how it works.
Continue reading Macro to Remove Pivot Table Calculated Fields