Based on 10 Years of Research by Three Noted Professors at Oregon State University

Corvallis, OR – RedRover Software CEO Joe Tanous today announced the May 21^st 2008 launch of the company’s AUDIT spreadsheet error-finding solution. AUDIT was developed for use by Fortune 1000 businesses seeking to reduce risk, increase compliance and avoid the kinds of critical spreadsheet errors that have made worldwide headlines in recent months.

“Audit is designed to help companies control the internal and external risks associated with spreadsheet errors, whether those errors exist through unintentional mistakes or through deliberate
fraud.” said Tanous.

“This is an important milestone in the development of RedRover Software as it is our first major application to come out of a long term BETA program and be announced for General Availability to corporate users. Among our launch customers are a major San Francisco Bay Area bank and a national financial services consulting firm,” said Tanous

Internal Auditors, Financial Managers, and Excel^® spreadsheet developers who have important responsibility inside their employer companies for risk management, regulatory compliance and other fiduciary matters rely on RedRover Software’s Audit to help find and fix errors in complex spreadsheet workbooks.Unlike other error detection or compliance software tools, RedRover’ s technology presents users with a highly intuitive visual “heat map” spreadsheet overlay that depicts potential problems by likelihood of risk, and provides point-and-click navigation to find and fix errors. More information about the company and its solutions may be found at www.redroversoftware.com.

Following completion of more than ten years of spreadsheet and user behavior research, Oregon State University Professors Margaret Burnett, Martin Erwig, and Gregg Rothermel recognized an extensive and largely untapped market for spreadsheet error detection tools. Together they co-founded RedRover Software to develop and market productivity solutions for corporate spreadsheet users.

RedRover Software is headquartered in Corvallis, OR with sales offices near Silicon Valley in Northern California. Angel funded, the company is actively raising venture capital to grow its sales and marketing effort to match the very large opportunity it believes exists for its software offering.

So, lets get the numbers out of the way:

Excel 2003: Max Rows: 65,536 Max Columns: 256

Excel 2007: Max Rows: 1,048,576 Max Columns: 16,384

That makes 16,777,216 cells per page in Excel 2003. This is increased to a whopping 17,179,869,184 (about 17 billion). So, we can fit 1,024 sheets of Excel 2003 data into one sheet of Excel 2007. Although at first one may think that it is good to have that much room to work. Fitting all of your data on one sheet may sound fantastic. But I’m sure that there are those of you out there who remember not having much room to work in, and the benefits of that restriction.

When there isn’t much room, we must always ask ourselves if we are approaching a problem the right way. Are we not cleaning up after ourselves? When everything fits in a smaller space, we can notice when things change. When these limitations are removed, we are not forced to keep things tidy. When there is old data, we can leave it where it was, and start the new data next to the old. That way we can reference the old data. When we are trying to design that Array Formula just so, we can leave old copies around so that we know what not to do, right? That’s a trap that is just too easy to fall into. There is a need to keep the data in check. To throw out the old data. Save it in an old workbook. Start fresh, and work carefully. But enough of the soap box.

Imagine now, a screen of about 50 by 20, we can look at 1000 cells at time, with the normal zoom. That means, without some tool to help you, to look at the whole sheet you would have to look through 17 million screens of data. Don’t even think about printing it out! Imagine trying to read a book with 17 million pages. I know that I wouldn’t even try to read that one cover to cover.

While you and I both know that much of that (we hope) is blank, but without a tool to show you where to look, it may still take weeks to look at the non-blank sections.

So, consider using a good tool such as RedRover Audit, or RedRover Detect. These tools help you to understand your spreadsheets, save you time reviewing and navigating your spreadsheets, and increase your confidence in your spreadsheets before you share them.

Posted by: Dan Vega, Software Engineer, RedRover Software Inc

What I’m specifically thinking about is clearing the contents of a cell. First off, I’ll tell you that the correct way to do this is to select the cell and press the delete key. This will clear the contents of the cell and won’t leave you any surprises in the future. Now let me tell you the way that I tend to clear the contents of a cell. My fingers like to press the spacebar. That’s right, I select the cell and hit space. This replaces the contents of the cell with a space. Then, when you click away from that cell, it looks just like a blank cell.

