This is the sequel to the Excel bits for the OC like me. If you like organizing your data, you gotta learn some Excel features no matter how small the function is! One of the most overlooked functionality is Excel tables.
I have seen colleagues send and present flat sets of data with overly formatted tables with borders, colors, headings in bold or what, and merged cells just to consolidate or analyze their data. And when one needs to be updated, they apply the format manually to the new cells. They use VLOOKUP when working on some other sheets, but miserably fail to achieve the purpose because some cells have some different arguments, mainly due to the table having merged cells, or non-dynamic arrays. Next month comes, it’s the same old stuff but with different formats or so. It doesn’t sound much of a problem, but if a) your work depends on it, b) you’re a productivity freak, or c) you’re an OC dude, you must be stressing over this for quite some time.
I’m a lazy person. As much as possible, I would rather spend my time looking for ways on how to make my data presentation easier to make and analysis flexible to update, than dump all the numbers manually or do some more clicks on the mouse. Luckily, my stubbornness to be a working student introduced me to Excel tables.
Now, Excel tables? Tables? Range?
Okay, this is a table. The not-so-smart table. The one that you do Name Range with on the cell reference field or the Name Manager every now and then every time you add rows or columns to your not-so-smart table. The one that you manually put colors and fills and borders on.
If we click anywhere in your not-so-smart table, and go to Insert > Table > OK
your not-so-smart table turns into the Excel table I’m telling you about, which is a smart table. What can the Excel table do that your not-so-smart table can’t?
Formatted enough to scream at you it is indeed the header row. Filter-enabled so you don’t shift-spacebar your fingers to quick fatigue. And when you have a huge table that extends down to row 718, you won’t need Freeze Panes when scrolling ‘cos the headers turn into the column letters’ look and will always be visible.
2. Quick table formatting
Formatted to banded rows by default so you don’t waste your time highlighting the rows one-by-one, or apply borders manually. You can change it easily to other colors or formats on the Design Tab > Table Styles group, with easy preview so you don’t spend too much time testing out different colors, and failing to choose which match and which don’t.
3. Totals rows
Not enabled by default because not all datasets need to have sums or averages or counts, but you can easily do so by going to Design Tab > Table Style Options and tick Total Rows. You can also choose which function is needed for your data by clicking on the drop-down list on the foot of your table. It’s also formatted enough to tell you that, “Hey, your data ends right at this row so you gotta be accountant-ish to present your schedule in good form.” By the way, I’m a sucker for totals row with the top and double bottom border since I’m an accountant, but this totals row feature can calm the OCD in me.
4. It’s self-involved: Printing it alone
You wanna print table CashOut alone so you normally select it and choose Print Selected Area Only on the Print Preview screen. Hmm well that’s okay, it’s also easy but there’s a more navigation-friendly way to do it if you’re under a or b or c above as I mentioned. If you have the Print Table command in your ribbon, you’re good to go. Just select on the table you wanna print, then click on the Print Table. It disregards all other items in your sheet and just prints the table you have selected.
If you don’t have it yet, we’ll have to do some customizing on your Ribbon.
- Right click on your ribbon
- Click Customize Ribbon
- On the first frop-down menu, select Commands Not in the Ribbon
- Select Print List
- Click on Home on the right-hand part of the window. Just by default we put items in Home tab.
- Click New Group, click Rename. I’d rename it to Print. Click OK.
- Click Add
- Close the Customize Ribbon window.
- You should be able to see Print Table command on your Home tab, in your newly created Print group.
5. It’s self-involved: Naming it is easy
It can be named in the Table tab. So whatever is in the blue lines shall belong to whatever I named it to. Plus doing so makes it to have a structured reference when dealing with formula. For example, cell E3 in table CashOut can be referred to in your formula as CashOut[@Admin].
6. It’s self-involved: It’s expandable
When you create an Excel table, you will notice a small blue arrow on the bottom right of your table. Drag it to the right to add columns, or downwards to add rows. No Name Manager needed to adjust the scope of the range ‘cos it’s smart enough to recognize that whatever is in the blue lines is my ever intact table. And speaking of my ever intact table…
7. Formula that auto-fill
Type your formula in one cell, and it automatically fills up the rest of the column cells. Now here’s the thing, sometimes you would find this fail and I will still blame you for this — you keep merging cells. Okay of course, merge cells has its purpose in life, but not when consolidating and analyzing data. What I suggest you to do is, let them be redundant. For example, a good chunk of your data needs to be classified as the Asset group, let Asset fill redundantly your group column. Don’t sacrifice function for presentation. Presentation can be done is so many different ways.
8. Love it? Use it for Pivot Tables
Pivot Tables is for the lazier people *raises hand*. I’d write a separate post about it in detail. But yea, converting your Excel table to a Pivot Table is easy — just go to Design tab > Tools group and click on Summarize with PivotTable. Make sure that you specify on the lower portion of the wizard where you want your Pivot Table located.
9. Hate it? Then convert it back to range
You’ll lose all its features and still will lend you all the changes you’ve done, you, dude. Just click on anywhere in the table, go to the Design tab > Tools group, and click on Convert to Range.