excel training by rajesh p

gyan session Oct 2014Rajesh P MenonExcel Training

1

Remember ..

Are you also like this??

Objectives and Take awayObjectivesThe main objective of this Session is to give the participants insight into Excel which can be used as a tool for managing organization data and also to analyze the data in efficient way

Take awayAt the end of this session each participants should be able to generate MIS and reports on their own using Excel features and functionalities acquired

Objectives for instruction and expected results and/or skills developed from learning. 4

Session TopicsExcel OverviewExcel Formulas and data FormattingWorking with Excel functions and Data fill utilityData validation, analysis (Pivot tables and Charts), Data Filter and SortingReport Printing in ExcelWorking with Charts and GraphsIntroduction on MacrosConclusion

Introductory notes.5

Chapter 1Excel overviewGYAN Excel Training

6

Excel OverviewWhat is ExcelMicrosoft Excel is an electronic spreadsheet that runs on a personal computer i.e. desktop, laptop etc. A spreadsheet is the computer equivalent of a paper ledger sheet. It consists of a grid made from columns and rows.Benefits of using ExcelYou can use it to organize your data into rows and columns i.e. in a tabular format.You can use it to perform mathematical and financial calculations accurately.You can use it to generate MIS and various trend analysis.

Relative vocabulary list. 7

Excel Overview

MS Office Button

Quick Access Bar

Title Bar

Formula Bar


Excel Overview

Example of a Ribbon shown below along with the explanation


Excel Overview


Excel OverviewUseful information about Excel 2007 which you can use and rememberFEATUREMAXIMUM LIMITWorksheet Size1,048,576 rows by 16,384 columnsColumn Width255 charactersRow Height409 pointsTotal number of characters that a cell can contain32,767 charactersCharacters in a Header & Footer255 charactersSheets in a workbookLimited by System memoryDate range allowedJanuary 1, 1900 to Dec 31, 9999


Chapter 2Excel formulas & formatting DATAGYAN Excel Training

12

Excel Formulas and FormattingFormatting in ExcelChange the column width manually and automatically.Change the row height manually and automatically.Auto adjust the entire sheetEditing/Clearing data in a cellInserting/Deleting columns/rows in a sheetAligning cells (top, bottom & center) and text (Left, Right and Center)Wrapping text


Excel Formulas and FormattingFormatting in Excel Contd Changing the fonts and paragraphFormatting the numbers and datesUsing Format PainterMerge and CenterUndo and RedoBorders & ShadingConditional FormattingFormat as table


Excel Formulas and FormattingExercise for Formatting Open the sheet Exercise Sheet for participants provided to you


Excel Formulas and FormattingArithmetic Operations in ExcelBasic Arithmetic Addition SubtractionDivisionMultiplicationExponent (Power and roots) and brackets Excel follows the BODMAS concept


Chapter 3WORKING with Excel Functions & AUTO Fill UTILITY GYAN Excel Training

17

Key Excel FunctionsFunctions in ExcelFunction Categories Arithmetic, Financial, Text, Date, Logical, ReferenceCommonly Used Arithmetic functions Sum, Average, Count, Round, Max and MinCommonly used financial functions PV, FV, PMTCommonly used String functions Upper, Lower, Proper, ConcatenateCommonly used Logical function and operator If, And, OrCommonly used Lookup functions Vlookup, Hlookup

Important Point:To use functions, you need to understand reference operators. Reference operators refer to a cell or a group of cells. There are two types of reference operators: range and union.A range reference refers to all the cells between and including the reference. A range reference consists of two cell addresses separated by a colon. The reference A1:A3 includes cells A1, A2, and A3. The reference A1:C3 includes cells A1, A2, A3, B1, B2, B3, C1, C2, and C3

18

Excel Auto FillAuto Filling of Data in ExcelExcel has provided a intelligent feature known as Auto Filling of the dataExample Type January in cell A1 and then use the handler and drag the same either vertically or horizontally. By doing the same the data gets filled automatically i.e. February, March, and so onSee the next slide which shows the Auto fill list that are available in the Excel by default.


Excel Auto FillSome more examples of standard list below


