7- Managing lists in Excel

(Open Exercise file) Back To Excel Index Page Back To Edmond Home Page

71- What happened to Excel Lists? Introducing Excel Tables Play Video

To make managing and analyzing a group of related data easier, you can turn a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells into a Microsoft Office Excel table (previously known as an Excel list). A table is a series of rows and columns that contains related data that is managed independently from the data in other rows and columns on the worksheet (worksheet: The primary document that you use in Excel to store and work with data. Also called a spreadsheet. A worksheet consists of cells that are organized into columns and rows; a worksheet is always stored in a workbook.).

Ø By default, every column in the table has filtering enabled in the header row so that you can filter or sort your table data quickly. You can add a total row (total row: A special row in a list that provides a selection of aggregate functions useful for working with numerical data.) to your table that provides a drop-down list of aggregate functions for each total row cell. A sizing handle in the lower-right corner of the table allows you to drag the table to the size that you want.

Ø To manage several groups of data, you can insert more than one table in the same worksheet. You cannot create a table in a shared workbook (shared workbook: A workbook set up to allow multiple users on a network to view and make changes at the same time. Each user who saves the workbook sees the changes made by other users.).

Create a Table:

1) Select the range of cells for the table. 
           Click the Insert tab, and then click the Table button.

2) Click the Home tab, click the Format as Table button, 
           and then select a table style.

3) Select the My table has headers check box.

72- Converting Text to Columns Play Video
Sometimes you will want to split data in one cell into two or more cells.  You can do this easily by utilizing the Convert Text to Columns Wizard.

§ Highlight the column in which you wish to split the data

§ Click the Text to Columns button on the Data tab

§ Click Delimited if you have a comma or tab separating the data, or click fixed widths to set the data separation at a specific size. 

Modify Fonts:
Modifying fonts in Excel will allow you to emphasize titles and headings. To modify a font:

§ Select the cell or cells that you would like the font applied

§ On the Font group on the Home tab, choose the font type, size, bold, italics, underline, or color

Format Cells Dialog Box:
In Excel, you can also apply specific formatting to a cell. To apply formatting to a cell or group of cells:

§ Select the cell or cells that will have the formatting

§ Click the Dialog Box arrow on the Alignment group of the Home tab

There are several tabs on this dialog box that allow you to modify properties of the cell or cells. 

Number:  Allows for the display of different number types and decimal places
Alignment:  Allows for the horizontal and vertical alignment of text, wrap text, shrink text, merge cells and the direction of the text.
Font:  Allows for control of font, font style, size, color, and additional features
Border:  Border styles and colors
Fill:  Cell fill colors and styles

Add Borders and Colors to Cells:
Borders and colors can be added to cells manually or through the use of styles.  To add borders manually:

§ Click the Borders drop down menu on the Font group of the Home tab

§ Choose the appropriate border

To apply colors manually:

§ Click the Fill drop down menu on the Font group of the Home tab

§ Choose the appropriate color

To apply borders and colors using styles:

§ Click Cell Styles on the Home tab

§ Choose a style or click New Cell Style

Change Column Width and Row Height:
To change the width of a column or the height of a row:

§ Click the Format button on the Cells group of the Home tab

§ Manually adjust the height and width by clicking Row Height or Column Width

§ To use AutoFit click AutoFit Row Height or AutoFit Column Width

Hide or Unhide Rows or Columns:
To hide or unhide rows or columns:

§ Select the row or column you wish to hide or unhide

§ Click the Format button on the Cells group of the Home tab

§ Click Hide & Unhide

Merge Cells:
To merge cells select the cells you want to merge and click the Merge & Center button on the Alignment group of the Home tab.  The four choices for merging cells are:

Merge & Center: Combines the cells and centers the contents in the new, larger cell
Merge Across: Combines the cells across columns without centering data
Merge Cells: Combines the cells in a range without centering
Unmerge Cells: Splits the cell that has been merged

Align Cell Contents:
To align cell contents, click the cell or cells you want to align and click on the options within the Alignment group on the Home tab.  There are several options for alignment of cell contents:

Top Align:  Aligns text to the top of the cell
Middle Align:  Aligns text between the top and bottom of the cell
Bottom Align:  Aligns text to the bottom of the cell
Align Text Left:  Aligns text to the left of the cell
Center:  Centers the text from left to right in the cell
Align Text Right:  Aligns text to the right of the cell
Decrease Indent:  Decreases the indent between the left border and the text
Increase Indent:  Increase the indent between the left border and the text
Orientation:  Rotate the text diagonally or vertically

73- Sorting and Grouping Play Video

Sorting Data in a Table:

1) Click the table cell with the field name. 
           Click the Data tab. 
           Click the Sort Ascending or the Sort Descending button.

2) The list arrow displays an icon indicating the field is sorted.

3) To clear all filters in a worksheet and redisplay all rows, click the Clear button.

4) To reapply a filter, click the Reapply button.

5) Sort a Table Using Multiple Fields and Attributes

6) Click anywhere within the table range

7) Click anywhere within the table range

8) Click the Data tab. 

9) Click the Sort button

10) Click the Column list arrow and then select a sort field.

11) Click the Order and select a sort field: A to Z, Z to A, or Custom List.

12) To add another level of sorting, click Add Level.

13) To change the sort order, select a sort, and then click Move Up or Move Down.

14) To delete or copy a sort level, select a sort, and click Delete Level or Copy Level.

15) Click OK.

Sort across a row:

1) Click the table cell. 

2) Click the Data tab. 

3) Click the Sort button.

4) Click Options.

5) Click the Sort top to bottom option, and then click OK.

6) Sort data with the case sensitive option

7) Click the table cell. Click the Data tab. Click the Sort button. Click Options.

8) Click Options.

9) Select the Case sensitive. Then click OK.

Sort a Table Using Multiple Fields and Attributes:

1) Click anywhere within the table range. 

2) Click the Data tab. 

3) Click the Sort button.

4) Click the Column list arrow and then select a sort field.

5) Click the Sort on and select: Values, Cell Color, Font Color, or Cell Icon.

6) Click the Order and select a sort field: A to Z, Z to A, or Custom List.

7) To add another level of sorting, click Add Level.

8) To change the sort order, select a sort, and then click Move Up or Move Down.

9) To delete or copy a sort level, select a sort, and click Delete Level or Copy Level.

10) Click OK.

74- Creating a Summary Report Play Video

Total the Data in a Table:

1) Click a cell in a table. 

2) Click the Design tab under Table Tools. 

3) Select the Total Row check box. 

4)  The total row appears as the last row.

5) Click the cell in the column for which you want to calculate a total, 
           and then click the drop-down list arrow.

6) From the drop-down list, select the function.

Subtotal Data in a List:

1) Select the data. 

2) Click the Data tab. Use sort buttons to sort the column.

3) Click the Subtotal button.

4) Click the column to subtotal.
Click the summary function for the subtotals.
Select the check box for each column you want to subtotal.
To set automatic page breaks following each subtotal, select the Page break between groups.
To show or hide a summary row above the detail row, select or clear the Summary below data To remove subtotals, click Remove All.

5) Click OK.

Back To Top Back To Excel Index Page (Open Exercise file)

Back To Edmond Home Page

Copyright © Edmond Hakmeh 2009. All Rights Reserved