Category: excel

Spell Check in Excel 2010 ”; Previous Next MS Excel provides a feature of Word Processing program called Spelling check. We can get rid of the spelling mistakes with the help of spelling check feature. Spell Check Basis Let us see how to access the spell check. To access the spell checker, Choose Review ➪ Spelling or press F7. To check the spelling in just a particular range, select the range before you activate the spell checker. If the spell checker finds any words it does not recognize as correct, it displays the Spelling dialogue with suggested options. Exploring Options Let us see the various options available in spell check dialogue. Ignore Once − Ignores the word and continues the spell check. Ignore All − Ignores the word and all subsequent occurrences of it. Add to Dictionary − Adds the word to the dictionary. Change − Changes the word to the selected word in the Suggestions list. Change All − Changes the word to the selected word in the Suggestions list and changes all subsequent occurrences of it without asking. AutoCorrect − Adds the misspelled word and its correct spelling (which you select from the list) to the AutoCorrect list. Print Page Previous Next Advertisements ”;

Move Data in Excel 2010 ”; Previous Next Let us see how we can Move Data with MS Excel. Step 1 − Select the data you want to Move. Right Click and Select the cut option. Step 2 − Select the first cell where you want to move the data. Right click on it and paste the data. You can see the data is moved now. Print Page Previous Next Advertisements ”;

Explore Window in Excel 2010 ”; Previous Next The following basic window appears when you start the excel application. Let us now understand the various important parts of this window. File Tab The File tab replaces the Office button from Excel 2007. You can click it to check the Backstage view, where you come when you need to open or save files, create new sheets, print a sheet, and do other file-related operations. Quick Access Toolbar You will find this toolbar just above the File tab and its purpose is to provide a convenient resting place for the Excel”s most frequently used commands. You can customize this toolbar based on your comfort. Ribbon Ribbon contains commands organized in three components − Tabs − They appear across the top of the Ribbon and contain groups of related commands. Home, Insert, Page Layout are the examples of ribbon tabs. Groups − They organize related commands; each group name appears below the group on the Ribbon. For example, group of commands related to fonts or group of commands related to alignment etc. Commands − Commands appear within each group as mentioned above. Title Bar This lies in the middle and at the top of the window. Title bar shows the program and the sheet titles. Help The Help Icon can be used to get excel related help anytime you like. This provides nice tutorial on various subjects related to excel. Zoom Control Zoom control lets you zoom in for a closer look at your text. The zoom control consists of a slider that you can slide left or right to zoom in or out. The + buttons can be clicked to increase or decrease the zoom factor. View Buttons The group of three buttons located to the left of the Zoom control, near the bottom of the screen, lets you switch among excel”s various sheet views. Normal Layout view − This displays the page in normal view. Page Layout view − This displays pages exactly as they will appear when printed. This gives a full screen look of the document. Page Break view − This shows a preview of where pages will break when printed. Sheet Area The area where you enter data. The flashing vertical bar is called the insertion point and it represents the location where text will appear when you type. Row Bar Rows are numbered from 1 onwards and keeps on increasing as you keep entering data. Maximum limit is 1,048,576 rows. Column Bar Columns are numbered from A onwards and keeps on increasing as you keep entering data. After Z, it will start the series of AA, AB and so on. Maximum limit is 16,384 columns. Status Bar This displays the current status of the active cell in the worksheet. A cell can be in either of the fours states (a) Ready mode which indicates that the worksheet is ready to accept user inpu (b) Edit mode indicates that cell is editing mode, if it is not activated the you can activate editing mode by double-clicking on a cell (c) A cell enters into Enter mode when a user types data into a cell (d) Point mode triggers when a formula is being entered using a cell reference by mouse pointing or the arrow keys on the keyboard. Dialog Box Launcher This appears as a very small arrow in the lower-right corner of many groups on the Ribbon. Clicking this button opens a dialog box or task pane that provides more options about the group. Print Page Previous Next Advertisements ”;

Using Functions in Excel 2010 ”; Previous Next Functions in Formula Many formulas you create use available worksheet functions. These functions enable you to greatly enhance the power of your formulas and perform calculations that are difficult if you use only the operators. For example, you can use the LOG or SIN function to calculate the Logarithm or Sin ratio. You can’t do this complicated calculation by using the mathematical operators alone. Using Functions When you type = sign and then type any alphabet you will see the searched functions as below. Suppose you need to determine the largest value in a range. A formula can’t tell you the answer without using a function. We will use formula that uses the MAX function to return the largest value in the range B3:B8 as =MAX(A1:D100). Another example of functions. Suppose you want to find if the cell of month is greater than 1900 then we can give Bonus to Sales representative. The we can achieve it with writing formula with IF functions as =IF(B9>1900,”Yes”,”No”) Function Arguments In the above examples, you may have noticed that all the functions used parentheses. The information inside the parentheses is the list of arguments. Functions vary in how they use arguments. Depending on what it has to do, a function may use. No arguments − Examples − Now(), Date(), etc. One argument − UPPER(), LOWER(), etc. A fixed number of arguments − IF(), MAX(), MIN(), AVERGAGE(), etc. Infinite number of arguments Optional arguments Print Page Previous Next Advertisements ”;

Delete Data in Excel 2010 ”; Previous Next MS Excel provides various ways of deleting data in the sheet. Let us see those ways. Delete with Mouse Select the data you want to delete. Right Click on the sheet. Select the delete option, to delete the data. Delete with Delete Key Select the data you want to delete. Press on the Delete Button from the keyboard, it will delete the data. Selective Delete for Rows Select the rows, which you want to delete with Mouse click + Control Key. Then right click to show the various options. Select the Delete option to delete the selected rows. Print Page Previous Next Advertisements ”;

Select Data in Excel 2010 ”; Previous Next MS Excel provides various ways of selecting data in the sheet. Let us see those ways. Select with Mouse Drag the mouse over the data you want to select. It will select those cells as shown below. Select with Special If you want to select specific region, select any cell in that region. Pressing F5 will show the below dialogue box. Click on Special button to see the below dialogue box. Select current region from the radio buttons. Click on ok to see the current region selected. As you can see in the below screen, the data is selected for the current region. Print Page Previous Next Advertisements ”;

Built-in Functions in Excel 2010 ”; Previous Next 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. Print Page Previous Next Advertisements ”;

Data Filtering in Excel 2010 ”; Previous Next 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. Print Page Previous Next Advertisements ”;

Data Validation in Excel 2010 ”; Previous Next 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. Print Page Previous Next Advertisements ”;

Create Worksheet in Excel 2010 ”; Previous Next Creating New Worksheet Three new blank sheets always open when you start Microsoft Excel. Below steps explain you how to create a new worksheet if you want to start another new worksheet while you are working on a worksheet, or you closed an already opened worksheet and want to start a new worksheet. Step 1 − Right Click the Sheet Name and select Insert option. Step 2 − Now you”ll see the Insert dialog with select Worksheet option as selected from the general tab. Click the Ok button. Now you should have your blank sheet as shown below ready to start typing your text. You can use a short cut to create a blank sheet anytime. Try using the Shift+F11 keys and you will see a new blank sheet similar to the above sheet is opened. Print Page Previous Next Advertisements ”;