Blog

Tip: 3 Steps to Check Excel Workbooks for Errors

Working on a team means that projects are passed from person to person. If one person makes an error, passes it on, and the error is not caught, then the whole project is in jeopardy. When working in Excel with a team, here are three steps to take to help you find mistakes and keep the project moving forward error-free.

1. Unhide All
What can make team projects (like working in Excel workbooks) complicated is that people will use different workflows. Errors passed on by not understanding these different workflows can shipwreck a project. To view a different user’s workflow, or essentially check their work, you will want to unhide everything. You can unhide an Excel sheet by right-clicking any tab and then selecting Unhide. Or, if you wish to unhide columns and rows in a sheet, then you will select the entire sheet and click on cell selector. From here, right-click any column header and choose Unhide.

2. Remove Hidden Formats
Excel has a Hidden format protection scheme in the Formula bar that is able to hide formulas while keeping the cell visible. You will want to check on these formulas and look for any funny business. To do this, you will unhide the cells by unprotecting the sheet to view the formulas. After you have checked your team member’s work, you will want to go ahead and hide the formulas once again.

One bad practice that you will want to be aware of is how some users may hide their formulas by making the text of the formula the same color as the cell background. You can check for this by selecting the entire sheet and then applying a new font color that contrasts the background; be sure to hit the Undo button when you are done making edits. You will then want to politely pass on to your team member guilty of doing this, that hiding formulas with font colors is the wrong way to go about it.

3. Scrutinize the Formulas
With everything unhidden, you will now search your Excel workbook for inconsistencies. You will do this by comparing sum values of rows and columns with known totals. For example, if column C is the sum value of rows 1-6, and a formula looks out of place in C6, then you will want to find out why. Remember to also check the formulas below the data range; all contiguous formulas should be similar in purpose.

Just like algebra class back in school, you will want to check your partners work in order to know if they are using correct math. Do this by checking their work against totals you know are correct. For example, take a total of an invoice that is displayed in the workbook and add up the invoice on your own, outside of the project. If your totals match the totals you inherited then you should be good to go.

After an Excel workbook is handed off to you, if you take the time to check for inconsistencies, then you will be able to save your team and your company the headache and money that comes from moving a weak project forward