Tag Archives: Excel and Spreadsheets

One Easy Excel Formula to Track Shipments

Wouldn’t it be awesome if a Microsoft Excel formula could automatically pull tracking information for shipments sent via UPS, FedEx, USPS, YRC, and other major carriers? Well – you can. Jimmy Pena from ShipTrackAddin.com has created a great Excel add-in that does just that. Best of all, it’s free (although the author does accept donations if it helps save you time and effort in tracking shipments).

How it Works

After a quick download and an easy install of the add-in, your Excel will have a new formula called ShipTrack. Then, you just use the ShipTrack formula in your spreadsheet just as you would use a SUM formula. For example, I would type the following into a cell:


After a second of grabbing the information from the FedEx servers, the cell would return this value:

Delivered Monday, January 13, 2014 at 5:53 PM at signed by MMORRALIS FEDEXGROUND

It’s as easy as that. If you have a long list of tracking numbers, you can just copy the formula down and it will populate them all instantly.

You can also use references in the formula, so the following would work just as well:



Carriers Included in ShipTrack

Perhaps the best part about the ShipTrack formula add-in is the long list of carriers it covers. The current version, 4.3.1, supports the following carriers:

Carriers Included in the ShipTrack Formula
  • A1 Express
  • A-1 International
  • Blue Dart
  • CEVA Logistics
  • Conway Freight
  • DHL
  • DHL Global Forwarding
  • Expeditors
  • FedEx
  • Lasership
  • New England Motor Freight
  • Old Dominion
  • Ontrac
  • Pegasus Logistics Group
  • Purolator
  • R+L Carriers
  • Rworld Couriers
  • Safmarine
  • SAIA LTL Freight
  • TNT
  • United Parcel Service (including LTL)
  • U.S. Postal Service
  • YRC Freight
  • YRC Regional (including New Penn, Holland, Reddaway)

Carriers are also being added regularly, so check the site to see if a specific carrier is in the works to be added.

Download the ShipTrack Add-in for Free

You can learn more and download the ShipTrack add-in over at ShipTrackAddin.com.

Build an Awesome Vendor Scorecard Program in 4 Easy Steps

Vendor scorecards measure and track supplier performance on various dimensions that are important to your organization. At first, I was reluctant to start a scorecard program because I thought our company was too small and too busy. However, after eventually beginning our program, I saw powerful results that freed up time and helped the company grow.

Vendor Scorecard Example

Vendor Scorecard Template with ExamplesVendor scorecards strengthen supply chain relationships and help focus your suppliers on what matters most to you. Scorecards set goals for your vendors to reach for so they can become your vendor of choice. You can clearly see where each vendor ranks against each other, which helps you decide which supplier to work with on complex projects. This article outlines the four steps I took in building our company’s vendor scorecard program. I have attached a Excel Vendor Scorecard Template that I put together as a starting place for your own scorecard.

1. Decide What Matters

The first step in creating a vendor scorecard program is to define what your ideal vendor would look like. For me, it would be someone that communicated clearly 100% of the time, shipped quality products for free, and had a lead time of 15 minutes. Although those requests are a bit ridiculous in my industry, it does highlight what matters to me in my vendors: communication, quality, pricing, and lead-time. Together with my team, we took my brainstorm farther and came up with four categories that matter most to us with our vendors:

  • Pricing/costs, including payment terms
  • Production and Supply chain, including communication and lead-time
  • Quality
  • Product Development

Essentially, if our vendors could continually improve on these four points each year, our organization would benefit immensely.

2. Measure the Metrics

Having defined the broad categories, we now have to build the nitty-gritty of the scorecard. You need to build specific, measurable metrics for each category. Specifically, what exactly will you measure, and more importantly, how? For example, a pricing metric could be a comparison of costs between all capable vendors. A quality metric might be the percentage of orders with quality defects.

Good scorecard metrics should clearly define what is good, acceptable, and bad performance in each dimension. Your metrics should be a score for how your vendors are doing in aspects that matter most to you. They should be easy to understand, and if possible, easy to calculate. Unfortunately, building the perfect metrics often takes some deep thought to get them right.

Nailing the Details is Key

