Microsoft Excel 2010 Interface

At first; Microsoft Excel 2010 interface (screen) looks as divided into two parts; 1) Command buttons and 2) Worksheet; but; there are many other parts as well, if you have knowledge about them you will have an ease to read and understand whenever they referred- in any process either downloaded from internet, book or even by a teacher.

Below is a snap along-with parts names. Click to enlarge.

.

Add or Delete Cell Borders (Shortkeys)

Shortkeys to add or remove a cell or range of cells borders

Ctrl + H + B + S ... Border Outline on a selected range
Ctrl + Shft + 7 .... Border Outline on a selected range
Ctrl + H + S + A ... Border all cells on selected range

Ctrl + H + B +N  ... Clear/delete borders
Ctrl + Shft + -  ... Clear/delete borders

Example:

Find a Quarter Number from Given Date

A custom formula using two functions and return quarter number from given date, based on calendar year.

   =ROUNDUP(MONTH(A1)/3,0)

Flash Fill

Flash fill is a new tool introduced in Microsoft Excel 2013, to fill out data based on an example.
 

Excel detect a pattern in your data, fill accordingly remaining data within a flash of time. It works best when your data has some consistency. Flash Fill is available in Data tab and Ctrl+E is a shortkey

Rather than manually entering first, middle, or last names in respective columns (or attempting to copy an entire client name from column A and then editing out the parts not needed in the First Name, Middle Name, and Last Name columns), you can use Flash Fill to quickly and effectively do the job.

For more detail check at office.com by clicking here.

Get first word of a cell

To extract first word from a cell, create a custom formula by combining LEFT() and FIND()

    =LEFT(A1,FIND(" ",A1,1)-1)

Change Orientation of Data (Transpose)

Change orientation of data from rows to column and vice versa.

    1. Select n copy data, 
    2. Place cursor where u want data
    3. Goto Paste Special, (CTRL+SHFT+V)
    4. Click to enable Transpose option, (E)
    5. Click on OK (Enter)

Below snap will be helpful to understand ...

(click on image to enlarge)

VLOOKUP Function

VLOOKUP() Search a given value in the first column of an array, and returns a corresponding value from another column.

Syntax:
 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 

Arguments:
1) lookup_value -- what u want to look in the first column of table_array

2) table_array -- range to look the lookup_value in first column and also it covers the corresponding return column.

3) col_index_num -- the corresponding column number in table_array where from desired return value lying.

4) range_lookup -- match type; for approximate match use true/1 and for exact match use false/0

Important:
* search from top to bottom, in first column of table_array and stops at the first match
* for corresponding column, it always goes from left to right.
* by default range_lookup is true
* sorted data is required if range_lookup is true, no need to sort if false.

How it works ...

How to use ...
suppose on a sheet we have data which downloaded from an ERP, where department IDs are mentioned instead of department names and department ID with department names are at available at another sheet.
The requirement is to add a column on main sheet and show department names.

check below example ...

VLOOKUP() with Approximate match ... 

For more detail, check below links or contact us ...

* VLOOKUP Function at https://office.com

* VLOOKUP When and How to use it at https://office.com

Paste with Source Column Width

Sometimes when u copy data from one location to another, u want to adjust the column width same as source data. You can do it by following steps ...

1. Copy the data u want
2. Goto location where u want to paste
3. Goto Home tab > Paste button (lower part as showing in pic)
4. Click on button "Paste with source width", at 2nd row 2nd column (as showing in pic)

Shortkey is ALT + H + V + W

Insert system date or time (Shortkey)

Shortcut keys to insert system date or time in a cell

for date ... Ctrl + ;
for time ... Ctrl + Shft + ;

Welcome to Excel in XL

Welcome to the Excel in XL training blog ...

The main goal of this community is to share the knowledge of Microsoft Excel, Tips n Tricks, exchange the experiences, response to the queries asked, etc.