Much data clean-up may occur within Excel, but also visit the OpenRefine site, it may be a useful tool for data clean-up
For identifying duplicate cells (counts the number of cells that meet a given condition): COUNTIF
For merging data across columns (adds the cells specified by a given condition): SUMIF
- SUMIF(range,criteria,[sum_range)
For replacing the last digit of a long string of numbers (ie MMS ID may have changed last digit from 1 to 0 in Excel) : REPLACE
- Count the number of digits, that are there and where you want to replace digit =REPLACE(cell#,16,17,"1")
- You can also try this formula if you count the digits LEFT(A1,LEN(A1)-1)&"1"
For ensuring that all of your numbers are in an ISSN format, use the following formula: TEXT
- A quick method is to use the custom number format option and create a custom format for 0000-0000 or xxxx-xxxx
- Another method is to use the TEXT(value,format_text) for example, =TEXT(A1, "0000-0000")
- If you have cells that aren't changed but you have 8 digits in the cell, try this formula =LEFT(4,A1) & "-" & RIGHT(4,A1)
- Remember to copy the cells and paste as numbers
For merging data from one dataset to another dataset using a common identifier number column: VLOOKUP
- Vlookup(lookup_value, table array, column index number, [range lookup])
- Let's say you have ISSNs in column A for dataset 1 and dataset 2 and you know that dataset 2 does not match exactly but you want to bring in a unique column that does not exist in dataset 1. Enter =Vlookup and click on the ISSN cell in column 1, dataset 1, ie. =vlookup(A2,
- Next, after entering a comma, go to dataset 2 and drag cursor over all of the data (except the headers), ie. =vlookup(A2, Sheet1!A2:M75,
- Next, after entering a comma, figure out what column your data is in, if it's in the 3rd column enter the number 3, so the formula should look like =vlookup(A2, Sheet1!A2:M75,3,
- Next, after entering a comma, enter the term false (for exact match), if you enter true you will be asking for an approximate match; ie. =vlookup((A2, Sheet1!A2:M75,3, false)
Calculate Cumulative
- =Portion/total in first cell (ie G2)
- =(Portion/total)+(Portion/total) ie =G2+(E3/852458)
Not sure if all your ISSNs have a dash (-)?
- Use the column 'Text Filter', choose 'does not contain' and search for -
Want to change the case of your cell?
- Use the formula =proper(cell#) or =upper(cell#) or =lower(cell#)
Seeing an error because your cell is stored as text when it should be stored as a number?
- Highlight the column and select 'Text to Columns' located under the Data tab, once it is open click Finish
Want to drag the same text or a formula down a column without using copy&paste?
- Go to the bottom right of the cell and look for a cross cursor, click on the cross and drag it down the column
Do you have spaces before or after your number within a cell?
- Highlight the column, then use CTRL F to bring up the Find & Replace box. Go into the replace tab and enter a space. In the replace with box make sure you have no spaces and click replace all
Do you have a column of cells but you prefer to have these same cells in a row?
- Copy the column and go to the Paste special option (option found with the right click), choose the transpose check box
Want to resize all your columns at the same time?
- Click on the square in the top left of the screen to highlight all the cells. hover over one of highlighted column lines, click on line and drag it
Want to quickly highlight your table?
Need to quickly check for the sum of column?
- Click on the top of the column to highlight all cell in at column and then look at the bottom right corner, the sum will be displayed
- The bottom right corner will provide the sum, average and count of any highlighted cells
Annoyed that the MMS ID number abbreviates itself when copied into Excel?
- Click on the column and choose the Number option dropdown, select number and click on the ->.0 to get rid of added decimals
- If you store the MMS ID as a text field before pasting it will avoid the automatic abbreviation
Is it possible to add delimited text (from a downloaded dataset or some other text file) into an Excel spreadsheet?
- Go to the Data tab of the Excel sheet and select 'From Text', you'll be asked to import your saved text file
- Next you'll see a Text Import Wizard box, select the Delimited radio button and click Next
- Select how your text is delimited (tab, semicolon, comma, space or other) and then click Next and then Finish
For removing all spaces from a text string except for single spaces between words: TRIM
Publication years have lots of unnecessary symbols?
- Use the find & replace option CTRL+H to remove any unnecessary symbols with a blank
- Sort the data to identify years that may easily be standardized with the copy and paste function
