Excelexpertsonline

How to Create a Cover Page in Microsoft Word 2007

2009-09-18T09:45:00.001+01:00

When you create reports, books and other long documents in Microsoft Word, you may want to include a cover page at the beginning of the document. A cover page usually includes a title, author, purpose of the document, date created and/or other general information. The use of a cover page adds a professional touch to the document. In older versions of Microsoft Word, you had to manually create your cover page by figuring out how to place the text, and add your own borders if desired. However, Microsoft Word 2007 has a very nice cover page feature with several pre-formatted options to make it really easy to create a nice, professional cover page.

Easy Instructions

1. Step 1
Open your document in Microsoft Word 2007.
2. Step 2
Select the "Insert Menu." In the "Pages" section on the left-hand side, click on "Cover Page."
3. Step 3
A menu will pop up with a number of pre-formatted cover pages. Scroll through them using the scroll bar on the right until you find one you like that suits your purpose.
4. Step 4
Click on the cover page template you want. Your cover page will appear at the beginning of your document.
5. Step 5
Click on each field, designated by brackets with descriptions such as "Title" and "Abstract." With one click, the whole field will be highlighted. Simply start typing what you want to appear there. When you click on the date or year field, if there is one on the template you chose, a down-pointing arrow will appear on the right. Click on that, and a calendar will appear. Click on the date you want. You should make all your changes to the fields before you click "Save," because they will be converted to text.
6. Step 6
Customize your text as you normally do with any other text. Change the size and color, add bold or underline. Microsoft Word 2007 uses text boxes and tables for some of the templates, so click on the "Format Menu" that appears when you are on the cover page. This will enable you to adjust things like the size and shapes of the text boxes or the "Design Menu" to change the look of the tables.
7. Step 7
When you are finished making changes to your cover page, click "Save" and continue working in your document as needed. The cover page will convert to text and become a part of your document.

Shortcut Commands 101

2009-09-17T16:26:00.004+01:00

CTRL combination shortcut keys---Shortcut Command

CTRL+SHFT+( Unhides any hidden rows within the selection. + –
CTRL+SHFT+) Unhides any hidden columns within the selection. + –
CTRL+SHFT+& Applies the outline border to the selected cells. + –
CTRL+SHFT_ Removes the outline border from the selected cells. + –
CTRL SHFT ~ Applies the General number format in Microsoft Excel. + –
CTRL+SHFT+$ Applies the Currency format with two decimal places (negative numbers in parentheses). + –
CTRL+SHFT+% Applies the Percentage format with no decimal places. + –
CTRL+SHFT+^ Applies the Exponential number format with two decimal places. + –
CTRL+SHFT+# Applies the Date format with the day, month, and year. + –

Microsoft Excel 2007
CTRL+SHFT+( Unhides any hidden rows within the selection. + –
CTRL+SHFT+) Unhides any hidden columns within the selection. + –
CTRL+SHFT+& Applies the outline border to the selected cells. + –
CTRL+SHFT_ Removes the outline border from the selected cells. + –
CTRL SHFT ~ Applies the General number format in Microsoft Excel. + –
CTRL+SHFT+$ Applies the Currency format with two decimal places (negative numbers in parentheses). + –
CTRL+SHFT+% Applies the Percentage format with no decimal places. + –
CTRL+SHFT+^ Applies the Exponential number format with two decimal places. + –
CTRL+SHFT+# Applies the Date format with the day, month, and year. + –

CONCATENATE

2009-09-17T16:15:00.001+01:00

Joins several text strings into one text string.

Syntax

CONCATENATE (text1,text2,...)

Text1, text2, ... are 1 to 30 text items to be joined into a single text item. The text items can be text strings, numbers, or single-cell references.
Remarks

You can also use the ampersand (&) calculation operator instead of the CONCATENATE function to join text items. For example, =A1&B1 returns the same value as =CONCATENATE(A1,B1).

Example
The example may be easier to understand if you copy it to a blank worksheet.
How to copy an example
1. Create a blank workbook or worksheet.
2. Select the example in the Help topic.
Note Do not select the row or column headers.

Selecting an example from Help
3. Press CTRL+C.
4. In the worksheet, select cell A1, and press CTRL+V.
5. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.

1
2
3
4
A
Data
brook trout
species
32
Formula Description
=CONCATENATE("Stream population for ",A2," ",A3," is ",A4,"/mile")
Concatenates a sentence from the data above (Stream population for brook trout species is 32/mile)

Start numbering on the second page with 1 (WORD 2007)

2009-07-02T21:53:00.002+01:00

1. Follow the steps for removing the page number from the first page.
2. On the Insert tab, in the Headers & Footers group, click Page Number, and then click Format Page Numbers.
3. In the Start at box, type 0.

Organize Your Records the Way You Want Them in Excel

2009-04-29T12:02:00.001+01:00

