Microsoft Excel Should Be Easy to Use

Based on my 37 years of programming in Microsoft Excel, I can personally tell you that there is absolutely no reason for Excel to frustrate you, or for Excel to be difficult to use.  Simply put, Microsoft Excel should be easy to use.

 

If Excel is not easy to use, you are programming it wrong.

When an Excel application is properly built by an expert Excel consultant, the file is not only super easy to use, but it can also be quite enjoyable to work in. Ease of use comes down to the design of the workbook.  Design it right, and it is easy to use. Design it wrong and your staff wastes hours or even days manually updating the workbook, which is completely unnecessary, and often very frustrating.  No argument can be made to the contrary, Microsoft Excel should be easy to use.

 

In the easiest-to-use Excel applications, you open the file, click a button, and Excel vba coding does the rest, as you sit and watch, what could be simpler.

 

All of the “ease-of-use” features listed in this post were created by Microsoft for one purpose, to make using Excel easy for the user.  Question: If Microsoft built Excel to be so easy to use, then why are so many Excel workbooks time-consuming and often frustrating to use?  Answer:  Because they were not designed around these user-friendly features; it just wasn’t built right.

First Reason Excel is not easy to use:  Most often the cause of Excel being difficult to use is due to the fact that the Excel consultant you hired to do the programming work was not an expert Microsoft Excel programmer. They may be a “good” programmer, they may be “intermediate“, but they were not an “Excel expert“.

There is a big difference between being an intermediate programmer and an expert programmer.  The intermediate programmers do not know all of the best practices, advanced features or advanced methods.  Hence the file is not built as advanced as it could be.  So, it is harder for the user to use the file, taking more human effor tthan  necessary.  If you want it built right, hire an expert, either internally or externally.

If you plan to hire an expert Excel programmer, be sure to hire one that programs in Excel, at the expert level, day in, day out, year after year, decade after decade. One that knows how to apply Excel’s best practices that makes Microsoft Excel easy to use.

Second Reason Excel is not easy to use:  Often the second reason your file is not easy to use is based on one simple fact, VBA/Macros are not used in your Excel file, but they should be.  Why should you use macros?  Simple, Excel macros are the most powerful way to make Excel easy to use, even a joy to use, some say a breeze to use.

VBA allows you to automate and to integrate your application, largely taking the user out of the equation with a point-n-click user interface. It should be used if you want your Microsoft Excel file to be easy to use.

 

You don’t push your car to work, you use the engine to do that, VBA is Excel’s engine.

 

 

 

 

---

 

 

Excel Just Got Easier to Program.

 

Editor’s Note: Excel just got easier to use!  Yes, you heard me, the Microsoft Excel Team have taken Microsoft Excel to the next level with their new Dynamic Array Spill Functions.  We STRONGLY suggest that you take the time to learn these, there are about 15-20 ne functions you should learn.  In so doing, you can do more advanced work, with fewer formulas, and no vba/macros.  The release of these functions has made the team at Excel and Access, LLC reconsider how we do Excel programming.

 

Excel programming just got a whole lot easier.

 

Just as the powerful XLookup Dynamic Array Function retired the Index/Match combination, as well as the need to use the VLookup and HLookup functions, these new functions make programming in Excel so much easier.  They greatly reduce the number of formulas you must write, as you write one function in one cell.  They greatly reduce the need for VBA, and they allow you to work with Dynamic Datasets.

In our opinion, Excel is becoming more Access like.  These new Spill Array Functions are very similar to Access Select Queries.  Queries allow you to select data, to filter data, to arrange data, to sort data, to merge data-sets, to perform calculations, to summarize data, etc.  The new Excel VStack Array Function does what the Access Union Query does.  Time to shift our thinking to writing functions in Excel that leverage the power of these new functions.

 

• ChooseCols Function
• ChooseRows Function
• Take Function
• Unique Function
• Filter Function
• Sort Function
• SortBy Function
• Drop Function
• VStack Function
• HStack Function
• XLookup

 

 

---

 

 

 

Microsoft Excel Programmer versus Microsoft Excel User

There is a difference between an “Excel Programmer” and an “Excel User”, for the purpose of this post. For this post we will assume an Excel user is someone that can work in Excel, knows some formulas (Sum, Count, If), can create basic charts and use Filters, but someone that does not do any of the Excel programming to change the structure of the file.

An Excel programmer would be a person that can write the formulas (XLookup, SumIfs, XMatch, etc.), change the macros, create UserForms, add rows and columns, make structural changes to the file, builds new files, etc.

 

Editor’s Note:  The solutions we are talking about, such as our 100% Custom Microsoft Excel Tracker Templates, are user friendly files, and that means that the files are used “as is“.  That means from period to period, there is no need to revise the file for use. The file is always good to go.  You add and revise data, sure, but you do not change the structure of the workbook.

True, an internal Excel programmer can possibly change the file as needed, but there is no need to do so.  The file should be used ‘as is’.  That is a user-friendly workbook.

This post is written with the Excel user in mind.  Though Excel programmers can learn a lot as well.  This post is about Microsoft Excel being easy to use, in an automated sense.  It is about applying best practices to design.  How can you make Excel easy for a user to do things, without doing them spending time or effort doing them manually, that is the topic of this post.

The reason I am differentiating between the two is because I am stating that in a fully automated and integrated Excel application, the user does not need to know how to program Excel in order to use the application.  As it is a fully locked-down, automated, solution where they point, click, and type, that is it.  With as little typing as possible. If you can use a mouse and keyboard, you can use the application I am describing.

 

Editor’s Advice:  If you want to make an Excel workbook super easy to use, just apply as many of the 15 user friendly features mentioned in this post as possible.  Do what works in your workbooks and make working in Microsoft Excel a true joy for your staff.  Microsoft Excel really should be easy to use.

 

 

 

