Excel is ubiquitous…said so in our last blog post. If you are an experienced Excel user, you probably know all about formulas and functions but there is a very good chance you don’t have the foggiest notion of what an Excel “Table” is. After all, as an Excel user, you already know what a table is: Columns and rows of data and formulas, right? That’s a tabular data table and not to be confused with Excel “Tables.” The fact that Microsoft named the function “Tables” is semantically correct, but when long-time users see the “Tables” option they may dismiss the option as something they neither need nor want…just get on with what we have always known and forget about this extra stuff from Microsoft.
Big mistake.
In this blog post we are going to focus on the “Tables” function and show why you need to embrace this wonderful tool and recognize that it is one of the best features Microsoft ever built into Excel. Really.
Let’s start with an existing data table. This grocery store’s fruit section has some basic inventory information.
Now, let’s apply the Tables function. Just highlight the entire data table and then from the Insert menu, click the Table icon.
At first, this looks like the Excel Tables function is just some pretty formatting. But there is way more.
Formatting. Click inside a table and a new menu item, Table Design, appears at the top of the Excel menu. It includes styles, options, and tools for formatting the current table and even future tables in future spreadsheets. Rows can be banded for easier left-right scrolling. The header row is automatically bolded. Granted, this is all stuff you can do elsewhere but with Excel Tables it’s automatic.
Sorting and Filtering. The drop-down arrow in the header provides the option to sort the column or to filter out certain values. If some cells have a background color, even the colors can be sorted.
Formulas. When a formula is entered into a cell, that same formula is copied to all cells in the column. Have you ever accidentally typed the wrong column or row into a formula? It’s dangerously easy to do. With Tables though, if you get it right once it’s right for the entire column.
New Rows. Tables automatically adds new rows to the table by simply tabbing beyond the bottom row. Existing formulas are copied automatically, awaiting entry of new values in the other columns. When a new row is inserted, other content outside of and adjacent to the table is unaffected.
New Columns. To add a new column just type something in the column to the right existing table and a new column is added and given the same formatting as the rest of the table.
Moving Rows Up or Down. Highlight a row and simply move it up or down and the other rows make room. No need to worry about one column replacing another because Tables keeps track and moves existing rows out of the way.
Moving Columns Left or Right. Highlight an entire column and then move it to another location and formulas that refer to that column are automatically updated.
Named Ranges. Every cell automatically has the name of the column header so formulas can refer to names instead of column letters.
Totals Row. Turn on this feature and the totals appear at the bottom of the Table. Add a new row and the totals row moves down and adjusts to include the new row.
This post barely scratches the surface of Excel Tables. There is so much more. And to be fair, there is indeed a learning curve needed to unlock all the Tables’ functionality. Charts that automatically add new values when a column is added. Pivot tables that “understand” the Excel Table. Formulas outside the Table that are magically updated when the Table structure is changed. Dig into Tables; you’ll be so happy you did.
Enough about Excel. Here at nGAP we have often suggested that our OAS could replace the typical spreadsheets that are used to track, manage, edit, and issue contracts and contract revisions. And it’s true. So, if your visit today is in search of answers for your acquisition and contract management needs, check out OAS and ask for a private demo.