upload image

US Black Heritage 1880 Project - New Spreadsheet Instructions

Privacy Level: Open (White)
Date: [unknown] [unknown]
Location: [unknown]
This page has been accessed 2,717 times.


US Black Heritage Project

See the US Black Heritage 1880 Project page for more information about the 1880 census project. Our goal with this project is to create a profile for every Black American enumerated in the 1880 US federal census.

If you'd like to help with getting spreadsheets set up so that we can coordinate our work on the 1880 census project and track our progress, please follow the directions below.

Contents

Creating Spreadsheets for Small Counties (under 4900)

Please follow the process below to create spreadsheets for any counties where there fewer than 4900 African American residents. Population numbers for each county can be found on the 1880 census project's free space pages for each state, which you can find on the project's main page here.
  1. Find a spreadsheet to work on:
    • We have a list of counties that WikiTreers have requested spreadsheets for in this requests spreadsheet - if you'd like to work on these requests, please make sure to read the Directions tab first.
    • If there is another county you'd like to work on, check the "Available 1880 Spreadsheets" section on the US Black Heritage 1880 Project page to see if the specific location you want to work on already has a spreadsheet. If not, contact Emma or Christy so that they can get it set up for you - they will assign a spreadsheet for the county. Please do not add tabs to existing spreadsheets yourself unless you have discussed it with Christy or Emma first.
  2. Set up your search on FamilySearch: Go to this FamilySearch search results page. In the residence search box, replace "Gallia, Ohio" with the county and state you are working on, then click on the Search button to refresh the results. Make sure that at the bottom left of the search results page, it is set to view 100 "Results per page" - this setting should already be selected if you started with the linked example search.
  3. Check the number of search results: Check that the number of search results you get for the county is pretty close to the population number in the "Colored"-"1880" column for the county in the Table V in this document, on pages 380-415 (images 4-41) - this number should also be listed in "Size" column in the requests spreadsheet or the "Black Pop." column of the tables on the free space pages for each state for this project (see index here). If the numbers are significantly off, please contact Emma or Christy before proceeding. Also contact them if you're getting a lot of search results for other counties besides the one you're working on. There are a few suggestions in the Tips & Tricks section below that may help you resolve the problem yourself, but if you can't get the numbers to match, contact Christy or Emma before doing any more work on the county.
  4. Make sure all African Americans for the location are selected: Click on the "Race" filter button above the search results. "Black" and "Mulatto" should already be selected, but check to make sure there are no other options that would refer to African Americans. If you see additional options, add those to the selection.
  5. Export a page of search results: Click on "Preferences" above the search results. In the "Export Search Results" section, make sure XLS is selected and that the number of results is set to the maximum number (100). Then click on the "Export Results" button.
  6. Import the data into the spreadsheet: Open your spreadsheet, then go to File>Import. In the Upload tab either browse to find the .xls file you just downloaded or drag and drop this file into the window. When the "Import File" box pops up, choose "Insert New Sheet(s)" and then import the data.
  7. Repeat until all data you need is imported: Go back to FamilySearch and go to the next page of search results. Repeat the process of exporting the data from FamilySearch then importing the data into the spreadsheet. You'll need to keep repeating this process for each page of results until you have all of the data for the location imported to the spreadsheet.
  8. Consolidate into one spreadsheet tab: Copy and paste all of the data into one single tab of the spreadsheet.
  9. Cleanup and Formatting: Follow the example of the Lawrence County tab from the Ohio spreadsheet to delete the unneeded rows and columns, rename the remaining columns, and adding in extra columns for WikiTree ID's etc. A few tips:
    • Freeze the top row so that the column headings are still visible when users scroll down. Go to View>Freeze>1 row.
    • For the columns for Residence, Children, and Other Household Members, set text wrapping and make the columns wider so that the information can be seen without spilling into adjacent columns. For text wrapping, go to Format>Wrapping>Wrap.
    • At this point in the process, do not add the additional row at the top with the total/profiles created counts. It will get added at the end.
  10. Split the name column so that we can sort by surname: Add three columns to the right of the name column. Select the whole name column, then go to Data>"Split text to column". Of the four columns you have for names now, make sure that all surnames are in the second column from the left. If people have a middle name or initial, the surname may be in the third or fourth name column and you'll need to move it to the second column. To save time, don't worry about moving middle names into the first column. When you have finished this, delete the extra name columns, leaving just one column for first name and one column for surname. Move the surname column to the left of the first name column. See Tips and Tricks for several tips about how to make this step easier and for how to do this on a Mac.
  11. Add county column for spreadsheet tabs with multiple counties: If a single tab is going to have multiple small counties, please add a column titled County to the right of the residence column and add the county name in that column. You should be able to highlight the column for everyone on the spreadsheet and paste the county name into all the rows at once.
  12. Split tabs if over 2000 names: If the county you are working on has over 2000 people included, the list of names will need to be split up into two tabs to limit the amount of scrolling users need to do. It's generally recommended to split the tabs according to surname:
    • Sort the tab by surname: select the whole spreadsheet, then go to Data>Sort range>Advanced range sorting options; check "Data has header row"; sort by surname.
    • Cut & paste the appropriate number of rows into the additional tabs. They should have approximately the same number in each tab, but it does not need to be exact. Make the split in between two letters of the alphabet for the first letter of the surname (i.e. the first tab may have A-M surnames and the second tab would have N-Z surnames).
    • Rename each tab to help people navigate - something like "Franklin County: Surnames A-M" and "Franklin: N-Z". The first tab has a little more info to make it clear what is in each tab, the second tab is more abbreviated so we can fit more tabs in each spreadsheet.
    • If you need help with this any of this, contact Emma MacBeath or Christy Melick.
  13. Sort into families: This will need to be repeated for each tab for the county. Select the entire tab. Go to Data>Sort range>Advanced range sorting options. Check "Data has header row". Sort by County (if applicable), then Residence Location, then Surname, then Children, then Spouse, then Father, then Mother, then Other Household Members. The county column will only be an option for tabs that have multiple small counties in a single tab.
  14. Add counters at top: Add an additional row at the top and follow the example of the Lawrence County tab from the Ohio #1 spreadsheet to add counters for the total number of people in the spreadsheet and the number of profiles created.
    • If you are working on a tab with multiple small counties, you can use the more complex formulas shown in the "Cleburne, Covington" tab of the Alabama #10 spreadsheet. If you have more than two or three counties in a single tab, it would be better to add a tab named "Tracking" and put the counters for this tab there so that the top of the spreadsheet doesn't get too cluttered.
  15. Have leaders check your first spreadsheet: After working on your first county, please have Emma MacBeath or Christy Melick check it before you create any more.

