Introduction to basics and advanced excel.pptx

Introduction to basic and advacne excel

1. Microsoft Office 2010 Microsoft Excel Basics 1

2. Introduction Excel is a commercially available Microsoft office application and is mainly used for numerical computation, data summarization and presentation Excel is basically a spreadsheet program and has all the capabilities of storing, organizing and manipulating the data. It is very rich in features and functions ranging from simple calculation and charting tools to advanced data management tools like data analysis tool pack, What-if analysis, Pivotal Table etc It also support a macro programming language environment called VBA programming.

3. Introduction Microsoft Excel spreadsheets have become somewhat of a standard for data storage, at least for smaller data sets. This, along with the program often being packaged with new computers, naturally encourages its use for statistical analyses. Excel offers a wide range of statistical functions you can use to calculate a single value or an array of values in your Excel worksheets. The Excel Analysis Toolpak is an add-in that provides even more statistical analysis tools. Check out these handy tools to make the most of your statistical analysis. Microsoft Excel, first released in 1985 has grown into a comprehensive spreadsheet which can also double up as an analysis software, charting software, flat-form database and a full-fledged programming environment. The MS Excel 2010 version contains about 120 statistical functions. Besides allowing for calculation of descriptive statistics like measures of central tendency, dispersion, skewness and kurtosis, it also has functions to compute correlation, linear regression slope and intercept. It even allows testing of hypothesis by providing functions like chi-test, f-test, t-test and z-test. It has functions to calculate the probability distributions of the major distributions – beta, binomial, chi-square, exponential, f, gamma, hyper-geometric, log normal, negative binomial, normal, Poisson, t, etc

4. Introduction MS Excel also provides “Data Analysis Tool Pack” which is available as an add-on in all the versions of the software. This tool pack acts as a graphical user interface between the user and the data and provides some additional analysis facilities to the user. The additional analysis that can be done using the tool pack include – ANOVA, Exponential Smoothing, Fourier Analysis, Histogram, Moving Average, Rank and Percentile, Multiple Regression and Sampling. Some of these techniques are described below:

5. Microsoft Excel 2010 is the spreadsheet program in Microsoft Office 2010. A spreadsheet is a grid of rows and columns in which you enter text, numbers, and the results of calculations. In Excel, a computerized spreadsheet is called a worksheet . The file used to store worksheets is called a workbook . 5 Introduction to Spreadsheets 5 5

6. Organize and store data in an easy-to-navigate way Advanced calculator Do basic  and  complex mathematical functions so you don’t have to Turn piles of data into helpful graphics and charts Analyze data and make forecasting predictions 7/21/2022 6 Why Excel ?

7. Application Areas Day-to-day computation Time / Event Schedule preperation Inventory Management Data Summarization / mining Data Visualization Database Management Data Analysis

8. 7/21/2022 8 Advantage of Learning Excel Visualize and manipulate data Design professional-level spreadsheets to lay out data intelligently and usefully Analyze information quickly and accurately Solve data management related problems with the advanced application of data Time saving Increase Efficiency

9. Pivot Tables Basic Math Charts and Graphs Conditional Formatting Sorting and Filtering AutoFill Drag and Drop AutoSum Grouping and Subtotal What-If Analysis Goal Seek Analysis 7/21/2022 9 Features of Excel 2010 Data Analysis Database management Sparklines

10. Excel program window 10 Starting Excel (continued)

11. active cell active worksheet adjacent range cell cell reference column formula 11 Vocabulary Formula Bar landscape orientation Microsoft Excel 2010 (Excel) Name Box nonadjacent range portrait orientation 11 11 range range reference row sheet tab spreadsheet workbook worksheet

12. Each workbook contains three worksheets by default. The worksheet displayed in the work area is the active worksheet. Columns appear vertically and are identified by letters. Rows appear horizontally and are identified by numbers. A cell is the intersection of a row and a column. Each cell is identified by a unique cell reference . 12 Exploring the Parts of the Workbook 12 12

13. The cell in the worksheet in which you can type data is called the active cell . The Name Box , or cell reference area, displays the cell reference of the active cell. The Formula Bar displays a formula when a worksheet cell contains a calculated value. A formula is an equation that calculates a new value from values currently in a worksheet. 13 Exploring the Parts of the Workbook (continued) 13 13

14. Available Columns: A through XFD – 16,384 columns Available Rows: 1 through 1,048,576 There are over 17 billion cells in each worksheet!!!! A cell is the intersection of a column letter and a row number. The cell address can be found in the Name Box just above column A. 7/21/2022 14 Exploring the Excel

15. The File tab (the first tab on the Ribbon) is used to display the Backstage view which contains all the commands related to managing files and customizing the program. It provides an easy way to create, save, open, share, print, and close files; find recently used files; view and update file properties; set permissions; set program options; get help; and exit the program. To display the Backstage view: Click the File tab on the Ribbon To exit the Backstage view: Click any tab on the Ribbon. Or, press the Esc key. 7/21/2022 15 File Tab (Backstage View)

16. Similar to earlier versions of File menu but instead of just a menu, it opens up a full-page view of controls which you can use.

17. Use to save a file that has had changes made to it. Use when needed to save a new a file for the first time or save an existing file with a different name. Use to open an existing file from your computer. Use to close an active worksheet. Use to create new workbook

18. The Home Ribbon The Insert Ribbon The Page Layout Ribbon The Formulas Ribbon 18 Excel Ribbons The Data Ribbon The Review Ribbon The View Ribbon The Developer Ribbon

