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*

Become Certified MOS 2016 (a YouTube PlayList)

Learn how to become certified Microsoft Office 2016 Specialist of Word, Excel, PowerPoint & Outlook / Expert of Word & Excel / Master from different experts.
.

.
Above video belongs to a PlayList
Collection of MOS 2016 Video Tutorials from experts

Data Validation - Basics

To prevent incorrect data posting in specific cells we use Data Validation. 
if you created a worksheet that will be used by others, you need to ensure that only correct data that matches with requirements is entered. 

For example: between (10 and 20), or only specific text (Mr., Mrs, or Miss), etc.

Following are some basic data validations:

Whole Number:This can be used to restrict whole number data entry as per defined validation only.
For example you can prevent data which is out of a number range. See below animation.

If you understand the above, you can explore following more options to validate the data entery:

* Decimal
* Date
* Time
* Text Length
* List   (multi-choice drop-down list.  Items separated with comma)

Following advanced options and will be posted seperately ...

* Custom

For more detail, write us ...

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 

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.

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)

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