FORMULAS AND FUNCTIONS
Formulas and Functions enhance Excels capability calculating and manipulating data for analytical purposes.
Formulas
A formula is a set of mathematical instructions that can be used to perform calculations. Formulas are started in the formula box with an = sign. A Formula may consist of:
Operators : Symbols (+, -, *, /, etc.) that specify the calculation to be performed.
References : The cell or range of cells that you want to use in your calculation.
Constants : Numbers or text values that do not change.
Functions : Predefined formulas in Excel.
Create a Simple Formula
We will learn to create a formula to add two numbers:
Click the cell where you want the formula to be defined (for example cell A3). Type = sign to let Excel know that a formula is being defined.
Type the two numbers to be added with the operator. For example type 35+1330 in cell A3 (after = sign). Press Enter.
The result of the above addition operation is displayed in the cell A3, instead of the formula that we had typed. If you select A3, the formula appears in the formula bar.
We can now modify the above formula to add contents of two cells instead of the constant values:
Click the cell where you want the formula to be defined and the answer will appear (for example cell A3).
Type = sign to let Excel know that a formula is being defined.
Type the cell number (example A1) that contains the first number to be added. Then type + operator and then the cell number (example A3)
that contains the second number to be added. For example type A1+A3 in cell A3 (after = sign). Please note, if a cell does not contain a
number then it is treated as containing zero.
Press Enter.
The result of the above addition operation is displayed in the cell A3. Cell A3 will display the value 333.
Change the value in cell A1 to 300, and notice that the value in cell A3 automatically changes to 334.
Create a Simple Formula using Point and Click Method
To create a formula using mouse:
Click the cell where the answer will appear (B3, for example).
Type the equal sign (=).
Click on the first cell to be included in the formula (B1, for example).
Type the operator sign (+ for addition or – for subtraction or * for multiplication or / for division) .
Click on the next cell in the formula (B3, for example). Press Enter or click Enter button on the formula bar.
Using Cell References
When a cell address is used as part of a formula, it is called a cell reference because instead of entering specific numbers into a formula, the cell address referring to a specific cell is being used.
You have used Fill Handle in the auto fill feature in section 3.4.7. The same feature can be used to copy formulas from one cell to another. For example, if you have the formula =A1+B1 in cell C1, and you can use the fill handle to fill the formula into cell C3. Note that the formula won't appear the same in C3 as it does in C1. Instead of =A1+B1, you will see =A3+B3 in cell C3. This is called Relative Reference where cell references in formulas has changed cell addresses relative to the row and column they are moved to. In relative reference, formulas automatically adjust to new locations when they are pasted into different cells.
Sometimes, our requirement is such that we don't want this change of cell address on pasting. To achieve this, cells must be addressed by Absolute Reference.
In Absolute cell references, a formula always refers to the same cell or cell range used in it. If a formula is copied to a different location, then the cell address remains the same. An absolute reference is designated in the formula by the addition of a dollar sign ($). It can precede the column reference or the row reference, or both. Examples of absolute referencing are:
$A1 - here the column will not change when copied. A$1 – here the row will not change when copied.
$A$1 – here both row and column will not change when copied.
In the above example, if we have formula as =$A$1+$B$1 in cell C1 and we copy this formula in cell C3, then you will still see =$A$1+$B$1 in cell C3.
Linking Worksheets
Sometimes, you may want to use the value from a cell in another worksheet within the same workbook in a formula. For example, the value of cell A1 in the current worksheet and cell A3 in the second worksheet can be added using the format "sheetname!celladdress". The formula for this example would be "=A1+Sheet3!A3" where the value of cell A1 in the current worksheet is added to the value of cell A3 in the worksheet named "Sheet3".
Functions
A function is a built in pre defined formula in Excel. One of the key benefits of functions is that they save your time since you do not have to write the formula yourself. For example, you could use an Excel function called Average to quickly find the average of a range of numbers.
Excel has hundreds of different functions to assist with your calculations. Each function has a particular syntax, which must be strictly followed for the function to work correctly.
Function Library
The function library is a large group of functions on the Formula Tab of the Ribbon. These functions include:
AutoSum: Easily calculates the sum of a range
Recently Used: All recently used functions
Financial: Accrued interest, cash flow return rates and additional financial functions
Logical: And, If, True, False, etc.
Text: Text based functions
Date & Time: Functions calculated on date and time
Math & Trig: Mathematical Functions
You can visit each of these functions in the library to know more about them.
Insert a Function
To insert a function:
Click the cell where you want the function applied
Click the Insert Function button on the formula bar. The Insert Function dialog box opens.
Choose the function from the dialog box. You may search on a particular function in the dialog box or change the category and select the function. Help for each function is available right there in the dialog box.
Click OK. Function Arguments dialog box opens.
Select the cells or range of cells for function arguments and click OK. The Function is added to the formula bar
WORKING WITH WORKSHEETS
In this section we will learn to name, add, delete, group or ungroup worksheets. We will also learn to format a worksheet for printing.
Name a Worksheet
The default names of Worksheets are Sheet1, Sheet3 and Sheet3. Since these names are not useful and descriptive, we will learn to rename the worksheet.
You can rename a worksheet using any of the following ways:
Right mouse click on the Sheet1 tab. Menu appears as shown in figure 14(a). Select option Rename. The name Sheet1 is highlighted by a black box and becomes editable.
Type the new name at the tab.
Press Enter. The worksheet is renamed.
OR
Click the Format command of the Cells group in Home Tab. A menu appears as shown is Figure 14(b).
Select option Rename Sheet under Organize Sheet. The sheet name is highlighted by a black box and becomes editable.
Type the new name at the tab.
Press Enter. The worksheet is renamed.
Insert a New Worksheet
You can add worksheets to the workbook anytime you want. The new sheets added will be named as Sheet4 and so on. There are many ways that you can add a new worksheet:
Click on the Insert Worksheet icon near the worksheet tabs OR press Shift+F11.
A new worksheet after the last tab will be added.
OR
Right mouse click on the worksheet tab.
Choose Insert… from the menu (shown in Figure 14(a)). Insert dialog box opens.
Select Worksheet. Click Ok
A new worksheet before the selected tab will be added.
OR
Click the down arrow of Insert command in the Cells group of Home Tab. A menu appears.
Choose Insert Sheet from the menu.
A new worksheet before the selected worksheet will be added.
Delete a Worksheet
Any number of worksheets can be deleted irrespective of the fact that they contain any data or not. But, there should be at least one worksheet in the workbook. To delete a worksheet:
Right mouse click on the worksheet tab.
Choose Delete from the menu (shown in Figure 14(a)). The selected worksheet is deleted.
OR
Click the down arrow of Delete command in the Cells group of Home Tab. A menu appears.
Choose Delete Sheet from the menu. The selected worksheet is deleted.
Grouping Worksheets
If the multiple worksheets of a workbook contain identical formula and formatting, then you can group them together. When the worksheets are grouped together, then any change made to one worksheet will be applied to all other worksheets in the group. You can group both contiguous and noncontiguous worksheets. To group contiguous worksheets:
Click on the first worksheet tab.
Press the Shift key.
While holding the Shift key, click the last worksheet tab you want in the group.
Release the Shift key.
All the sheets from the first sheet to the last sheet are now grouped. The tab colour will now change to white indicating that they are grouped together.
To group noncontiguous worksheets:
Click on the first worksheet tab. Press the Ctrl key.
While holding the Ctrl key, select all the other worksheets you want in the group.
Release the Ctrl key.
All the sheets that you selected while keeping the Ctrl key pressed would be grouped together and sheet tabs will appear white.
Ungrouping Worksheets
To ungroup worksheets:
Right mouse click one of the worksheets in the group.
Select Ungroup Sheets from the menu.
Reposition Worksheets in a Workbook
To change the position of worksheets in a workbook:
Click and hold the worksheet tab that is to moved until an arrow appears on the left corner of the sheet.
Drag the worksheet to the desired location
Hide Worksheets
To hide a worksheet:
Right-click on the tab of the sheet you wish to hide.
Select Hide
OR
Click Format button.
Select Hide & Unhide under Visibility in the menu.
Choose Hide Sheet option.
To unhide a worksheet:
Right-click on tab of any sheet.
Select Unhide…. A dialog box with the list of hidden worksheets is displayed.
Choose the sheet to unhide.
OR
Click Format button.
Select Hide & Unhide under Visibility in the menu.
Choose Unhide Sheet… option. A dialog box with the list of hidden worksheets is displayed.
Choose the sheet to unhide.
Formatting and Printing the Workbook
In this section, we will learn how to set page headers, footers, margin, etc and prepare our workbook for printing.
To Change Page Orientation
Select Page Layout Tab on the Ribbon.
Click Orientation command in the Page Setup group.
Choose the orientation you want – Landscape (horizontal) or Portrait (vertical).
To Change Paper Size
Select Page Layout Tab on the Ribbon.
Click Size command in the Page Setup group.
A drop down menu appears with all the available paper sizes. Current size is highlighted.
Choose the size option. Page size of workbook changes.
To Set Page Margins
Select Page Layout Tab on the Ribbon.
Click Margins command in the Page Setup group. Choose the predefined margins from the list.
OR
Customize your margins by selecting Custom Margins from the menu and entering the desired margins in the appropriate fields.
To Set Headers and Footers
The header is the text that appears in the top margin of every page of the printed worksheet. Similarly, the footer is the text that appears in the bottom margin of every page of the printed worksheet. To add header and footer:
Select Insert Tab on the Ribbon.
Click the Header & Footer button in the Text group. A Design context tab appears under Header & Footer Tools. And worksheet changes to Page Layout view from the Normal view. Page Layout view structures the worksheet so that it is easy to change the format of the worksheet.
Both Header and Footer are divided into three sections: left, center, right. You can type in your custom header/ footer or you can use predefined headers and footers. Click on Header/ Footer button in Header & Footer group of Design context tab to see the list of pre-defined headers and footers.
To Use Scale to Fit
Scale to Fit is a useful feature that can help you format spreadsheets to fit on a page.
Select the Page Layout tab.
Locate the Scale to Fit group.
Enter a specific height and width, or use the percentage field to decrease the spreadsheet by a specific percent.
Be careful with how small you scale the information – you should be able to read it.
To Define a Print Area
At times you may want to print just a part of the whole worksheet. In that case you need to select your print area that you need to be printed. To define your print area:
Click and drag your mouse to select the cells you wish to print.
Click the Print Area command in Page Setup group of Page Layout Tab. Choose Set Print Area. Now, only the selected cells will print. You can confirm this by viewing the spreadsheet in Print Preview.
To return to printing entire worksheet, which is the default setting, click the Print Area command and select Clear Print Area.
To Print Titles on Each Page
Print Title command allows you to select specific rows and/or columns to appear on each printed sheet. This helps when the worksheet prints into many pages, since we can have row and column heading printed on each page for easy association and readability.
Select the Page Layout tab.
Click the Print Titles command in Page Setup group. The Sheet tab of Page Setup dialog box opens.
Click the icon at the end of the field Rows to repeat at top.
Select the row headings in the spreadsheet that you want to appear on each printed page.
Repeat for the column, if required.
Click OK. The select row/ column will now appear on each printed page.
Preview before Printing
Click Office Button.
Select Print à Print Preview. The worksheet opens in the Print Preview mode. In Print Preview, you can access many of the same features that you can from the Ribbon, through the Page Setup dialog box. However, in Print Preview you can see how the spreadsheet will appear in printed format.
Click Print to print the document or Close Print Preview to come back to the document in original mode.
You can modify page margins, orientation, page size, etc in Print Preview mode.
To Quick Print the Document
Click Office Button. Select Print à QuickPrint
The document prints to the default printer. It bypasses the Print dialog box.
To Print the Document
Click Office Button.
Select Print à Print. The Print dialog box appears. Select the printer from the drop down list.
Click Properties to change any necessary settings.
Select the pages you would like to print – specific pages, all of the worksheet, a selected area, the active sheet, or the entire workbook.
Select the number of copies. Click OK to print.
No comments:
Post a Comment