Many metrics were much more complicated to fully define than I thought they would be. For example, lead time is an excellent metric that I use. Tracking the time from when you place an order to when it gets delivered is a great way to compare vendors and encourage reductions in lead time. However, measuring this can be tricky when you get into the details. Should you track the time until delivery at to your location or delivery at port? If you ask a vendor to delay a shipment, will their lead-time artificially inflate?

For most quantitative metrics, your accounting system should have the records you need. However, based on the specific things you want to measure  you also might need to start tracking new events or information. For both of the above lead-time questions, I had to change our receipt processes to account for how we wanted to measure that metric. Despite the added work, tracking more data allowed us to trust our metrics and better compare our vendors apples to apples.

A Note on Subjective Scores

When hard data is unavailable or impossible, use a subjective grade. For example, “This Vendor is Flexible in Requests to Alter Production” is a difficult metric to track in our ERP system. Instead, at the end of each quarter, our supply chain team fills out a survey for each vendor that rates them on several dimensions such as flexibility. Rating vendors on a scale is the best way to get a good score from a soft metric. Even better is when the survey has an example for a top, middle, and bottom score for the metric so that scoring is more consistent across teammates. Recording everything in a free Google Form that you send out to your team is even better.

Google Doc Questionnaire 2

Weight What Matters

Once you have the metrics you want to measure (I have 4-6 in each category), it’s time to weight them. Start by rating the overall categories. The pricing category may be 25% of the total score, quality 40%. When your categories equal 100%, weight the individual components of each category. For example, if the quality category is weighted at 20% and has three metrics, then those three metrics could be 5%, 12%, and 3%, which adds up to 20%. The Vendor Scorecard Template shows my weighting.

Example Weighting

Pull Out the Gradebook

Maybe it’s from the report cards I received every semester in public school, but the A through F scale carries a lot of significance to me. That’s why I like to use that scale for each of my metrics. Some can only receive an A or F, or A, C, or F, but they all have the same percentage score. Based on their grade, vendors receive a percentage of that metrics weight as follows:

  • A – 100% A metric with 10% of the total scorecard weight would be 10% with an A
  • B – 75% (7.5% with the same metric)
  • C – 50% (5%)
  • D – 25% (2.5%)
  • F – 0%

Color-coding the scale adds the final touch of understanding so that it translates well and conveys the message clearly.

Example Weighting

Build the Document

Finally, once you’ve figured out your categories, metrics, and weighting, put it all together in a spreadsheet scorecard. You can use my template as a starting point to build your own.

3. Roll Out the Program

Once your scorecard is complete, implementation is your next bull to lasso. You’ll need to devise a plan to clearly communicate what, why, and how you are measuring your vendors. Depending on your suppliers, your experience could be much different, but here’s what I did.Why a Vendor Scroecard?

First, I put together a presentation with one or more slides explaining the following. It was detailed and thorough so that our vendors could clearly understand each score. Specifically, the document had the following:

  • A detailed explanation of each category and metric
    • For complex calculations, I included an example slide
    • Explanation of weights were also included
  • Reasons why we were beginning the vendor scorecard program
  • The implementation schedule (trial and full launch)
  • Our commitment to our vendors

Armed with a document that clearly defined the program, our CEO emailed the presentation and the scorecard spreadsheet to the leadership of our key suppliers. He asked them to review it and then meet with us in a video conference discussing the program. During the meetings with our six key suppliers, the CEO expressed support of the program and our supply chain team explained the details. Most vendors appreciate being measured on more than just price, and so all of our vendors were excited about the program as a chance to prove their holistic value to our company.

We designated the first month as a trial period where we would track performance, iron out issues, and report scores but not take action based on their results. After meeting at the end of the first month to discuss the trial run, we began the program in earnest.

4. Review and Reward

What will make your vendor scorecard program truly succeed is your diligence after implementation. I strive to send out scorecards on-time at the end of every quarter. My team schedules meetings via Skype or in person to review the scorecard each quarter and discuss ways to improve. The communication is two-way – we want all our vendors to reach perfect scores. That is why we council openly about what each of us can change to improve the metrics.

Another big decision to make is what you’ll do because of the scores. Will vendors with consistently high scores obtain a preferred status? Will quality checks or audits happen less frequently? Will you distance yourself from vendors who are very cheap, but fail in every other category? Will you reward contracts based on scores?

