Excel Tips
May 10, 2018
I download data from the system and some of my numbers have a trailing minus sign. How do I convert only the ones with a trailing minus sign?
Filter by Quarter in Pivot Table with Daily Dates »
May 9, 2018
How can you filter a pivot table by quarter when your pivot table data only has daily dates? It is fairly easy to do using these steps.
May 7, 2018
You have two columns of numbers. There is an AutoSum at the bottom of each column. While inserting rows, the AutoSum in one column stops working. What could be causing that?
May 4, 2018
Controlling multipled pivot tables is one of the main benefits of slicers. But both of those pivot tables have to come from the same data set. When you have data from two different data sets, using one slicer to control both data sets becomes more difficult.
May 2, 2018
This question comes up once every decade: Can you do a Median in a pivot table. Traditionally, the answer was No. I remember back in 2000 when I hired Excel MVP Juan Pablo Gonzalez to write an awesome macro that created reports that looked like pivot tables but had Medians.
Creating a Hierarchy in a Pivot Table »
April 26, 2018
Recently, a friend of mine wondered about the Drill-Down and Drill-Up buttons in the Pivot Table Tools tab of the Ribbon. Why are these perpetually greyed out? They take up a lot of space in the Ribbon. How is anyone supposed to use them?
Improved Handling of Empty Cells in Pivot Table Source »
April 25, 2018
Quietly and without any fanfare, Microsoft has changed the default behavior for handling empty cells in a pivot table source. In my live Power Excel seminars, when I get to the pivot table section of the day, I can count on at least one person asking me: "Why do my pivot tables default to Count instead of Sum when I drag Revenue to the pivot table." If you have Office 365, a change is coming to Excel to reduce the number of times this happens.
How to Wrap Data to Multiple Columns in Excel »
April 19, 2018
Gwynne has 15 thousand rows of data in three columns. She would like to have the data print with 6 columns per page. For example, the first 50 names in A2:C51, then the next 50 names in E2:G51. Then move the third 50 rows to A52:C101 and so on.
April 17, 2018
Sandy works for a construction company. They sell flooring in packages of 20 Board Feet. When their estimators go to a job, they might say that they need 441 board feet of material. Sandy needs a way to always round up to the next 20.