How to Connect Excel Spreadsheets

1. Open the two spreadsheets that you want linked (you should have two open windows).
2. Go to the worksheet you want to import data to and click on a cell that you want the imported data displayed.
3. Type an '=' sign into the formula window (the blank line at the bottom of the toolbar.
4. Navigate to the sheet you want to import the data from and click on the cell with the data. For example, click on cell A1.
5. Go back to the spreadsheet that you want the data imported to. Excel will paste the name of the worksheet and the cell reference into the formula line. Press 'Enter' and the imported total will show in the specified cell.

Read more ►

How to Disable Auto Backup in Excel

1. Click the 'File' tab and then 'Save As.'
2. Click the down arrow next to Tools and click 'General Options.'
3. Un-check the 'Always create backup' box to stop Excel from automatically backing up your files.

Read more ►

Microsoft Excel Visual Basic Tutorial

1. Click on the 'Alt' and 'F11' keys from within any Excel worksheet or workbook to open the Visual Basic Editor (VBE). You can also access the VBE by clicking on the 'Developer' tab and then clicking on 'Visual Basic.'
2. Click on the 'Insert' tab, and then click on 'Module' to open a blank window for your program.
3. Type the word 'Sub' (for subroutine) or 'Function' into the window, followed by the name of your program. For example, 'Sub italics()' is the first line of a subroutine called 'Italics.' If the VBA returns a result such as a number, it's a function. If it performs a task without returning anything, it's a subroutine.
4. Type an apostrophe on a new line, followed by a description of what the program is for. For example: ''this macro italicizes a highlighted cell.'
5. Type the body of your code starting on a new line. For example, to italicize a cell, type:'Selection.Font.Italic = True.'
6. Type 'End Sub' or 'End Function' on a new line, depending on if you defined a Sub or Function in Step 3.
7. Run the code by pressing the 'F5' key.

Read more ►

How to Merge Cells on an Excel Spreadsheet

1. Click the 'Home' tab on the command ribbon.
2. Click and drag to select two or more adjacent cells. A black border surrounds the range of cells. The highlighted cells display one white cell in the upper-left corner and one or more blue cells. Only this white cell retains its data.
3. Click the down-arrow in the 'Merge Center' command in the 'Alignment' group. The three merge options include 'Merge Center,' 'Merge Across' and 'Merge Cells.'
4. Click the preferred merge option. The cells become one larger cell.

Read more ►

How to Create a Table in Excel 2003

1. Open Excel on your computer, and start a new spreadsheet.
2. Enter your data into the spreadsheet. If the table is to be row-oriented, enter the desired calculation formulas to the left and just below the data. If the table is column-oriented, enter the calculation formulas above the first value and in the column immediately to the right of the data. Assistance for understanding formulas is available in the Excel 'Help' section.
3. Go to the 'Data' tab, and select 'Table.'
4. Enter the cell number where the calculated data should appear in the 'Column input cell' field or 'Row input cell' field, depending on the orientation of your table. Click 'OK.'

Read more ►

How to Remove Mass Links in Excel 2007

1. Open the Microsoft Excel 2007 file on your computer that contains the links you want to remove.
2. Enter '1' into a blank cell within your worksheet. Right-click on the cell with your mouse and then click on the 'Copy' option.
3. Hold down the 'CTRL' key on your keyboard and select each link in your worksheet that you want to remove.
4. Click the 'Home' tab. Click the arrow below the 'Paste' field in the 'Clipboard' group. Click the 'Paste Special' option.
5. Click the 'Multiply' option and then click the 'OK' button. Click the 'Home' tab again and then click the 'Cell Styles' option from the 'Styles' group.
6. Select the 'Normal' option below the 'Good, Bad, and Neutral' field and all of the links you selected will be removed.

Read more ►

How do I Add the Microsoft Excel 11.0 Object Library?

1. Insert your Office 2003 disc. The Office 2003 Setup window will open.
2. Select 'Add or Remove Features.' Click 'Next.'
3. Check the 'Choose advanced customization of applications' box. Click 'Next.'
4. Click the plus sign next to 'Microsoft Office Excel.' This expands that section of the applications and tools list.
5. Click the down arrow beside '.NET Programmability Support.' Click 'Run from My Computer.' Click the 'Update' button.

Read more ►

How to Remove Grid Lines

For Excel 2003
1. Select the cells in the spreadsheet from which you want to remove the grid lines. Do this by left-clicking on the mouse and holding it down as you move the cursor over the cells you want to select.
2. Click on the 'Format' menu at top of page.
3. Click 'Cells.'
4. Click on the 'Patterns' tab.
5. Click on the background color of the spreadsheet. In most cases, the color is white.
6. Click on the 'Border' tab.
7. Click on the arrow in the 'Colors' box. Click on the color of the gridlines. In most cases, the color is 25% gray.
8. Click 'Outline.'
9. Click on 'OK.' The grid lines are now gone.
For Excel 2007
10. Open your spreadsheet.
11. Click 'View' on the taskbar.
12. Look for the 'Show/Hide' group.
13. Clear the checkbox near 'Gridlines' to uncheck the box. The gridlines disappear.
For Excel 2010
14. Select your spreadsheet.
15. Click 'File' in the taskbar. Click 'Options.' A dialog box will appear.
16. Click 'Advanced' in the right pane. Scroll to find 'Display Options for this Worksheet,' and click on it.
17. Disable 'Show Gridlines' by clicking on the check box to remove the check. The gridlines disappear.

Read more ►

How to Determine if a File Exists in Excel VBA

1. Copy the following code:Option ExplicitFunction FileOrDirExists(PathName As String) As Boolean'Macro Purpose: Function returns TRUE if the specified file' or folder exists, false if not.'PathName : Supports Windows mapped drives or UNC' : Supports Macintosh paths'File usage : Provide full file path and extension'Folder usage : Provide full folder path' Accepts with/without trailing '\' (Windows)' Accepts with/without trailing ':' (Macintosh)Dim iTemp As Integer'Ignore errors to allow for error evaluationOn Error Resume NextiTemp = GetAttr(PathName)'Check if error exists and set response appropriatelySelect Case Err.NumberCase Is = 0FileOrDirExists = TrueCase ElseFileOrDirExists = FalseEnd Select'Resume error checkingOn Error Goto 0End FunctionSub TestItWithWindows()'Macro Purpose: To test the FileOrDirExists function with Windows'Only included to demonstrate the function. NOT required for normal use!Dim sPath As String'Change your directory heresPath = 'C:\Test.xls''Test if directory or file existsIf FileOrDirExists(sPath) ThenMsgBox sPath ' exists!'ElseMsgBox sPath ' does not exist.'End IfEnd SubSub TestItWithMacintosh()'Macro Purpose: To test the FileOrDirExists function with a Macintosh'Only included to demonstrate the function. NOT required for normal use!Dim sPath As String'Change your directory heresPath = 'HardDriveName:Documents:Test.doc''Test if directory or file existsIf FileOrDirExists(sPath) ThenMsgBox sPath ' exists!'ElseMsgBox sPath ' does not exist.'End IfEnd Sub
2. Open Excel and press 'Alt-F11' to enter the Visual Basic Editor.
3. Click 'Insert' and then click 'Module.'
4. Paste the code into the right-hand pane by pressing 'Ctrl-'V.'
5. Change 'text.xls' to the file name you are searching for.
6. Press 'F5' to run the procedure. The procedure will return a pop-up window telling you whether the file exists.

Read more ►

How to Share Excel Macros

1. Write the macro to be shared in an empty spreadsheet. Click the 'View' tab on the ribbon menu. If you are going to record the macro, click the small arrow at the bottom of the 'Macros' button and then select 'Record Macro...' from the drop-down menu. If you elect to write the macro using Visual Basic for Applications, select 'View Macros...' from the drop-down menu. Provide a macro name and click 'Create' on the 'Macro' dialog.
2. Save the spreadsheet as an 'Excel Macro-Enabled Workbook' file. If the macros are saved in other types of Excel files, they may not be visible due to internal security settings.
3. Distribute the Excel workbook master file to those co-workers with whom you want to share the macro. When a user bases a workbook on this master file, the macro remain embedded.

Read more ►

How to Link a File to MS Excel

1. Open the saved Excel file.
2. Click on the cell or object to insert the file link.
3. Click on the 'Insert' tab on the command Ribbon.
4. Click on the 'Hyperlink' button on the 'Links' group. A window will show 'Insert Hyperlink.'
5. Click on the file name from the list of files in the box.
6. Click 'OK.' The colored hyperlink will show on the Excel worksheet.
7. Save this Excel file.

Read more ►

How to Summarize Data in Excel

1. Use the Subtotals method. Choose one customer name by selecting a cell in column A. Click the 'AZ' sort button--you can find this on the standard toolbar. Next, select 'Data' and then 'Subtotals.' The Subtotals dialog box should appear. Make sure you've checked off 'OldSales,' 'NewSales' and 'Total.' Click 'OK' to see the summary data in bold on your spreadsheet. Press the '2' in the top left corner of your spreadsheet to see only the summary data.
2. Try the Consolidate method. Select a blank cell to the right of your data. Click 'Data' and 'Consolidate.' In the 'Reference' section in the 'Consolidate' dialog box, select your entire table and click 'OK'. This produces a new row with summarized totals for each unique customer in Column A. Delete the 'Date' field in the summary results.
3. Create a Pivot Table. Select a blank cell to the right of your data. Click 'Data' and 'Pivot Table and PivotChart Report.' Select your entire data table and click 'Finish.' Choose to add 'Customer' and 'Total' to the new pivot table. Excel sorts the summarized data by unique customer.

Read more ►

How to Make a Pie Chart Using Microsoft Office 2003

1. Click 'Start,' 'All Programs' and then click 'Microsoft Office Word 2003.'
2. Click 'File' from the top menu and then click 'Open.' Click the file to which you want to add a pie chart and click 'Open.'
3. Click 'Insert' from the top menu and click 'Object' from the drop-down menu.
4. Click 'Create New.'
5. Click 'Microsoft Graph Chart' from the 'Object Type' box and click 'OK.'
6. Click inside a cell on the datasheet window and enter the text or numbers that you want to display in the pie chart. The default chart type is a bar graph.
7. Double-click the chart. A box appears around the chart.
8. Right-click inside a blank area of the chart and select 'Chart Type.'
9. Click 'Pie' from the 'Chart Type' and click 'OK.'

Read more ►

How to Display a Count of Cells on the Bottom of the Window in Excel

1. Open Microsoft Excel and load the spreadsheet you want to edit. This can be an existing file, or you can just use the default new page to test the functions.
2. Enter data in the first column. Use the following data to populate the 'A' column on the spreadsheet:Price
2.00
3.00
4.00
3. Use the Count or CountA function in the bottom field. The following code is an example of how to use the Count function:=count(a1:a4)To use the CountA function, enter the following code instead:=countA(a1:a4)The equal sign is used to denote a formula. This is prefixed in all cells that contain formulas. The two alphanumeric numbers in the parenthesis are the range of fields. The 'a' represents the column and the number represents the row.
4. Use the CountBlank function to count the number of blank cells. This tells you how many cells contain no data in your list of fields. To use the CountBlank function in conjunction with the test data, type the formula below into a cell at the bottom of the spreadsheet:=countblank(a1:a5)
5. Use the CountIF function to display a count only if a particular cell contains the specified data. For instance, suppose you want to count how many cells contain '2.' Use the CountIF function to determine the output. The code is below:=countif(a1:a4, 2.00)The first parameters are the range of cells to check. The second parameter (2.00) is the value to count.
6. View the results. The Count function returns the value of '3.' This is because only three out of the four cells populated have numerical values. The CountA function returns '4.' CountA displays a count of all cells regardless of the data type. For the CountBlank function, the range has only one blank cell, so the return value is '1.' Finally, for the CountIF function, there is one cell with the matching value, so the return value is '1.'

Read more ►

How to Turn Off Filter Arrows in Microsoft Excel 2007

1. Open the Excel sheet that has the filtering option turned on.
2. Click the 'Data' tab on the Microsoft Ribbon.
3. Click the highlighted 'Filter' button in the 'Sort Filter' section to turn off all filter arrows in the spreadsheet.

Read more ►