Automated Excel Solutions for Business

~ Dynamic Excel Development; Optimized Excel Solutions for Business ~

Excel Should be Easy to Use; the update process should not require effort from the user.  This is the hallmark of a proper Excel solution.  At Excel and Access LLC, we build fully automated Excel solutions for Business, Government, and Education.  (We also offer Training and Mentoring services).   877-392-3539

Fully Automated Update Process:  The Excel file should be programmed such, that it knows what period you are in, it knows the location of the updated data source.  The file has all of the code, functions, and methods, to allow Excel to fully update, without user interaction.

Well Thought-Out Design:  It takes effort to make such a solution, as well as a lot of thought about design.  It is so advanced that it is easy to use. So easy, that unless you need to manually type something, the user is not needed; the user is the optional component.

Benefits of Automation:   The benefit is that the file will update itself, and no user is needed.  There is no need to copy and paste, instead use the extra time to conduct analysis.

Image of Power Query Table being used to automate the data process.


Power Query is one of the most powerful automation tools in Microsoft Excel.  Add some VBA and anything is possible.

 

~ No User Needed:  The User is an Optional Component in a Fully Automated Excel Solution ~

 

Why do you need to have the user update the file?

  • If there is no need to have a user sit and the computer, to manually enter data, then the user is not needed.
  • Other than manually typing data into the file, what does the user need to do?
  • Can’t the computer do that for the user?  If you think it through, you can 100% automate the update process.
  • Let the computer do all of the work for you.
  • Spend the extra time conducting analysis.

 

Image of Excel expert.

The user is the optional component of a well-thought-out Excel solution.   Other than manually typing data or making selections from Drow-Lists, there should be nothing for the Excel user to do.

The file should update itself, fully, in the automated update process.

 

Think about this:

Question?  If you do not have the need to manually type data into the spreadsheet, then why would you need to have the user involved in the update process? 

Answer:   You don’t.  We have automated hundreds of solutions in Excel for business.

Do you have a Smart Computer?  Your computer knows the date and time.  With that, Excel can easily update the data in the file, the reports, the analysis, and the distribution of the file.

Do you have a Smart Programmer?  Excel has changed, does your programmer know how to apply the latest and greatest to Excel?  Do they program dynamic Excel?

You will need to use several tools for full automation:  The combination of the new Dynamic Array Functions, Macros, Excel Tables, Power Query, and well-thought-out design, greatly simply Excel programming, as well as simplifying the user interface and the user experience.    It is just Excel, make it easy and joyful to use; think about the user before you build the solution.

How will they use the file:  This is the most important question of the project.

No User Needed:  Can I eliminate the user 100% from the update process?  That drives everything.  If not 100%, then as much as possible.

Automation begins with the thought of the user experience:  How will the user actually use the file?  What can I eliminate?  Is it intuitive?

 

Full Automation:  The user does not need to manually import and then scrub (transform) the data;  Power Query does that automatically

 

Below is an example of an automated Periods Table, a table that is referenced by the functions, reports, and VBA when the file conducts the update process.  This Table aids Excel automation.

Automated Excel Solutions for Business Post: Image of Excel Table going into Power Query, then Power Query Table.


We made it easy for the user to add a period to the Periods Table, and up into Power Query. Power Query provides the details we need in a fully automated system.

 

Note on Power Query:  When it comes to slicing and dicing of data, Power Query is easier and does more, than traditional Excel functions.   You may not be able to do 100% of your calculations in Power Query, but you should be able to do a good amount, say 70% or more.  Power Query is fully automatable.

 

 


 

Microsoft Excel 365 Automation Tools

Most Powerful Tool for Automation:  The number one automation tool is VBA (Macros). Period; no argument.  It is the easiest way to automate an Excel workbook.  Hence it is often overused.

When it comes to adding macros to the file, a good programmer will minimize their use, in favor of using dynamic functions and Power Query.   A good programmer can do a lot with Excel Tables, Power Query and VBA.

When should you use code?  If you want to append Power Query data to an Excel Table, write a few lines of code. ( See example below )

