U.S. flag

An official website of the United States government

Department of Labor Logo United States Department of Labor
Dot gov

The .gov means it's official.
Federal government websites often end in .gov or .mil. Before sharing sensitive information, make sure you're on a federal government site.

Https

The site is secure.
The https:// ensures that you are connecting to the official website and that any information you provide is encrypted and transmitted securely.

Occupational Requirements Survey
Bureau of Labor Statistics > Occupational Requirements Survey > Publications > Factsheets

Using Pivot Tables to compare and analyze multiple occupational requirements

PDF version

The Occupational Requirements Survey (ORS) publishes job-related information on physical demands; environmental conditions; education, training, and experience; as well as cognitive and mental requirements. ORS can be used to identify occupations that have different combinations of these requirements. Understanding occupations with multiple specific requirements can be useful to job seekers, vocational and rehabilitation experts, human resource professionals, hiring managers, safety and medical professionals, and the disability community.

Follow the steps below to use spreadsheet tools to view and analyze multiple occupational requirements.

Steps:

    1. Download and open the ORS complete dataset.

    2. Click Enable Editing at the center top of the screen.

    3. Go to the ORS 20XX dataset tab at the bottom of the sheet.

    4. Within the ESTIMATE column (column N), are range values that include a less than (<) or greater than (>) symbol. Prior to creating a pivot table, all cells in column N will need to be converted to a numeric value (shown in the next step). Some options to consider when working with range estimates:

        1. Remove all range values from the dataset

        2. Remove less than or greater than symbols, but retain the range value

        3. Assign a different numeric value that denotes estimates as range estimates (i.e., a value of 100 for all greater than ranges)

    5. Convert estimates to numbers:

        1. Select first data cell in the ESTIMATE column (N2).

        2. Hold Shift+Ctrl+Down Arrow to select all estimates (column N).

        3. Click on the exclamation point symbol at the top of the column.

        4. Select Convert to Number.

        Image with a Pivot Table icon with a drop down arrow  

      1.  Select Insert > Pivot Table.

        Image displaying pivot table icon  

      2. Hit OK. Note: The pivot table tool will automatically select the complete ORS dataset as your table range and create a new sheet with the table.

        Image, letting one select a table or range within Excel with a red arrow pointing to the OK button  

      3. In the PivotTable Fields area, grab and drag the following fields to the following areas:

          1. OCCUPATION -> Rows

          2. ESTIMATE TEXT -> Columns

          3. ESTIMATE -> Values

          Note: The fields you selected and moved should all be checked.

          Graphic with a dashed red arrow showing how to drag a field to the Columns area
               

          1. In the Values area, click the drop-down menu beside Count of ESTIMATE > Value Field Settings.

            Graphic with red arrow pointing to the view field settings option  

          2. Select Sum and hit OK.  Note: Sum is only a method for displaying the estimate value. No summation calculation is done.

            Graphic with red arrow pointing to the Sum option when selecting how to summarize the value field  

          3. Select multiple requirements from the Columns Labels drop-down menu.

              1. Click on the Column Labels drop-down menu arrow.

              Red arrow pointing to where to click on the Column Labels drop down menu
                     

                1. Unselect Select All.

                2. Expand the menu to view the complete estimate text titles by hovering your cursor over the lower right-hand corner of the window until a double-headed arrow appears. Drag it and expand.

                Image with red arrow showing select all button
                     

              1. Search for the first estimate you wish to analyze by placing keywords in the text box below Value Filters. Select the estimate(s).

                  1. Check all the requirements you wish to analyze identified by the keyword search.

                  2. Click OK.

                  Red arrow pointing to a filter selection and the displayed text  

                1. Remove the Grand Total column by selecting the Grand Total column heading cell (i.e., the cell in row 4), right click, and select Remove Grand Total from the drop-down menu.

                  Graphic showing how to remove the Grand Total column by selecting Remove Grand Total from a drop down menu  

                2. Format the pivot table.

                    1. Select column B.

                    2. Hold Shift+Ctrl+Right Arrow to select the remainder columns to the right in the worksheet. All the columns should be grey indicating they are selected.

                    3. Right click somewhere in the worksheet (without selecting a cell) and select Column Width from the drop-down menu.

                    4. Enter 20 and click OK.

                    5. Click Wrap Text in the Home tab.

                    Graphic of red arrows showing where to click on Wrap Text on the Home page. An arrow also points to the highlighted Column Width option in the drop down menu
                           

                  1. As you did above, search for additional requirements you wish to analyze using key words.

                      1. Select the additional requirements and check Add current selection to filter.

                      2. Follow the same procedure to add additional requirements.

                      Graphic with red arrows showing again where to enter and search keywords and then select additional requirements. An arrow points to a box to check to Add current selection to filter
                           

                    1. Copy and paste as values all the data in your newly created pivot table sheet to have all data analysis tools available to analyze the data. Note: Analysis tools are limited within pivot tables.

                          1. Create a new sheet to conduct your pivot table data analysis. Right click on any tab and the bottom and click Insert > Worksheet > OK.

                        Graphic of a red arrow pointing to a Pivot Table Data Analysis tab title
                               

                          1. Select all the data in your pivot table sheet by clicking on the triangle in the upper left corner of your worksheet.

                          2. Hold Ctrl + C to copy the sheet to your clipboard.

                          Red arrow showing how to click on the triangle in the upper left of the worksheet to Select All
                                 

                            1. Select cell A1 in the new sheet you created to analyze your pivot table data.

                            2. Right click and select the Values (V) icon under Paste Options.

                            Graphic with red arrow pointing to where to select the Values icon under Paste Options
                                 

                          1. Format the new sheet.

                              1. Expand column A so that all the occupation titles are visible. To expand the width of the column, place your cursor on the border of column A, click and drag to the width desired.

                              Red arrow showing where to place your cursor on the border of a column to expand its width
                                     

                                1. Format the remaining columns as you did in the Step above.

                                2. Delete the top 3 rows so filters can be applied to the requirement columns. The estimate text headings should be in row 1.

                                Graphic showing an Excel Worksheet after all the formatting directions have been implemented
                                     

                              1. Analyze the data using the data analysis tools. Here is one example of using these tools to display only the occupations that have mean Hours of Standing equal to or less than 4 hours.

                                  1. Turn on the filter tool by selecting the Filter icon in the Data tab.

                                  Graphic with red arrows showing how to turn on a filter tool by selecting the Filter icon in the Data tab
                                         

                                    1. Click on arrow in the Hours of standing, mean column and select Number Filters from the drop-down menu. Then select Less Than Or Equal To.

                                    Graphic with red arrows showing where to use the Number Filters tool in the column drop down menu
                                           

                                      1. Enter 4 and select OK.

                                      Graphic with a red arrow shows the “is less than or equal to” criteria in a numbers filter and where to add your value.
                                             

                                         

                                     

                                    Additional resources:

                                     

                                    Articles:

                                    For additional information on occupational requirements see the ORS homepage or download the ORS complete dataset to explore the latest estimates.