Category Archives: Excel

Using Excel data in Access

Posted by on November 29th, 2012
Leave a reply

I have data in an Excel spreadsheet that I would like to use in an Access Database. What are the steps?

Importing or linking data from Excel to Access works well if the Excel data is in rows and columns without any subtotal rows, formulas or blank rows or blank columns within the worksheet or range. Column headings should not be wrapped or contain special characters and must be unique names. Then the data should import pretty cleanly by:

  1. Create a blank database in Access (or an existing database you wish to place it) and select the External Data tab > Excel (from the Import group).
  2. Locate the Excel file and select “Import the source data into a new table in the current database” or “link to the data source by creating a linked table.”
  3. Follow the screens until the process is finished.  

If you have cells which contain formulas, they will import as values. When importing, the Access table is completely separate from your Excel file. Any updates made in Excel or Access after the import are not updated in both applications. However, if you “Link” the data, you can only update from the Excel file, but the data is automatically updated in Access so that it can be used for filters, queries and reports.

 Keep in mind that just because your Excel data imports well into Access does NOT mean that your file is set up ideally to work in Access. You may have to “normalize” your data – add fields, split the data into more tables, add primary keys, add validation properties, etc. In addition, Microsoft Access requires a considerable investment in time to learn how to use it, compared to Excel. But Access works well if you have a lot of data in separate but related tables to analyze. To learn more about database design concepts, come to ITS’ mini-session on Thursday, 11/29 from 2-3 p.m. in the ITS training room, 8011 Wittson Hall. Additional Access training is available in December. Click here for the ITS class schedule and on-line registration.

Question of the week of 9/12/11 – Remove duplicate values in Excel

Posted by on September 12th, 2011
Leave a reply

Question: Is there a way to remove duplicate values from a long list of names in Excel?

Answer: Yes, select your range and from the Data tab, click Remove Duplicates. A popup will allow you to select which fields to select for your duplicate values. You can unselect all fields, and selecting just a couple of fields will break down your list more than if you have many fields included. Be careful, as this will delete your data, so you may want to save the results with the different name using “Save As” (you can also click “undo” if the list does not look correct or not save your file at all).

Excel add-ins and online tools for charting

Posted by on August 29th, 2011
Leave a reply

I wanted to share some excellent tools that I’ve recently learned about. One is a free statistical analysis add-in program for Excel. Greg McFadden from Internal Medicine shared his knowledge with us in a mini-session a couple of weeks ago. To use this add-in:

  1. Select Excel Options (available from the Office button in Excel 2007 or File menu in Excel 2010).
  2. Click Add-ins and select Go (toward bottom of add-ins window)
  3. Place a check on the Analysis ToolPak and click OK. You may get a prompt asking to install it, and you may need the installation disk (although I didn’t with Excel 2010).
  4. After it has been installed, the analysis features are available on the far right of the Data tab. Some of the analysis tools available are: anova, correlation, covariance, descriptive stats (wonderful), F-test, histogram, fourier analysis, regression and more.

This site recently came to my attention from Teri Hartman, from McGoogan Library. Here you will find described a number of on-line programs that can be used to create charts and graphs of all types, including drawing tools, mind mapping, flow charts, organizational charts, iPhone chart application and more! I haven’t tried any of them yet, but I can’t wait to try out the mind mapping tools!

Thanks Greg and Teri for sharing your great finds!

Question of the week of 6/13/11 – Excel summary sheet linking to other worksheet

Posted by on June 15th, 2011
Leave a reply

Question: I have an Excel Workbook with several worksheets. On the summary sheet I want to refer to the values on various individual worksheets. How can I do that?

Answer: The easiest way is just to click in the Summary worksheet cell where you want the cell’s value and press = (to indicate a formula) and then click on the source worksheet and cell you’re referring to and press enter (or the formula check box). This creates an automatic link to the contents of that particular cell. If the source worksheet is modified by adding or deleting rows or columns, the summary sheet link will change based on the new cell address (relative cell address). Absolute cell addresses ($B$12) would assure that that exact cell  address B12 is always used regardless of modifications to a worksheet.

I would also recommend using Excel Pivot Tables to simply your work even more, and providing some powerful tools. However, your data needs to be set up with contiguous rows and columns, eliminating blank, total and subtotal rows and columns. Pivot Tables can be a tremendous timesaver for budgets and other worksheets with a lot of rows and columns and allows you to create a variety of reports and charts by updating the original set of data. There are several online tutorials at http://office.microsoft.com/training (and select Excel) which can get you started. ITS also teaches sessions occasionally on Pivot Tables.

Question of the week of 6/6 – Excel startup files

Posted by on June 8th, 2011
Leave a reply

Question:  Every time I open an Excel file or start a new Excel workbook, two files automatically pop up. How can I get rid of these files, as it is very annoying to have to close these each time.

Answer: For some reason, the files were placed or saved into an obscure Excel startup folder (user’s nameappdataroamingmicrosoftexcelxstart) on the desktop which automatically starts these files when opening Excel. When these files were deleted, Excel opened normally.