We all know pivot table functionality is a powerful & useful feature. But it comes with some quirks. For example, we cant insert a blank row or column inside pivot tables.
So today let me share a few ideas on how you can insert a blank column.
But first let’s try inserting a column
Imagine you are looking at a pivot table like this.
And you want to insert a column or row. Go ahead and try it. Here is what happens.
- Excel gets mad thinking you are attempting anarchy and throws a stern, but very long & confusing warning message.
In fact the error message is so long, I can’t even fit it in one image on this blog. Here it goes, verbatim.
So how DO we insert a column in the pivot
The answer is simple.
Don’t
Don’t bother inserting the columns in actual pivot table. Instead, follow this approach.
- Select any cell in the pivot
- Press Ctrl+Shift+8 – This selects the entire pivot
- Copy it by pressing CTRL+C
- Go to a new worksheet
- Paste as references – ALT+CTRL+V and L
- Select any cells containing 0 and press DELETE key
- Now, go ahead and insert any number of columns & rows in this new worksheet
- When your pivot changes (either due to refresh or new data), the copy worksheet changes too
- Bonus: You can format the new worksheet cells any way you want. It just works.
Here is an example of what you can do.
But I want to insert a column in my pivot!!!
Okay, clearly you have a case of OCDIS (Obsessive Column Deletion / Insertion Syndrome).
Here is one way to technically insert a column inside the pivot table.
Before understanding the process, let’s pause and ask, “why do you want to insert a column?”
Here are few possible reasons.
- Cosmetic / formatting reasons. A blank column makes things easy to read
- To add commentary / notes / extra data
- To perform intermediate calculations on the data
If your answer is 1, the above approach (copying pivot and pasting as references) gives you most control over the layout and formatting. Go for it.
If your answer is 2, again above approach is still good.
If your answer is 3, you can use calculated item / fields is your best option.
If your answer is 3 & you are using Excel 2013 (or power pivot), you can use either Sets feature or MDX formulas to mimic blank rows. Unfortunately, I can’t explain this because squirrels know more MDX than me.
Let’s say you want to calculate certain percentage or similar…
Okay, so want to calculate North / West % in below pivot.
In this case, you can use calculated items feature of pivot table like this.
- Select any region name in the column labels are of pivot
- Go to Home > Insert > Calculated Item
- Give your calculated item a name like “North by West %”
- Write the formula =North / West
- Click ok
- This new column will added to your pivot, like this:
As you can see, it works fine until we hit the grand total row. There our North / West % should be 96%. Instead it reads 386%. Clearly a number calculated by my 6 year old son.
Why is the total wrong? Because, pivot table grand totals are a simple sum of all the above values. So Excel went ahead and added up the four percentages.
How to fix this? One simple options is to turn off the grand totals. Note that even row level grand totals are off as the % was added to actual values.
If you must see the grand totals, then your best bet is to use Power Pivot. It allows you to define formulas (using DAX) and create powerful pivot tables.
So no easy way to insert columns then?
It took us a few minutes to get here, but that is the answer. There is no simple work around to this problem. Instead, here is a 4 step process you should follow.
- Take a few deep breaths
- Insert your favorite expletive in this sentence “______ pivot tables” and shout it.
- Use Power Pivot
- If Power Pivot cant be used, copy the pivot as references and manipulate the layout as you wish.
Happy pivoting.
One Response to “SQL vs. Power Query – The Ultimate Comparison”
Enjoyed your SQL / Power Query podcast (A LOT). I've used SQL a little longer than Chandoo. Power Query not so much.
Today I still use SQL & VBA for my "go to" applications. While I don't pull billions of rows, I do pull millions. I agree with Chandoo about Power Query (PQ) lack of performance. I've tried to benchmark PQ to SQL and I find that a well written SQL will work much faster. Like mentioned in the podcast, my similar conclusion is that SQL is doing the filtering on the server while PQ is pulling data into the local computer and then filtering the data. I've heard about PQ query folding but I still prefer SQL.
My typical excel application will use SQL to pull data from an Enterprise DB. I load data into Structured Tables and/or Excel Power Pivot (especially if there's lot of data).
I like to have a Control Worksheet to enter parameters, display error messages and have user buttons to execute VBA. I use VBA to build/edit parameters used in the SQL. Sometimes I use parameter-based SQL. Sometimes I create a custom SQL String in a hidden worksheet that I then pull into VBA code (these may build a string of comma separated values that's used with a SQL include). Another SQL trick I like to do is tag my data with a YY-MM, YY-QTR, or YY-Week field constructed form a Transaction Date.
In an application, I like to create a dashboard(s) that may contain hyperlinks that allow the end-user to drill into data. Sometimes the hyperlink will point to worksheet and sometimes to a supporting workbook. In some cases, I use a double click VBA Macro that will pull additional data and direct the user to a supplemental worksheet or pivot table.
In recent years I like Dynamic Formulas & Lambda Functions. I find this preferable to pivot tales and slicers. I like to use a Lambda in conjunction with a cube formula to pull data from a power pivot data model. I.E. a Lambda using a cube formula to aggregate Accounting Data by a general ledger account and financial period. Rather than present info in a power pivot table, you can use this combination to easily build financial reports in a format that's familiar to Accounting Professionals.
One thing that PQ does very well is consolidating data from separate files. In the old days this was always a pain.
I've found that using SQL can be very trying (even for someone with experience). It's largely an iterative process. Start simple then use Xlookup (old days Match/Index). Once you get the relationships correct you can then use SQL joins to construct a well behaved SQL statement.
Most professional enterprise systems offer a schema that's very valuable for constructing SQL statements. For any given enterprise system there's often a community of users that will share SQL. I.E. MS Great Plains was a great source (but I haven't used them in years).
Hope this long reply has value - keep up the good work.