FLUID Excel Solutions for Any Organization

~ Custom Excel 365 Solutions w/ Power Query for Business, Government, Education, Non-Profits, & Individuals ( We Mentor ) ~

 

 

Excel 365 changes almost daily now.  Microsoft Excel is in a constant state of change.  As such, so should custom Excel programming.   Hence FLUID Excel solutions.   The solution we program today will be different than the one we do in a month or two, it is changing that quickly.

At Excel and Access, LLC, we’re pioneering a new approach to Excel development—one built around Excel Tables, Power Query, and Power Query Tables as Data Forms.

A respected leader in the Power Query community once observed that I had “taken Power Query beyond cleaning and shaping data into something else,” describing it as a new method of Excel development. That philosophy has become the foundation of how we build solutions. This approach is shared by other developers that I work with.

 

 

Are you ready?  If not, if you need help, we offer mentoring services in Excel development.

Call for help today 877-392-3539

 

 

---

 

 

Challenges in Being an Excel 365 Expert.

Microsoft Excel is no longer programmed as Excel was in the past.  The Microsoft Excel Team has put a significant amount of effort into expanding Excel 365 capabilities.   As such the functions, features, tools available to the programmer are forever changing.

Excel is now ‘FLUID’, for the professional Excel developer, that changes things significantly, as one must ‘stay‘ the ‘expert‘, constantly.

 

If you want to be an Excel expert these days, you must learn learn learn: 

Excel 365 changes so frequently that the programmer must be constantly learning new functions, features, etc., and then the developer must learn to apply these new techniques in their new projects.

This means one must first learn, then one must practice practice practice.  Then you can use it in a custom Excel solution.

 

It is now Old-School or New-School, or Both:  Forget Index/Match, the VLookup, excessive use of macros, etc., ( Old-School).   Microsoft Excel 365 is now based on Tables, Power Query, and the new Dynamic Array Functions such as PIVOTBY, ( New-School ).  Formulas are now placed in one cell, and they will Spill as needed.  This is range-based programming ( Arrays ), versus cell-based programming.

 

That said, being an expert in Old-School, and then New-School, is as valuable as being an Accountant before transferring into Finance.  More skills and tools to choose from, an expert in both worlds.  Old-School skills and experience are priceless.

 

FLUID Excel Development is the way Microsoft Excel is NOW programmed.  Continually evolving, interactive, automated solutions, built for the user, based on the best tools, techniques, and best practices available in Microsoft Excel 365 and Power Query.   As new functions, tools, objects are rolled out, we will implement them in our new solutions.

 

Our Excel 365 solutions are continually evolving; thus FLUID Excel 365 Solutions.

 

The solution we program today will be different than the one we do in a month or two, it is changing that quickly.

 

 

---

 

The top 5 Components in a FLUID Excel 365 Solution

This is the Important Stuff

Microsoft Excel 365 has hundreds of functions, dozens of features, tools, methods, objects, etc., to use in your custom solutions.  But you do not need most of that, maybe 10% of it, if that.  For a robust, easy to use, easy to automate custom Excel solution, base everything on Tables, Power Query, Pivots, DAFs, and Code.  This is the easiest way to program Microsoft Excel, really.

1. Excel Tables.
2. Power Query Tables.
   1. Based on Excel Tables loaded into Power Query, Transformed, then loaded to Sheet.
3. Pivot Tables and Pivot Charts with Slicers.
   1. Via Power Query:  Based on Queries, Transformed, then loaded to a Pivot Table or a Pivot Chart
4. Dynamic Array Functions ( DAF’s ).
   1. Many of the older functions like the VLOOKUP work like a DAF ( They Spill ).
5. M-Code, VBA, Scripts, as needed.
   1. Power Query reduces the need for VBA.
   2. M-Code is the next frontier in Excel 365 automation.
6. Other Considerations:
   1. Concurrent Users: Teams/SharePoint/OneDrive.

 

 

