Category: excel

Copy & Paste in Excel 2010 ”; Previous Next MS Excel provides copy paste option in different ways. The simplest method of copy paste is as below. Copy Paste To copy and paste, just select the cells you want to copy. Choose copy option after right click or press Control + C. Select the cell where you need to paste this copied content. Right click and select paste option or press Control + V. In this case, MS Excel will copy everything such as values, formulas, Formats, Comments and validation. MS Excel will overwrite the content with paste. If you want to undo this, press Control + Z from the keyboard. Copy Paste using Office Clipboard When you copy data in MS Excel, it puts the copied content in Windows and Office Clipboard. You can view the clipboard content by Home → Clipboard. View the clipboard content. Select the cell where you need to paste. Click on paste, to paste the content. Copy Paste in Special way You may not want to copy everything in some cases. For example, you want to copy only Values or you want to copy only the formatting of cells. Select the paste special option as shown below. Below are the various options available in paste special. All − Pastes the cell’s contents, formats, and data validation from the Windows Clipboard. Formulas − Pastes formulas, but not formatting. Values − Pastes only values not the formulas. Formats − Pastes only the formatting of the source range. Comments − Pastes the comments with the respective cells. Validation − Pastes validation applied in the cells. All using source theme − Pastes formulas, and all formatting. All except borders − Pastes everything except borders that appear in the source range. Column Width − Pastes formulas, and also duplicates the column width of the copied cells. Formulas & Number Formats − Pastes formulas and number formatting only. Values & Number Formats − Pastes the results of formulas, plus the number. Merge Conditional Formatting − This icon is displayed only when the copied cells contain conditional formatting. When clicked, it merges the copied conditional formatting with any conditional formatting in the destination range. Transpose − Changes the orientation of the copied range. Rows become columns, and columns become rows. Any formulas in the copied range are adjusted so that they work properly when transposed. Print Page Previous Next Advertisements ”;

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

Using Themes in Excel 2010 ”; Previous Next Using Themes in MS Excel To help users create more professional-looking documents, MS Excel has incorporated a concept known as document themes. By using themes, it is easy to specify the colors, fonts, and a variety of graphic effects in a document. And best of all, changing the entire look of your document is a breeze. A few mouse clicks is all it takes to apply a different theme and change the look of your workbook. Applying Themes Choose Page layout Tab » Themes Dropdown. Note that this display is a live preview, that is, as you move your mouse over the Theme, it temporarily displays the theme effect. When you see a style you like, click it to apply the style to the selection. Creating Custom Theme in MS Excel We can create new custom Theme in Excel 2010. To create a new style, follow these steps − Click on the save current theme option under Theme in Page Layout Tab. This will save the current theme to office folder. You can browse the theme later to load the theme. Print Page Previous Next Advertisements ”;

Insert Page Break in Excel 2010 ”; Previous Next Page Breaks If you don’t want a row to print on a page by itself or you don”t want a table header row to be the last line on a page. MS Excel gives you precise control over page breaks. MS Excel handles page breaks automatically, but sometimes you may want to force a page break either a vertical or a horizontal one. so that the report prints the way you want. For example, if your worksheet consists of several distinct sections, you may want to print each section on a separate sheet of paper. Inserting Page Breaks Insert Horizontal Page Break − For example, if you want row 14 to be the first row of a new page, select cell A14. Then choose Page Layout » Page Setup Group » Breaks » Insert Page Break. Insert vertical Page break − In this case, make sure to place the pointer in row 1. Choose Page Layout » Page Setup » Breaks » Insert Page Break to create the page break. Removing Page Breaks Remove a page break you’ve added − Move the cell pointer to the first row beneath the manual page break and then choose Page Layout » Page Setup » Breaks » Remove Page Break. Remove all manual page breaks − Choose Page Layout » Page Setup » Breaks » Reset All Page Breaks. Print Page Previous Next Advertisements ”;

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

Header and Footer in Excel 2010 ”; Previous Next Header and Footer A header is the information that appears at the top of each printed page and a footer is the information that appears at the bottom of each printed page. By default, new workbooks do not have headers or footers. Adding Header and Footer Choose Page Setup dialog box » Header or Footer tab. You can choose the predefined header and footer or create your custom ones. &[Page] − Displays the page number. &[Pages] − Displays the total number of pages to be printed. &[Date] − Displays the current date. &[Time] − Displays the current time. &[Path]&[File] − Displays the workbook’s complete path and filename. &[File] − Displays the workbook name. &[Tab] − Displays the sheet’s name. Other Header and Footer Options When a header or footer is selected in Page Layout view, the Header & Footer » Design » Options group contains controls that let you specify other options − Different First Page − Check this to specify a different header or footer for the first printed page. Different Odd & Even Pages − Check this to specify a different header or footer for odd and even pages. Scale with Document − If checked, the font size in the header and footer will be sized. Accordingly if the document is scaled when printed. This option is enabled, by default. Align with Page Margins − If checked, the left header and footer will be aligned with the left margin, and the right header and footer will be aligned with the right margin. This option is enabled, by default. Print Page Previous Next Advertisements ”;