Creating Spreadsheets for Large Counties (more than 4900)

Both FamilySearch and Ancestry have a limit to the number of search results you can access with a single search - at most 4900 (FamilySearch) or 5000 (Ancestry). So although the top of the search page may say there are 12,000 search results for a county, you'll only be able to access the first 4900. Because of this, we need to break down the search results for larger counties into smaller groups when creating spreadsheets. This also breaks it down into smaller sets that are easier for spreadsheet creators to manage and that are easier for the eventual users of the spreadsheets to navigate in order to find the people they're looking for.
While you are following the process below, please be sure you are checking along the way that the numbers add up. There are known problems with locations not always being indexed correctly (see bullet point 2 in the Tips and Tricks section below), so we want to stop at the first point where we run into problems so that you don't waste a lot of time working on a spreadsheet that needs to be completely redone because it's missing a large number of people. Please stop as soon as you run into any problem and contact Christy or Emma so that we can help you work it out before you put a lot of time into it. It's frustrating for everyone involved if there is a problem with a spreadsheet and it has to be completely redone, and that frustration is magnified when working on a large county spreadsheet that takes even more time to create. There's no rush - these large county spreadsheets are going to take some time to finish and there are plenty of spreadsheets already available for people to work on. It's better to pause when you need to so that you can get guidance, rather than to have to redo a ton of work.
If you are new to this process, please contact Emma or Christy after doing one tab of a spreadsheet to have your work checked for the same reason - we don't want you to do a whole huge county before realizing you've missed an important step in the process and now have to redo a ton of work. In fact, it is strongly recommended that before working on one of these large counties, you pick a small county (less than 1,000 African American residents) for your first spreadsheet attempt so that you can get the process down before moving on to work on a large county. We would much rather spend extra time answering questions and checking your work along the way than to have you finish a large spreadsheet and then tell you there's a problem and the whole thing has to be redone.
  1. Contact Christy or Emma to get a spreadsheet created for you to work from. You can choose from one of the requested locations in this requestspreadsheet or choose another large county that doesn't have a spreadsheet yet. They'll send you a link for one or more spreadsheets to use. Please do not create any new spreadsheets yourself or add tabs to an existing spreadsheet for these large counties. For counties that have a population over 9,000, once the tracking tab has been created, multiple people can work on one county, keeping track of their work on the tracking tab for the county.
  2. For counties with an African American population between 4,900 and 9,000, the spreadsheet will have several tabs for the county with a link at the top of each tab. That link will have part of the county for you to work on, usually divided by the first letter of surname, i.e. the first tab will have surnames A-L and the second one will have surnames M-Z. For these counties, use the linked search at the top of the tab, then generally follow the instructions above for smaller counties to import search results and then format the tab. The search links can be deleted when you've finished the tab. Let Christy or Emma know when you have all tabs for the county completed so that they can get the spreadsheet linked in the right places and let anyone who requested it know it's ready.
  3. For counties with an African American population over 9,000, the spreadsheet will need a tracking tab, as seen in this spreadsheet for Lafayette County, Mississippi. If this tracking tab is not already set up when you are sent a spreadsheet to work on, you'll need to follow this example to divide the search results for the county into groups of ~2,000 (anywhere from 1,500 to 2,500 is fine - we're just trying to break it down into manageable chunks).
    • To create a tracking tab, first verify that just searching for the whole county gives you approximately the number of search results expected for the county based on the population tables on each state's free space page for this 1880 census project - see index here. If the number is significantly off, please contact Christy or Emma to discuss how to proceed for the county.
    • Divide search results into manageable groups. Generally, it's been working best to divide counties up by surnames. You can put a* b* c* d* in the surname search box, for example, to get anyone with a surname that begins with a letter from A to D. You can copy/paste from here to make it easier: a* b* c* d* e* f* g* h* i* j* k* l* m* n* o* p* q* r* s* t* u* v* w* x* y* z* . In some cases, you may be able to divide a county by location, as shown in the tracking tab for Charleston County, South Carolina in this spreadsheet. However, this seems to work best when each census location for the county is named for a city/town/township rather than being designated as something like "Ward 11", "Beat 10", or "District 4" - those tend to be badly indexed on FamilySearch and difficult to work with for these purposes. In some cases, a combination of locations and surnames may be possible/necessary. If you are having trouble with getting the search results divided up into manageable groups of about 2,000, please stop and contact Christy or Emma for assistance. As you get it divided up, follow the Lafayette County, Mississippi example to keep track of what is in each group, a link to the search results for that group, and how many are in each group.
    • Check the numbers again. After getting the county divided up, check to see that the number of results in each group add up to approximately the expected number of African American residents in the county. If they do not and you cannot find the problem, stop and contact Christy or Emma for assistance.
    • Mark the "Tracking Tab Complete" column of the requests spreadsheet with an X when you have finished creating this tab.
  4. Add tabs if necessary. If it has not already been done before you were sent a spreadsheet to work on, add a tab for each of the county divisions from the tracking tab and give them a name that will help people using the spreadsheet find the people they are looking for. To keep things manageable and navigable for users, counties over 20,000 will be split into multiple spreadsheets, with an additional spreadsheet being added approximately every 20,000 residents. So a county with 60,000 African American residents would be divided into three spreadsheets with 10 tabs each.
  5. Work on one tab at a time. Work through the county one tab at a time, using the searches from the tracking tab. Before starting work on a tab, put your WikiTree ID in column D of the tracking tab so that all of our spreadsheet-creators can coordinate their efforts. For each tab, you will follow the same process used for small counties, just using the search from the tracking tab instead of a search for the whole county. When you have completed a tab, mark it as complete in column E of the tracking tab.
  6. When all tabs for a county are complete, put an X in column G ("Completed") of the requests spreadsheet. Christy or Emma will then double check it, get it linked in the appropriate places on the project pages, and contact anyone who had requested that county's spreadsheet.
  7. For some of the counties requested by the USBH project's state teams, we may ask you to copy/paste into different tabs for locations so that each tab is a single location in the county. For a number of reasons (please trust me on this), it is easier to export data using surnames than to do it by location, so in these cases, we will first follow the above process, exporting in groups by surname and then sort into locations.