Excel Expert Note:  Excel Development BEGINS with Tables ( Excel Tables, Power Query Tables, Pivot Tables ).

 

Design Determines Everything; Think Before you Touch the Keyboard:

Unless you need to manually type, or select, there is no user needed in a FLUID Excel 365 Solution.

If you design it correctly.   We’re here to help – Free Consultations.

 

---

 

 

---

 

 

Excel Tables in Microsoft Excel 365

Tables, Tables, Tables.  Did I mention Tables?  If not, let me, Excel Tables are the most IMPORTANT component in Microsoft Excel 365 solution.  Period.

Every Excel 365 solution should start with Excel Tables.  Period.

 

 

Excel Tables are where a new Excel solution begins, with the design of the first Table. 

Proper Excel files are based on Tables ( Excel Tables, Power Query Tables, Pivot Tables ).  But that is another post.  For now, know this, Excel Tables are the most important component in most Excel 365 solutions.

Excel Tables are the foundation on which everything else is based.

 

Why Tables, because Tables are objects that are built to house data.  Sure, you could just place data in cells, in a range, that looks like a Table, but it will not work like a Table, and referencing the data will be harder than it would if it were in a Table.

Not going to go all into why you should use Tables, we have other posts on that, for now know, use Tables.

 

Tables do more than Store Data

Expert’s Note:  You can do a lot with Excel Tables, more than just place data in them. Tables can be interactive, dynamic, and automated.  Tables can tell you a lot about your data, before you ever get to the reports and analysis.  The data is trying to tell you something, use the tools available to listen.

• Drop-Down Validation Controls often reference Excel Tables, making it easier for the user to correctly enter/select the desired information.
• Add CheckBoxes, Functions, Conditional Formatting, Data Validation, and Slicers, to make your Tables come alive.
• Add Calculation Columns to Power Query Tables, for onsheet calcs.
• Note: Columns in one Table may look at other Tables for their values.
• Tables make the most amazing data entry forms.  More to come.

 

 

 

Using Tables as Forms

We used to build Excel UserForms.  We used to build OnSheet Forms.  Now we use Tables as Forms.

For basic data entry, new records, we use Excel Tables.  To manipulate existing records, we use Power Query Tables as Forms.  We can recall, revise, edit, append, and delete, as needed.  Very little vba is actually used with this method.  It is immensely powerful.

 

 

 

Microsoft Excel 365 Tables used as Data Entry Forms

We do this all the time, in almost every solution where we need to add records to a Very Hidden Table.  One Table, as the data Entry Form, the other Table holds historical data.

Why “Very Hidden“, you do not want to allow the users direct access to the data Table.  Instead, you use Tables as Forms, to allow the user to create the record.  When the push the + button, the record is added to the bottom of the hidden Excel Table.

You can add calculations, drop-downs, CheckBoxes, Slicers, Subtotals, and more.

 

 

Keep Users Out of your Data Tables – Use Data Entry Forms

 

 

 

 

Excel Tables loaded into Power Query

Loading Excel Tables into Power Query really changes the solution; so much more powerful.  Now you can use Joins, in Power Query, for dynamic results.  Joins instead of using SUMIFS or XLOOKUPS between Tables, onsheet.

 

Advanced PQ Method:  Default Values in Tables and Power Query:   Excel Tables as Lists, loaded into Power Query, with a designated Default, to be used in Power Query.  The User can easily change the default, and when the system refreshes, Power Query will apply the defaults.  What could be easier for the user?

 

 

 

Microsoft Excel Tables as Lists for DropDowns

Using DropDown Validation Controls provides the user with a convenient way to enter data, correctly.  If you allow users to type KPI data, they may spell it differently, and you do not want that.  As such, provide them with a list of choices.

* That said, there is a setting that will allow them to type items that are not in the list, useful at times.

These lists also allow the user to add new records, edit existing records, delete, or sort, without being an Excel developer.

 

