Apply a Sum or Other Function to Every Row in a Data Range in One Step MS Excel
When you format a data range as a table in Microsoft Office Excel 2007, you can add a calculated column—a column of functions (such as sums or averages)—in just one step. To convert a data range to an Excel 2007 table, click in the range, and then on the Home tab, in the Styles section, click Format as Table, and then click to apply a table style.
When your range is formatted as a table, click in a cell of any empty table column, or a cell in the column directly to the right of the table, on any data row. Then in just that cell, add the function or formula you need. (For example, sum all data on that row of the table.) When you press ENTER to apply your formula in that cell, the entire table column is populated with the same formula, adjusted for each applicable data row. If your insertion point was in the column to the right of the table, Excel formats that column to become part of the table when it generates the calculated column.
Tampilkan postingan dengan label MS Excel. Tampilkan semua postingan
Tampilkan postingan dengan label MS Excel. Tampilkan semua postingan
Selasa, 18 November 2008
Selasa, 11 November 2008
How to Calculate the Matrix Determinant on the Fly in MS Excel
Calculating the Matrix Determinant on the Fly in MS Excel
1. Create a square matrix in Microsoft Office Excel with values.
2. Type an equal sign (=) followed by MDETERM in an empty cell. While typing, you’ll notice that Excel automatically gives you a list of functions beginning with what you have already written. Many more functions are available in Microsoft Office Excel 2007.
3. After the opening parenthesis, select your matrix data. You'll instantly see the determinant being calculated. Excel updates this value if you change the source data.
1. Create a square matrix in Microsoft Office Excel with values.
2. Type an equal sign (=) followed by MDETERM in an empty cell. While typing, you’ll notice that Excel automatically gives you a list of functions beginning with what you have already written. Many more functions are available in Microsoft Office Excel 2007.
3. After the opening parenthesis, select your matrix data. You'll instantly see the determinant being calculated. Excel updates this value if you change the source data.
How to Convert Text to Tables in MS Excel
Convert Text to Tables in MS Excel
Sometimes you may find different types of information within the same piece of text, where the only thing separating the information is a space. Microsoft Office Excel makes it easy to redistribute this data by using different columns, and to display the data in a more digestible format.
1. Choose delimiters and column separators.
a. Select the cells containing the data that you want to separate.
b. On the Data ribbon, in the Data Tools section, click the Text to Columns button.
c. Select your preferred data type (that is, Delimited or Fixed width) from the options in the first step presented by the wizard.
d. In the second step of the wizard, select your preferred delimiters from the Delimiters options, or the column separator position from the Fixed width options.
2. Define the data format of your new columns.
a. In the dialog box of the wizard’s third step, select one column at a time and define its data format by clicking your preferred option under Column data format.
b. Click the Finish button. The text chains will now be distributed into columns according to your chosen data format.
Sometimes you may find different types of information within the same piece of text, where the only thing separating the information is a space. Microsoft Office Excel makes it easy to redistribute this data by using different columns, and to display the data in a more digestible format.
1. Choose delimiters and column separators.
a. Select the cells containing the data that you want to separate.
b. On the Data ribbon, in the Data Tools section, click the Text to Columns button.
c. Select your preferred data type (that is, Delimited or Fixed width) from the options in the first step presented by the wizard.
d. In the second step of the wizard, select your preferred delimiters from the Delimiters options, or the column separator position from the Fixed width options.
2. Define the data format of your new columns.
a. In the dialog box of the wizard’s third step, select one column at a time and define its data format by clicking your preferred option under Column data format.
b. Click the Finish button. The text chains will now be distributed into columns according to your chosen data format.
Use Conditional Formatting to Highlight Data Trends in MS Excel
Use Conditional Formatting to Highlight Data Trends in MS Excel
Conditional formatting can help you highlight trends in your data. First, either open a fully populated Microsoft Office Excel file, or start with an empty document and fill its cells with some values. To apply conditional formatting:
1. Click the Conditional Formatting button on the Home tab of the Ribbon.
2. On the menu that appears, try selecting Data Bars, Icon Sets, or Color Scales and pause over the options. Your data is partitioned according to the rules you set, and it instantly displays visual cues that will aid understanding of the data.
3. If you don't like the options available, you can create your own rules, use your own colors, and establish your own thresholds. Better still, you can reuse your rules each time for consistent and clear presentation. All of these options are available on the Conditional Formatting menu.
Conditional formatting can help you highlight trends in your data. First, either open a fully populated Microsoft Office Excel file, or start with an empty document and fill its cells with some values. To apply conditional formatting:
1. Click the Conditional Formatting button on the Home tab of the Ribbon.
2. On the menu that appears, try selecting Data Bars, Icon Sets, or Color Scales and pause over the options. Your data is partitioned according to the rules you set, and it instantly displays visual cues that will aid understanding of the data.
3. If you don't like the options available, you can create your own rules, use your own colors, and establish your own thresholds. Better still, you can reuse your rules each time for consistent and clear presentation. All of these options are available on the Conditional Formatting menu.
How to Wrap Text in Excel 2007
How to Wrap Text in Excel 2007
In Microsoft Office Excel 2007, if the text you type is too long to be displayed fully in a cell, you can easily set the text to wrap. This setting enables the cell (and the rest of the row) to expand automatically so that the content of the cell is always visible.
1. Click in the cell that you want to wrap.
2. On the Home tab, in the Alignment section, click Wrap Text.
In Microsoft Office Excel 2007, if the text you type is too long to be displayed fully in a cell, you can easily set the text to wrap. This setting enables the cell (and the rest of the row) to expand automatically so that the content of the cell is always visible.
1. Click in the cell that you want to wrap.
2. On the Home tab, in the Alignment section, click Wrap Text.
Sabtu, 25 Oktober 2008
How to Add and Editing Comments in MS Excel
Adding and Editing Comments in MS Office Excel
You can add and edit comments in Microsoft Office Excel by using the keyboard. To add a comment:
1. Press SHIFT+F2 to insert a comment in the current cell.
2. Type the text of your comment, and then press ESC twice.
To edit a comment:
1. Go to the cell that contains the comment and press SHIFT+F2. The comment opens.
2. Make your changes, and then press ESC twice.
You can add and edit comments in Microsoft Office Excel by using the keyboard. To add a comment:
1. Press SHIFT+F2 to insert a comment in the current cell.
2. Type the text of your comment, and then press ESC twice.
To edit a comment:
1. Go to the cell that contains the comment and press SHIFT+F2. The comment opens.
2. Make your changes, and then press ESC twice.
Jumat, 03 Oktober 2008
How to Select Excel Ranges by Using the Name Box
Selecting Excel Ranges by Using the Name Box
In Microsoft Office Excel, you can select a range of cells by typing it in the Name box. This is particularly useful if the range is large and will therefore take time and a lot of scrolling to select by hand
In Microsoft Office Excel, you can select a range of cells by typing it in the Name box. This is particularly useful if the range is large and will therefore take time and a lot of scrolling to select by hand
Selasa, 30 September 2008
How to Set the Print Area in Microsoft Office Excel
Setting the Print Area in Microsoft Office Excel
Have you ever wanted to print only certain rows or columns from a spreadsheet? Here’s how:
1. With a spreadsheet open, select all the rows or columns that you want to print.
2. On the Page Layout tab, in the Page Setup section, click Print Area, and then click Set Print Area.
3. Click the Print icon on the toolbar, or click the Office button, point to Print, and click Print. Only the required content gets printed.
Have you ever wanted to print only certain rows or columns from a spreadsheet? Here’s how:
1. With a spreadsheet open, select all the rows or columns that you want to print.
2. On the Page Layout tab, in the Page Setup section, click Print Area, and then click Set Print Area.
3. Click the Print icon on the toolbar, or click the Office button, point to Print, and click Print. Only the required content gets printed.
How to Use the Status Bar to Show Range Data in MS Excel
Use the Status Bar to Show Range Data in MS Excel
In Microsoft Office Excel 2007, when you select a range of numbers, three data counts (Average, Count, and Sum) appear automatically on the status bar. By right-clicking the status bar, you can customize the parameters that the bar displays. There are plenty of parameters to choose from, including minimums and maximums
In Microsoft Office Excel 2007, when you select a range of numbers, three data counts (Average, Count, and Sum) appear automatically on the status bar. By right-clicking the status bar, you can customize the parameters that the bar displays. There are plenty of parameters to choose from, including minimums and maximums
Senin, 29 September 2008
How to Find the Correct Format for Excel Formulas in MS Excel
Find the Correct Format for Excel Formulas in MS Excel
You can use Microsoft IntelliSense technology in Microsoft Office Excel 2007 to create formulas. If you want to type a formula in Excel 2007, start by typing the first few characters of the formula (for example, an equal sign and then a letter) to view a list of available formulas. Excel 2007 makes it easy to see all the available types of formulas; the enhanced tooltips in the 2007 Office system provide a detailed explanation of each formula.
You can use Microsoft IntelliSense technology in Microsoft Office Excel 2007 to create formulas. If you want to type a formula in Excel 2007, start by typing the first few characters of the formula (for example, an equal sign and then a letter) to view a list of available formulas. Excel 2007 makes it easy to see all the available types of formulas; the enhanced tooltips in the 2007 Office system provide a detailed explanation of each formula.
How to Publish Items to Excel Services in MS Excel
Publishing Items to Excel Services in MS Excel
To publish your Microsoft Office Excel spreadsheets (or single items) to Microsoft Office SharePoint Server 2007:
1. With your spreadsheet open, click the Office button, and then click Publish.
2. Click Excel Services.
3. In the dialog box, select a trusted document library location in Office SharePoint Server 2007, and then click Excel Services Options.
4. Select whether you want to publish the entire spreadsheet, individual worksheets, or only selected items (such as charts, tables, or Microsoft PivotTable dynamic views). Click OK.
Your spreadsheet will be saved to the document library, and only your published items will be visible through the Web browser interface
To publish your Microsoft Office Excel spreadsheets (or single items) to Microsoft Office SharePoint Server 2007:
1. With your spreadsheet open, click the Office button, and then click Publish.
2. Click Excel Services.
3. In the dialog box, select a trusted document library location in Office SharePoint Server 2007, and then click Excel Services Options.
4. Select whether you want to publish the entire spreadsheet, individual worksheets, or only selected items (such as charts, tables, or Microsoft PivotTable dynamic views). Click OK.
Your spreadsheet will be saved to the document library, and only your published items will be visible through the Web browser interface
Kamis, 25 September 2008
How to Manage Rules in MS Excel
Managing Rules in MS Excel
To view conditional formatting rules or to perform changes:
1. On the Home tab, in the Styles group, click Conditional Formatting, and then click Manage Rules.
2. Select the rule that you want to view or modify, and then click Edit Rule.
3. Edit the rule in the dialog box, and then click OK.
To view conditional formatting rules or to perform changes:
1. On the Home tab, in the Styles group, click Conditional Formatting, and then click Manage Rules.
2. Select the rule that you want to view or modify, and then click Edit Rule.
3. Edit the rule in the dialog box, and then click OK.
How to Apply Conditional Formatting in MS Excel
Applying Conditional Formatting in MS Excel
Conditional formatting is a feature that enables users to apply formatting to cells automatically depending on the value of the cell or the value of a formula. By visualizing data in a graphical way, you can identify trends and exceptions more quickly.
To visualize the value of a cell relative to other cells in the same range:
1. Select a range of cells.
2. On the Home tab, in the Style group, click Conditional Formatting, click Data Bars, and then select a data bar icon.
To classify data into three to five categories separated by a threshold value, use icon sets:
1. Select a range of cells.
2. On the Home tab, in the Style group, click Conditional Formatting, click Icon Set, and then select an icon set.
Conditional formatting is a feature that enables users to apply formatting to cells automatically depending on the value of the cell or the value of a formula. By visualizing data in a graphical way, you can identify trends and exceptions more quickly.
To visualize the value of a cell relative to other cells in the same range:
1. Select a range of cells.
2. On the Home tab, in the Style group, click Conditional Formatting, click Data Bars, and then select a data bar icon.
To classify data into three to five categories separated by a threshold value, use icon sets:
1. Select a range of cells.
2. On the Home tab, in the Style group, click Conditional Formatting, click Icon Set, and then select an icon set.
How to Insert and Format Charts in MS Excel
Insert and Format Charts in MS Excel
In Microsoft Office Excel 2007, you will find many new professional-looking charts to help you visualize your data and communicate it more effectively. To insert a chart:
1. On the worksheet, arrange the data that you want to use in a chart.
2. Select the cells that contain the data that you want to use for the chart.
3. On the Insert tab, in the Charts group, click the chart type, and then click a chart subtype that you want to use.
To see all available chart types, click a chart type, and then click All Chart Types. Excel will select the default formatting layout, but you can quickly modify it:
1. On the contextual Design tab, select a chart layout from the gallery to display the legend, axis, and title the way you want.
2. Select a chart style to modify color and effects.
In Microsoft Office Excel 2007, you will find many new professional-looking charts to help you visualize your data and communicate it more effectively. To insert a chart:
1. On the worksheet, arrange the data that you want to use in a chart.
2. Select the cells that contain the data that you want to use for the chart.
3. On the Insert tab, in the Charts group, click the chart type, and then click a chart subtype that you want to use.
To see all available chart types, click a chart type, and then click All Chart Types. Excel will select the default formatting layout, but you can quickly modify it:
1. On the contextual Design tab, select a chart layout from the gallery to display the legend, axis, and title the way you want.
2. Select a chart style to modify color and effects.
Senin, 22 September 2008
How to Chang Table Styles in MS Excel
Changing Table Styles in MS Excel
To change table style:
1. Click inside the table you’d like to change.
2. On the contextual Design tab, pause on any table style for a live preview, and click when you want to confirm the change (click the arrows in the gallery to view more styles).
3. Make selections in the Table Style Options group to change formatting of the first and last column, to add a Totals Row, or to have banded rows or columns.
To change table style:
1. Click inside the table you’d like to change.
2. On the contextual Design tab, pause on any table style for a live preview, and click when you want to confirm the change (click the arrows in the gallery to view more styles).
3. Make selections in the Table Style Options group to change formatting of the first and last column, to add a Totals Row, or to have banded rows or columns.
Selasa, 16 September 2008
How to Increase 2003 to 2007 Worksheet Capacity in MS Excel
2003 to 2007 Increased Worksheet Capacity in MS Excel
Do you regularly work with data sets larger than the capacity of 65,536 rows by 256 columns in Microsoft Office Excel 2003? Microsoft Office Excel 2007 supports up to 1,048,576 rows by 16,384 columns, so you no longer have to use many different worksheets to support your data. You can use the zoom slider at the lower right of the Office Excel 2007 window to zoom out for a bird's-eye view of how much data your worksheet can hold.
To support a bigger grid, Office Excel 2007 uses several other increased limits. For example, the number of rows allowed in a Microsoft PivotTable chart has increased from 64,000 to more than 1 million, and the amount of memory that Excel can use has increased from 1 gigabyte (GB) to the maximum allowed by the Windows operating system. The limit on the number of rows in a column or columns that can be referred to in an array formula has been completely eliminated.
Do you regularly work with data sets larger than the capacity of 65,536 rows by 256 columns in Microsoft Office Excel 2003? Microsoft Office Excel 2007 supports up to 1,048,576 rows by 16,384 columns, so you no longer have to use many different worksheets to support your data. You can use the zoom slider at the lower right of the Office Excel 2007 window to zoom out for a bird's-eye view of how much data your worksheet can hold.
To support a bigger grid, Office Excel 2007 uses several other increased limits. For example, the number of rows allowed in a Microsoft PivotTable chart has increased from 64,000 to more than 1 million, and the amount of memory that Excel can use has increased from 1 gigabyte (GB) to the maximum allowed by the Windows operating system. The limit on the number of rows in a column or columns that can be referred to in an array formula has been completely eliminated.
How to Create a Hyperlink in MS Excel
To Create a Hyperlink in MS Excel
In Microsoft Office Excel 2007, you will find a style gallery in the Ribbon. A cell style combines a preset font format, a cell background, and borders. To apply a cell style to a cell:
1. Click the Insert tab on the Ribbon.
2. In the Links section, click Hyperlink.
3. In the Address box, type the URL of the Web page to which you want to link.
NOTE: You can also use the controls in the Insert Hyperlink dialog box to create hyperlinks to locations within the current workbook or to other documents, and links that can be used to send an e-mail message.
In Microsoft Office Excel 2007, you will find a style gallery in the Ribbon. A cell style combines a preset font format, a cell background, and borders. To apply a cell style to a cell:
1. Click the Insert tab on the Ribbon.
2. In the Links section, click Hyperlink.
3. In the Address box, type the URL of the Web page to which you want to link.
NOTE: You can also use the controls in the Insert Hyperlink dialog box to create hyperlinks to locations within the current workbook or to other documents, and links that can be used to send an e-mail message.
How to Enter a Series of Data Using Fill Series in MS Excel
To Enter a Series of Data Using Fill Series in MS Excel
1. Type the first value in the series into a cell.
2. Type the second value into a cell below or to the right of the first cell.
3. Select the cells.
4. Drag the Fill Handle (a black square that appears at the bottom right corner of the last selected cell) over the cells into which you want to extend the series.
1. Type the first value in the series into a cell.
2. Type the second value into a cell below or to the right of the first cell.
3. Select the cells.
4. Drag the Fill Handle (a black square that appears at the bottom right corner of the last selected cell) over the cells into which you want to extend the series.
Senin, 08 September 2008
How To Enter the Same Value into Multiple Cells Simultaneously in MS Excel
To Enter the Same Value into Multiple Cells Simultaneously in MS Excel
1. Select the cells into which you want to enter the value.
2. Type the value in the active cell.
3. Press CTRL+ENTER.
1. Select the cells into which you want to enter the value.
2. Type the value in the active cell.
3. Press CTRL+ENTER.
How to See that How a Query Is Progressing
See How a Query Is Progressing in MS office Excel
Because Microsoft Office Excel 2007 can now display more than 1 million rows in a spreadsheet, you can run queries that return a lot of data and therefore take a long time. By setting a query to refresh in the background, you can still work in Excel while data is being retrieved.
On the left side of the status bar is a little spinning globe, which lets you know that the query is still running. Clicking the globe opens a dialog box where you can see the queries that are being executed in the background, how long they have been running, and how many records have been returned so far. You can even stop the query if you want to.
Because Microsoft Office Excel 2007 can now display more than 1 million rows in a spreadsheet, you can run queries that return a lot of data and therefore take a long time. By setting a query to refresh in the background, you can still work in Excel while data is being retrieved.
On the left side of the status bar is a little spinning globe, which lets you know that the query is still running. Clicking the globe opens a dialog box where you can see the queries that are being executed in the background, how long they have been running, and how many records have been returned so far. You can even stop the query if you want to.
Langganan:
Postingan (Atom)