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

This week’s tip is about the MATCH function and how it can be used to return the relative position of a value within a range. If you are in Sales, you can use the MATCH function to find the position of a sales value within a data range. The MATCH function searches for a specified […]

Continue reading

When you create a table, you can manage and analyze the data in that table, independently of data outside the table. For example, you can filter table columns, add a row for totals, apply table formatting, and publish a table to a server that is running Windows SharePoint Services 3.0 or Microsoft SharePoint Foundation 2010. […]

Continue reading