Excel Tables as Lists is very common. You can use them in Lookups, and in Joins. Tables reference Tables. Users love them.

 

 

Override List Option in DropDowns Enhances the User Experience

Making the Excel solution ultra easy to use, that is the aim.  The user experience is very important to the user.  We know how to build custom Excel solutions where the user is the center focus.  We make it as intuitive, and as easy as possible for the user to run the file.

One example is the use of DropDown Validation Controls.  95% of the time, we want the user to select from the list, but sometimes, we want to allow the user the option, select a record, or type a new one.  Simple to do, just uncheck a setting.

 

 

Power Query Output

Power Query Table as a Report, Pivot Table, Pivot Chart, and PIVOTBY Function.  Drop-Down Validation Control feeds Year Filter into Power Query.

 

---

 

 

 

Power Query & Power Query Tables in Excel 365

Power Query is NOW where the work is done.  In the past, prior to 2018, Excel tabs was where the work was done, there and often in massive amounts of vba.  But that was then and this is now, now the heavy lifting is more and more being done in Power Query, as New-School programmers emerge.

• If you are not on LinkedIn, you might not have noticed, but the top posting, most followed MVPs are heavily in favor of Power Query, even though they have been at this for some time. 

 

The optimal solution is the perfect mix of the new, filled in with the old; If the new tools do not do it, then go to the old tools.

 

 

Power Query Transforms Data More Easily than Excel Functions

98% of Excel users would have done these calcs onsheet.   We however prefer the use of Power Query to Transform our data.    Have you tried it?

 

 

 

Transform Your Data in Power Query instead of using Excel Functions

I have found it much much easier to teach someone Power Query than say the VLOOKUP.  Power Query is largely intuitive, point and click, at the basic levels.

The basics in Power Query are VERY easy to learn.  Two to three hours one-on-one mentoring in Power Query is all you need to get started.

 

 

Power Query Table used as Data Entry Form – Edit Existing Records

Power Query is where the heavy lifting is done in Excel, we all know that.  But did you know that Power Query is the perfect tool for user Data Entry?

These are the fun solutions, the database replacement solutions.  Where we store data in Excel Tables, but we use Power Query Tables as Forms.  We can add, edit, or delete records in the Excel Table quite easily this way, with minimal vba.

If you would like to use Excel as a quasi-database, give us a call, we build these solutions daily.  877-392-3539.

 

 

 

Using a Power Query Table to Recall, Edit, Append Records to an Excel 365 Table.

Have you done this, have you seen anyone do this?

This is advanced Excel programming.  Yet, it uses a fraction of the vba needed to do this in a UserForm.    The only vba used is the code to copy the data from the Form, and Append it to the Table.

An option is to Delete the Original Record, after Appending the Revised Record, or you can keep both records, marking one as revised.

 

1. Recall one or more records via Power Query.
2. Populates a Power Query Table with existing data.
   1. The Power Query Table has:
      1. Calculations
      2. CheckBoxes
      3. DropDown Lists
      4. Conditional Formatting
      5. Slicers
      6. Grouping
      7. Data Validation
      8. Cell Protection
      9. Cells to manually to type information
      10. Default values
      11. And a totals row.
3. The User makes their changes.
4. The User presses the Append Button.
5. The data is added to the Data table.

• Very little vba used in this solution.

 

Dial 877-392-3539 for Excel Help

Power Query is amazing, and it should be used as much as efficiently possible.

Why should we place formulas in cells when we can use PQ and simply place the results?

One formula per cell is Old-School, DAF’s are New-School, as is Power Query.

Users Break Formulas all the time, if allowed, use Power Query, it Refreshes !

 

Leverage Power Query in all of your Excel 365 Solutions

 

 

 

ALERT: Many Excel Experts do not know Power Query

They should.  If you work in Microsoft Excel, at the professional level, for clients, then you MUST know Power Query.  You do not need to use it, but you need to master it, no worries, it is good for you 😉

 

 

 

