Must know tips in Excel ?

Tips for Navigation

1. Press [Ctrl] + [Home] and it will takes you to cell A1

2. Press [Ctrl] + [End] and it will take you to the last data cell in your worksheet

3. Press [Ctrl] + [PgDn] to move to next sheet

4. Press [Ctrl] + [PgUp] to move to previous sheet

5. The control buttons on the down left corner of the sheet tabs lets you

navigate through sheets. You can right-click on these control buttons and from the resulting menu you can select the sheet you want to view.

6. Press [Ctrl] + [Tab] to move to the next window (i.e. to next open workbook)

7. The Status Bar, at the very bottom of the screen usually says Ready in the lower lefthand corner. It provides us with useful informations. One of the useful feature is that when a block of cells is selected the SUM of the cells will appear in the Status Bar. Right-click the SUM in the Status Bar and you can choose another available functions to apply to the selected cells. You can't do anything with this result, however, except view it in the Status Bar.

  

8. View à Split or View à Freeze Panes will divide the window above and to the left of the current cell pointer position. This will allow column and /or row headers to remain displayed in one section of the window while you can scroll and move through data in another section of the window.
You can also split the window by dragging the little gray bar above the up arrow in the vertical scroll bar and /or the little gray bar to the right of the right arrow in the horizontal scroll bar.

9. With View à Arrange All option you can arrange the open workbooks with the below options :

Tiled | Horizontal | Vertical | Cascade

10. With View à Switch Window Option you will be able to see all open workbooks in the dropdown. From here you can directly select the workbook you need to view.

Note - all tips are tested for Microsoft Excel 2010


Tips for Selecting Cells

1. Hold [Ctrl] while using mouse to select the cells it will help you to select a random / non-contiguous block of cells

2. Click the mouse once in the upper lefthand corner cell in the block of data you want to select, then hold the [Shift] key down when you click the mouse on the cell at the the lower righthand corner of your block. This will select all the data in the range of cells.

3. Press [Ctrl] + [Shift] + [down arrow] to select the range of cells in a column in downward directed from the selected cell.

4. Press [Ctrl] + [Shift] + [up arrow] to select the range of cells in a column in upward direction from the selected cell.

5. Press [Ctrl] + [Shift] + [right arrow] to select the range of cells in a row in right direction from the selected cell.

6. Press [Ctrl] + [Shift] + [left arrow] to select the range of cells in a row in left direction from the selected cell.

7. Press [Ctrl] + [*] or [Ctrl] + [A]  to select cuurent block of data. This selected block of data can be moved with the help of mouse to some other location. Also, if you press [Ctrl] while moving the data, this will copy the data to other location.

Note - all tips are tested for Microsoft Excel 2010


Formatting the Cells

1. You can use the Format | Conditional Formatting to check the different logical conditions and highlight the cells based on that. e.g. error checking (invalid values can show in the formmatting we have selected)
2. In conditional formatting, If you have specified multiple conditions, then the conditions will be evaluated from the top of the list. Once the cell satisfies a condition it applies that formatting and doesn't continue down through the rest of the possible conditions.

3. To find cells that are formatted with Conditional Formatting use Edit | Go To... | Special and choose the Conditional formats radio button.

4. To find cells with identical conditional formats to the selected cell, click Same below Data validation.

5. To find cells with any conditional formats, click All below Data validation.

6. Tools | Options | Calculation | Precision as Displayed  checkbox can prevent you from falling into a potentially embarrassing "rounding error" situation 
7. You can transpose your columns to rows or rows to columns? Copy the data you want to transpose, go to Home | Paste | Paste Special dialog box | click on Transpose check box and then OK.

8. Home | Clear will provide you with different options to clear the selected cells. i.e, Clear All | Clear formats | Clear contents | Clear comments | Clear Hyperlinks

9. There may be certain portions of the worksheet that you'd like to protect from any possible changes. By default all the cells in the worksheet are locked but the locks are ignored. Review | Protect Sheet activates recognition of the locks. Before using Protect Sheet you would unlock all the cells you want to be able to edit when the rest of sheet is protected. Select Cells, Format | Cells | Protection tab and uncheck the default lock. Then use Review | Protect Sheet.

