Formulas in MS Excel

Formulas are the Bread and butter of worksheet. Without formula, worksheet will be just simple tabular representation of data. A formula consists of special code, which is entered into a cell. It performs some calculations and returns a result, which is displayed in the cell.

Formulas use a variety of operators and worksheet functions to work with values and text. The values and text used in formulas can be located in other cells, which makes changing data easy and gives worksheets their dynamic nature. For example, you can quickly change the data in a worksheet and formulas works.

Elements of Formulas

A formula can consist of any of these elements −

• Mathematical operators, such as +(for addition) and *(for multiplication)

  Example −

  • =A1+A2 Adds the values in cells A1 and A2.

• Values or text

  Example −

  • =200*0.5 Multiplies 200 times 0.15. This formula uses only values, and it always returns the same result as 100.

• Cell references (including named cells and ranges)

  Example −

  • =A1=C12 Compares cell A1 with cell C12. If the cells are identical, the formula returns TRUE; otherwise, it returns FALSE.

• Worksheet functions (such as SUMor AVERAGE)

  Example −

  • =SUM(A1:A12) Adds the values in the range A1:A12.

Creating Formula

For creating a formula you need to type in the Formula Bar. Formula begins with ‘=’ sign. When building formulas manually, you can either type in the cell addresses or you can point to them in the worksheet. Using the Pointing method to supply the cell addresses for formulas is often easier and more powerful method of formula building. When you are using built-in functions, you click the cell or drag through the cell range that you want to use when defining the function’s arguments in the Function Arguments dialog box. See the below screen shot.

As soon as you complete a formula entry, Excel calculates the result, which is then displayed inside the cell within the worksheet (the contents of the formula, however, continue to be visible on the Formula bar anytime the cell is active). If you make an error in the formula that prevents Excel from being able to calculate the formula at all, Excel displays an Alert dialog box suggesting how to fix the problem.

Copying Formulas in MS Excel

Copying formulas is one of the most common tasks that you do in a typical spreadsheet that relies primarily on formulas. When a formula uses cell references rather than constant values, Excel makes the task of copying an original formula to every place that requires a similar formula.

Relative Cell Addresses

MS Excel does it automatically adjusting the cell references in the original formula to suit the position of the copies that you make. It does this through a system known as relative cell addresses, where by the column references in the cell address in the formula change to suit their new column position and the row references change to suit their new row position.

Let us see this with the help of example. Suppose we want the sum of all the rows at last, then we will write a formula for first column i.e. B. We want sum of the rows from 3 to 8 in the 9^th row.

After writing formula in the 9^th row, we can drag it to remaining columns and the formula gets copied. After dragging we can see the formula in the remaining columns as below.

• column C : =SUM(C3:C8)

• column D : =SUM(D3:D8)

• column E : =SUM(E3:E8)

• column F : =SUM(F3:F8)

• column G : =SUM(G3:G8)

 

Cell References in Formulas

Most formulas you create include references to cells or ranges. These references enable your formulas to work dynamically with the data contained in those cells or ranges. For example, if your formula refers to cell C2 and you change the value contained in C2, the formula result reflects new value automatically. If you didn’t use references in your formulas, you would need to edit the formulas themselves in order to change the values used in the formulas.

When you use a cell (or range) reference in a formula, you can use three types of references − relative, absolute, and mixed references.

Relative Cell References

The row and column references can change when you copy the formula to another cell because the references are actually offsets from the current row and column. By default, Excel creates relative cell references in formulas.

Absolute Cell References

The row and column references do not change when you copy the formula because the reference is to an actual cell address. An absolute reference uses two dollar signs in its address: one for the column letter and one for the row number (for example, $A$5).

Mixed Cell References

Both the row or column reference is relative and the other is absolute. Only one of the address parts is absolute (for example, $A5 or A$5).

 

Built In Functions

MS Excel has many built in functions, which we can use in our formula. To see all the functions by category, choose Formulas Tab » Insert Function. Then Insert function Dialog appears from which we can choose the function.

Functions by Categories

Let us see some of the built in functions in MS Excel.