---

 

Pivot Tables in Excel 365

Forget the hype, Pivot Tables are still used, will still be used, and are actually about to be set to AutoRefresh, a huge announcement.  So to be clear, Pivot Tables are not going away.

 

Pivot Tables are not going away; why would they

 

If you have not used Pivot Tables, you should, they are an amazingly powerful tool. C-Level Execs love them; as such they are a common component in most Excel Dashboards.

Excel Expert’s Note:  Yes, the PIVOTBY Function is nice, very nice, yet, it will not replace the Pivot Table, both will still be used, as they work differently.  Each has its place.  In fact, I use both in all of my Dashboard files, along with Power Query Pivot Reports.

 

Several Pivot Options:

1. Sheet based Pivot Table
2. PIVOTBY Function
3. Power Query Pivoted Report Table
4. Power Query based Pivot Table
5. Power Pivot

• Being creative is an important trait in an Excel developer. Which method(s) will you use, how will you use the tools available, as is, or with your twist?

 

This is Excel; Pivot Tables are Excel:   We have all seen these; these go way back to 1994.  One of our top 3 components in every custom Excel 365 solution we build.  I cannot imagine building a Dashboard, Reporting or Analysis solution without Pivot Tables.

 

 

Microsoft Excel 365 Pivot Tables are an EXPECTED Component in an Excel Dashboard

 

 

 

Excel 365 PivotBy Function

Much hype on this one.  It is amazing, look at what it outputs.  Formatting is not so much fun, but …… it is a great function, the FUTURE of functions.   (Released September 25th, 2025)

Headers, Sub-Totals, Grand-Totals, Sorting, Filters, all in one function, placed in a single cell, as it Spills.

 

 

Power Query Pivot Report w/ Slicers

Power Query can generate many of the same reports you would create onsheet, with a series of Excel functions.  But you can do it all in Power Query and skip the onsheet functions, skip the Pivot Table, just load the results.

 

 

 

The Pivot Table will soon Auto-Refresh Announcement.

 

 

 

 

---

 

Using Code in Excel 365 w/ Power Query

VBA made Excel what it is.   Up until 2018, VBA was not only necessary, it was King.  Then Microsoft rebuilt the calculation engine, and everything changed.

Excel Tables with Power Query and Dynamic Array Functions now make Excel what it is.

This is what Microsoft wants; this is the future of Excel, online, multi-user, automated, Excel based on Tables, Power Query and DAF’s.

Also, it is time to learn scripting, we must, like it or not as it is the future.

 

There are many programing languages available in MS Excel 365.  I only care about three.  I mostly care about M-Code as that is the New Frontier.  Are you ready?

1. VBA, aka Macros
2. Scripts
3. M-Code
4. etc.

 

Visual Basic for Applicaitons ( VBA, aka Macros ) in Excel

If you want to be a professional Excel developer, you must know VBA.  But you can use it a lot less than you would have say three years ago, due to the changes in Excel 365.

 

 

 

 

M-Code in Power Query

If you want to be an Excel expert, a top-level expert, then you will need to know M-Code.  M-Code really changes how you use Excel, and what you can do with Power Query.

M-Code is the New Frontier.

 

 

 

 

Office Scripts in Excel 365

Office Scripts are not ‘new’, but they are being revamped.  So like it or not, if you want to build modern solutions, with concurrent users, and code, then you will need to master Offie Scripts.

Resistance is futile.

 

Office Scripts in Excel – Office Scripts | Microsoft Learn

 

 

Why use code in Excel at all

Because code can easily do things other methods would take more effort, and code can do things other methods cannot.  That said, in Excel 365, less vba is needed, more M-Code and Office Scripts are needed.  In our solutions, we use all three.  But in our opinion, use as little code as possible, and if possible, use none.

 

We use as many tools and twists as needed, to provide the optimal solution.  We prefer to use zero code.  Why?  Since 2003 98% of our client’s issues with files were code related.  Code breaks.

 

 

