EXCEL Tips/EXCEL Macro

From WSUWiki

Return to Collections and Technical Services, Bibliographic Control, Collections, Monograph Acquisitions or Serials and E-Resources

Contents 1 Use of EXCEL to Customize Review File Spreadsheets 1.1 DELIMITED FILES 1.2 EXCEL SET UP 1.3 BASICS 1.4 USE OF TEXT TO COLUMNS WIZARD – FIXED WIDTH 1.5 AUTOFILTERS 1.6 MERGING TWO SPREADSHEETS 1.7 CALCULATIONS AND SUBTOTALS 1.8 CONCATENATE - Merging data from two cells into one 1.9 FORMATTING TO PRINT 1.10 NOTE - Problem with Formula format 2 MERGE MACRO 2.1 Directions

Use of EXCEL to Customize Review File Spreadsheets

DELIMITED FILES

1. Review files are downloaded as Delimited Files. If a file is comma-delimited, commas are used to separate the text into Columns. Choose a delimiter that is not used in variable field text (such as *).
2. Save the downloaded delimited files as text files (.txt).
3. Open EXCEL and browse for the file. If it can not be located, clicked on FILE - OPEN in the menu and change "Files of Type" at the bottom of the OPEN box to "All Files". This will allow viewing of all formats including TXT files.
4. Step 1 of 3. After you open the file in EXCEL, the TEXT IMPORT WIZARD will display. Click DELIMITED. Click NEXT.
5. Step 2 of 3. Delimiters - check comma, tab, * or semicolon. After selecting the correct delimiter, the data will move into Columns. Click NEXT.
6. Step 3 of 3. Change all date Columns from General to date; change all ISSN, ISBN from General to TEXT. Change dates to DATE format. FINISH.
7. Save the file in .xls format. MASTER.xls

EXCEL SET UP

1. To view all menu choices, go to TOOLS - CUSTOMIZE - OPTIONS – will always show full menu
2. To view more icons on the taskbar - for example I have a Page Setup and Autofilter on mine - Go to COMMANDS - FILE - locate the functions you would like to add and drag the icon to the toolbar.

BASICS

1. Resize Columns – Mouse over the column letters at the top of the spreadsheet (+). Click and increase or decrease width of cell column.
2. Resize rows after resizing columns – Highlight spreadsheet – FORMAT – ROW - AUTOFIT
3. Highlight whole worksheet – Click on square between Column A and Row 1 in the left hand corner.
4. Wrap text – Highlight worksheet or just a column. FORMAT – CELL – ALIGNMENT – WRAP TEXT.
5. Undo – Backwards arrow or EDIT - UNDO
6. Drag information into other cells – mouse over right bottom corner (+) – Click and drag.
7. Push data from one cell to another –Mouse over the top line of the cell (+ and arrow), click and push the data into another cell.
8. Make a cell a color – highlight cell – Click on paint can.
9. Make text a different color – highlight text – Click on A
10. Insert a row – Click on the number of the row to be added. Right click. – INSERT ROW
11. WINDOW – SPLIT or WINDOW – FREEZE PANEL

USE OF TEXT TO COLUMNS WIZARD – FIXED WIDTH

1. OCLC – More than one OCLC number in Column A.
2. Insert a blank Column B.
3. Highlight Column A of OCLC numbers. DATA – TEXT TO COLUMNS
4. Step 1 of 3: Click on Fixed Width on the Text to Columns Wizard
5. Step 2 of 3: Draw a line to the right of the last number.
6. Step 3 of 3: Change General to TEXT for OCLC Column A. FINISH.
7. Delete Column B with the extra OCLC numbers
8. If any semi-colons remain, highlight Column A. EDIT – FIND ; REPLACE (blank)

AUTOFILTERS

1. Highlight heading Row 1. DATA – FILTER – AUTOFILTER
2. Autofilters can be used to check for mistakes in data sets and answer many queries
3. AUTOFILTERS DO NOT WORK IF THERE IS A ROW WITH NO DATA - Sometimes adding a column of numbers so every row that has some content in it, will fix a spreadsheet so the autofilters will work properly.
4. You can tell autofilters are on because the numbers on the rows are colored.
5. You should not PASTE when you have autofilters turn on. Drag the information because if you paste, you may be pasting onto cells that are hidden.
6. Turn off autofilters – DATA – FILTER – AUTOFILTER

MERGING TWO SPREADSHEETS

