How to Import Excel Spreadsheets Into Access

1. Save your spreadsheet data in Excel using the 'File,' 'Save' menu. Although Access recognizes a range of alternate formats, including dBASE and text delimited, it is best to use the native Excel format for your saved file. Access is configured to import Excel files directly and provides options for continuing to work with the data in both products after the import process.
2. Start Microsoft Access and open the database into which you want to import your Excel data. You can create a new, empty database in Access by clicking 'File,' 'New' and selecting the 'Blank Database' option. Provide a name for the file when prompted. Click 'File,' 'Get External Data,' 'Import' and select the Excel format. Locate your spreadsheet subdirectory and import your spreadsheet into Microsoft Access. If you are using Microsoft Access version 7, you can also click the 'External Data' tab at the top of your screen and select 'Excel' as the format to import the spreadsheet data. You can append the data to an existing table or create a new table for your Excel spreadsheet data.
3. Select the worksheet or range you want to import from the Import wizard and click 'Next.' Select the Excel column headings you want to import as field names and verify that you want to create a new table or append data to an existing table. Click 'Next' to continue. You can change field names, skip fields that you do not want to import and select indexing options, including a primary key for sorting your spreadsheet data in Access. Click 'Finish' when you are satisfied with your selections.
4. Review the Table Analyzer wizard results to verify the structure of the database tables that Access has created from your Excel worksheet. The wizard will divide tables containing duplicate information into separate tables so that each category of data is stored only once. Verify field placement within tables, select the primary key used for sorting and correct any typographical errors prior to completing the import process.

Read more ►

How to Add Multiple Sheets to a Workbook in Excel 2007

1. Open Excel 2007. At the bottom of the page, there are tabs you can use to select which sheet you want to view. These tabs are labeled 'Sheet 1,' 'Sheet 2,' and so on.
2. Right-click over one of the sheet tabs to insert a sheet. Click 'Insert,' select 'Worksheet,' and click 'OK.' Look down at the tabs to verify that a sheet was inserted. Repeat this to insert multiple sheets.
3. Hold down the 'Shift' key. Drag your mouse over the spreadsheet tabs to highlight the number of sheets you want to insert. You can insert multiple sheets by highlighting more than one tab.
4. Click 'Insert,' select 'Worksheet,' and click 'OK.' Look down at the tabs to verify that the extra sheets were inserted.

Read more ►

How to Create Forms in Excel 2007

1. Open an Excel 2007 spreadsheet. Type in column headers in the top row for each column you will enter data under.
2. Click and drag the mouse over the column headers only to select them. Do not highlight any other columns or select the entire row, including the blank cells to the right of the column headers or else Excel will not know which columns to use to create the form.
3. Click on the arrow button beside the Quick Access Toolbar at the top of the screen, then click 'More Commands' from the drop-down list that appears.
4. Click the 'Choose commands from' drop-down menu and click 'All Commands.' Scroll down the list and then double-click the 'Form' button. Click 'OK' to close the Excel Options dialog box.
5. Click the 'Form' button that you just added to the Quick Access Toolbar. Excel creates a form with the column headers that you may use to add new records to the spreadsheet.

Read more ►

How to Show Formulas in Excel 2003

1. Click on the square in the upper-left corner of the Excel 2003 spreadsheet to select all cells.
2. Press and hold the 'Ctrl' key on your keyboard after all the spreadsheet cells are selected.
3. Press '`,' the grave accent key, located in the upper-left hand corner of your keyboard below 'Esc.'
4. Release both buttons.

Read more ►

Step

1. Start Microsoft Excel. Double click on the Desktop icon and Excel will open with a blank spreadsheet. If you cannot locate the Desktop icon, click on the 'Start' or 'Microsoft' button, and then click on 'All Programs.' Click on 'Microsoft Office,' and then click on 'Microsoft Excel.'
2. Click on cell 'A1' and enter a name for the column. For example, if you are making a budget, then you might type 'Income.' Data in Excel are entered in columns for easy formatting and insertion of formulas and functions.
3. Enter your other column headings in row 1. In the budget sheet example, click on cell 'B1' and then type 'Amount,' then click on cell 'C1' and type 'Expenses' and then click on cell 'D1' and type 'Amount.' If you have other column heading you want to enter, click on cell 'E1' and continue working right through the worksheet.
4. Enter the data into your columns. Click on cell 'A2' and type your first item. In the budget example, this could be 'Mortgage.' Press the 'Right arrow' key to move the cursor to cell 'B2' and then type the corresponding amount. If your mortgage is '$1,000' then type '$1,000' into cell 'B2.'
5. Add up the numbers in a column by using 'Autosum.' Highlight all of the data in a column that you want to add, click the 'Home' tab and then 'Autosum' to add up the entire column. To highlight data in a column, left click on the top left of the first item and then drag the cursor to the bottom right of the column. You can also click on the column identifier to highlight the entire column. The column identifier is the number or letter at the very top of the column.
6. Format your cells by highlighting them, and then clicking on 'Format Cells.' The pop up window gives you a myriad of choices to align cells, choose borders and fill, and select the type of data you want in cells. In the budget example, you may want to make sure all of your entries are in dollars. Click on the 'Number' tab, click on 'Currency' and then choose the type of currency you would like to have by clicking on the down arrow next to 'Currency.'

