Handy Excel Tips

If we have a list of student names and we want to calculate the frequency of each name then we can use a countif function to obtain this. However the disadvantage of countif function is we first need to make a table with no repetition of names and only then we can apply countif.

The main advantage of using pivot table instead of countif function is, it will create a table itself and we don’t need to worry about it.
Moreover to make a pivot table, it is not necessary that we have two categorical variables. Even if we have one variable like “names” then we can drag “names” to the rows and “count of names” to the data region. So against each name it will give us the frequency.

Below is the step by step procedure:

Choose the “Pivot Table and PivotChart Report” from the “Data” drop down menu and click “Next”.

Select the range and then click “Next” tab.

Then click “Layout” tab and drag the variable and click “Ok”as shown in the diagram below.

So you have got exactly the same table as above using countif function but the best part is you did not manually enter the names.

Popularity: 26%

To switch between your open excel workbooks,you can use the “Window” drop-down menu as it lists all the workbooks that are currently open. Alternatively, you can use the shortcut keys(Alt+Tab) to move from one window to another.You need to hold down the Alt key as you repeatedly press the Tab key to switch from one window to another.
When the desired workbook window is selected, release the Alt key and the
actual workbook will be displayed.
However using this combination(Alt+Tab) will display the names of not just the excel
workbooks but all other applications that are currently open like word documents, notepad, internet explorer etc. If you only want to toggle between your open excel workbooks you can use (Ctrl+Tab) key combination instead You can also switch between excel sheets using the shortcut keys (Ctrl + F6).

Popularity: 11%

If you right click on the chart, you would not see a delete option. So to delete a
chart, just select the chart area with a single mouse click and press the Delete key.

Popularity: 4%

If an entry in a particular cell is too long, its value will be displayed as bunch of hash keys (#####). This is not an error and you can get around this problem by using the “Autofit selection” feature of excel. This can be found under “Column” tab of “Format”
drop down menu. Autofit selection option will expand the column to the size of the longest entry. If you want to expand all the columns then just press(Ctrl+A) to select the whole data and press(Alt+O+C+A) which is the shortcut for the Autofit selection option.

Popularity: 5%

If you want to see all open workbook names,choose “Options” from the Tools menu.And
make sure the “Windows in Taskbar”check box is selected on the “view”tab.
However if you don’t want too many taskbar buttons you can simply uncheck this
“Windows in Taskbar”check box.

Popularity: 6%

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%