pg. 1 Excel and Data analysis Seminar Report in partial fulfilment of the requirements for the award of degree of MASTER OF COMPUTER APPLICATIONS by SRISHAILA (Reg. No: 170970025) Under the guidance of Dr. Rohini Rao Assistant Professor -senior Scale Department of computer Application.

M.I.T., Manipal – 576 104 Mrs. Linda Varghese Assistant Professor Department of computer Application. M.

I.T., Manipal – 576104pg. 2 ABSTRACT The ability to analyse data is a powerful skill that helps you make better decisions. Microsoft Excel is one of the top tools for data analysis. The use of Excel is widespread in the industry.

It is a very powerful data analysis tool and almost all big and small businesses use Excel in their day to day functioning. Excel provide commands and tools that make our data analysis tasks easy. we can avoid many time consuming and complex calculations using Excel. Here we are using some excel features like Sorting, Filtering, What if analysis, Spark lines, Pivot tables, Pivot charts, Conditional Formatting, Color code for manipulating data.pg. 3 Contents Abstract …………………………………………………………………………………2 List of Tables ………………………………………………………………………….4 List of Figures …………………………………………………………………………4 Introduction 1.

1 Objectives …………………………………………………………………………5 Literature Review ………………………………………………………5 Functionality of Excel Features 2.1 Sorting …………………………………………………………………………..….7 2.2 Filtering ……………………………………………………………………………..

7 2.3 What-If Analysis …………………………………………………………….…..

8 2.4 Sparklines …………………………………………………………………….……10 2.5 Pivot Tables ………………………………………………………………….……10 2.

6 Pivot Charts ……………………………………………………………………….11 2.7 Color Coding …………………………………………………………………….

..12 2.

8 Conditional Formatting ………………………………………………………12 2.8.1 Highlight Cells Rules 2.8.

2 Top/bottom 2.8.3 Color Scales 2.8.

4 Icon Sets 2.8.5 Clear Rulespg. 4 Summary/Conclusion 3.1 Conclusion ………………………………………………………………………….

14 References …………………………………………………………………..14 List of Tables 2.2 Sorting vs Filtering ………………………………………………….

…………

8 2.5 Excel Table vs Pivot Table ……………………………………………..….11 List of Figures Figure 2.1 Sorting …………………………………………………………………….7 Figure 2.

2 Filtering ……………………………………………………………………8 Figure 2.3 What-If Analysis ……………………………………………………….8 Figure 2.4 Sparklines ………………………………………………………………..10 Figure 2.5 Pivot Tables ……………………………………………………………..

10 Figure 2.6 Pivot Charts ……………………………………………………………..

11 Figure 2.8 Conditional Formatting …………………………………………….12pg.

5 Introduction 1.1 Objectives Excel provide commands and tools that make our data analysis tasks easy. we can avoid many time consuming and complex calculations using Excel. Review of Literature ? What is Data Analysis (Definition) It is a process of inspecting, cleaning, transforming, and modelling data. The main goal of data analysis is to highlight the useful information, suggest the conclusions, and support the decision making.

? History of Spreadsheet A paper called ‘A Brief History of Spreadsheets’ by D. J. Power (Editor, DSSResources.COM) in his paper he says that Spreadsheets have been used by accountants for hundreds of years.

But Computerized or electronic spreadsheets are of much more recent origin. And was invented by Dan Bricklin. Muliplan was Microsoft’s first electronic spreadsheet program It was introduced in 1982 as a competitor for VisiCalc. VisiCalc was the first electronic spreadsheet program. Excel and Data analysispg.

6 Muliplan was very popular on CP/M systems, but on MS-DOS systems it lost fame to Lotus 1-2-3. This thing motivated Microsoft to develop another spreadsheet product Excel. The first version of Excel was released in 1985 for Mac. Later in November 1987, the first Windows version was released. ? Microsoft Excel Excel is one of the powerful tool which are used to organize, calculate and format a data. Excel provides many commands and we are using excel as tool for data analysis .It makes data analysis task easy.

? Features Excel comes up with plenty of features. Some basic and important features are discussing in this topic such as, Sorting, Filtering, What-If Analysis, Sparklines, Pivot Tables, Pivot Charts, Color Coding and Conditional Formatting. Excel and Data analysispg. 7 Functionality of Excel Features 2.1 Sorting You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order. To sort in ascending order, on the Data tab, in the Sort ; Filter group, click AZ To sort in descending order, click ZA. figure 2.

