Excel: Remove Duplicate

Description: This feature will help us to remove the duplicate cell or row from the sheet.
Steps:
1. Select the data.
2. Click Data tab.
3. Select ‘Remove Duplicate’ option as part of Data Tools session.

4. Select all items to remove the duplicate row. Select column names to remove duplicate from columns.

Excel: Summary from different sheets

Description: Pivot table wizard can be used for merging the data from different sheets.
Steps:
1. Select PivotTable and PivotChart Wizard option.
[Normally this option is not available in menu. Add this from Office icon-> Excel Options->Customize->All Commands-> PivotTable and PivotChart Wizard]


2. Select “Multiple consolidation ranges” and click next.

3. Select “Create a single page field for me” and click Next

4. Add ranges from different sheets and click Next

5. Specify whether the summary is needed in existing sheet or in new sheet and click Finish

We have more Pivot options under PivotTable Tools -> Options tab.

Excel: Table

Description: Insert table is a new feature in Excel 2007. Even though we can form tables using columns, rows and Gridlines in Excel, Insert table option is having lot of advantages or features in it.

Steps for inserting a table:
      1. Select the cells/ columns required to be part of the table.
      2. Click Insert-> Table option.
      3. Specify whether your table is having headers or not.
We have lot of formatting options for the table as given below



We have more options under Table Tools -> Design tab. Some of the features are export the table to SharePoint list/Visio pivot diagram, Summarize with Pivot table, Convert to Range, etc.
Main features of Table are given below.
Inclusion of new columns or Rows
When you add a new column near to the table, it automatically format and include the column to the table. Same way, it will format the new rows too.
Formulas
Table will take care of the empty cells referred in formulas. When you add a new record/row into the table, it automatically modifies the formulas which refer to the columns of the table.
Table Name
We can name the table using Table design tab.

By default system will name each table as Table1, Table2, and so on. Table names are useful to create formulas in a more meaningful way. We can refer the table from any sheet using the table name. Also, each column names are getting intellisence as follows.
Here, Product Tab, ProductTab11 are table names.

Column names are also getting intellisense as follows.

Freeze heading
Freeze the top row or portions of excel, to refer the column headings when you scroll down. If we have multiple tables/ headings, then the freeze feature will not be helpful. For avoiding this limitation, Table feature is came with an excellent feature, while scrolling the table change the real column heading [A, B, C,..] with column name of the Table.

Note: this will be available, only when you point to the table.

Excel: Green Error Mask

Description: This is the familiar green mark appearing in the left top corner of the cell. It indicates that, the data present in the cell is having some discrepancy or error. It is up to the user to correct the errors.

Number Stored As Text
One such indication is number is stored as Text. If a number stored in text format, this data will not be considered for any formulas referring the cell; in turn gives wrong data or result.

For avoiding this error, click on the cell and select the icon appearing on the left side of the cell
For Selecting all such errors in the sheet and act on it, press Ctrl+A two times. Click on one cell contains the error mask, press ctrl+A twice for selecting the entire sheet. Then the icon will appear on the top left corner of the sheet.


Inconsistent Formula
This error mark represent the cell is having a formula which is different from the adjacent cells. In the below screen shot, we used the formula as Amount*3.2% in all cells except the one with green error mask, where we used the formula Amount*3.3%.

Formula Omits Adjacent Cells
This indicates that the formula is omitted some data adjacent to it. In the below example, we calculated the Total revenue as a sum of revenues from Jan to April. After that, we added the revenue of May.

Excel: Conditional Formatting

Description: Make the business data more appealing using color or icon combinations. Old versions of Excel are having a limitation of maximum 3 conditions. In Excel 2007, we can have more conditions. Different options available are:-

Data Bars – Represent the data along with a graphical notation of the data.
Color Scales – Represent the value using color ranges
Icon Sets: - suitable for specific range of values like grades, ranks, etc.
Top/Bottom Rules: - Useful when we want to highlight Top 10 values or bottom 10% items or the items above/below average.
Highlight Cells Rule: - Useful when we need to apply the style using complex conditions.