Category: excel

Insert Comments in Excel 2010 ”; Previous Next Adding Comment to Cell Adding comment to cell helps in understanding the purpose of cell, what input it should have, etc. It helps in proper documentation. To add comment to a cell, select the cell and perform any of the actions mentioned below. Choose Review » Comments » New Comment. Right-click the cell and choose Insert Comment from available options. Press Shift+F2. Initially, a comment consists of Computer”s user name. You have to modify it with text for the cell comment. Modifying Comment You can modify the comment you have entered before as mentioned below. Select the cell on which the comment appears. Right-click the cell and choose the Edit Comment from the available options. Modify the comment. Formatting Comment Various formatting options are available for comments. For formatting a comment, Right click on cell » Edit comment » Select comment » Right click on it » Format comment. With formatting of comment you can change the color, font, size, etc of the comment. Print Page Previous Next Advertisements ”;

Keyboard Shortcuts in Excel 2010 ”; Previous Next MS Excel Keyboard Short-cuts MS Excel offers many keyboard short-cuts. If you are familiar with windows operating system, you should be aware of most of them. Below is the list of all the major shortcut keys in Microsoft Excel. Ctrl + A − Selects all contents of the worksheet. Ctrl + B − Bold highlighted selection. Ctrl + I − Italicizes the highlighted selection. Ctrl + K − Inserts link. Ctrl + U − Underlines the highlighted selection. Ctrl + 1 − Changes the format of selected cells. Ctrl + 5 − Strikethrough the highlighted selection. Ctrl + P − Brings up the print dialog box to begin printing. Ctrl + Z − Undo last action. Ctrl + F3 − Opens Excel Name Manager. Ctrl + F9 − Minimizes the current window. Ctrl + F10 − Maximize currently selected window. Ctrl + F6 − Switches between open workbooks or windows. Ctrl + Page up − Moves between Excel work sheets in the same Excel document. Ctrl + Page down − Moves between Excel work sheets in the same Excel document. Ctrl + Tab − Moves between Two or more open Excel files. Alt + = − Creates a formula to sum all of the above cells Ctrl + ” − Inserts the value of the above cell into cell currently selected. Ctrl + Shift + ! − Formats the number in comma format. Ctrl + Shift + $ − Formats the number in currency format. Ctrl + Shift + # − Formats the number in date format. Ctrl + Shift + % − Formats the number in percentage format. Ctrl + Shift + ^ − Formats the number in scientific format. Ctrl + Shift + @ − Formats the number in time format. Ctrl + Arrow key − Moves to the next section of text. Ctrl + Space − Selects the entire column. Shift + Space − Selects the entire row. Ctrl + – − Deletes the selected column or row. Ctrl + Shift + = − Inserts a new column or row. Ctrl + Home − Moves to cell A1. Ctrl + ~ − Switches between showing Excel formulas or their values in cells. F2 − Edits the selected cell. F3 − After a name has been created F3 will paste names. F4 − Repeat last action. For example, if you changed the color of text in another cell pressing F4 will change the text in cell to the same color. F5 − Goes to a specific cell. For example, C6. F7 − Spell checks the selected text or document. F11 − Creates chart from the selected data. Ctrl + Shift + ; − Enters the current time. Ctrl + ; − Enters the current date. Alt + Shift + F1 − Inserts New Worksheet. Alt + Enter − While typing text in a cell pressing Alt + Enter will move to the next line allowing for multiple lines of text in one cell. Shift + F3 − Opens the Excel formula window. Shift + F5 − Brings up the search box. Print Page Previous Next Advertisements ”;

Set Background in Excel 2010 ”; Previous Next Background Image Unfortunately, you cannot have a background image on your printouts. You may have noticed the Page Layout » Page Setup » Background command. This button displays a dialogue box that lets you select an image to display as a background. Placing this control among the other print-related commands is very misleading. Background images placed on a worksheet are never printed. Alternative to Placing Background You can insert a Shape, WordArt, or a picture on your worksheet and then adjust its transparency. Then copy the image to all printed pages. You can insert an object in a page header or footer. Print Page Previous Next Advertisements ”;

Using Styles in Excel 2010 ”; Previous Next Using Styles in MS Excel With MS Excel 2010 Named styles make it very easy to apply a set of predefined formatting options to a cell or range. It saves time as well as make sure that look of the cells are consistent. A Style can consist of settings for up to six different attributes − Number format Font (type, size, and color) Alignment (vertical and horizontal) Borders Pattern Protection (locked and hidden) Now, let us see how styles are helpful. Suppose that you apply a particular style to some twenty cells scattered throughout your worksheet. Later, you realize that these cells should have a font size of 12 pt. rather than 14 pt. Rather than changing each cell, simply edit the style. All cells with that particular style change automatically. Applying Styles Choose Home » Styles » Cell Styles. Note that this display is a live preview, that is, as you move your mouse over the style choices, the selected cell or range temporarily displays the style. When you see a style you like, click it to apply the style to the selection. Creating Custom Style in MS Excel We can create new custom style in Excel 2010. To create a new style, follow these steps − Select a cell and click on Cell styles from Home Tab. Click on New Cell Style and give style name. Click on Format to apply formatting to the cell. After applying formatting click on OK. This will add new style in the styles. You can view it on Home »; Styles. Print Page Previous Next Advertisements ”;