If you find yourself rewarding higher scores with more business, then your weighting is probably correct. However, if more and more business is still going to vendors with lower scores, then consider revising your scorecard to better reflect your company’s true priorities.

A great and relatively inexpensive way to encourage scorecard improvement is a vendor of the year program. This could involve a personal meeting, dinner with the CEO, and a plaque for the winning company. When I watch the “Walmart Vendor of the Year” award go to one of my competitors, I find new motivation to improve. Your suppliers may feel the same.

Bonus Step – Survey Your Vendors for Improvement Tips

If your vendor scorecard program is chugging along, then consider asking your vendors to score you. Sending a quarterly feedback survey to your vendors to discuss at the same time as their scorecard can bring insights into how you can be a better customer. Some questions could be:

  • What good practices do your other customers do that you wish we did?
  • What can we do to help you reduce lead-time?
  • What was an example of a project that went well? What about that experience can we recreate for all future projects?

If you make it clear they won’t be penalized for honesty, then you may be lucky enough to get great feedback on how to truly improve. Becoming a better customer can help your vendors better service you. In addition, you may pick up some best practices from their other customers or resolve root causes of your own deep problems. Address these issues in the scorecard review meetings and make commitments to improve when possible. We received a lot best practice tips from our vendors when we said, “we’re really bad at forecasting, so we’ve brought on staff with forecasting experience and invested in the software we needed.” They detailed how their other customers forecast and recommended we try the same.

Final Thoughts

As I talked about in my article on supply chain gamification, games have a way of bringing out our passion and motivation. A vendor scorecard brings the power of game mentality to supplier relations. “Just keep everything green and keep out reds” becomes the goal of your vendors. “Work with the highest scoring vendors” becomes your vendor selection shortcut. Measuring progress brings improvement that both your vendor and you will enjoy.

From the success I’ve seen from the program, I wish I had started it years ago. This quickly brought to mind the mantra of a friend of mine in process improvement. “There’s two good times to plant a tree: twenty years ago and now.”

If you haven’t started a program yet, begin today. If you have one already, take a look at how you can improve. Either way, share your experience in a comment below.

Update – Learn More about Vendor Scorecards in our Podcast

In our podcast interview with Mark Kosiba (former VP of Operations at Skullcandy), Mark talks about vendor scorecards and their effect on his company. The above model was based on his help, so it definitely applies to anyone wanting to implement a vendor scorecard program similar to the above.

Check out the podcast to learn more: How Skullcandy Rocked S&OP (and Vendor Scorecards)

7 Simple Ways to Manage Temporary Workers Better

Temporary WorkersWhether it’s a seasonal jump in orders or an unexpectedly large rework project, calling on temporary workers is often the only way to get everything done on time. I refer to temporary employees as one of my three supply chain silver bullets that help me pull off operational miracles. Having managed dozens of projects with workers I just met, I’ve gathered a list of seven ways to make those projects run smoother and quicker. By incorporating the concepts below, your next project with temporary workers will be a better experience for everyone involved.

1.     Start Right with a Clear Orientation Huddle

The first twenty minutes together with your new team sets the tone for the entire day. Use this opportunity to set clear expectations and preempt later distractions and problems by answering common questions.

When temporary workers first arrive, I have them read over a laminated, double-sided sheet. Not only does this protect our company by letting them know of HR policies, but also addresses common issues and questions. The sheet contains a two- or three-sentence summary of our policies on the following topics:

  • About Our Company
  • General Warehouse Safety
  • Forklift Safety
  • Dress Code
  • Time Clock Procedures
  • Breaks and Lunches
  • Bathrooms, Break Area, and Smoking Area
  • Cell Phones and Personal Items (be sure to address cell phones specifically)
  • Substance Abuse Policy (from HR)
  • Harassment Policy (from HR)

After reading over the list, each temporary worker signs an acknowledgement sheet that we keep on file. This protects the company and gives us recourse to send people home that break policies.

After gathering everyone’s signed acknowledgement, I hold a brief huddle. Before going into the days’ work, I emphasize a few key expectations. Specifically, I show them the lockers they can put personal items (or when lacking lockers, ask them to keep everything in their cars). I also point out work and break areas as well as recommend local places for lunch. These few minutes answer 90% of the common questions I encounter, which allows us all to focus on the work at hand.

