Handy Excel Tips

This article talks about formatting and borders.

Read the rest of this entry »

Popularity: 12%

This article basically compares excel’s vlookup and sumif array functions and explains which one to use, when and why?

Read the rest of this entry »

Popularity: 46%

If you hit F2 on any cell, you can see the formula of that particular cell only.This article gives you a shortcut with which you would be able to see all the formula’s present on your spreadsheet all together.

Read the rest of this entry »

Popularity: 9%

This article talks in great detail about excel’s Left and Right formula’s and explains how by combining “Find” and “Len” functions with Left and right we can easily handle more comlicated situations which may arise.

Read the rest of this entry »

Popularity: 24%

Unlike microsoft word, Excel does not have an option wherein you could Format text to change case. But don’t worry, excel does have “change case” functions, so in case you have incorrectly typed everything in one case than another, you won’t need to rekey them.
To change case you can use upper, lower or proper formula.
Upper would change everything to upper case. Similarly lower would put them in lower case.Proper case would capitalise the first letter, keeping the rest in lower case.

Suppose you wish to change the contents of column A to lower case instead of upper, you can do that as follows:

Popularity: 13%

The Excel’s Concatenate function can be used to join up contents of multiple cells into one cell.

The syntax for the CONCATENATE function is
=CONCATENATE(text1, text2)

When can “Concatenate” function come in handy??

When say first name is given in column A and last name in column B and you want to combine the two.
Concatenate function can help you do this as shown below:

But notice there is no space between the two so if you want to insert a space you can modify your concatenate function as follows:

Points to remember:
Concatenate function uses contents of other cells(like A and B here). So if the linked data is changed, the concatenated cell( Say C1) would change as well. And if you delete the related columns then you will get a reference error in the concatenated cell.To overcome this, you can either hide column A and B or simply copy the contents of column C and use paste special values command under the “Edit” tab.

An alternative to concatenate function:

The ampersand (&) operator can also be used instead of the CONCATENATE function to do the same task.

So in our example above if we enter =A1&B1 in cell C1, it would give the same value as =CONCATENATE(A1,B1) as shown below:

Popularity: 13%

Suppose you have the marks of each student and you want to generate a frequency distribution ie total number of students who obtained marks within a given interval.
You can do this using either Excel frequency function or by applying logic.
One of the con of using set functions like “Frequency” is that you have to familiarize yourself with the syntax first.
Like the syntax of the Frequency function is:
Frequency(Data_Array,Bins_Array)
So you should know what are data and bins array.

Data_array is an array of or reference to a set of values for which you want to count frequencies. Bins_array is an array of or reference to intervals into which you want to group the values in data_array.
The following example would make it clear.
Here is a list of marks and the interval for which you need a frequency.

So you want to find out how many students got marks below or equal to 20, how many got between 21 and 30 and so on.

Since it is array formula, so we first need to select the cells where we want the frequency i.e. E5:E10, then enter the data and bin array and hit CTRL+SHIFT+ENTER.

So, 2 students got marks below or equal to 20, 2 got between 21 and 30 and so on.

We can also do this using SUM(IF()) array formula.

This function uses two criteria and “* “in array formula denotes the second condition. So it says sum column B if marks are greater than 0 and less than or equal to 20. As you would notice, the answer is same using both options.

This function can also handle multiple frequencies which “Excel frequency function” can not. For eg. if 3 students got the same score 12 and 4 students got 47. Then if we want to do the above exercise using frequency function we will have to enter the same marks thrice or four times whatever be the case. Whereas if we use the above mentioned SUM(IF()) array formula we can simply enter these in column B as shown below:

Popularity: 29%

Sometimes when you enter text in excel, it automatically converts it to some other format. This can be very annoying. Like if I want to enter age bands as 11-20,21-30 etc then excel would read 11-20 as 20-Nov.

We can get around this problem by adding a single quote in front. This quote won’t get displayed or printed but just tells excel to treat the entries as text. However if you press F2 and try and edit the values, you will see a single quotation mark in front.

But make sure you enter the quote before hitting enter i.e. if excel has already made it 20-Nov and now if you put a quote in front then it won’t change.

This can also come in handy when you have to enter more than 15 digits in a single cell and you want to keep the format as text. If you don’t enter a quote in front or change it to a text format, excel would display it in scientific notation like 4545121215241541514 would be shown as 4.54512E+18.

Popularity: 11%

If you want to select adjacent sheets then you can do that by holding down the shift key and clicking on the next sheet tab.For e.g. There are six worksheets - sheet 1 to sheet 6. And you want to select one to four then simply select the first sheet, hold down the shift key and click on the forth sheet.You will notice this will automatically select sheet 2 and 3 as well.
However if you want to select non adjacent sheets then hold down both the shift and control keys together and choose the desired sheet.

If you want to do a mouseless selection, just hold down the shift and control keys and press “Page up” or “page down”.

If you want to select all the worksheets at once then you can also right click on one of the sheet tab and choose “Select all sheets” option.

Popularity: 5%

We can select non-adjacent cells by holding down the control key and then simply choosing the desired ones by a single click of a mouse.
However this can be very frustrating at times, because if you have mistakenly released the control key and you are not through with your selection yet,then you will have to start all over again.
We can get around this problem by just pressing the (shift + F8) keys once and then highlighting the cells that you need,using the mouse.
When you are through with your selection, you can press the same keys again or just hit the “Esc” key.

Popularity: 14%

Once we have applied filter to our data using the excel’s “AutoFilter” option and we want to sum only the non-hidden range, then we can’t use the simple “sum” function as it adds both the hidden and visible cells. In that case we can use the excel’s subtotal function as it only sums the visible range.

The syntax of the Subtotal function is:

SUBTOTAL(function_num,range_reference1, range_reference2….)

We can perform 11 different functions in conjunction with subtotal.

Function_Num - Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Suppose you have a list of 4 different products - A, B, C and D along with their quantity sold.

And you wish to add up only the quantity sold of product A. Then if you apply filter and sum the range using “Sum” function then it will give the total for all the products

Whereas if you use subtotal function you will only get it for product A as shown below:

Popularity: 9%

Whenever you have some text in one column and you wish to break it into several ones then instead of retyping the text in the next column, you can use the “Text to Columns” option under the “Data” tab. For e.g. Both the names and marks of the students are in the same column(say Column A) and you want to separate them in different columns as shown below:

The first step is to highlight the column that needs to be separated. Then click “Data” and choose “Text to Columns” option.

Then choose the delimited option.

Select the “space” check box, keeping others unchecked. So whatever text is after the space will be put into the next column.

As soon as you click the “Next” tab you will see the preview of your data. And you will notice that it has put marks into the next column.

Now just click the “Finish” button and it will be done.

Popularity: 5%

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: 23%

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: 10%

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: 8%

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: 7%

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: 6%