PM2 - Management Reporting – IT Office Tools (MS Excel)

Chapter - 9
Analyzing Data:
Create and Modify Tables:
By turning an Excel range into a table, the table data can be worked with independently
from the rest of the worksheet, and filter button arrows appear automatically on the
column headers, allowing columns to be filtered and sorted even faster. Add total rows
and quickly apply table formatting.

Tables, like normal data ranges of data, consist of two parts:

• Records (rows): Each record contains information about a unique thing or person, just
like a listing in a phone book.

• Fields (columns): Records are broken up into fields which store specific pieces of
information, such as first and last name.

Tips:

 Before turning a range of data into a table, remove blank rows and columns, and make
sure that a single column doesn’t have different types of data within it.

If desired, make sure there are column headers entered. For example, to make a table
that lists a company’s sales reps, enter headers such as Last Name, First Name, Territory,
etc. Unique records, such as the names and territories of each of the sales reps, should be
entered as rows.

Create a table from a cell range:

If an organized range of data already exists, it can be turned into a table.

1. Select a cell range to make into a table.

Normally the cell range needs to include a header row, with labels identifying each of the
columns.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

Other Ways to Create a Table: It’s possible to simultaneously create and format a
table. Select the cells to include in the table and click Format as Table in the Styles

group on the Home tab; select a table style and click OK.

2. Click the Insert tab on the Ribbon and click the Table button in the Tables group.

The Create a Table dialog box appears. Edit the range that will become a table, and specify
whether or not the table has a header row (if it doesn’t, Excel adds a header row above the
table data).
3. Set the options in the Create a Table dialog box and click OK.

The table is created. Filters are added to each column, and the table is automatically
formatted. Under Table Tools on the Ribbon, the Design contextual tab appears.

Create a blank table:

If data to include in a table hasn’t already been entered, create the table first.

1. Select a range of cells that is approximately the size the table needs to be.

It’s also possible to change the size later.

2. Click the Insert tab on the Ribbon and click the Table button in the Tables group.

The Create Table dialog box appears.

3. Click OK.

The table appears, including placeholder column headers that can be edited, and a resize
handle that appears in the lower-right corner of the table.

Tips for Organizing Tables:

Avoid putting blank rows and columns in So that Microsoft Excel can more easily
the table. detect and select the table.
Create column labels in the first row of the Excel uses the labels to create reports and to
table. find and organize data.
Design the table so that all rows have This makes the table more meaningful and
similar items in the same column. organized.
Try to break up information as much as This gives more power to sort, filter and
 PM2 - Management Reporting – IT Office Tools (MS Excel)

possible. manipulate the table.

Each column should contain the same type This will make the table easier to read and
of information. understand.
Don’t use duplicate field names. Duplicate field names can cause problems
when entering and sorting information.

Adding and Removing Data:

It’s easy to add or remove table data.

Add table rows and columns:

1. Select a cell in the table row or the table column next to which to add the row or column.

Add a new table row above the row selected, or add a new column to the left of the
column selected (unless the last column is selected, in which case a column can also be
added to the right).

Tip: Select only the columns or rows within the table for more inserting options. For
example, clicking a column header does not allow choosing to insert new columns to the
right or left.
2. Click the Home tab on the Ribbon and click the Insert button list arrow.

The options available here change, depending on the cell(s) that are selected in the table
or sheet.

3. Select the insertion option to use.

A row or column is inserted into the table.

Other Ways to Insert a Table Row Or Column: Right-click the row or column
where to add a row or column, point to Insert in the contextual menu, and select Insert

Table Rows Above or Insert Table Columns to the Left or Right. Or, to add a
new row to the bottom of the table, place the cell pointer in the last cell of the table and
press <Tab>.

Tips:
 PM2 - Management Reporting – IT Office Tools (MS Excel)

 When entering a formula in a blank column of a table, the formula is automatically

extended to all the rest of the column—without using the AutoFill feature. If rows are
added to the column, the formula appears in those rows as well.