---

 

 

Ultimate Example of Ease of Use – No User Needed

In the custom Excel application, we built below, the user literally does nothing other than press the “Update File” button.  As you can see, nothing could be easier to use.

The easiest Excel applications to use are point-n-click, run, and done solutions.

 

In this 100% automated file, the date in the ADP download drives everything this file does.  It does so via vba, no user interaction needed, nor allowed.

The code will import the ADP data file into the workbook, it will then manipulate that data and the file, it will populate and print the reports, and it will close the file for the user.

The user does not do a single thing to produce the output of the file, nor do they do a single thing to update the file.  The user really is not needed.

 

Now that is about as easy as it gets to use Microsoft Excel. Type nothing, do nothing, let Excel do everything, simply based on the current date, which too is a formula.

The real power of Excel VBA/Macros when combined with best practices and advanced design allows you to eliminate the user from the update process entirely. Excel coding is a game changer and should be used to make a file more user-friendly if not 100% automated and integrated.

Focus:  Taking the user out of the process, as much as possible.

Result:  A Smarter Microsoft Solution.  Point, Click, Done.

 

 

 

---

 

 

Our Company’s Excel Consultants Make Working in Microsoft Excel as Easy as the Click of a Mouse

Our Team’s approach to programming an Excel file for our clients is simple, we build it as if we were going to be the one to use it.  We add all of the useful bells and whistles, always applying best practices, always automating the file to 100%.  We literally want to work in a push button solution where other than the need to manually type some data, the file does everything for us.  Call us lazy, call us smart.

 

No User Needed: Our Excel programmers can build a file that does not require a user to run it

 

It is our job as the expert Excel programmer to predict what you might try to do in the file, and to account for that in advance through design.  Think of Excel programming being much like a game of Chess.

 

We want you the user to do as little manual work in the file as possible, restricted to manually typing data.  Our approach is in ultimately trying to remove the user from the equation via vba driven automation. We have built hundreds of applications where the user clicks a single button in the file (See image below), the vba does the rest, as the user sits and watches, or goes does something else.  Saving the user hours of time each time they use the workbook. The workbook usually runs in seconds, for massive files, in minutes.

 

What could be simpler to use?  So simple you might lose your job?

We once built a file for a client that literally cost one of their staff their job, as we automated them out of work.  That is not a happy situation, but it shows, Excel when built right can do the work without you. So why are you wasting hours or days manually updating and unnecessarily revising your files?

Read this post, apply what is here, and you too can have Excel files that are not only easy to use, but that are also a joy to use.

 

 

 

Excel when built right can do the work without you; the user is the optional component in a 100% automated file

 

 

---

 

 

Apply Excel’s 15 Best Design Practices to Maximize Ease-of-Use

I say maximize ease-of-use, because unless you fully automate the workbook, the user will still be needed, and it will not be ‘easy to use’ as that implies a point-n-click experience. The more user-friendly design features you can incorporate into your Excel files, the easier they will be to use.

If you apply Excel’s best design practices to your programming efforts, with ease-of-use central in mind, you will be amazed at just how little time you spend updating an Excel template from period to period. You will find you have more time to do other tasks, now that you are not constantly updating the files that you work in. You can spend the time doing analysis and decision making, or even work less.

By using 15 Excel’s best practices, expert Excel programmers are able to fully leverage the true powerful of Microsoft Excel and what it has to offer the user and in particular, the user experience. This makes it easy to use Excel workbooks.  We call this a Smarter Microsoft Solution.

Our Smartest Microsoft Excel Solutions run themselves, literally.  Microsoft Excel-based VBA/Macros makes this possible.  If built to the extreme, the user need do nothing other than open the Excel file, then watch Excel do the rest, programmatically. Point, Click, Done.   Excel should be this easy to use, and it can be.

 

Excel’s 15 primary ease-of-use features:

1. 1. 1. VBA/Macros/Code
         1. Expert Excel VBA programming will do more for ease-of-use in your workbook than anything else. Period.
            1. This is what they call automation, it allows you to click a button, then watch Excel do the work for you.
      2. Data Entry Forms (Excel UserForms, Tab based Forms)
         1. Control the user experience, control data entry, simplify the effort, and automate the entire process
      3. Programmatically add data to the workbook with the click of a button
         1. Don’t type your data, programmatically import the data using VBA
            1. Data Sources:  Excel, Access, SQL Server, Azure, QuickBooks, ADP, Web, csv, txt, etc.
      4. Protection
         1. By applying protection to cells, and tabs, you can create a data entry tool that is easy to use, one that is controlled
            1. Provide a more secure and less stressful environment for the user
      5. Pivot Tables, Pivot Charts, with Slicers.
         1. Enhance your analysis and reporting with drill-down capabilities that are so easy to use.
            1. Senior Managment loves Pivot based KPI Dashboards
               1. A Data Visualization Experts Tool
      6. Validation Controls (Dropdown Lists)
         1. A powerful data entry tool that users love
            1. Base these on Dynamic Named Ranges for further ease of use.
      7. Filters
         1. An easy way to isolate what records you will see with the click of the mouse
      8. Groupings
         1. Quickly hide/unhide rows and columns to maximize screen space, for a user-friendly environment
      9. Conditional Formatting
         1. Let your eyes do the work, give your brain a rest
            1. Excel will change the appearance of certain cells based on the values of the data in them, very powerful
      10. Cell Comments
          1. Quickly tells the user what they need to know
      11. Custom Message Boxes
          1. Interact with the user via Message Boxes and Custom Dialog Boxes
             1. Collect their response, and use that elsewhere in the file, and in your macros
      12. AutoFill
          1. Allows you to do things in Excel without knowing formulas
      13.  Format Painer (Paint Brush)
          1. Quickly make one cell or range look like another, point-n-click
      14. Navigation Links
          1. Help the user to find what they are looking for
      15. Integrate Excel with the other Microsoft Applications, into one seamless solution
          1. Using VBA you can have Excel control Access, Word, Outlook, and PowerPoint.
             1. Great for Financial Modeling between Microsoft Applications.

 

