Quite a long time ago, I wrote a post Fill in the blanks – quickly to talk about the tip to turn a human-reading-freindly table into an Excel-friendly table, as shown below:
Have you ever thought about why we are doing this? Of course you know because we need the “right” table for further analysis. Let me rephrase my question: Where is that Excel-unfriendly table on the left coming from? Bingo. #PivotTable in #Excel itself.
Most of the time (in my experience) your colleagues summarized / aggregated data in a pivot table, then copy and paste it as value before sharing to you… Sound familiar?
This kind of problem is not uncommon, even nowdays. Why I am saying this? Because there is an easy way to fill in the blanks in Pivot Table itself but not many people are aware of it yet.
Go to Design under PivotTable Tools –> Report Layout –> Repeat All Item Lables 
Note: Make sure the pivot table is selected in order to have the PivotTable tools on the ribbon activiated
Here we go!
As simple as this.
Did you know that this function is available since Excel 2010? Yes, it has been sitting there on the ribbon for 7 years! 🙂
I always wonder… How many years it takes for users to be aware of the exisitence of some new cool features/functions in Excel? Probably when new turns old. 😛
Do you know any cool functions/features in Excel that has been left unnoticed for too long? Please share with us in comment.
wmfexcel 
Hello MF,
here is my second one. I use it with an inventory file in csv format but you can use .xlsx as well. Combine it with the instruction above and in the future you are three clicks away from desired result.
Manual refers to Excel 2003, unless otherwise stated.
The following steps are necessary:
Save the file on your computer.
Open a new Excel sheet.
Get xyz. csv under Data -> Import external data -> From text file into the sheet.
Perform all desired formatting and then turn on the autofilter.
5.Under Data -> Import external data -> Set the parameters for the query and confirm with OK.
Save this file; under Tools -> Customize -> Commands -> File category, select the command “New…” and place it with the mouse in a prominent position.
Click with the right mouse button, enter a meaningful name or symbol and assign a hyperlink to the saved xyz. csv.
In the future, the file can be opened directly in Excel, Word or even from Outlook. The “Update Query”window will then open automatically. Clicking on “Enable automatic update” opens the location of the updated file. The file name is already entered. All you have to do is click on “Import” and everything is already updated.
Important: The updated file must always be saved under the same name in the future. There must not be any additions, such as xyz (1). csv or similar.
Sabine
LikeLike
Thanks for sharing your tips. Appreciate it 😀
LikeLike
Hello MF,
here is one of my prefered:
QuickTip: Save filter criteria easily
Versions: Excel 2016,2013,2010,2007 and Office 365
Excel offers powerful filter functions to evaluate lists in your workbooks. A few mouse clicks, for example, are enough to display all addresses from a certain city, the turnover above a certain amount, etc. If you regularly perform the same evaluations over and over again, you have to redefine the desired criteria each time you change filters, but there is a little-known function to save your settings and then call them up again at any time with just a few mouse clicks. We are now talking about Excel 2016, 2013, 2010 and 2007:
1. Assign a filter in your table.
2. Call the function View-> Workbook Views -> User Default function.
3. In the following dialog box click on Add and enter any description for the current filter criteria (e. g.:”Customers from Hongkong”) and confirm with Return or Add.
4. Repeat steps 1 to 4 for all combinations of criteria that you need on a regular basis.
In the future, you can access your AutoFilter criteria at any time by selecting View Workbook Views User Default. views and select the desired entry by double-clicking.
In Excel 2003, proceed as follows:
1. Right-click in one of the Excel toolbars and select the Customize shortcut menu.
2. Go to the Commands tab and select the View category.
3. Click on the “Custom Views” entry in the right-hand list box and drag it to any position in the toolbars while holding down the left mouse button. Then exit the dialog with Close.
4 .Assign a filter in your table and click in the newly added Custom Views field.
5. Enter any description for the current filter criteria (e. g.:”Customers from Hongkong”) and press Return.
6. Confirm the following dialog with Ok.
7. Repeat steps 4 to 6 for all the combinations of criteria that you need on a regular basis.
In the future, you can access your AutoFilter criteria at any time by selecting the desired item from the Custom Views list.
LikeLike