Excel Tips

Creating the Power Pivot Table »

March 21, 2023

The Pivot Table dropdown offers 8 choices and most of them are fairly silly. PivotTable and Pivot Charts are obviously needed. Pivot table pros will love the Flattened PivotTable. But all of those choices in the middle are redundant.

Refer to a Related Table in a Formula »

March 20, 2023

I am entering a formula in the Fact table. I need to lookup a value from Product table. I’ve already defined a relationship between the tables.

Adding Calculations In the Power Pivot Grid »

March 17, 2023

Power Pivot introduces a new formula language called Data Analysis eXpressions or DAX. When you see a Power Pivot demo, the 2 million rows and the joining tables look impressive. But it turns out that DAX is the really jewel in Power Pivot.

Create a Calendar Table »

March 15, 2023

One downside of Power Pivot is the inability to group daily dates up to months and years. The common workaround is to build a lookup table that contains every daily date from the earliest date to the latest date in your data.

Sort Month Name by Month Number »

March 14, 2023

Regular pivot tables use the Custom Lists dialog to automatically sort months into Jan, Feb, Mar sequence. Power Pivot doesn’t seem to be aware of Custom Lists and sorts into the alphabetic sequence of Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar. May, Nov, Oct, Sep.

Define Relationships Between Tables »

March 13, 2023

There are three different ways to define a relationship in Power Pivot. Say you want to link from the ProdID field in the Fact table to the ProdID field in the Products table. Follow these steps:

Get Excel Data Into Power Pivot »

March 9, 2023

How do I get my Excel data into Power Pivot?

Five Reasons to Use Power Pivot »

March 7, 2023

What is Power Pivot?

Use Joiner Tables Between Tables »

March 6, 2023

I have a top-level budget table with one row per month, region, product. It has 54 rows. I want to create a report comparing the budget table to an invoice table with hundreds of rows.