Excel Experts Advice:   Based on the 15 user friendly features listed above, you can make Excel an easy-to-use application. Apply as many of the 15 as you can, with the focus on being, “how can I reduce the effort the user is making to use this workbook”.  Use VBA/Macros to automate as much of the work as you can.  Control what the user can and cannot do, and how they do it.  Require as little as possible from the user to create the optimal user-friendly workbook.

 

 

---

 

“Easy to use” and “easier to use” are not the same thing.

There is a difference between making a file “easy to use” and making a file “easier to use“.  “Easy to use” refers to a fully automated Excel solution. “Easier to use” possibly has some automation built in, but the user still does much of the update process, manually.

They are not the same thing; “easier to use” still requires manual efforts by the user. Easier to use still requires the person to be sitting at the computer, spending days or hours updating the file. That is not easy to use.  Point-n-click, let Excel do the work, that is what we call ‘easy to use’.

This post covers making a file so easy to use, that if you take it to the extreme, the user is not needed.

Applying some of the 15 user-friendly features in this post will make your files easier to use, but not ‘easy to use‘.  But easier to use still sounds pretty good, right?

Applying any of these 15 best practices in workbook design can decrease the frustration your staff feel when working in manual Excel workbooks.  Even doing it in phases over time has a huge benefit. Sometimes easier is good enough.

 

 

 

---

 

This is How Easy an Excel Application Should Be to Use

In the image of the Excel Data Entry UserForm below, the user has two ways to enter manually add data, they can type data using the keyboard into the fields on the form, or they can use the Drop-Down lists on the UserForm to select an item. This is how manual data entry is done on a UserForm.  This allows the user to quickly and efficiently enter their data into the Excel file. They enter the data in a controlled environment.

An Excel UserForm can also have a button to programmatically import the desired data if external data is available, say in a csv, txt, sql, qb, adp, etc, format. This eliminates the need for the user to spend hours or even days typing data into a file,.  Whenever possible, bring the data into the file programmatically, via vba coding.  A HUGE time saver. Ubber friendly.

The UserForm also allows the user to take other actions in the file by clicking the buttons on the form, such as the ‘Backup File’ or ‘Print Receipt’ buttons. The buttons on the form allow the user to do things automatically, via code, so they do not manually need to do them.  Point-n-click, done.

By using an Excel UserForm, you can completely control what the user can do, what data they can enter, along as what happens to the file.  Microsoft Excel does not get any easier to use than this.

This UserForm is an example of a hybrid application, where there is the combination of typed and imported data.  In this example we use code to bring in as much of the data we can.   Their other data needs to me be manually entered live into the system, so we use this UserForm for ease-of-use.

You do not need to know how to program in Excel to use this file, you do not need to know how to write Index/Match. This is point-n-click graphical user interface designed to make Excel super easy to use.

Have you seen an Excel file like this at work? Doesn’t this look like it would be a joy to use?  Like working in a Windows application.  Work smarter, not harder, work in a Smarter Microsoft Solution.

 

You do not need to know a single Excel formula to use our user-friendly Excel workbooks.

 

 

Excel Expert’s Note: Instead of manually typing your data into cells, programmatically import the data, and let Excel do the heavy lifting.  But if you have to type your data, do so in an Excel UserForm, as those are the second most powerful ease-of-use features in Microsoft Excel.

Take Away: The simple combination of Excel Macros and Excel UserForms will transform how you use your Excel workbooks. Add any of the 15 user-friendly features discussed in this post and it will make working in Excel a joy.

 

 

 

 

---

 

 

How can your organization get an Excel file that is easy to use?

If you want an Excel file that is easy to use, you basically have two options.  Which you choose will depend on your Excel programming skills.

Option #1:  You can learn the best practices of Excel programming, applying proper design techniques, and do the work inhouse, if your Excel programming skills are advanced, and if you know vba coding.

Option #2:  You can hire a consultancy, such as Excel and Access LLC, to custom build your Excel templates for you. 100% to your exact needs, built to be as user-friendly as possible, based on your needs.

If your current Excel file is not user friendly, nor easy to use, you do not necessarily need to build a new file from scratch.  Another option is to retrofit your current Excel workbook, making changes to the file that will increase the ease of use.  You can leave the rest of the file as it currently is.  This also helps to keep the costs down, if that is a concern.

You can do the retrofit work in phases, versus all at once, first going after the low hanging fruit, such as writing a few macros, and make other changes over time.  Eventually achieving a fully integrated and automated Excel workbook that is easy to use.

 

We are here to help.  Contact us and we can either build the file for you, or we can train your staff in advanced Excel programming, and they can build the file internally.  Same end result, a super easy to use Microsoft Excel file.

 

 

 

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

 

 

 

---

 

 

Example: Easy-to-use Automated and Integrated Microsoft Excel & Access Solution:

You can build an easy-to-use solution that uses multiple Microsoft applications (Excel, Access, SQL Server, Office 365, Azure) in one seamless solution by leveraging the power of VBA.  You can build it so that it is 100% integrated and automated, one that does not require a single thing from the user, one that will run itself.

Really, it will run 100% by itself, even if you are not in the office.  In a 100% automated solution, the user is not needed.  How can it get easier than that?

 

 

Solution:  I personally built an Excel and Access application for Tenet Healthcare Finance that used AS400 data to populate a Microsoft Access database. The manipulated Access data was then imported into a Microsoft Excel workbook.  The Excel file would further manipulate the data, populating reports, analysis and charts.  The file would then be distributed on the company network as well as via company email.