1. Arrange the worksheets to be merged so they can be viewed simultaneously. WINDOW - ARRANGE – VERTICAL
2. Add a new Column A to the Master spreadsheet.
3. On the Price.xls worksheet, do the following:
   1. Add a Column A - write a word in here that can be used to remove all of the rows if needed (like remove)
   2. Color the columns that you will be adding to the Master spreadsheet.
   3. Line up all of the rows so that they match perfectly or there are blank columns in the Master worksheet that you are adding. One way to do this is to use the WINDOW- ARRANGE - HORIZONTAL
   4. Save both files before merging
   5. Copy the rows from the spreadsheet to be merged and paste them onto the Master worksheet at the bottom.
   6. Highlight spreadsheet. DATA - SORT - HEADER ROW - TITLE
   7. Go through each line of the spreadsheet and push the data into the Master file
   8. If a warning notice asks if you want to overwrite data, this can be turned off - TOOLS - OPTIONS - EDIT - unclick Alert before overwriting cell.
   9. When all data has been merged, remove the extra rows by autofiltering on Column A – “remove” - and delete all of the rows at once.

CALCULATIONS AND SUBTOTALS

1. To view a SUM – highlight the column of interest
   1. Look at the bottom right hand side of the spreadsheet - the Status bar right about the Windows Toolbar.
   2. Right click and view a list of Functions such as SUM, Count, etc.
2. SUBTOTAL – Example of ILL generated from ILLiad query
   1. First sort the column alphabetically
   2. Create a blank column next to the column you want to subtotal
   3. Highlight the column to be subtotaled
   4. DATA -SUBTOTAL - USE function Count; Click on subtotal to the same column; replace subtotals; Summary below data are both checked.
   5. Three new columns on the left hand side of the screen will appear. Click on number 2 to get the subtotals.
   6. If you want to be able to merge this information on another spreadsheet the data must replace the subtotal formulas.
      1. Highlight Column A and B; EDIT - COPY - COPY PASTE SPECIAL - VALUES
      2. Remove the subtotals - DATA - SUBTOTAL - REMOVE ALL
      3. DATA -SORT - Column A
      4. Erase all raw data below the subtotal information
      5. Remove the word Count by using EDIT – FIND and REPLACE function

CONCATENATE - Merging data from two cells into one

1. Click on INSERT - FUNCTION
2. Click on CONCATENATE
   1. A function box will appear. Move it out of the way so the cells to be merged can be viewed.
   2. Text1 = click on first holding in the start data Column
   3. Text2 = Type hyphen (-)
   4. Text3 = Click on first holding end date
   5. Click on okay
3. Drag the formula down the column for all remaining cells.
4. Freeze the data in the cell by highlighting the column with the concatenation formula, COPY - PASTE SPECIAL - VALUES

FORMATTING TO PRINT

1. Highlight the area to be printed. FILE - PRINT AREA
2. Hide columns or rows not to be included in the printout. Highlight the column(s) - Right click on mouse - Hide
3. File - PRINT SETUP
   1. PAGE - Choose Portrait or Landscape; Size - 75% minimum
   2. MARGINS - Scroll or type in the blocks - .1 minimum
   3. HEADER/FOOTER - Be sure to adjust the margins correctly or spreadsheet will overwrite the header
   4. SHEET - Rows to repeat at the top - use the format $1:$1 to repeat the first row on every new page; use $1:$3 to repeat the first three rows.
   5. SHEET - Columns to repeat at left - use $A:$A to repeat the first column.
      1. Click on Gridlines and Rows and Column Headings especially if presenting the spreadsheet to others.
   6. To make the spreadsheet more readable, color every other row.
      1. Highlight whole spreadsheet
      2. FORMAT - CELLS - ALIGNMENT - uncheck WRAP TEXT
      3. Highlight the second row -change the color (usually light blue or green is best) by using the underlined paint can.
      4. Highlight the colored row and the third row by clicking on the row numbers. Click on the paint brush and then click on Row 4 through the end of the spreadsheet. Every other row should be colored. It will pick up all formatting, not just the color, so avoid using the first row as a template.
   7. Highlight spreadsheet - CELLS - ALIGNMENT - Check WRAP TEXT again
   8. When color is use, the cells blend together. To put a border around the cells - highlight the area you want to print, Click on borders and select the framed cells.

NOTE - Problem with Formula format