So what’s the big deal, you ask? Well, it turns out that a blank cell (empty from delete) and a cell with a space are treated differently by functions in Excel. I know that there are those you might expect, =CountBlank() for example, but there are also sneakier ones. Just a little bit sneakier is the =if(test, True, False) function. If, as the test expression, you reference a cell with a space in it say D4, =if(D4, True, False) , you get #VALUE, but, if you have nothing in D4, you get False.

There are more examples of this where instead of an error message, Excel just keeps on going. Next week, we’ll take a look at an example of =HLOOKUP() playing tricks with cells with spaces versus cells which are blank. It should be fun.

See you then, and remember. Delete clears the contents of the cell, and cells with spaces will lead to errors later.

Posted by: Dan Vega, Software Engineer, RedRover Software Inc

1. Make it read left to right, top to bottom
2. Use headers to make it clear what each cell means
3. Pull constants into their own cells
4. Don’t nest formulas too deep, use separate and meaningful operations
5. Try to group formulas in contiguous blocks

Left to right, top to bottom

Most people already build spreadsheets partially following this guideline, simply because it makes it very easy for someone used to reading left to right to read the spreadsheet. Readability is a key consideration, not just for error checking, but also for whoever is the end user of the spreadsheet. Additionally, knowing that a formula is supposed to reference things to the left and above it makes the X in Audit all the more powerful. What you can do is place an X on a cell somewhere in the middle of a large spreadsheet constructed this way and see, very easily, if that cell conforms to the design.

For example, in the image above I’ve placed an X on a cell to investigate it. Because I know this section is supposed to read left to right, I know that the highlighted cells that feed into it are supposed to be behind the cell I’m investigating. Seeing this will prompt me to go through and figure out what is going on when I see behavior like in the image below:

Use consistent and descriptive labels

A spreadsheet, like most complicated models, wouldn’t make much sense without some language describing what is going on; this isn’t news. The difficulty comes when spreadsheets get a little more complex and formulas need to get pulled out into separate sheets. Once that starts happening, the intermediate steps aren’t going to be examined by anyone other than the creator, or at least, that’s very tempting to believe.

The problem doesn’t come up when the sheet is being created though, it comes up down the road, when you’ve inherited a sheet from someone else and you have to change something about the way the model works. Without reasonable labels above columns and on sheets, it’s almost impossible to update a spreadsheet without making a mistake.

Put constants in their own cells

Consider the following:

I have a number, in euros, for the cost of a part (10,000) in a budget I’m assembling, and everything else is in dollars. That’s fine though, I have Google handy and according to Google, one euro is worth 1.5513 U.S. dollars. Great. So I setup a little formula in a nearby cell converting the machine like this: =A3*1.5513. Having fixed my unit problem I move on and finish my spreadsheet. Time passes.

It’s a year or two later and I want to bring this old budget up and use it for a new project, and as I do it I’m lucky and I at least notice in one cell where I have the magic number 1.5513 times some value. That’s great, but a year out I’m not likely to recall what that number was for because it wasn’t labeled. Moreover, even if I’m very clever and do know what I was doing in the past, its still going to be very difficult to find the other four or five places in the spreadsheet where I did this, and then update their values to the new exchange rate.

So, as you’re building a spreadsheet and you have a value embedded in a formula its worth considering whether the value is apt to change and whether or not its clear what the formula is doing, especially when you come back to look at it a year later.

Separate formulas into multiple steps

I’ve read articles that suggest you do just the opposite of this, so clearly there is some debate about whether it’s better to have fewer, but more complex formulas, or more, simpler formulas. I’ve taken to trying for the latter, especially in conjunction with Audit for the following reasons:

