Scroll Through Filter Items in Excel Table

To see specific data in an Excel Table, you can select an item from the drop down filter in a column heading. Someone asked me if there was a way to scroll through the items, instead of opening the filter list each time. This technique uses a pivot table, which could be hidden on a different sheet, and a spin button, to go up or down in the list of items.

Continue reading "Scroll Through Filter Items in Excel Table"

Quick Trick to Add or Move Pivot Table Fields

We're still recovering from Tuesday's Spreadsheet Day celebrations, so we'll keep it simple today. Here's a quick trick to add or move pivot table fields, just by typing. Watch the really short video, and there are written instructions below, if you prefer those.

Continue reading "Quick Trick to Add or Move Pivot Table Fields"

Compare Pivot Table Values As Percentages

As you know, pivot tables are a quick and easy way to summarize a huge table of data in Excel. With just a few clicks, you can create totals for thousands of rows of data, without writing a single formula. Then, with filters and Slicers, zero in on specific parts of the data. But there's an underused feature that you might be missing – do you know how to compare pivot table values as percentages?

Continue reading "Compare Pivot Table Values As Percentages"

Compare Weekdays in Fiscal Year Summary

In the retail businesses, some days of the week are busier than others. So, if you're looking at year over year sales, it's not too helpful to compare sales for a specific calendar date, because they'll fall on different weekdays. With a few calculations, you can set up a summary that lines up the weekdays, so you can get a better comparison.

Continue reading "Compare Weekdays in Fiscal Year Summary"

Pivot Table Fill Colour Disappears

Microsoft just announced the winner of their Excel World Champ data visualization contest. Congratulations to Ghazanfar Abidi, from Canada! I found his website today, and learned something new from his latest blog post – depending on how you apply it, pivot table fill colour might disappear!

Continue reading "Pivot Table Fill Colour Disappears"

Pivot Table Subtotal Problem in Excel 2016

If you using grouping, you might run into a pivot table subtotal problem in Excel 2016. There was a change in a recent update, so you might see this problem if you have an Office 365 subscription. I just learned about this issue, and will show you how to fix the problem if it affects your workbooks.

Continue reading "Pivot Table Subtotal Problem in Excel 2016"

Remove Excel Pivot Fields With Macros

If you’re rearranging a complex pivot table, it can take a while to manually remove each field. To make the job easier, you can remove Excel pivot fields with macros. There’s a sample macro below that remove all the column fields, and you’ll find more examples, and a free workbook, on my website.

Continue reading "Remove Excel Pivot Fields With Macros"

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"