Power User vs Top feature in Pivot Table – Part 2

Ways of inserting Pivot Table

a) There are many ways to connect your data to Pivot Table. One of the most mainstream way to insert Pivot Table is hitting the “Pivot Table” command from the “Insert” tab.

b) You also can find the “Summarized with Pivot Table” under the “Table Tools” tab. You will only be able to find this table after converting the range of data into a form of Table. Plus, the cursor must be active within the Table region in order to enabling the “Table Tools Tab”.

c) If you are a shortcut key freak like me, just loving working with the command using the keystrokes, you’re going to love this. I highly recommend this if your work involve a lot of creating Pivot Report.
Alt + N + V + T : For Excel version 2010 and below
Alt + N + V : For Excel Version 2013 and above
This is called KeyTips where it allow you to work with the commands in Microsoft Application without using mouse. You can access to most commands in the ribbon using this KeyTip. The moment you hit on the Alt key, you will start to see black little card float right above the tabs.

My machine is current running Excel version 2019, hence it is only Alt + N + V. Now, if you’re using this KeyTips, don’t ever use it like how usually you use your shortcut keys where you have to hit on all those keys at the same time. With KeyTips, you have to hit on the key one by one by the sequence, not all at once.

Refreshing Pivot Report

If you’re working with Pivot Table, the biggest advantage is being able refresh your report whenever there are changes at the data source. There are many ways for you to refresh your report, so let’s see which is your way. The refresh button breakdown into 2 different type. The first one is Refresh, when you click one this button, it will apply the refresh to all the report that is currently connecting to the same data source; The second one is Refresh All. This command will refresh all the Pivot report no matter which data source that it is currently connecting to.

a) You can find the Refresh command under the Pivot Table Tools: Analyze tab. This tab is tricky, you will first have to place your cursor within the region of a Pivot Report, or else you will not be able to get this command.

b) You can also find the Refresh button in the Data tab. With this Refresh button, you will not need to place no cursor in the Pivot report, you can immediately reach out to the button.

c) This is what you’ve been waiting for, the shortcut key.
Alt + F5: Refresh
Ctrl + Alt + F5: Refresh All
To make sure that this keystroke works, you gotta make sure your cursor is being placed in at least one of the Pivot Report in order to make it work.

d) Refresh upon opening your file. Do you know that you can refresh your report as you open your file? So, you can always have your fresh report every time you open it. You can enable this setting through the Pivot Table Options window. One thing you have to know, once this is being enabled, the refresh applies to every pivot report no matter which data source it is being connecting to.

Some people might not like this feature because they want to have control of when the report is being refresh and when it is not.

e) Refresh Report with Macro. If you’re real fancy, you can add some VBA code in your workbook to enable the refresh every time the file is opened. To do this, you must first enable the Developer tab, from there hit the Visual Basic button. Then double click “This workbook”.

At the (General), select the dropdown and pick “Workbook”. The moment you do that, the code window will automatically generate the code as shown below.

Enter this line in between, “ThisWorkbook.RefreshAll”

After that, close the Visual Basic Editor window. Don’t forget to save this file as “Macro enabled workbook”, if you want to make sure your macro function as usual the next time when you open the file.
Every single time when you open the file, you will being prompted by the Excel Security notice whether you should enable the Macros.

You may be promted by the security setting in a form of ribbon. It should be in yellow. This is how it looks like:

If you’re not planning to hit on that “Enable Macros” or “Enable Content” button, the refresh code that you embed inside the file will not work. You can also set the setting in the Macro security setting window to automatically Run the Macro without asking you to enable it everytime when you start the file.
This is what you should do.
Locate this button ‘Macro Security‘ in the Developer Tab, then add the location (AKA the location path) of where you save the file as the trusted location. So all the Macro Enabled files that are being saved at the same location will not be prompted by the Excel Security Notice.

After all the setting has been done set up, click OK. Try to close and reopen the file, the refresh will Run the moment you open it. Meantime you will not be prompted by the warning again.

Please follow and like us:

Leave a Reply

Your email address will not be published. Required fields are marked *