Chapter 22 Flashcards
Filters in Excel
In a large dataset, we frequently do not need to consider all the fields that are listed. We are usually interested only in selected fields. Then again, we are interested in the summary of the data rather than needing to consider each observation in the dataset. To help us select a subset of the data, excel gives us a choice of several tools. Here we use filters.
How to invoke the Filter
Filtering is two step process. First you need to invoke the filter; and then choose the criteria by which you wish to filter.
How to invoke the filter: You can invoke the filter using the mouse or the keyboard.
Keyboard: ALT-H-S-F, ALT-D-F-F
Mouse: Home ribbon -> Editing group -> Sort and filter -> Filter.
Invoking a filter will create a small drop down arrow to the right of the column header. Clicking on the drop down arrow brings up the filter dialog box.
SUBTOTAL function
The SUBTOTAL function takes two arguments. The first argument specifies whether we want the sum, average, count, or some other value about the second argument, the range that is of interest.
The SUBTOTAL includes only the visible rows from the range specified.
Pivot Table
A Pivot Table is a powerful tool that enables us not only to extract data (as the filters do), but also perform calculations and help us organize and present our data.
‘TABLES’ in Excel
The word ‘table’ has a special meaning in Excel. By designating a dataset as a ‘table’, the dataset acquires some special properties. A simple range of data does not have these properties. First we convert a range of data from a mere range to a table.
Checks before converting a range into a table
Before converting a dataset into a table, we need to do some preliminary checks.
1) Are there any blank rows or blank columns? If our dataset has one or more blank rows or place our cursor prior to creating a table, Excel will automatically select a contiguous range of data around the active cell.
2) If, for some reason, we want to have blank rows/columns we must manually select the range of interest to us to include the blank rows/columns.
3) Do our columns have headers? We can choose existing column headers to create the labels for columns. If not, Excel will name the columns COLUMN 1, COLUMN 2, etc.
4) If our column headers are not unique, Excel will make each column unique by assigning a number to differentiate that column from another column.
5) If your dataset already has headers, you can choose the existing headers by checking the box “my table has headers’. If you do not check this box, Excel will create generic labels in addition to the existing headers.
How to convert a dataset to a table
There are two easy ways to convert a dataset into a table. Your cursor should be on any cell in the dataset.
1) If you are using Windows, press CTRL-t. (This is the control key followed by the letter t.)
2) Select the command to insert a table. (This command is available on the ‘INSERT’ ribbon, in the group of buttons called Tables).
Naming a table
You can name a Table just as you would give a name to a range of data. As we discussed earlier, a named range is an absolute reference, not only within a given worksheet, but also in the entire workbook. In
the same way, a Table is a named reference. Excel gives Tables the default names of Table1, Table2 etc. If you want to, you can rename a Table by selecting a Table and replacing the generic Table1 or Table2 (in
the Name Box) with a name of your choice. As with range names, Table names cannot contain spaces, cannot contain some special characters, and cannot begin with a number.
Advantages of using a TABLE
1) Navigating to a table: Naming a Table makes it easy to navigate to that Table. In Windows, you can press CTRL-g (the ‘go to’ short cut) and you will see all the Tables in your workbook listed in the dialog box. By selecting the appropriate Table, you can navigate to that Table even if that Table is in a different worksheet from the active worksheet.
Advantages of using a TABLE
2) Displaying data: Designating a dataset as a Table makes it easier to read the data. Excel, by default, formats the rows in a Table with alternative shades of a
color. (This design can be changed by choosing one of several options that appear in the DESIGN ribbon. Note that this ribbon is visible – under Table Tools – only when a Table is chosen).
Another feature that facilitates reviewing the data in a Table is that column headers are automatically frozen. Let us say that you have a large dataset. This dataset has (for example) 500 rows of data, and one
row, the top row, with column headers. In an ordinary dataset, as you scroll down the dataset, the column headers will scroll out of sight. To avoid this loss of visibility, you can freeze the top row. In contrast, if you designate a dataset as a Table, the top row is automatically frozen. When you scroll down the Table, Excel replaces the default column labels (such
as A, B, C etc.,) with the column headers for your Table.
Advantages of using a TABLE
3) Reorganizing data in a table: A Table makes it easier to select or reorganize data. You can select a
row, a column, or even the entire Table and move it to a different location. Let us say you want to move a column. A short cut to select a column is CTRL-SPACE. (This is the control key followed by the space
bar.) A similar short cut for selecting a row is SHIFT-SPACE. (This is the shift key followed by the space bar.) Once a column has been selected, move your cursor slowly near the bottom edge of the column header until it changes to a four-headed arrow. You can then drag and drop the column in a new location.
Advantages of using a TABLE
4) Dimensions of a Table adjust automatically: We can assign a name to a range of data. However, the named range is usually not dynamic. By ‘dynamic’, we mean that when we add new data to the existing range of data, we have to re-define the range name to include the new data.
In contrast, if we have to add a new row of data to a Table, the Table adjusts (and formats) automatically. The same is true when we add a new column of data to the Table. The only requirement is that the new row or new column must be adjacent to the existing row or column.
Advantages of using a TABLE
5) Using formulas with Data in a Table: A Table makes it easier to use formulas. Let us say we have a Table named as SalesData. Just as with range names, we can use formulas with a Table that are easy to read. Consider the examples below.
=Rows(SalesData) gives the number of rows of data in the Table (not including the header row)
=Columns(SalesData) gives the number of columns of
data in the Table
=Counta(SalesData) gives the count of values (both text and numbers) in the Table
=Count(SalesData) gives the count of numeric values
(excluding text) in the Table
=Sum(SalesData) gives the sum of numeric values in the Table
=Max(SalesData) gives the largest value in the Table
=Min(SalesData) gives the smallest value in the Table
Advantages of using a TABLE
6) Replicating formulas with data in a Table: When we enter a calculation, or a formula in a Table, this calculation or formula is automatically replicated for all appropriate rows or columns.
Advantages of using a Table
7) Additional features in a Table: Using a Table also presents us with several choices on how to display
the Table and how to summarize the data. These features are available in the Table Tools menu that appears when the cursor is on any cell in a Table. Formatting presents design choices such as different color schemes, ability to shade alternating rows and/or columns, ability to highlight the first row and/or first column etc. The options with regard to
the data in a Table include the ability to summarize the data, remove duplicate values in the data, analyze using a pivot table, filter using a slicer, etc.