2.     Set Clear Expectations

Having given a clear orientation, I then strive to set very clear expectations of the day’s work. The key here is many visual examples of the end product and a clear standard procedure to reach that result. For example, if we are trying to build 3000 retail displays that day, then I have several completed examples to show everyone. Each station has a color picture of what the display should look like at that station’s point in the process. I build one or two displays completely with everyone watching to ensure they understand how the display look as it is built and completed.

As much as possible, I strive to make the work mistake-proof. Setting up checks to ensure the display is built correctly helps catch errors. If physical checks aren’t possible, then I ask several people to act as quality lookouts along the assembly line to catch any defects. I empower them with the ability to stop the process and call for help when they see errors. I also let everyone know I’ve asked them to do this job to avoid offense.

Finally, I share with the team hourly and daily production goals. This gives a score to the team’s work and helps them gauge their speed. When I’m building something new and have no experience on what to set the goal as, I just guess optimistically. The team usually rises to meet my estimated goal. Sometimes I even run the process myself ahead of time. This allows me to time how long it takes me to complete a few rounds of the process to set a realistic expectation.

3.     Add Meaning to the Work

Just before they get set to work, I answer the often-unasked question of “why am I doing this?” Even though these workers may just be on the job for a day, I’ve seen impressive results when they know the deeper reason behind their work. My goal becomes theirs as well, and many of the workers will give extra effort and suggestions to better accomplish the larger goal.

The explanation doesn’t need to be long. It could go something like this: “Today we’re building 3000 displays that we’re sending to Walmart. They have to be built this specific way because it helps the Walmart employees quickly put the product out in the store. In a couple weeks, you can visit your local Walmart and find one of the displays you built. Then you can point to that display and tell your friends or children ‘I helped make that.'” As your team is able to focus on the higher goal, not just the menial work that lies ahead, they will rally behind the cause and work hard to produce something they are proud of. Five minutes before lunch or the end of the day, I gather everyone around and solicit their feedback for improvements to the process. Without your team knowing the end goal of their work, helpful feedback is rare.

4.     Create a Positive Work Environment

As the workers begin, I help set the pace and atmosphere by working alongside them. This helps me make sure the project gets off to a good start, but it also helps me learn more about my team. I rotate people to find their strengths and adjust workloads to balance bottlenecks. Once everyone is comfortable in his or her role, I try to ensure enthusiasm remains long after the first hour of work.

If everyone can do a great job while talking together, then those conversations often keep everyone upbeat. However, if they become distracted while talking, then I instead turn on the radio. I always see better results when I try to have a little fun with my team, especially toward the end of the day, than when I am overly strict and serious. Simple rewards for meeting goals, such as cheap popsicles if it’s a hot day, or letting the team take five minutes longer on their break, go a long way toward motivation.

5.     Don’t Make Leadership a Mystery

The biggest problems I’ve had with temporary workers come from not assigning adequate supervision. I am frequently called away from the work, and when I don’t assign someone to be in charge, disagreements often arise. Therefore, if I can’t be there to supervise, I do everything I can to have one of my full-time employees, or at the very least a returning worker, assigned to answer questions that arise. This isn’t to quell power struggles, but to create order in a group of workers who still don’t know each other. Knowing there is a supervisor close who can answer question creates order and prevents most problems.

Having someone you know and trust working on the project also fosters more communication. That person can act as a liaison to the shyer, new employees by giving voice to concerns or suggestions they have. I’ve received some great suggestions for improvement that passed from a new worker, through a returning worker, to me.

6.     Be Detailed in Time Management

Simple Excel Punch Clock

Being exact in timing brings great results. I once used a clipboard to have temporary workers track their time each day. This created some tension because some people would write 8:00, even though they really showed up at 8:07. To avoid this problem, I put together a simple punch clock in Excel – which you can download from the Supply Chain Resources page. Having the computer track the time took away any question of timing – and saved our company a few hundred dollars.

