If you use Office 365 Pro Plus and Excel 2016, you should see a new tab under File – Options. The Data tab. This has many useful features which help improve Pivot performance. In addition, it also helps you with incorporating Data Models more easily into your Excel data management process.
If you do not see the Data tab in File – Options, you are not using the the right version of Excel. Escalate to your IT team to set it right.
Here is a set of Practical usage guidelines for some of these new options.
Continue reading Pivot performance improvement options
This is a sneak preview of my upcoming Udemy course on Pivot Tables. This video explains the relationship between Pivot Tables and File Size. Watch this 6 minutes video and learn from it.
Apply this knowledge to your existing pivots – especially those with large file sizes and see the difference. Make sure you try it out on a copy. Never learn using Live files.
Continue reading Pivot Tables and File Size
Here is the list of articles I have written about Show Values As in Pivot table. This is a very important set of features but are mostly ignored.
Continue reading Show Values As: Knowledge Pack
A simple but very useful method. All of us use AutoFilter in Excel. The dropdown shows unique values in the column. Sometimes we just want to see the rows with blank cells. Unfortunately, the (blank) item appears at the end of the list. If the data contains many unique values, you have to waste time and scroll manually to the bottom of the long list. Often, after reaching the list we realize that we should actually have unselected all the items first. Then you go up again! Pathetic. Here is a smart way to Filter on Blanks instantly.
Continue reading How to Filter on Blanks quickly
I am happy to announce that my first detailed training course is now up and running at Udemy. It is about Power BI. But it starts with what you already know – Pivot Tables. That is why the course is called Pivot Table to Power BI.
Continue reading Announcing Pivot Table to Power BI course
You must read this article first: Grouping Text in Pivot Tables. This article explains how to combine multiple text items into a single group. This is used for classifying or for correcting spelling mistakes. If new items appear, they remain ungrouped. That way, you have to keep doing the Grouping update repetitively. An alternative is to use VLOOKUP based text grouping. Read on to find out how this is done.
Continue reading VLOOKUP based text grouping
Data Analysis and Reports has been a major theme for my articles so far.
Here is a collection of 50+ articles, categorized and sorted in the recommended reading order.
Continue reading Knowledge Pack: Data Analytics
Very common problem. Very easy solution. Deserves a separate article. This alone can save hundreds of thousands of person years of drudgery globally every day.
This article shows the problem and the solution in a concise way. Quick and Easy.
Continue reading Bad Data to Good Data: Pivot Copy Paste