Posts

Working with Excel Table

/0 Comments/in /by

An important but often underutilized feature in Excel is tables. This tip describes when to use a table and also lists the advantages and disadvantages.

Understanding what a Table is:

A table is a rectangular range of structured data. Each row in the table corresponds to a single entity. For example, a row can contain information about a customer, a bank transaction, an employee, or a product. Each column contains a specific piece of information. For example, if each row contains information about an employee, the columns can contain data, such as name, employee number, hire date, salary, or department. Tables have a header row at the top that describes the information contained in each column.

You’ve probably created ranges that meet this description. The magic happens when you tell Excel to convert a range of data into an “official” table. You do so by selecting any cell within the range and then choosing Insert ➜ Tables ➜ Table.

When you explicitly identify a range as a table, Excel can respond more intelligently to the actions you perform with that range. For example, if you create a chart from a table, the chart expands automatically as you add new rows to the table. If you create a pivot table from a table, refreshing the pivot table will include any new data that you added to the table.

Below figures shows a range before it was converted to a table and the range after it was converted to a table.

range-of-data-that-is-not-an-official-table

A Range of data that’s not an official Table

range-of-data-that-has-been-designated-a-table

A range of data that has been converted to a Table

Difference between a standard Range and a Range that has been converted to a Table:

  • Activating any cell in the table gives you access to a new Table Tools context tab on the Ribbon.
  • You can quickly apply background color and text color formatting by choosing from a gallery. This type of formatting is optional.
  • Each column header contains a filter button that, when clicked, lets you easily sort the rows or filter the data by hiding rows that don’t meet your criteria.
  • A table can have “slicers”, which makes it easy for novices to quickly apply filters to a table.
  • If you scroll down the sheet so that the header row disappears, the table headers replace the column letters in the worksheet header. In other words, you don’t need to freeze the top row to keep the column labels visible.
  • If you create a chart from data in a table, the chart automatically expands if you add new rows to the end of the table.
  • If you create a name for a column in a table, the “refers to” range for the name adjusts as you add new rows to the table.
  • Tables support calculated columns. A single formula entered in a cell is automatically propagated to all cells in the column.
  • Tables support structured references in formulas outside of the table. Rather than use cell references, formulas can use table names and column headers.
  • When you move your mouse pointer to the lower-right corner of the lower-right cell, you can click and drag to extend the table’s size, either horizontally (add more columns) or vertically (add more rows).
  • Selecting rows and columns within the table is simplified.

Limitations of using a Table:

  • If a workbook contains at least one table, a few Excel features are not available:
  • For some reason, when a workbook contains at least one table, Excel doesn’t allow you to use the Custom Views feature (choose View ➜ Workbook Views ➜ Custom Views).
  • You cannot share a workbook (using Review ➜ Changes ➜ Share) if the workbook contains a table.
  • You can’t insert automatic subtotals within a table (by choosing Data➜Outline➜Subtotal).
  • You cannot use array formulas within a Table.

Shading Alternate Rows in Excel

/0 Comments/in /by

When you create a table (using Insert➜Tables➜Table), you have the option of formatting the table in such a way that alternate rows are shaded. Shading Alternate Rows can make your spreadsheets easier to read.

This tip describes how to use conditional formatting to obtain alternate row shading for any range of data. It’s a dynamic technique: If you add or delete rows within the conditional formatting area, the shading is updated automatically. Shading-Alternate-Rows

Here’s how to apply shading to alternate rows:

  1. Select the range to format.
  2. Choose Home➜Conditional Formatting➜New Rule. The New Formatting Rule dialog box appears.
  3. For the rule type, choose Use a Formula to Determine Which Cells to Format.
  4. Enter the formula in the box labelled Format Values Where This Formulas Is True: =MOD(ROW(),2)=0
  5. Click the Format button. The Format Cells dialog box appears.
  6. In the Format Cells dialog box, click the Fill tab and select a background fill color.
  7. Click OK to close the Format Cells dialog box, and click OK again to close the New Formatting Rule dialog box.

This conditional formatting formula uses the ROW function (which returns the row number) and the MOD function (which returns the remainder of its first argument divided by its second argument). For cells in even-numbered rows, the MOD function returns 0, and cells in that row are formatted.

For alternate shading of columns, use the COLUMN function instead of the ROW function.