Example of vba code being used:   In the image below vba was used to move data from the green Power Query Table to the blue Excel Table.  The code also clears the filters on the Slicers as well as protecting the tab.

 

Excel Experts Note:  Not all Automated Excel files require vast amounts of VBA, some require no VBA at all.

 

Full Automation:  The user does not need to Copy and Paste; That is what macros are for

 

Image of Power Query and an Excel Table using macros to move the data.


Automated Excel Solutions for Business

~ Dynamic Excel Development; Optimized Excel Solutions for Business ~

 

Ease of Use via Workbook Automation

The more you can automate the file, the less the user has to do.  Fewer chances of error.  The user will save a lot of time.  They can then focus on analysis.

  1. How easy can I make the file to use?
  2. How can I protect the file from the user?
  3. How can I make the file so that it is easy to update?
  4. How can I make the file to that it is easy for the client to expand on it, for needs currently unknown?
  5. How can I make it so that the user does nothing other than press “Update File”?

 

Top 5 Tools for Automated Excel Files:

  1. VBA – Macros, Code
  2. PQ – Power Query
  3. DAF – Dynamic Array Functions, Excel 365
  4. Pivots – Tables, Charts, w/ Slicers
  5. Tables – Excel Tables, Power Query Tables

 

VBA ( Visual Basic for Applications, aka Macros )

Image of Excel based VBA/MAcros


VBA is the most powerful tool in Microsoft Excel, followed by Power Query.

 

 


 

Not all Excel Solutions can be Fully Automated.

~ Most Excel Solutions can be at least partially Automated and Much Easier to Use ~

If they must type data into the file:  For those projects, say where you want to have the user manually entering in sales transactions.  If you cannot automate that, you can still automate the rest of the workbook.  And more importantly, you can control how the user enters the manual data.

Make it Easy:  For example, instead of typing the City and State they are from, provide them with a drop-down list of choices.  Use formulas to filling as much as possible, use vba if you have to.  But do as much as you can for the user, and to protect the file from the user.

In the image below, the user manually types some data, and they select items from lists.  We have made the process as simple to use as possible.  This is an order entry system, using Excel Tables.

Making the file easy to use is your top priority.

 


Excel Automation happens in many different places. Once place is with Tables and Macros, such as the image above. Her we automated the Append process for the user.

 

Partial Automation in Excel is Common

Partial Automation:  If you cannot fully automate an Excel solution, automate as much of it as you can, and make sure to have an intuitive user interface.  You can still make the file as easy to use as possible, and the user will not mind typing the data or making selections from drop-down lists.  The user will appreciate it and they will save a good amount of time.

Example:  Above, the user manually adds records to two Excel Tables.  Some sections are dropdowns some are typed, and some are calculations.  With this approach the user can easily add data to an Excel file.

 

 


 

 

Excel and Access LLC: Automated Excel Solutions for Business

877-392-3539

Free Consultations

 

 <br /> Excel and Access, LLC: Automated Excel Solutions for Business

 

 


 

 

Excel Automation is Based on Advanced Design

If you want to completely automate an Excel file, you will most likely program it differently than if you were going to manually do the update process.  It takes a lot of thought to design to build an automated Excel Template.  Why does it take more thought to design, because you want to minimize moving parts.

Advanced Design:  Much attention goes into one thing, how will the user use the file.  That drives everything.

  1. What does the user need to do to update the file?
  2. How do I automate that, through design?
  3. How do I account for what the user might accidentally do to the file?
  4. Is this a file I would want to use?

 

Built as if for my Personal Use

I build every file as if I were going to be the one using it.  I am lazy, I do not want to do anything, I make Excel do it.  This drives me, the user experience.

As such, the file should be easy to use.  If I have an important Excel data Table (See image below), I will often add the following components to the sheet:

  • Slicers
  • Aggregate Totals up Top.
  • Sub-Totals in the Total Row.
  • DAF Summary Report
  • Mini Tables-Based Dashboard
    • Pivot Table
    • Pivot Chart
    • Conditional Formatting

 

 

Tables, always use Excel Tables for Automation