Conditional Format in Excel 2010 ”; Previous Next Conditional Formatting MS Excel 2010 Conditional Formatting feature enables you to format a range of values so that the values outside certain limits, are automatically formatted. Choose Home Tab » Style group » Conditional Formatting dropdown. Various Conditional Formatting Options Highlight Cells Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the cells in the cell selection that contain certain values, text, or dates, or that have values greater or less than a particular value, or that fall within a certain ranges of values. Suppose you want to find cell with Amount 0 and Mark them as red.Choose Range of cell » Home Tab » Conditional Formatting DropDown » Highlight Cell Rules » Equal To. After Clicking ok, the cells with value zero are marked as red. Top/Bottom Rules − It opens a continuation menu with various options for defining the formatting rules that highlight the top and bottom values, percentages, and above and below average values in the cell selection. Suppose you want to highlight the top 10% rows you can do this with these Top/Bottom rules. Data Bars − It opens a palette with different color data bars that you can apply to the cell selection to indicate their values relative to each other by clicking the data bar thumbnail. With this conditional Formatting data Bars will appear in each cell. Color Scales − It opens a palette with different three- and two-colored scales that you can apply to the cell selection to indicate their values relative to each other by clicking the color scale thumbnail. See the below screenshot with Color Scales, conditional formatting applied. Icon Sets − It opens a palette with different sets of icons that you can apply to the cell selection to indicate their values relative to each other by clicking the icon set. See the below screenshot with Icon Sets conditional formatting applied. New Rule − It opens the New Formatting Rule dialog box, where you define a custom conditional formatting rule to apply to the cell selection. Clear Rules − It opens a continuation menu, where you can remove the conditional formatting rules for the cell selection by clicking the Selected Cells option, for the entire worksheet by clicking the Entire Sheet option, or for just the current data table by clicking the This Table option. Manage Rules − It opens the Conditional Formatting Rules Manager dialog box, where you edit and delete particular rules as well as adjust their rule precedence by moving them up or down in the Rules list box. Print Page Previous Next Advertisements ”;

Excel – Quick Guide ”; Previous Next Getting Started with Excel 2010 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. Explore Window in Excel 2010 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. BackStage View in Excel 2010 The Backstage view has been introduced in Excel 2010 and acts as the central place for managing your sheets. The backstage view helps in creating new sheets, saving and opening sheets, printing and sharing sheets, and so on. Getting to the Backstage View is easy. Just click the File tab located in the upper-left corner of the Excel Ribbon. If you already do not have any opened sheet then you will see a window listing down all the recently opened sheets as follows − If you already have an opened sheet then it will display a window showing the details about the opened sheet as shown below. Backstage view shows three columns when you select most of the available options in the first column. First column of the backstage view will have the following options − S.No. Option & Description 1 Save If an existing sheet is opened, it would be saved as is, otherwise it will display a dialogue box asking for the sheet name. 2 Save As A dialogue box will be displayed asking for sheet name and sheet type. By default, it will save in sheet 2010 format with extension .xlsx. 3 Open This option is used to open an existing excel sheet. 4 Close This option is used to close an opened sheet. 5 Info This option displays the information about the opened sheet. 6 Recent This option lists down all the recently opened sheets. 7 New This option is used to open a new sheet. 8 Print This option is used to print an opened sheet. 9 Save & Send This option saves an opened sheet and displays options to send the sheet using email etc. 10 Help You can use this option to get the required help about excel 2010.

Insert Data in Excel 2010 ”; Previous Next In MS Excel, there are 1048576*16384 cells. MS Excel cell can have Text, Numeric value or formulas. An MS Excel cell can have maximum of 32000 characters. Inserting Data For inserting data in MS Excel, just activate the cell type text or number and press enter or Navigation keys. Inserting Formula For inserting formula in MS Excel go to the formula bar, enter the formula and then press enter or navigation key. See the screen-shot below to understand it. Modifying Cell Content For modifying the cell content just activate the cell, enter a new value and then press enter or navigation key to see the changes. See the screen-shot below to understand it. Print Page Previous Next Advertisements ”;

Rotate Cells in Excel 2010 ”; Previous Next You can rotate the cell by any degree to change the orientation of the cell. Rotating Cell from Home Tab Click on the orientation in the Home tab. Choose options available like Angle CounterClockwise, Angle Clockwise, etc. Rotating Cell from Formatting Cell Right Click on the cell. Choose Format cells » Alignment » Set the degree for rotation. Print Page Previous Next Advertisements ”;