What business in the past 20 years hasn’t used Excel? In fact, CIO reports that Microsoft Office (which includes Excel) can be found on some 450 million desktops today. From timesheets to invoices to inventory lists, Excel has been a cornerstone in modern commerce. Excel even permeates our personal lives to organize budgets, track Christmas present lists, and to ensure that we don’t forget a single item when we pack for vacation. Excel spreadsheets are also being used to help non-developers make their own customized business apps. However, there are some things to consider when making your own Excel spreadsheet app; follow these three tips to avoid some of the most common mistakes.
Nothing can get a point across as well as a colorful visual and incorporating the rights charts into a sales presentation can win over potential customers. On the other hand, one that is confusing or misrepresents the facts can negate your whole presentation. If you are making charts for your sales team to use out in the field, it is crucial that they understand every aspect of the chart and the message that you are trying to communicate.
Additionally, field titles are usually written in a type of shorthand since space is limited. Do your employees have a thorough understanding of the information that is required of them? For example, if you have a mobile sales team and you want to track how many customers they are seeing per day and you have the field labeled as “Customers per day”, does this mean individual visits or people seen? One representative may record “1” as in 1 visit while another employee inputs “2” since he shared his presentation with two managers in the same visit. These numbers can greatly impact your data analysis concerning employee evaluations and expectations.
Stuart Leung of Salesforce reported that, “Various studies over the past few years report that 88 percent of all spreadsheets have ‘significant’ errors in them. Even the most carefully crafted spreadsheets contain errors in 1 percent or more of all formula cells. A majority of errors reported were caused by human error—meaning they could have been completely avoidable mistakes.”
So maybe 1 percent doesn’t seem like much… yet. “Illustrating the power of pricing, a study of the Global 1200 found that if companies raised prices by just 1%, their average operating profits would increase by 11%,” explains leading pricing expert Rafi Mohammed. “Using a 1% increase in price, some companies would see even more growth in percentage of profit: Sears, 155%; McKesson, 100%, Tyson, 81%, Land O'Lakes, 58%, Whirlpool, 35%.”
Pertaining to your business app, how much would 1 percent amount to in overtime costs, unpaid accounts receivables, or mispricing your mobile catalog products? Taking the time to accurately input these figures could save you thousands of dollars over the fiscal year.
With Excel users, many times they fall into two categories: those who create one spreadsheet with a ton of information on it versus those who create a variety of pages with limited content. For apps, it works much better if you focus on joining the second group. “As a general rule, most complex workbooks could be simplified by moving each major function or display to a new worksheet in the workbook,” suggests Charley Kyd on ExcelUser.com. “To make each major function easier to find in your workbook, assign each tab of your workbook a relevant name. To do so, double-click on a tab to highlight its original text; then enter your new text.”
When creating an app, the more complex your structure is, the more difficult it is to translate it into a working mobile app. This process works best when you have an orderly table in your main (first) worksheet. Your columns should be clear and include five or more rows of data before you publish. Your table should also have a defined set of columns and corresponding headers. (Unlike the example below!)
Mobile app platforms using Excel spreadsheets have made it easier than ever to create and utilize your own customized app. However, your app is only as good as the accuracy and usability of the information that you put into it. Avoiding these simple mistakes can save you time, money, and a ton of frustration.