Introduction: Data Validation is meant to ensure that user inputs correct type of information in the cell. The Data Validation can be achieved in the form of “Drop-down Lists” that make sure that user can only choose from a given set of values or using options like input being whole number, a decimal value, a date or something defined by a formula. The data validation menu … [Read more...]
Choosing between Vlookup and Index-Match formulas
Introduction INDEX()+MATCH() and VLOOKUP(), both have their proponents and opponents. Both of these formulas perform almost the same function. Both have pros and cons. In today’s tutorial we will try to compare their usage and that how they can be used to get most out of them, when we should drop one and use another. A comparison of syntax for the two formulas: Index-Match … [Read more...]
Exploring Excel’s Internal Audit for Formulas
Introduction Formulas are very common element of a spreadsheet. Smaller sheets tend to have relatively small number of formulas where larger sheets may span over many worksheets. The formulas in such large sheets are not easy to understand. With Excel's Internal Audit for formulas, we have a convenient way to do internal audit for formulas and for a tool that can make audit … [Read more...]
Accounts Receivable Aging – Managing it with MS Excel
Whenever a firm is done with its sales target, the next thing in the queue is how long it will take to get the receivables from the customers. Accounts Receivable and hence Accounts Receivable Aging is important because they form an important part of the Cash Conversion Cycle for a company and is critical to maintain the liquidity of the company as well as to avoid cost of … [Read more...]
Managing attendance with Excel zoom’s Attendance Template
Managing attendance of staff is a very critical issue, be it a manufacturing firm or a service firm. There are several tools out in the market to manage this function. This ranges from the simple spreadsheets to ERP system that is very complicated and are extremely reliable. We at Excel zoom have produced a simple template to monitor the attendance of employees. This is a MS … [Read more...]