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

Human Resource Managers can benefit from this week’s tip. The YEARFRAC function can help with calculating the current life span of an asset, or the age of employees based upon their dates of birth. The YEARFRAC function calculates an accurate difference between two dates because unlike other methods that return a whole number, the YEARFRAC […]

Continue reading

Consolidating budget allocations for different departments which are on separate worksheets can be a tedious task, unless you automate it. 3-D Reference is a Microsoft® Excel® function that is useful for referencing several worksheets that follow the same pattern and cells and contain the same type of data. In this week’s tip we will show […]

Continue reading

Let’s say you have imported a list of names into Microsoft® Excel® from a payroll program, and the first names and surnames are all added to one column of data but in order to manipulate the data you would like to have the surnames extracted to a separate column. You can do this using various […]

Continue reading

Naming formulas in a Microsoft® Excel® workbook makes it easier to maintain them and improves clarity and understanding of the respective data. You can define a name for a cell range, function, constant, or table. Names can easily be updated, audited, and managed once they are defined. In this tip you will learn how to […]

Continue reading

Let’s say you want to count the number of employees in a data range and would like to ignore any blank cells. The way to go is to use the COUNTA function. The COUNTA function counts cells containing any type of information, including error values and empty text (“”). For example, if the range contains […]

Continue reading

As a Sales Manager, a report which allows you to see how sales perform on different days of the week could be useful to you; enabling you to make informed business decisions on weekly stock orders. The TEXT function converts a numeric value to text, so you can set dates to display as text to make […]

Continue reading

Data duplication in Microsoft® Excel® can lead to a host of irregularities; so it is a good idea to prevent it. In this tip we explain how you can prevent duplicate data from being captured in an Excel worksheet by using custom data validation. The COUNTIF function will be added to the Data Validation option […]

Continue reading