Read more ►

How to Fix Broken Links in Excel 2007

1. Close all the workbooks that are open.
2. Open the workbook with the broken link.
3. Click 'Options' on the trust bar, and then click 'Enable this Content.'

Read more ►

How to Convert Date to String in Excel

1. Open Microsoft Excel, then open the document where you want to convert a date to string.
2. Type '=TEXT(' in the cell where you want to place the string.
3. Click on the cell with the date. This should put the cell reference (like A1) in the initial cell.
4. Type 0, 'MM/DD/YYYY') after the cell reference. Exchange 'MM/DD/YYYY' with the format of your date. In this case MM is the month, DD is the date, and YYYY is the year. Press enter and the cell should have a text (string) version of the date.

Read more ►

How to Convert Text to Upper Case in Excel 2007

1. Convert text in a cell, say cell A1, into fully capitalized text in another cell, say B1, with the UPPER function. For example, type in B1 the following: =UPPER(A1). You can even copy the contents of B1 into A1 by right-clicking the mouse on B1, selecting Copy, then right-clicking A1, and selecting “Paste Special” and “Value.” (If you insert the formula instead, the reference will be circular and you’ll get an error.)
2. Convert the returned value of a formula using the following syntax: =UPPER(function). For example, if the original function in a cell is =IF(A1>B1,C1D1,A1), then change the cell contents to =UPPER(IF(A1>B1,C1D1,A1)).
3. Perform this capitalization conversion on more than one cell at once as follows. For a row of text, say A1 to A100, type in an empty cell, say B1, the following: =UPPER(A1). Highlight B2 to B100. Click 'Home.' Click 'Fill.' Click 'Down.' The B-column cells now range in contents from =UPPER(A1) to =UPPER(A100). Now just perform a special paste of the column as for the single cell in Step 1.

Read more ►

How to Make a Cell Reference Absolute in Excel

1. Start Microsoft Excel 2007 and open an existing workbook from your files or start a new blank workbook.
2. Click to select the cell where you would like to enter a formula where the cell reference is absolute.
3. Begin typing the formula into the cell. Type a dollar sign right before the column reference in the formula to make it absolute. For instance, you may type '$A1' into a formula. This means that wherever you copy and paste this formula, Excel will always keep the reference in column A.
4. Type a dollar sign right before the row number to make the row number absolute. For example, you can type 'A$2' inside of a formula. This means that the cell reference will always refer to the second row, no matter where you copy and paste the formula.
5. Use dollar signs before both the column letter and cell number to make the entire cell reference absolute. If you type, '$A$2' inside of a formula than that formula will always refer to cell 'A2.'
6. Continue typing the rest of your formula. You can include dollar signs into other cell references inside your formula if you need to.
7. Press the 'Enter' key on your keyboard to enter the formula into Microsoft Excel and view your calculation result.

Read more ►

How to Nest Subtotals in Excel 2003

1. Click and drag your mouse to highlight the three columns---two columns of headings and the third of values.
2. Click the toolbar's 'sort' button. The Sort window will open.
3. Sort the table first by the first column and then by the second column.
4. Click 'Subtotals' from the Data menu.
5. Click the down-arrow in the 'At each change in' drop-down box. Click the first column's heading.
6. Click 'Sum' in the 'Use function' box.
7. Click the down-arrow in the 'Add subtotal to:' drop-down box. Click the third column's heading.
8. Click 'OK.'
9. Click and drag your mouse to highlight the three columns again. Click 'Subtotals' from the Data menu.
10. Click the down-arrow in the 'At each change in' drop-down box. Click the second column's heading.
11. Click 'Sum' in the 'Use function' box.
12. Click the down-arrow in the 'Add subtotal to:' drop-down box. Click the third column's heading.
13. Uncheck the 'Replace current subtotals' box.
14. Click 'OK.'