1. With the cell highlighting in Audit, it’s easy to visualize how your spreadsheet works, even if the highlighting goes back over several different cells.
2. If you’re approving regions in your spreadsheet, you can track your progress as you get each simple formula approved, rather than having to test the one very complex formula and risk losing your place.
3. Excel’s formula bar is not a very friendly way to read a formula, especially as they get long. Why strain your eyes if you don’t have to?
4. If you separate out complex formulas and use good header names, your spreadsheet becomes self documenting, meaning that it’s very easy to read through and see what all of the steps in a calculation really are.

Put formulas in contiguous blocks

The more I use Audit, the more I come to rely on regions to see how my spreadsheet works, and as a result I’ve taken to constructing my spreadsheets to try and create fewer regions. The more regions in a spreadsheet, the more complex it is, and the more difficult it is to audit, so by slimming down the structure I can save a lot of time and improve my confidence in the model.

Take a look at the following example:

In the image above I have the yearly data divided up with spacing between columns. That may improve readability, but it also breaks apart my regions. What I’ve taken to doing now is removing those spaces and re-adding them only if I actually need them.

Another trick for reducing the number of regions is to be aware of how Audit builds them. Specifically, Audit will try and make vertical regions rather than horizontal regions, so if I have a large square of identical formulas Audit will create a series of vertical regions. Most of the time this makes a lot of sense, but in some applications you’ll create a spreadsheet that looks like this:

Rather than having a bunch of two-cell vertical regions, I’d rather have to long horizontal regions. One easy way to fix that is to insert a row between the regions, forcing them apart and making Audit draw the regions horizontally.

Posted by: Erik Rehn, Product Manager, RedRover Software Inc

“Who’s Your Auditor’s Boss?”, CFO Magazine, February 29, 2008.

“Telling Auditors How to Audit Themselves,” CFO.com, February 25, 2008

New Auditing Tool for Microsoft Excel Is First to Visually Display Probable Spreadsheet Errors with Point-and-Click Error Correction and Audit Documentation Trails

Corvallis, Ore. (Business Wire) - February 27, 2008 - RedRover Software Inc, a provider of productivity tools for spreadsheet quality control, announced today that its flagship product, RedRover Audit, is now available for purchase from www.redroversoftware.com. RedRover Audit is a powerful yet simple-to-use spreadsheet auditing tool that operates as a complement to Microsoft Excel. RedRover Audit is designed to give those who review or certify Excel spreadsheets the confidence that formulas, data and logic are accurate.

“Companies spend thousands of dollars and hours each year to audit their financial and operational spreadsheets. Unfortunately, these costs are rising because of the widespread ramifications spreadsheet errors can have in terms of lost revenue or harm to a company’s reputation,” said Matthew Johnen, chief executive officer of RedRover Software Inc. “RedRover Audit renews confidence in spreadsheet content. This powerful and intuitive spreadsheet auditing tool reduces risk and ensures better compliance with corporate policies or regulatory requirements.”

Key features of RedRover Audit:

• Visually depicts spreadsheet by regions, making it easier to understand how a spreadsheet is constructed;
• Presents a highly intuitive visual “heat map” overlay which shows potential problems by likelihood of risk;
• Makes navigation easy through multiple worksheets and workbooks;
• Pinpoints inconsistencies by examining underlying data and antecedents, not just formulas;
• Allows groups to work together to track and review approvals cell-by-cell for improved audit compliance.

About RedRover Audit - Confident Accurate Spreadsheets

RedRover Audit is a productivity tool that runs on the desktop as a complement to Microsoft Excel. Designed for those who build, review, or certify Excel spreadsheets, RedRover Audit visually guides reviewers through a structured process or audit, tracks progress cell by cell, and enables point-and-click navigation to flagged cells. Based on ten years of algorithmic and Excel usage research, RedRover Audit uses sophisticated logic that is completely independent of Excel and results in higher-level functionality and accuracy.

“As a CPA, the way RedRover Audit visually highlights cells is extremely helpful for a quick review of a spreadsheet,” said Elizabeth Schellberg, principal at Tatum LLC. “While many formulas I review are not errors, per se, they are often inconsistent. By seeing and correcting inconsistencies, I can create workbooks that avoid errors from top to bottom.”

