3 Hidden Power of Excel You Must Acquire in 2020

The 3 Hidden Power of Excel are Power Query, Power Pivot and Power View.

These 3 Powers of Excel have been around since Excel 2010, the 4 versions behind the current one. When they were introduced, there were not many experts who could convinced me how powerful these 3 functions were. These 3 Powers were not easy to use and users had to make major changes to their existing process. But over the years, Microsoft enhanced the functions and made a 10-notch improvement in the way you should use Excel.

Power #1: Power Query

With Power Query, you do not have to repetitively clean your data anymore. What Power Query does is that it will capture the steps you did to clean the data from its raw form to the report friendly format. And once it is done, it will continue to do exactly the same steps week after week until you changed it. It is like a robot, once you do it once, it will follow your exact steps to reproduce the exact outcome you wanted. There is no Macro / VBA involved at all. 

This means that if you are currently using half a day a week to prepare the data, you can save this half a day every week and use it for higher value work. The increase in productivity is instantly measurable. We have used this function in multiple scenarios and one of the scenarios which could help you save time is in budgeting. Using Power Query as part of the solution, we have shortened the budgeting cycle from 6 months to one hour. If you are taking a long time to do your budget, it’s time to check out our solutions. Drop me a message and find out.

Power #2: Power Pivot

Have you heard of Pivot Table? If you have and maybe have used it, then you probably did not encounter Power Pivot. Before I tell you why, let me explain what is Pivot Table for those who have not heard about it before.

Pivot Table is a reporting function that exist in Excel since Classic/Antique Excel times. You can trace the first version to Excel 1995. That’s right, 25 years ago. It is one of those tools that saved you hours and hours of filtering just to get the total based on some criterias. Pivot Table basically list down every category for you, summarized the numbers and present them in a report for you to read off effortlessly.

Those who have used Pivot Table probably have not heard of Power Pivot because they always jumped to the conclusion that Power Pivot refers to Pivot Table. They are wrong.

Power Pivot is a big jump from Pivot Table. It fulfills the wish of those who wanted improvements in Pivot Table, such as performing calculations such as the growth percentage, the variance between 2 periods, percentage of the number against the grand total. With Power Pivot, you can create multiple reports and joined them together. With one click, all the numbers will change according to your selection. Some companies called this synchronized reporting a dashboard, weekly report, monthly report, Key Performance Indicators (KPI), Red Amber Green (RAG) Report. With Power Pivot, the reports are done for you. If you wish to drill down to look at the details, it is just a click of a button away. No matter how many times you click and change, it will not get angry with you. It will deliver every request you made instantly.

I tapped on the Power of Power Pivot and use it to prepare reports for comparison during budgeting. With the proper setup, I can easily compare actual versus budget, forecast and multiple versions of them too. And it can even auto-highlight the performance numbers that fall out of range automatically. No need to hunt for those numbers that did not meet the target among the ocean of numbers.

Feel free to drop me a message if you are simply curious about Power Pivot.

Power #3: Power View.

Excel has a very very unknown function called Power View. It is actually the same as Power BI Desktop, a data visualization tool. With Power View, you can visualize the numbers on charts, maps and tables. One of the most impressive visualization Power View could offer is a map chart, that is to present your numbers on a world map. This is especially useful if you are responsible for the performance of companies in multiple countries, states, cities, towns, etc. With Power View, the numbers can be easily displayed on the world map.

Do you know these 3 powers of Excel? If not, you are likely to be under-performing, especially if you are using Excel half the time in your job.

Send me a message if you are keen to boost your productivity. I have a training that could empower you to be more productive and boost your job performance.