In last week’s tip we explained how, by using the LARGE function, you can extract the greatest value from a given range of values. For example, a Debtors Clerk can extract the top five largest outstanding balances from a range of outstanding balances to a separate worksheet or a new set of rows. This week, […]

Continue reading

If you would like to see a specific set of values within a given range. For example, if you are a Debtor’s Clerk and want to see what your top five outstanding balances are you can use an Array Formula.  An Array Formula is a formula that can perform multiple calculations on one or more […]

Continue reading

Sometimes, understanding how a figure is calculated is difficult because the formula seems quite complicated; comprising of several intermediate calculations and logical tests. Luckily Microsoft® Excel® has a powerful auditing tool, Evaluate Formula, which will help you to unravel the different parts of a formula, in the order that it is calculated, to understand how […]

Continue reading

If you need to calculate how often a value occurs within a range of values, you can use the FREQUENCY function.  For example if you are a Sales Manager you might need to determine how often a sales figure occurs within an interval or bracket to determine the most frequent or least frequent sales figures […]

Continue reading

When a formula  refers back to its own cell, either directly or indirectly, it creates a circular reference.  A circular reference can have a significant impact on performance because it can iterate indefinitely.  Iteration is the repeated recalculation of a worksheet until a specific numeric condition is met.  By default, iterative calculations are turned off […]

Continue reading

 We’re back into full swing, we hope you are too! Here’s your first tip for this year: When you want to print certain rows/columns of data in a worksheet, you will most likely hide the other rows/columns, however when you want to unhide cells in hidden rows/columns it can be hard to locate them. By […]

Continue reading

Happy New Year! We hope that you had a fantastic festive season! Before we get into this year’s Microsoft® Excel® tips and tricks, we’ve compiled a list of our five most popular ones in 2013. In total, these tips received over 14 000 views – thank you for taking the time to read and make use […]

Continue reading

When cells are not visible on a worksheet, you can watch those cells and their formulas in the Watch Window. The Watch Window makes it convenient to inspect, audit, or confirm formula calculations and results in large worksheets. By using the Watch Window, you don’t need to repeatedly scroll or go to different parts of […]

Continue reading

Data validation is a Microsoft® Excel® feature that you can use to define restrictions on what data can or should be entered in a cell. In this tip we show you how to use custom data validation to ensure that other people enter the correct type of data in cells, and that they are notified […]

Continue reading

We have previously covered the INDEX and MATCH functions; in this tip we revisit these functions but with a different example. The INDEX and MATCH functions can be used as an alternative to the VLOOKUP function and has the added advantage of looking up values to the left of the lookup column/value. With the VLOOKUP […]

Continue reading