How to Use Excel's Concatenate Function

1. Learn the syntax for Concatenate. It is Concatenate (text_1,text_2,...text_n) where text_1,text_2,...text_n are 1 to 30 text items that will be joined together. These values may be numbers, text strings or references to single cells.
2. Use the ampersand () as the calculation operator. This method also may be used to join text items instead of the Concatenate function. For example, =A1A2 will return the same result as =Concatenate (A1,A2).
3. Look at an example for Concatenate. Set A2=salmon, A3=species, A4=25 and enter =Concatenate('Stream population for ',A2,' ',A3,' is ',A4,'/mile') in a cell. This formula will return a result of 'Stream population for salmon species is 25/mile'.
4. Study the use of cell ranges for Concatenate. Enter =Concatenate(A2:A3) in cell B2. Notice that Concatenate only displays salmon in cell B2 because it only uses text strings that are in the results row. You would need to use an appropriate Concatenate formula on each row in order to use fields on those rows.
5. Enter a Concatenate formula automatically. Position the cursor in the cell you wish to use, select the Insert menu on the menu bar and select 'Function' from the 'Insert Sub-menu.' Select the 'Text' category and then the 'Concatenate' function. Supply the arguments as prompted and click the 'OK' button to display the results

Read more ►

How to Preview an Excel Worksheet

1. Start Microsoft Excel and open a spreadsheet that you are ready to print.
2. Choose the 'File' menu. Select 'Print Preview' to enter 'Print Preview' view.
3. Navigate through your Excel spreadsheet pages by using the 'Next' and 'Preview' buttons at the top of the 'Print Preview' view.
4. Zoom on your Excel spreadsheet by clicking the 'Zoom' button while in the 'Print Preview' view. Click on 'Zoom' once to magnify the spreadsheet page you are on. Click it again to return to full-page view.
5. Print the spreadsheet by clicking the 'Print' button to exit 'Print Preview' and bring up the 'Print' dialog box.
6. View the formatting specifics of your spreadsheet by clicking the 'Setup,' 'Margins' and 'Page Break Preview' buttons. The 'Setup' button will open the 'Page Setup' dialog box, the 'Margins' button will show the current margins of the spreadsheet and the 'Page Break Preview' will show the location of each page break.
7. Use the 'Help' button if you need more help and then click the 'Close' button to exit 'Print Preview' and return you to your spreadsheet.

Read more ►

How to Modify Pivot Table Data

Change Data
1. Locate the source data that your pivot table is based on. This is normally at the top left corner of the worksheet, or on a separate worksheet that you can access from the tabs at the bottom left corner of the Excel window.
2. Make the required modifications to your data.
3. Navigate back to the Excel pivot table and click on any cell within the table.
4. Click the 'Options' tab at the top of the screen, then press the 'Refresh' button in the middle of the toolbar. Your pivot table will now reflect the changes you made in the source data.
Add New Data
5. Locate and navigate to your source data.
6. Add the necessary information to your source data. Just like the original data, all information must have column headers in the top row and cannot contain subtotal cells.
7. Navigate back to your pivot table and select any cell in the table. Click the 'Options' tab at the top of the screen and then click the 'Change Data Source' button in the middle of the toolbar. A small window will appear.
8. Navigate back to your source data, the 'Move PivotTable' window will remain in place, even if you have to change Excel worksheets.
9. Click and hold on the top-left cell in your source data, then drag your mouse to the bottom-right cell and release the mouse button. Click 'OK' on the 'Move PivotTable' window to complete the process. Your pivot table will change any data that you updated, and you will see any new fields appear in the field list.

Read more ►

How to Identify Duplicate Items in Excel

1. Click on the column name that you want to check for duplicates. You can also select a range across several columns by selecting the top-left cell in the range and then holding 'Shift' while selecting the bottom-right cell.
2. Select the 'Home' tab at the top of the Excel window. Click on the 'Conditional Formatting' button in the 'Styles' area of the ribbon.
3. Move your mouse over 'Highlight Cell Rules' in the drop-down menu and then choose 'Duplicate Values.'
4. Choose the type of color to apply to duplicate cells by using the drop-down box on the right. It defaults to a light red color with red text but you can quickly change it to yellow, green or you can create a custom look by choosing 'Custom Format.' Click 'OK' when you are done and all the duplicate cells will be highlighted.

