How I can View All the Text in my Columns

76. How to use Fill In the Blanks in Spreadsheets the Easy Way
77. How to View All the Text in Your Columns
78. How to Fill a Selected Range with the Same Data

76. Fill In the Blanks in Spreadsheets the Easy Way

1. Select all the rows in your Microsoft Office Excel spreadsheet data set.
2. On the Home tab, in the Editing section, click Find & Select, click Go To, click Special, click Blanks, and then click OK.
3. Click the equal sign (=) on the toolbar, select the cell above, and then hold down the CTRL key while you press ENTER. Blanks in the spreadsheet will be filled automatically.
4. Copy all the data, click Paste Special, and then click Values. This will overwrite the formulas and enable you to sort the data by filling in each empty cell with the same information as the one above. This tip will work on any size of data set.

77. View All the Text in Your Columns
In Microsoft Office Excel, you can resize multiple columns to different widths so that you can see all the text in your spreadsheet.
1. Select the columns where your text appears by clicking the column headings. Select multiple columns if necessary.
2. To resize the columns to fit, double-click in the column heading, exactly on top of the border that divides any of the two columns you selected.
All of your selected columns are resized based on the longest text string in each column. In this way, a large and confusing spreadsheet with lots of text that you can't see is converted into a legible spreadsheet with all text showing.

78. Fill a Selected Range with the Same Data
Need to enter the same data in a range of cells? Here’s how:
1. Select a range of cells.
2. Type in some data.
3. Use the fill function in Microsoft Office Excel—press CTRL+ENTER, and the range selected will be filled with the same data.

How I can Set the Print Area in Microsoft Office Excel

73. How to Set the Print Area in Microsoft Office Excel
74. How to Create a Linear Series of Values in Excel
75. How to Change Gridline Colors

73. 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.

74. Create a Linear Series of Values in Excel
Here’s how to use Auto Fill to create a series of values:
1. Create the initial series. In a Microsoft Office Excel workbook, type 1 in the A1 cell and 2 in the A2 cell.
2. Select cells A1 and A2.
3. Click the Auto Fill handle (the little black box in the lower-right corner of the selection) and drag to A15 to fill the cells with the numbers 1 through 15. You can create different series of numbers, odd and even numbers, or even dates.

75. Changing Gridline Colors
You can change the look of your worksheet.
1. With a Microsoft Office Excel worksheet open, click the Office button, click Excel Options, and then click Advanced.
2. Under Display options for this worksheet, select the gridline color of your choice.
3. Click OK.

How I can find Correct Format for Excel Formulas

64. How to Find the Correct Format for Excel Formulas
65. How I can Easily Insert Rows or Columns in Excel
66. How to Previewing Charts in Grayscale

64. Find the Correct Format for Excel Formulas
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.

65. Easily Insert Rows or Columns in Excel
With a Microsoft Office Excel document open, you can insert extra rows and columns by holding down the SHIFT key while you grab a cell by its handle and drag it. Excel will insert as many cells as needed

66. Previewing Charts in Grayscale
You can preview your charts in Microsoft Office Excel before you print.
1. Open the file that you want to print, click the Office button, point to Print, and then click Print
Preview.
2. On the Print Preview ribbon, click Page Setup, click Sheet, and then click Black and white. The
preview will be in grayscale so that you can evaluate whether the contrast is good enough to distinguish the lines, bars, and columns.

How I can Identify Duplicate Values in an Excel Table

55. How to Identify Duplicate Values in an Excel Table
56. How to See if a Query Is Progressing
57. How to Select Excel Ranges by Using the Name Box

55. Identify Duplicate Values in an Excel Table
In Microsoft Office Excel 2007, you can easily highlight duplicate values with conditional formatting.
1. Select the list in which you want to identify duplicates.
2. Click the Home tab on the Ribbon.
3. In the Styles section, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values. Pairs of duplicates will then be highlighted, and you can choose which of each pair to delete

56. See How a Query Is Progressing
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.

57. 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.

How I can Publish Items to Excel Services, Sorting And Filtering, And Create Pivot Table View

Microsoft Excel Tips
40. How to Publish Items to Excel Services
41. How to use Sorting and Filtering
42. How to Create PivotTable View

40. Publishing Items to Excel Services
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.

41. Sorting and Filtering
New options for sorting and filtering make Microsoft Office Excel 2007 the ideal tool for working with large amounts of complex data. The sorting and filtering menu is sensitive to the type of data you’re working with. To sort and filter:
1. To enable filter and sort options, do one of the following:
a. Format your data range as a table.
b. Select the headings of the columns that you want to sort or filter.
c. On the Ribbon, on the Home tab, in the Editing group, click Sort and Filter.

2. To sort, click the AutoFilter arrow in the column heading, and then select the filter mode (in Office Excel 2007, you can also filter by cell color).

3. To filter, click the AutoFilter arrow in the column heading.
a. Select individual values, and then select the filter mode.
b. Select Number Filters, Text Filters, or Date Filters for more advanced filter options.

42. Creating a PivotTable View
Microsoft PivotTable dynamic views help you change the axis that your data is organized around. To create a PivotTable view:
1. Click anywhere inside the range of data.
2. On the Ribbon, on the Insert tab, click PivotTable.
3. In the PivotTable Field List pane, check the fields that you want to visualize.
4. Rearrange your fields by dragging the row labels, column labels, and values to where you want them.
5. To add a filter, check the desired field, and drag it to the Report Filter box.

Microsoft Office Excel will display the sum of the data in the value field, but you can quickly modify the field to track other totals types, such as average, minimum value, or maximum value:
1. In the Values box, click the arrow next to the value that you want to modify.
2. Select Value Field Settings.
3. Change the setting from Sum to the desired type of calculation.

How I can Enter the Same Value into Multiple Cells Simultaneously in Microsoft Excel

16. How to Add Rows and Columns to Spreadsheets
17. How To Enter the Same Value into Multiple Cells Simultaneously in Microsoft Excel
18. How to Avoid the Unnecessary Use of Menus in Excel and Word


16. To Enter the Same Value into Multiple Cells Simultaneously in Microsoft 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.

17. Add Rows and Columns to Spreadsheets
To add a row or column: Select any row or column, and then press the CTRL key at the same time as the PLUS SIGN (+) key on the numeric keypad.
To delete a row or column: Select the row or column, and then press the CTRL key at the same time as the MINUS SIGN (–) key on the numeric keypad.

18. Avoid the Unnecessary Use of Menus in Excel and Word
1. In your Microsoft Office Excel and Microsoft Office Word programs, place the mouse over the status bar at the foot of the program window.
2. Right-click to reveal the Customize Status Bar menu, and then select which options you want to appear on the bar.