Recent Microsoft Excel Tables Project

Where we used Excel Tables instead of an Access Database

We recently completed an Excel Development project for a custom guitar builder.  The Excel solution was mostly built with Excel Tables.  Lots of Excel Tables.  The Tables provided the data structure we needed, to build a semi-relational database, in Microsoft Excel.  Excel Tables versus Access Database, Excel as an alternative to a database.

Yes, I know, Microsoft Excel is not a database, I wrote that post.  But with that said, we have built many solutions in Excel that were originally intended to be Access solutions.    Access solutions cost a whole lot more than comparable Excel solutions.  As a result, we build a lot of custom Excel solutions that provide the same results as a database.

SumIfs, XLookups, and Power Query, that is where most of the heavy lifting is done in this solution, but here Tables are the star.  Their structure made the solution possible.

But unlike Access, Excel is a powerful Data Visualization Tool, with Custom Dashboards, Pivot Tales, and Charts.

What we did for this client was to build an Access-Replacement solution, in Microsoft Excel. The Excel solution would allow them to process orders, and receive payments, as well as include a custom Dashboard package for reporting and analysis.  The solution would work on all of their computers, running Excel 365.  There is no need to pay for Microsoft Access on each PC.

 

 

Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261

Contact us for a Free Consultation Today. We will build you a custom Excel Solution, based on Excel Tables.

 


 

Microsoft Access Databases are Based on Tables

Just like a good Excel solution is based on Tables and Power Query

 

Excel Tables versus Access Database – an interesting concept is it not?

Access databases are based on Tables, as are all databases.   Without Tables you would not have a database. The tables can be internal and / or external.

Access is a relational database.  In a relational database, you use joins to create relationships between Tables.  You do so with Primary Keys and Foreign Keys.

While Excel is not a relational database, you can try to mimic one, using Tables with Power Query, and the Data Model.  In doing so, you can get many of the same end results, just a different method of getting there, often a far cheaper one.

Primary Components of an Access Database:

  1. Tables
  2. Relationships
  3. Queries
  4. Forms
  5. Reports
  6. VBA

 

There are a Variety of Ways to Manually Enter Data in Excel

If the client had gone with an Access database, the database would be forms drive.  Meaning, the user would use Access forms, to manipulate the database.

They would use the Access Forms to 1) manually enter data, 2) they would use Controls on the Forms to run Macros, to update the file.

So, if we are going to do this in Microsoft Excel, we would have to choose a method of manual data entry, preferably into a form.

We have three solid methods, so we needed to see what would work best for this client.  Our choices were:

  1. Excel UserForms.
  2. Tab based Forms.
  3. Excel Tables w/ Power Query
  4. Directly into a cell – Don’t do it!!!!

 

Our Choice for Excel Tables versus Access Database

For our use, the Tables approach was the best method, far less code than UserForms, but same approach, based on Drop-downs and Text Fields.  One method is in a Form, one in a Table.  We went with the Table approach for obvious reasons.

Note:  Our Tables used unique, Cascading Validation Controls for the Drop-down feature.  Unique to each cell in the Table.

  1. Cells with unique cascading validation controls
  2. Cells with functions
  3. Cells to manually type text and / or numeric data
  4. Conditional Formatting
  5. Each cell was a Named Range
  6. VBA was used to clear the choices after the append as well as doing the append

 

In Microsoft Excel there are 10 ways to do each task.  At least it feels that way (Lookup, Index/Match, HLookup, VLookup, XLookup, etc.).  It is no different when it comes to entering data.  What determines which method, or methods you use, depends on your client, and their unique set of needs.  Hence, knowing all top methods is always wise.

 

Three Common Approaches – Excel Tables versus Access Database

There are three common methods for manual data entry in Excel, and they are Forms based.  Well, you could also use the just type into any cell desired approach.  Manual data entry should be controlled.

 

We used Excel Tables in place of Excel UserForms

Excel UserForms:  For years we have programmed Excel UserForms for our clients.  They have long been an integral part of Excel.  But they take much effort to program, and they can be buggy at times.

Image is not from this project; we did not use UserForms here

 

Image of Excel UserForm


Excel UserForms are powerful, and they have many uses. But they are code based, and they take great effort to program. Why not just use an Excel Table? Excel Tables versus Access Database.

 

 

Excel Tab Forms:  Often in lieu of UserForms we choose to build forms using Excel Tabs.  For this we use Validation Controls, Formulas, and data entry cells.  See Excel UserForm Below.

Image is not from this project; we did not use Tab Forms here

 

Image of Tab based Form in EXcel.


Here we built the custom form on a tab in Excel. Far less code than the UserForm we built above. Excel Tables versus Access Database.

 

 

Excel Table:  With the recent changes in Dynamic Excel, it is time to embrace Excel Tables and Power Query.  The DAF’s make extracting the data you need from Tables ultra easy.  See Image Below.

Here, Excel Tables, Cascading Validation Drop-Down Controls, the XLookup and a little vba is all that is needed.

Plus protection of course. And if you apply the protection, the user can tab through the cells to populate, what could be easier.

 

Image of Excel Tables used for data entry


Two Excel Tables, one-to-many relationship, used for data entry. Instead of using Microsoft Access, or Excel UserForms. Excel Tables versus Access Database.

 

Which of the three methods works best for you and your client will depend.  But if you have not tried using Excel Tables as Interactive Forms, you might want to give it a try.

 

 


 

Dual use of Excel Tables for Data Entry

In this custom Excel solution, (see image above) we had two uses of Excel Tables.  All the Tables did store data, but how and why the data was placed in each Table is different.