Pricing, Availability and System Requirements

RedRover Audit is available today for $695 by visiting www.redroversoftware.com. A free 30-day trial version is also available. The minimum system requirements to run Audit 1.0 are: Microsoft Office Excel 2003 and above; Microsoft Windows XP SP2 and above; 1.8 GHz CPU; and 512 MB RAM.

About RedRover Software Inc

RedRover Software develops productivity solutions for the Excel®-dominated spreadsheet market. Designed for all levels of Excel users, from Fortune 1000 organizations to small businesses, RedRover products build confidence in spreadsheets while reducing the costs associated with auditing and error detection. Unlike other error detection or compliance software products, RedRover’s technology presents users with a highly intuitive visual “heat map” overlay that depicts potential problems by likelihood of risk, and provides easy-to-use navigation to find and fix errors. RedRover products are a seamless complement to Excel and allow users to quickly and easily review complex, multi-sheet workbooks in real time while retaining the integrity of each spreadsheet. RedRover’s flagship product, RedRover Audit, is applauded for its superior ability to examine underlying data and antecedents, not just formulas. Headquartered in Oregon, RedRover Software is a privately held company with an office in the Silicon Valley. For more information or to download an evaluation copy, visit www.redroversoftware.com.

RedRover Audit Launch Press Release (.pdf)

PR Contact: Jill Dopp, Market4Demand, Inc.
Phone: (303) 256-9097
Email: jill@market4demand.com

Corvallis, Ore. - A start-up software company based on technology developed over a ten-year period by computer scientists at Oregon State University today announced it has launched its first commercial product.

RedRover^TM Software, Inc. was officially founded in 2006 by OSU professors Margaret Burnett, Martin Erwig and Gregg Rothermel, who were searching for ways to build confidence among spreadsheet users and reduce the costs associated with auditing and error detection of spreadsheets. The company incubated in OSU’s new Kelley Engineering Center for almost a year.

The new technology addresses the need to reduce spreadsheets errors, which cost businesses and individuals millions of dollars annually. According to the European Spreadsheet Risks Interest Group (EUSPRIG), which maintains a list of publicized, high-impact spreadsheet errors, a single “cut and paste” error cost Trans Alta Corporation $24 million in profits and a $655 million budget error at NASA.

Errors are easily introduced by users when building spreadsheets and are difficult to detect, especially for large workbooks. Finding and correcting spreadsheet errors also represents a major cost to businesses in terms of personnel time and auditing expense.

“Programs like Excel make it very easy for people to create spreadsheets, however, this same accessibility and ease-of-use make it difficult for companies to impose quality controls on spreadsheets,” said Erwig. “RedRover’s tools help users find errors in their spreadsheets. These tools are based on analysis techniques that have been employed successfully in tools for professional programmers to detect and remove errors from software.”

RedRover’s flagship product, RedRover Audit, is designed for people who build, review, or certify Microsoft Excel spreadsheets. It runs on the desktop as a complement to Excel, visually guiding users, tracking progress cell by cell, and enabling point-and-click navigation to flagged cells.

Starting in 1995, the OSU faculty members became intrigued by the idea that spreadsheets are programs - and spreadsheet software, such as Excel, are actually programming systems ­- yet most spreadsheet users have not been trained as programmers. So the professors decided to bring software quality control concepts into the spreadsheet domain. By studying actual human behavior, they developed tools to enable average users to build and use spreadsheets with greater confidence and accuracy.

After more than ten years of spreadsheet and user behavior research, the trio co-founded RedRover Software, which operated for almost a year in the Kelley Engineering Center, a 153,000-sq-ft. building designed to foster new company spinouts based on OSU research.

“This is exactly what we hoped would happen in the Kelley Engineering Center,” said Ron Adams, dean of engineering at OSU. “The entire building is constructed to encourage communication and collaboration, which leads to new ideas, innovation, and ultimately results in new companies, like RedRover. We’re thrilled the company is well on its way, and their first product has the potential to help millions of spreadsheet users.”