The majority of Excel files should be using Excel Tables.  Doing so allows you to use Power Query, and to leverage the power of the dynamic array functions with ease.  Dynamic Excel begins with the data, and that data goes into Tables.

 

Automated Excel Solutions for Business Post: Image of Excel Table with Slicers, a Pivot Table, and a Pivot Chart, with Conditional Formatting.


Always use Excel Tables to house your data. Add Slicers, Pivots and DAFs for a quick mini dashboard on that Table.

 

 

Dynamic Array Functions (DAF), Leverage the Spill Cell #

In the example below, when the data changes, the report will automatically update, there is nothing for the user to do. 

Excel programming has dramatically changed.  The days of Index/Match are behind us.  GroupBy, PivotBy, those are in front of us.  One cell, one complex functions, Spills a Range.

The Blue cells have the functions. The other cells are empty.  They look like there is a formula in the cell, but the cell is in fact empty.  Put something there and see what happens.

 

Automated Excel Solutions for Business Post: Image of Dynamic Array Functions Reporting.


Interactive, automated dynamic array function reports. Nothing for the user to do.

 

 

 


 

The user should not do these manually

  1. Copy and Paste
  2. Update Formulas
    1. Copy down formulas
  3. Change Report Headers
  4. Insert New Columns
  5. Create New Tabs
  6. Update Links
  7. Highlight Cells meeting specific criteria
  8. Manually Export Data
  9. Manually Import Data
  10. Filter Data
  11. Change a Pivot Table
  12. Print a Report
  13. Distribute the File
    1. Backup file, or Email, Upload, to Network, to Web.

 

 

Things you should Automate

As I have been saying, you can pretty much automate anything, except the user manually typing data into a file.  You cannot automate that, but you can automate everything else.

When you design a file, you design it with automation of the update process in mind.  It guides you on what to build, and how to build it, to keep the user out of the process.

Automate as many of the following as you can:

  1. Importing/Exporting of Data from a variety of data sources and applications
  2. Pre-dynamic Excel
    1. Code to Copy Down Formulas, as needed.
    2. With Excel 365, the functions are dynamic, and they grow and shrink as needed.
  3. Based on the current date, and the details in the Periods Table, your file can:
    1. Update Column Headers.
    2. Update Report Headers
    3. Determine which data to include in the update.
    4. Determine what data goes in which column
      1. Example: Pull Actuals are Forecast from the data Table, based on the current date.
  4. You can use Power Query to pull data from external data sources, then have Power Query Transform your data.
  5. You can use VBA and Power Query to populate Excel Tables.
  6. You can have your dashboard automatically printed and or distributed.
  7. Basically everything will be Table, Power Query, DAF and VBA based.  No need for the user.  The computer knows what it is doing, you just need to program it right.

 

Partial Automation:  Even if the user needs to type some data, or to be able to make some selection, you can automate the rest of the file and the process.  Yes, there are degrees of automation, based on how the file will be used.

 

 

 


 

 

How to Update a Fully Automated Excel Workbook

~ So easy a caveman can do it ~

 

  1. Open the file.
  2. Press the “Refresh” file button.
  3. Look at the output, once the process completes.
  4. Close the file and get on with your day.

 


To Update the file, RefreshAll. Done.

 

 

How to Program an Automated Excel Workbook

How you write functions in Excel has changed, it did when the calculation engine changed. Due to this change, Excel functions are now range-based, i.e. arrays. Hence, the need to learn new methods of custom Excel development. Excel just does not work the way it used to.

If you follow these steps, you can program an easy to use, 100% automated Excel workbook.

Excel Development Step by Step Guide:

    1. Excel Data RangesCommon, not recommended
      1. Convert ranges to Excel Tables
    2. Excel Tables
      1. With Slicers
    3. Power Query
      1. With Slicers
    4. Pivots
      1. Pivot Tables
      2. Pivot Charts
      3. With Slicers
      4. Power Pivot
    5. Dynamic Array Reports / Functions
      1. Leverage the # in your Excel functions.
      2. Build Interactive reports and analysis.
      3. Build Excel Dashboards.
  • Add VBA as needed.

 

 

 


 

~ The Excel User Interface is Very Important ~

