Category: excel

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 ”;

Copy Worksheet in Excel 2010 ”; Previous Next Copy Worksheet First of all, let us create some sample text before we proceed. Open a new excel sheet and type any data. We”ve shown a sample data in the screenshot. OrderDate Region Rep Item Units Unit Cost Total 1/6/2010 East Jones Pencil 95 1.99 189.05 1/23/2010 Central Kivell Binder 50 19.99 999.5 2/9/2010 Central Jardine Pencil 36 4.99 179.64 2/26/2010 Central Gill Pen 27 19.99 539.73 3/15/2010 West Sorvino Pencil 56 2.99 167.44 4/1/2010 East Jones Binder 60 4.99 299.4 4/18/2010 Central Andrews Pencil 75 1.99 149.25 5/5/2010 Central Jardine Pencil 90 4.99 449.1 5/22/2010 West Thompson Pencil 32 1.99 63.68 6/8/2010 East Jones Binder 60 8.99 539.4 6/25/2010 Central Morgan Pencil 90 4.99 449.1 7/12/2010 East Howard Binder 29 1.99 57.71 7/29/2010 East Parent Binder 81 19.99 1,619.19 8/15/2010 East Jones Pencil 35 4.99 174.65 Here are the steps to copy an entire worksheet. Step 1 − Right Click the Sheet Name and select the Move or Copy option. Step 2 − Now you”ll see the Move or Copy dialog with select Worksheet option as selected from the general tab. Click the Ok button. Select Create a Copy Checkbox to create a copy of the current sheet and Before sheet option as (move to end) so that new sheet gets created at the end. Press the Ok Button. Now you should have your copied sheet as shown below. You can rename the sheet by double clicking on it. On double click, the sheet name becomes editable. Enter any name say Sheet5 and press Tab or Enter Key. Print Page Previous Next Advertisements ”;

Getting Started with Excel 2010 ”; Previous Next This chapter teaches you how to start an excel 2010 application in simple steps. Assuming you have Microsoft Office 2010 installed in your PC, start the excel application following the below mentioned steps in your PC. Step 1 − Click on the Start button. Step 2 − Click on All Programs option from the menu. Step 3 − Search for Microsoft Office from the sub menu and click it. Step 4 − Search for Microsoft Excel 2010 from the submenu and click it. This will launch the Microsoft Excel 2010 application and you will see the following excel window. Print Page Previous Next Advertisements ”;

Entering Values in Excel 2010 ”; Previous Next Entering values in excel sheet is a child’s play and this chapter shows how to enter values in an excel sheet. A new sheet is displayed by default when you open an excel sheet as shown in the below screen shot. Sheet area is the place where you type your text. The flashing vertical bar is called the insertion point and it represents the location where text will appear when you type. When you click on a box then the box is highlighted. When you double click the box, the flashing vertical bar appears and you can start entering your data. So, just keep your mouse cursor at the text insertion point and start typing whatever text you would like to type. We have typed only two words “Hello Excel” as shown below. The text appears to the left of the insertion point as you type. There are following three important points, which would help you while typing − Press Tab to go to next column. Press Enter to go to next row. Press Alt + Enter to enter a new line in the same column. Print Page Previous Next Advertisements ”;

Close Workbook in Excel 2010 ”; Previous Next Close Workbook Here are the steps to close a workbook. Step 1 − Click the Close Button as shown below. You”ll see a confirmation message to save the workbook. Step 2 − Press the Save Button to save the workbook as we did in MS Excel – Save Workbook chapter. Now your worksheet will get closed. Print Page Previous Next Advertisements ”;

Move Around in Excel 2010 ”; Previous Next Excel provides a number of ways to move around a sheet using the mouse and the keyboard. First of all, let us create some sample text before we proceed. Open a new excel sheet and type any data. We”ve shown a sample data in the screenshot. OrderDate Region Rep Item Units Unit Cost Total 1/6/2010 East Jones Pencil 95 1.99 189.05 1/23/2010 Central Kivell Binder 50 19.99 999.5 2/9/2010 Central Jardine Pencil 36 4.99 179.64 2/26/2010 Central Gill Pen 27 19.99 539.73 3/15/2010 West Sorvino Pencil 56 2.99 167.44 4/1/2010 East Jones Binder 60 4.99 299.4 4/18/2010 Central Andrews Pencil 75 1.99 149.25 5/5/2010 Central Jardine Pencil 90 4.99 449.1 5/22/2010 West Thompson Pencil 32 1.99 63.68 6/8/2010 East Jones Binder 60 8.99 539.4 6/25/2010 Central Morgan Pencil 90 4.99 449.1 7/12/2010 East Howard Binder 29 1.99 57.71 7/29/2010 East Parent Binder 81 19.99 1,619.19 8/15/2010 East Jones Pencil 35 4.99 174.65 Moving with Mouse You can easily move the insertion point by clicking in your text anywhere on the screen. Sometime if the sheet is big then you cannot see a place where you want to move. In such situations, you would have to use the scroll bars, as shown in the following screen shot − You can scroll your sheet by rolling your mouse wheel, which is equivalent to clicking the up-arrow or down-arrow buttons in the scroll bar. Moving with Scroll Bars As shown in the above screen capture, there are two scroll bars: one for moving vertically within the sheet, and one for moving horizontally. Using the vertical scroll bar, you may − Move upward by one line by clicking the upward-pointing scroll arrow. Move downward by one line by clicking the downward-pointing scroll arrow. Move one next page, using next page button (footnote). Move one previous page, using previous page button (footnote). Use Browse Object button to move through the sheet, going from one chosen object to the next. Moving with Keyboard The following keyboard commands, used for moving around your sheet, also move the insertion point − Keystroke Where the Insertion Point Moves Forward one box Back one box Up one box Down one box PageUp To the previous screen PageDown To the next screen Home To the beginning of the current screen End To the end of the current screen You can move box by box or sheet by sheet. Now click in any box containing data in the sheet. You would have to hold down the Ctrl key while pressing an arrow key, which moves the insertion point as described here − Key Combination Where the Insertion Point Moves Ctrl +  To the last box containing data of the current row. Ctrl +  To the first box containing data of the current row. Ctrl +  To the first box containing data of the current column. Ctrl +  To the last box containing data of the current column. Ctrl + PageUp To the sheet in the left of the current sheet. Ctrl + PageDown To the sheet in the right of the current sheet. Ctrl + Home To the beginning of the sheet. Ctrl + End To the end of the sheet. Moving with Go To Command Press F5 key to use Go To command, which will display a dialogue box where you will find various options to reach to a particular box. Normally, we use row and column number, for example K5 and finally press Go To button. Print Page Previous Next Advertisements ”;

Text Decoration in Excel 2010 ”; Previous Next You can change the text decoration of the cell to change its look and feel. Text Decoration Various options are available in Home tab of the ribbon as mentioned below. Bold − It makes the text in bold by choosing Home » Font Group » Click B or Press Control + B. Italic − It makes the text italic by choosing Home » Font Group » Click I or Press Control + I. Underline − It makes the text to be underlined by choosing Home » Font Group » Click U or Press Control + U. Double Underline − It makes the text highlighted as double underlined by choose Home » Font Group » Click arrow near U » Select Double Underline. More Text Decoration Options There are more options available for text decoration in Formatting cells » Font Tab » Effects cells as mentioned below. Strike-through − It strikes the text in the center vertically. Super Script − It makes the content to appear as a super script. Sub Script − It makes content to appear as a sub script. Print Page Previous Next Advertisements ”;