Excel-in-XL Intermediate

Excel-in-XL course:
Basic to Intermediate Level

Attendees knowledge of this course steps forward from Beginners level to Intermediate level user of Microsoft Excel.


WHO SHOULD ATTEND

This course is important for those who are using Excel as their basic working tool and want to step ahead by learning smart ways. After learning skills in this course and by efficiently use them can save at-least 4 to 5 hours weekly.

Beginner users who have basic knowledge of Excel and can make basic calculations.

WHAT YOU WILL BE ABLE TO DO

Environment:

• Better understanding of Excel 2016 interface
• Easily working around with Excel environment (e.g. rows/columns/etc, selections)
• Secure data and/or restrict your users to post data within required cells only.

Data & File Handling:

• Change the order of data
• View part of data based on criterias
• Reports Preparations (Data and Cell Formatting), 
• Page Setup, File handling and Printing.

Formulas: 
Formulas make Excel a smart spreadsheet program. In this course you will learn ...

• Fundamentals of formulas and functions. 
• Simple and complex formulas introduction
• Cell Referencing, its types and their usages.
• Auto & quick SUMs, other basic functions.

Charts:

• Introduction to data visualization with Charts
• Ability of select appropriate chart, which is suitable for which data
• Create Column, Bar, Line and Pie graphs.
• Customization

Smart practical tips and tricks along-with shortcut keys.






To register yourself in upcomming sessions fill the below form for registration...
<...>
 

*int_v2*

Formatting Shortcut Keys in Excel 2010

Formatting Shortcut Keys

SHORTCUT_KEYS____	DESCRIPTION______________________________________
Ctrl + B	Apply bold formatting on selected contents
Ctrl + I	Apply italic formatting on selected contents
Ctrl + U	Apply underline formatting on selected contents
Ctrl + 5	Apply/remove strike-through formatting on selected contents
Ctrl + 1	Open Format Cells dialog box
Alt  + ‘	Open Style dialog box
Ctrl + Shft + ~	Apply General Format on selected range
Ctrl + Shft + $	Apply Currency format on selected range
Ctrl + Shft + %	Apply Percentage format on selected range (#,###%)
Ctrl + Shft + #	Apply Date format on selected range (dd-mmm-yy)
Ctrl + Shft + @	Apply Time format on selected range (h:mm:ss AM/PM)
Ctrl + Shft + !	Apply Number format on selected range (#,###.00)
Ctrl + Shft + &	Apply outline-border on selected range
Ctrl + Shft + _	Remove cell borders from selected range
Alt + H + B + A	Apply cell border to all selected cells.

for more information, check below link:

Support.Office.Com

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.

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

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)

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