Another time trick I love is something I learned from my high school band director. Whenever we had a concert, she would ask us all to report at 6:53 PM. Such a detailed time was memorable, and many more people showed up on time than had she said 7:00 PM. I use this same concept with breaks. If its 2:03, then I tell everyone, “Ok, it’s break time, we will start the line back up at 2:14 – so be back by 2:13.” This brings much more success than “Be back in 10 minutes.”

7.     Build Your “A” Team

Finally, do all you can to build your temporary worker dream team. If your project spans over many days, only invite back the hard workers – and ask the temp agency to send you others to try out.

If someone is not working, hindering the work, or fostering a negative work environment, don’t be afraid to send him or her home. I’ve only had to do this on rare occasions because talking to the person often resolves the issues. However, if someone is causing a safety risk or HR issue, send that person home as soon as you can. Failure to do so not only invites the issue to grow, and other workers may mirror that behavior since it’s bringing no consequence.

For the most part, however, your team will likely be full of good, hard workers. Pay attention to the best and consider bringing them on full-time. We have found some of our very best employees through temporary assignments. It’s our vehicle of choice to add a new team member in our warehouse because we can try them out for an extended period before investing completely in them.

These seven simple tips have helped me better manage the projects I’ve run with temporary workers. Investing some time and effort into the process will result in more efficient workers and better results. These projects, although sometimes stressful, can become positive experiences for everyone involved.

What other suggestions do you have for managing temporary workers? Please leave your thoughts in a comment.

Getting Started with Big Data in a Small Business

Data Analyst Sheriff Badge
Better data leads to better decisions, especially in supply chain and operations. Unfortunately, most small businesses don’t have enough analysts (or any at all) who can comb through databases to provide data-backed recommendations. In fact, that number crunching responsibility often falls to the operations team, who may not have much experience analyzing big data. Perhaps you are in a similar position. You recognize the need for more data in your decisions, but accessing that information seems a bit daunting. If so, then here are three easy levels of data-crunching proficiency that will help you earn your data analyst sheriff badge.

Level 1 – Pivot Tables

If you need to analyze a large, pre-made table of data, then you should to start with a pivot table. Microsoft Excel’s pivot table tool is the data analyst’s faithful and reliable six-shooter. It can handle almost any problem a set of data brings your way. Pivot tables allow you to transform a set of data into an easy-to-rearrange and organized summary grid. The grid of information it produces helps you quickly find patterns, trends, and outliers. In fact, when you hear the words “slice and dice,” you should instantly think pivot tables.

Pivot Tables in Excel

Most people I’ve met who have yet to use pivot tables just don’t know how to start. The easiest way to learn is to just jump in and play around with one in Excel. You can find lots of good online tutorials and videos that will get you up to speed with pivot tables in less than 15 minutes. For example, here’s a seven-minute YouTube video that does a great job at walking through the basics: Microsoft Excel Pivot Table Tutorial for Beginners – Excel 2003, 2007, 2010

Once you feel comfortable manipulating data someone else has provided, then you can take the next step and access the data yourself.

Level 2 – Database Queries

For many small businesses, the challenge with data often lies in how to access your own database. Depending on your accounting  or ERP system, the exact technique will vary. However, you can usually access most small business systems easily through Excel’s Microsoft Query tool. For your exact system, run a web search for “how to access QuickBooks database in excel”, replacing “QuickBooks” with your own system’s name. Most systems will have an ODBC driver, which basically means it has the ability to be queried by outside programs such as Microsoft Excel.

How to Access your Database if it has an ODBC Driver

If your system uses an ODBC driver, then the query process is straightforward. In Excel, go to Data > From Other Sources > From Microsoft Query

Bringing Data Into Excel 1

A list will appear of available databases. Choose the one you want to query and press OK. Below I chose the connection for our Sage 100 database (formerly known as Sage Mas 90 or 200). If you don’t recognize the name of your database, you may need to search the web a little more.

Bringing Data Into Excel 2

Next, you’ll want to select the fields and tables you want in your report. You’ll need to pick fields from tables that are linked together in order to pull the items correctly. It may require some trial and error, as well as some research into how your database is structured.

Bringing Data Into Excel 3

After selecting the fields, the query wizard gives you a few options to filter and sort your data. However, you can access more advanced query options by manipulating your query in the Microsoft Query program. On the last page of the Query Wizard, select that option and press Finish.