• Text Functions

  • LOWER − Converts all characters in a supplied text string to lower case

  • UPPER − Converts all characters in a supplied text string to upper case

  • TRIM − Removes duplicate spaces, and spaces at the start and end of a text string

  • CONCATENATE − Joins together two or more text strings.

  • LEFT − Returns a specified number of characters from the start of a supplied text string.

  • MID − Returns a specified number of characters from the middle of a supplied text string

  • RIGHT − Returns a specified number of characters from the end of a supplied text string.

  • LEN − Returns the length of a supplied text string

  • FIND − Returns the position of a supplied character or text string from within a supplied text string (case-sensitive).

• Date & Time

  • DATE − Returns a date, from a user-supplied year, month and day.

  • TIME − Returns a time, from a user-supplied hour, minute and second.

  • DATEVALUE − Converts a text string showing a date, to an integer that represents the date in Excel’s date-time code.

  • TIMEVALUE − Converts a text string showing a time, to a decimal that represents the time in Excel.

  • NOW − Returns the current date & time.

  • TODAY − Returns today’s date.

• Statistical

  • MAX − Returns the largest value from a list of supplied numbers.

  • MIN − Returns the smallest value from a list of supplied numbers.

  • AVERAGE − Returns the Average of a list of supplied numbers.

  • COUNT − Returns the number of numerical values in a supplied set of cells or values.

  • COUNTIF − Returns the number of cells (of a supplied range), that satisfies a given criteria.

  • SUM − Returns the sum of a supplied list of numbers

• Logical

  • AND − Tests a number of user-defined conditions and returns TRUE if ALL of the conditions evaluate to TRUE, or FALSE otherwise

  • OR − Tests a number of user-defined conditions and returns TRUE if ANY of the conditions evaluate to TRUE, or FALSE otherwise.

  • NOT − Returns a logical value that is the opposite of a user supplied logical value or expression i.e. returns FALSE if the supplied argument is TRUE and returns TRUE if the supplied argument is FAL

• Math & Trig

  • ABS − Returns the absolute value (i.e. the modulus) of a supplied number.

  • SIGN − Returns the sign (+1, -1 or 0) of a supplied number.

  • SQRT − Returns the positive square root of a given number.

  • MOD − Returns the remainder from a division between two supplied numbers.

Filters in MS Excel

Filtering data in MS Excel refers to displaying only the rows that meet certain conditions. (The other rows gets hidden.)

Using the store data, if you are interested in seeing data where Shoe Size is 36, then you can set filter to do this. Follow the below mentioned steps to do this.

• Place a cursor on the Header Row.

• Choose Data Tab » Filter to set filter.

• Click the drop-down arrow in the Area Row Header and remove the check mark from Select All, which unselects everything.

• Then select the check mark for Size 36 which will filter the data and displays data of Shoe Size 36.

• Some of the row numbers are missing; these rows contain the filtered (hidden) data.

• There is drop-down arrow in the Area column now shows a different graphic — an icon that indicates the column is filtered.

Using Multiple Filters

You can filter the records by multiple conditions i.e. by multiple column values. Suppose after size 36 is filtered, you need to have the filter where color is equal to Coffee. After setting filter for Shoe Size, choose Color column and then set filter for color.

 

Sorting in MS Excel

Sorting data in MS Excel rearranges the rows based on the contents of a particular column. You may want to sort a table to put names in alphabetical order. Or, maybe you want to sort data by Amount from smallest to largest or largest to smallest.

To Sort the data follow the steps mentioned below.

• Select the Column by which you want to sort data.
• Choose Data Tab » Sort Below dialog appears.

• If you want to sort data based on a selected column, Choose Continue with the selection or if you want sorting based on other columns, choose Expand Selection.

• You can Sort based on the below Conditions.

  • Values − Alphabetically or numerically.

  • Cell Color − Based on Color of Cell.

  • Font Color − Based on Font color.

  • Cell Icon − Based on Cell Icon.

• Clicking Ok will sort the data.

Sorting option is also available from the Home Tab. Choose Home Tab » Sort & Filter. You can see the same dialog to sort records.

Ranges in MS Excel

