Excel: Page Layout View

Description: Excel 2007 contains Page layout view similar to print layout view in Word. This will display the excel sheet in the form of pages with header and footer options.
Steps:
1. Select View Tab.
2. Select Page Layout option as shown below.



Page Layout view

Excel: Trace Error

Description: Trace error will point to the root of the error. If the cells display some error message and the data in the cell combined of some complex formulas, then it is difficult to find out the real cause for the error. Trace error will trace the error till its original position.

In the below example, K14 having error message. For tracing the error, select Error Checking option under Formula Auditing section under Formula tab. Select Trace Error option from the Error Checking window. This will trace the root cell or till the cell which caused the error.

Excel: Formatting

Description: There are many format options available which we are not aware. As there are lots of format options, this section is not going to explain all options. It will give an example where the usage of a bad format resulted in wrong business data.

Scenario: We have the start date and end date of a project. We need to calculate the project duration in hours. The formula used is simple and placed the cell format as hh:mm. The duration between 15th April 8 AM and 15th April 5PM is calculated correctly [9 hrs]. But the duration between 15th April 8 AM and 16th April 5PM is also calculated as 9 hours, which is wrong. Heredue to the wrong formatting, formula is avoiding the date, it considering only the time.



Now let us see the correct format option. Noticed the formula used is same. Only difference is the cell format where it changed from hh:mm to [hh]:mm.

Excel: Trace Precedents/Trace Dependents

Trace Precedents: Shows arrows that indicate what are all the cells affecting the value of currently selected cell.
Here, value of the currently selected cell H14 is the sum of the cells H10 and H12. In turn, value of H10 and H12 is calculated using other cells.



Trace Dependents: Shows arrows that indicate what cells are affected by the value of the currently selected cell.

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.