Bringing Data Into Excel 4

Inside Microsoft Query, you can add additional filter criteria, table links, and column labels.

Bringing Data Into Excel 5

Once you’ve fine-tuned your query to just what you want, click the “Return Data” button (the one with an arrow pointing to a door by the “Save” button). This will bring the data you’ve selected back into Excel.

Once I learned how to access our company’s database, I began running dozens of queries each day. This helped tremendously, and I learned a great deal from the data. However, I eventually reached the limits of what Microsoft Query could offer. To go further, I had to learn a bit of Structured Query Language, or SQL (pronounced “sequel”), to get the answers I needed.

Level 3 – Advanced Queries with SQL

For some strange reason, I was more eager to learn French in high school than computer code, despite the fact that I query databases much more frequently than I ask which café has the best baguette. Luckily, SQL is very easy to learn (there’s definitely no conjugation or verb tenses).

Although Microsoft Query and Microsoft Access have great query-building interfaces, knowing a bit of SQL can empower you to do much more. SQL functions allow you to manipulate or summarize data as you pull it from the database. SQL also will allow you to make advanced links that might give you data hard to get at any other way. Accordingly, even a little SQL will quickly empower you to more efficiently and effectively get the answers you need. SQL is also the language you’ll use to connect your database with most outside SaaS modules and programs. Knowing SQL can help make implementing such add-ons much easier and cheaper.

The best part of SQL is that you can learn most of what you need to query big data in an afternoon. I recommend Sams Teach Yourself SQL in 10 Minutes. Each lesson actually takes about 10-15 minutes, and after about twenty lessons, you’ll know everything you need to build advance database queries. The book also starts at the very basics, which is great for operations people like me who don’t have a computer science degree.

No matter what the next step is, each of these levels will help you to make data more accessible for your company. With more data, you’ll quickly be making better, fact-based decisions to meet your goals and improve your supply chain.

Your Spreadsheet is Lying to You

I love Excel spreadsheets. There’s nothing more satisfying than turning a blank Excel grid into a fountain of data and conclusions. Invite over pivot tables, external data, and macros, and suddenly you have an entire business analysis party. You can confidently make big dollar decisions from small and simple cells – or so we think.

Excel Lying

Knowing my dependence and appreciation of spreadsheets, a good friend sent me a very interesting Wall Street Journal article, 88% of Spreadsheets Have Errors by Jeremy Olshan. In the article, Olshan exposes how almost all spreadsheets have at least one error in them, and large spreadsheets are usually filled with mistakes. We often are so rushed to get the calculated conclusion to other people that we don’t take time to check our formulas and have someone else proofread our work. Miscalculations and mistrust of analysis can often be the result. The article mentions major economic research that drew incorrect conclusions based on flawed spreadsheet formulas. Likewise, I’d like to share a story of how incorrect cells can wreak havoc on supply chains.

Barcode Blunder

I still shudder to think that a simple, one-sheet spreadsheet completely ruined two weeks of my life.

My company uses a barcode printing program that pulls its information from a simple spreadsheet. The spreadsheet allows users to easily enter the item number they need, and the close-by thermal printer quickly spits out the barcode stickers. I had set up the spreadsheet to pull all the necessary information, including the 12-digit barcode and description, from external sources when the item number was typed in. The formulas worked great, and the spreadsheet seemed perfectly self-explanatory. Or so I thought.

Because the interface was so easy to use, more and more employees began to use it to print barcodes. This was great for me because I was not called out seven times a day to print the stickers. However, the lack of standardized training and safeguards eventually created a problem. Seeing the UPC column, someone replaced my formula that calculated the UPC with a static 12-digit UPC number for the item they needed. Having been saved with the incorrect number, the spreadsheet now printed the same, incorrect UPC barcode for every item. However, because the description was correct, no one caught the error. Worst of all, that week we had a big project that required a large number of labels.

One of our customers increased their orders significantly for an item that we had to label individually. This amounted to over 50,000 incorrect barcodes stickers that we put on, and then shipped without knowing the error. They went out the door on time, and the problem wasn’t fully realized until the product was already set in thousands of retail locations.

Was the Spreadsheet to Blame?