The VBA coding in the Excel file did all of the work, it even ran the Microsoft Access queries. There was literally no human involvement.

Results:  The application that originally took the user 2-3 hours per day to manually run now ran while I was at home in bed.  The application I built ran itself, 6 hours prior to my getting to the office, great for those working on the east coast.  Once in the office I reviewed the reports, which were always 100% accurate, and I went on with my day.

 

I do not know how using Microsoft Excel can get any easier than this.

 

Easy to Use:  The integrated Excel and Access application has taken the concept of “easy to use” to the extreme, showing that Microsoft Excel can be so easy to use that you do not even need to be at work to run it.

 

 

 

---

 

Example:  “Building it Right“, with Advanced Design for Ease-of-use.

While working for Resources Global Professionals as an Excel, Access, and SQL Server programmer I was tasked with automating a Budget/Forecast/Actuals/Variances workbook for an international retail business based out of California.  The way the file was originally built, the user would spend several days each month, changing formulas, expanding ranges, manually importing data, and making other manual changes to the workbook in order to account for the new accounting period.

 

Unnecessary:  A lot of unnecessary work. If they had just built it to best practices, none of this manual and time-consuming work would be needed.

 

This is how many budget files are built unfortunately. Often the person tasked with using the file will also be tasked with revising it and updating it to account for the new period.  This is fine if you are an experienced Excel programmer, but it can be frustrating if you are not well versed in advanced Excel functions or vba programming.

It is also completely unnecessary, definitely not user friendly, and the entire process can easily be eliminated.  It just takes smarter programming.

I changed all of that, making the file ultra-easy to use. By using VBA to programmatically import the new periods data, I was able to cut several days effort off of the work.  I also eliminated the need to change any of the formulas in the file to account for the new period.  The file worked because we designed it right, and we were able to use advanced formulas that new which table to look in, based on the date. Applying these 15 user friendly features can make a huge difference to the user experience.

 

Excel Expert’s Opinion:  You should never have to change a formula in an Excel file, unless you are revising the design and layout of the file.  Instead, write more advanced formulas.

 

Build it right and you either largely or completely take the user out of the update process, thus making the Excel file easy to use.

 

 

---

 

 

 

 

 

---

 

 

Examples:  How using Microsoft Excel VBA with a Vertical Data Table Makes an Excel file Easy to Use

 

Challenge #1:  Sometimes you do a lot more typing than you need to, simply because the workbook was initially designed for manual data entry, when in fact a soft copy (cvs, txt, xls, qb, adp, etc.) of the data is available for import.

Instead of manually typing your data you could click a button and vba coding would programmatically import the data for you. The code would manipulate the data and the file for you, taking you ot of the equation.  Which is easier, and which is more accurate, and which takes seconds, versus hours or days?

 

Challenge #2:  Sometimes you find yourself doing more work changing the design of your workbook than you actually need to do, because you store your data across multiple tabs, instead of placing the data in a single data table. So many people do this, but it is wrong. It creates a lot of unnecessary work.

Thus, each time you want to add data to the file, you find yourself adding tabs, changing formulas, expanding ranges, copying and pasting contents from one range to another, etc.

All of this effort is 100% unnecessary.

 

They Know Not What They Do:  In the two examples below, both situations were using incorrect design, and both lacked Excel vba automation to simplify use. Both examples are very common to what we see clients doing. The majority of clients we see store their data across numerous tabs, instead of placing the data in one vertical data table.

They just do not know better.  They do not know that Microsoft Excel should be easy to use. They just don’t know what they don’t know. Hence this post.

 

 

First Example:  In the first image below, we had a client that had five tabs in their Excel workbook where they would manually input data, one tab per week. (Very common approach).  They had identical tabs for each of the five weeks in the month.  Each week they would spend several hours manually typing their data into the workbook, changing dates, changing headers, printing the file, faxing the report, etc.  There was no need for any of this manual activity.

Not Necessary:  There was no need for five tabs, one tab would have done the trick, if designed with best practices in mind. Having data spread across multiple tabs actually makes the workbook less friendly and harder to use and to update. This approach requires you to roll-up the details, why do that?   Why break the data apart in the first place?

The Solution:  First, we designed the file to use one vertical data table.  Next we eliminated the need to type any data at all, zero, none.  Instead, we decided to use a few Excel macros to import the ADP data into the Excel file.  With the use of additional macros to further manipulate the file, along with the use of advanced formulas, the file was programmatically populated, and the desired output was produced.

The Result:  With the use of some simple Excel VBA coding, we eliminated all of the typing.    The file would now update in 5-7 seconds.  The user would simply push one button to run the code to update the file, Excel does the rest. This saved the user hours of unnecessary work.

 

Experts Note:  The staff member using the Excel file is not an Excel programmer, and they are not trained in workbook design, so they did the best they could, and it ultimately worked, it just took a lot of unnecessary effort and a lot of time. But that is why we are here, we know best practices, and we know ease of use, our Excel consultancy is here to help.

 

 

Like data is best served in a single table, with a vertical format, and not across multiple tabs, with one tab for each period.

 

Excel Update (November 2022): Microsoft released a series of new Array Spill Functions that allow an Excel programmer to “fix” the problems such as above, using the VStack Function. Microsoft noticed that users were not using Excel properly, that they were in fact making it less easy to use, so Microsoft decided to create a bunch of new functions to allow a seasoned excel programmer to fix what the user broke.  See, this is a HUGE issue; stop separating your data across tabs, use vertical data tables instead.

 

 

