Pivot Table (Beginner)

PIVOT TABLES
 
Being able to quickly analyze data can help you make better business decisions.

When you have data especially large data, PivotTables are a great way to summarize, analyze, explore, and present it. PivotTables are highly flexible and can be quickly adjusted depending on how you need to display your results. You can also create PivotCharts based on PivotTables that will automatically update when your PivotTables do.

You can create PivotTable with just a few clicks, but before you get started be careful for …
* Data should be in tabular format
* All columns should have proper header at first row
* Not have any blank row or column
* Data types within a column should be same
* Excel tables are a great PivotTable data source, because rows or columns added to a table are automatically included in the PivotTable when you refresh the data. Otherwise, you need to manually update the data source range.
* PivotTables work on a snapshot of your data, called the cache, so your actual data doesn't get altered in any way.

GETTING STARTED

Create PivotTable: 
1. click anywhere in your source data
 

2. from Insert tab click at PivotTable button

3. Create PivotTable dialog box will be appeared. Review the selections, then click OK.

4. A blank PivotTable will be appeared on left side of your worksheet, and its field lists will be at right side.
 

Add fields to the PivotTable:

Example of PivotTable usage
1. Comparing Sales Totals of Different Products
2. Combine Duplicate Data

Our data consist of 500 rows and having 08 columns. InvNo, InvDate, Customer, City, Product, ProdCategory, QuantitySold, Amount

To see product wise sales, in PivotTable Fields pane add/drag fields as
* Product in Row area
* Amount in Values area
* ProdCategory in Filter area.

For more information, check below link or write us ...
Support.Office.com 

Repeat Last Command Used (Shortkey)

Repeat last command used by pressing <F4> key.

Shortkeys to work around in Excel Environment (Shortkeys/HotKeys)

Important short keys to work around in Microsoft Excel Environment to speedup your productivity ...  

KEYS------------------------	Description-------------------------------------------
Selection
Ctrl + A	Select All (if...)
Shft + ArrowKeys	Select adjacent cell
Shft + SpaceBar	Select whole row
Ctrl + SpaceBar	Select whole column
Column/ Rows/ Cells
Alt O C W	To change column width by points
Alt O R E	To change row height by points
Alt O C A	AutoFit column width as selected cell’s data.
Alt I R	Insert above a blank row
Alt I C	Insert right-side a blank column
Ctrl +	Insert column/ Row/ Cell.
Ctrl –	Delete column/ Row/ Cell.
Ctrl 0	Hide column(s)
Ctrl 9	Hide row(s)
Alt O C U	Unhide columns within selected range
Alt O R U	Unhide rows within selected range
Alt W F F	Toggle Freeze/ Unfreeze rows/columns
Sheets
Ctrl PgUp	Goto previous sheet
Ctrl PgDn	Goto next sheet
Shft F11	Insert new worksheet
Alt H O R	Rename active sheet
Alt T P P	Toggle Protect/ Unprotect sheet
Alt I B	Toggle page break
Alt H O M	To get “Copy and Move” dialogue box
While Cell Editing
Home	Goto start of line
Ctrl Home	Goto Start of cell contents
End	Goto end of line
Ctrl End	Goto end of cell contents
Ctrl LeftArrow	Move one word left side
Ctrl RigthArrow	Move one word right side
Ctrl Delete	To delete all data right side of cursor in cell.

CHOOSE Function

Returns a value from a list based on position. Uses index_num to return a value from arguments.

Syntax:     =CHOOSE(index_num, value1, [value2], ...)

index_num  must be a number or a formula or reference to a cell containing a number between 1 and 254.

value1, value2, ...  Value 1 is required, subsequent values are optional. 1 to 254 value arguments from which CHOOSE selects a value.


Examples:
if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

=CHOOSE(A1,"Mon","Tue","Wed","Thu","Fri","Sat","Sun")

=SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))
this will work as =SUM(B1:B10)


Common Errors
#VALUE!    -  
*  The supplied index_num is less than 1 or is greater than the supplied number of values;
*  The supplied index_num argument is non-numeric.

#NAME?
*  Occurs if any of the value arguments are text values that are not enclosed in quotes and are not valid cell references.




For more detail, check below links or contact us ...
* CHOOSE Function at https://office.com

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)