Read more ►

How to Customize Columns and Rows in Excel

Customize the Columns
1. Click and drag your mouse over the columns you want to customize. Alternately, click the letter directly above the column to automatically select all the cells in this area.
2. Select the 'Column' option under the 'Format' drop-down menu, then click the 'Width' option to enter a distinct value for this measurement.
3. Use the 'Formatting Palette' (Mac), the 'Formatting Toolbar' (Windows) or the 'Font' group on the 'Home' tab (Excel 2007) to personalize the column. Click the 'Fill Color' option to apply a background color, or click the 'Pattern' or 'Pattern Color' box to insert a design.
4. Click the 'Type' or 'Border Type' and choose the line, color and style that define the outline of the column area.
5. Save your workbook.
Customize the Rows
6. Select rows by repeating the process in Step 1, or click the numbers to the left of the cell.
7. Choose the 'Row' option under the 'Format' menu, then click the 'Height' title to enter a number for this dimension.
8. Repeat Steps 3 and 4 from Section 1 to format the background color and border of the row(s).
9. Save your Excel file.

Read more ►

How to Convert Quattro Pro for Windows to Excel 2007

1. Download the Quattro Pro file converter from Microsoft. The link is in the Resources section below.
2. Double-click on the file you download (qp7conv.exe). Follow the download instructions on the screen.
3. Open Excel 2007. Click on the Office Button (the round icon at the upper left with the Microsoft Office logo on it) and select 'Open.' On the drop-down menu for 'file type,' select Quattro Pro. This will allow you to open any Quattro Pro file (with extensions *.wb3 or *.wb2) into Excel 2007.

Read more ►

How to Open to a Specific Directory in Excel 2007

1. Click the 'Office' button in the upper left of Excel.
2. Select 'Excel Options.'
3. Click 'Save' on the left hand menu.
4. Change 'Default file location' to the specific directory you want to use. Click 'OK.' Now if you open or save a file, your new directory is the one that will open.

Read more ►

How to Insert Page Numbers in Excel 2007

Adding Page Numbers
1. Open the worksheet to which you would like to add sequential numbering, and select the 'Insert' tab from the text group.
2. Choose 'Header and Footer' and then select 'Click to add header,' or 'Click to add footer,' depending on where you would like the numbers to be located. This will display the 'Header and Footer Tools' option.
3. In order to select the location of the page number, you will choose the left, center or right section of the header or footer you have decided on.
4. Click 'Page Number' from the 'Header and Footer Elements group.'
5. Once you see 'Page' appear, press the space bar once and then type the word 'of' followed by a space. This will allow you to add the total number of pages to the document. Next, click 'Number of Pages' from the 'Header and Footer Elements' group.
Completing the Process
6. Once the placeholder '[Page] of [Pages]' appears, click anywhere outside of the header or footer to display the actual page numbers in your 'Page Layout View.'
7. You can choose to start with a different number by clicking 'Page Setup,' and then 'Page Layout.'
8. From there, you can click the dialog launcher beside 'Page Setup.'
9. On the 'Page' tab, you would simply change the 'First Page Number' option to whatever you prefer.

Read more ►

How to Link Cell Sheets in Excel 2007

1. Type '=' in the cell that you want the link to occur in.
2. Click the worksheet, located at the bottom of the page, that contains the cell you want to link.
3. Select the cell you want to link and press 'Enter.' Excel then takes you back to the page where you created the link. By selecting the cell, the Excel automatically formats the 'sheetname!celladdress' for you.

Read more ►

How to Calculate Compound Annual Interest in Excel 2003

1. Label cell A1 'Original Principal,' cell A2 'Compounding Periods Per Year,' A3 'Annual Interest Rate (as a percentage)' and cell A4 'Annual Interest.'
2. Enter the amount of money you put into the account in cell B1, the number of times per year into B2 and the annual interest rate expressed as a percentage in cell B3. For example, if you started with $1,000 and your bank pays 3.5 percent interest, compounded on a monthly basis, you would enter '1000' in cell A1, '12' in cell A2 and '3.5' in cell A3.
3. Enter the formula '=B1*(1 B3/1200)^B2-B1' in cell A4. When you enter this formula, the amount of annual interest will be displayed. In this example, after entering the formula, Excel would display 35.56695, meaning you would earn $35.57 in interest for the year.

