Finding errors while proof reading an Excel file can feel like finding a needle in a haystack. Frequently it ends up being a task that doesn’t get completed on time (like writing a blog post for our editor, Jonathan) or doesn’t get the attention that it deserves.
Less-experienced proofreaders will often resort to simply spot-checking a few important numbers to ensure that the file doesn’t contain any egregious errors. While this technique works for smaller files and simple reports, it will usually fall short for more complicated files and troubleshooting. This post will cover some intermediate techniques that you can employ while proofing larger files, building reports, or troubleshooting broken files.
Data Sources and Sums
Abraham Lincoln is famously quoted as telling the nation “not to trust anything they read” (on the internet). Making yourself look bad by citing data that isn’t accurate was a big problem back then, and it’s a big problem today. Excel isn’t immune to this timeless mistake either.
Set yourself up for success by establishing agreement between the tools that provide your data (like AdWords), your back-end data in Excel, and the dashboard in Excel. A common mistake is to only QA the data that appears on your report’s dashboard, or to trust that the backend data that you depend on is accurate.
Spot-checking the data on your dashboard without verifying that it has accounted for all of the data on your back-end data sheet has the potential to cause catastrophic results (especially if this happens in a budget-pacing document). A quick look at the total spend and conversions from your back-end data can eliminate this problem. Simply click on the columns that contain that data and compare the autosum numbers from Excel with the data on your dashboard.
Similar issues can occur between the data that you get from your tools and the data that we have in the back-end data sheets in Excel. Clients with latency issues with conversions are likely to be well aware of this. The data that you pulled from your tools two weeks ago may not agree with the data that they would provide you with if you re-pulled the same report today. Fraudulent clicks and latent conversions are the two most likely causes for these types of data issues.
Once we’ve ensured that we’ve accounted for all of the correct data from our tools, and have incorporated all of that data into our dashboards correctly, then we’re ready to dive into Excel a little more.
The Formulas Tab
The Formulas ribbon in Excel provides us with a few tools that can assist while proofreading larger reports and troubleshooting tricky workbooks. Let’s take a look at a few of the more useful functions that you can find there.
We’ll get started with the Show Formulas button. This button will cause Excel to display the content (formulas) of a cell, instead of the values that those formulas resolved to. So each cell will have an appearance that looks more like what you would see in the formula bar if you edited a cell, instead of the values that you’re accustomed to seeing in the spreadsheet itself.
For extra credit you can get in the habit of toggling between these two views with the simple keyboard shortcut Ctrl + ~.
Commonly when we identify a single formula that we want to inspect, we’ll be interested in understanding what data that formula uses. Clicking on the formula bar will highlight the data that is being used on the current worksheet. An advantage to this is that you get a color-coded representation of which data is being used by each parameter within your formula. This technique is effective when troubleshooting a formula that isn’t working correctly.
Clicking on the Trace Precedents button while a cell is selected will do a similar thing. This will cause arrows to show up on your worksheet that demonstrate which cells the formula is dependent upon. This technique has a few differences that can make it preferable sometimes. First of all, the arrows will continue to be displayed until you clear them (Remove Arrows button). This can often be useful when you’re trying to make sense of how a document is put together, and it’s also useful if you want to analyze multiple formulas at the same time (something you cannot do with the previous technique).
Example of using the formula bar or Trace Precedents while the Total Conversions cell was selected:
An even more useful benefit of the Trace Precedents button is that it will alert you when your formula is using cells that are contained on another worksheet. This has a way of being useful with larger formulas where you may be interested in itemizing all of the data being used before you dive into the formula itself.
Icon indicating that this formula is dependent upon data that is contained on another worksheet:
Trace Dependents is right below Trace Precedents, and it can also help you out tremendously when you are performing editing and proofreading at the same time. Trace Dependents will tell you which other cells within your workbook are dependent upon the cell that you have currently selected. The two best uses for this button are to gain confidence before deleting data from a worksheet and to establish the scope of a problem if an individual formula stops working.
Deleting cells that are being used elsewhere can cause obvious problems. This technique will ensure that anything that you are considering deleting is not being used elsewhere, or at least if it is you will have a chance to replace that functionality before you start deleting.
Another closely related use for this button is determining the scope of an issue when an individual formula stops working. If we do have a cell with a broken formula, we can determine if that is causing issues anywhere else by clicking on this button. Excel will then show us with arrows where this data is being used.
Trace Dependents demonstrating that the data for March is being used in the grand total:
Using these techniques, you can: 1) reduce the amount of time you spend proofreading; 2)increase your accuracy by using these techniques to ensure that you are correctly accounting for all of the right data; 3) then take a closer look at the actual formulas within your workbook.