---

 

So many changes in the new Excel, Excel 365

Here is the one we are watching, waiting for, this is rather significant, Auto-Refreshing Pivot Tables and Pivot Charts.   Wow, that is significant, really significant.  One less step, one less action, nice.

Then there was the new CheckBox (July 31, 2024) , the changes to Validation Control Lists ( Late 2024 ), the new PIVOTBY and GROUPBY Functions, all of these changes make Excel easier to program, and they also make Excel easier to use.

What is next, who knows, but we know it will be good.

 

 

---

 

Conclusion:

The Microsoft Excel Team has shown no signs of stopping, with these amazing additions and changes to Microsoft Excel 365.  What is next, I do not know, but the Excel MVPs know.  As such, when looking to learn what is the latest and greatest in Excel, look no further than LinkedIn.Com.

What do these changes mean to professional Excel Developers and Trainers, plenty, it means they need to keep learning.  It means keeping ones skills sharp.  It means learning how to use what is new, which is not always apparent.

Programming Excel is in a constant state of flux, custom Excel 365 solutions are now FLUID.  Everything changes, even Excel.

 

---

 

 

 

 

 

---

 

So you want to learn all about the Latest & Greatest in Excel 365?

 

The easiest way to learn what is new in Excel is simply to visit LinkedIn, see what the Excel MVPs are posting, and there you have it.  It is free, it is timely, and they have put a lot of time and effort into first learning the materials, and then presenting them as posts.  If you want to dig deeper, visit LinkedIn Learning, and see what is new on there.

 

Mentions, Excel 365 Experts Worth Following:

If you want to learn Power Query, the three gentlemen below are ones to watch.   Want to go to the highest levels in Power Query, look no further than below.

 

 

Excel Off the Grid

Amazing source for advanced Excel and Power Query education.  Tables DAF,s PQ, etc.

I have viewed many of Mark’s courses 2-3 times, as there is just so much detail, so much to learn, so well done.

 

 

 

Wyn Hopkins

See Wyn’s LinkedIn posts and content.

 

 

 

 

 

Chandeep Chhabra

If you want to learn M-Code, start here.  So well done.

And don’t forget the book.

 

Microsoft Excel MVP’s Teach Excel on LinkedIn & LinkedIn Learning

The Microsoft certified Excel MVP’s are the ones to watch, if you want to learn the latest and greatest in Microsoft Excel.  Videos on GROUPBY, PIVOTBY, the pending Auto-Refresh Pivot Tables, etc.  You can learn all about the new features before they are released in the non-beta versions of Excel 365.

 

Excel Mentor’s Advice:  Don’t forget about LinkedIn Learning.  There are amazing Excel MVPs on there, like Oz the Excel Trainer.  I am on there all the time; Learn, Learn, Learn as Excel 365 is in a constant state of improvement.

 

 

 

---

 

Here are a few of our Favorite Client Reviews.

 

Most Recent Testimonial:

Tom Nevitt:

I have had the absolute pleasure of working with Christopher from Excel and Access LLC on our new membership database.

The solution is incredible – far more advanced than I had ever thought possible for Excel. Not only is the team at Resolve saving lots of time, but we are picking up on leads we would have missed and seeing a tangible impact on our bottom line.

The entire service from Christopher has been flawless. He is very patient, he knows how to explain things clearly in the right level of detail depending upon the user and is the ultimate Excel guru.

We really could not be happier and are so grateful for all of Christopher’s time and efforts that have gone into this solution – particularly with our last-minute changes!

HIGHLY RECOMMEND

August 2025.

 

Excel MVP Testimonial:

LinkedIn Testimonial:

 

 

Microsoft Testimonial:

 

 

Google Testimonials:

 

 

 

---

 

 

Evaluating Power Query Usage in Microsoft Excel

Is Power Query a viable component in a fully automated Microsoft Excel 365 Solution for Business?