5 MS EXCEL HACKS AND TRICKS

There are millions of Microsoft Office Excel spreadsheets users who rarely care to look for the best side of it, I mean hacks. There are a wide range of functions performed on Microsoft Excel from calculations to analyzing data to integrating information, the list continues. With a fluent interface rich data visualization, and PivotTable views MS Excel is now easier to use. There is lot to do with MS Excel, if you wish to be one of those Excel gurus. Well, I have some cool ideas to excel your MS excel performance. Here are my MS Excel hacks to improve you MS Excel experience.

1. How to Merge Cells Easily

If you are a Excel working you know how often do you need to merge cells. As I've known there are no shortcut keys for the task. Well, you can create your own shortcut using a macro. Here's the macro that you can use to merge your selected cells

Sub MergeCells1()
Selection.Merge
End Sub

Assign the created macro to a keyboard shortcut and get set to go. Alternately, you can create a macro to work as a shortcut for Merge and Center tool. Here's the macro

Sub MergeCells2()
With Selection

• HorizontalAlignment = xlCenter
• Merge
  

End With
End Sub

2. How to Count Items using a PivotTable in Exel 2007

The PivotTable can be used to generate counts of the items in a data table. Let's say you have a data table in Excel containing the club members. Suppose, the first column has the numbers and the second column contains all the cities in which the members live. Now, if you are to find how many people live in each city, the easiest way is to create a PivotTable. Let's do with the steps for the hack

• Select a cell from the data table
• Ensure that the Insert tab of the Ribbon is displayed
• Go to the Tables group, click the PivotTable tool. Excel displays the Create PivotTable dialog box
• Make sure that the entire data is selected. In the range box click OK. An empty PivotTable worksheet is created
• Drag the City field to the Row Labels area
• Drag the Name field from the field list to the Values area.
  

Your PivotTable will be created.

3. How to Delete the X Row in the Work sheet

By filtering the data you can use just a portion of the information you need. Sometimes you need to retrieve data from another user or external program this may require to delete certain rows, say you need to remove every third row or every fifth row in theworksheet. Generally you would use a macro but there's a simpler hack. Let's see the steps

• Insert two columns (A and B) on the far left side of the worksheet
• In the new column A, use AutoFill to sequentially number the rows from 1 to the end
• In cell B1 (assuming you have no header row), enter the formula =MOD(A1,4). (I assume you want to delete every 4th row. For other multiple of rows, substitute that number in place of the 3 in the formula.)
• Copy the same formula downward to all the other cells in the B column
• Now Display the datatab of the Ribbon. In the Sort & Filter group, click the Filter tool. You will see small drop-down arrows at the top of each column in theworksheet.
• Select 0 in the dropdown list in column B.
• Choose all the displayed rows and delete them
• Turn off AutoFilter ( look for the step 5)
• Delete columns A and B
  

4. How to Hide Duplicate Records

A common mistake by most of the excel users is including duplicate entries while preparing reports or sorting data. This can b avoided with a simple hack. Let's see the steps

• Go to the Data menu, and then to the point to Filter and click Advanced Filter
• Drag across the worksheet to select/highlight the lists containing duplicate entries
• Check Unique records only option
• Click OK
  

This will hide any duplicate records in the selected range

5. How to Delete undesired Web Stuff

Often you copy information from the Web and Paste it in your worksheet. The worst part of it is that you are stuffed with all sorts of other items, such as checkboxes, pictures, logos, and such other undesired stuffs. So how do you get rid of them, let's see the hack

• First press the F5. Excel will displays the Go To dialog box
• Hit the Special button. Excel will display the Go To Special dialog box
• Select the Objects option
• Click OK.
  

This will select a number of the objects in the worksheet. Press the Delete key to get rid of them. For a more viable solution use the macro provided below

Sub DeleteAllShapes()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next
End Sub