FAQ's about Large County Spreadsheets

  1. Why aren't we dividing counties by location instead of surname?
    It is much easier to do the divisions by surname, especially for locations you're not familiar with, and it will consistently work for all counties so that we don't have to make a more complicated process with fallback plans for counties where division-by-location won't work. We want to minimize the time that is needed to create each spreadsheet and keep the process as streamlined as possible. Specifically:
    • Dividing by location requires doing additional research to determine what all the locations are in that county and how exactly they are recorded in the 1880 census and its transcription on FamilySearch or Ancestry. Division by surname requires no additional research as you start working on the tracking tab for a county.
    • We anticipate that some very large counties may have towns/districts/etc. that have more than 4900 African American residents and will need to be further split up by surname anyways. Starting with surname divisions means we don't have to have a separate process in these cases.
    • Dividing counties up on Ancestry is already more complicated because you have to do each search twice for Black and Mulatto since you can only enter one term at a time in this searchbox. On FamilySearch, for some counties, the county is accurately indexed, but the individual districts within that county are not really searchable - this especially seems to be the case when locations are numbered districts/wards/beats. Because of this, dividing counties by surname on FS is the easiest way to do it when the county is accurately indexed there.
    • It is easier to get tabs to equal approximately 2000 people and succinctly name each one so that people know what is in it if you divide by surname. This means that overall, we can reduce the number of tabs that are needed in each spreadsheet which makes it easier for users to navigate.
  2. I've read everything above, but I'd still rather divide this county by location than by surname. Can I?
    If you really feel like you need to do it by location and you can get all of the numbers to add up correctly by yourself, you can. Support will generally not be provided to help divide it up this way, though. It is also very important that you add a tracking tab with links to the searches you plan to use so that others can continue your work if something happens and you don't finish it. Otherwise, if it takes too long with no response from you, your work will have to be deleted and other volunteers will have to start over dividing it by surname.