Second Example:  In the image below, the client was making the same mistake as the other client in this example, as this is a very common approach to Excel design. But in this case the challenge is greater, in the that will have over 50 tabs in this file, and they will have over 10 files. Rolling this data up will take some effort, and it is completely unnecessary.  Once again, the people building these files are not programmers and they just don’t know a better way to do it.

Their Initial Needs Statement:  “We need to build a new Excel timesheet for use by those in the field to populate.  We will have many tabs, many workbooks, and currently it is not at all easy to use. As such we are seeking help.  The various tabs will need to roll up.”

Their First Problem:  The problem these designs created, are that they need to roll up the data. If the data was in a data table, this need would not exist, nor would the work to make the changes to the file, each time they add data.  Don’t forget there are over 10 files, over 100 tabs!

Their Second Problem:  The user would spend days typing this data into QuickBooks, after extracting it from Excel, via a printed copy.  That could be 100% automated with Excel vba coding, and they would not need to type anything into QuickBooks.

Their Third Problem:  They said they would have dozens, yes dozens, of errors every day, and they would need to find those and to fix those.  This is not user friendly.

Their Ideal Solution:  A few design changes can make this workbook much easier to use, literally. First, store the data in a data table. Second, use formulas to populate the reports, charts, and analysis. Third, use Excel vba to upload the QuickBooks data into QuickBooks.  Fourth, add additional macros to automate other aspects of the file.  Finally, add all of the user-friendly features listed in this post.

 

Excel Expert’s Note:  When you have multiple identical tabs, to store data for different periods there is an additional issue you cause.  If you want to change the design of one of the tabs, you will need to make those same changes on all of those tabs, in this case, in over 100 of them.  Or you can make the change to one vertical data table. Once again, if you want ease of use, design the file to best practices.

 

 

 

 

 

---

 

 

Excel Expert’s Note:  Not all custom Excel solutions are 100% built to best practices, or fully integrated and automated as they could be, as the client often has limited resources.

All solutions are built to the client’s desires and interests, not ours, and a fully integrated and automated solution may not be desired.  We get it, no worries; we are here to build the solution you want.

That said, you can still make these solutions as user-friendly as possible.

 

 

 

Built 100% to Best Practices is Ideal, but it is not Required

 

Partially Automated is Okay Too:  You do not have to fully automate your Excel applications, you do not need to make them a 1-Click solution, you do not need for them be 100% user-friendly in order to use them.  You can get your work done in a partially automated solution; you can get your work done in a 100% manual solution. It comes down to degrees, how important is it to you that the file be easy to use.

The difference between each solution is this, how much time and effort do you want to put into using the file, each time you use it. Do you want to click one button and have Excel do all of the work, or do you want to spend hours or days typing data, changing formulas, creating new tabs, copying this, pasting that, etc.? Both can get the job done.  One is ideal, the other is not, but life is not ideal, so ……..

 

Balancing Act:  Importantly, the degree to which your file is easy to use often comes down to available resources.  Do you either have the time and the skills to program it right? Do you have the budget to have it built right?  Often the answer is no.  So, while you may want a single-click, fully automated solution, but based on resources that may not be an option for you.

If a 100% point-n-click application is not of interest, or not within your budget, no worries, you can still create a user-friendly file by applying as many of the best design practices as you can. You would want to start with the three below, as this is the low hanging fruit when it comes to making a Microsoft Excel workbook easy to use.

• VBA coding is the low hanging fruit so start there.  Macros can overcome many design flaws, and they can make the file easier to use.
• Using vertical data tables to store your data, along with using advanced functions would greatly enhance the user experience as well, less changes to formulas and to the file.
• Add Excel Forms for controlled data entry and you have largely created a user-friendly file right there.

 

Entering the Data in the Report:  Sometimes the client does not want complete separation between the data being entered and the report being produced (See Hybrid Solution below).  Sometimes they insist on entering data directly into the reports tab. While that is not as clean as we would like, it is what it is and we can make that work just as well.

In these situations, you still want to control the user’s data entry as much as possible.  To address this, you will often use Validation Controls or Input Cells on the report sheet.  These will determine what is shown on the report as formulas will reference these cells.  This is a hybrid application, and it is very common, we build a lot of these. These are easier to use, but not easy to use.

 

Expert’s Advice:  If you have limited resources, yet you still want a semi-user-friendly workbook, look at your current solution, and look at the list of best practices, see which of design changes can be applied to your workbook.  Try to go after the ones that will give the biggest bang for your buck/time, such as applying macros.

And consider this, you can do this in phases, as resources become available.  Anything you can do to make the file easier for the user is a win-win situation as you will save your staff time, frustration, and errors, which in turn saves you money.

 

 

---

 

 

Example:  Hybrid Report Data Entry Form / Report Tab

Report Based Data Entry:  The use of a ‘controlled data entry‘ form is not always restricted to a UserForm or a Tab-Based-Form.  Often the Excel file is built to allow data entry into the report sheet itself, which is not ideal as we have stated, but it is common.  This is combining the data entry (Inputs) with the reports (Output), in one tab.

In this example, this works better for manual data entry scenarios than programmatically imported data as the ‘customer’ and the ‘order’ do not exist until the user enters them into the Excel workbook.  So this works, and it works well, it is just not ideal.

In this example, the interactive ‘form’ is also the report. This report requires manual data entry; it requires the user to use the Validation Controls to select the ‘Item’s, the’ Quantities’, and the ‘Company’ for the ‘Order’. Formulas populate the rest of the invoice.

In this example the user is being controlled on what they can enter into the application. They can use the Validation Controls for controlled data entry, and they have the Input cells to hold variables (Tax rates). But the rest of the report is protected and locked down.

So while not following best practices, it still works, it gets the job done, and the client is very happy.

 