A cell is a single element in a worksheet that can hold a value, some text, or a formula. A cell is identified by its address, which consists of its column letter and row number. For example, cell B1 is the cell in the second column and the first row.

A group of cells is called a range. You designate a range address by specifying its upper-left cell address and its lower-right cell address, separated by a colon.

Example of Ranges −

• C24 − A range that consists of a single cell.

• A1:B1 − Two cells that occupy one row and two columns.

• A1:A100 − 100 cells in column A.

• A1:D4 − 16 cells (four rows by four columns).

Selecting Ranges

You can select a range in several ways −

• Press the left mouse button and drag, highlighting the range. Then release the mouse button. If you drag to the end of the screen, the worksheet will scroll.

• Press the Shift key while you use the navigation keys to select a range.

• Press F8 and then move the cell pointer with the navigation keys to highlight the range. Press F8 again to return the navigation keys to normal movement.

• Type the cell or range address into the Name box and press Enter. Excel selects the cell or range that you specified.

Selecting Complete Rows and Columns

When you need to select an entire row or column. You can select entire rows and columns in much the same manner as you select ranges −

• Click the row or column border to select a single row or column.

• To select multiple adjacent rows or columns, click a row or column border and drag to highlight additional rows or columns.

• To select multiple (nonadjacent) rows or columns, press Ctrl while you click the row or column borders that you want.

Data Validation

MS Excel data validation feature allows you to set up certain rules that dictate what can be entered into a cell. For example, you may want to limit data entry in a particular cell to whole numbers between 0 and 10. If the user makes an invalid entry, you can display a custom message as shown below.

Validation Criteria

To specify the type of data allowable in a cell or range, follow the steps below, which shows all the three tabs of the Data Validation dialog box.

• Select the cell or range.

• Choose Data » Data Tools » Data Validation. Excel displays its Data Validation dialog box having 3 tabs settings, Input Message and Error alert.

Settings Tab

Here you can set the type of validation you need. Choose an option from the Allow drop-down list. The contents of the Data Validation dialog box will change, displaying controls based on your choice.

• Any Value − Selecting this option removes any existing data validation.

• Whole Number − The user must enter a whole number.For example, you can specify that the entry must be a whole number greater than or equal to 50.

• Decimal − The user must enter a number. For example, you can specify that the entry must be greater than or equal to 10 and less than or equal to 20.

• List − The user must choose from a list of entries you provide. You will create drop-down list with this validation. You have to give input ranges then those values will appear in the drop-down.

• Date − The user must enter a date. You specify a valid date range from choices in the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2013, and less than or equal to December 31, 2013.

• Time − The user must enter a time. You specify a valid time range from choices in the Data drop-down list. For example, you can specify that the entered data must be later than 12:00 p.m.

• Text Length − The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).

• Custom − To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either TRUE or FALSE).

Input Message Tab

You can set the input help message with this tab. Fill the title and Input message of the Input message tab and the input message will appear when the cell is selected.

Error Alert Tab

You can specify an error message with this tab. Fill the title and error message. Select the style of the error as stop, warning or Information as per you need.

Using Styles in MS Excel

With MS Excel 2010 Named styles make it very easy to apply a set of predefined formatting options to a cell or range. It saves time as well as make sure that look of the cells are consistent.

(toc)

A Style can consist of settings for up to six different attributes −

• Number format
• Font (type, size, and color)
• Alignment (vertical and horizontal)
• Borders
• Pattern
• Protection (locked and hidden)

Now, let us see how styles are helpful. Suppose that you apply a particular style to some twenty cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 12 pt. rather than 14 pt. Rather than changing each cell, simply edit the style. All cells with that particular style change automatically.

Applying Styles

Choose Home » Styles » Cell Styles. Note that this display is a live preview, that is, as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection.

Creating Custom Style in MS Excel

We can create new custom style in Excel 2010. To create a new style, follow these steps −

• Select a cell and click on Cell styles from Home Tab.
• Click on New Cell Style and give style name.
• Click on Format to apply formatting to the cell.

• After applying formatting click on OK. This will add new style in the styles. You can view it on Home »; Styles.