Excel Auto FillAuto Filling of Custom Data in ExcelYou can also create Custom Auto fill list. This can be achieved by selecting Office Button and then select Excel Options and from the Popular tab select Edit Custom Lists. Create the Custom list that you use frequently and is not available by default. In this example below I have created a Auto list of alphabets from A to Z.

Relative vocabulary list. Show the example to the participants.

21

Excel Auto FillAuto Filling of Data in Excel Custom Lists


Excel Auto FillAuto Filling of Data in Excel Custom Lists


Chapter 4data Validation, Analysis, FILTERING AND SORTINGGYAN Excel Training

24

Data Management Using ExcelData ManagementExcel is extensively used by Corporates / Banks /FI etc for managing large volume of data including Balance sheetsThe major functionality available in the Excel for managing data and extracting reports are as follows:Data SortingData filteringData ValidationData GroupingData SubtotalPivot Tables


Data Management Using ExcelData SortingSorting allows one to manipulate and manage data in a worksheet on a give set or criteria.There are two types of sorting features available in ExcelBasic SortingCustom SortingBasic Sorts : To arrange the data based on the first column select the option as displayed below. Remember that all the data is selected before you sort or else the data will be distorted.


Data Management Using ExcelData SortingCustom Sort : if you want to sort on more than 1 criteria then you need to use the Custom sort.

On clicking the above the following dialogue box will open and here you can select multiple sorting criteria.


Data Management Using ExcelData Sorting contd .. The following dialogue box will open and here you can select multiple sorting criteria.

28

Data Management Using ExcelData FilteringFiltering allows to display data in a worksheet that meets a given set of criteria.To filter the data select the option as displayed below. Excel automatically places the filter on the topmost header row of the selected data in the worksheet.The filtered data can then be copied to another worksheet as reports or it can be directly printed


Data Management Using ExcelData ValidationValidation helps to build business rules while capturing data in Excel. This feature also helps reduce data entry errors which in turn helps in easy reconciliation.For example we are capturing students exam data and the marks column should contain marks between 0 to 100. We can build validation which will enforce the same and thus help us to eliminate data entry errors. Time to show the same in Excel.


Data Management Using ExcelData SubtotalThe subtotal command can be used to outline data in many different waysIt uses common functions like SUM, COUNT, AVERAGE, MAX, MIN and PRODUCT to summarize the dataThe data must be sorted before the subtotal command can be used and this is very importantOpen the Sales data sheet for this exercise. We will find the subtotals of all the sales figures based on the product


Data Management Using ExcelOnce you click on the Subtotal option the following box opens where you can provide the parameters for change, function and subtotal

Select the data sheet and then click on the Data ribbon and you can select subtotal from the Outline group.


Data Management Using ExcelOnce the above actions are done the sheet will look like the following. You can click on the levels highlighted below to see different views


Data Management Using ExcelData GroupingThe Group utility helps you to group any range of cells either columns or rowsIt does not calculate a subtotal of the group This feature gives you a ability to show or hide any part of your worksheet ad display only the information you needIn our Sales data sheet we can group Qtr2 to Qtr4 and then hide that to see only the Qtr1 data


Pivot TablesWhat are pivot tablesPivot tables are a versatile reporting tool that makes it easy to extract information from large tables of data without using any formulaPivot tables are extremely user friendly because by moving, or pivoting, fields of data from one location to another using drag and drop we can look at the same data in a number of different ways


Pivot Tables ContdImportant points to rememberThere has to be at least 3 columns of data for crating pivot tableIt is important that the data is entered correctly. Remember GIGOLeave no blank rows or columns when entering the data. This means that even between the column header and first row of data there should not be any blank row


Pivot Tables Contd

The Pivot table field name listThe data areas are linked to the Pivot table


Pivot Tables ContdFiltering the Pivot table dataThe Pivot table has built in filtering tools that can be used to fine tune the results shown by the table

Filtering data involves using specific criteria to limit the data that is displayed by the Pivot table

To do this the user has to select the drop down from the header of the Pivot table report and filter


Chapter 5Report printing in excelGYAN Excel Training

39

