Handy Excel Tips

To hide the selected rows enter CTRL+9.Entire row can be selected by pressing (shift+spacebar) together and then holding the shift key use the up or down arrow key to select more than one row.

To unhide any hidden rows within a selection enter CTRL+SHIFT+(

Similarly, to hide the selected columns enter CTRL+0.Entire column can be selected by pressing (Ctrl+spacebar) together and then holding the shift key use the right or left arrow key to select more than one column.

To unhide any hidden columns within a selection enter CTRL+SHIFT+)

Popularity: 9%

Long route: Copy the cell (CTRL+C) and then select the range by holding the shift key and paste it((CTRL+V).

Short route: No need to copy the cell. Just select the range including the topmost cell by holding the shift key and hit CTRL+D.

Popularity: 8%

Excel’s handy tips:

CTRL+1 Displays the Format Cells dialog box.

CTRL+2/CTRL+B Applies or removes bold formatting.

CTRL+3 Applies or removes italic formatting.

CTRL+4 Applies or removes underlining.

CTRL+5 Applies or removes strikethrough.

CTRL+9 Hides the selected rows.

CTRL+0 Hides the selected columns.

CTRL+A Selects the entire worksheet.

CTRL+C Copies the selected cells.

CTRL+F Displays the Find and Replace dialog box.

CTRL+G/F5 Displays the Go To dialog box.

CTRL+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.

CTRL+N Creates a new, blank workbook.

CTRL+O Displays the Open dialog box to open or find a file.

CTRL+SHIFT+O selects all cells that contain comments.

CTRL+P Displays the Print dialog box.

CTRL+R Uses the Fill Right command to copy the contents and format of

the leftmost cell of a selected range into the cells to the right.

CTRL+S Saves the active file with its current file name, location, and file format.

CTRL+T Displays the Create Table dialog box.

CTRL+U Applies or removes underlining.

CTRL+W Closes the selected workbook window.

CTRL+X Cuts the selected cells.

CTRL+Y Repeats the last command or action, if possible.

CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

CTRL+SHIFT+& Applies the outline border to the selected cells.

CTRL+SHIFT+_ Removes the outline border from the selected cells.

CTRL+SHIFT+~ Applies the General number format.

CTRL+SHIFT+$ Applies the Currency format with two decimal places.

CTRL+SHIFT+% Applies the Percentage format with no decimal places.

CTRL+SHIFT+# Applies the Date format with the day, month, and year.

CTRL+SHIFT+@ Applies the Time format with the hour and minute, and AM or PM.

CTRL+SHIFT+! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values.

CTRL+SHIFT+Plus (+) Displays the Insert dialog box to insert blank cells.

CTRL+Minus (-) Displays the Delete dialog box to delete the selected cells.

CTRL+’ Copies a formula from the cell above the active cell into the cell or the Formula Bar.

F1 Displays the Microsoft Office Excel Help task pane.

ALT+F1 Creates a chart of the data in the current range.

F2 Edits the active cell

SHIFT+F2 Adds or edits a cell comment.

CTRL+F2 Displays the Print Preview window.

SHIFT+F3 Displays the Insert Function dialog box.

F4 Repeats the last command or action, if possible.

CTRL+F4 Closes the selected workbook window.

F5 Displays the Go To dialog box.

CTRL+F6 Switches to the next workbook window when more than one workbook window is open.

F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.

F9 Calculates all worksheets in all open workbooks.

SHIFT+F9 calculates the active worksheet.

CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.

CTRL+F9 minimizes a workbook window to an icon.

SHIFT+F10 displays the shortcut menu for a selected item.

CTRL+F10 maximizes or restores the selected workbook window.

F11 Creates a chart of the data in the current range.

SHIFT+F11 inserts a new worksheet.

ALT+F11 Opens the Microsoft Visual Basic Editor, in which you can

create a macro by using Visual Basic for Applications (VBA).

F12 Displays the Save As dialog box.

CTRL+HOME moves to the beginning of a worksheet.

CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.

TAB Moves one cell to the right in a worksheet.

SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.

ESC Cancels an entry in the cell or Formula Bar.It also closes an open menu or submenu, dialog box, or message window.

CTRL+END moves to the last cell on a worksheet.

CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet.

BACKSPACE Deletes one character to the left in the Formula Bar.

DELETE Deletes one character to the right in the Formula Bar.

ALT+DOWN ARROW opens a selected drop-down list.

SHIFT+ARROW KEY extends the selection of cells by one cell.

CTRL+TAB switches to the next tab in dialog box.

CTRL+SHIFT+TAB switches to the previous tab in a dialog box.

PAGE UP Moves one screen up in a worksheet.

ALT+PAGE UP moves one screen to the left in a worksheet.

CTRL+PAGE UP moves to the previous sheet in a workbook.

CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.

PAGE DOWN Moves one screen down in a worksheet.

ALT+PAGE DOWN moves one screen to the right in a worksheet.

CTRL+PAGE DOWN moves to the next sheet in a workbook.

CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.

Popularity: 7%

If you want to compare two excel worksheets on the same screen you can use “Compare side by side with” command on the window menu. Excel will arrange them horizontally.
If more than two workbooks are open, Compare Side by Side dialog box will give the names of all the workbooks so that you can choose the one you want to compare with the active workbook.
Below is the screen shot of “Compare side by side with” option where we have assumed two workbooks “A” and “B” are open.

However “compare side by side with” command can not be used to compare more than two workbooks. In that case we can use the “Arrange” option on the window menu and can choose the desired arrange option-Tiled, Horizontal, Vertical, or Cascading. When choosing the “Arrange” option make sure the “Windows of Active Workbook” check box is not selected.

Popularity: 6%

If your calculations in a particular column are dependent on values in some other column which has blank entries in some cells then to avoid any value error you can use a simple “IF” command say column B2 is blank and column D2 is dependent on values in B2 then the equation would be:
=IF(B2=”",”",Formula) Where “” represents blank cell.

Popularity: 5%

Identifying duplicate entries in a large list can be very time consuming but with conditional formatting duplicate entries can be identified very fast.You just need to select the cell range and then click “format” tab and then conditional formatting (Keyboard Shortcut is: Alt+O+D). From the condition1 drop down menu choose “Formula Is” and enter =A3=A2 (Assuming the selected cell range is A2:A23). Click the format button and choose any color and just hit OK.

However the above formula would work only when we have duplicate entries in adjacent cells. If instead we want to identify all the duplicate entries that are there in the entire column we can use the countif function instead of “=A3=A2″. So this time in the formula bar of conditional formatting you enter =COUNTIF($A$2:$A$23,A2)>1. We need to freeze the range( A2:A23) by using F4 key otherwise as we go down from A3 to A4 the range would become (A3:A24) which is not what we need. However cell A2 is an active cell and we want excel to take A3 and so on as move down that is why we have kept A2 as a relative reference instead of making it absolute(Dollar sign).

Popularity: 16%

Sumif(offset) Functions

Sumif function is useful as long as we want data only from one column as sumif only allows a single condition. However we can get around this problem by using a combination of sumif and offset as is illustrated in the example down below.

Suppose you work at some XYZ organisation and it launched product A and B in year 2001, C in 2002, D in 2003 and E in 2004. They have provided you data in one format but require the results to be presented in some other format as shown in the sheet below.

Now we can use the sumif function to get the results in the required format.

The syntax of the sumif function is:
SUMIF(range,criteria,sum_range) OR
SUMIF([Column to be evaluated],[Criteria],[Column to sum if criteria is met])

The main problem with using a plain sumif function here is you will not be able to drag the formula and you will have to change the name of the column from B to C to D to E as you move along 2001,2002,2003 and 2004 as shown below.

The offset function can resolve this issue as it allows multiple conditions and will pick the numbers from the correct column.

The syntax of the offset function is:
Offset( range, rows, columns, height, width)

Although the result is same using both options but using sumif along with offset would spare us from changing the formula as we move down.

Popularity: 23%

Excel to PDF Converter

For this conversion, you don’t require any special Excel to PDF Conversion software. All you need is either Adobe Acrobat 8 Standard or Adobe Acrobat 8 Professional installed on your computer. If you have Adobe® Acrobat® 3D Version 8 then that is the king of the Acrobat family. It does just about anything. However if you just have the Adobe Reader, then that can only be used to read a PDF file. It cannot create one.
Once you have Adobe Acrobat professional or standard installed on your PC, there are two ways you can do the conversion.

1) Create a PDF using Adobe Acrobat icon

Double click on this. It will give you various options like create PDF, combine files, export, start meeting, secure, sign, forms and review and comment. Choose the create PDF tab and then click on the “Create PDF from a file” option. Just give the location of your excel file and it will be done.

2) Create a PDF using the print command

The simpler method would be to open your excel file and select the “print” option from the “File” tab. And just change the name from your printing machine to Adobe PDF. So print option does not necessarily give you the print outs or the hard copies but it can also print to the Adobe PDF format.

Popularity: 6%