1 2.2 Filtering You can Filter your Excel data if you only want to display records that meet certain criteria. Click any single cell inside a data set. Excel and Data analysispg. 8 figure 2.2 On the Data tab, in the Sort ; Filter group, click Filter. Sorting vs Filtering: Sorting Filtering Same search results, different order. Filtering reduces the number of results Sorting takes a set of data and sorts it by a chosen metric or letter Filtering removes data that’s irrelevant to your analysis 2.

3 What-If Analysis What-If Analysis in Excel allows you to try out different values (scenarios) for formulas. figure 2.3 Excel and Data analysispg. 9 A Scenario is a set of values that Excel saves and can substitute automatically in cells on a worksheet. You can create and save different groups of values on a worksheet and then switch to any of these new scenarios to view different results. For example, suppose you have two budget scenarios: a worst case and a best case. You can use the Scenario Manager to create both scenarios on the same worksheet, and then switch between them.

For each scenario, you specify the cells that change and the values to use for that scenario. When you switch between scenarios, the result cell changes to reflect the different changing cell values. If you know the result that you want from a formula, but you are not sure what input value the formula requires to get that result, you can use the Goal Seek feature. For example, suppose that you need to borrow some money. You know how much money you want, how long a period you want in which to pay off the loan, and how much you can afford to pay each month. You can use Goal Seek to determine what interest rate you must secure in order to meet your loan goal. If you have a formula that uses one or two variables, or multiple formulas that all use one common variable, you can use a Data Table to see all the outcomes in one place. Using Data Tables makes it easy to examine a range of possibilities at a glance.

Because you focus on only one or two variables, results are easy to read and share in tabular form. Excel and Data analysispg. 10 2.

4 Sparklines A Sparkline is basically a little chart displayed in a cell representing your selected data set that allows you to quickly and easily spot trends at a glance. figure 2.4 2.5 Pivot Tables Pivot tables are one of Excel’s most powerful features. A pivot table allows you to extract the significance from a large, detailed data set. figure 2.5 Excel and Data analysispg.

11 Excel Table vs Pivot Table: Excel Table Pivot Table An Excel table is simply a set of rows and columns in a worksheet that contains related data and is displayed in a specific table format A PivotTable is an interactive table Helpful for calculating values and displaying totals and grand totals Quickly summarizes large amounts of numeric data, which you can then analyze in detail. 2.6 Pivot Charts A pivot chart is the visual representation of a pivot table in Excel. Pivot charts and pivot tables are connected with each other. figure 2.6 Excel and Data analysispg. 12 2.7 Color Coding Color coding makes your sheet faster to read, harder to mess up, and more professional.

2.8 Conditional Formatting Conditional formatting in Excel enables you to highlight cells with a certain color, depending on the cell’s value. figure 2.8 Excel and Data analysispg. 13 2.

8.1 Highlight Cells Rules The Excel Conditional Formatting Highlight Cells Rules allow you to apply formatting to highlight cells that satisfy one or more specific conditions. These conditions can relate to numeric values (e.g. greater than, between), dates (e.g. a date occurring..

.) or text values (e.g. text containing.

..).

You can also use the Highlight Cells rules to highlight cells containing duplicate values. 2.8.2 Top/bottom The Excel Conditional Formatting Top/Bottom Rules allow you to apply formatting to cells that satisfy a statistical condition in relation to other cells in the range (e.g. above average, within top 10%, etc.). These conditions will only be applied to cells containing numeric values.

2.8.3 Color Scales When using color scales in conditional formatting, Excel assigns one color to the lowest value, and another to the highest value.

Other values are assigned a weighted blend of color. This makes it easy to see general patterns in data, especially with a large data set. 2.8.4 Icon Sets For conditional formatting, you can use icon sets to mark the values which meet your conditions.

The icon sets are more attractive to draw your attention. This article will show you how to use icon sets to highlight values in conditional formatting in Excel in details. Excel and Data analysispg. 14 2.8.5 Clear Rules To clear a conditional formatting rule we can use Clear Rules. Summary/Conclusion 3.

1 Conclusion Using above Excel Features we can sort our excel data , filter our data ,can apply effect to a data and we can represent data as pivot tables and pivot charts. References 1.www.tutorials.com 2.

www.edx.org. 3.

www.excel-easy.com 4. www.exceltrick.com Excel and Data analysis