Read more ►

How to Use Checkboxes to Control Formulas in Excel

1. Open your Microsoft Excel spreadsheet. Locate the check box that you want to use, if you already have one in place. Alternatively, click 'Developer,' 'Insert,' then click the check box to insert one. Right click the check box and click 'Format Control.' Click the cell button next to 'Cell link,' and designate a cell, then press 'Enter.' Choose a cell in a row or column that doesn't contain any other data as you can hide this row or column if you wish. Click 'OK.'
2. Click the check box, and you will see the words 'TRUE' or 'FALSE' appear in the designated cell. 'FALSE' indicates an unchecked box, and 'TRUE' indicates a checked box.
3. Type '=IF(A1=TRUE,'checked','unchecked')' in the cell where you want to enter your formula.Replace 'A1' with the cell linked to the check box. This cell now responds as you wish to your check box.
4. Replace 'checked' (including quotation marks) with the formula you want to use if the check box is ticked. Replace 'unchecked' with the formula you want to use if the check box is not ticked. You now have a formula that will respond to the check box.

Read more ►

How to Set Up a Drop

1. Make a list of the items that you want to be listed in the drop-down menu. It is best to do this in a separate worksheet from where you want to set up the drop-down menu box, so that it doesn't clutter up the spreadsheet. To access different worksheets, click on the tabs at the bottom of the page. Beginning in cell A1 of the new worksheet, enter each item for the list in a separate cell.
2. Name the list by first highlighting the list of items. Then click in the 'Name Box' just above column A. Enter in a name for the list--Animals, for example. Once the list is created and named, it can easily be added to the drop-down box menu.
3. Go back to the worksheet where you want the drop-down box. Click in the cell where it should be inserted.
4. Click on the 'Data' tab on the top toolbar. Click on the drop-down arrow next to 'Data Validation' in the 'Data Tools' section. Select 'Data Validation' and a box will open up.
5. Select the 'Settings' tab in the 'Data Validation' box. This is where you will enter the information to set up the drop-down box and menu.
6. Choose 'List' from the menu under the 'Allow' section. Additional fields will be displayed in the box. Make sure that the 'Ignore' blank and 'In-cell' drop-down boxes are checked.
7. Enter a source for the drop-down box. In the 'Source' box, enter the equal symbol (=) and the name of the list that you created. The name should be entered just as you entered it when you named the list. For example: enter =Animals.
8. Click 'OK.' You will see an arrow out to the side of the cell where you added the drop-down box. Click on the arrow to access items from the list. To add a specific item to the cell, click on it and the cell will be populated with that item.

Read more ►

How to Create Labels Using Microsoft Word

1. Open a new document in Microsoft Word.
2. Click on “Tools.”
3. Place the cursor on “Letters and Mailings” and click.
4. Drag the cursor to “Envelopes and Labels…” and click.
5. Open the “Labels” tab.
6. Type the information that you wish to appear on the label.
7. Check your printer to ensure that it is loaded with blank labels.
8. Click print.

Read more ►

How to Update the Links in Excel 2007

1. Open the Microsoft Excel 2007 spreadsheet file on your computer that you want to update links for.
2. Click on the 'Data' tab and then click on the 'Edit Links' option from the 'Connections' group.
3. Click on the 'Update Values' option and the links will automatically be updated. Click on the 'Close' button.
4. Click on the 'Data' tab and then click on the 'Edit Links' option again. Click on the 'Startup Prompt' option.
5. Click on the box next to the 'Ask to update automatic links' field so that it's selected. Close out of the dialog box, and now you will receive an automatic message that appears every time you open Excel, asking if you want to update your links.

Read more ►

How to Use Formula Auditing in Microsoft Excel 2007

1.
Click in the cell to which you would like to trace the connections. For example, if you would like to know which cells are part of a particular formula, click in the cell containing the formula.
2.
Click on the 'Formula' tab in the Ribbon. The Ribbon is the group of icons and tools located directly above the work area.
3.
Select the auditing option you want to appear on your worksheet. Trace Precedents will show cells included within a formula. Trace Dependents will show which formula a cell is included in. The arrows that Excel adds to your work sheet will print on your document.
4.
Click 'Remove Arrows' to delete the arrows the auditing function added to your worksheet.

Read more ►