Graphing Bureau of Labor Statistics Data to Track Trends in Technical Writing Employment

As a second response to the same LinkedIn conversation as mentioned in the The Complex Web of Industries Technical Communicators Support post, I created the following short procedure explaining how to make your own Bureau of Labor Statistics (BLS) graphs tracking the top client industries of technical writers nationwide:

1. Go to http://www.bls.gov/oes/tables.htm
2. Download the National industry-specific and by ownership Excel files for the years that you desire to include in your final graph (for example, download oesm11in4.zip and extract file nat3d_M2011_dl.xls)
3. In Excel, select all of column D (occ_code). Go to the Data tab and click the Filter button.
4. At the upper, right corner of column D, a down-triangle will appear. Click it to display a Filter pop-up window.
5. In the Filter window, de-select the Select All checkbox, select the 27-3042 checkbox, and click the OK button.
6. Create a new Excel file and name it TechWritingByYears.
7. Select all rows in the original BLS Excel file and copy them to a new spreadsheet in your TechWritingByYears file. Create a new spreadsheet for each year to be included. Repeat steps 1-5 for each of these years and copy the rows into the year-based spreadsheets in TechWritingByYears.
8. Sort each new spreadsheet on column G (tot_emp).
9. Copy the top rows of the first spreadsheet to a new file (name this one Graph) with new spreadsheets for each year.
10. Repeat steps 8 and 9, copying the top rows of each TechWritingByYears spreadsheet to the corresponding Graph spreadsheet.
11. Create one more spreadsheet within the Graph file. Name this spreadsheet Actual Graph.
12. Copy the top rows out of the first year's Graph spreadsheet and into the the Actual Graph spreadsheet. Eliminate all columns except for the naics, naics_title, and tot_emp columns.
13. Take note of the naics code for each of the technical writing client industries in the Graph spreadsheets created in steps 10 and 11 (for example, naics code 541000 for Professional, Scientific, and Technical Services). (That is, take note of the naics code on each row.)
14. From the Graph year-based spreadsheet for the second year, copy the tot_emp cell from the row that has the naics code corresponding to the naics code for the first row of the first column. Paste the value one column to the left of the cell containing the first year's tot_emp for that naics code. Repeat this process for the second row and each following row until you build an entire column (accounting for all of the technical writing client industries for that year).
15. Repeat steps 13 and 14 until you fill each column for each year. If the titles naics_title and tot_emp are on the spreadsheet, remove naics_title and replace each tot_emp with the year for that column. Delete the naics code column. At the end, you should have a table like that shown below.

Select all rows of the table, go to the Data tab and generate a Line graph.