Technical Tips from the Field
Assorted Excel Tech Tips from MdAIR
This corner of the newsletter is reserved for readers to share practical tips, techniques and shortcuts that can make a difference in our work. Please share your technical prowess with us and send your tips to Gayle Fink (mailto:gfink@usmd.edu). Since this is a small corner, KISS (Keep It Short and Smart) will work the best.
I put out a general call (plea actually) to my MdAIR colleagues for help with this month’s Tech Tip. Below are the Excel-related ones I received. I also got suggestions like “when XYZ program crashes just reboot and pray!” and a recipe for Grilled Red Snapper Stuffed with Fresh Salsa and Cilantro. Have not tried the red snapper but I have used the other tips including talking to the ultimate being! Thank you Michelle Appel, University of Maryland, College Park; Rebecca Bell, University of Maryland, Baltimore; Lou Cox and Alan Harmon, US Naval Academy; Jean Marriott, Carroll Community College; and Selina Smith, Frostburg State University; for sharing your favorite Excel Tech Tip. If you have a favorite Tech Tip, please send it my way: gfink@usmd.edu
“I got this tip from you at an IR director's meeting a couple of years ago and I have used it probably every day since I downloaded it!! It is great for filling in leading zeros, for finding duplicates in a list, coloring each n'th column or row in a selection, etc. And it is free!” I second this especially if you are not on Office 2007!
“The problem was dropped leading zeros when importing data as numbers into Excel (such as IDs for example). Highlight column, left click to select format cells, under the Number tab, click on Custom, and type in a zero for each space that the ID (or whatever variable) should take up. In this case there would be 9 zeros.” Notice a trend about how to fix the leading zero issue!!!
“I use MS Excel’s “concatenate” function to create MySQL scripts to load up MySQL tables. Example: I’m given a list a spreadsheet of usernames and corresponding information such as phone number, address, etc. My goal is to put that into a script that can load the values into MySQL. If we have a simple table, called “students” that has 3 fields, let say “name”, “phone_number”, “city”, in that order, the format for a simple script would be: INSERT INTO students VALUES (‘Gayle Fink’, ‘410-XXX-XXXX’, ‘Annapolis’); (the semi-colon is a part of the script). What I would do is this:
Open my Excel spreadsheet with the data I need to convert to a script. Go to the first empty column – that will be my formula column. I click in the cell in the formula column that corresponds to the first row of data (if you have a header row, your first data would be in row 2, if no header row, it would be row 1). Example if column G is my formula row and I have a header row, I would click in cell G2. Next I insert function “concatenate”; I use the formulae wizard to make things easy. I then fill in the cell using a combination of text and other cell values to get the script I need for that row of data. Then I drag that formulae into the rest of that column so that I now have a formula for every row. I copy that formula column and paste “values only” into a new column. I can then copy the cells containing the ‘values only’ scripts into a MySQL script tab. This can be used for other database systems as well, not just MySQL.” This one has saved me on several occasions!"
“I use MS Excel (or some spreadsheet application along with a plain text editor) when importing information/data from one software package to another when there is no ‘easy way’. Example: A customer sends me a survey in pdf and I need to enter the questions as variable labels into SPSS. I open the pdf file and copy the contents. I paste that into a plain text editor and strip out all the non-question text. I then format the questions so they are each on one line. I then ‘select all’; copy; and paste into the first label cell in SPSS. When I paste into the first cell, the cells below that first cell are automatically filled in; each line break that is in the plain text file is what tells SPSS to go to the cell below the current one. One may think it would be just as fast to copy each individual question straight from the pdf file and put it into an individual SPSS label cell, but that is not the case; using the plain text editor is much faster for me. Also, if you do decide to copy the questions one for one from the pdf file, when you try to paste them into SPSS, any line returns in the pdf file tells SPSS to advance to the next label field – that results in questions that span multiple lines being spread out in multiple label fields.”