SERVICES

REPETITIVE

REPETITIVE VERSION

Let’s say we have a table with several properties that we want to keep tabs on. We want to be able to quickly filter for information we need.

One way to do this would be as shown here. We can add filters to each header and manually use them to filter and then un-filter for specific items.

While this method isn’t bad, it still requires us to manually click each drop-down and enter each search term for every item we want to look for.

Anyone who’s done this over and over will know how frustrating it can become. The mouse moves and clicks begin to add up over time.

But it doesn’t have to be that way…

Here is a much more efficient solution. Utilizing visual basic functionality that’s already in Excel, we can automate the filtering process.

In this version, we’ve added a live filter box and tied it to back-end programming to take out all of the extra steps.

Now, we can search for text in any of the columns just by entering a word in the box. Note that the box also stays selected even after the text is entered to allow for another filtering command

Then, to clear all filters, we just delete the text in the box and all the filters reset.

No unnecessary mouse movements or clicks.

AUTOMATED VERSION

Here is a much more efficient solution. Utilizing visual basic functionality that’s already in Excel, we can automate the filtering process.

In this version, we’ve added a live filter box and tied it to back-end programming to take out all of the extra steps.

Now, we can search for text in any of the columns just by entering a word in the box. Note that the box also stays selected even after the text is entered to allow for another filtering command

Then, to clear all filters, we just delete the text in the box and all the filters reset.

No unnecessary mouse movements or clicks.

CLUNKY

CLUNKY VERSION

Here we have some quality control data in a manufacturing environment, located in several pdf files. There has been an issue with a customer and we need to be able to find the data for a specific part on a specific date.

The good news is that we’ve named the files systematically with part number, start date and end date in each file name. So, for a single search, we can scroll down through the list and eventually find the file we need.

But what if there are several we’re looking for?

Or what if we need to go back and find this same file again?

This kind of system, as it is here, often sticks around because there’s no better alternative. But there can be…

By creating a search tool in Excel and leveraging Excel’s ability to search for and manipulate files outside itself, we can come up with a new system as shown here.

With our new, streamlined system, we’re able to enter a part number and a date and let the back-end programming automatically search through our list of PDFs and place our desired file (or files) at the top.

Then, we’re able to easily open it straight from Excel without even having to open a new window or use any additional tools. This file becomes our home base, eliminating several steps in the process and speeding up the overall system.

Once a system is streamlined, it becomes clear just how much unnecessary action was needed with the clunkier system.

STREAMLINED VERSION

By creating a search tool in Excel and leveraging Excel’s ability to search for and manipulate files outside itself, we can come up with a new system as shown here.

With our new, streamlined system, we’re able to enter a part number and a date and let the back-end programming automatically search through our list of PDFs and place our desired file (or files) at the top.

Then, we’re able to easily open it straight from Excel without even having to open a new window or use any additional tools. This file becomes our home base, eliminating several steps in the process and speeding up the overall system.

Once a system is streamlined, it becomes clear just how much unnecessary action was needed with the clunkier system.

CONFUSING

CONFUSING VERSION

In this example, we have a production line that is giving us minute-by-minute Pass/Fail results.

We’d like to not only see how many failures we have per day, but also  when they are occurring so that we can maximize our efforts to solve the problems.

In the current configuration, this proves to be very difficult, as we have to scroll down to find each failure.

There are several ways we can attack this, but currently the information is confusing and susceptible to misinterpretation.

Let’s clarify it…

In this improved version, we’ve done a few things.

First, we created a mini dashboard at the top using formulas that gives us a responsive Pass/Fail readout.

But it still doesn’t give us the whole picture. So, we then use visual basic to take all of the data and present it into a simple popup that shows us quickly where the most failures are occurring during the day.

We can  then use that information to more efficiently focus our efforts to reduce the failures.

When data is presented more visibly, it reduces miscommunication and helps teams focus on the true root problem.

CLARIFIED VERSION

In this improved version, we’ve done a few things.

First, we created a mini dashboard at the top using formulas that gives us a responsive Pass/Fail readout.

But it still doesn’t give us the whole picture. So, we then use visual basic to take all of the data and present it into a simple popup that shows us quickly where the most failures are occurring during the day.

We can  then use that information to more efficiently focus our efforts to reduce the failures.

When data is presented more visibly, it reduces miscommunication and helps teams focus on the true root problem.