Tips for Setting Up a Table in Excel

There are a few things to consider before creating your Table:

• Columns are the foundation of an Excel Table. Table rows are easy to add and remove, but adding or removing Table columns disrupts a Table's basic structure. To save time and frustration, determine exactly what columns are necessary before creating your Table.
• Do not leave blank rows in the middle of your Table. Blank rows will interfere with Table analysis functions.
• Enter numeric data either as numbers or as text; do not combine the two.
  HINTS:
  When using analysis functions, numbers are counted before text.
  When using mathematical formulas (e.g., SUM and AVERAGE), numbers cannot be entered as text.
  For more information on entering numbers and text, refer to Getting Started with Excel.

read more "Tips for Setting Up a Table in Excel"

Table Terms in Excel

Excel Tables are made up of columns, column labels, and rows; they can be sorted and/or filtered according to your specifications.

Columns
determine the informational structure of table rows
NOTE: These are the same as database fields.

Column Labels
identify columns; they often have special formatting
NOTE: These are the same as database field names.

Rows
contain specific data, according to column labels
NOTE: These are the same as database records.

AutoFilter
button at top of each Table column; provides quick access to sort and filter tools

Sort
arranges Table data in order according to text, numbers, time, date, or specific criteria (also see Using the Sort Command).
NOTE: Unlike filter, sort displays all table data, but puts them in a specific order.

Filter
displays only data meeting criteria you specify (e.g., name, year) (also see Filtering Your Database).
NOTE: Unlike sorting, filtering hides some table data, showing only that which fits your criteria. 

read more "Table Terms in Excel"

Sorting Data in Excel (Sort Button and Sort Dialog Box)

Sorting Data: Sort Button

If you simply want to sort your data by one column from smallest to largest or largest to smallest, you can do so with one click.

1. Select a cell in the column used to sort
2. From the Data command tab, in the Sort & Filter group, click SORT SMALLEST TO LARGESTor SORT LARGEST TO SMALLEST

The selected column is sorted.

Sorting Data: Sort Dialog Box

Using the Sort dialog box, you can create multi-level sorts that meet a variety of specifications.

1.      Select a cell in the column you want to use to sort

2.      From the Data command tab, in the Sort & Filter group, click SORT
The Sort dialog box appears.

3. To sort by ascending or descending values (i.e., alphabetically, numerically, time, or date)
1. In the Sort by pull-down list, select the column you want to use to sort
2. In the Sort On pull-down list, select Values

c.       In the Order pull-down list, select A to Z or Z to A
The data is sorted according to the selected order.

4. To sort according to a custom list
1. In the Sort by pull-down list, select the column you want to use to sort
   EXAMPLE: select a column containing days of the week
2. In the Sort On pull-down list, select Values
3. In the Order pull-down list, select Custom List...
   The Custom Lists dialog box opens

d.      Select the custom list by which you want to sort
EXAMPLE: select Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

5. To sort according a formatting criterion
1. In the Sort by pull-down list, select the column you want to use to sort
2. In the Sort On pull-down list, select the appropriate option
   EXAMPLE: Select Cell Color

c.       In the Order pull-down list, select a color and appropriate sort order
EXAMPLE: select Blue and On Top

6. OPTIONAL: To sort by more than one column
1. Click ADD LEVEL

b.      In the Then by row of the Sort dialog box, select the additional sort options

7. OPTIONAL: To control how to sort lists of days or months, case-sensitive data, and orientation
1. Click OPTIONS...
   The Sort Options dialog box appears.

2. Make necessary changes

c.       Click OK

8.      Click OK
The data is sorted.

read more "Sorting Data in Excel (Sort Button and Sort Dialog Box)"

Separating Text in Excel (Separating the Data)

1.      Select the cells containing the data you want to separate
NOTE: Excel can only separate one column at a time.

2.      Windows only: Select the Data command tab

3.      Windows: In the Data Tools group, click TEXT TO COLUMNS

Macintosh: From the Data menu, select Text to Columns...
The Convert Text to Columns Wizard dialog box appears.
NOTE: Your data is displayed in the Preview of selected data section.

4.      Under Original data type, select Delimited

5.      Click NEXT

6.      Under Delimiters, select or deselect the appropriate delimiters
The Data preview box shows you how your data will be separated.
NOTE: If you would like to keep some data containing your delimiter from being separated, the Text qualifier pull-down menu allows you to select the symbol you wish to use to indicate that the data included within is to be treated as text.

7.      OPTIONAL: As appropriate, select or deselect Treat consecutive delimiters as one
HINT: For more information, refer to Considerations.

8.      Click NEXT

9. OPTIONAL: To change the location where the separated data will appear on the worksheet, in the Destination text box, type the data destination in the format of: [$(column letter)$(row number)]
   OR
1. In the Destination text box, click COLLAPSE DIALOG
2. Select the cell(s) where you want the separated data to appear

c.       To return to the Convert Text to Columns Wizard, click RESTORE DIALOG

10.  Click FINISH
If there is any existing data in the cells where the separated data will be placed, a dialog box appears asking if you want to replace the content of the destination cells.

11. To replace the data, click OK
    To cancel the data separation and leave the cells as they were, click CANCEL

read more "Separating Text in Excel (Separating the Data)"

Separating Text in Excel (Considerations)

Here are some things to consider before separating your cells:

·         To ensure consistent data separation, standardize all cell data and corresponding delimiters. Using Find and Replace can make standardization quicker and easier.

·         Do not use delimiters that also function as text characters.
EXAMPLE: In Eau Claire WI, the space between Eau Claire and WI could work as a delimiter. But there is also a space operating as a text character between Eau and Claire, which you would not want to separate. One solution could involve separating cities and states with commas and using the comma as your delimiter.