Read more ►

How to Change the X

1. Open Excel and navigate to the chart for which you wish to change the x-axis range.
2. Move your cursor so that it is directly over the x-axis of the chart, and right-click with your mouse.
3. Select 'Format Axis...' from the menu that appears. The Format Axis dialog box will open.
4. Click so that 'Fixed' is selected instead of 'Automatic' for both the Minimum and Maximum under Axis Options.
5. Select the values you wish to use as the minimum and maximum for the x-axis and enter them in the appropriate boxes. Click 'Close' to finish.

Read more ►

How to Add Headers and Footers to Excel Documents

1. Start Microsoft Excel and open the file you want to change.
2. Open the View menu and select Header and Footer option.
3. In the Page Setup dialog box, click the Custom Header or Custom Footer button.
4. Click inside the Left section, Center section or the Right section.
5. Click the A icon to change the font styles.
6. Click the # icon to insert page numbers.
7. Click the icon to insert the number of pages in the document (so if you wanted the header or footer to read 'Page X of 25,' you'd click the # icon followed by the icon).
8. Click the date, time, file name or tab icon to insert those items.
9. Click OK to accept the changes.

Read more ►

How To Copy Name Ranges in Excel 2003 to Another Workbook

1. Open the workbook you will be copying the range names from. If you do not already have the names set, define the range names. To do so, click on the 'Insert' menu, select 'Name' and then choose 'Define' from the list. Find the names in the workbook box.
2. Locate the name of the second workbook. Make sure that workbook is saved as a Macro-enabled Workbook. The file extension should be .xlsm or .lsm.
3. Hold down the 'Alt' and 'F11' keys. The Visual Basic Editor will open. Click 'Module' in the 'Insert' menu. The module window will pop-up.
4. Type the following code into the window. Change the reference to book2.lsm or book2.xls to the name of the book you are pasting too.Sub Copy_All_Defined_Names()' Loop through all of the defined names in the active' workbook.For Each x In ActiveWorkbook.Names' Add each defined name from the active workbook to' the target workbook ('Book2.xls' or 'Book2.xlsm').' 'x.value' refers to the cell references the' defined name points to.Workbooks('Book2.xls').Names.Add Name:=x.Name, _RefersTo:=x.ValueNext xEnd Sub
5. Close the Visual Basic Editor Window. Click on the 'X' button or hit 'ATL' and 'F11' again to close the window. The macro will automatically save. When you are back at book 1 or the workbook you are copying from, save the workbook. Click on 'File' and choose 'Save' or 'Save as.' Do not change the name of the file in any way.
6. Open the workbook you are pasting to or start a new workbook. Save the workbook as an Excel Macro-Enabled Workbook. Make sure the name is the same as the name for book2.lsm in the code. The spelling capitalization and extension have to be exact.
7. Open the first workbook again and click on the 'tools' menu. Select 'Macro' and click 'Macros.'
8. Click on 'Copy_All_Defined_Names' from the list of macros. Select the 'Run' option. The names will be automatically pasted in the second workbook.
9. Open Book2 or the paste destination workbook. All the names should be pasted.

Read more ►

How to Merge Information From Several Excel Spreadsheets

1. Open the workbook with the spreadsheets you want to consolidate. If you the spreadsheet is not in the workbook, create a duplicate spreadsheet within the workbook by doing a copy and paste.
.
2. Chose Data and then Consolidate. Excel displays the Consolidate dialog box.
3. Specify the range with the Reference box. After you specify the range (or the area of cells on the spreadsheet you want to consolidate) click Add. The reference will appear in the All References list. Continue to add ranges until you have added them all.
4. Click on the Create Links to Source Data box if you want the consolidated data to contain links to the original data. If not, leave blank and click OK to do the consolidation.

Read more ►

How to Create a Bar Graph in Excel 2003

1. Log on to your computer and pull up your Excel worksheet. You should be able to locate the program by using your 'Start' button. When you cannot find it there, use your 'Search' or 'Find' function.
2. Arrange desired data in columns or rows. You only need to enter the numbers at this time.
3. Highlight the cells containing desired data. To select separated data, highlight the first piece. Then hold 'Ctrl' while clicking on the remaining data.
4. Select 'Chart' from the 'Insert' menu or click on the 'Chart Wizard' icon on the toolbar.
5. Choose your chart type. Click 'Next'. Confirm the data range mentally and select if you want your series in rows or columns. Select 'Next'.
6. Enter titles for your chart and axis. Choose the location of your chart. Select 'Finish'.

Read more ►