RedRover Software outgrew their on-campus incubator space and relocated to an off-campus office building, where they now retain 23 people, seven of whom hold engineering degrees from OSU.

“The drive and ingenuity found in OSU engineering grads is a rare find,” said RedRover CTO and Vice President of Engineering Matt McLaughlin, who received his bachelor’s and master’s degrees in computer science from OSU. “Oregon State engineers have been key innovators in the success of RedRover technology. We will continue to actively recruit the top talent from OSU.”

About the OSU College of Engineering: The OSU College of Engineering is among the nation’s largest and most productive engineering programs. In the past six years, the College has more than doubled its research expenditures to $27.5 million by emphasizing highly collaborative research that solves global problems, spins out new companies, and produces opportunity for students through hands-on learning.

OSU RedRover Audit Press Release (.pdf)

Media Contact: Gregg Kleiner, 541-737-9684
Sources: Ron Adams, 541-737-3101; Matt McLaughlin, 541-753-1777

Method 1 - Hidden Rows/Columns

This is probably the first cell hiding method most people learn. After a while the slightly thicker column breaks are obvious, and while a clever designer might use protection to keep their hidden things hidden, Excel’s built in protection is weak at best.

Method 2 - White-on-white

Another very common method for hiding data is using matching foreground and background colors. Click on a cell and change the text color to white, and there you go, we’ve got an invisible cell, at least sort of. Certainly, Excel add-ins and macros can still see it:

It is also fairly easy to turn off. Now you can get a little cleverer here–black on black, red on red–it’s somewhat less obvious. If you play with conditional formatting, you’ll find it’s even better. With normal formatting, the toolbar to fix it is right there staring you in the face, but with conditional formatting, you might not even know its there. What’s more, you can click all you want on that black foreground option, and the text will still stay white until someone actually goes digging into file menus and disables the conditional formatting.

Method 3 - Graphs and Images

Rather than changing text colors or hiding entire rows, another option is to just stick something right on top of the value you want to hide. You can use images, graphs, embedded Visio documents, or any of the other strange things Excel will let you insert into a sheet. Once it’s covered up many people won’t even think to look. Interestingly, Audit’s overlay draws right on top of these objects, so it’s hard to use them to hide anything from someone using it:

Method 4 - Hidden and “Very Hidden” Sheets

Rather than hide a row or column, it’s also possible to hide an entire sheet in Excel. One advantage to this method is that unless someone is looking at each and every formula, it will be easy for them to miss the reference that leads off to the other sheet. Using Excel alone, the only way someone is going to notice those hidden sheets is by going into the menus and selecting the unhide sheet option. To get around that you can create a “very hidden” sheet with VBA. Once you’ve done that, the sheet won’t show up in Excel’s list. Audit, on the other hand, ignores this property, so you can still see a list of every sheet in the Regions Navigator–hidden, “very hidden” or whatever.

Method 5 - Hidden Formulas

While this method doesn’t hide the formula, it hides the logic behind the formula which is often just as good, especially in conjunction with a hidden sheet or one of the other methods above. To hide the formula, just go into Format Cells | Protection and you can check the ‘Hidden’ box. Then turn on protection and you’ll see that you can’t get the underlying formula for that cell. This, unlike the other methods, does hide things from programs, so if you’re using Audit on a spreadsheet you’ll want to check and see if protection is on and turn it off if you can.

Method 6 - Custom Format

This is what I’ve been looking at recently, and if you’ve ever played around with custom formats you can find some extremely strange behavior. The easiest way to hide something is go into the custom format option in the Format Cells dialog and type in three semicolons (;;;) and you can see that the cell doesn’t display anymore. That’s interesting, but possibly more interesting is what happens if you type in a number, or some words:

“Foo”, is in this case, 5. It’s a number, it behaves like a number in the sum below, but it shows up as something else entirely. It’s easy to see how you could create a column heading that was a number, and then overextend a formula below to grab it, and no one would ever suspect a thing.

Posted by: Erik Rehn, Product Manager, RedRover Software Inc

View other blog posts »