Tag Archives: Excel 2010

If you are dealing with a colossal spreadsheet which contains lot of functions and formulas, then it would be a dire need to know the precedents and dependents of function/formula. Excel 2010's Tracking Precedents and Tracking Dependents feature would let you know which are the Precedents (on which data field is used for evaluating formula/function) and Dependents (on which data field it depends upon) of the selected cell containing formula or function. This feature interactively tells you from where the function is taking values and how it is dependent by depicting directed arrows towards the concerned fields. Read More

Finding out the rank of the data value from the list is very easy through Excel 2010 inherent RANK function. This function takes three simple arguments and will let you to rank the data in a specified sorting order, i.e, Ascending or Descending. Read More

Unlike other spreadsheet programs, Excel 2010 offers a range of functions to automatically deal with all kinds of date and time formats. The Time function takes plain values and show them in time format, letting users easily performs complex operations over time values. Using TIME function, you can show the values in time format, i.e, hr:min:sec. It requires three arguments to be passed, and automatically determines the military time. Furthermore, it's capable of detecting (AM) Ante Meridian or (PM) Post Meridian. All you need is to specify the data range i.e. the cell location where values are stored or direct values that you want to convert into TIME format. You can also pick values from different locations and convert them in a single field using the time function. As stated earlier, it automatically detects the values greater than 12 and convert them into military time. Read More

Sometimes while filling in cells in spreadsheet, we try to get them filled in much haste, which eventually lead us to irregular word-case in cells. To get it fixed right-away Excel 2010 provides a simple function which show the standardized word-case, i.e, first letter of each word must be kept capital. Through PROPER function you can change the case irregularities in nice looking standard. Read More

Excel 2010 contains many advance mathematical functions which have been extensively used for different calculations, from a list factorial finder is an important one. Excel FACT function which stands for FACTorial is used for finding out the factorial of the specified number. It require a single argument to be passed, along with this Excel 2010 also include a function which could find out factorial of alternate numbers called FACTDOUBLE. Read More

Excel 2010 includes two types of referencing, i.e, Absolute and Relative, these referencing types are very important when you are dealing with formulas and their addresses. If you want to find out the different type of data cell address, then you can easily show it by providing certain parameters. Excel ADDRESS function provide list of options to show address in desired ways; with row/column headers or row/column numbers, with absolute, relative or mixed format, etc. This function is very useful especially when you are dealing with different and large-scale spreadsheets in Excel 2010 workbook. Read More

Excel 2010 provide an easy way to find out the matching value between multiple fields. Through EXACT function you will be able to find out the exact match between two strings, it takes two argument to be matched against each other and return results in logical value either TRUE or FALSE, so you can easily categorize the match field by applying desired functions and formulas over two logical values. The EXACT function is case-sensitive, i.e, it returns TRUE only if case of both strings are same. Read More

Through simple Excel 2010 functions you can easily find out the maximum and minimum value in the spreadsheet. The MAX and MIN functions immediately returns the maximum and minimum values for the specified range. But sometimes there is a need of including logical values along with numeric values to find out max and min value, then MAXA and MINA functions are very helpful. The syntax of theses functions are same and takes only one argument which either be number or location of data range. In this post we will using a simple example to demonstrate the core usage of these functions. Read More

Dealing with different data types in spreadsheet could be very hectic to get by. With simple functions provided by Excel 2010, you can ensure data consistency by applying different conditions over the values. Through INT and FIXED functions we can show the decimal values in any desired place, eventually giving your spreadsheet a nice & tidy look. Read More

Excel 2010 enables user to create database table for performing database functions and applying formulas. Excel 2010 inherited different database functions which lets one tweak data from the specified database, you just need to choose the primary field, and apply a certain condition for pulling out specified content from database. THE DAVERAGE and DSUM functions are used to find out the average and sum of the database values as specified in criteria. Read More