Excel PrintingHeader & Footer in ExcelHeader and Footers helps while printing the Excel data in hard copy (printer). For example - the company logo can be inserted as a Header and page number can be inserted in the FooterThe advantage of the Header and Footer is that it will repeat in each and every page that you print and the report will look authenticTo insert Header and Footer go to the Insert Ribbon and select Header and Footer from the Text option. The Page layout view will automatically become active (default is Normal view) and you can insert the logo and page numbers.


Excel PrintingPrinting in ExcelTo print any document from Excel just press Ctrl+P or click on Office button and select Print.You can select the Print Preview to see how the output will look before printing the documentOther features are Page Orientation, Paper size, MarginsYou can also use Page Break view to check how the output looks and it also helps to compress the contents for printing in single sheets


Chapter 6working with graphs & chartsGYAN Excel Training

42

Graphs/ChartsA chart or graph often makes it easier to understand the data in a worksheet because users can easily pick out patterns and trends illustrated in the chart that are otherwise difficult to seeIn Excel we can create the following types of graphsBar GraphsLine GraphsScatter Plot GraphsPie Charts


Bar GraphsBar Graphs are the most common type of graph used to display data. Also known as Column charts, bar graphs are most often used to show the amounts or the number of times a value occursBar Graphs make it easy to see the difference in the data being compared. See the example below.

Example graph/chart.44

Line GraphsLine Graphs are often used to plot changes in data over time, such as monthly temperature change or daily stock price changeThey can also be used to plot data recorded from scientific experiments, such as how a chemical reacts to changing temperature or atmospheric pressure. See the chart below.

Example graph/chart.45

Pie ChartsPie charts, or circle graphs (as they are sometimes known), are used to show percentagesThe circle of the pie charts represents 100%The circle is subdivided into slices representing data values

Example graph/chart.

46

Sessionintroduction to macroGYAN Excel Training

47

MacrosA Macro is a tool which helps the users to automate many actions This helps in saving time to do the repetiive tasksYou can create data entry forms using Macros and that only a programmer or persons with reasoning ability can do


How to create MacrosTo record a macro follow the stepsFirst activate the Developer tab (ribbon)In the Developer ribbon click on the Start RecordingRecord you actionsOnce done press the Stop RecordingTo run the macro click the shortcut or select the macro and it will display the list of the macros. Select the one you want to run and leave the rest to Excel


Sessionwhats mail mergeGYAN Excel Training

50

Mail MergeMail merge utility helps users to create multiple documents from a single template and structured data sourceThe document created using Mail merge can be sent to many recipients wit small changes, such as change in address or a change in greeting linePlaceholders are created in the document which will point to the external data source like Excel or any database and while generating the document these placeholders are replaced with the values in the data source


Mail Merge ContdWe will see the illustration whereby we are using the data from the mail merge data sheet and creating document in Word

Excel Data Source which contains the invitee listIn MS Word open a new document and select the Mailings Ribbon and click on Start Mail Merge


Mail Merge ContdForr selecting the data source see the screen shotOnce the data source is selected then the following Mail merge options becomes active


Mail Merge ContdOnce the data source is selected now it is time to create the template and also inserting the placeholders in the document

The dropdown above shows the list of column in the Excel data sourceThe placeholders are created as shown


Mail Merge ContdNow the final stages of the mail merge. We have to generate the letters using the data source provided and lets see how that is doneOnce you have created the document then click on the Finish Merge and create the merged document

Click this option to preview the document and it will show the record which is currently selected


ConclusionToday we had discussed on the following:How to enter Excel Formulas and formatting dataHow to use the Excel functionsHow the Auto fill feature to be usedHow to Print reports How to Filter and Sort the dataHow to create Pivot tablesHow to group and subtotal How to create Charts/GraphsMail merge using Excel & Word

Conclusion to course, lecture, et al. 56

Questions/DiscussionsQuestions and Discussion

What Next ?You can reach me on 8095254254 from morning 10 AM to 9 PM on weekends On weekdays you can call me between 7 PM to 10 PM.My email id is [email protected]

An opportunity for questions and discussions.57

Thanks

Example graph/chart.

58

excel training by rajesh p

Education