Using Ancestry to Extract Data

Some locations are returning very poor transcription results on Family Search. In those cases, it may be best to use Ancestry. The 1880 census is free on Ancestry.

  1. Go to the main 1880 Census page on Ancestry.
  2. For smaller counties, leave the name fields blank.
  3. Put the <county, state> in the "lived in" field and mark it "exact."
  4. You will need to do two separate searches. 1) Put "Black" in the race field and mark it "exact." (Example) 2) Put "Mulatto" in the race field and mark it "exact." (Example)
  5. Click "Search" and pull up the results.
  6. Copy/paste the entire results section from Ancestry to the spreadsheet tab you are working on. Do this for each page of results.
  7. Start at #9 in the first set of instructions above and follow the steps for formatting and sorting the data on the spreadsheet.
  8. You can use the Tennessee #2 spreadsheet as an example of how to name columns and generally format the spreadsheet. The number and types of columns you get when extracting data from Ancestry are different than FamilySearch so it will look a little different.

For larger counties, the same instructions apply, however, you will need to follow the tracking and breaking down by last name instructions above in the large counties section.

Tips and Tricks

  1. If you get more search results than expected on FamilySearch, there is a "Residence" filter above the search results that you can use to narrow down to the desired county. This may eliminate some of the extra search results.
  2. The residence locations on FamilySearch in some cases don't match what is in the census image. If you're getting results that don't appear to match the location you're searching for after applying the residence location filter described above, check some of the individual record pages for the unexpected location. You'll see an "Event Location" which is the incorrect location that shows in the search results page and an "Event Location (Original)" which is generally the location that actually appears on the original census document. The search results we use to create spreadsheets seem to be based on the original event locations (which are correct) even though the residence location that gets displayed and gets downloaded into the spreadsheet is the incorrect one. If this is the case, please still include these results in the spreadsheet if the "Event Location (Original)" matches the county you're working on. If you'd like to replace the incorrect residence location with the correct one in the spreadsheet, that would be helpful.
  3. How to split a column on a Mac
  4. Before splitting the name column, it may be helpful to highlight that column then to find and replace to fine Mc plus a space ("Mc ") and replace it with Mc without a space ("Mc"). Oftentimes surnames that start with Mc have been indexed with a space between Mc and the rest of the surname and this makes sure the surname doesn't get split up into separate columns.
  5. After splitting the name column, it might help to sort the spreadsheet by all of the columns with names in them from right to left - this way, you can select all of the cells that have the surname in the same column at once and move them over to the column you want them to be in.
  6. Occasionally, you may need to add more columns to properly split the name column. Sometimes people have multiple middle initials/names.




