AGB 260: Agribusiness Information Technology

1. AGB 260: Agribusiness Information Technology Tables

2. Useful Chapters in the Textbook Regarding this Lecture • Chapter 26

3. Tables • A table in Excel is a rectangular array of information/attributes where the first row is normally used for headers for each column of information. • Each row in a table usually represents a single object that has different types of information related to it. • Excel has the ability to format rectangular array data effortlessly.

4. Creating Tables • There are two basic ways to create a table in Excel: • The first way is to highlight the array of cells you would like to make a table out of and then press CTRL+T • Another way is to highlight the cells of interest and then go to the Insert tab and select the table button.

5. Header Row for Tables • When you create a table out of an array of data, Excel puts drop down arrows in the header row that allow you to: • Sort the data (either ascending or descending) • Filter the data, i.e., choose particular rows of the data based on criteria • Select certain data

6. Design Tab • Whenever you have a table and you have selected an item in the table, you should notice that the Design tab appears. • This tab gives you the ability to: • Give the table a name, • Resize the table, • Summarize with a pivot table, • Remove duplicate values, • Convert to a normal range, and • Insert Slicers.

7. Table Styles and Options • When you create a table out of an array of data, Excel has many preprogrammed formats located under Table Styles and Table Style Options. • Under Table Style Options, you have the ability to add and delete: • Header Row, • Total Row, • Banded Rows, • Banded Columns, and • Filter Button. • You can also bold the First Column and Last Column.

8. Slicers • A slicer is a quick way to have a tool that allows you to quickly examine particular aspects of your data. • It is basically another filtering device. • You can right click on the slicer to bring options that you can do or use the options tab.

9. Referencing Columns in Formulas • Once a table has been defined, you can reference that table anywhere in your spreadsheet to use in a function. • For example, if you wanted to average a particular column of information where the column name is 2011 Value and the table name is Commodity, your formula would be =Average(Commodity[2011 Value]).

10. Adding a Calculated Column • Excel has the ability once a table is established to create a new calculated column off of other columns in the table. • You only need to enter the formula for the first cell and Excel will create a new column and copy the formula down.

11. Referring to Particular information in a Table • #All refers you to all the cells in the table including the total row if it exists. • #Data refers to all the cells in the table except for the header and row columns. • #Headers refers only to the header row. • #Totals refers to the total row. • #This Row refers to cells in the current row.