Look and Feel:  The look and feel of the Excel file is important to the user.  The cleaner you make it, the more automated you make it, the easier it is on the user.  All of our solutions begin and end with our focus being on the user experience.

The user experience is heavily influenced by the methods of automation.  That is why I am discussing both here.  An Excel Table is a great way to have a user enter a path to a file.  It is also a great way to feed variables into Power Query. This method allows any Excel user to specify to Power Query what data should be used, no programming experience needed.

All of our Automated Excel Solutions begin and end with our focus being on the user experience

  • If they do not need to see it, do not put in on the sheet.
  • If they need to see it, make it stick out.
  • Make the file intuitive to use.
  • Make the file easy to look at.
    • No gridlines people.

Result:  The users are able to get more work done, in less time, with less effort, and fewer mistakes.

Protection:  The file should be protected.

Automation:  The file should be 100% automated, if possible.   If you cannot fully automate it, take it as far as you can, and that will still provide benefit.

 

Use utility macros to allow the user easy ways to manipulate the file update process, as needed.  Point and click simple.


The User Experience is Important. It is part of Excel automation. Make the user experience intuitive.

 


 

Users can easily add records to an Excel Table

The information in the mini tables below will be used by macros, formulas, reports and drop-down lists. The user can easily add a record, delete a row or make a quick change.  An intuitive and pleasing design is important.

Image of Lists tab where we create lists to feed the Validation Controls, a good way to control the user in a user-friendly environment.


Do not enter data in ranges, enter data in Tables.

 

 


 

Examples of Excel User Interface used in Excel Automation Projects

Fully Automated Excel Solutions for Business

 

Automated Excel Solutions for Business Post: Image of Excel Automation using Power Query.


The User Experience is very important. Here we use Excel Tables to house the Paths to the Data Source FIles. Macros make it easy to use.

 

 

Automated Excel Solutions for Business Post: Image of Report based on the new Dynamic Array Functions. Added Slicers and a Pivot Table and a Pivot Chart.


Dynamic Array Functions allow for interaction between the user, and the data. Automation at its finest.  Point, Click, Done.

 

 

User Settings – Automated Excel Solutions for Business

User Settings allows the user to interact with the Excel workbook.  There are many options as to what you will put on such a sheet.  Goal, make using the Excel workbook as easy as possible.

Automated Excel Solutions for Business Post: Image of another User Settings Tab.


Track who updated the file, when they updated it and where it is saved.

 

 

If the user must manually enter data into an Automated workbook, make to have them to so in Excel Tables.  Control what is entered, how it is entered, and make sure to protect the Tables.

Automated Excel Solutions for Business Post: Image of Two Excel Tables being automated order entry system.


We built this “Access Replacement” Solution in Excel, instead of a more costly Access database.

 

 

Add Interactive Excel Tables – Automated Excel Solutions for Business

Add Slicers, sub-totals, Conditional Formatting and macros to your Excel Tables, to make them interactive, in addition to being 100% automated.  Build flexibility into your solutions.  Predict what the user may do.

Automated Excel Solutions for Business Post: Image of Two Excel Table being automated Sales Order.


Excel Tables make great data entry forms.

 

 

Automate your Reporting – Automated Excel Solutions for Business

When a new order is entered it automatically takes you to the receipt page, it populates and prints the report, no user interaction needed.  But, if you want to pull up a historical receipt, you can, easily, just enter the Invoice #.

What could be easier on the user?  Everything on the page is mean to interact with the user.  If we can predict it, we can automate it.

Automated Excel Solutions for Business Post: Image of dynamic array functions report being automated.


Perfect example of the separation between data and the report. You do not put data in the report.

 

 

 

Simplify Excel Reporting – Fully Automated Excel Solutions for Business

Select the Association from the Drop-Down list, and the report updates.   It is easy and intuitive for the user.  Simplicity.  100% automated Excel Solutions for Business

 

 

 

Conclusion: Automated Excel Solutions for Business

The majority of things that people do in Excel, could be done by the computer.  Other than manually typing information, or manually selecting items from a list, what else does the user need to do?

 

Contact Us 877-392-3539