You can customize how you sort your records by using a filter. To activate a filter:
1. In Microsoft Office Excel 2007, click Filter on the Data tab.

2. To choose your sorting options, click the filter arrow on the column that you want to sort, point to Sort by Color, and then click Custom Sort.

3. Create a customized sort order.
a. In the Sort dialog box, under Column, click the drop-down arrow next to Sort by, and choose the field that you want to be sorted. Values should appear in the Sort On field.
b. In the Order field, click Custom List. This will open a window that offers an entirely custom sort list.
c. In the left pane, list the values (each separated by a comma) in the order that you want them to be sorted, and then click Add.
d. In the right pane, select the list you've created, and then click OK.
e. In the Sort dialog box, click OK.

From now on, your records will be sorted according to your customized list.

Easy Filtering of Data in a Microsoft PivotTable View

2009-04-29T11:53:00.001+01:00

There is a better way to select multiple report filters than dragging the filter down to a row. It is quicker to select additional filters directly from the original filter.

1. Select the filter, and then click multiple items at the bottom.

2. The filter selection changes to check boxes. Simply select the boxes you require.

Change the Default File Location

2009-02-02T11:48:00.000+01:00

Many users find that they tend to open workbooks from the same folder on their computer. If that Windows Vista folder isn't Documents (My Documents in Windows XP), you can change the folder that Microsoft Office Excel 2007 displays when you want to open or save a workbook. Here’s how:
1. Click the Office button.
2. Click Excel Options.
3. In the left column, click Save.
4. Type the desired location in the Default file location box.

Fill In the Blanks in Spreadsheets the Easy Way

2009-02-02T11:12:00.000+01:00

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.

Insert and Format Charts

2008-11-19T19:13:00.001+01:00

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.

Move the Cursor in Any Direction During Data Entry

2008-07-30T19:15:00.001+01:00

By default, the cell pointer in Microsoft Office Excel moves down when you press ENTER after entering data in a cell. You can, however, change the direction in which the cell pointer moves.

1. Click the Office button, click Excel Options, and then click Advanced.
2. Select the After pressing Enter, move selection check box if it isn't already selected, and then on the Direction menu, click the direction in which you want the pointer to move.

Also, when working in a document, you can make the pointer move in the opposite direction from the one you've chosen by holding down the SHIFT key while you press ENTER.

Change the Default File Location

2008-07-25T08:57:00.001+01:00

To Change the Color of a Sheet Tab

2008-07-08T14:51:00.001+01:00

1. Right-click the sheet tab.

2. Click Tab Color.

3. Click the color from the palette that appears.

View All the Text in Your Columns

2008-07-08T14:36:00.001+01:00

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.

Creating a Watermark in Excel

2008-07-07T17:54:00.001+01:00

To add a watermark at the top of your Microsoft Office Excel worksheet:

1. In your Excel file, click Insert, and then in the Text section, click Header & Footer.

2. With your pointer in the center section of the header, click Picture in the Header & Footer Elements section of the Design tab, browse your hard disk drive to find the picture you want, and then click OK.

3. You may need to press ENTER several times to center the watermark on the page.

Convert Text to Tables

2008-07-03T11:18:00.002+01:00

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.

COUNT FUNCTIONS

2008-07-01T10:40:00.003+01:00

This formulas help you in calculating
the various Count functions on excel.

I wish you all the best while you learn
Microsoft Excel with me.

Organize Your Records the Way You Want Them in Excel

2008-07-01T08:47:00.003+01:00

You can customize how you sort your records by using a filter.

To activate a filter:

1. In Microsoft Office Excel 2007, click Filter on the Data tab.

2. To choose your sorting options, click the filter arrow on the column that you want to sort, point to Sort by Color, and then click Custom Sort.

3. Create a customized sort order.

a. In the Sort dialog box, under Column, click the drop-down arrow next to Sort by, and choose the field that you want to be sorted. Values should appear in the Sort On field.

b. In the Order field, click Custom List. This will open a window that offers an entirely custom sort list.

c. In the left pane, list the values (each separated by a comma) in the order that you want them to be sorted, and then click Add.

d. In the right pane, select the list you've created, and then click OK.

e. In the Sort dialog box, click OK.From now on, your records will be sorted according to your customized list.

Turn Your Data Range into a Table for Easy Data Management and Formatting

2008-06-30T10:26:00.001+01:00

The new tables functionality in Microsoft Office Excel 2007 provides options for formatting and managing your data more easily and dynamically than ever before.

To convert a data range to a table:

1. Click anywhere in the range.

2. On the Home tab, in the Styles section, click Format as Table.

3. Click to apply a table style. The Format as Table dialog box appears, where you can confirm the data range and indicate whether your range contains headers. Click OK in this dialog box to convert the range to a table and apply selected formatting.