10. If most of your cells are going to be unprotected with just a few protected
Use [Ctrl] + [A] to select all the cells in the sheet Format | Cells | Protection tab and uncheck the default lock. Select the cells you DO want to protect Format | Cells | Protection tab and CHECK the lock back on
Review | Protect Sheet

Note - all tips are tested for Microsoft Excel 2010

How to create Cell charts in excel ?

Ever wondered how the small graphs are shown in the cells in excel. These are used to show small trends for many different values, which can be easily understood with these views. e.g. in case of stock company values these charts can be use to show the market trends.

There is a new feature introduced in the Microsoft Excel 2010 which helps you to insert the graphs in cells. This new feature is the sparklines for a single cell. Using these sparklines you can now create tiny integrated graphics within each cell. These sparklines can be easily used to detect patterns of the data tables. This is a very simple and quick feature, to highlight important trends in the that data (such as seasonal increases and decreases).

Below are the guidelines to create the spraklines charts in cell :

1. To make use of sparklines, select the cell where we need to place the tiny graph.

2. Then, click on the Insert tab àsparklines. here you have 3 types of charts to choose Line , column and gain or loss.

3. Once you choose the type, you will get a pop-up message to choose the Target cell and the Data Range.

4. Update the data range, where your data is present update the cell number in Location Range, where you need to see your graph.

5. Once the range is updated and you click ok, the graphs are generated instantly in the location range.

The choice of the sparkline chart type seems more visual in the online format. The format with gain or loss is very suitable for interpreting balance sheets.

It is very fast, simple and intuitive interpretation of the data table, identify trends and monitor developments. The graphs which were initially quite difficult to read as a set of numbers has now become easily understandable.

How to use VLOOKUP and HLOOKUP ?

VlookUp stands for vertical column look up. This formula is used to find the value in the column vertically.

e.g.

We have a table of employee details, where employee id, first name, last name, email id and contact number data is there. The data is in column A, B, C, D and E respectively.

This table contains data for 10000 employees.

Problem : We need to get the email id and contact number data for 43 employees in the output. 
The only input we have is employee id in column H.

Solution - Vlookup

below is the formula which can be used to get the employee details -

Get email id
=VLOOKUP(H2,A:E,4,0)

how it works ?
1. here H2 is the reference employee id for which we need to find the data
2. A:E is the table range where our data is present
3. 4 is the number of the column starting from column A, in which our data is present
4. 0 is for exact match

Similarly use the below formula to  get contact number
=VLOOKUP(H2,A:E,5,0)

Note - 
1. The reference value which we are using to look up the data should be always in the left hand side of the required output data. in above example employee id is on the left side of the data.
2. The data range should start from the reference number column. In above example range is starting from (A;E) where A is employee id column.
3. The number for the output column should be counted from the start column of reference value. In example above we are counting the output value from column A, i.e. our output values are in column 4 and 5.



 
HlookUp stands for horizontal row look up. This formula is used to find the value in the rows horizontally.

e.g.

We have a table of students marks details, where column headers are 
Student Name
English

Maths

Science

History.

The data is in column A, B, C, D and E respectively.

Data under Student Name column is -

Amol

Vijay

Kiran

Vidya

....

...

..

. and more

This table contains data for 5000 Students.

Problem : We need to find the marks of English and Science, only for Vijay and Vidya or for list of say, 30 students only.

The input we have is the table data in which we can use the user name and the subject name to lookup the required value.

example table:

Solution : Hlookup

below is the formula which can be used to get the required output

=HLOOKUP(B8,$A$1:$E$5,3,0)

result = 32

Below is the screenshot of output table, this table is on cells A8:C10.

how it works ?

1. here B8 is the subject name (English) for which we need to find the data

2. $A$1:$E$5 is the table range where our input data is present

3. 3 is the number of the row starting from column header, in which our data is present

4. 0 is for exact match

Similarly use the below formula to  get data for subject Science

=HLOOKUP(C8,$A$1:$E$5,3,0)

result = 27

There is one challenge in the above formula for which we need to take help of one more formula combining with Hlookup.


Challenge : For 5000 students how to enter the number automatically in step 3 above

Solution : use the MATCH formula to find the row number in which our required data is present

e.g. find row number for Vijay, use the below match formula

=MATCH(A9,$A$1:$A$5)

result = 3

how it works ?

1. here A9 is the Student name (Vijay) for which we need to find the data

2. $A$1:$A$5 is the table range where our input data is present