Excel 2010 provides a list of functions that lets you categorize the value types in the spreadsheet. You can use a wide range of built-in function or create custom formulas to categorize your data set into different group to perform further complex operations. Using ISTEXT, ISNUMBER and ISNONTEXT functions, you can easily make categories of data types (text or numeric), and perform more operations over two logical values TRUE or FALSE, as these functions yield TRUE or FALSE against the specified values. These functions take a single argument, which can either be the location of cell where data is residing or direct values. The above mentioned function help users identify the type of data from a large data set. For instance, if you want to apply some formulas on only numeric values in a data set, use ISNUMBER function to quickly check the cell location of all numeric values, and then apply the required formulas or functions over them. In this post, we will categorize numeric and text data of a simple data set. Read More

Excel 2010 provides an extremely useful feature that enables users on network to share and access spreadsheet. Through worksheet sharing, you can take input from other users as well, and let them do analysis and point-out any mistakes you might have done. Excel 2010 enables user to share and access spreadsheets in very convenient way, you just need to enable sharing for the Excel file or place it in network location and other users can view and make changes to it simultaneously while you are working on the spreadsheet. Apart from simple sharing, it also lets you apply required constraints and rules over the spreadsheet. Read More

Excel provides a very simple way to pull out data from databases to perform desired functions over it. It also provides list of functions which can be used exclusively for databases, you just need to create a database or simple table and let Excel know to consider it as one complete database, define some criteria to pull out specified entries. Excel DCOUNT function lets you to count the occurrence of specified data if the criteria is met. Read More

Excel  INFO function is used for showing different kind of information in the spreadsheet. This function encapsulates different commands that shows; Path of directory, Active Worksheets, Origin of first cell, OS version, Recalculation mode, Current Excel Version, and Operating Environment. On writing this function it will automatically shows the list from which you can choose the specific argument to place in, according to the situation. Read More

Excel SUMSQ is very useful function when you need to return the sum of squares of the specified values in spreadsheet, rather than going for manual evaluation. It would be more useful by providing it with location of cells as function arguments where the data is residing, but you can directly put in values as argument to get them evaluated. Read More

Using logical functions provided by Excel 2010, we could apply and evaluate the specific rationale on the data. The most commonly used logical functions are AND, OR, and NOT. They take values as arguments to apply a simple logic over them and yield TRUE and FALSE result. The logical function helps users in situations where they want to categorize the value into groups, so that further complex functions can be easily performed. For example, if you want to check two different values that belong to different fields against a condition, you can use the logical function to check where the required condition is met. This may assist you in filtering out unwanted values and in focusing on only those values that you require. Additionally, Excel 2010 lets you use the multiple logical functions to check required conditions for the data set in one go. In what follows, we will demonstrate the usage of AND & OR logical functions. Read More

Remembering all the functions that Excel 2010 provides is not possible, but you should know all basic functions, so that you can quickly apply them. Excel 2010 offers SUBTOTAL function that provides users with a list of functions that can be used by only entering their function number. It takes the function number of corresponding functions as an argument and location of cells to show you the subtotal of the selected data range. The SUBTOTAL function supports a total of 11 functions including AVERAGE, COUNT, MAX, MIN, SUM, VAR.S, VAR.P etc. This function prevents user from writing different subtotal functions over and over again in their spreadsheets to calculate the subtotal amount of the list. Just enter the corresponding number of the function, and it will show the required subtotal amount. In this post we will be using only four functions from the list to apply them over on the spreadsheet. Read More

Writing dates and time values in spreadsheets are always very tedious because of standards to comply. Excel Date/Time Converter is very useful add-in which lets you convert values into date/time format instantly. It supports multi-languages, so you can also convert dates and time written in other eminent languages. Unlike others, It sports direct integration into Excel. Read More

For sharing out Excel spreadsheets, sometimes you need to hide the main headers to give it more tidy and professional look. In this post we will let you know how to hide the main row and column headers. Read More