Notice that table headers automatically contain AutoFilter arrows. Additionally, when you click into a table, the Table Tools Design tab becomes available, from which you can edit the table style, add options such as banded columns or a total row, and perform other data management tasks.

Apply a Sum or Other Function to Every Row in a Data Range in One Step

2008-06-30T10:19:00.001+01:00

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.

Turn Your Data Range into a Table for Easy Data Management and Formatting

2008-06-27T13:29:00.001+01:00

The new tables functionality in Microsoft Office Excel 2007 provides options for formatting and managing your data more easily and dynamically than ever before.To convert a data range to a table:

1. Click anywhere in the range.

2. On the Home tab, in the Styles section, click Format as Table.

3. Click to apply a table style. The Format as Table dialog box appears, where you can confirm the data range and indicate whether your range contains headers. Click OK in this dialog box to convert the range to a table and apply selected formatting.

Notice that table headers automatically contain AutoFilter arrows. Additionally, when you click into a table, the Table Tools Design tab becomes available, from which you can edit the table style, add options such as banded columns or a total row, and perform other data management tasks.

EXCEL TIPS AND TRICKS 3

2008-06-26T13:58:00.001+01:00

To Fit a Printout onto a Set Number of Worksheet Pages

1. Click the Page Layout tab on the Ribbon.

2. In the Scale to Fit section, in the Width box, type the number of pages you want the printout to span horizontally.

3. In the Height box in the same section, type the number of pages you want the printout to span vertically.

Print an Entire Worksheet Even If You Defined a Print Area

1. Click the Office button.

2. Click Print.

3. In the Print what section, select the Ignore print areas check box.

EXCEL TIPS AND TRICKS 2

2008-06-25T16:36:00.001+01:00

To Find Text Formatted Using a Specific Format

1. Click the Home tab on the Ribbon.
2. In the Editing section, click Find & Select.
3. Click Find.
4. In the Find and Replace dialog box, click Options.
5. Click Format.
6. In the Find Format dialog box, specify the format for which you want to search.
7. Click OK.Note: You can also define the format you want to find by clicking Choose Format From Cell in the Find Format dialog box, and then clicking a cell with the desired format.

EXCEL TIPS AND TRICKS 1

2008-06-24T18:34:00.003+01:00

Paste into Non-sequential Cells in Excel

You can use the CTRL key for multiple selections. Sometimes, you want to copy a formula or piece of data into a series of non-sequential cells in Microsoft Office Excel. You can do this quickly without having to paste into each cell individually.

1. Copy the data from the source cell.

2. Hold down the CTRL key as you click to select each destination cell.

3. After all the cells are highlighted, paste the data by pressing CTRL+V. You have to paste only once.

Similarly, you can type data into a series of cells simultaneously.1. While holding down the CTRL key, click all the cells that you want to type the same text (or value) into.2. Type the entry, and then press CTRL+ENTER. The text will be added to all the selected cells.

Excel’s Counting and Summing Functions

2008-06-24T16:37:00.003+01:00

Function Description

* These are new functions, available only in Excel 2007.

COUNT
Returns the number of cells that contain a numeric value

COUNTA
Returns the number of nonblank cells

COUNTBLANK
Returns the number of blank cells

COUNTIF
Returns the number of cells that meet a specified criterion

COUNTIFS*
Returns the number of cells that meet multiple criteria

DCOUNT
Counts the number of records that meet specified criteria; used with a worksheet database.

DCOUNTA
Counts the number of nonblank records that meet specified criteria; used with a worksheet
database.

DEVSQ
Returns the sum of squares of deviations of data points from the sample mean; used primarily
in statistical formulas

DSUM
Returns the sum of a column of values that meet specified criteria; used with a worksheet
database.
FREQUENCY
Calculates how often values occur within a range of values and returns a vertical array of
numbers. Used only in a multicell array formula,

SUBTOTAL
When used with a first argument of 2, 3, 102, or 103, returns a count of cells that comprise a
subtotal; when used with a first argument of 9 or 109, returns the sum of cells that comprise
a subtotal

SUM
Returns the sum of its arguments

SUMIF
Returns the sum of cells that meet a specified criterion
SUMIFS*
Returns the sum of cells that meet multiple criteria

Excel -- Conditional Formatting -- Based on another cell

2008-01-25T12:19:00.000+01:00

Apply Conditional Formatting to a Row
You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, you could colour the entire row in a table, if the values in column B are over a set value.

1. Select the cells to be formatted (A2:D4 in this example)2. Choose Format>Conditional Formatting...
3. From the first drop-down list, choose Formula Is4. In the text box, enter a formula that refers to the active cell in the selection.
In this example, the formula is: =$B2>75
Use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B. Otherwise, the formula will be adjusted in each column, and won't work properly.

5. Click the Format button.6. Select the formatting options, click OK7. Click OK