Data Validation Excel’s data validation feature enables you to set up specific rules that restrict a user from entering invalid data. For example, you may want to limit data entry in a particular cell to whole numbers between 3000 & 20000. If the user makes an invalid entry, you can display an error message. Excel makes it easy to specify the validation criteria, and you can also use a formula for more complex criteria. Types of Validation Criteria You can Apply The settings tab of the Data Validation dialog box enables you to specify a wide variety of data validation criteria. Types of options which are available for Data Validation.Also, Keep in mind that the other controls in the settings tab which will be vary depending on your choice in the allow drop-down box. Specifying Validation Criteria To specify the type of data allowable in a cell or range, follow the below steps • First Select the cell or range • Then, go to Data tab-> Data Tools group-> Data Validation.Excel displays its Data Validation dialog box. • Click the Settings tab (see the above figure). • Choose an option from the drop-down box labeled Allow. The contents of the Data Validation dialog box will change, displaying controls based on your choice. To specify a formula, select Custom. • Specify the conditions by using the displayed controls. • (Optional) Click the Input Message tab and specify which message to display when a user select the cell. • (Optional) Click the Error Alert tab, and specify which error message to display when a user makes an invalid entry. • Click Ok. Circle around Invalid Entries For the getting Circle for the Invalid Entries. Go to Data-> Data Validation-> Setting. In setting-> Validation Criteria-> select whole number-> Min:-100 , Max: 1000. Again go to Data-> Data Tools -> Data Validation drop-down control contains an item name Circle Invalid Data. When you click this item, circles appear around cells contain incorrect entries. If you correct an invalid entry, the circle will disappear. In this fig, invalid entries are defined as values less than 100. Creating a Drop-Down List Perhaps one of the most common uses of data validation is to create a drop-down list in a cell. Figure below shows an example that uses the month name in A1:A2 as the list source To create a drop-down list in a cell 1)Enter the list items into a single-row or single-column range. These items are the ones that appear in the drop-down list. 2)Select the cell that will contain the drop-down list and access the Data Validation dialog box. 3)In the Settings tab, select the List option and specify the range that contains the list using the Source control. If the source cells are in the same worksheet you can specify the range will cell address, otherwise, you have to specify the name of the range of source cells. 4)Make sure that the In-cell Dropdown check box is checked. 5)You can set Input Message and Error Alert if you want. After, performing these steps, the cell displays a drop-down arrow when it’s activated. Click the arrow and choose an item from the list that appears. Custom Data Validation The following sections contain a data validation example that uses a formula entered directly into the formula control on the settings tab of the Data Validation dialog box. These examples help you understand how to create your own Data Validation Formula
What are Logical Functions in Excel?
Definition of Logical Functions Excel’s logical functions are used to perform analytical tests to reveal whether a statement is true or false. Depending on the outcome of the logical trial, a specific result is returned. If() This function returns a value depending on the logical test given as an argument of the function. The syntax of the function is =IF(Logical_test,Result_if_true,Result_if_false) Logical_test is any value or expression that can be evaluated as true or false. Result_if_true is the value that will return if the Logical_test is true. Result_if_false is the value that will return if the Logical_test is false. Example 1: The figure shows a worksheet with student grades in column B. Formulas in column C use the IF function to return text either Pass (a score of 65 or higher) or Fail(a score below 65). The formula in cell C2 is: =IF(B2>=65,”Pass”,”Fail”) Example 2: Nested IF You can “nest” IF functions to provide even more decision-making ability. This formula, for example, returns one of four strings. Excellent (a score of 90 or higher), Very Good (a score of 70 or higher but less than 90), Fair (a score of 50 or higher but less than 70), or Poor (a score less than 50). In the formula in cell D3, the first logical test will be C3>=90, If this returns true, the cell value should be Excellent, but if it returns false what should be the value: Very Good, Fair, or Poor? So there should be another logical test and another If function that can be nested inside. The logical test should be C3>=70. Again if this test returns false you need to take another decision: whether the value is more than or equal to 50 or less. So one more If the function should be nested. Thus the final formula in cell D3 is: =If(C3>=90,”Excellent”,If(C3>=“Very Good”,If(C3=>50,”Fair”,”Poor”))) AND() and OR() Function We often have to work with multiple conditions to come to a decision. Excel provides an efficient way to work with these conditions through these two functions. Both these functions can accept more than one condition (maximum 30) as their arguments and returns a logical value, i.e, TRUE or FALSE. AND() returns true only when all the conditions given are true otherwise returns false. On the other hand OR() returns true when any one of the conditions given is true. It returns false only when all the conditions given are false. The syntax of AND() is : =AND(condition1,condition2,condition3,……….). The syntax of OR() is : =OR(condition1,condition2,condition3,……….). These two functions are widely used in place of logical tests on IF() function as like logical tests these functions also return true/false. Let us explain the function with the following examples: Example 1: A builder’s merchant gives a 10% discount on certain product lines. The discount is only given on products which are on Special Offer when the Order Value is Rs,1000 o above. In this example, we have to check two conditions to offer a discount to the customer, one is whether the product is on Special Offer and another one is whether the Order Value is Rs 1000 or above. That can be checked through AND() function [=AND(B3=“Yes”,C3>=1000)]. Now based on that conditions, we have to take a decision, on whether to offer a discount or not, So this above function has to be incorporated with If() function. Thus the final formula is : =If(AND(B3=“Yes”,C3>=1000),C3810%,0). Example 2: A handling charge of Rs 50/- is made on all orders paid by Visa or Delta Cards. The =OR() function has been used to determine whether the charge need to be applied. In this example, any of the two conditions (whether the payment type is Visa or Delta) has to be satisfied to change the Handling charge of Rs,50. So, Or() function has to be used within the If() function. The final formula is: IF(Or(C3=“Visa”, C3=“Delta”),50,0). Not() Not () function is used to reverse the value of its argument. Not() function accept a logical value as its argument. Use Not() function when you want to perform an action if the condition (s) are not met. Example : The =NOT() function has been used to calculate whether the book was returned within the correct time, by adding the Loan value to the Taken date. If the book was not returned on time the result Overdue is shown, otherwise OK is shown. The formula in D2 is =IF(NOT(C2<=A2+B2).”Overdue”,”OK”) This formula can also be written without using Not() function: =if(C2>A2+B2,”Overdue”,”OK”)
What are Basics Formulas & Functions of Excel?
Understanding Formulas Basics The formula is what makes a spreadsheet program so useful. If it weren’t for formulas, a spreadsheet would simply be a glorified world-processing document that has great support for tabular information. You use formulas in your Excel worksheets to calculate results from the data stored in the worksheet. When data changes, those formulas calculate updated results with no extra effort on your part. Note: Formulas always being with the (=) equal sign so that Excel can distinguish them from the text. Using operations in formulas Excel lets you use a variety of operators in your formulas. Operators are symbols that indicate the type of mathematical operations you want the formula to perform. Table 1 lists the operators that Excel recognizes. In addition to these, Excel has many built-in functions that enable you to perform additional calculations. SUM() Formula SUM is one of the most commonly used functions. It adds all the numbers in the range of cells.The syntax of the SUM functions is: =SUM(>Range>)The range is a group of cells to be calculated by the function. You can also provide values as arguments.Example:=SUM (15,20,45,83)-> You can use values in the function.=SUM(A1: A9)-> For contiguous cells, give the reference of the first cell, then a colon sign(:), and then the cell reference of the last cell of the range. (See figure).=SUM(B5:B10, B15, B20:B25)-> for non-contiguous references, use a comma as a separator. ROUND () Formula Round Function round a number to a specified number of digits. The syntax for this function is: =ROUND(<Number>, <Num_digits>)The number is the number you want to round. Num_digits specifies the number of digits to which you want to round the number.Example: =ROUND(235.75,1) The return value will be 235.8 because you want to round off the number to one decimal place. Since the second decimal value is 5. The round function will add 1 with the first decimal value. SQRT () Formula This function returns the square root value of a given number. The syntax of this function is =SQRT(25). It will return 5. Range A group of cells is called range cells. The cells in a range may all be in one column, one row, or any combination of columns and rows, as long as the range forms a rectangle. A range also can be a single cell. Ranges are referred to by their anchor points, which means the upper-left cell address and its lower-right cell address, separated by a colon.Here are some examples of range addresses:-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: AD4:- 16 cells (four rows by four columns).C1:C1048576:- An entire column of cells; this range also can be expressed as C:C.A6:XFD6:- An entire row of cells; this range also can be expressed as 6:6.A1: XFD1048576: All cells in a worksheet. Selecting Range: To select a range, place the mouse pointer on the first cell of that range, click and hold the left mouse button, drag the mouse to the lower-right corner of the range, and release the mouse button. The cells are highlighted on the chart and the selected range appears in the formula bar. You can also select a range through the keyboard. There are so many methods to select a range. Naming a Range Select the range. Go to Formula Tab-> Defined Names-> Define Name to display the dialog box as shown in the figure. Types a name in the box labeled Name (or use the name that Excel proposes, if any). The selected cell or range address appears in the box labeled Refers To. Use the Scope drop-down to indicate the scope for the name. The scope indicates the scope for the name. The scope indicates where the name will be valid, and it’s either the entire workbook or a particular sheet. If you like, you can add a comment that describes the named range or cell. Click OK to add the name to your workbook and close the dialog box. Managing Names A workbook can have any number of names. If you have many names, you know about the Name Manager. The Name Manager appears when you choose Formulas-> Defined Names-> Name Manager (or press Ctrl+F3). The Name Manager has the following features: • Displays information about each name in the workbook. You can resize the Name Manager Dialog box and widen the columns to show more information. You can also click a column heading to sort the information by column. • Allows you to filter the displayed names, Clicking the Filter button lets you show only those names that meet certain criteria. For e.g.: – you can view only the worksheet-level names. • Provides quick access to the New Name dialog box. Click the New button to create a new name without closing the Name Manager. • Let you edit names. To edit a name, select it in the list and then click the Edit button. You can change the name or refers to the range or edit the comment. • Lets you quickly delete unneeded names. To delete a name, select it in the list and click Delete. Adding Comments to Cells In Excel, a note can be added to a certain cell by inserting a comment. You can edit the comment and delete those comments which are no longer useful. Select the cell and then go to Review tab-> Comments group -> New comment. Right Click the cell and choose Insert Comment from the pop-up menu. After you finished writing, click anywhere outside the comment area to hide the comment box. A small red arrow at the top right corner of the cell indicates that a comment is inserted on the cell. Statistical Functions Excel’s statistical functions are used on lists of data. Some of the simpler functions are AVERAGE, MAX, MIN, etc. AVERAGE () This function returns the average or arithmetic mean of the supplied arguments which can be numbers, names, cell
Introduction to Excel | Entering Data & Simple Calculation
Introduction to Excel Excel is nothing but a spreadsheet program that has powerful tools and features, with the help of tools and features it analyzes the data and helps us to maintain and share, it with the least effort. The large sheet containing information that is arranged in the form of a matrix of rows and columns is called Spreadsheet. With the help of the Spreadsheet, you can enter the data which you want to calculate, analyze and manipulate. When you added a formula in the spreadsheet and if you change any data then it automatically recalculates and gives us the new answer Like Excel, there are many other spreadsheets such as Lotus 1-2-3, Quattro Pro, etc. are available but Excel is very easy as compared to the above spreadsheet and it is very useful and world standard. Area of Usage: We get a complete listing of applications under one roof in Excel, it includes budgeting displays, enrollment records, inventories, coded surveys, field, checkbook registers, laboratory research data, and financial and accounting applications. Excel also includes automatic recalculation, graphs, and functions. The answer to all the above is only one – MS – Excel. There are so many examples are there where excel is used as the most powerful to solve day-to-day problems. Launching Excel 1) Click Start Menu ->All Programs -> Microsoft Office -> Microsoft Office Excel 2007,2010, 2013 OR 2) You can also type “EXCEL” under the open text box in the Run dialog box (Start Button -> Run) to launch the program. When you open Microsoft Office Excel, the first thing you will notice is the new interface. Now that we’ve opened Excel, let’s take a look at what’s on our screen. A brief explanation of the different components of Excel’s opening Screen: Rows, Columns & Cells: – Every worksheet consists of rows (numbered 1 through 1,048,576) and columns (labeled as A through XFD). After column Z comes column AA, which is followed by AB, AC, and so on. After column AZ comes to BA, BB, and so on. After column ZZ is AAA, BBB, and so on. Each worksheet has 16,384 columns and 10, 48,576 rows. So, each worksheet contains 16384 X 1048576 numbers of cells. A cell is the intersection of a row and a column. For example, the first cell is referred to as A1 (column A, row 1). Data and formulas are entered into cells. Entering Data in Cell: – Whenever you enter in excel you are given a blank file (Workbook) containing three worksheets. Out of this sheet, 1 is activated by default. In each worksheet in the combination of multiple cells (in each worksheet there are multiple rows and columns, interacting areas of rows and columns create rectangular zones, and each rectangular zone is called a cell) we will enter data under different cells. To enter data in a cell select the cell and type the data. As soon as you start entering characters from the keyboard, a text will appear in the cell and in the formula bar. Press Enter key to complete the data entry, and immediately cell pointer moves down one cell. When the text does not fit into the cell, it overflows into the next cell, provided that cell, provided that cell is empty. If it is not, then the excess part of the text will be seen as hidden. To expand a cell to display the data properly, you have to change the column width. For a data type of data, either you have to use Front Slash (“/”) or Hyphen (“-“) as a date separator (example: – 12/10/2003 12-10-2003). If you make any mistake while entering data, press the ESC key to cancel the entire entry for the current cell. You can use backspace to erase single or multiple characters from the current entry. Types of Data you can Enter in Excel An Excel workbook can hold any number of worksheets, and each worksheet is made up of more than 17 billion cells. A cell can hold any of three basic types of data: The Text is any combination of letters, numbers, and spaces. By default, the text is automatically left aligned in a cell. However, the numbers and dates are right aligned. A worksheet can also hold charts, diagrams, pictures, buttons, and other objects. These objects aren’t contained in cells. Rather, they reside on the worksheet’s draw layer, which is an invisible layer on top of each worksheet. Moving Around a Worksheet At the opening interface, Excel provides you with a default workbook name book 1 containing 3 worksheets. When you want to place your cursor or activate or cell in order to insert, edit or format the cells, there are ways to help you. You can use: – Navigation with your Keyboard You can use the standard navigational keys on your keyboard to move around a worksheet. These keys work just as you’d expect. The down arrow moves the active cell down one row, the right arrow moves it from one column to right, and so on. PgUp and PgDn move the active cell up or down one full window. (The actual number of rows moved depends on the number of rows displayed in the window.) Navigation with your Mouse: – To change the active cell by using the mouse, click another cell; it becomes the active cell. If the cell that you want to activate isn’t visible in the workbook in any direction. To scroll one cell, click either of the arrows on the scrollbar. To scroll by a complete screen, click either side of the scrollbar’s scroll box. You also drag the scroll box for faster scrolling. Press Ctrl while you use the mouse wheel to Zoom the worksheet without pressing Ctrl, choose Office Button -> Excel Option and select the Advanced Section. Place a checkmark next to the Zoom on Roll with the Intellimouse check box. Using the scrollbars or scrolling with your mouse doesn’t change the active cell.
What are Rows & Columns in Excel?
Working with Rows and Columns Rows and columns make up an Excel Worksheet. Every worksheet has 1,048,576 rows and 16,384 columns, and these values can’t be changed. You can insert/delete Row(s) and Column(s) but here rows and columns will be deleted automatically from the last according to the number of rows and columns inserted. Alternatively, You can also insert Row(s) or columns from the home Tab ribbon by clicking on the Insert Button and clicking on the insert sheet rows or columns option Deleting Rows or Columns Row(s) or Column(s) can be deleted by right-clicking on the selected Row(s) or Column(s) and clicking on the Delete option. Alternatively, You can also delete selected row(s) or column(s) by clicking on Delete sheet Rows or Columns from the Insert button under the home tab ribbons Cells group Hide/Unhide rows or columns To get the options of hiding and unhiding rows and columns, go to the format button of the Home tab Adjusting Row Height How to select a cell, a range, or text in a cell To Select Do This A single cell Click on the cell, or press the arrow keys to move to the cell. A range of cells From the range click the first cell and drag to the last cell or you can press SHIFT and hold the arrow keys to extend the selections. There is a second method to do this, select the first cell in the range and press F8 it will open the selection by using the arrow keys. Press F8 again to stop extending the selection. All cells on a worksheet Click the Select All button. To select the entire worksheet, you can also press Ctrl + A.NOTE If the worksheet contains data, CTRL + A selects the current region. Pressing CTRL+A a second time selects the entire worksheet. Nonadjacent cells or cell ranges. Select the first cell or range of cells, and then hold down CTRL while you select the other cells or ranges. You can select the first cell or range of cells, and then press SHIFT+F8 to add another nonadjacent cell or range to the selection. To stop adding cells or ranges to the selection, press SHIFT+F8 again.NOTE We cannot cancel the selection of a cell and range of cells in a nonadjacent selection without canceling the entire selection. An entire row or column Click the row or column heading. 1. Row heading. 2. Column Heading. Select multiple or single cells in a column or row by selecting the first cell. Then press CTRL+SHIFT+ARROW Key (RIGHT ARROW or LEFT ARROW for rows, UP ARROW or DOWN ARROW for columns). NOTE If the row or column contains data, the CTRL+SHIFT+ARROW key selects the row or column to the last used cell. Pressing the CTRL+SHIFT+ARROW key a second time selects the entire row and column. The first or last cell on a worksheet or in a Microsoft Office Excel Table The first or last cell on a worksheet or in a Microsoft Office Excel Table The last used cell on the worksheet (lower-right corner) Select the first cell, then press CTRL+SHIFT+END for extending the selection to the last used cell on the worksheet (lower-right corner) Cells to the beginning of the worksheet Select the first cell, and then press CTRL+SHIFT+HOME to extend the selection of cells to the beginning of the worksheet More or few cells than the active selection Press SHIFT while you click the last cell that you want to include in the new selection. The rectangular range between the active cell and the cell that you click becomes the unique selection.