UserForm Data Entry:  In this Excel file new customers are entered into the customers data table via an Excel UserForm. The data entered on the UserForm is appended to a customer’s table, and then referenced on this report via the Validation Controls. Once again, a controlled environment to maintain data integrity while adding ease of use.

There is a products table as well that contains the details and pricing on all products, which in turn is referenced on the report, for the items selected, via XLookups.

Once the invoice has been generated the user simply clicks the append button, which processes the new invoice.   The invoice is printed, and the invoice details are sent to a detailed, transaction table, recording all invoices to date.  Your work is done, no formulas to change, nothing to copy or paste, nothing to distribute, just save and close the file.

In the example below, this is the reverse order for generating a report, but for this client, that is how they wanted it to work, so here it is.  Even in this rather interesting twist, the file is still just as user friendly and just as easy to use. Type, Point, Click, Done = Easy to Use.

 

The Result:  The end result is a printed invoice for the client.  And the details of the order are appended to data table in the file.  Not the norm, but it worked for their need.

 

Expert’s Note:  Clients do not always want a complete separation between the output and the inputs.  Sometimes they want a hybrid solution where they do enter some data, where they do make some choices, on the actual report itself.  In those cases, we build a hybrid solution that is easy to use.

 

 

 

 

 

---

 

 

 

20 Common Reasons Microsoft Excel is Not Easy to Use

1. 1. 1. It is built wrong; it was not designed correctly.
         1. It was not designed with best practices in mind.
      2. You manually type data into the Excel file when an electronic version is available.
         1. You are not taking advantage of Excel vba to programmatically import the data for you.  A HUGE time saver.
         2. You do not control what is typed.
            1. You are not using Excel Forms for controlled data entry.
      3. You find yourself changing formulas from use to use, or when entering a new period.
         1. Write more advanced Excel functions to avoid this.
      4. You are having to copy cells down rows or across columns as the data expands.
         1. Excel should be built to account for increases in the size of data tables.
      5. You manually copy and paste data from one range to another range in the file.
         1. Let macros do that for you.
      6. You manually add new tabs, columns, or rows to the file when using it.
         1. If this needs to be done, allow vba to do that for you.
      7. You are using the wrong formulas to do the job.
         1. There are over 450 Functions in Excel, many will do the trick, but some better than others.
            1. You are using Index/Match or the Vlookup instead of the XLookup.
      8. You are not using an Inputs Sheet to hold variables.
         1. You are adding hard-coded values into your formulas instead of referencing a Named Input Cell.
            1. When the value of your variable changes, you need to manually update your formulas.
      9. You are not using data tables to store your data.
         1. You are storing your data across multiple tabs, possibly multiple files
            1. You are having to roll-up your data in order to use it.
      10. You are manually importing or exporting data.
          1. You are not using code to automate your workbook.
             1. You are spending time doing something you do not need to do.
      11. You are not using Excel macros to automate the distribution process.
          1. Excel VBA / Macros can send emails, save files to the server, as well as upload to the web.
      12. You are manually reconciling the file.
          1. Using advanced functions such as SumIfs and the XLookup, you can quickly reconcile your data for accuracy.
             1. Design it once, and then you simply view the results each time you update the file.
             2. Add Conditional Formatting to ‘see‘ the incorrect results instantly.
      13. Your formulas, charts and named ranges are not dynamic.
          1. Expect your data tables to grow or even shrink over time.  Use Dynamic Named Ranges to reference those tables so that all of the data in being included.
      14. You are not using Validation Controls.
          1. If you want accuracy of typed data, using Drop-Down Lists is one of the best ways to do that.
      15. You are not using Conditional Formatting.
          1. With Conditional Formatting, you can instantly see certain results, in your data, based on the conditions you have set, which formats the cells of interest.
             1. One of the best ways to spot errors in your data, before you distribute the work.
             2. ‘See‘ past due accounts before they go past due.
      16. You are not using filters.
          1. A quick and easy way to isolate records of interest, without using formulas.
      17. You are not using Lists or Reference tables to populate your Validation Controls.
          1. Use Dynamic Lists to populate your Drop-Down lists
             1. Provide an easy to access, unprotected range to house items for the list, easily updateable.
      18. You are not using UserForms or Tab based forms for ease of use.
          1. After Excel VBA, UserForms are the most powerful ease of use tool in Excel.
             1. Completely control the environment, the user, and the data, while optimizing ease of use.
             2. This is the most advanced thing you can do in Excel.
                1. Any manual action the user would take, create a macro to do it for them, place it on a Form.
      19. You are not using Pivot Tables, Pivot Charts, and Slicers.
          1. If you want to analyze your data, there is no better tool to do so.
          2. Pivot Tables, Pivot Charts with Slicers should be at the center of your Excel Dashboards.
          3. Quickly drill-down into the details, do Ad Hoc analysis on the fly.
      20. You are not letting Excel do the work Microsoft built it to do.
          1. Microsoft created Excel VBA with one goal in mind, ease-of-use.  Excel is intended to be a fully integrated and automated application, that is how Microsoft Designed it.
             1. Few even begin to tap the true power of Microsoft Excel.
                1. Most companies use Excel to maybe 20% of its capacity.
                   1. They use Excel as a manual tool that one must maintain, revise, and update.
                      1. But all of that is completely unnecessary when built right.
                         1. Microsoft Excel should be easy to use.
                            1. We make Excel templates that are easy to use. 877-392-3539.

 

 

---

 

 

Example of how to run a well-built Excel File

1. 1. 1. Open the file.
      2. Type data if required but do so in a form-based and controlled environment.
      3. Push the Run button to import data and to manipulate the file.
      4. Review the results.
      5. Save and close the file.

 

Point, Click, Done – Microsoft Excel Should be Easy to Use.

 

In a fully integrated and automated system that is all the user needs to do in order to update their Excel spreadsheet, to do the complete update process, start to finish.  If you can eliminate the manual data entry, you can completely eliminate the user from the process all together.