Collaboration


Comments: 14

Leave a message for others who see this profile.
There are no comments yet.
Login to post a comment.
In working on Fauquier County, VA, from Ancestry, I'm finding that the search for surname breakdowns, e.g., A-D, also brings up other last names who are in A-D households. One example was a married daughter with a non-A-D last name, in her father's household. I assume those should be left on the tab with the head of household (in this case A-D), to simplify working with households rather than having these show up as Family of One.
posted by Judith Booker
For now, let's just keep these like you say. When I have time to investigate the issue further, I might update the instructions for them.
posted by Christy Melick
Alabama #4 spreadsheet for Coffee County has tabs for the alphabet breakdown that are labelled Conecuh, rather than Coffee. I assume this is just an artifact of copying another spreadsheet, but I hesitate to change it until I can confirm that. Thanks!
posted by Judith Booker
Hi Judith, the tabs are correct as created. Coffee county gets only one tab and Conecuh get the next several tabs since it's bigger. We often use one spreadsheet for several counties. Thanks!
What is the procedure for using Ancestry? The numbers for the county I'm working on aren't very close in familysearch and Emma suggested I use ancestry. I don't see a way to export in Ancestry.
posted by Judith Booker
Hi Judith, took me awhile to figure out that you don't need to export from Ancestry; you can just highlight the 1st page of search results and copy and paste directly into the Google spreadsheet. Then go to next page of results and repeat.
posted by Katie (Cooper) Bryant
Posting that I answered Judi by private message, but also, Christy, if you're reading this, we need to put a couple of instructions somewhere about how extracting from Ancestry is different from FS :-) Emma
I was able to copy and paste the data from Ancestry into the spreadsheet for Montgomery County, New York. However, the formatting instructions for FS don't work for Ancestry. Can someone explain how to format the data, please?

Thanks!

posted by Jaki Erdoes
Which part of the formatting, Jaki? The sorting is the same. If you need an example spreadsheet that uses only Ancestry, check out this one https://docs.google.com/spreadsheets/d/1fqcjG8ZoJtrfRFk1uQ_bDopJXir74zzl94nrcGBsV94/edit?usp=sharing
Hi Emma and Christy,

I believe I have Clinton and Scott Counties set up correctly on their tab for Iowa . Clinton County is listed first, then Scott County below. I've also created a profile for the first person under Clinton County. Please check my work and let me know if there is anything amiss.

posted by Heather (Shear) Ford
Thanks Heather! The spreadsheets and the profile look good.
posted by Christy Melick
I need spreadsheets created for the following Texas counties: Caldwell and Gonzales.

Thank you. Camilla

posted by Camilla Hall
Hi Camilla, We are so delighted that you are interested in working on those two counties. Currently we are overwhelmed with requests for new counties. If you wouldn't mind helping out on some of the other Texas county spreadsheets until we can get those done. You can find them here: https://www.wikitree.com/wiki/Space:US_Black_Heritage_1880_Census_Project%2C_Texas

I'll add your county requests to the list. You can check back periodically to see if the spreadsheets are on there. But just understand it might be awhile.

posted by Gina (Pocock) Jarvi
Hi Camilla - the Gonzales County spreadsheet has been created and we'll let you know when Caldwell is ready.
posted by Christy Melick