1. If the formula does not change to the value of the ISSN in the cell you are moving and looks like this "=F456" after you click on the check mark, do the following.
2. Clear the cell
3. Type =, click on cell you want to transfer data, check mark
4. Control ` (Grave)
5. This should change the whole column so that it will display the value and not the formula.
6. If this does not work, highlight the column you are placing the formula in and change the column format to General.

Diane Carroll, WSU

MERGE MACRO

Nick Peterson from the Oregon Health & Science Library developed this EXCEL MERGE MACRO and Diane Carroll from WSU Liberaries developed the procedures for using it. Nick's Macro is designed to match data from two worksheets and to merge the information on the correct row on the Master worksheet.

The Merge Macro is located at WSU Research Exchange (Institutional Repository)

Directions

Example: Two worksheets contain the unique identifier or matching point (ISSN, bibliographic, checkin or order record number, titles in identical format, etc) and data from both worksheet needs to be merged onto the same row on one worksheet. In these directions, we will be matching on ISSN and merging use data to the Master worksheet.

• Prepare data to be merge
  • The Master worksheet is the worksheet that will receive data from a Contributing data worksheet
  • Both the Master worksheet and the Contributing data worksheet must be located in the same spreadsheet. Copy the Contributing data worksheet into the same spreadsheet that contains the Master worksheet.
    • Go to tab at the bottom of the spreadsheet that you want to move. Right click - move or copy. Select to book: the Master worksheet. Before sheet box - select where you would like the spreadsheet to be moved. Click on create copy box. OK.
  • Name the worksheets Master and Contributing
  • Sort the matching points on both worksheets. I will use ISSN for this example.
  • If data is not clean, for example, if there are trailing spaces at the beginning or end of the ISSN, the Merge Macro will not match them.
    • To trim the data, insert a column next to the ISSN and type the following into the first cell of the column:
      • =Trim(cell with first ISSN)
      • Drag the formula down the column. Highlight the trimmed data column. Edit Copy. Edit Paste Special. Click on Value. Delete the original data column.
  • IMPORTANT! All duplicate ISSN must be removed and merged by hand for both the MASTER and Contributing data worksheets.
  • IMPORTANT! If no matches work, clean the cells. Something not visible may be not allowing the MACRO to post on those cells. Use the same procedures as Trim but substitute the command =CLEAN()
    • To locate duplicates, copy the ISSN to another worksheet. Sort. Highlight the column.
    • Insert the label ISSN to the first cell in the column if needed.
    • Data - Subtotals - At each change in ISSN; Use function COUNT; Add Subtotal to ISSN. Check boxes: Replace current subtotals and Summary below data. Click Okay.
    • Look to the left of the screen and click the little box that says 2.
    • Add autofilters to row 1 (DATA-Filters-Autofilter)
    • Review all ISSN have have 2 or more hits and modify spreadsheets so these are eliminated before using the MERGE MACRO. If you don't you will double post information and get errors.
  • Create a new column on the Master worksheet labeled Use data. It is assumed there is a column on the contributing worksheet called "use data"
  • Create a new column on the Contributing worksheet labeled Match?

• Prepare Merge Macro
  • Open the spreadsheet with the two worksheets containing data to be merged.
  • Merge Macro is located at WSU Research Exchange (Institutional Repository)
  • The first time you use this, you may have to change the security setting on EXCEL. Tools - Options -Security - Look for box that says Macro Security. Try Medium or Low.
  • Click on Launch Merge Data
  • Select the spreadsheet containing the two worksheets to be merged
  • Worksheet A = Master; Worksheet B = Contributing
  • Select Match. Match Column Worksheet A = ISSN; Match column Worksheet B = ISSN
  • If there is a match, copy column Use data from Worksheet B (Contributing data worksheet) to column "Use data" on Worksheet A (Master worksheet)
  • I usually don't overwrite.
  • Click Merge
  • Go to Master worksheet and confirm that the correct matches were made.

• Prepare backward merge
  • Sometimes depending on how clean the data is, some merges will not be made. Regardless, you may still need to merge the titles, ISSN and data from the contributing worksheet to the Master worksheet. To do this, you have to know which rows merged and which did not.
  • I do a backward merge so that I can autofilter on the titles that did not merge and add them to the Master worksheet.
  • Click on Launch Merge Data
  • Select the spreadsheet containing the two worksheets to be merged
  • Worksheet A = Contributing; Worksheet B = Master
  • Select Match. Match Column Worksheet A = ISSN; Match column Worksheet B = ISSN
  • If there is a match, copy column Use data from Worksheet A (Master worksheet) to column "Match?" on Worksheet B (Contributing worksheet)
  • I usually don't overwrite.
  • Click Merge
  • If no data populates the "Match?" column, you know that data was not merged onto the Master and you may need to add by hand.