Remove table rows and columns:

Remove unwanted table rows and columns by deleting them.

1. Select the table row(s) or columns(s) to delete.

Remember that doing this removes the data from the worksheet completely.
2. Click the Home tab on the Ribbon and click the Delete button list arrow in the Cells
group.

3. Select Delete Table Columns or Delete Table Rows.

The selected row(s) or column(s) are deleted.

Other Ways to Delete a Table Row or Column: Right-click the row or column to
delete, point to Delete in the contextual menu, and select Table Columns or Table

Rows.

Resize a table:
Expand the size of a table to include more data, or reduce the size to remove data.

1. Select a cell in the table.

2. Under Table Tools on the Ribbon, click the Design tab.

3. Click the Resize Table button in the Properties group.

The Resize Table dialog box appears.

4. Select the range to include in the table.

5. Click OK.

The table is resized. If cells are added to the table, they are empty so that data can be
entered in the cells.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

Other Ways to Resize a Table:

Click and drag the sizing handle in the lower-right corner of the table to include more or
fewer cells. Or, enter data in a cell below or to the right of the table; the table
automatically expands.

Working with the Total Row:

With the Total Row feature, Excel will automatically add a total row to the bottom of a
table and sum the last column of the table. The total row can also perform other types of
calculations.

Add a Total row:

1. Select a cell in the table.

Table Tools tab appears on the Ribbon.

2. Click the Design contextual tab under Table Tools on the Ribbon.

Now access is available to commands that can help change the design of the table.

3. Click the Total Row option in the Table Style Options group so that it is selected.

A Total row appears at the bottom of the table and the last column is summed.

Tip: If the last column doesn’t contain numbers, Excel displays a count of the number of
items in the column.

Calculate Total row values:

Once a total row has been added, decide what type of calculation to perform for the total
of each table column.

1. In the Total row, select the cell at the bottom of the column that contains values to
calculate.
2. Click the cell’s list arrow and select the calculation to perform.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

Sorting a Table
Excel is very good at sorting information. Excel can sort records alphabetically, numerically,
or chronologically (by date). Additionally, Excel can sort information in ascending (A to Z)
or descending (Z to A) order. Sort an entire list or any portion of a list by selecting it.

1. Click the filter button for the column to sort.

A list appears, displaying several options for sorting the table data. The options at the top
are for sorting.

Other Ways to Sort:

Click the Home tab and click the Sort & Filter button in the Editing group. Select a
sorting option from the list.

2. Select the sort option to use.

3. Click OK.

Tips:
 If adding or editing data in a table that is filtered or sorted, click the Reapply button in the
Sort & Filter group on the Data tab to include the new or edited data.

Custom Sorting:

To sort by more than one field, use a Custom Sort.

The Custom Sort can sort records by more than one field, such as to sort alphabetically by
first and last name, or by state and city.

1. Click a field’s filter button and select Sort by Color  Custom Sort… from the list.

The Custom Sort dialog box appears.

Other Ways to Use Custom Sort: Click the Home tab and click the Sort & Filter
button in the Editing group. Select Custom Sort from the list.

2. Click the Sort by list arrow and select the field by which to sort.

This is the first field to sort by.

 PM2 - Management Reporting – IT Office Tools (MS Excel)

3. Click the Sort on list arrow and choose what to sort by in the field. The options for the

Order change depending on the Sort On variable.

4. Click the Order list arrow and select the order by which to sort the data.

5. Click OK.

6. (Optional) Sort by multiple fields, by clicking the Add

Level button and set the sort specifications for the next field.

7. Click OK.

The data in the table is sorted by the sort specifications.

Filtering a Table:
When creating a table, a filter button that looks like a list arrow is added to the header of
each column in the table. Use this arrow to filter the table columns; they remain available
when scrolling down the list.

1. Click the filter button for the column to filter.

A list appears, displaying several options for sorting or filtering the table data. The check
boxes are all the data entries for the selected field.