·         Determine if you should separate your data into three columns (e.g., Jane M. Doe or John Doe Jr.).

·         Look for consecutive delimiters (e.g., the comma and space in Canton, OH). If your cell data has consecutive delimiters, you should select the Treat consecutive delimiters as one option.
NOTE: If you use a space as one of your delimiters, Excel automatically selects Treat Consecutive Delimiters As One. If you do not wish to use this option, deselect it.

WARNING: Separating data overwrites both the source cells and the cells directly to their right. To avoid deleting adjacent cell data, you can insert one more blank columns to the right of the column you are separating. For more information on adding columns, refer to Working with Rows and Columns

read more "Separating Text in Excel (Considerations)"

Hiding Columns, Rows, and Cells in Excel (Hiding Cell Contents)

You have the ability to hide the contents of individual cells if you do not need to view their contents or you simply do not want to print certain cells.

1.      Select the cell(s) to be hidden

2.      From the Home command tab, in the Cells group, click FORMAT » select Format Cells...
The Format Cells dialog box appears.

3.      Select the Number tab

4.      Under Category, select Custom

5.      In the Type text box, type three semicolons ( ;;; ) 

6.      Click OK
The cells are now hidden.

To redisplay cell information:

1.      Select the cell(s) to be redisplayed

2.      From the Home command tab, in the Cells group, click FORMAT » select Format Cells...
The Format Cells dialog box appears.

3.      Select the Number tab

4.      Under Category, make the appropriate selection
The dialog box refreshes to display options corresponding to the selected category.

5.      Select the desired options

6.      Click OK
The cell(s) reappear.

read more "Hiding Columns, Rows, and Cells in Excel (Hiding Cell Contents)"

Hiding Columns, Rows, and Cells in Excel (Redisplaying Rows)

1.      Select at least one cell from both of the rows around the hidden row(s) to be redisplayed
EXAMPLE: If row 5 is hidden, select a cell from rows 4 and 6.
HINT: If you cannot select the appropriate cells, you can use the Go To command.

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Unhide Rows
The row appears.

Redisplaying Rows: Quick Menu Option

This option works well for redisplaying row 1, since there are not rows on both sides of row 1.

1.      Hold your cursor between row IDs where the hidden row is located
The cursor will change to an open, double sided arrow as shown here.

2.      Right click » select Unhide

read more "Hiding Columns, Rows, and Cells in Excel (Redisplaying Rows)"

Hiding Columns, Rows, and Cells in Excel (Hiding Rows)

You can hide rows containing information that you do not need to view or do not want to print.

1.      Select a cell within the row(s) to be hidden

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Hide Rows
The row is hidden.

Hiding Rows: Quick Menu Option

1.      Right click the row ID » select Hide
The row is hidden.

read more "Hiding Columns, Rows, and Cells in Excel (Hiding Rows)"

Hiding Columns, Rows, and Cells in Excel (Redisplaying Columns)

1.      Select at least one cell from both of the columns around the hidden column(s) to be redisplayed
EXAMPLE: If column B is hidden, select a cell from both columns A and C.
HINT: If you cannot select the appropriate cells, you can use the Go To command.

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Unhide Columns
The column reappears.

Redisplaying Columns: Quick Menu Option

This option works well for redisplaying column A, since there are not columns on both sides of column A.

1.      Hold your cursor between column IDs on the right side of the hidden column
The cursor will change to an open, double sided arrow as shown here.

2.      Right click » select Unhide

read more "Hiding Columns, Rows, and Cells in Excel (Redisplaying Columns)"

Hiding Columns, Rows, and Cells in Excel (Hiding Columns)

You can hide columns of your worksheet containing information that you do not need to view or do not want to print.

1.      Select a cell within the column(s) to be hidden

2.      On the Home command tab, in the Cells group, click FORMAT

3.      From the Format menu, in the Visibility section, select Hide & Unhide » Hide Columns
The column is hidden.

Hiding Columns: Quick Menu Option

1.      Right click the column ID » select Hide

read more "Hiding Columns, Rows, and Cells in Excel (Hiding Columns)"

Find and Replace Features in Excel (Using the Replace Feature)

The Replace feature is useful when you want to change the same piece of information throughout your worksheet.
WARNING: Use the Replace All option with caution. If you do not first select specific cells to change, this option will replace the specified information throughout the entire document. This can change the meaning of your document if text is replaced where it should not be.

1.      OPTIONAL: If you want to find and/or replace information in a specific section of your document, select the desired portion of the document
HINTS:
To select contiguous cells, click the desired cells
To select non-contiguous cells, hold down [Ctrl] + click the desired cell(s)

2.      In the Editing group, click FIND & SELECT» select Replace...
OR
Press [Ctrl] + [H]
The Find and Replace dialog box appears.

3.      In the Find what text box, type the text or data to be found

4.      In the Replace with text box, type the text or data to replace the information found

5.      OPTIONAL: Adjust the searching options

6.      Click FIND NEXT
The first occurrence is highlighted.

7.      To replace only that occurrence, click REPLACE
To replace all occurrences, click REPLACE ALL
The old information is replaced with the new.
WARNING: Use the Replace All option with caution. If you do not first select specific cells to change, this option will replace the specified information throughout the entire document. This can change the meaning of your document if text is replaced where it should not be.

8.      To find and replace additional instances of your text or data, repeat steps 6–7

9.      To close the dialog box, click CLOSE

read more "Find and Replace Features in Excel (Using the Replace Feature)"