10 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 the worksheet. 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 the worksheet.
• 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

6. How to Export Excel to AutoCAD

There are some offices that uses AutoCAD. Now you need to get the information stored in your AutoCAD drawings. In most cases, whenever you import information from Excel into AutoCAD, if it’s larger than 8.5×11 page it is trimmed.

The problem is that AutoCAD imports the woksheets as picture. If you just want the worksheet’s data and not a picture of the data, then export the Excel data in CSV format (which is plain text) and then import it into AutoCAD. To make sure that the data is pasted properly you follow the steps of the hack that follow

• Open the Excel and select the data area you want to copy
• Press Ctrl+C to copy the data to the Clipboard
• In AutoCAD, choose Edit > Paste Special > AutoCAD Entities
• Click OK

Zoom the model to a correct size before you paste the Excel data.

7. How to Ignore Other Applications

Normally Excel works well with other applications. In case, another application is interfering with the way you want to work with excel, you may stop the other applications from running with Excel. Go through the steps below to know how to

• Choose Options from the Tools menu. Excel displays the Options dialog box
• Ensure that the General tab is displayed
• Select the Ignore Other Applications check box
• Click OK

8. How to Highlight Cells of Specific Text

At times you need to draw attention to cells that contain important text in which you are interested. This can be accomplished through the conditional formatting feature in Excel. Suppose you have a wide array of cells and you need to know the cell that contains Johnny.
Here’s the hack for you

• Choose the range of cells
• Choose Conditional Formatting from the Format menu. Excel displays the Conditional Formatting dialog box
• Select the Formula Is in the drop-down Condition list
• Enter the following formula in the formula box (Replace A1 with the cell address of the cell in the upper-left corner of the range selected in step 1.)

=NOT(ISERR(SEARCH(”John”,A1)))
•  Click on Format. Excel displays the Format Cells dialog box.
• Using the controls in the dialog box, specify a format that you want to use for those cells that contain the specified text. For instance, you may set it to italic text in a blue typeface
• Click on OK to close the Format Cells dialog box
•  Close the Conditional Formatting dialog box.

9. How to Paste Column Widths

To copy paste the cells you use Ctrl C and Ctrl V. But this doesn’t paste the column widths that can accommodate that data. So what you do

• Use the normal Paste option to paste in the data and cell formats
• Right click on the new range of data you have pasted
• Click the Paste Special, and select the Column Widths radio button.

10. Shortcut for Switching Focus

This is the easiet hack I have got to inform you. Most of you know that in Windows the Alt+Tab shortcut allows you to switch from one opened application to another. In case, you want to switch to the desktop, and then back to your application again, what’s the simplest way to do it? Here’s the hack for you

Well, I assume your keyboard has the Alt and WIndows ket side by side.

Press Windows+D to minimize all the open windows and change focus to the desktop. Press Windows+D again, and focus is returned to the window in which you were previously working.