Simple Charts in Excel 2010 ”; Previous Next Charts A chart is a visual representation of numeric values. Charts (also known as graphs) have been an integral part of spreadsheets. Charts generated by early spreadsheet products were quite crude, but thy have improved significantly over the years. Excel provides you with the tools to create a wide variety of highly customizable charts. Displaying data in a well-conceived chart can make your numbers more understandable. Because a chart presents a picture, charts are particularly useful for summarizing a series of numbers and their interrelationships. Types of Charts There are various chart types available in MS Excel as shown in the below screen-shot. Column − Column chart shows data changes over a period of time or illustrates comparisons among items. Bar − A bar chart illustrates comparisons among individual items. Pie − A pie chart shows the size of items that make up a data series, proportional to the sum of the items. It always shows only one data series and is useful when you want to emphasize a significant element in the data. Line − A line chart shows trends in data at equal intervals. Area − An area chart emphasizes the magnitude of change over time. X Y Scatter − An xy (scatter) chart shows the relationships among the numeric values in several data series, or plots two groups of numbers as one series of xy coordinates. Stock − This chart type is most often used for stock price data, but can also be used for scientific data (for example, to indicate temperature changes). Surface − A surface chart is useful when you want to find the optimum combinations between two sets of data. As in a topographic map, colors and patterns indicate areas that are in the same range of values. Doughnut − Like a pie chart, a doughnut chart shows the relationship of parts to a whole; however, it can contain more than one data series. Bubble − Data that is arranged in columns on a worksheet, so that x values are listed in the first column and corresponding y values and bubble size values are listed in adjacent columns, can be plotted in a bubble chart. Radar − A radar chart compares the aggregate values of a number of data series. Creating Chart To create charts for the data by below mentioned steps. Select the data for which you want to create the chart. Choose Insert Tab » Select the chart or click on the Chart group to see various chart types. Select the chart of your choice and click OK to generate the chart. Editing Chart You can edit the chart at any time after you have created it. You can select the different data for chart input with Right click on chart » Select data. Selecting new data will generate the chart as per the new data, as shown in the below screen-shot. You can change the X axis of the chart by giving different inputs to X-axis of chart. You can change the Y axis of chart by giving different inputs to Y-axis of chart. Print Page Previous Next Advertisements ”;

Pivot Tables in Excel 2010 ”; Previous Next Pivot Tables A pivot table is essentially a dynamic summary report generated from a database. The database can reside in a worksheet (in the form of a table) or in an external data file. A pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. Pivot tables are very powerful tool for summarized analysis of the data. Pivot tables are available under Insert tab » PivotTable dropdown » PivotTable. Pivot Table Example Now, let us see Pivot table with the help of example. Suppose you have huge data of voters and you want to see the summarized data of voter Information per party, then you can use the Pivot table for it. Choose Insert tab » Pivot Table to insert pivot table. MS Excel selects the data of the table. You can select the pivot table location as existing sheet or new sheet. This will generate the Pivot table pane as shown below. You have various options available in the Pivot table pane. You can select fields for the generated pivot table. Column labels − A field that has a column orientation in the pivot table. Each item in the field occupies a column. Report Filter − You can set the filter for the report as year, then data gets filtered as per the year. Row labels − A field that has a row orientation in the pivot table. Each item in the field occupies a row. Values area − The cells in a pivot table that contain the summary data. Excel offers several ways to summarize the data (sum, average, count, and so on). After giving input fields to the pivot table, it generates the pivot table with the data as shown below. Print Page Previous Next Advertisements ”;

Pivot Charts Excel 2010 ”; Previous Next Pivot Charts A pivot chart is a graphical representation of a data summary, displayed in a pivot table. A pivot chart is always based on a pivot table. Although Excel lets you create a pivot table and a pivot chart at the same time, you can’t create a pivot chart without a pivot table. All Excel charting features are available in a pivot chart. Pivot charts are available under Insert tab » PivotTable dropdown » PivotChart. Pivot Chart Example Now, let us see Pivot table with the help of an example. Suppose you have huge data of voters and you want to see the summarized view of the data of voter Information per party in the form of charts, then you can use the Pivot chart for it. Choose Insert tab » Pivot Chart to insert the pivot table. MS Excel selects the data of the table. You can select the pivot chart location as an existing sheet or a new sheet. Pivot chart depends on automatically created pivot table by the MS Excel. You can generate the pivot chart in the below screen-shot. Print Page Previous Next Advertisements ”;

Setting Cell Type in Excel 2010 ”; Previous Next Formatting Cell MS Excel Cell can hold different types of data like Numbers, Currency, Dates, etc. You can set the cell type in various ways as shown below − Right Click on the cell » Format cells » Number. Click on the Ribbon from the ribbon. Various Cell Formats Below are the various cell formats. General − This is the default cell format of Cell. Number − This displays cell as number with separator. Currency − This displays cell as currency i.e. with currency sign. Accounting − Similar to Currency, used for accounting purpose. Date − Various date formats are available under this like 17-09-2013, 17th-Sep-2013, etc. Time − Various Time formats are available under this, like 1.30PM, 13.30, etc. Percentage − This displays cell as percentage with decimal places like 50.00%. Fraction − This displays cell as fraction like 1/4, 1/2 etc. Scientific − This displays cell as exponential like 5.6E+01. Text − This displays cell as normal text. Special − Special formats of cell like Zip code, Phone Number. Custom − You can use custom format by using this. Print Page Previous Next Advertisements ”;

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

Special Symbols in Excel 2010 ”; Previous Next If you want to insert some symbols or special characters that are not found on the keyboard in that case you need to use the Symbols option. Using Symbols Go to Insert » Symbols » Symbol to view available symbols. You can see many symbols available there like Pi, alpha, beta, etc. Select the symbol you want to add and click insert to use the symbol. Using Special Characters Go to Insert » Symbols » Special Characters to view the available special characters. You can see many special characters available there like Copyright, Registered etc. Select the special character you want to add and click insert, to use the special character. Print Page Previous Next Advertisements ”;