2. Click the check box/boxes of the filter criteria to use.

Other Ways to Select Filter Criteria:

Click the Search box in the filter list and type the criteria by which to filter. The list
displays criteria that match the search.

3. Click OK.

Clear a filter:
When finished with a filter, clear it so all the table data appears again.

1. Click the filter button for the filter to clear.

 PM2 - Management Reporting – IT Office Tools (MS Excel)

2. Select the Clear Filter From option.

The filter is cleared and the table data is displayed without the filter.

Custom AutoFilter:
To filter using more complicated criteria, use a Custom AutoFilter. Custom AutoFilters are
more difficult to set up and create than ordinary AutoFilters, but they’re much more
flexible and powerful.

1. Click a field name list arrow and select Custom from the list.

2. Select Text Filters or Number Filters from the list.

A list of ways to filter the text appears.

3. Select a filter option from the list.

If Top Ten was selected, the Top 10 AutoFilter dialog box appears.

4. Complete the dialog box as necessary and click OK.

Excel now filters the table by the custom filter.

Removing Duplicate Rows of Data:

If there are duplicate rows of identical data in the table, Excel can find and remove the
duplicate rows.

Trap: Removing duplicate values actually deletes the duplicate data, so copy the data to
another worksheet or workbook first in case.

1. Select a cell in the table.

Tip: Remove duplicates from cell ranges outside of a table as well, but in that case, select
the entire cell range to examine.

2. Click the Data tab on the Ribbon and click the

Remove Duplicates button in the Data Tools group. The Remove Duplicates dialog
box appears.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

Other Ways to Display the Remove Duplicates Dialog Box:

Select a cell in the table. Under Table Tools on the Ribbon, click the Design tab. Click the

Remove Duplicates button in the Tools group.

3. Select the columns to check for duplicates.

All columns are selected by default, but it’s possible to select/deselect individual
columns in the Columns list. Use the Select All and Unselect All buttons to select
columns.
4. Click OK.

Duplicate values are deleted and a message appears, telling how many duplicate values
were found and removed.

5. Click OK.

Formatting the Table:

Change the appearance of a table by applying a preset table formatting style.

Apply a style while creating a table:

Apply a style while also creating a table.

1. Select a cell range that to format as a table.

2. Click the Home tab on the Ribbon and click the Format as Table button in the Styles
group.

The table format gallery appears. Select styles from the Light, Medium, or Dark categories.
Scroll down the list to see the Dark category.

3. Select a table style.

The Format As Table dialog box appears.

4. Click OK.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

A table is created and formatted with the selected style. The Table Tools tab appears on
the Ribbon, and the Design contextual tab appears. Apply a table style to an existing
table

1. Select a cell in the table.

2. Under Table Tools on the Ribbon, click the Design tab.

3. Select the style to use in the Table Styles group.

Tip: To display the entire Table Styles gallery, click the More button in the Table Styles
group.

Remove a table style:

A table style can easily be removed from a table.

1. Select the table that is formatted with the table style.

The Design tab appears.

2. Under Table Tools on the Ribbon, click the Design tab.

3. Click Quick Styles table Styles.

Click Clear in the Quick Styles Table Styles menu. The table format is removed

Format the table style:

After applying a table style, format individual table style elements.

1. Select a cell in the table.

2. Under Table Tools on the Ribbon, click the Design tab.

The formatting options available in the Table Style Options group include:

• Header Row: Toggles the table’s header row on and off.

• Total Row: Adds a total row to the bottom of the table. This option doesn’t just change
formatting, but also allows calculating values in the total row.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

• First/Last Column: Displays special formatting for the first or last columns in the table.

• Banded Rows/Columns: Displays odd and even rows and columns differently for
easier reading.

3. Select the option(s) to use in the Table Style Options group.

Converting to a Range:
If a table is no longer wanted, turn it back into a normal range.

1. Select a cell in the table.

2. Under Table Tools on the Ribbon, click the Design contextual tab and click the Convert