So who or what can we really blame for this mistake? After lots of pondering, I couldn’t blame anyone but myself – the creator of the spreadsheet. I don’t know who changed the cell, but it really wasn’t that person’s fault. Whenever I create a process, program, or spreadsheet for others, I need to make sure it’s error-proof. To fix it, I immediately locked down all the cells and rewrote the spreadsheet to be much harder to incorrectly edit (some simple poka-yoke, or in English, error-proofing). Just as important, we added a barcode-check step in which we scan the barcode and get a second pair of eyes to approve it. So, when the malevolent spreadsheet decides to strike again, we’ll be able to catch it.

While this may be a story more on the need for better processes, it also illustrates the devastating power one spreadsheet cell can conjure.

In the end, the retailer was able to change their computer system to accept the incorrect barcode, so the effect on end consumers was very minimal. The real pains were internal as we identified our process problems and worked to correct them. Of course, I lost some sleep and worked under heavy stress for a couple weeks, but as a result, I learned some important lessons – not the least of which concerns spreadsheets.

Five Tips toward Error-free Spreadsheets

Even if the world is plagued by lying spreadsheets, we can still personally become sources of accurate and correct data analyses. Here are five tips toward more accurate spreadsheets.

1. Get Someone to Proof Your Work and Make You Explain Your Formulas

If you do nothing else, please do this: Ask someone to look through your spreadsheet and test all your assumptions and formula logic. Have them examine key calculations closely and confirm everything is calculating correctly. Offer to do the same for your friend’s spreadsheets in return.

2. Trace Cell Relationships

excel trace function

Excel’s trace functions, found under the formula tab in Excel 2007 and 2010, are excellent ways to make sure all the cells you think are included in a calculation actually are. Trace precedents will point arrows to all the cells that feed into the cell you’ve selected. Trace dependents show all the cells depending on the selected cell. These help you catch problems of cells sneaking in or out of formulas – as well as seeing what might happen to other cells when you change the current cell’s value. Plus you feel pretty smart when there are hundreds of arrows all over your screen, “Wow, I can make some intense calculations.” Note that this step would probably have helped me catch the error in the story above.

3. Verify Links

Update Links

External data, especially from other spreadsheets, can be very tricky. Excel isn’t the best database system in the world, so links sometimes break or don’t update. To avoid problems, verify and update links through the edit links button on the data tab. This tool let’s you ensure links are active and correct. I’ve had many links break for no apparent reason, so be vigilant on checking this frequently.

4. Address background error checks

Background Error

Those little triangles in the top left corner cells are signaling that Excel thinks something might be wrong. Address them all, since often they are true problems. Only ignore the error if you’re sure it’s not an error, and the triangle will disappear.

5. Force Excel to Calculate

There are several reasons Excel stops calculating formulas. Usually it’s because calculations are turned off, but sometimes big files have quirks that hamper calculation – especially when VBA is involved. To recalculate all formulas and update all links, press Ctrl + Alt + Shift + F9. If you have external data, you’ll want to use the Refresh All command. If you have pivot tables that pull from External Data, you’ll want to refresh the pivot tables after you Refresh All (or Refresh All twice). Pressing Refresh All twice, then pressing Ctrl + Alt + Shift + F9 will refresh and calculate everything possible.

When You Need to be 100% Accurate, Rebuild from Scratch

Although these tips will help you catch many errors, there are still chances of problems. Several third-party vendors offer error-checking software. I’ve never tried them, but I do have a pretty strong technique to ensure high accuracy. For very important spreadsheets and decisions, I often recreate the entire project from a blank workbook to confirm everything is correct. I don’t copy and paste, but instead retype the formulas to make sure everything matches. Quite time intensive indeed, but duplicating my work usually takes a fraction of what it usually takes to create it. In fact a two-week spreadsheet took only an hour to completely rebuild from scratch because I knew exactly what to do. Having rebuilt it, and with everything matching perfectly, I feel confident that my spreadsheet is accidental-error free. Of course, whether my human assumptions are correct is an entirely other issue.

So the next time you find yourself in possession of a breakthrough calculation in Excel, I hope you’ll be a bit more leery that your spreadsheet is likely lying to you in some part. Encourage honest spreadsheets by error checking and proofreading so we can all avoid painful spreadsheet problems.