I have worked with some larger than usual (for qualitative inquiry) datasets through the course of a few projects that used CDC National Violent Death Reporting System (NVDRS) data. Sample sizes have been in the hundreds but case details are typically brief. The data also generally focus on a single (violent) incident although fortunately for qualitative researchers, there is often some contextual detail - although the amount varies from one case to the next. This summer I was provided with multiple opportunities to work with even larger samples - including some that include more than 1,000 cases. Like NVDRS data, case lengths vary and tend to be short. There are also clear categorical patterns that will allow for some quicker analyses - although how many things fit depends on how broadly you define a given pattern. Basically, I am trying to balance use of some automation and programming with hands on engagement with the data. I expect to have a few, maybe several more comments as these projects continue, but the point of this post is to describe my experience using the Microsoft Excel fill handles to copy data. What I was trying to do was paste a formula that totaled (or calculated the average of) a multiple item measure. I should mention this is a primarily quantitative dataset that also includes some lengthy open response items - which will be the focus of both the qualitative portion of a mixed methods cross sectional and a primarily qualitative longitudinal analysis. I found quickly that copy/paste down the row (not singly, but holding shift to move down) was pretty slow given 3600 + rows. I spent a little time looking for some sample visual basic code to use to copy the formula, since my ability to work with macros is pretty limited. While I was doing this, I found one recommendation to use the fill handles instead of pasting and I thought I would try this out. I have used the fill handles before, but usually just to copy a number or sequence of numbers and not for a formula.
To use the "fill handles," you establish the pattern in a few cells, select the cells, then drag the bottom right corner down. When you stop, Excel fills in the cells using the established pattern. For example, to fill a column with sequential numbers, type 1, 2, 3, in descending rows, select, and drag the corner (the "fill handle") down as far as you like. Excel will insert ascending numbers. If you want all 1s, then type 1, 1, 1, etc. Or if you want to number by 8s or 10s, Excel can figure this out given a few cells that show the pattern. I found out right away that the fill handles worked a lot faster than copying/pasting down a column. Better yet, I found that the lower I placed the curser - and even including beneath the workbook on my screen - the faster I could go down the rows. I could put the brakes on, so to speak, by moving back up. The brief video shows a little demo of how this worked on my Mac. Video made with ScreenFlow 9. The workbook is in Microsoft Excel for Mac version 16.39 and I am using the High Sierra OS. The data are real but this file includes no identifying information.
0 Comments
Leave a Reply. |
AuthorI am Sheryl L. Chatfield, Ph.D, C.T.R.S. I am a member of the faculty in the College of Public Health at Kent State University. I also Co-coordinate the Graduate Certificate in Qualitative Research and I am a member of the Design Innovation Team at Kent State. Archives
February 2024
Categories
|