Furthermore, for each data type, we had two Tables, one Detail Table, one Summary Table, a one-to-many relationship.

  1. We created several Tables to store product pricing information.
  2. We used four Tables as Data Entry Forms.  Yes, we used them like forms, and less like Tables.
  3. We used Excel Tables to store the transactions.

 

Tables are not always used as Forms, I do not recall ever seeing them being used quite this way, but they work well, and we have used them this way, on many a project.

 

Use the tool as it best works for you; learn to bend the rules.  There is no box.

 

 


 

We added Power Query to the Excel Solution

For ultimate power, upload your Excel Tables into Power Query, Transform the data, and then download the Power Query Table onto an Excel Sheet.  Power Query is much more powerful, efficient, and often easier to use than the traditional Excel functions.

 

Microsoft Power Query is one of the most amazing and most powerful features inside Microsoft Excel.  Microsoft Power Query integrates with Excel Tables and Excel Pivot Tables, natively.  What does this mean to you?  It means that you can build a much better business solutions, with fewer moving parts, in a fraction of the time of traditional Excel solutions, based on formulas and VBA.

The proper combination of Power Query with Excel Tables is a game changer.  For best results, hire our team of Power Query programmers.

Image of Excel Table into Power Query, down to Excel

1) Excel Tables uploaded into Power Query, 2) Results downloaded into Tables and Pivots. Dates in PQ, so much easier than in Excel.

 

Splitting Columns is much easier in Power Query than in Excel

Look at the image below.  Power Query can Split Columns by Digit to Non-Digit, by Lowercase to Uppercase.  Try that in Excel, without writing functions.  Excel should be easy to use, and if you use Excel with Power Query, it is.

 

Image of Power Query being used to manipulate Excel data. Excel Tables versus Access Database post.


Forget Left, Mid, Right, Find, Count, TextBefore, TextAfter, etc., etc., etc., and use Power Query to manipulate your data.

 


 

For the Reports we used Dynamic Array Functions  (DAF’s)

Long gone are the days of placing a formula in each cell ( Cell-based programming ) of a report range.  In Dynamic Excel you write formulas at the range level; one function, Spills into adjacent rows and columns; we call this range-based programming.

Here, the user picks an Order Status from the Drop-Down Validation Control, and all of the details, for all matching orders are shown.  One cell, one function, no use of vba.

 

 

 


 

For the Receipts we used the XLookup and SumIfs Functions

We built two receipts into the system, both use the same two functions to grab the desired data, from the Excel Tables – XLookup & SumIfs.   If you build your Tables right, you should be able to extra the desired data easily, with just these two functions.

Build your Excel solutions like a database, use Tables, and create relationships between them.

For ultimate power, upload your Tables into Power Query, Transform the data there, and then download the Power Query Table into Excel.

 

 

 

 


 

 

Cascading Validation Controls via Filters Function

Validation Controls allow you to make a selection from a drop-down list.  Cascading Validation Controls are usually based on two or more Drop-Down Validation Controls, and the choice in one impact the choices in the other.

 

Basically, you make a selection in the first list, and that determines what you see in the second list.

 

There have long been methods of creating cascading Validation Controls, Indirect Function being one of them.  But Indirect is volatile, so you do not want to use that.

The Unique Function works well, to feed Cascading Validation Controls. In the image below, you can see the use of the Filter Function to Cascade the Validation Controls.   The “Mode” selected will populate a series of validation controls, as see in the image below.  This ultimately determines the cost of the items being purchased.

Note:  The BLUE cells are the Spill Cells.  The Validation Control references the Spill Cells #.

 

Image of Cascading Validation Controls. Excel Tables versus Access Database post.


Cascading Validation Controls, via th3e Filter Function.

=FILTER($J:$J,$H:$H=N5)

 

 

Second Set of Validation Controls

We used two dynamic array functions for this series of cascading validation controls.  We still used Filter, but we added ChooseCols.  Here we use multiple criteria.  Same end result.

 

Image of other Cascading Validations. Excel Tables versus Access Database post.


The DAF’s are making Excel programming a snap.

=IFERROR(FILTER(CHOOSECOLS(tblServiceRates,Y6),(CHOOSECOLS(tblServiceRates,$J$6)=Y3)*(CHOOSECOLS(tblServiceRates,$I$6)=$U$3)),”n/a”)

 

 


 

 

Conclusion: Excel Tables can be an Access Database Replacement Tool

Our company is named “Excel and Access, LLC”.  We primarily work in Excel and Access.  So why would we build solutions in Excel, versus in Access?  Cost.

Excel Tables versus Access Database, which do you prefer

If you want a reliable solution, that is easy to use, easy to update, one that runs itself, then base your next Excel solution on the new Dynamic Excel.  We recommend Excel Tables versus Access Database when cost is an issue.

  1. Excel Tables.
  2. Power Query.
  3. Data Model.
  4. Dynamic Array Functions.
  5. Pivot Dashboards.
  6. Vba.

 

Minimize the code, minimize the number of functions used, use Tables instead of Ranges, and you will have a better solution.

 

 

 

Hire our Team of Excel Experts

We can build you a completely custom system using Excel Tables, Power Query, the Data Model, plus the Dynamic Array Functions and some good old fashioned vba.  Fully integrated, fully automated, and easy to use.  We offer free consultations via Zoom where we can screen share some of our work with youPlease call today.

 

 

Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261

 

Contact us for a Free Consultation Today. We will build you a custom Excel Solution, based on Excel Tables.

 

Image of Contact Banner