to Range button in the Tools group.

3. Click Yes.

The table converts back to a normal range of cells, but the table formatting is still applied.

Other Ways to Convert a Table to a Range: Select the table, right-click the table
and select Table  Convert to Range from the contextual menu.

Apply Intermediate Conditional Formatting:

Apply Data Bars, Color Scales and Icon Sets:
Format cells with data bars, color scales, or icon sets to visually display variations in the
values of cells in a range.

1. Select the cell range to format.

2. Click the Home tab on the Ribbon and click the Conditional Formatting button in the

Styles group.

Take a closer look at three similar types of conditional formatting:

Data Bars: Colored bars appear in the cells. The longer the bar, the higher the value in
that cell. Choose from different bar colors.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

Color Scales: Cells are shaded different color gradients depending on the relative value
of each cell compared to the other cells in the range. Choose from different colors.

Icon Sets: Different shaped or colored icons appear in cells, based on each cell’s value.
Choose from several types and colors of icons.

3. Point to Data Bars, Color Scales or Icon Sets.

A menu appears, differing based on the selection.

4. Select a data bar, 2- or 3-color scale, or icon set.

The conditional formatting is applied to the cells.

Tips:
 Additional options for data bars have been added to Excel 2016. Apply solid fills and
borders, and even change the direction of the bar. Data bars also have a new way to
display negative values

 More icon sets have been added to Excel 2016. Specify which icons appear from an icon
set.

Apply Advanced Conditional Formatting:

Create and manage new conditional formatting rules that follow the parameters and
formatting specified.

Create a new rule:

1. Select the cell range to format with a customized rule.

2. Click the Home tab on the Ribbon and click the Conditional Formatting button in the

Styles group.

3. Select New Rule.

The New Formatting Rule dialog box appears.

4. Select a rule type in the Select a Rule Type list.

 PM2 - Management Reporting – IT Office Tools (MS Excel)

5. Complete the fields in the Edit the Rule Description area.

This area will display different fields depending on the type of rule selected.

Tip: Click Preview in the New Formatting Rule dialog box to see how the rule will appear
before applying it.
6. Click OK.

The new rule is created and formatting is applied.

Other Ways to Create a New Rule: Click the Home tab on the Ribbon and click the
Conditional Formatting button in the Styles group. Click Manage Rules, then click
New Rule. Or, click the Home tab on the Ribbon and click the Conditional
Formatting list arrow in the Styles group. Click one of the rule types, then click More
Rules.

Manage rules:
Manage all aspects of conditional formatting—creating, editing, and deleting rules—in one
place using the Rules Manager.

1. Select the cell range with the conditional formatting to manage.

2. Click the Home tab on the Ribbon and click the Conditional Formatting button in the
Styles group.

3. Select Manage Rules.

The Conditional Formatting Rules Manager dialog box appears. The rules applied to the
selected cells appear in the dialog box.

Use these buttons to manage the rules:

• New Rule: Create a brand new conditional formatting rule.

• Edit Rule: Edit the selected formatting rule.  Delete Rule: Delete the selected rule
from the worksheet.
 PM2 - Management Reporting – IT Office Tools (MS Excel)

Tip: If a cell range is not selected where conditional formatting is applied, it is still possible
to view all the rules in the worksheet. Click the Show formatting rules for list arrow

and select This Worksheet.

4. Manage the formatting rules. Click OK when finished.

Formula Based Conditional Formatting:

You can create a formula-based conditional formatting rule in four easy steps:

1. Select the cells you want to format.

2. Create a conditional formatting rule, and select the Formula option
3. Enter a formula that returns TRUE or FALSE.
4. Set formatting options and save the rule.

Formulas that apply conditional formatting must return TRUE or FALSE, or numeric
equivalents. Here are some examples:

=ISODD(A1)

=ISNUMBER(A1)

=A1>500

=AND(A1>200,B1<500)

=OR(A1="Karachi",A1="Lahore")