In that scenario the user opens the file, pushes the run button and then sits back and waits for a few seconds, and watches the computer do all of the work.  When they hear the printer printing the output, they will see an onscreen prompt saying that the update is complete, please save and close the file. That is all there is to it.

Sure, the user will want to review the results, to look at the reports and analysis, to play with the Pivot Charts and Tables, but they do not need to be part of the update process.  This is Microsoft Excel made easy.

 

 

 

 

 

---

 

 

Below we go over some of the User-Friendly Features of Excel

 

Pivots w/ Slicers as Data Visualization Tools

Pivot Tables, Pivot Charts, Slicers and TimeLine are super user-friendly, and they are also one of the most powerful features of Microsoft Excel.  They are amazing data visualization tools.   I mean just look at them.

Those are dynamic, interactive charts and graphs, based on the detailed data in your file. The use of Pivot Tables makes drilling down into the details of the data as simple as the click of a mouse.  The use of Slicers makes filtering the data a breeze.

Senior management loves the KPI reporting capabilities of Pivot Charts and Pivot Tables as the primary components of an Excel dashboard.

VBA, UserForms, then Pivot Tables, those are the top three user-friendly features of Microsoft Excel.  With them Excel can be easier to use.

 

 

 

 

 

Custom Dialog and Message Boxes to Interact with the User

You can leverage the graphical user interface capabilities of Microsoft Excel to require inputs as well as data from the user, as they work in the file. Their choices and their data can then be used by the code, to determine what to do next.

An example would be putting a message box prompt on a button that is used to delete data from the file.  After the user clicks the button, you want to ask them, via the message box, do they want to continue with the deletion of the data, yes or no.  if they click yes, the data is then deleted, if they click no, then their data is preserved.  A good safety precaution.

 

 

 

 

Lists to Populate Validation Controls and Drop-Down boxes for Controlled Data Entry

We see these used more and more all the time, as they are a powerful way to control what the user can enter, when entering data via a drop-down list.  Both Validation Controls and ComboBoxes use these.

Data integrity is an issue with many Excel files, these Dynamic Named Lists allow you to guide the user in selecting the right item from a list.

 

 

 

 

 

 

 

Excel Tab Based Forms for Controlled Data Entry

Excel Tab Based Forms are a way to make manual data entry, controlled, and super easy to do. Cheaper to have built than the more advanced and VBA heavy Excel UserForms, ultimately accomplishing the same end result, just not as powerful.

Excel UserForms are a powerful tool, the gold standard in Excel, but if you have limited resources or limited programming skills, a tab-based form such as below will do the trick.

 

 

 

 

 

 

Protect the File to Enhance the User Experience

If you protect the file, leaving some cells unlocked, it makes it much easier to control the user, to maintain the integrity of the file, and to prevent the user from accidentally deleting formulas and such.

If you protect the file, you can guide the user through the data entry cells, allowing them to rapidly enter their data.  This is how Excel forms work.  You only unprotect the cells that you want them to have access to, everything else is locked down.

 

Subject Matter Expert’s Advice:  Always work in a protected workbook, as unprotected files will quickly accumulate errors.  Errors that can be hard to spot. Overriding formulas with hard-coded values is often the cause, as is the accidental deletion of data, or even changes to formulas.   Maintain the integrity of your workbook by properly applying workbook protection.

 

 

 

 

 

 

 

Simplify File Distribution via Buttons and Macros.

When your update file button is clicked, it can email a copy of the file to staff on a distribution list, it can save the file out on the server for users to access, it can print to the printer, print to a PDF, and it can be faxed.  There is no reason for you to manually distribute the file or the reports.  Microsoft Excel can do all of that for you.

For each distribution option, you can have a button, and when you click the button, the action will be taken.  It really simplifies the distribution of reports and such.

 

 

 

 

 

 

Build Custom Excel Dashboards for Interactive Analysis

The quickest way to analyze your data is with a custom Excel Dashboard. Dashboards look at the data in your table(s), they allow you to quickly filter the results, no formulas needed, to instantly see the results.  Pivot Tables give you powerful drill-down capabilities, as well as the ability to pivot your data.  No wonder senior management loves them.

 

 

 

 

 

 

Separate the Reports from the Data – Use a Detailed Data Table

A Common Design Error:  You will often see Excel files where the data is manually keyed into the various worksheets and reports, for what is believed to be ease of use for the user.  But it actually has the opposite effect. You now have the user looking for the cells where data should be entered, all around the file, instead of being in one place. Did they properly update all of them?

In an optimized solution:  The data should be entered via a form, and then stored in an Excel table.  The reports, analysis and Pivot Tables should use formulas to populate the desired details, and the report should be protected and read-only.

In our opinion:  The user should not make changes to the report tabs, that usually leads to issues and frustrations. Why should you design it this way?  Because Microsoft Excel should be easy to use.

Conclusion:  It may sound confusing or more difficult, but it is not.  With this approach any record, any period, any historical report can be instantly created.  All of your data is in one place.  Remember, this is built off of a vertical data table, and any record in the table can feed the report.  That truly is ease of use.

 

Excel Expert’s Note:  Using the SumIfs, CountIfs, and XLookup Functions can extract all of the data you need for your file, from vertical data tables quite easily, and efficiently.  Don’t complicate the file by spreading your data across multiple tabs, that ultimately need to be rolled up.

Reports and Data Tables are not one thing; they are two different objects in Excel.  Treat them as such for ease-of-use.

 

 

 

 

 

Use an Inputs Sheet to Store Variables for Ease-of-use

Keep your reports as read-only, don’t place hard coded values in formulas, for ease of use.  Instead place them on a special sheet, where you can enter these values, and they will be used across the file. This will make life easier.

