Bright Hub has excellent articles which teach you how to use a number of different Excel Functions, in detail, with examples. Once you learn the function, it is yours to manipulate your data, pulling information from your table that you may not have even realized you could find.
Excel functions also allow you to format how your data appears to the end user, and a number of them let you make formatting changes to regularize the information, because sometimes it is entered by more than one person, each of whom may have an idiosyncrasy in the way they fill in a field.
Many people start to reference cells with the function LOOKUP, but there are a number of other Excel functions which refer you to specific cells. Bright Hub has an article explaining the various LOOKUP and other reference functions, to help you find the data you need. As well as LOOKUP, you can use VLookup and HLOOKUP, so the article will help you decide if INDEX or MATCH may be more functional.
While using a table in Excel, you often need to use the COUNT function. However, there are a number of different COUNT functions which can make your life easier by focusing on how you want your data counted, and what you intend to do with it. In Bright Hub’s article Excel COUNT Function Variations, the difference between COUNT and DCOUNT, COUNTBLANK and COUNTIF is explained, and we refer you to specific articles on each function if you need more information.
Using the AVERAGE functions
AVERAGE is a useful function for finding the mean of a range of numbers. However, there are the additional options of using DAVERAGE and AVERAGEA. Bright Hub explains what the differences are, and when you will want to choose one over another to accomplish your task, in the article Excel AVERAGE Function Variations.
Functions for manipulating TEXT
Our spreadsheets contain a great deal of information, but captured in a table, with rows and rows of numbers, or columns of records, the meaning can be less than clear. With the use of TEXT functions, you can make the output of information more usable for the viewer because you have entered numbers within a text string, and explain what the numbers mean in the sentence formed with the text string and your values.
There are other TEXT functions which you may also find useful. If you need to format text in a field, you may want to change the text to all caps, or all lowercase letters. Or you may want the text expressed as a PROPER name. Proper, along with UPPER and LOWER, are the tools in the function box to do your text formatting. In the article Excel Text Function Variations, you can find out how to use these functions, and your text will look as you planned, not dependent on how someone else decided to enter the records.
Switching data from rows to columns
Sometimes you are given a spreadsheet created by someone else, and the format of the data is very awkward to work with. Excel has a function that can help you change the way the data is presented. This is TRANSPOSE, which enables you to take data in rows, and put it in columns, and the reverse. The article also explains the precise syntax you need to make sure you change the whole array, rather than a single cell, which would not have too much useful effect.
Whether you are making a calendar for scheduling your time, or a sheet to calculate your time, date functions are essential. There are a number of useful date functions to use in various situations. You may need to subtract a date, find out the amount of length of time between two dates. Useful functions are YEAR and MONTH, which can help you move from a day to a specific point in time, give you total years, total months, or total days. If you need more specific numbers, YEARFRAC can be used to give partial year information, including months and days. With YEARFRAC, you adjust the syntax to give results based on how many days in a month are figured, or how many days are specified for the length of a year.
Isolating and recombining functions
There are a couple different situations that might cause you to want to find out a portion of the information in a cell. In some places, a portion of a car license number could indicate the location of the owner’s residence. Again, if you have a number such as a telephone number, with an area code, an exchange number, and a final identification number and you had a table with telephone numbers, names and addresses, you might want to see how many of your customers are from the same area code- which can indicate where the phone is located. In that case, you would pull out the section of the number, in the same place in every line, which is the area code.
To isolate the section of the number you are interested in, you will use the LEFT, RIGHT or MID functions. Bright Hub has an article with an explanation of the syntax to use in in the LEFT, MID and RIGHT section functions. Successful results from this function depend on the string being the same length in each cell.
The reverse of this, CONCATENATE, involves combining information from more than one cell together into another cell. This function is often used when there is a column for a first name and another column for the last name, and a full name is desired.
Random number functions
Generating random numbers in Excel can be done with two different functions - RAND and RANDBETWEEEN. They allow you to generate whether random numbers that are real numbers, or random numbers that are integers. RAND also only gives you values between zero and one, so if you want a larger random number, you will need to use an additional number for a multiplier.
Even more functions
Excel has close to a hundred functions, and this article has just covered some of the areas where functions create useful formulas. For more on Excel functions, this article on Understanding the Different Types of Microsoft Excel Functions will be helpful.