By Mehul Mehta
As you know that Excel is an essential tool for management accountants, who deal in and out with financial and quantitative data and information for analyzing and further reporting for decision purpose, it is must for the management accountants to use the right tools of Excel. I have observed from my experience as Excel trainer that there are so many of the unexplored tools and techniques in Excel which are really blessings for the users but most of the users either don’t know about those tools and techniques or ignore or underestimate them.
As you know that there in business operations, one has to maintain databases for various information requirements. The major business data such as accounting transactions, supply chain data, sales data, production data, etc. are usually maintained in ERP based on relational databases such as SAP, Oracle, J.D. Edwards, Navision etc. However there are so much of other pieces of raw data which are usually not captured and maintained in ERP or structured databases. Most of those data are maintained by individuals or teams in Excel files. Sometimes there are extracted data from ERP / SCM / CRM etc. which are filtered from the systems and maintained on Excel in their “as is” form or with some additional data fields appended to the base data fields. Sometimes it is not possible to maintain some of the information on structured databases as reporting from structured databases requires professional programming which is not feasible in all the cases considering time and cost factors involved therein.
So the best solution is to keep the data in Excel files. However, strictly speaking Excel can maintain only small databases say up to 3000-5000 records. Basically Excel is not a database program but there are some advanced features called Excel Tables in Excel 2007 and onwards versions which do lot of database related work. There is also add-in in Excel 2010 called PowerPivot which provides features of relational database management. PowerPivot is regular feature of Excel 2013. What I want to discuss here is not PowerPivot but Table feature which is the basic database feature of Excel and for relating multiple databases / table, functions such as VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET etc. can be easily used. Table has lot of magical features which are otherwise not available in normal range mechanism.
Using Table Feature of Excel for Database
Before Excel 2007 also Excel users were maintaining information in Excel files. But from 2007 version, Microsoft seems to have taken seriously the general practice of using Excel as database tool. Thus in Excel 2007 the table feature is packed by Microsoft, which empowers the users to do many tasks which can be done in database such as Access. Of course it is still not comparable to Access, but in Excel 2013 various advanced database features such as using multiple tables in pivot tables are available which can be said to be formal introduction of relational database management in Excel. However the basic constraint of size of database is still there, so Excel database features can be used effectively for 5,000-10,000 records. It is enough in case of Small Business or Personal databases.
Structure of Tables
Tables are structured range comprising of ROW x COLOUMN mechanism. Each column corresponds to the different fields of tables such as employee no., name of employee, department, age, gender, data of joining, previous employer etc. and each row is a separate record.
Creating / Inserting Tables
Tables can be either directly inserted into the blank sheet (or blank area of the sheet) or any existing range (in tabular format i.e. rows x columns arrangement) can be converted into a table. Various powerful features are available to table, which are not available to the lists entered into normal ranges. To insert the new table or convert the existing range into table:
- Select the range
- Choose from Ribbon, Insert -> Tables -> Table
With any cell selected in the table Design tab (contextual tab) is activated which can be used to customize the table for changing the look, renaming, resizing, adding total row, exporting etc.
Table Name
Excel gives the name to a table such as Table1, Table2 etc. by default. You can however change the name from Design tab. Further each column is also given the name by Excel. For example if a table has 3 columns viz. Product, Price, Qty., Excel also gives name such as Table1[Product], Table1[Price] and Table1[Qty]. All the names can be effectively used in building Excel database applications.
Magical Features of Excel Tables
Some of the Magical features of table are discussed below:
- You can change the look of table to the extent you can use your creativity. Excel offers 61 pre-designed templates / styles, but if you are not satisfied with any of the 61 in-built styles, you can create your own style and give it a separate name to reuse it in other tables.
- You can customize the table with Table Style Options such as adding total row based in the SUBTOTAL function which gives you the flexibility of using various functions such as SUM, AVERAGE, COUNT, MAX, MIN etc. Also if the table is filtered the total is displayed only for the visible rows.
- Advanced sorting and filtering options are in-built into table so no need to again insert filters from theData tab.
- Automatic total feature gives flexibility to insert total at the end of the table without re-inserting formulas in case of insertion of new records / fields.
- Duplicate records can be easily removed.
- There can be multiple tables on the same sheet. It is most important for Excel projects.
- If there are multiple tables on the same sheet, each table is considered as a separate object and therefore rows and columns can be added / deleted only for a particular table without affecting the other tables in the sheet.
- If rows and columns are added to the table the formulas are automatically inserted.
- If formula is inserted in any one of the cell, it is automatically copied and updated in the entire column.
- The reference to the table in other sheet or formula will be automatically updated in case of new records / fields are inserted or deleted.
- Finally entire row / column can be selected at the mouse click without selecting the areas outside the table.
Using Data Entry Form
If you feel uncomfortable to enter data directly into table, you can also use the readymade “data-entry form” to make entries into table. The form can be accessed from QAT (Quick Access Toolbar). To incorporate the form tool on the QAT select it from the Excel Options. Data entry form can be used to add new record directly from the form without manually inserting the row at the end of table.
The data entry form looks like below screen shot:
Reconverting Table into Range
Even though tons of power features are available with Excel tables, you may not be comfortable with the tables and you want to convert the table into normal range, then it is very easy. Just select any one of the cell within the table, click on Design tab and then click Convert to Range on the Tools section. The entire table will be converted into the normal range. Any color formatting will remain as it is but the formulas which use the table and columns / rows names will be converted into normal formulas without using table components names.
Effective use of Table Technology
In tables each field (column), each record (row) has inbuilt name which can be directly used. In addition the table itself is named and can also be renamed. So various analytical functions such as SUMIFS, AVERAGEIFS, COUNTIFS etc. can be easily used effectively with the tables in place. Of course it is not possible to elaborate each and every artistic and creative way of using tables, I will recommend to start using tables wherever you have to maintain information in database format and then explore the features of Table. You will finally master the tables and will surely enjoy using tables in analytics and reporting.
About Author:
Mehul Mehta is Excel expert and currently working with KEC International Ltd. as Senior Manager and head of commercial function at KEC’s Algeria operations. Mehul has worked with reputed organizations such as Deloitte, PwC, Asia Motor Works, RPG Group etc. For any queries related to Excel Mehul can be approached at camehul123 @ gmail.com.
