Vertical Lookup is one of the commonly used MS Excel functions. But it has limitations in that the main search criterion needs to be in the first column. However by using a combination of MATCH and INDEX, you can return values from an array regardless of what information is in the first column of the […]

Continue reading

Looking for a quicker way of highlighting duplicates in a data range? Then look no further than the Highlight Duplicates conditional formatting option. Having duplicates in a data range can lead to disorganization and confusion in your data list. Follow the steps below as we explain how your data can be cleaned up by removing […]

Continue reading

Are you looking for a smarter way of monitoring the performance of Sales Staff?  By using Conditional Formatting one can apply formulae to a range of data. Only the data where the result of the formula is positive will be formatted. In that way it will be easy to monitor the performance of Sales Staff. […]

Continue reading

Ever tried to sum up all the positives/negatives in a column per GL account and per month?  If the task is done manually it will certainly turn out to be boring and repetitive.  However by using the Sumif function this seemingly boring and complex task can be simplified.  Follow our example below as we show […]

Continue reading

Are you planning on calculating commission for the sales staff, but the thought of creating a spreadsheet with complex formulae scares you. We have a solution for you, by nesting the IF and the AND functions one can ensure that the set conditions are met before commission is paid out. Below we explain how commission […]

Continue reading

Have you ever tried to Sum data values based on cell color, but without any success? Perhaps you have allocated colors to values representing cost centers and would like to sum based on the cost centers. Well, by using the Subtotal function and the Data Filter option one can sum data values based on cell […]

Continue reading

Do you struggle to compile a report of Total Sales per Product? If your answer is yes, then the Subtotal Command can be the solution to your problem. The Subtotal Command automatically applies grouping and subtotals into a data list, using the specified summary function. Follow the steps below as we explain how that can […]

Continue reading

As an accountant, do you struggle to easily categorize debtors based on how much they owe your company? If you do, your struggles are over as we have a solution for you. The Conditional formatting – Icon Sets option creates visual effect in your data and helps to see how the value of a cell […]

Continue reading

Have you ever tried to filter data based on multiple and complex criteria yet without any success? Thus you are frustrated that the Excel Autofilter Option is not yielding the desired results. Well, the Advanced Filter Option is the solution to your problem. The Advanced Filter Option can extract data based on user defined criteria […]

Continue reading

Common sense dictates that the End Date of an employment contract should be greater than the Start Date. But alas, one can easily enter the End Date wrongly in an MS Excel data range. To ensure that the End Dates are entered correctly the Data Validation option should be used.  Below we explain how by […]

Continue reading