Microsoft Excel Pivot Table Tips & Tricks
9.1 Not utilizing table object enough
In fact, many people always mistakenly thinking the range that has been highlighted with borders, cell fills are table. Oh well, couldn’t blame you if you’re thinking the same way. Once an instructor said, Table has one of the most boring and normal name which is why people have always mistakenly treat a formatted range as Excel Table.
After going through this article, I hope you understand Table object in Excel better. In order to use table, I can say it is the easier thing you could create. Plus, it jams packed with so many benefits. It’s your lost if you do not use it. Table object compliments so many of the other cool awesome tools in Excel.
This is what is used to think it is Excel Table too.
Well, I have been wrong for almost a decade until I was trying to find a solution to automate certain of my calculation and formatting, I came across table this object. Alright, I’ll admit. I’m a self-taught Excel user too. So, many times I struggle to get my report perfect and I realize I have non-stop reaching out to that Format Painter tools to keep the whole worksheet look align and tidy. Besides, whenever I have my data being updated to the range, my formula just won’t update. I’ll have to spend quite good amount of time to get everything updated.
Create Table
Let’s get back to this table topic. So how easy it is to create this Table object and why am I praising it like I’m praising to Jesus. You’ll know why, it is so awesome that I couldn’t get anything almost anything away from Table Object.
To create table, it is as simple as Ctrl + T. Yes, just that, you will get to enjoy the awesomeness of this incredible tools
Make sure the Table has all the cells covered. If your table contains header, make sure the header is checked. Sometimes the Table object can’t recognize the header especially when your range is filled with text/string. The headers will be checked mostly is when your data contains string/text and value, which easily can be identified by table. Hence the reason why sometimes the header check box is checked and sometimes it is not.
When the header checkbox is not check automatically
Once the table is created, you can instantly turn a boring blunt looking range into a proper formatted Table object. It is looking handsome now isn’t ?
Self-Expand Mechanism
One thing I enjoy having Table the most which is that self-expand mechanism. Which means while you’re aggregating a field in the table, you do not have to constantly update the size of the range. The formula can simply refer to the field/column, and whenever the column has expanded or shrank, your formula will automatically re-calculate based on the most recent size. Isn’t that cool ?! no more manually update the range of the formula.
Example for having normal range
Example of having table with formula
Take a look difference whenever the data is updated. When you’re using normal range and when you’re using the column reference of the table.
If it can update your formula easily, it definitely can update your data source for your Pivot Table. Remember you used to highlight the range and insert as Pivot Table? What happened whenever the range get updated? You’ll have to manually update the Data Source eh. Now, with Table, it happened automatically. You simply have to refresh the Pivot Report, no kidding.
Auto Filling
Endlessly dragging and filling up formula? This drive me insane especially when I was trying to copy the formula and fill down, the dragging seriously is a waste of time. With the help of Table, it automatically fill down the cell from the first position to the last.
Freeze header
Besides, you no longer have to waste so much space while freezing the row. I guess you’re familiar with freeze pane command, you know you will be needing it while you’re dealing with many rows of data. Using Freeze Pane it allow Excel to lock the position of the row or column so it will remain visible whenever you’re trying to scroll the worksheet. But to be honest, usually the freeze pane doesn’t take place at row No. 1 or column A, it usually starts way way way below. Don’t you realize with that few rows being frozen at the most top of the screen causing you not being able to fully utilize the whole worksheet?
So, by default, the Table comes with this capability of freezing your first row (similar with the freeze pane function). The best part is while you’re scrolling the worksheet, the table fully utilizes the whole sheet area, and it only will lock the header when the header reaches the column headers.
Click here for Part 9.2 of this article.
Related Articles: Part 1, Part 2, Part 3, Part 4, Part 5, Part 6, Part 7, Part 8
