{"id":167,"date":"2016-10-18T00:06:49","date_gmt":"2016-10-18T00:06:49","guid":{"rendered":"https:\/\/pressbooks.usnh.edu\/math502\/chapter\/table-basics\/"},"modified":"2023-03-24T14:26:17","modified_gmt":"2023-03-24T14:26:17","slug":"table-basics","status":"publish","type":"chapter","link":"https:\/\/pressbooks.usnh.edu\/mth402\/chapter\/table-basics\/","title":{"raw":"Table Basics","rendered":"Table Basics"},"content":{"raw":"
\n
\n

Learning Objectives<\/h3>\n
    \n \t
  1. Understand table structure.<\/li>\n \t
  2. Plan, create, and edit a table.<\/li>\n \t
  3. Freeze rows and columns.<\/li>\n \t
  4. Sort data in a table.<\/li>\n<\/ol>\n<\/div>\n \n\nThis section reviews the fundamental skills for setting up and maintaining an Excel table. The objective used for this chapter is the construction of a multi-sheet file to keep track of two cities\u2019 national weather data for the month of January. Organizing, maintaining, and reporting data are essentials skills for employees in most industries.\n\nFigure 2.91<\/strong> shows the completed workbook that will be demonstrated in this chapter. Notice that this workbook contains three worksheets. The first worksheet lists average weather for January in Portland, Maine. The second sheet lists average weather data for January in a very different climate \u2013 Portland, Oregon. The third sheet adds a weekly column to the Portland, Oregon data so that it can be subtotaled by week.\n\n \n
    \n\n[caption id=\"\" align=\"alignnone\" width=\"819\"]\"Completed<\/a> Figure 2.91 Completed National Weather Workbook[\/caption]\n\n<\/div>\n

    Creating a Table<\/h2>\nIf you wish to follow along, download Data file: CH5 Data<\/a><\/em>\n\nWhen data is presented in long lists or columns, it helps if the table is set up well. Here are some rules of data-entry etiquette to follow when creating a table from scratch:\n
      \n \t
    1. Whenever you can, organize your information using adjacent (neighboring) columns and rows.<\/li>\n \t
    2. Start the table in the upper-left corner of the worksheet and work your way down the sheet.<\/li>\n \t
    3. Don\u2019t skip columns and rows just to \u201cspace out\u201d the information. (To place white space between information in adjacent columns and rows, you can widen columns, heighten rows, and change the alignment.)<\/li>\n \t
    4. Reserve a single column at the left edge of the table for the table\u2019s row headings or identifying information.<\/li>\n \t
    5. Reserve a single row at the top of the table for the table\u2019s column headings.<\/li>\n \t
    6. If your table requires a title, put the title in the row(s) above the column headings.<\/li>\n<\/ol>\nFollowing these rules will help insure that the sorts, filters, totals, and subtotals you apply to your table with give you the desired results.\n\nWith these rules in mind, we will begin working on the Portland ME <\/strong>worksheet in the National Weather workbook<\/strong>. Notice that the data is in adjacent columns and rows. The upper-left corner of the table is in A5 and the titles are above the column headings in Row 5. Since the set-up of our data looks good, we are ready to turn our data range into an Excel table:\n
        \n \t
      1. Open data file CH5 Data<\/em>\u00a0and save a file to your computer as CH5 National Weather<\/strong>.<\/li>\n \t
      2. Click on A5 in the Portland ME <\/strong>sheet.<\/li>\n \t
      3. Click the Table button in the Insert tab of the Ribbon.\nFigure 2.92<\/strong> will appear on your screen.<\/li>\n<\/ol>\n \n
        \n\n[caption id=\"\" align=\"alignnone\" width=\"244\"]\"Create<\/a> Figure 2.92 Create Table[\/caption]\n\n<\/div>\n
          \n \t
        1. Make sure \u201cMy table has headers\u201d is checked. Click OK.<\/li>\n \t
        2. Click in A5 again.<\/li>\n \t
        3. Adjust your columns widths so that you can see the complete headings in row 5 with the filter arrows showing. The filter arrows are the down-arrow buttons that will appear in row 5 when you create your table. We will learn how to use these to sort and filter later in this chapter.<\/li>\n<\/ol>\nAfter this, your spreadsheet will look like Figure 2.93<\/strong>.\n\n[caption id=\"\" align=\"alignnone\" width=\"819\"]\"Table<\/a> Figure 2.93 Weather Table[\/caption]\n\nNotice that a new ribbon tab, Table Tools Design, appears when you click inside your table. This ribbon tab allows you to edit, style, and add functionality to your table.\n\nLet's try these steps again in the following steps:\n
            \n \t
          1. Click on the Portland OR <\/strong>sheet and click in cell A5.<\/li>\n \t
          2. Click the Table button in the Insert tab of the Ribbon.<\/li>\n \t
          3. Make sure \u201cMy table has headers\u201d is checked. Click OK.<\/li>\n \t
          4. Click in A5 again.<\/li>\n \t
          5. Adjust your columns widths so that you can see the complete headings in row 5 with the filter arrows showing.<\/li>\n<\/ol>\n \n
            \n

            Skill Refresher<\/h3>\nCreate a Table<\/strong>\n
              \n \t
            1. Click on the top left cell in your data.<\/li>\n \t
            2. Click the Table button in the Insert tab of the Ribbon.<\/li>\n \t
            3. Make sure \u201cMy table has headers\u201d is checked. Click OK.<\/li>\n \t
            4. Click on the top left cell again.<\/li>\n \t
            5. Adjust your columns widths so that you can see the complete headings with the filter arrows showing.<\/li>\n<\/ol>\n<\/div>\n

              Formatting Tables<\/h2>\nThere are many ways to format an Excel table. There are preset colored Table Styles with Light, Medium, and Dark colors. There are also a variety of Table Style Options listed in Table 5.1<\/strong>.\n\nTable 2.91 Table Style Options\n<\/colgroup>\n\n\n\n\n\n\n\n\n\n
              Table Style<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
              Header Row<\/b><\/td>\nTop row of the table that includes column headings<\/td>\n<\/tr>\n
              Total Row<\/b><\/td>\nRow added to the bottom that applies column summary calculations<\/td>\n<\/tr>\n
              First Column<\/b><\/td>\nFormatting added to the left-most column in the table<\/td>\n<\/tr>\n
              Last Column<\/b><\/td>\nFormatting added to the right-most column in the table<\/td>\n<\/tr>\n
              Banded Rows<\/b><\/td>\nAlternating rows of color added to make it easier to see rows of data<\/td>\n<\/tr>\n
              Banded Columns<\/b><\/td>\nAlternating columns of color added to make it easier to see columns of\ndata<\/td>\n<\/tr>\n
              Filter Button<\/b><\/td>\nButton that appear at the top of each column that lists options for sorting and filtering<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nWe\u2019ll add some formatting to both of our Portland weather tables in the following steps:\n\n1. Click on the Portland ME <\/strong>sheet in your file.\n\n2. In the Table Tools Design tab, in the Table Styles group, click the More button.\u00a0\"\"\n\nA gallery of table styles will appear as in Figure 2.94<\/strong>.\n\n[caption id=\"\" align=\"alignnone\" width=\"750\"]\"Gallery<\/a> Figure 2.94 Table Styles[\/caption]\n\n3. In the Table Styles gallery, in the Medium Section, click Table Style Medium 7.\n\n4. In the Table Style Options group in the Ribbon, click Banded Rows.\n\nThe alternating colored rows will disappear. The data in the table is now more difficult to read.\n\n5. Try out some of the other options in the Table Style Options group. Once you\u2019re finished, check just Header Row, Banded Rows, and Filter Button as in Figure 2.95<\/strong> below.\n\n[caption id=\"attachment_760\" align=\"alignnone\" width=\"300\"]\"Table<\/a> Figure 2.95 Ribbon Table Style Options[\/caption]\n

              Adding Data to Tables<\/h2>\nOver time, you will need to add new data to an Excel table. You will add the data to the table in a blank row. The easiest way to do this is to enter the data in the first blank row below the last row in the table. You can then rearrange the data in the table by sorting it. If you need to add data in a specific place in the middle of a table, you can insert a blank row in the middle and add your data there.\n\nWe need to add the last three days of the months to both our Portland, Maine and Portland, Oregon tables. The following steps will walk you through doing this.\n
                \n \t
              1. Click on the Portland ME<\/strong> worksheet.<\/li>\n \t
              2. Click on A34 (the left-most cell below the last row in the table).<\/li>\n \t
              3. Enter the following data:<\/li>\n<\/ol>\nTable 2.92 Portland, Maine data\n<\/colgroup>\n\n\n\n\n\n
                Day<\/b><\/td>\nHigh (\u00b0F)<\/b><\/td>\nLow (\u00b0F)<\/b><\/td>\nRain<\/strong>\n (inches)<\/strong><\/td>\nSnow<\/strong>\n (inches)<\/strong><\/td>\n<\/tr>\n
                29<\/b><\/td>\n31.4<\/td>\n13.3<\/td>\n0.12<\/td>\n0.59<\/td>\n<\/tr>\n
                30<\/b><\/td>\n31.6<\/td>\n3.4<\/td>\n0.08<\/td>\n0.47<\/td>\n<\/tr>\n
                31<\/b><\/td>\n31.7<\/td>\n13.5<\/td>\n0.12<\/td>\n0.63<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\nNotice that the banded row formatting continues as additional rows are added to the tables.\n
                  \n \t
                1. Click on the Portland OR <\/strong>worksheet.<\/li>\n \t
                2. Click on A34 (the left-most cell below the last row in the table).<\/li>\n \t
                3. Enter the following data:<\/li>\n<\/ol>\nTable 2.93 Portland, Oregon data\n<\/colgroup>\n\n\n\n\n\n
                  Day<\/b><\/td>\nHigh (\u00b0F)<\/b><\/td>\nLow (\u00b0F)<\/b><\/td>\nRain<\/strong>\n (inches)<\/strong><\/td>\nSnow<\/strong>\n (inches)<\/strong><\/td>\n<\/tr>\n
                  29<\/b><\/td>\n48.8<\/td>\n36.2<\/td>\n0.16<\/td>\n0<\/td>\n<\/tr>\n
                  30<\/b><\/td>\n49.0<\/td>\n36.2<\/td>\n0.11<\/td>\n0.32<\/td>\n<\/tr>\n
                  31<\/b><\/td>\n49.1<\/td>\n36.1<\/td>\n0.16<\/td>\n0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

                  Finding and Editing Data<\/h2>\nIt is inevitable that you will find data errors in your table and need to correct them. While you can visually scan through a table to find your errors, this can be a tedious and tiresome process. Excel can help with this through the Find command. When you use Find, the best practice is to start at the top of the table to ensure that all your data is included in the search.\n\nWe know that a temperature of 3.4 degrees (brrr!) was entered erroneously in the Portland Maine sheet<\/strong>. It should have been 13.4. To fix this error, complete the following steps.\n
                    \n \t
                  1. Click on the Portland ME <\/strong>sheet.<\/li>\n \t
                  2. Press the CTRL+HOME keys together to go to the top of the sheet (A1).<\/li>\n \t
                  3. In the Home tab of the ribbon, click on Find & Select in the Editing Group and then click Find.<\/li>\n \t
                  4. In the Find box, type 3.4<\/strong>, and then click Find Next.\n\n[caption id=\"\" align=\"alignnone\" width=\"458\"]\"Find<\/a> Figure 5.6\u00a0Find and Replace[\/caption]<\/li>\n \t
                  5. Click the Close button.<\/li>\n \t
                  6. Replace 3.4 in the Low column for Day 10 with 13.4.<\/strong><\/li>\n \t
                  7. Now switch to the Portland Oregon <\/strong>sheet and find the Snow error of .32. Change it to 0.12<\/strong>. You should find the error in Day 3.<\/li>\n<\/ol>\n
                    \n

                    Skill Refresher<\/h3>\nFinding and Replacing Data<\/strong>\n
                      \n \t
                    1. In the Home tab of the ribbon, click on Find & Select in the Editing Group and then click Find.<\/li>\n \t
                    2. In the Find box, type what you want to find, and then click Find Next.<\/li>\n \t
                    3. Continuing click Find Next until you find.what you are looking for.<\/li>\n \t
                    4. Click Close and edit your data.<\/li>\n<\/ol>\n<\/div>\n \n

                      Freeze Rows and Columns<\/h2>\nWhen you freeze panes, Microsoft Excel keeps specific rows or columns visible in your table when you scroll through it on your screen. For example, if the first row in your spreadsheet contains labels, you might freeze that row to make sure that the column labels remain visible as you scroll down in your spreadsheet. When we scroll through our weather data, it would be nice to keep our column headings visible on the screen.\n\nTo freeze your headings:\n
                        \n \t
                      1. Click in A6, the left-most cell below the headings row.<\/li>\n \t
                      2. Click the View tab in the ribbon.<\/li>\n \t
                      3. Select Freeze Panes and then Freeze Panes again.<\/li>\n \t
                      4. Scroll up and down the sheet and notice that the headings are always displayed at the top of the table.<\/li>\n<\/ol>\n[caption id=\"attachment_761\" align=\"alignnone\" width=\"325\"]\"Freeze<\/a> Figure 2.97 Freeze Pane[\/caption]\n\n
                        <\/div>\n
                        To unfreeze your headings:<\/div>\n
                          \n \t
                        1. Click on the View tab in the ribbon.<\/li>\n \t
                        2. Select Unfreeze Panes.<\/li>\n<\/ol>\n

                          Simple Sort<\/h2>\nContent in a table can be sorted alphabetically, numerically, and in many other ways. Sorting helps organize data by one or more columns in your table. Table 5.4<\/strong> describes the different sort orders available for each column of data.\n\nTable 2.94 Sort Options\n<\/colgroup>\n\n\n\n\n
                          Sort Order<\/b><\/td>\nText<\/td>\nNumbers<\/b><\/td>\nDates<\/b><\/td>\n<\/tr>\n
                          Ascending<\/b><\/td>\nAlphabetical (A-Z)<\/td>\nSmallest to Largest\n\n \n\nLowest to Highest<\/td>\nChronological (oldest to newest)<\/td>\n<\/tr>\n
                          Descending<\/b><\/td>\nReverse Alphabetical (Z-A)<\/td>\nLargest to Smallest\n\n \n\nHighest to Lowest<\/td>\nReverse Chronological (newest to\noldest)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nLet\u2019s say we want to know what the snowiest day was in January in Portland, Maine; so we want to sort the Snow column in Descending order so that the snowiest day ends up at the top of the table.\n
                            \n \t
                          1. Click on the filter Click arrow to the right of the header Snow (inches) in the Portland ME <\/strong>worksheet.<\/li>\n \t
                          2. Click on the choice Click ZA\u2193 Sort Largest to Smallest. See Figure 2.98<\/strong> below.<\/li>\n<\/ol>\n[caption id=\"attachment_762\" align=\"alignnone\" width=\"271\"]\"Filter<\/a> Figure 2.98 Sort by One Column[\/caption]\n\n
                            <\/div>\n
                            If you did this correctly, you\u2019ll see that the snowiest day at the top of the list in January 3rd (in row 6) with 0.73 inches of snow! Notice the filter arrow changes in the snow column to a downward pointing arrow to indicate you sorted that column in descending order (largest to smallest).<\/div>\n\n[caption id=\"attachment_763\" align=\"alignnone\" width=\"300\"]\"Data<\/a> Figure 2.99 Snowiest Days in Maine[\/caption]\n\n3. Now switch to the Portland Oregon<\/strong> sheet and repeat these sort steps to find the snowiest day in Oregon. Check your answers with Figure 2.910<\/strong>.\n\n\"Data<\/a>\n\nFigure 2.910 Snowiest Days in Oregon\n
                            \n
                            \n

                            Skill Refresher<\/h3>\nSort a Column<\/strong>\n
                              \n \t
                            1. Click on the filter Click arrow to the right of the header in the column you want to sort.<\/li>\n \t
                            2. Click on the choice AZ! or ZA\u2193 to sort your data by that column.<\/li>\n<\/ol>\n<\/div>\n \n\n<\/div>\n
                              <\/div>\n
                              \n

                              Key Takeaways<\/h3>\n