Tag Archives: PIvot Table

Pivot Report Sharing

Here is a fast paced, 8 minute video which will revolutionize Pivot Report Sharing. Using OneDrive or SharePoint, control the visibility and provide full interactivity. Reports render instantly even with large files and low bandwidth.

Pivot Report Sharing articles

New, Improved way of delivering Excel reports: Part 1
New, Improved way of delivering Excel reports: Part 2
New, Improved way of publishing Excel reports: Part 3

How to calculate YOY growth in Excel Pivot Table

Estimated reading time 3 min. Works with ALL versions of Excel.
The data is a list of sales transactions, two columns – amount and date.
We have 5000 transactions over many years. We want to know how the business grew year on year. Here are the steps…

SNAGHTML48223f                  image

Continue reading How to calculate YOY growth in Excel Pivot Table

How to create a regular Pivot Table from Power Pivot Data Model

Power Pivot Data Model adds database like features to Excel. Power Pivot allows you to handle millions of rows of data, from multiple data sources and lightening fast analysis. It also eliminates the need for using VLOOKUP function for decoding codes into descriptions.

Power Pivot does NOT provide Grouping, Calculated Columns and Calculated Fields features. These need to be managed using related tables and DAX formulas. This can be a limitation in some cases – especially when you are new to Power Pivot.

In some cases, you want best of both worlds. The database consolidation, relationships and performance of data model. But at the same time, you also want the features of Pivot Tables which you are used to for so many years.

The good news is that you can do it quite easily. Here is how (requires Excel 2010 & higher).

Create Pivot Table from Power Pivot Data Model

  1. Create the data model as required.
  2. Now go to the Excel workbook and choose Insert – Pivot Table.
  3. Please note that there is no data in the Excel sheet.
  4. Now, from the dialog, choose the checkbox Choose this workbook’s data model
  5. A regular pivot table is now created.
  6. All the expected features are now available.
  7. That’s It. Enjoy.

Create Pivot Table from Power Pivot Data Model

What did I learn today: Pivot Table Transpose

Consider a pivot table which has many fields in row as well as column area. Now, for whatever reason, you have to transpose the pivot table. Whatever is in the rows has to go into columns and vice versa. We cannot use Paste Special Transpose with Pivots.

The only choice seems to be manually dragging and dropping fields across row and column areas. Not only is this cumbersome, but it can also lead to mistakes. Don’t worry. I just found a smarter way.

Add a Pivot Chart. Never mind which type. Choose Pie because it takes least amount of effort graphically and it happily ignores child series of data. Now click inside the chart. Choose Design tab and click Switch Rows / Column. It instantly transposes the row and column fields. Delete the chart. Job done.

Pivot table transpose  image  image

This works with Power Pivots as well. For large pivot tables, you may get the maximum series limit reached error for charts. Ignore that error and continue – because in this case, the chart is just a temporary means of achieving transpose operation.

Are you making this dangerous mistake while comparing percentages?

This dangerous mistake while comparing percentages: I really don’t know how the world is still working. This is probably the most useful service I have done to humanity till date! It is dangerous mistake and is extremely common.
Read it NOW or view the 5 min video. Share it with everyone you love.

Continue reading Are you making this dangerous mistake while comparing percentages?