Excel University Sparkline
A newsletter designed to help you learn Excel and work faster
Excel University Newsletter Vol 2 Issue 4 © excel-university.com - all rights reserved
Message from Jeff
Errors in spreadsheets are indeed a problem. Since Excel is so flexible by default, it allows anyone to type anything into any cell at any time. While this creates an environment that allows us to build just about anything we can imagine, it also means it is easy to accidentally introduce an error. Something as simple as typing a value into a formula cell can break the workbook.
One of my favorite managers of all time, Mike G, taught me to carefully review each number in every deliverable before sending it to management. He said, basically, if you ever give them a report that contains an error, your reputation will be tarnished and it will be hard for them to fully trust your future reports. It sounds extreme, but he was right.
The good news is that Excel has many built-in features that help to prevent or detect errors. They include things like data validation, worksheet protection, conditional formatting, and locked cells. In addition to such built-in features, good workbook design and user skills can dramatically reduce the likelihood that errors make their way into a final report.
Things like writing reliable formulas, splitting data from the report, automation, PivotTables, using the correct cell reference styles, anticipating errors, selecting optimal functions, using Tables, eliminating helper columns, using Power Query, and creating an Error Check worksheet will all help to minimize or eliminate errors. For the Excel Topic this week, I'll share an article that shows specifically how I like to set up my Error Check worksheets.
Excel Topic of the Week
How do you know your workbook is accurate? This question is important because it’s our responsibility to ensure that data flows properly through the workbook, with numbers that tie out, and with reports that are internally consistent.
One way to help ensure workbook accuracy is by creating a worksheet within the workbook dedicated to this task, and one that will help you perform your review more quickly.
In my workbooks, I call this error check worksheet ErrorCk.
I wrote an article for the California Society of CPAs called Put Errors in Check that demonstrates how to set up such an Error Check worksheet.
>>> Learn more now by reading my Put Errors in Check article.
I'd like to highlight one of our students. Chris is a CPA and controller from Ohio.
One of the things he loved learning about was Tables. (They are an easy way to improve efficiency and reduce errors.)
He said he appreciated learning about "a lot of the menu items which you don't realize are there." He appreciated that the training revealed many such features.
He also loved learning several ways to accomplish specific tasks. He said this "really comes in handy when you get stuck and realize maybe this isn't the best tool for the job ... but remember another way to do it." By knowing multiple ways to accomplish any given task, you are able to pick the most appropriate (efficient, reliable) method based on the workbook.
Congratulations Chris on completing the training program ... way to go!
This week, I challenge you to find the error I've hidden in the workbook.
Once you do, check out the ErrorCk worksheet and notice how it will help you more quickly detect that an error exists.
I hope this challenge shows you hands-on how to set up an ErrorCk worksheet and why.
Select direct precedents for the active formula cell:
- Windows: Ctrl [
- Mac: ⌃ [
When using Excel, do NOT be afraid to make mistakes. Get in there and try stuff out. This is how we learn and grow. The key is to detect and correct worksheet errors BEFORE you deliver the report. This quote says it well:
"Mistakes are a fact of life. It is the response to the error that counts."
As I've learned and used Excel over the years, I've probably made every mistake possible. But these were important to my understanding of Excel. If you refuse to risk making a mistake, then you'll make little forward progress with Excel. So, don't hold back. Experiment with features. Write and re-write formulas. And make mistakes. Just be sure to detect and correct them before delivering the final report 🙂