19. Chart - Introduction A chart is a graphic representation of data. Charts are used to make a graphical representation of any set of data Data is represented by symbols such as bars in a bar chart or lines in a line chart.

20. Chart Type Scatter chart Stock Surface Doughnut Bubble Radar histogram PIE CHART: BAR CHART: COLUMN CHART: AREA CHART: LINE CHART :

21. Chart Component

22. Chart Component Chart Area — is the whole chart. Gridlines —are the vertical and/or horizontal lines that are useful in guiding the eye to more easily identify the value associated with each series. Plot Area: -area of the chart where the values are graphed. Legend —is the color key for the value series. By default this would be the row labels. Category Axis —if the series is by rows it will display the column labels. If the series is by column, the row labels will be displayed. Value Axis —displays the range of values plotted. Value Series —data in the default column chart are plotted by row in column bars called "series".

23. COLUMN CHART Shows the comparison between one or more series of data point. STACKED: two comparisons of data points for time periods are stacked. CLUSTERED: comparison of data series for each time period.

24. COLUMN CHART A column chart is a primary Excel chart type, with data series plotted using vertical columns. Column charts are a good way to show change over time because it's easy to compare column lengths.  Like bar charts, column charts can be used to plot both  nominal data  and  ordinal data , and they can be used instead of a pie chart to plot data with a part-to-whole relationship. Column charts work best where data points are limited With more data points, you can switch to a line graph. Column chart is used when a comparison is to be made across categories. Pros Easy to read Simple and versatile Easy to add data labels at ends of bars Cons Become cluttered with too many categories Clustered column charts can be difficult to interpret

25. Clustered Column Chart A clustered column chart can be used if you need to compare multiple categories of data within individual Items as well as between the items. In this example The comparison between years and between Revenue of XYZ and ABC

26. Stacked Column Chart In a Stacked Column Chart, the data points for each time period are "stacked" instead of "clustered." This chart type lets us see the percentage of the total for each data point in the series. In this Example each year was stacked with Revenue of XYZ and ABC showing the % of revenue share along with YoY comparison !00% Stacked column are showing the Share of each revenue item within each year out of 100% Normal Stacked Column Chart 100% Stacked Column Chart

27. PIE CHART Another frequently used chart is the pie chart. A  pie chart , sometimes called a  circle chart , is a useful tool for displaying basic statistical data in the shape of a circle A pie chart is  a circular chart that is sliced into sections  (similar to slicing a pie you would eat), each section represents a percentage. A pie chart represents the distribution or proportion of each data item over a total value(represented by the overall pie). Unlike in bar charts or line graphs, you can only display a single data series in a pie chart, and you can’t use zero or negative values when creating one. A negative value will display as its positive equivalent, and a zero value simply won’t appear. When creating a pie chart, the description of each section is called the  category , and the number connected to the category is called the  value . The categories will add up to 100 percent of whatever is being charted, and the relative size of each category is a visual representation of its relation to the whole. Categories shouldn’t overlap.

28. PIE CHART Pie charts are not effective if there are more than seven categories, but some of the variations available allow charts to display a couple more categories. Some experts don’t like pie charts because they find it difficult to accurately compare the angles, but adding labels to the data can overcome that issue. Doughnut Chart : This option looks just like a pie chart, but with a hole in the middle. Doughnut charts can have more than one data series. Each data series that you plot in a doughnut chart adds a ring to the chart.

29. Line Chart The line chart is one of the most frequently used chart types, typically used to show trends over a period of time. In this example we have shown the revenue trend of of xyz and abc over a period of three years.

30. Bar Chart The Bar Chart is like a Column Chart. The horizontal axis of a Bar Chart contains the numeric values. Use of Bar Chart versus a Column Chart depends on the type of data and preferences of the user. But advantage of Bar Charts is, they can display and compare a large number of series better than the other chart types.

31. Area Chart Area Charts are like Line Charts except that the area below the plot line is solid. And like Line Charts, Area Charts are used primarily to show trends over time or other category.

32. XY (Scatter) Chart XY (Scatter) charts are typically used for showing and comparing numeric values, like scientific, statistical, and engineering data. A Scatter chart has two Value Axes − Horizontal (x) Value Axis Vertical (y) Value Axis It combines x and y values into single data points and displays them in irregular intervals, or clusters. To create a Scatter chart, arrange the data in columns and rows on the worksheet. Place the x values in one row or column, and then enter the corresponding y values in the adjacent rows or columns.

33. XY (Scatter) Chart Consider using a Scatter chart when − You want to change the scale of the horizontal axis. You want to make that axis a logarithmic scale. Values for horizontal axis are not evenly spaced. There are many data points on the horizontal axis. You want to adjust the independent axis scales of a scatter chart to reveal more information about data that includes pairs or grouped sets of values. You want to show similarities between large sets of data instead of differences between data points. You want to compare many data points regardless of the time. The more data that you include in a scatter chart, the better the comparisons you can make.

34. Combination Chart (Combo) A combo chart is a combination of two or more chart types into a single chart. Combination charts are best to compare two categories of each individual Series. They are commonly used to create visualizations that show the difference between targets versus actual results.

35. Combination Chart (Combo) A combo chart is a combination of two or more chart types into a single chart. Combination charts are best to compare two categories of each individual Series. They are commonly used to create visualizations that show the difference between targets versus actual results. Combo charts combine two or more chart types to make the data easy to understand, especially when the data is widely varied. It is shown with a secondary axis and is even easier to read.