May 23, 2019
When you think of comparing lists, you probably think of VLOOKUP. If you have two lists to compare, you need to add two columns of VLOOKUP.
May 22, 2019
Here is a great trick I learned from southern California–based Excel consultant Szilvia Juhasz.
May 20, 2019
It is fairly cool that you can use Ctrl+T after setting up the pivot table, VLOOKUP, or chart, and Excel still makes the range expand.
May 16, 2019
If you choose all of columns A:J and you later want to add more records below the data, it takes only a simple Refresh to add the new data instead of having to find the Change Data Source icon. In the past, this made sense. But today, Change Data Source is right next to the Refresh button and not hard to find.
May 15, 2019
I‘ve managed to lobby the Excel team to get a few changes into Excel. It isn‘t always easy. It took me eight years of lobbying to get the Repeat All Item Labels feature added to Excel 2010. It took seven years to get the Pivot Table Defaults feature added.
May 13, 2019
It took me six years, but I finally convinced the Excel team that a lot of people prefer Tabular layout for pivot tables to the Compact layout that became the default layout in Excel 2007. If you have Office 365, you now have the ability to specify pivot table defaults.
May 9, 2019
Pivot tables offer a Top 10 filter. It is cool. It is flexible. But I hate it, and I will tell you why.
May 8, 2019
In almost every seminar, someone asks why pivot tables default to count instead of sum. This long-standing problem was fixed in May 2018 for Office 365 subscribers. The Count was triggered if you had one revenue cell that contained text or an empty cell.
May 6, 2019
Pivot tables offer a myriad of calculations in the Field Settings dialog box. Here is a faster way to change a calculation.
May 2, 2019
Instead of creating a formula outside of the pivot table, you can do this inside the pivot table. Start from the image with column D empty. Drag Revenue a second time to the Values area.
May 1, 2019
Let’s say you have two years’ worth of detail records. Each record has a daily date. When you build a pivot table from this report, you will have hundreds of rows of daily dates in the pivot table.
April 29, 2019
If your pivot table is in Tabular or Outline Form and you have more than one row field, the pivot table defaults to leaving a lot of blank cells in the outer row fields.