Now combining the Match formula with Hlookup, we can get the required output without manual entry.

Our actual Hlookup formula:
=HLOOKUP(B8,$A$1:$E$5,3,0)

Formula combined with MATCH:
=HLOOKUP(B8,$A$1:$E$5,MATCH(A9,$A$1:$A$5),0)

result = 32

How to calculate days in a month using Excel formula ?

Below is the formula which can be used to calculate the days in a specific month.

Enter any date in cell B2

Copy paste the below formula in Cell B3

=DAY(DATE(YEAR(B2),MONTH(B2)+1,1)-1)


e.g. if the value in the Cell B2 is 10/22/2014 (i.e. 22nd october 2014)

Then the result in the cell B3 will be 31


How it works ?

we will break up the above formula in  parts.

1. Year(B2)          - this will provide us the year of the date result (2014) 
2. Month(2) + 1   - this will provide us the next months number result (11)
3. 1                     - one is entered to get 1st date of next month 
4. -1                     - this minus one will give us last day of previous month result (10/31/2014)
5. Day                 - this formula will provide us the required day output result (31)

conclusion - Initially we are finding first day of next month, then subtracting it with 1 to get last day of current month and at the end with the help of day formula we get the last day i.e. 31 as output.

Done...

How to use the CASE and the INPUTBOX in Excel VBA ?

Below is the example of the input box and the Case, used in excel VBA to check, the letter belongs to which grade description.

Copy paste the below code to any of the Excel VBA module and run it. you will get the pop-up window to enter the input letter. Enter the letter and click OK.

You will get the message box with the letter is for description value.

Sub testcase()

Dim grade As String
grade = "A"
grade = InputBox("Enter the Grade from A to D")
Select Case grade

Case "A"
MsgBox grade & " is for High Distinction"

Case "B"
MsgBox grade & " is for Credit"

Case "C"
MsgBox grade & " is for Pass"

Case Else
MsgBox grade & " is for Fail"

End Select
End Sub

Using the code examples in the above macro, this can be modified to do different tasks at different situations.
e.g., if you want to write the grade against the marks in excel, we can modify the above code adding the loop code and mapping the grades to marks.

Done...

How to find character in text using excel formula ?

You can use below formula to find the character in any word.

e.g. cell B1 contains the text Marks with 50% and we need to find if it contains %, we can write the below formula - 
=MID(B1,FIND("%",B1,1),1)

Output of the above formula will be %.


You can further customize this formula to write Found -
=IF(MID(B1,FIND("%",B1,1),1)="%","Found")

Output of the above formula will be Found.


If there is no "%" in cell B1 the formula will return #value! error, to overcome this we can customize the formula as below -
=IFERROR(IF(MID(B1,FIND("%",B1,1),1)="%","Found"),"Not Found")

If the character is missing in cell B1 then the output of the above formula will be Not Found.


You can replace the % to any character you need to find and you will get the output accordingly.

Done....

Find the difference between the two dates in excel

Below is the inbuilt function in excel which will retrieve the difference between two dates. We can get the results in number of days, number of months or number of years.

We can take a simple example to find the age difference from date of birth till today -

Date of Birth    - 23/01/1984    (enter value in cell A1)
Today's Date   - 06/05/2014    (enter value in cell B1)

=DATEDIF(L2,M2,"Y")&" Years, "&DATEDIF(L2,M2,"YM")&" Months, and "&DATEDIF(L2,M2,"MD")&" Days"

The output of the above formula will be -
30 Years, 4 Months, and 13 Days



=DateDif(A1,B1,"Y") = output will be number of years (Y)
=DateDif(A1,B1,"M") = output will be number of months (M)
=DateDif(A1,B1,"D") = output will be number of days (D)
=DateDif(A1,B1,"YM") = output will be number of calendar months from start year, here the months will be for current year
=DateDif(A1,B1,"YD") = output will be number of calendar days from start year, here the days will be for current year
=DateDif(A1,B1,"MD") = output will be number of calendar days from start month, here the days will be for current month


Above combination of codes can be used at different situations, one example we saw above -
1. To find the Age
2. Remaining days to your wedding
3. Days remaining to Expiring warranty or maintenance
4. Total employment experience
5. Property Agreement going to expire
...... and more

Done....

*all codes in this blog are tested on Excel 2010