How to Plan a Pivot Table in Excel

Setting up a pivot table is a bit like news reporting – you can give a quick summary of the Who, What, When, Where and How (Much) of your data. After you’ve been using pivot tables for a while, it’s easy to create a new report, and drop the fields into the right locations. But, if you’re just starting out, it’s not clear what to put where. I’ve put together a short guide on how to plan a pivot table.

Thanks to Anne Walsh for suggesting today’s topic! Anne has led Excel classes for many years, and she knows that people struggle to get their pivot tables set up correctly. Anne recently published Your Excel Survival Kit, which is jam-packed with useful Excel tips, from her extensive experience.

Continue reading “How to Plan a Pivot Table in Excel”

Pivot Table Show Details Sheets

When you double-click on a pivot table value cell, Excel creates a new sheet, with a list of all the records that make up that total value. The double-click runs Excel’s Show Details command – it’s a helpful troubleshooting feature, but can add clutter to a workbook, because of all the sheets that it creates.

To help you keep things tidy, I created a sample file with macros that label the Show Details sheets when you create them. Then, when the workbook closes, another macro will check for those sheets, and asks if you’d like to delete them. I updated the workbook this week, so take a look, if it’s something you need.

Continue reading “Pivot Table Show Details Sheets”

Quick Ways to Unpivot Excel Data

Before you can build a flexible pivot table, you might need to rearrange the data. For example, if the data has a separate column for each month’s sales, that won’t work well in a pivot table. You need to “unpivot” your data first. Here’s what that means, and 2 quick ways to unpivot Excel data.

Continue reading “Quick Ways to Unpivot Excel Data”

Excel Problems, Fixes and Webinars

Did you get hit by the disappearing Excel add-in affliction recently? After a long couple of weeks, that frustrating Excel problem is finally solved. After spending countless hours on that issue (details below), I was happy to get back to more productive work, updating my website and sample files. I hope the rest of the summer goes smoothly!

Continue reading “Excel Problems, Fixes and Webinars”

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.

Continue reading “Hide Sheets Based on Tab Color”

Keeping Track of Garbage in Excel

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. 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!

Continue reading “Keeping Track of Garbage in Excel”

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.

Continue reading “Show Specific Sheets in Excel”