Too often we will see hard-coded values, which are really variables, placed inside Excel functions.  That works until the value of the variable changes, then you find yourself chaining every formula that uses that variable.  This is 100% unnecessary and definitely not user friendly.

An Inputs Sheet, or the use of Input Cells is one of the most important best practice design practices.

 

 

 

 

 

 

 

Use On Sheet Controls to Simplify the User Experience

Sometimes you want to have controls and Inputs on the report sheet for ease of use.  If so, the approach below can work well.  There are Validation Controls, Buttons to perform specific actions and an input cell to store a value that will be used in formulas.  This works in a protected environment.  It is intuitive to use, and it can make working in Excel a joy.

 

 

 

---

 

 

Frequently Asked Questions:

 

How good do my staff need to be in order to run/update a fully automated Microsoft Excel application?

Answer:  

Not good at all, just a typical Excel user.  If you hired a firm to build you a fully automated Excel solution, why should your staff even need to know any of the Excel functions?  The file should be built so that the formulas work, as is, for the life of the file. As such the user will not be changing and of the formulas or the structure of the file, they are updating the file by adding new data.

The user just needs to type be able to type data, to click buttons, and to review the results.  Being able to Filter, use Pivot Tables, and all of that is helpful, but not required to run/update the file for a new period.

Our Advice: 

Demand more in the Excel solutions you pay for, demand full automation. Require an advanced graphic user interface with ease of use built into the file.  Get a Smarter Microsoft Solution.

 

How many cars do you see with a manual stick-shift to change gears? I hear the Telsa will drive itself. Excel can be just as easy to use as the Telsa is to drive.

Why drive a car that will drive itself?  Why manually update an Excel file that will upsdate itself.

 

 

Expert’s Note:  

When you build a fully integrated and automated user-friendly Microsoft Excel workbook, you are building a file that anyone can use, without having any experience in Excel. In these highly-automated solutions, the user does not even need to know what a VLookup is, as they will never write a formula in the file, that was the Excel programmers’ job, not the job of the user.

 

 

 

 

How much time can an integrated and automated workbook save me?

Answer: 

85% – 99%.  It depends on how many hours or days your staff is currently using to manually update the file for the new period. Most automated systems run their code and the entire update process in seconds. Sometimes it might require a few minutes if you have a lot of data to process and other applications to run, else, the entire update process takes just a few seconds and no user involvement.

99% in a fully automated solution.   In a semi-automated application, we can still reduce the effort by say 85% or more.  Manual typing data is where 99% of the user’s time goes in a semi-automated system.

 

Editor’s Comment:  Think of what your staff can do with all of the saved time. More time can be spent on analysis and decision making.

 

 

 

 

 

What if I was given a poorly designed workbook to use, how can I make it easier to work in?

Answer: 

You have two options:  You can rebuild the entire workbook, or you can retrofit the existing workbook with best design practices in mind.   If you decide to retrofit the Excel file, you can do so all at once, or you can do the work in phases.

You can either hire a firm such as Excel and Access, LLC to help you, or you can do the work in-house.

 

Excel Expert’s Note:  Look at the list of 15 user Friendly features, and apply all that fit your situation, your needs, and your budget. Each one you are able to apply will increase the user-friendliness of your application.

 

 

 

 

 

What is VBA (Macro) and what does it do?

Answer: 

VBA automates Excel, allowing you to do a lot of work with very little, if any human effort, with the push of a button, with the click of a mouse.

Benefit of using VBA in your Excel file:  Excel vba/macros can save the user up to 99% of the time that they use when they work on updating an Excel workbook for a new period. VBA allows anyone to update the data in an Excel file, without knowing a single Excel function.  Excel coding is a game changer. An Expert Excel Programmer can make a workbook so that anyone can use it, no experience needed.

 

Excel Macros allow you to take 99% of the effort out of the update process. Point, click, done.

 

 

 

 

 

 

How can I get better in Excel, so that I can make some of these changes myself?

Answer: 

Training, one-on-one training in Microsoft Excel is the best way to learn Excel, it is the best way to learn how to apply best practices for a user-friendly workbook.

Training Expert’s Advice:  When it comes to Excel training, always work with a Microsoft certified Excel MVP, as they know Excel like no other. Work with a Microsoft subject matter expert by the hour.

 

 

 

 

---

 

 

Microsoft Excel User-Friendly File Checklist

If you read through this post, you have a good idea of what features make working in Excel easier. You know that if you fully automate your Excel workbook, you can make Excel easy to use.

• Proper workbook design.
• Optimal use of VBA/Macros to Automate file.
• Use of Excel UserForms or Tab Forms.
• Use of Validation Controls, Command Buttons, coding.
• Protected, Template format.
• Integrated with other Office applications.
• Pivot tables.
• Data Tables.
• Report Tabs.
• Inputs Tab.
• Lists Tab.
• Advanced use of functions.
• Point-n-click interface.

 

Apply as many of these best practices as possible to your Excel applications, and you will make the file a joy to use, because as we say, Microsoft Excel should be easy to use.

 

 

---

 

 

 

If Your Excel Files are not User Friendly, Our Excel Consultancy is Here to Help

 

If you need help making your Microsoft Excel files user-friendly, our Excel Consultancy can help you in two ways:

1. Our team of Excel programmers can build a 100% custom Excel template for you.
2. Our team of expert Microsoft Excel trainers can train you or your staff on how to program an Excel file to best practices, to be easy to use.

 

Consultations are free, we can discuss your needs in detail, we can look at your files live over Zoom, we’re here to help. No matter your need, be it Excel programming or Excel training, our team of Microsoft Excel consultants can provide the exact help your company needs.  Believe me when I say, Microsoft Excel should be easy to use.

 

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